Android操作sqlite数据库

Sqlite数一种轻量级的关系型数据库，android里面可以用来持久化存储一些用户数据。

一、SQLiteOpenHelper方式

SQLiteOpenHelper是原生的数据库帮助类，继承这个类，用来创建，更新数据库的操作

public class MySqliteOpenHelper extends SQLiteOpenHelper {
    private static final String TAG = MySqliteOpenHelper.class.getSimpleName();
    private static final String DBNAME = "test_sqlite.db";
    private static final int DBVERSION = 1;

    /**
     * @param context 上下文
     *                name    数据库名称
     *                factory 游标工厂
     *                version 版本号
     */
    public MySqliteOpenHelper(@Nullable Context context) {
        super(context, DBNAME, null, DBVERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.i(TAG, "创建数据库时的回调");
        String sql = "create table student(_id integer,name varchar,age integer,salary integer)";
        db.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.i(TAG, "升级数据库时的回调");
        String updateSql = "";
        switch (oldVersion) {
            case 1:
                updateSql = "alter table student add sex integer";
                break;
            case 2:
                updateSql= "alter table student add address varchar";
                break;
            case 3:
                break;
        }
        db.execSQL(updateSql);
    }
}

创建一个DAO,封装操作具体数据表操作，插入，删除等等

public class Dao {
    private static final String TAG = Dao.class.getSimpleName();
    private MySqliteOpenHelper mHelper;

    public Dao(Context context) {
        mHelper = new MySqliteOpenHelper(context);
    }

    public void insert() {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        String sql = "insert into student(_id,name,age,salary) values(?,?,?,?)";
        db.execSQL(sql, new Object[]{1, "Tony", 17, 100000});
        db.close();
    }

    public void delete() {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        String sql = "delete from student where _id = 1";
        db.execSQL(sql);
        db.close();
    }

    public void update() {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        String sql = "update student set age = 23 where _id = 1";
        db.execSQL(sql);
        db.close();
    }

    public void query() {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        String sql = "select * from  student ";
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            Log.e(TAG, "_id :" + cursor.getString((int) cursor.getColumnIndex("_id")) +
                    ", name : " + cursor.getString((int) cursor.getColumnIndex("name")) +
                    ", age :" + cursor.getString((int) cursor.getColumnIndex("age")) +
                    ", salary :" + cursor.getString((int) cursor.getColumnIndex("salary")));
        }
        db.close();
    }
}

最后在界面上添加几个按钮，用来测试添加删除等等操作

public class SqliteActivity extends AppCompatActivity implements View.OnClickListener {
    private static final String TAG = SqliteActivity.class.getSimpleName();
    private Button mButtonCreateDb;
    private MySqliteOpenHelper mMySqliteOpenHelper;

    @Override
    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.sqlite_test);
        initView();
    }

    private void initView() {
        mButtonCreateDb = findViewById(R.id.create_db_id);
        mButtonCreateDb.setOnClickListener(this);
    }

    @Override
    public void onClick(View v) {
        switch (v.getId()) {
            case R.id.create_db_id:
                createDb();
        }
    }

    // 创建数据库
    private void createDb() {
        mMySqliteOpenHelper = new MySqliteOpenHelper(this);
        mMySqliteOpenHelper.getWritableDatabase();
    }

}

layout.xml页面

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical">

    <Button
        android:id="@+id/create_db_id"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="创建数据库"/>

    <Button
        android:id="@+id/add_db_id"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="增"/>

    <Button
        android:id="@+id/delete_db_id"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="删"/>

    <Button
        android:id="@+id/update_db_id"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="改"/>

    <Button
        android:id="@+id/query_db_id"
        android:layout_wid