mardi 8 mars 2016

Obtaining database information depending on what day it is SQLite Android

I have developed an application for students that allows them to access module details such as time and date.

For this I have a method in my DB.Java to query the database and select the name and times of the module WHERE the day is equal to the current day. Like so:

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;      
    }

I call this in my MainActivity.Java - All I want it to do is display what lectures/seminars the student has, the modules are found in the student table then they're used to match against the database to determine what time, then displayed. - Unfortunately the program stops and the LogCat is shown at the bottom.

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());
                      }

                    }

            }   

Logcat:

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

Aucun commentaire:

Enregistrer un commentaire