在android-SQLite中无法成功查询

Question

我在学习安卓工作室。作为图书管理应用程序，我编写了这个MainActivity.kt和SimpleDatabaseHelper.kt

SimpleDatabaseHelper类在SimpleDatabaseHelper.kt中

class SimpleDatabaseHelper (context: Context?): SQLiteOpenHelper(context, DBNAME,null, VERSION{      
    companion object{
    private const val  VERSION=1 
    private const val  DBNAME="sample.sqlite" 
    }
    override fun onCreate(db: SQLiteDatabase?) {
        db?.let{
            it.execSQL("CREATE TABLE books("+"isbn TEXT PRIMARY KEY,title TEXT,price INTEGER)")
            val initialBookData=listOf(
                mapOf("isbn" to "00001-0001","title" to "11111111","price" to "1234"),
                mapOf("isbn" to "00002-0002","title" to "2222222","price" to "3000"),
                mapOf("isbn" to "00003-0003","title" to "333333","price" to "3500"),
                mapOf("isbn" to "00004-0004","title" to "4444444","price" to "600"),
                mapOf("isbn" to "00005-0005","title" to "55555555","price" to "1000"),
                mapOf("isbn" to "00006-0006","title" to "66666","price" to "1111")
            )
            it.beginTransaction()
            try{
                val sql=it.compileStatement(
                    "INSERT INTO books(isbn,title,price) VALUES(?,?,?)"
                )
                initialBookData.forEach{
                    sql.bindString(1, it["isbn"] )
                    sql.bindString(2, it["title"] )
                    sql.bindString(3, it["price"])
                    sql.executeInsert()
                }
                it.setTransactionSuccessful()
            }catch(e:SQLException){
                e.printStackTrace()
            }finally {
                it.endTransaction()
            }
        }
    }
    
    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        db?.let{
            it.execSQL("DROP TABLE IF EXISTS books")
            onCreate(it)
        }
    }
    
    override fun onOpen(db: SQLiteDatabase?) {
        super.onOpen(db)
    }
}

MainActivity类在MainActivity.kt中

class MainActivity : AppCompatActivity() {
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main4)
        val txtSearch = findViewById<EditText>(R.id.textSearch)
        val txtSearchTitle = findViewById<TextView>(R.id.textSearchTitle)
        val txtSearchAnswer = findViewById<TextView>(R.id.textSearchAnswer)
        val btnS = findViewById<Button>(R.id.buttonToSearch)
        val btnD = findViewById<Button>(R.id.buttonToDelete)
        val btnI = findViewById<Button>(R.id.buttonToInsert)
        val helper = SimpleDatabaseHelper(this)
    
        btnI.setOnClickListener {
            helper.writableDatabase.use { db ->
                val cv = ContentValues().apply {
                    put("isbn", "new_isbn")
                    put("title", txtSearch.text.toString())
                    put("price", "10000")
                }
                db.insertWithOnConflict("books", null, cv, SQLiteDatabase.CONFLICT_REPLACE)
                Toast.makeText(this, "Inserted", Toast.LENGTH_SHORT).show()
            }
        }
        btnD.setOnClickListener {
            helper.writableDatabase.use { db ->
                val params = arrayOf(txtSearch.text.toString())
                db.delete("books", "isbn= ?", params)
                Toast.makeText(this, "Deleted", Toast.LENGTH_SHORT).show()
    
            }
        }
        btnS.setOnClickListener {
            val cols = arrayOf("isbn", "title", "price")
            val params = arrayOf(txtSearch.text.toString())
            helper.readableDatabase.use { db ->
                db.query(
                    "books", cols, "isbn = ?",
                    params, null, null, null, null
                ).use { cs ->
                    if (cs.moveToFirst()) {
                        txtSearchTitle.setText(cs.getString(1))
                        txtSearchTitle.setText(cs.getString(2))
                    } else {
                        Toast.makeText(this, "No Book Found", Toast.LENGTH_SHORT).show()
                    }
                }
            }
        }
        helper.writableDatabase.use { db ->
            Log.d("DATABASE", "connected to SearchDatabase")
        }
    }
}

isbn搜索时，应从数据库中显示标题和价格。

                mapOf("isbn" to "00001-0001","title" to "11111111","price" to "1234"),
                mapOf("isbn" to "00002-0002","title" to "2222222","price" to "3000"),
                mapOf("isbn" to "00003-0003","title" to "333333","price" to "3500"),
                mapOf("isbn" to "00004-0004","title" to "4444444","price" to "600"),
                mapOf("isbn" to "00005-0005","title" to "55555555","price" to "1000"),
                mapOf("isbn" to "00006-0006","title" to "66666","price" to "1111")

但是当我搜索"00001-0001“时，没有找到一本书。我怎么解决这个问题？

检查数据库检查器，似乎数据库创建不正确。

我怎么解决这个问题？

Answer 1

最可能的原因是您运行了该应用程序，并且在onCreate方法中出现了一个问题，您修复了该问题，然后重新运行，然后遇到了数据/行未被找到的问题。

这可能是因为当调用/调用onCreate方法时，数据库当时已经创建，但它没有其他系统表(sqlite_master和android_metadata)。

在您的示例中，由于错误不会成功地设置事务，那么事务将被回滚，所以即使创建了表，它的创建也会回滚，因此表就不存在了。

由于数据库本身的存在，任何后续的运行都不会调用onCreate方法，因此appear可能看起来是“数据库创建不正确”。

在新项目中实现了这些代码，不知何故我获得了成功。

这将工作，因为初始问题已得到纠正，并由于数据库不存在的新项目，然后onCreate将被调用，并做什么是预期的。

为了修复旧的项目，

1. 卸载应用程序，这将导致数据库被删除，
2. 将重新运行该应用程序，并将安装该应用程序，并调用onCreate。