SQLite的简单操作

本文参考郭霖《第一行代码》第2版

public class DBActivity extends AppCompatActivity {

    private static final String TAG = "DBActivity";
    private MyDatabaseHelper dbHelper;

    @Override
    protected void onCreate(Bundle savedInstance) {
        super.onCreate(savedInstance);
        setContentView(R.layout.activity_db);

        dbHelper = new MyDatabaseHelper(DBActivity.this, "BookStore.db", null, 4);
        //绑定view
        Button createDatabase = findViewById(R.id.create_database);
        Button addData = findViewById(R.id.add_data);
        Button updateData = findViewById(R.id.update_data);
        Button deleteData = findViewById(R.id.delete_data);
        Button queryData = findViewById(R.id.query_data);
        //设置监听器
        createDatabase.setOnClickListener(v -> {
            //创建数据库
            dbHelper.getWritableDatabase();
        });
        //添加数据
        addData.setOnClickListener(v -> {
            SQLiteDatabase db = dbHelper.getWritableDatabase();
            ContentValues values = new ContentValues();
            //开始装第一组数据
            values.put("name", "第一行代码");
            values.put("author", "guolin");
            values.put("pages", 570);
            values.put("price", 30);
            db.insert("Book", null, values);
            values.clear();
            //开始装第二组数据
            values.put("name", "疯狂Android讲义");
            values.put("author", "ligang");
            values.put("pages", 510);
            values.put("price", 50.5);
            db.insert("Book", null, values);
        });
        //更新数据
        updateData.setOnClickListener(v -> {
            SQLiteDatabase db = dbHelper.getWritableDatabase();
            ContentValues values = new ContentValues();
            values.put("price", 9.99);
            db.update("Book", values, "name = ?", new String [] {"第一行代码"});
        });
        //删除数据
        deleteData.setOnClickListener(v -> {
            SQLiteDatabase db = dbHelper.getWritableDatabase();
            db.delete("Book", "pages < ?", new String[]{"550"});
        });
        //查询数据
        queryData.setOnClickListener(v -> {
            SQLiteDatabase db = dbHelper.getWritableDatabase();
            //查询Book表中所有的数据
            Cursor cursor = db.query("Book", null, null, null, null, null, null);
            if (cursor.moveToFirst()){
                do {
                    //遍历Cursor对象，取出数据并打印
                    String name = cursor.getString(cursor.getColumnIndex("name"));
                    String author = cursor.getString(cursor.getColumnIndex("author"));
                    int pages = cursor.getInt(cursor.getColumnIndex("pages"));
                    double price = cursor.getDouble(cursor.getColumnIndex("price"));
                    Log.d(TAG, "book name is " + name);
                    Log.d(TAG, "book author is " + author);
                    Log.d(TAG, "book pages is " + pages);
                    Log.d(TAG, "book price is " + price);
                }while (cursor.moveToNext());
            }
            cursor.close();
        });
    }
}

下面是继承SQLiteOpenHelper的数据库帮助类的源代码：

public class MyDatabaseHelper extends SQLiteOpenHelper {

    public static final String CREATE_BOOK = "create table Book ("
            + "id integer primary key autoincrement, "
            + "author text, "
            + "price real, "
            + "pages integer, "
            + "name text)";

    public static final String CREATE_CATEGORY = "create table Category ("
            + "id integer primary key autoincrement, "
            + "category_name text, "
            + "category_code integer)";

    private Context mContext;

    public MyDatabaseHelper(Context context, String name,
                            SQLiteDatabase.CursorFactory factory, int version){
        super(context, name, factory, version);
        mContext = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_BOOK);
        db.execSQL(CREATE_CATEGORY);
        Toast.makeText(mContext, "Create succeeded", Toast.LENGTH_SHORT).show();
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        db.execSQL("drop table if exists Book");
        db.execSQL("drop table if exists Category");
        onCreate(db);

    }
}

备注：用adb查看db文件：

adb shell 
if （手机root ） 会显示 # else 会显示$ 
如果没有root 执行 su root
cd /data/data/packagename/databases
sqlite3  BookStore.db
.table   //查看所有表
.schema   //查看建表语句

特别注意：查询语句最后需要加“；”

select * from Book;