java - 根据当天返回 SQLite 数据 - 无结果

Question

我在数据库类中编写了一个方法，根据具体日期搜索数据库，但是，即使详细信息正确，也没有结果。

有人能明白为什么它不会返回任何东西吗？我已经尝试了大约 15 种不同的 SQL 查询变体，所以我陷入了困境!

我的DB.java中的方法如下:

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 (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(0);
                module.modulelecturedate = cursor.getString(1);
                module.moduleseminardate = cursor.getString(2);
                moduleList.add(module);
            } while (cursor.moveToNext());
        }
        else
        {
            Log.d("Cant", "Find any lectures today");
        }   
        // return contact list
        return moduleList;      
    }

然后我在 MainActivity 中使用此方法将数据分到:

public void getTodaysModules(String sessionName)
    {
        List<tableStudents> studentModules = db.getStudentsModules(sessionName);    
        if (!studentModules.isEmpty())
        {           
        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());
                  }
                  else
                  {
                      event1.setText("You have no seminars/lectures today");
                  }             
                }
            }
        }
        else
        {
            Toast.makeText(MainActivity.this, "N", Toast.LENGTH_LONG).show();           
        }

    }

Answer 1

这是您的 SQL 可读方式:

Cursor cursor = db.rawQuery("SELECT modulename, modulelecturetime, moduleseminartime" +
                             " FROM " + Table2 +
                            " WHERE modulename=?" +
                               " OR modulename=?" +
                               " OR modulename=?" +
                              " AND (modulelecturedate=?" +
                                " OR moduleseminardate=?)",
                            new String[]{module1, module2, module3, day});

您会注意到，有 5 个标记，但您只提供 4 个值。

此外，请记住 AND 的优先级高于 OR，因此 SQL 实际上是:

SELECT modulename, modulelecturetime, moduleseminartime
  FROM xxx
 WHERE modulename=?
    OR modulename=?
    OR ( modulename=?
     AND ( modulelecturedate=? OR moduleseminardate=? ) )

这就是你的 Intent 吗？我假设你会想要:

SELECT modulename, modulelecturetime, moduleseminartime
  FROM xxx
 WHERE ( modulename=? OR modulename=? OR modulename=? )
   AND ( modulelecturedate=? OR moduleseminardate=? )