android - 如何比较 sqlite 数据库中的文本数据值？

Question

在这里，我将 sqlite 数据库中的文本值与字符串(部分、分支等...)进行比较。这是比较的正确方法吗？我在 query() 语句中没有得到这样的列错误。

public Cursor getAllRows(String year, String section, String branch,
                         String subject, String date) {    

  return db.query(
      DATABASE_TABLE,
      null, 
      KEY_YEAR + "=" + year + 
        " AND "+ KEY_SECTION + "=" + section +
        " AND "+ KEY_BRANCH + "=" + branch + 
        " AND "+ KEY_SUBJECT + "=" + subject + 
        " AND "+ KEY_DATE + "=" + date,
      null, 
      null, 
      null, 
      null
  );
} 

日志

04-04 10:31:51.568: ERROR/AndroidRuntime(21928):
   Caused by:
     android.database.sqlite.SQLiteException:
       no such column: CSE: ,
       while compiling:
         SELECT * 
         FROM student 
         WHERE year=2 AND branch like CSE 
         AND subject=SE AND date=04-Apr-2012

04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:91)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:64)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:80)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:46)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:42)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1345)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1229)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1184)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1264)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at sharath.android.trail.DBAdapter.getAllRows(DBAdapter.java:100)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at sharath.android.trail.MytableActivity.onCreate(MytableActivity.java:52)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2627)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): ... 20 more

其实CSE是属于分支列的一个值，在此先感谢。

Answer 1

查询中的字符串常量需要用单引号 (') 字符括起来。您正在插入不带引号的字符串，因此它们被解释为列引用。

通常直接插入字符串值不是一个好主意。您应该创建一个带有绑定(bind)参数的语句。

这将自动转义并引用字符串:

public Cursor getAllRows(String year, String section, String branch,
    String subject, String date)
{
    return db.rawQuery("SELECT * FROM " +
         DATABASE_TABLE + " WHERE " +
         KEY_YEAR + "=? AND " +
         KEY_SECTION + "=? AND " +
         KEY_BRANCH + "=? AND " +
         KEY_SUBJECT + "=? AND "+
         KEY_DATE + "=?",
         new String[] { year, section, branch, subject, date }
    );
}