Sqlite的使用详解，Sqlite使用详解

Sqlite的使用详解，Sqlite使用详解

1.SQLite的介绍

        sqlite是一款轻量型的数据库，不需要安装且不依赖第三方软件，除了主流操作系统 windows，linux之后，SQLite还支持其它一些不常用的操作系 统。 当然sqlite也是Android原生数据库，既然sqlite这么好，那么今天我就带着大家一起探索一下sqlite的真面目。

2.SQLite的使用步骤

1.创建SQLiteDBHelper, 继承SQLiteOpenHelper

class SQLiteDBHelper extends SQLiteOpenHelper{
   private static final int VERSION = 1;
   private static final String DB_NAME = "persons.db";
   
   public SQLiteDBHelper(Context context) {
      //在构造器中创建数据库
      super(context,DB_NAME,null,VERSION);
   }

   @Override
   public void onCreate(SQLiteDatabase sqlitedatabase) {
      //在oncreate中创建表
      sqlitedatabase.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" + COLUMN_ID + " INTEGER PRIMARY KEY,name TEXT,age INTEGER)");
   }

   @Override
   public void onUpgrade(SQLiteDatabase sqlitedatabase, int i, int j) {
      //修改表,创建新表,数据备份,删除表
      sqlitedatabase.execSQL("ALTER TABLE " + TABLE_NAME + " RENAME TO PERSON_TEMP");
      sqlitedatabase.execSQL("CREATE TABLE " + TABLE_NAME + " (_id integer primary key,name varchar(20),age integer,sex varchar(2))");
      sqlitedatabase.execSQL("INSERT INTO " + TABLE_NAME + " (_id,name,age,sex) SELECT _id,name,age,'女' FROM PERSON_TEMP");
   }
}

2.实例化SQLiteDataBase

//在类的构造方法中初始化Context,SQLiteDatabase
public SQLiteDAO(Context cxt) {
   mContext = cxt;
   sqliteDBHelper = new SQLiteDBHelper(mContext);
   //创建数据库
   sqliteDataBase = sqliteDBHelper.getWritableDatabase();
}

3.创建bean对象，并为之序列化

public class Person implements java.io.Serializable{

   private static final long serialVersionUID = 1L;
   
   private int id;
   private String name;
   private int age;
   
   public Person(){}
   
   public int getId() {
      return id;
   }
   
   public void setId(int id) {
      this.id = id;
   }
   
   public String getName() {
      return name;
   }
   
   public void setName(String name) {
      this.name = name;
   }
   
   public int getAge() {
      return age;
   }
   
   public void setAge(int age) {
      this.age = age;
   }
}

以上表，库创建完成，接着看具体操作：

3.SQLite的具体应用

1.增加数据：

//添加数据
public void insertPerson(Person person) {
   String INSERT_SQL = "INSERT INTO " + TABLE_NAME + " (" + COLUMN_NAME
                   + "," + COLUMN_AGE + ") " + "VALUES (" + "'" + person.getName()
                   + "'" + "," + person.getAge() + ")";
   sqliteDataBase.execSQL(INSERT_SQL);

如果你觉得麻烦的话，还可以：

 ContentValues cv = new ContentValues();
 cv.put(COLUMN_NAME, person.getName());
 cv.put(COLUMN_AGE, person.getAge());
 //当cv参数为空或者没有内容的时候,insert会失败,为了防止这种情况发生,
 // 要在第二个参数设置一个列名,当要插入的行为空行时,将指定的列名值设置为null
 sqliteDataBase.insert(TABLE_NAME, null, cv);

 2.删除数据：

//删除数据
public void deletePerson(int id) {
   String DELETE_SQL = "DELETE FROM " + TABLE_NAME + " WHERE " + COLUMN_ID
                   + " = " + id;
   sqliteDataBase.execSQL(DELETE_SQL);
}

同样你也可以：

sqliteDataBase.delete(TABLE_NAME, COLUMN_ID + "=?", new String[]{String.valueOf(id)});

3.修改数据：

//修改数据
public void updatePerson(Person person) {
   String UPDATE_SQL = "UPDATE " + TABLE_NAME + " SET " + COLUMN_NAME
                   + " = " + "'" + person.getName() + "'" + " WHERE " + COLUMN_ID
                   + " = " + person.getId();
   sqliteDataBase.execSQL(UPDATE_SQL);  
}

或者：

   ContentValues cv = new ContentValues();
   cv.put(COLUMN_NAME, person.getName());
   sqliteDataBase.update(TABLE_NAME, cv, COLUMN_ID + "=?", new String[]{String.valueOf(person.getId())});

4.查询单条数据：

//查询单条数据
public ArrayList<Person> findPerson(int sid) {
   ArrayList<Person> personList = new ArrayList<Person>();
   Person person = new Person();
   Cursor cursor = sqliteDataBase.rawQuery("SELECT * FROM " + TABLE_NAME
               + " WHERE " + COLUMN_ID + " = ?", new String[] { String
               .valueOf(sid) });
   while (cursor.moveToNext()) {
      int id = cursor.getInt(cursor.getColumnIndex(COLUMN_ID));
      String name = cursor.getString(cursor.getColumnIndex(COLUMN_NAME));
      int age = cursor.getInt(cursor.getColumnIndex(COLUMN_AGE));
      person.setId(id);
      person.setName(name);
      person.setAge(age);
      personList.add(person);
   }
   cursor.close();
   return personList;
}

5.查询所有数据：

//查询所有数据   
public ArrayList<Person> listPerson() {
   ArrayList<Person> personList = new ArrayList<Person>();
   Cursor cursor = sqliteDataBase.rawQuery("SELECT * FROM " + TABLE_NAME
               + " WHERE " + COLUMN_ID + " IS NOT ?", new String[] { "NULL" });
   while (cursor.moveToNext()) {
      int id = cursor.getInt(cursor.getColumnIndex(COLUMN_ID));
      String name = cursor.getString(cursor.getColumnIndex(COLUMN_NAME));
      int age = cursor.getInt(cursor.getColumnIndex(COLUMN_AGE));
      Person person = new Person();
      person.setId(id);
      person.setName(name);
      person.setAge(age);
      personList.add(person);
   }
   cursor.close();
   return personList;
}

6.附上测试代码：

public class MainActivity extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        //打开或创建sqlite.db数据库
        SQLiteDatabase db = openOrCreateDatabase("sqlite.db",MODE_PRIVATE,null);
        db.execSQL("DROP TABLE IF EXISTS student");

        //创建学生表
        db.execSQL("CREATE TABLE student (_id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR , age SMALLINT)");
        Student student = new Student();
        student.setName("xiaoxing");
        student.setAge(23);
        //插入数据
        db.execSQL("INSERT INTO student values(null,?,?)",new Object[]{student.getName(),student.getAge()});

        student.setName("wangxiaoer");
        student.setAge(24);
        //ContentValues以键值对的形式存放数据
        ContentValues cv = new ContentValues();
        cv.put("name",student.getName());
        cv.put("age",student.getAge());
        //插入contentValue的数据
        db.insert("student",null,cv);

        //修改数据
        cv = new ContentValues();
        cv.put("age",30);
        db.update("student",cv,"name = ?",new String[]{"xiaoming"});

        //查询数据
        Cursor c = db.rawQuery("SELECT * FROM student WHERE age > ?",new String[]{"20"});
        while(c.moveToNext()){

            int _id = c.getInt(c.getColumnIndex("_id"));
            String name = c.getString(c.getColumnIndex("name"));
            int age = c.getInt(c.getColumnIndex("age"));

            Log.i("db","_d=>"+_id+",name=>"+name+",age=>"+age);
        }
        c.close();

       db.delete("student","name=?",new String[]{"wangxiaoer"});
      db.close();
    }
}

执行完成后，系统会默认在你的模拟器/data/data的目录下生成你对应的数据库，效果图：
好了，今天就到这里了，我是张星，欢迎您的关注，后期更精彩。