java - SQLite Android 根据今天是哪一天获取数据库信息

Question

我为学生开发了一个应用程序，允许他们访问时间和日期等模块详细信息。

为此，我在 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

Answer 1

一条 SQL 语句中有两个 WHERE 子句，这不是有效的 SQL。

其中一个位于WHERE modulename=？ OR modulename=?.... 和另一个 AND WHERE modulelecturedate=?或者......

如何在一条 SQL 语句中克服这个问题？

如果您想将其作为单个 SQL 语句，请使用括号并删除第二个 WHERE 子句。示例 WHERE (modulename=? OR modulename=?....) AND (modulelecturedate=? OR....)