SQLite详解

SQLite是一款轻量级的关系型数据库。无需服务进程，无需安装和配置，支持事务处理。它的运算速度快，占用资源少，适合在存储大量复杂的关系型数据的时候使用。

一、SQLite数据库使用

（1）使用帮助类SQLiteOpenHelper创建数据库

• SQLiteOpenHelper是SQLiteDatabase的帮助类， 用于管理数据库的创建和升级。
• 自定义帮助类继承SQLiteOpenHelper，并重写onCreate()和 onUpgrade()，分别在这两个方法中去实现创建、升级数据库的逻辑。
• 调用帮助类对象的getReadableDatabase() 和getWritableDatabase()去创建或打开一个现有的数据库，并返回一个可对数据库操作的SQLiteDatabase。
• 在onCreate方法中调用SQLiteDatabase的方法execSQL()创建数据库。execSQL()可以接受和处理SQL语句。

public class MySQLiteHelper extends SQLiteOpenHelper {
    public static final String CREATE_MEMBER = "create table member(id integer primary key autoincrement, name text, sex text, age integer)";
    public static final String CREATE_INTEREST = "create table interest(id integer primary key autoincrement,content text)";
    public static final String CREATE_MEMBER_AGE = "alter table member add column age integer";

    public MySQLiteHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        //创建member表
        db.execSQL(CREATE_MEMBER);
        //创建interest表
        db.execSQL(CREATE_INTEREST);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        switch (oldVersion) {
            case 1:
                //升级数据库增加表
                db.execSQL(CREATE_INTEREST);
            case 2:
                //升级数据库增加表中字段age
                db.execSQL(CREATE_MEMBER_AGE);
                break;
            default:
        }
    }
}

（2）操作数据表

    //获取到SQLiteDatabase的实例
    SQLiteOpenHelper dbHelper = new MySQLiteHelper(this, "demo.db", null, 1);
    
    private void insert() {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("name", "黄渤");
        values.put("sex", "男");
        values.put("age", 34);
        db.insert("member", null, values);
        values.clear();

        db.execSQL("insert into member(name,sex,age) values('黄渤','男',34)");
    }

    private void update() {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("name", "岳云鹏");
        db.update("member", values, "id = ?", new String[]{"1"});
        values.clear();

        db.execSQL("update member SET id = '1' WHERE name = '岳云鹏'");
    }

    private void delete() {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        db.delete("member", "name= ?", new String[]{"岳云鹏"});

        db.execSQL("delete from member where name = '岳云鹏'");
    }

    @SuppressLint("Recycle")
    private void rawQuery() {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        //查询member表中所有数据
        Cursor cursor1 = db.query("member", null, null, null, null, null, null);
        //查询member表中年龄大于30的数据
        Cursor cursor2 = db.query("member", null, "age > ?", new String[]{"30"}, null, null, null);

        Cursor cursor3 = db.rawQuery("select * from member", null);

        //从Cursor对象中取出数据
        List<Member> memberList = new ArrayList<Member>();
        if (cursor1 != null && cursor1.moveToFirst()) {
            do {
                int id = cursor1.getInt(cursor1.getColumnIndex("id"));
                String name= cursor1.getString(cursor1.getColumnIndex("name"));
                String sex= cursor1.getString(cursor1.getColumnIndex("sex"));
                int age= cursor1.getInt(cursor1.getColumnIndex("age"));
                Member member = new Member();
                member.setId(id);
                member.setName(name);
                member.setSex(sex);
                member.setAge(age);
                memberList.add(member);
            } while (cursor1.moveToNext());
        }
    }

SQLite源码解析