"kotlin" sqlite query for one record

Question

We can not believe we are asking this question!
How do we query a SQLite database for one record?

Say we want the id but only know the name in table

Here is the code that makes the call to the DB

        btnGetID.setOnClickListener {
        val dbManager = DBHelper(this)
        val name = etPerson.text.toString()
        dbManager.getOneName(name)
        println("##################### where is return"+empName)
    }

And here is the DB fun getOneName

    fun getOneName(name: String): String {

    val db = this.writableDatabase
    val selectQuery = "SELECT  * FROM $TABLE_NAME WHERE $colName = name"
    // val selectQuery = "SELECT  * FROM $TABLE_NAME WHERE $colId = id"
    val cursor = db.rawQuery(selectQuery, null)
    var empName = ""
    //var empID = 0

        if (cursor.getCount() > 0) {
            cursor.moveToFirst()
            empName = cursor.getString(cursor.getColumnIndex(colName))
            //empID = cursor.getInt(cursor.getColumnIndex(colId))
        }
        cursor.close()
        println("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ ID "+empName)
        return empName
}

This DB has a Model and the app has an Adapter

class Contact{

var id: Int = 0
var name: String = ""

}

We would like to enter the name and retrieve the id
We can NOT even retrieve the name.
We have done this multiple times in Java but no luck with Kotlin

zapl · Accepted Answer · 2018-09-30T21:08:33.157

3

For example like

fun getOneName(name: String): Contact? {
    val db = this.writableDatabase
    val selectQuery = "SELECT  * FROM $TABLE_NAME WHERE $colName = ?"
    db.rawQuery(selectQuery, arrayOf(name)).use { // .use requires API 16
        if (it.moveToFirst()) {
            val result = Contact()
            result.id = it.getInt(it.getColumnIndex(colId))
            result.name = it.getString(it.getColumnIndex(colName))
            return result
        }
    }
    return null
}

and use like

btnGetID.setOnClickListener {
    val dbManager = DBHelper(this)
    val name = etPerson.text.toString()
    val contact = dbManager.getOneName(name)
    println("##################### where is return"+contact?.name)
}

$TABLE_NAME and $colName in "SELECT * FROM $TABLE_NAME WHERE $colName = ?" get replaced before the call and becomes a regular string like "SELECT * FROM persons WHERE name = ?".

Then db.rawQuery runs and the second parameter is used to provide your actual query parameters.

selectionArgs – You may include ?s in where clause in the query, which will be replaced by the values from selectionArgs. The values will be bound as Strings.

Don't do "SELECT * FROM $TABLE_NAME WHERE $colName = '$name'" or little Bobby comes to bite you

edited Sep 30 '18 at 21:08

answered Sep 30 '18 at 20:46

zapl

63,179
10
123
154

This is great I entered name and would like to get back the id ? This would have taken me hours and believe me little Bobby soon to be bookmarked bit me more than once – Vector Sep 30 '18 at 21:27
DUH etPerson.setText(contact?.id.toString()) – Vector Sep 30 '18 at 21:29
Where did ".use" come from never seen it AND the convert Java to Kotlin must have never seen it As I tried to convert old Java code to Kotlin WORD to the wise this concept of Java to Kotlin gives strange results – Vector Sep 30 '18 at 21:33
@Grendel `.use` is a Kotlin [extension](https://kotlinlang.org/docs/reference/extensions.html) function added to `Closable`. It's essentially the try-with-resources of kotlin: https://stackoverflow.com/questions/26969800/try-with-resources-in-kotlin - But: `Cursor` didn't implement `Closable` until api level 16, so it would likely crash when run on an older phone. But you can add that extension function into your own code too: https://discuss.kotlinlang.org/t/why-cursor-use-does-not-compiles/5454/4 – zapl Sep 30 '18 at 21:43

"kotlin" sqlite query for one record

1 Answers1