Android Studio中Kotlin操作SQLite写入数据库崩溃求助

问题核心原因 #

你的应用调用writeToDb时崩溃，实际是多个问题共同导致的，以下是关键问题点：

1. SQL语法错误（易被误判为写入崩溃） #

getFromDb方法中的查询语句缺少FROM关键字，字段名大小写与表定义不匹配，且直接拼接字符串存在SQL注入风险：

val result = db.rawQuery("SELECT * dbPatients WHERE Surname = '$surname'", null)

2. 数据类型不匹配 #

数据库表中amount字段定义为TEXT，但Patient类的Amount是Int类型，写入时类型不匹配会触发异常。

3. 空字段未终止后续逻辑 #

主活动中，字段为空时仅弹出Toast，但未终止后续代码执行，依然会创建Patient并尝试写入数据库，引发异常。

4. 数据库连接管理不当 #

每次操作后手动调用db.close()，SQLiteOpenHelper会自动管理连接池，频繁关闭/打开会导致连接异常。

修复后的代码 #

1. 数据库类（DBHelperPatients） #

class DBHelperPatients(context: Context, factory: SQLiteDatabase.CursorFactory?) :
    SQLiteOpenHelper(context, "dbPatients", factory, 1) {

    override fun onCreate(db: SQLiteDatabase?) {
        // 修改amount字段类型为INTEGER，匹配Patient类的Int类型
        val query = ("CREATE TABLE " + "dbPatients" + " ("
                + "id" + " INTEGER PRIMARY KEY AUTOINCREMENT, " + // 自动生成主键
                "name" + " TEXT," +
                "surname" + " TEXT," +
                "fathername" + " TEXT," +
                "birthday" + " TEXT," +
                "phonenumber" + " TEXT," +
                "amount" + " INTEGER" + ")")
        db!!.execSQL(query)
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        db!!.execSQL("DROP TABLE IF EXISTS dbPatients")
        onCreate(db)
    }

    fun writeToDb(patient: Patient): Boolean {
        val values = ContentValues()

        values.put("name", patient.Name)
        values.put("surname", patient.Surname)
        values.put("fathername", patient.FatherName)
        values.put("birthday", patient.Birthday)
        values.put("phonenumber", patient.PhoneNumber)
        values.put("amount", patient.Amount)

        val db = this.writableDatabase
        val result = db.insert("dbPatients", null, values)
        // 由SQLiteOpenHelper自动管理连接，无需手动关闭
        return result != -1L
    }

    @SuppressLint("Recycle")
    fun getFromDb(surname: String): Boolean {
        val db = readableDatabase
        // 修复SQL语句，使用参数化查询避免注入
        val result = db.rawQuery("SELECT * FROM dbPatients WHERE surname = ?", arrayOf(surname))
        val exists = result.moveToFirst()
        result.close() // 仅关闭Cursor即可
        return exists
    }
}

2. 主活动（AddPatientActivity） #

class AddPatientActivity : AppCompatActivity() {
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_add_patient)

        val dbPatients = DBHelperPatients(this, null)

        val nameField = findViewById<EditText>(R.id.newPatientName)
        val surnameField = findViewById<EditText>(R.id.newPatientSurName)
        val fathernameField = findViewById<EditText>(R.id.newPatientFatherName)
        val phonenumberField = findViewById<EditText>(R.id.newPatientPhoneNumber)
        val birthdayField = findViewById<EditText>(R.id.newPatientBirthday)

        val btnConfirm = findViewById<Button>(R.id.btnAddPatient)

        btnConfirm.setOnClickListener{
            val name = nameField.text.toString().trim()
            val surname = surnameField.text.toString().trim()
            val fathername = fathernameField.text.toString().trim()
            val phonenumber = phonenumberField.text.toString().trim()
            val birthday = birthdayField.text.toString().trim()

            if(name.isEmpty()||surname.isEmpty()||fathername.isEmpty()||phonenumber.isEmpty()||birthday.isEmpty()){
                Toast.makeText(this, "Не все поля заполнены!", Toast.LENGTH_LONG).show()
                // 添加return终止后续逻辑
                return@setOnClickListener
            }
            val patient = Patient( name, surname, fathername, birthday, phonenumber, 0)
            Log.w("myapp", "${patient.Name}, ${patient.Surname}, ${patient.FatherName}, ${patient.Birthday}, ${patient.PhoneNumber}, ${patient.Amount}")
            
            val isWritten = dbPatients.writeToDb(patient)
            Log.w("myapp", "写入结果: $isWritten")

            val exists = dbPatients.getFromDb(surname)
            Log.w("myapp", "是否存在该患者: $exists")
        }
    }
}

3. Patient类（保持原代码即可） #

class Patient(val Name:String, val Surname:String, val FatherName:String, val Birthday:String, val PhoneNumber:String, val Amount:Int) {
}

额外建议 #

• 查看Logcat的AndroidRuntime标签下的崩溃日志，能更精准定位问题。
• 建议使用Kotlin空安全语法，避免空指针异常。
• 日期字段（birthday）建议存储为时间戳或标准格式字符串，方便后续查询处理。

内容的提问来源于stack exchange，提问作者RayanUtka