我正在使用Kotlin构建和Android应用。它使用sqlite
我需要从表中选择数据，查询条件取决于方法参数
就像
fun getData(a:String, b:String, c:String): Array<String> {
val db = readableDatabase
var selectALLQuery = "SELECT * FROM $TABLE "
var conditions:ArrayList<String> = arrayListOf()
var arguments: // WHAT should be type?
if (a != "") {
// add a to the query with WHERE
conditions.add("a = ?")
// add a value to arguments. how?
}
if (b != "") {
// add b to the query with WHERE
conditions.add("b = ?")
// add b value to arguments. how?
}
if (c != "") {
// add c to the query with WHERE
conditions.add("c = ?")
// add b value to arguments. how?
}
if (conditions.count() > 0) {
selectALLQuery += " WHERE "+conditions.joinToString(" AND ")
}
val cursor = db.rawQuery(selectALLQuery, arguments)
}
这是好方法吗？有更好的方法？如果这是corect方式，则应该是参数的类型？
update
我找到了跳过SQL模板使用的解决方案，并且只需完全构建一个查询，并且使用databaseutils.sqlescapestring引用文本字段。
也许这会有所帮助：
fun buildQuery(mainQuery: String, vararg args: Pair<String, Any>): String {
return mainQuery + if (args.isNotEmpty()) {
args.map {
"${it.first} = ${it.second}"
}.joinToString(" AND ")
} else {
""
}
}
然后呼叫是：
val query = buildQuery("SELECT * FROM PERSON ", "a" to "Tim", "b" to 77)
println(query)
输出为：
SELECT * FROM PERSON a = Tim AND b = 77
var参数：//应该类型什么？
RAWQUERY方法期望一个数组。
我会使用
fun getDataV2(a: String, b: String, c: String): ArrayList<String> {
val rv = ArrayList<String>() //Return value
var sql = "SELECT * FROM $TABLE " //Core SQL ro always be used
val values = arrayOf(a, b, c) //Convert inputs to an array
val columns = arrayOf("a", "b", "c") //Likewise for the columns
val arguments = ArrayList<String>() //Initially 0 arguments
val whereclause = StringBuilder() //Initially empty whereclause
var after_first = false //Flag to indicate whether the first arg has been added
for (i in values.indices) { //Loop through values
if (values[i].length > 0) { //Is there a value?
if (after_first) { //Is this not the first value
whereclause.append(" AND ") //If it is not the first value add the ADD keyword
}
whereclause.append(columns[i]).append("=?") //if there is a value then add the expression
arguments.add(values[i]) // and then add the value to the arguments ArrayList
after_first = true // change flag to indicate that a value has been processed
}
}
// Add the WHERE keyword and the where clause if needed
if (whereclause.isNotEmpty()) {
sql = "$sql WHERE $whereclause"
}
//Prepare to run the rawQuery
val db = this.writableDatabase
//Run the rawQuery
val csr = db.rawQuery(sql, arguments.toTypedArray())
Log.d("GETDATAV2",sql + " Argcount =" + arguments.size) //TODO for testing, REMOVE before publising
//Populate the ArrayList to be returned from the Cursor
while (csr.moveToNext()) {
rv.add(csr.getString(csr.getColumnIndex(COLNAME))) //<<<<<<<<<< COLNAME assumed for testing
}
//CLose the Cursor
csr.close()
//Finally return the ArrayList
return rv
}
测试
在活动中使用以下内容来测试许多预告： -
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
val dbHelper = DBHelperKotlin(this)
dbHelper.writableDatabase.delete(DBHelper.TABLE,null,null); //Delete any existing rows
// Add the testing data
dbHelper.add("Test1", "a", "b", "c")
dbHelper.add("Test2", "b", "c", "d")
dbHelper.add("Test3", "c", "d", "e")
dbHelper.add("Test4", "d", "e", "f")
//testing against older method removed (hence starting with 3)
val result3 = dbHelper.getDataV2("","","");
for (i in result3.indices) {
Log.d("TEST1V2RESULT",result3.get(i))
}
val result4 = dbHelper.getDataV2("","b","");
for (i in result4.indices) {
Log.d("TEST4V2RESULT",result4.get(i))
}
val result5 = dbHelper.getDataV2("a","b","c");
for (i in result5.indices) {
Log.d("TEST5V2RESULT",result5.get(i))
}
val result6 = dbHelper.getDataV2("a","","");
for (i in result6.indices) {
Log.d("TEST6V2RESULT",result6.get(i))
}
val result7 = dbHelper.getDataV2("","","c");
for (i in result7.indices) {
Log.d("TEST7V2RESULT",result7.get(i))
}
val result8 = dbHelper.getDataV2("a","","c");
for (i in result8.indices) {
Log.d("TEST8V2RESULT",result8.get(i))
}
}
导致
05-26 12:23:36.452 3593-3593/? D/GETDATAV2: SELECT * FROM mytable Argcount =0
05-26 12:23:36.452 3593-3593/? D/TEST1V2RESULT: Test1
05-26 12:23:36.452 3593-3593/? D/TEST1V2RESULT: Test2
05-26 12:23:36.452 3593-3593/? D/TEST1V2RESULT: Test3
05-26 12:23:36.452 3593-3593/? D/TEST1V2RESULT: Test4
05-26 12:23:36.452 3593-3593/? D/GETDATAV2: SELECT * FROM mytable WHERE b=? Argcount =1
05-26 12:23:36.453 3593-3593/? D/TEST4V2RESULT: Test1
05-26 12:23:36.453 3593-3593/? D/GETDATAV2: SELECT * FROM mytable WHERE a=? AND b=? AND c=? Argcount =3
05-26 12:23:36.454 3593-3593/? D/TEST5V2RESULT: Test1
05-26 12:23:36.455 3593-3593/? D/GETDATAV2: SELECT * FROM mytable WHERE a=? Argcount =1
05-26 12:23:36.455 3593-3593/? D/TEST6V2RESULT: Test1
05-26 12:23:36.455 3593-3593/? D/GETDATAV2: SELECT * FROM mytable WHERE c=? Argcount =1
05-26 12:23:36.456 3593-3593/? D/TEST7V2RESULT: Test1
05-26 12:23:36.456 3593-3593/? D/GETDATAV2: SELECT * FROM mytable WHERE a=? AND c=? Argcount =2
05-26 12:23:36.458 3593-3593/? D/TEST8V2RESULT: Test1
但是
使用简单的修改，上述可以更灵活，并适合任何表上的任何数量的列(假设列存在在指定的表中(，请按照： -
fun getDataV3(table: String, args: Array<String>, columns: Array<String>) : ArrayList<String> {
val rv = ArrayList<String>() //Return value
var sql = "SELECT * FROM $table " //Core SQL ro always be used
if (args.size != columns.size) {
// handle mismatch between columns and args??????
return rv
}
val arguments = ArrayList<String>() //Initially 0 arguments
val whereclause = StringBuilder() //Initially empty whereclause
var after_first = false //Flag to indicate whether the first arg has been added
for (i in args.indices) { //Loop through values
if (args[i].length > 0) { //Is there a value?
if (after_first) { //Is this not the first value
whereclause.append(" AND ") //If it is not the first value add the ADD keyword
}
whereclause.append(columns[i]).append("=?") //if there is a value then add the expression
arguments.add(args[i]) // and then add the value to the arguments ArrayList
after_first = true // change flag to indicate that a value has been processed
}
}
// Add the WHERE keyword and the where clause if needed
if (whereclause.isNotEmpty()) {
sql = "$sql WHERE $whereclause"
}
//Prepare to run the rawQuery
val db = this.writableDatabase
//Run the rawQuery
val csr = db.rawQuery(sql, arguments.toTypedArray())
Log.d("GETDATAV3",sql + " Argcount =" + arguments.size) //TODO for testing, REMOVE before publising
//Populate the ArrayList to be returned from the Cursor
while (csr.moveToNext()) {
rv.add(csr.getString(csr.getColumnIndex(COLNAME)))
}
//CLose the Cursor
csr.close()
//Finally return the ArrayList
return rv
}
然后可以像以下方式调用： -
val result10 = dbHelper.getDataV3(DBHelperKotlin.TABLE,arrayOf("","",""), arrayOf(DBHelperKotlin.COLA,DBHelperKotlin.COLB,DBHelperKotlin.COLC))
for (i in result10.indices) {
Log.d("TEST10V2RESULT",result10.get(i))
}
val result11 = dbHelper.getDataV3(DBHelperKotlin.TABLE,arrayOf("","b",""), arrayOf(DBHelperKotlin.COLA,DBHelperKotlin.COLB,DBHelperKotlin.COLC))
for (i in result11.indices) {
Log.d("TEST11V2RESULT",result11.get(i))
}
上述结果为： -
05-26 12:52:24.444 3960-3960/aso.so56298529querywithmultileargs D/GETDATAV3: SELECT * FROM mytable Argcount =0
05-26 12:52:24.444 3960-3960/aso.so56298529querywithmultileargs D/TEST10V2RESULT: Test1
05-26 12:52:24.444 3960-3960/aso.so56298529querywithmultileargs D/TEST10V2RESULT: Test2
05-26 12:52:24.444 3960-3960/aso.so56298529querywithmultileargs D/TEST10V2RESULT: Test3
05-26 12:52:24.444 3960-3960/aso.so56298529querywithmultileargs D/TEST10V2RESULT: Test4
05-26 12:52:24.444 3960-3960/aso.so56298529querywithmultileargs D/GETDATAV3: SELECT * FROM mytable WHERE b=? Argcount =1
05-26 12:52:24.444 3960-3960/aso.so56298529querywithmultileargs D/TEST11V2RESULT: Test1
a， b， c都是 String s。
因此，您可以将arguments键入String的列表。
val arguments = mutableListOf<String>()
原因我使用MutableList而不是Array是动态添加项目。
在您的if语句中，添加条件后，您可以将项目添加到arguments，例如
arguments.add(a)
最终在db.rawQuery()中，您可以使用toTypedArray。
db.rawQuery(selectALLQuery, arguments.toTypedArray())
anko
在Kotlin中，您可以使用 anko 。
简化DB连接和查询https://github.com/kotlin/anko/wiki/anko-sqlite
检查查询数据部分。