Sqlite的使用详解

来源：互联网发布：csp绘画软件模型编辑：程序博客网时间：2024/06/04 18:57

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,SQLiteDatabasepublic 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的目录下生成你对应的数据库，效果图：

好了，今天就到这里了，我是张星，欢迎您的关注，后期更精彩。

2 0