Android Studio 将 Android SQLite 查询标记为错误 [英] Android SQLite queries marked as Error by Android Studio

问题描述

在我的一个 Android 应用程序中，我使用了一些 SQLite 查询，自从我更新到 Android Studio 3.0 以来，这些查询开始出现问题.

In one of my Android apps I use some SQLite queries that are starting to give problems since I've updated to Android Studio 3.0.

尽管我可以编译并运行应用程序，但它们被标记为错误.

Despite I can compile and run the app they are marked as error.

这些是查询.

 db.execSQL("INSERT INTO table1 (…) ... substr(replace(table2.field2, ',', ''), table1.field1, 1) … FROM table1 … WHERE …");

在替换"中给出错误:

')' 或预期的表达式，得到 'replace'

')' or expression expected, got 'replace'

我认为这是可行的，因为 replace 返回一个 String.

I think this would work because replace returns a String.

还有:

db.execSQL("DELETE FROM table1 WHERE table1.rowid IN (…)");

在rowid"中给出错误:

gives error in 'rowid':

需要列名或表名，得到'rowid'

column name or table name expected, got 'rowid'

我认为这也可以，因为 SQLite 向每个表添加了一个隐式 rowid.

I think this would work too because SQLite adds an implicit rowid to each table.

还有:

db.execSQL("ATTACH DATABASE ? as newDB", new String[] { getDatabasePath(DATABASE_NAME_NEW).getPath() });

在?"中给出错误:

应有表达式，得到'?'

expression expected, got '?'

我已将此查询更改为:

db.execSQL("ATTACH DATABASE '"+getDatabasePath(DATABASE_NAME_NEW).getPath()+"' as newDB");

还有:

db.execSQL("UPDATE table1 SET field1=0 WHERE field2 IN 
(SELECT field2 FROM table2 WHERE field3 LIKE '%blablabla%' OR field4 LIKE '%blebleble%' OR (field5 LIKE '%bliblibli%' AND field6 NOT LIKE '%blobloblo%') 
COLLATE NOCASE)");

导致这些错误的原因:

在field6 NOT LIKE"中:

In 'field6 NOT LIKE':

')' 或 '.'预期，得到不"

')' or '.' expected, got 'NOT'

在NOCASE)"中:

In 'NOCASE)':

BETWEEN、IN 或分号，得到 ')'

BETWEEN, IN or semicolon expected, got ')'

我不知道这个有什么问题.

I don't know what is wrong with this one.

这些查询是否正确?在 iOS 中是相同的并且运行良好.

Are these queries correct? In iOS are identical and they are working well.

更新:

这些是完整的查询(不幸的是，由于我客户的安全策略，我必须更改表和字段的名称，但我希望可以检测到错误).

These are the full queries (unfortunately due to my client's security policy I have to change the name of the tables and fields, but I hope is ok to detect the errors).

db.execSQL("INSERT INTO table1 (field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field11, field12, field13, field14) " +
                    "SELECT t6.field1, t7.field2, t3.field4, t3.field5, CASE WHEN t5.field1='1' THEN '11' " +
                    "WHEN t5.field1='2' THEN '22' WHEN t5.field1='3' THEN '33' WHEN t5.field1='4' THEN '44' " +
                    "ELSE t5.field1 END AS newfield1, t4.field1, CASE WHEN t2.field1 = '0' THEN t4.field2 " +
                    "ELSE substr(replace(t4.field3, ',', ''), t2.field1, 1) END AS newfield2, t2.field4, '0', -1, '0', '1', -1, t8.field1 " +
                    "FROM table2 AS t2, table3 AS t3, table4 AS t4, table5 AS t5, table6 AS t6, table7 AS t7, table8 AS t8 " +
                    "WHERE t2.field2=t3.field3 AND t2.field3=t4.field1 AND t2.field3=t5.field2 AND t3.field2=t7.field2 AND " +
                    "t3.field1=t6.field1 AND substr(t5.field1, 1, 1) IN ('1', '2', '3', '4') AND substr(t5.field1, 2, 1) IN ('1', '2', '3', '4') AND t2.field5=t8.field2");

db.execSQL("DELETE FROM table1 WHERE table1.rowid IN (SELECT table1.rowid FROM table1, table4 AS t4 WHERE table1.field6=t4.field1 AND (((t4.field2 NOT LIKE '%%' || substr(table1.field5, 1,1) || '%%') AND (t4.field3 NOT LIKE '%%' || substr(table1.field5, 1,1) || '%%')) OR ((t4.field2 NOT LIKE '%%' || substr(table1.field5, 2,1) || '%%') AND (t4.field3 NOT LIKE '%%' || substr(table1.field5, 2,1) || '%%'))))");

db.execSQL("ATTACH DATABASE ? as newDB", new String[] { getDatabasePath(DATABASE_NAME_NEW).getPath() });
Changed to:
db.execSQL("ATTACH DATABASE '"+getDatabasePath(DATABASE_NAME_NEW).getPath()+"' as newDB");
And the error is gone.

db.execSQL("UPDATE table1 SET field13=1 WHERE field2 IN (SELECT t7.field2 FROM table7 AS t7 WHERE t7.field1='Text1' OR t7.field1='Text2' OR t7.field1 LIKE '%TEXT3%' OR t7.field1 LIKE '%TEXT4%' OR t7.field1 LIKE '%TEXT5%' OR t7.field1 LIKE '%TEXT6%' OR (t7.field1 LIKE '%TEXT7%' AND t7.field1 NOT LIKE '%TEXT8%') COLLATE NOCASE)");

推荐答案

@Wonton 这是一个使用 MVP 设计的建议，这是你有一个数据库模型的地方，你有一堆数据库表中的每个项目的获取和设置.接下来在名为 DBHelper 的 Activity 中处理所有 CRUD，然后您可以从任何其他 Activity 调用它，而出错的可能性要小得多.是的，我使用 db.execSQL，但是一旦您有了要在 DBHelper 中更新或插入的字符串，生活就会很好这里是 DBHelper Activity HINT 中的代码示例，请阅读有关 MVP 的内容

@Wonton Here is a suggestion use MVP design this is where you have a Database Model where you have a bunch of get and set for each item in the database table. Next handle all your CRUD in an Activity called DBHelper then you can make calls to it from any other Activity with a lot less chance of errors. YES I use db.execSQL but once you have the string to update or insert in DBHelper life is good Here is an example of code from a DBHelper Activity HINT read about MVP

    /* Update Record in Database*/
public void updateDBRow(String rowid,String website, String username, String password, String question,String answer, String notes){

    db = this.getWritableDatabase();
    ContentValues cv = new ContentValues();

    cv.put(Col_WS,website);
    cv.put(Col_UN,username);
    cv.put(Col_PW,password);
    cv.put(Col_SQ,question);
    cv.put(Col_SA,answer);
    cv.put(Col_NOTES,notes);

    /*NOTE WHERE THE quotation MARKS ARE */
    db.update(TABLE_INFO,cv, Col_ID + " = ?",new String[] { rowid });
    db.close();
}

这篇关于Android Studio 将 Android SQLite 查询标记为错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！