在android中使用数组中的值时Sqlite IN子句不起作用？

Question

我想执行sqlite查询：

c =  db.rawQuery("SELECT " + KEY_BOOK_ID+ " , " + KEY_BOOK_CODE  + " , " + KEY_ISBN + " , " +
                 KEY_VOL_NO  + " , " + KEY_BRAND  + " , " + KEY_SKU + " , " + KEY_TITLE +
                 " , " + KEY_PRICE + " , " + KEY_LANG + " , " + KEY_STATUS + " FROM " +
                 BOOKS_TABLE + " WHERE " + KEY_SKU + " IN " + inClause + ";", null);

in子句中的值需要取自字符串数组：

String values[] = {"Singles","5 in 1 series","Childrens Book"};

以下是我的错误日志。

原因：Android。数据库sqlite。SQLiteException:接近“1”：语法错误（代码1）：，编译时：从sku所在的books_表中选择book_id、book_code、ISBN、vol_no、brand、sku、title、price、lang、status（单打、5合1系列、儿童图书）；在Android。数据库sqlite。SQLiteConnection。android上的nativePrepareStatement（本机方法）。数据库sqlite。SQLiteConnection。android上的acquirePreparedStatement（SQLiteConnection.java:1112）。数据库sqlite。SQLiteConnection。在android上准备（SQLiteConnection.java:689）。数据库sqlite。SQLiteSession。在android上准备（SQLiteSession.java:588）。数据库sqlite。Sqlite程序。（SQLiteProgram.java:58）在android上。数据库sqlite。SQLiteQuery。（SQLiteQuery.java:37）在android上。数据库sqlite。SQLiteDirectCursorDriver。android上的查询（SQLiteDirectCursorDriver.java:44）。数据库sqlite。SQLITE数据库。android上的rawQueryWithFactory（SQLiteDatabase.java:1433）。数据库sqlite。SQLITE数据库。rawQuery（SQLiteDatabase.java:1372）

这是我用来获取Json对象的方法。

public JSONObject searchForBooks( )
{
    String values[] = {"Singles","5 in 1 series","Childrens Book"};
    String inClause = Arrays.toString(values);

    //replace the brackets with parentheses
    inClause = inClause.replace("[","(");
    inClause = inClause.replace("]",")");

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor c ;
    c =  db.rawQuery("SELECT " +KEY_BOOK_ID+ " , " + KEY_BOOK_CODE  + " , " + KEY_ISBN +
                " , " + KEY_VOL_NO  + " , " + KEY_BRAND  + " , " + KEY_SKU + " , " +
                KEY_TITLE + " , " + KEY_PRICE+ " , " + KEY_LANG+ " , " + KEY_STATUS
                + " FROM " + BOOKS_TABLE + " WHERE " + KEY_SKU + " IN " + inClause + ";",
                null);

    if (c.moveToFirst()) {
        do {
            JSONObject jobj = new JSONObject();
            try {
                jobj.put("book_id", c.getString(0));
                jobj.put("book_code", c.getString(1));
                jobj.put("ISBN", c.getString(2));
                jobj.put("vol_no", c.getString(3));
                jobj.put("brand", c.getString(4));
                jobj.put("sku", c.getString(5));
                jobj.put("title", c.getString(6));
                jobj.put("price", c.getString(7));
                jobj.put("lang", c.getString(8));
                jobj.put("status", c.getString(9));

                jarr1.put(0, jobj);
                jm1.put("books_info", jarr1);

                Log.d("Selected BOOKS info", jm1.toString());

            } catch (JSONException e) {
                e.printStackTrace();
            }
        } while (c.moveToNext());
    }
    c.close();
    db.close();

    return jm1;
}

任何帮助或建议都将不胜感激。非常感谢。

Answer 1

你错过了那些字符串周围的引号。

不过，我会用稍微不同的方式构建in从句

String values[] = {"Singles","5 in 1 series","Childrens Book"};
boolean first = true;
String inClause = "(";

for(String v : values){
    if(first){
        first = false;
    } else {
        inClause += ","
    }
    inClause += "'" + v + "'";
}
inClause += ")";

编辑

为了避免CL.在评论中描述的内容，您可以替换

inClause += "'" + v.replaceAll("'", "''") + "'";

与

inClause += "'" + v + "'";

这样，如果字符串中有单引号，它们将被转义，并且不会扰乱最终的查询。