在android中不使用数组中的值的Sqlite ?

I want to execute a sqlite query:

我想执行一个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);

The values in the in clause need to be taken from an array of strings:

in子句中的值需要从字符串数组中获取:

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

Following is my error log.

下面是我的错误日志。

Caused by: android.database.sqlite.SQLiteException: near "1": syntax error (code 1): , while compiling: SELECT book_id , book_code , ISBN , vol_no , brand , sku , title , price , lang , status FROM books_table WHERE sku IN (Singles, 5 in 1 series, Childrens Book); at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1112) at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:689) at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588) at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:58) at android.database.sqlite.SQLiteQuery.(SQLiteQuery.java:37) at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44) at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1433) at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1372)

引起的:android.database.sqlite。SQLiteException:接近“1”:语法错误(代码1):，编译时:选择book_id、book_code、ISBN、vol_no、brand、sku、title、price、lang、books_table中sku的状态(单打，5个系列，儿童读物);在android.database.sqlite.SQLiteConnection。在android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java . query .databas .database. sqliteit .sqlite. sqlite. SQLiteConnection.java:收购价android. sqlitedirectcursordriver .query(SQLiteDirectCursorDriver.java:44)在android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1433)。

This is the method that I call to get the Json Object.

这是我调用的获取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;
}

Any help or suggestion is appreciated.Thank you.

如有任何帮助或建议，我们将不胜感激。谢谢你！

1 个解决方案

#1

You're missing the quotes around those strings.

你漏掉了这些字符串的引号。

I would build the in clause in a slightly different way though

我将以稍微不同的方式构建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 += ")";

Edit

编辑

To avoid what CL. describes in his comment, you can replace

为了避免CL。在他的评论中，你可以替换。

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

with

与

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

This way if you have single quotes inside your strings they will be escaped and won't mess the final query up.

这样，如果字符串中只有一个引号，它们将被转义，不会打乱最终的查询。

#1