13. SQLite数据库存储
13.1 创建数据库
class MyDatabaseHelper(val context: Context, name: String, version: Int): SQLiteOpenHelper(context, name, null, version) {
private val createBook = "create table Book(" +
" id integer primary key autoincrement," +
"author text," +
"price real," +
"pages integer," +
"name text)"
override fun onCreate(db: SQLiteDatabase?) {
db?.execSQL(createBook)
Toast.makeText(context, "Create succeeded", Toast.LENGTH_SHORT).show()
}
override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
}
Android提供的SQLiteOpenHelper类，帮助我们非常简单的对数据库进行创建和升级。
val dbHelper = MyDatabaseHelper(this, "BookStore.db", 1)
binding.createDatabaseButton.setOnClickListener {
dbHelper.writableDatabase
}
13.2 升级数据库
比如添加一个表
class MyDatabaseHelper(val context: Context, name: String, version: Int): SQLiteOpenHelper(context, name, null, version) {
private val createBook = "create table Book(" +
" id integer primary key autoincrement," +
"author text," +
"price real," +
"pages integer," +
"name text)"
private val createCategory = "create table Category (" +
"id integer primary key autoincrement," +
"category_name text," +
"category_code integer)"
override fun onCreate(db: SQLiteDatabase?) {
db?.execSQL(createBook)
db?.execSQL(createCategory)
Toast.makeText(context, "Create succeeded", Toast.LENGTH_SHORT).show()
}
override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
db?.execSQL("drop table if exists Book")
db?.execSQL("drop table if exists Category")
onCreate(db)
}
}
调用时，把版本号比原来的高就行，然后dbHelper.writableDatabase后会走到onUpgrade方法中。
val dbHelper = MyDatabaseHelper(this, "BookStore.db", 2)
binding.createDatabaseButton.setOnClickListener {
dbHelper.writableDatabase
}
13.3 添加数据
binding.addDataButton.setOnClickListener {
val db = dbHelper.writableDatabase
val values1 = ContentValues().apply {
put("name", "The Da Vinci Code")
put("author", "Dan Brown")
put("pages", 454)
put("price", 16.96)
}
db.insert("Book", null, values1)
val values2 = ContentValues().apply {
put("name", "The Lost Symbol")
put("author", "Dan Brown")
put("pages", 554)
put("price", 26.96)
}
db.insert("Book", null, values2)
}
CRUD: create、retrieve、update、delete。
SQLiteOpenHelper的readableDatabase和 writableDatabase方法是可以用于创建和升级数据库的，而且两个方法都返回一个SQLiteDatabase对象，用这个对象可以对数据进行CRUD操作。
13.4 更新数据
binding.updateButton.setOnClickListener {
val db = dbHelper.writableDatabase
val values1 = ContentValues()
values1.put("price", 100)
db.update("Book", values1, "name = ?", arrayOf("The Da Vinci Code"))
}
使用SQLiteDatabase的update()方法执行具体的更新操作。
13.5 删除数据
binding.deleteButton.setOnClickListener {
val db = dbHelper.writableDatabase
val value = ContentValues()
db.delete("Book", "pages > ?", arrayOf("500"))
}
13.6 查询数据
query()方法参数的详细解释
| query()方法参数 | 对应SQL部分 | 描述 |
|---|---|---|
| table | from table_name | 指定查询的表名 |
| columns | select column1, column2 | 指定查询的列名 |
| selection | where column = value | 指定where的约束条件 |
| selectionArgs | - | 为where中的占位符提供具体的值 |
| groupBy | group by column | 指定需要group by的列 |
| having | having column = value | 对group by后的结果进一步约束 |
| orderBy | order by column1, column2 | 指定查询结果的排序方式 |
binding.retrieveButton.setOnClickListener {
val db = dbHelper.readableDatabase
val cursor = db.query("Book", null, null, null, null, null, null)
if (cursor.moveToFirst()) {
do {
// 遍历Cursor对象，去除数据并打印
val nameIndex = cursor.getColumnIndex("name")
val name = cursor.getString(nameIndex)
val authorIndex = cursor.getColumnIndex("author")
val author = cursor.getString(authorIndex)
val pagesIndex = cursor.getColumnIndex("pages")
val pages = cursor.getString(pagesIndex)
val priceIndex = cursor.getColumnIndex("price")
val price = cursor.getString(priceIndex)
Log.d("Loong", "name: $name; author: $author; pages: $pages; price: $price")
} while (cursor.moveToNext())
}
cursor.close()
}
13.7 使用SQL操作数据库
binding.execButton.setOnClickListener {
val db = dbHelper.writableDatabase
db.execSQL("insert into Book (name, author, pages, price) values(?, ?, ?, ?)", arrayOf("The Lost Symbol", "Dan Brown", "510", "19.95")
)
}
// 更新数据
db.execSQL("update Book set price = ? where name = ?", arrayOf("10.99", "The Da Vinci Code"))
// 删除数据
db.execSQL("delete from Book where pages > ?", arrayOf("500"))
// 查询数据
val cursor = db.rawQuery("select * from Book", null)
13.8 使用事务
SQLite数据库是支持事务的，事务的特性可以保证让一系列的操作要么全部完成，要么一个都不会完成。
binding.transactionButton.setOnClickListener {
val db = dbHelper.writableDatabase
// 开启事务
db.beginTransaction()
try {
db.delete("Book", null, null)
// if (true) {
// throw NullPointerException()
// }
val values = ContentValues().apply {
put("name", "Game of Thrones")
put("author", "George Martin")
put("pages", 700)
put("price", 22)
}
db.insert("Book", null, values)
// 事务执行成功
db.setTransactionSuccessful()
} catch(e: Exception) {
e.printStackTrace()
} finally {
// 结束事务
db.endTransaction()
}
}
13.9 升级数据库的最佳写法
在onUpgrade中根据数据库版本号执行具体操作
if (oldVersion <= 1) {
db.execSQL(createCategory)
}
if (oldVersion <= 2) {
db.execSQL("alter table Book add column category_id integer")
}
13.10 简化ContentValues的用法
在implementation 'androidx.core:core-ktx:1.8.0'库中，有一个contentValuesOf()方法
// 源码
public fun contentValuesOf(
vararg pairs: Pair<String, Any?>
): ContentValues = ContentValues(pairs.size).apply {
for ((key, value) in pairs) {
when (value) {
null -> putNull(key)
is String -> put(key, value)
is Int -> put(key, value)
is Long -> put(key, value)
is Boolean -> put(key, value)
is Float -> put(key, value)
is Double -> put(key, value)
is ByteArray -> put(key, value)
is Byte -> put(key, value)
is Short -> put(key, value)
else -> {
val valueType = value.javaClass.canonicalName
throw IllegalArgumentException("Illegal value type $valueType for key "$key"")
}
}
}
}
调用的时候就可以简写成
// 原来
val values1 = ContentValues().apply {
put("name", "The Da Vinci Code")
put("author", "Dan Brown")
put("pages", 454)
put("price", 16.96)
}
// 简写
val v1 = contentValuesOf(
"name" to "The Da Vinci Code",
"author" to "Dan Brown",
"pages" to 454,
"price" to 16.96
)
