我为学生开发了一个应用程序,允许他们访问模块详细信息,例如时间和日期。
为此,我在DB.Java
中有一个方法来查询数据库并选择模块的名称和时间,WHERE
当天等于当天。这样:
public List<tableModules> getModuleTime(String module1, String module2, String module3, String day) {
List<tableModules> moduleList = new ArrayList<tableModules>();
// Select All Query
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery("SELECT modulename, modulelecturetime, moduleseminartime FROM " + Table2 + " WHERE modulename=? OR modulename=? OR modulename=? AND WHERE modulelecturedate=? OR moduleseminardate=?", new String[]{module1, module2, module3, day, day});
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
tableModules module = new tableModules();
module.modulename = cursor.getString(1);
module.modulelecturedate = cursor.getString(2);
module.moduleseminardate = cursor.getString(3);
moduleList.add(module);
} while (cursor.moveToNext());
}
// return module times
return moduleList;
}
我在MainActivity.Java
中称之为 - 我希望它做的是显示学生的讲座/研讨会,模块在学生表中找到,然后它们用于与数据库匹配以确定时间,然后显示。 - 不幸的是,程序停止,LogCat显示在底部。
List<tableStudents> studentModules = db.getStudentsModules(sessionName);
for (tableStudents session: studentModules)
{
//Save modules to string
String module1 = session.modules.toString();
String module2 = session.modules1.toString();
String module3 = session.modules2.toString();
String today = getCurrentDay().toString();
Toast.makeText(MainActivity.this, "Today is: " + today, Toast.LENGTH_LONG).show();
TextView event1 = (TextView)findViewById(R.id.event1);
event1.setText(today + "'s Events:");
List<tableModules> moduleDayList = db.getModuleTime(module1, module2, module3, today);
for (tableModules module: moduleDayList) {
if(!module.modulelecturedate.isEmpty())
{
event1.setText("Your Lecture for" + module.modulename.toString() + "is at" + module.modulelecturetime.toString());
}
if (!module.moduleseminardate.isEmpty())
{
event1.setText("Your Seminar for" + module.modulename.toString() + "is at" + module.moduleseminartime.toString());
}
}
}
日志猫:
03-08 19:29:23.823: E/AndroidRuntime(1852): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903)
03-08 19:29:23.823: E/AndroidRuntime(1852): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698)
03-08 19:29:23.823: E/AndroidRuntime(1852): Caused by: android.database.sqlite.SQLiteException: near "WHERE": syntax error (code 1): , while compiling: SELECT modulename, modulelecturetime, moduleseminartime FROM Modules WHERE modulename=? OR modulename=? OR modulename=? AND WHERE modulelecturedate=? OR moduleseminardate=?
03-08 19:29:23.823: E/AndroidRuntime(1852): at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
03-08 19:29:23.823: E/AndroidRuntime(1852): at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
03-08 19:29:23.823: E/AndroidRuntime(1852): at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
03-08 19:29:23.823: E/AndroidRuntime(1852): at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
03-08 19:29:23.823: E/AndroidRuntime(1852): at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
03-08 19:29:23.823: E/AndroidRuntime(1852): at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
03-08 19:29:23.823: E/AndroidRuntime(1852): at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
03-08 19:29:23.823: E/AndroidRuntime(1852): at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1316)
03-08 19:29:23.823: E/AndroidRuntime(1852): at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1255)
03-08 19:29:23.823: E/AndroidRuntime(1852): at com.example.project.DB.getModuleTime(DB.java:134)
03-08 19:29:23.823: E/AndroidRuntime(1852): at com.example.project.MainActivity.onResume(MainActivity.java:111)
03-08 19:29:23.823: E/AndroidRuntime(1852): at android.app.Instrumentation.callActivityOnResume(Instrumentation.java:1257)
03-08 19:29:23.823: E/AndroidRuntime(1852): at android.app.Activity.performResume(Activity.java:6076)
03-08 19:29:23.823: E/AndroidRuntime(1852): at android.app.ActivityThread.performResumeActivity(ActivityThread.java:2978)
03-08 19:29:23.823: E/AndroidRuntime(1852): ... 11 more
语句中有两个 WHERE
子句,这不是有效的 SQL。
一个在WHERE modulename=? OR modulename=?....
的地方,另一个AND WHERE modulelecturedate=? OR..........
如何在一个 SQL 语句中克服此问题?
如果要将其作为单个 SQL 语句,请使用括号并删除第二个 WHERE 子句。示例WHERE (modulename=? OR modulename=?....)
AND (modulelecturedate=? OR..........)