android学习记录(十)

升级数据库

改变传入的数据库版本号就可以触发数据库升级.

val dbHelper=MyDatabaseHelper(this,"BookStore.db",5)

此时会调用onUpgrade方法.
重写onUpgrade方法

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)
    }

这样就会重新调用onCreate方法

override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(createBook)
        db.execSQL(createCategory)
        Toast.makeText(context,"Create succeeded",Toast.LENGTH_SHORT).show()
    }

添加数据

        addData.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",510)
                put("price",19.95)
            }
            db.insert("Book",null,values2)
        }

更新数据

      updateData.setOnClickListener {
            val db=dbHelper.writableDatabase
            val values=ContentValues()
            values.put("price",10.99)
            db.update("Book",values,"name = ?", arrayOf("The Da Vinci Code"))
        }

删除数据

    deleteData.setOnClickListener { 
            val db=dbHelper.writableDatabase
            db.delete("Book","page > ?", arrayOf("500"))
            
        }

简单查询数据

        queryData.setOnClickListener {
            val db=dbHelper.writableDatabase
            val cursor=db.query("Book",null,null,null,null,null,null)
            if(cursor.moveToFirst()){
                do{
                    val name=cursor.getString(cursor.getColumnIndex("name"))
                    val author=cursor.getString(cursor.getColumnIndex("author"))
                    val pages=cursor.getInt(cursor.getColumnIndex("pages"))
                    val price=cursor.getDouble(cursor.getColumnIndex("price"))
                    Log.d("MainActivity","book name is name")
                    Log.d("MainActivity","book author is $author")
                    Log.d("MainActivity","book pages is $pages")
                    Log.d("MainActivity","book price is $price")
                }while (cursor.moveToNext())
            }
            cursor.close()
        }

运行结果

也可以不使用Android 提供的API

			//添加数据
            db.execSQL("insert into Book (name,author,pages,price) values (?,?,?,?)", arrayOf("The Da Vinci Code","Dan Brown","454","16.96"))
            //更新数据
            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)

使用事务

实现删除数据和添加数据的操作必须一起完成.

测试代码

replaceData.setOnClickListener {
            val db=dbHelper.writableDatabase
            db.beginTransaction()
            try {
                db.delete("Book",null,null)
                throw NullPointerException()//手动抛出一个异常进行测试
                val values =ContentValues().apply { 
                    put("name","Game of Thrones")
                    put("author","George Martin")
                    put("pages",720)
                    put("price",20.85)
                }
                db.insert("Book",null,values)
                db.setTransactionSuccessful()

            }catch (e: Exception){
                e.printStackTrace()
            }finally {
                db.endTransaction()
            }

        }

通过手动抛出异常进行测试发现,数据并没有删除也没有更新,也就是说事务出现异常后之前的删除并不会执行. 删除这行测试代码后就可以正常删除和更新了.

升级数据库的最佳写法(保留升级之前的数据)

需求一(添加一张新的表)

package com.example.databasetest

import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.widget.Toast

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) {
       if (oldVersion<=5){
           db.execSQL(createCategory)
       }
    }
}//MyDatabaseHelper 继承抽象类SQLiteOpenHelper,并且重写其中的两个抽象方法

如果用户第一次安装,那么会直接调用Oncreate方法,由于之前没有数据,于是在这里直接新建两张表.
如果用户不是第一次安装,那么执行更新操作,调用onUpgrade方法.此时判断其版本,然后添加数据表
需求二 在Book表中添加一个category_id字段
修改建表语句, 将该版本需要的操作添加到onUpgrade中,而对于新用户只要通过Oncreate实现最新版本即可.

package com.example.databasetest

import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.widget.Toast

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,"+"category_code integer)"
    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) {
       if (oldVersion<=5){
           db.execSQL(createCategory)
       }
        if (oldVersion <=6){
            db.execSQL("alter table Book add column category_id integer")
        }
    }
}//MyDatabaseHelper 继承抽象类SQLiteOpenHelper,并且重写其中的两个抽象方法