java - 选择查询星期几？

Question

我有事件表，其中包含开始时间、结束时间、星期几、位置等。我想根据星期几检索记录。我将 dayOfWeek 保存为字符串。(“周一”、“周二”等)。

如何检索每天的记录？

这是我的 EventTableHelper。

public class EventTableHelper extends SQLiteOpenHelper {


    private static final String TABLE = "event";
    private static final String KEY_ID = "id";
    private static final String KEY_TITLE = "title";
    private static final String KEY_FROM_DATE = "datefrom";
    private static final String KEY_TO_DATE = "dateto";
    private static final String KEY_DAY_OF_WEEK = "dayofweek";
    private static final String KEY_LOCATION = "location";
    private static final String KEY_NOTIFICATION_TIME = "notification";


    public EventTableHelper(Context context) {
        super(context, Constants.DATABASE_NAME, null, Constants.DATABASE_VERSION);

    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }

    public void createTable(SQLiteDatabase db){
        String CREATE_EVENTS_TABLE = "CREATE TABLE " + TABLE+ "("
                + KEY_ID + " INTEGER PRIMARY KEY,"
                + KEY_TITLE + " TEXT,"
                + KEY_FROM_DATE + " DATE,"
                + KEY_TO_DATE + " DATE,"
                + KEY_DAY_OF_WEEK + " TEXT,"
                + KEY_LOCATION + " TEXT,"
                + KEY_NOTIFICATION_TIME + "TEXT" + ")";
        db.execSQL(CREATE_EVENTS_TABLE);
    }
    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        db.execSQL("DROP TABLE IF EXISTS " + TABLE);

       // createTable(db);
       // onCreate(db);
    }

    public void addEvent(EventData event) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_TITLE,event.getTitle());
        values.put(KEY_FROM_DATE, event.getFromDate());
        values.put(KEY_TO_DATE,event.getToDate());
        values.put(KEY_DAY_OF_WEEK,event.getDayOfWeek());
        values.put(KEY_LOCATION,event.getLocation());
        values.put(KEY_NOTIFICATION_TIME,event.getNotificationTime());

        db.insert(TABLE, null, values);

        db.close();
    }

   EventData getEvent(int id) {

        SQLiteDatabase db = this.getReadableDatabase();
        EventData eventData = new EventData();
        Cursor cursor = db.query(TABLE, new String[]{KEY_ID,
                        KEY_TITLE, KEY_FROM_DATE, KEY_TO_DATE, KEY_DAY_OF_WEEK, KEY_LOCATION}, KEY_ID + "=?",
                new String[]{String.valueOf(id)}, null, null, null, null);

       if( cursor != null && cursor.moveToFirst() ) {
          eventData = new EventData(Integer.parseInt(cursor.getString(0)), cursor.getString(1), cursor.getString(2),
                   cursor.getString(3), cursor.getString(4), cursor.getString(5),cursor.getString(6));

       }
       return eventData;
    }

    public List<EventData> getAllEvents() {
        List<EventData> conList = new ArrayList<EventData>();

        String selectQuery = "SELECT  * FROM " + TABLE ;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        if (cursor.moveToFirst()) {
            do {

                EventData event = new EventData();

                event.setId(Integer.parseInt(cursor.getString(0)));
                event.setTitle(cursor.getString(1));
                event.setFromDate(cursor.getString(2));
                event.setToDate(cursor.getString(3));
                event.setDayOfWeek(cursor.getString(4));
                event.setLocation(cursor.getString(5));
                event.setNotificationTime(cursor.getString(6));

                conList.add(event);
            } while (cursor.moveToNext());
        }

        return conList;
    }



    public int updateEvent(EventData event) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_TITLE,event.getTitle());
        values.put(KEY_FROM_DATE,event.getFromDate());
        values.put(KEY_TO_DATE,event.getToDate());
        values.put(KEY_DAY_OF_WEEK,event.getDayOfWeek());
        values.put(KEY_LOCATION,event.getLocation());
        values.put(KEY_NOTIFICATION_TIME,event.getNotificationTime());


        // updating row
        return db.update(TABLE, values, KEY_ID + " = ?",
                new String[] { String.valueOf(event.getId()) });
    }
    public void deleteEvent(EventData eventData) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE, KEY_ID + " = ?",
                new String[]{String.valueOf(eventData.getId())});
        db.close();
    }

  }

我尝试像这样检索:

   public List<EventData> getAllEvents(String day) {
        List<EventData> conList = new ArrayList<EventData>();

        String selectQuery = "SELECT  * FROM " + TABLE + " WHERE (" + KEY_DAY_OF_WEEK + " = " + day;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        if (cursor.moveToFirst()) {
            do {

                EventData event = new EventData();

                event.setId(Integer.parseInt(cursor.getString(0)));
                event.setTitle(cursor.getString(1));
                event.setFromDate(cursor.getString(2));
                event.setToDate(cursor.getString(3));
                event.setDayOfWeek(cursor.getString(4));
                event.setLocation(cursor.getString(5));
                event.setNotificationTime(cursor.getString(6));

                conList.add(event);
            } while (cursor.moveToNext());
        }

        return conList;
    }

但它给出了语法错误:

 android.database.sqlite.SQLiteException: near "Mon": syntax error (code 1): , while compiling: SELECT  * FROM event WHERE (dayofweek = Mon)

我正在检索这样的事件:

String day = "Mon"
    mDb = new EventTableHelper(getActivity());
    events = mDb.getAllEvents(day);

我正在另一个 Activity 中保存 dayOfWeek。我想检索 fragment 中的记录。我不知道如何在函数中传递日期参数。

Answer 1

喜欢在单引号中传递值

String selectQuery = "SELECT  * FROM " + TABLE + " WHERE " + KEY_DAY_OF_WEEK +" = '"+day+ "'";