时间戳

首页 > 解决方案 > Android - 使用arraylist中的字符串值的sqlite in子句？

java - Android - 使用arraylist中的字符串值的sqlite in子句？

问题描述

public void DBSearchCategory(String tableName) {
    // 1st way 
    String inClause = s1.ListViewCategory.toString();
    inClause = inClause.replace("[", "(");
    inClause = inClause.replace("]", ")");

   //        Cursor cursor = database.rawQuery("SELECT CATEGORY FROM " + tableName
  //                + " WHERE CATEGORY NOT IN " + inClause
 //                + " ORDER BY RANDOM() LIMIT 1 ", null);

    // 2nd way
    try {
        StringBuilder sb = new StringBuilder("");
        for (String param : s1.ListViewCategory) {
            sb.append(",").append('"').append(param).append('"');
        }
        params = sb.toString().substring(1); 
        Log.v("Tag", "params value is " + params);
    } catch (StringIndexOutOfBoundsException e) {

    }
    Cursor cursor = database.rawQuery("SELECT CATEGORY FROM " + tableName
                    + " WHERE CATEGORY NOT IN (?) "
                    + " ORDER BY RANDOM() LIMIT 1 ", new String[]{params});

    while (cursor.moveToNext()) {
        category = cursor.getString(cursor.getColumnIndex("CATEGORY"));
        s1.keyCategory = category;
    }
    cursor.close();
}

  1. s1.ListViewCategory 是 Singleton 类 s1 中的 String 类型的 ArrayList，它有 category 的值："game","country","city","subway","actor","pet" // 数据库里有总数33 个类别，我想排除 s1.ListViewCategory 中的这 6 个类别

  2. 在 rawQuery 中，我想排除 s1.ListViewCategory 中的类别，所以我尝试了 2 种游标方式来引用这 2 个 stackoverflow 问题： Android - sqlite in 子句使用数组中的字符串值？ /// Android - sqlite in 子句使用数组中的值

我使用 WHERE 和 NOT IN 语句排除这 6 个类别

  1. 当我尝试第二种方式光标时，我没有收到错误。但是，Sql 查询不起作用。它必须排除 String[params] 中的类别，但它不起作用。所以我用日志来查看参数是什么，我得到了这个

    2020-01-09 09:16:47.233 8978-8978/com.kj.word V/Tag: 参数值为“游戏”、“国家”、“城市”、“地铁”、“演员”、“宠物”

  2. 当我尝试第一个光标类别时，我得到了错误 logcat：

    Error Code : 1 (SQLITE_ERROR)
Caused By : SQL(query) error or missing database.
(no such column: game (code 1): , while compiling: SELECT CATEGORY FROM KeyWordDB WHERE CATEGORY 
NOT IN (game, country, city, subway, actor, pet) ORDER BY RANDOM() LIMIT 1)
#################################################################
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1008)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:573)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:59)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
at android.database.sqlite.SQLite

我确认有数据库，所以我猜可能是sql查询问题...

  1. 所以我的问题是如何修复第一个或第二个光标以排除 s1.ListViewCateogry 中的类别？

我已经很努力地搜索了，但我找不到答案......如果有人回答这个问题，我将不胜感激

标签： javaandroidsqlitearraylistandroid-sqlite

解决方案

在构造逗号分隔列表的循环内用单引号更改双引号：

for (String param : s1.ListViewCategory) {
    sb.append(",").append("'").append(param).append("'");
}
params = sb.toString().substring(1);

This code constructs a list like:

'game', 'country', 'city', 'subway', 'actor', 'pet'

If you use it as a parameter in the rawQuery() method then this list will be treated as a string literal and not a list of values.
So do this instead:

String sql = "SELECT CATEGORY FROM " + tableName
             + " WHERE CATEGORY NOT IN (?) "
             + " ORDER BY RANDOM() LIMIT 1 ";
sql = sql.replace("?", params);
Cursor cursor = database.rawQuery(sql, null);

Note that this method is prone to sql injection.

Another way is to create a list of ? placeholders instead of 1 placeholder and pass the list of values as an array of strings like this:

for (String param : s1.ListViewCategory) {
    sb.append(",?");
}
String[] array = ListViewCategory.toArray(new String[s1.ListViewCategory.size()]);

params = sb.toString().substring(1);
String sql = "SELECT CATEGORY FROM " + tableName
           + " WHERE CATEGORY NOT IN (@) "
           + " ORDER BY RANDOM() LIMIT 1 ";
sql = sql.replace("@", params);

Cursor cursor = database.rawQuery(sql, array);

推荐阅读