java - 如何从数据库中获取日期并检查它们是否重叠？

Question

我有一个存储所有事件的 sqlite 数据库。我以日期格式针对每个事件存储 StartTime 和 EndTime。

我有几天的 fragment ，其中包含选项卡和一个添加事件的 Activity 。事件显示在另一个 fragment 中。

现在我想检查事件是否相互重叠，以及基于哪个应用程序应该显示消息。

当用户保存事件时，如何在添加的事件 Activity 中显示消息。

我该怎么做？

我已经尝试过这种方式，但仍然会添加具有相同开始时间和结束时间的事件:

ImageButton imageButton = (ImageButton)findViewById(R.id.imgbtn_fab);
imageButton.setOnClickListener(new View.OnClickListener() {
    @Override
    public void onClick(View v) {
        eventTitle = title.getText().toString();
        EventTableHelper db = new EventTableHelper(getApplication());
        List<EventData> events;
        events = db.getAllEvents();

        for (EventData eventData : events) {

            String datefrom = eventData.getFromDate();

            String[] times = datefrom.substring(11, 16).split(":");
            minutesFrom = Integer.parseInt(times[0]) * 60 + Integer.parseInt(times[1]);

            String dateTo = eventData.getToDate();

            //times = dateTo.substring(11,16).split(":");
            String[] times1 = dateTo.substring(11, 16).split(":");
            minutesTo = Integer.parseInt(times1[0]) * 60 + Integer.parseInt(times1[1]);
        }

        if (eventTitle.length() == 0) {
            Toast.makeText(getApplicationContext(), "Title can not be empty.", Toast.LENGTH_LONG).show();
        }
        else if(minutesFrom == minutesTo) {
            Toast.makeText(getApplicationContext(),"Event with same time exists.",Toast.LENGTH_LONG).show();
        }
        else
        {
            db.addEvent(new EventData(eventTitle, startTime, endTime, dayOfWeek, location));
            setResult(RESULT_OK, i);
            finish();
        }
    }
});

我得到这样的日期:

c = Calendar.getInstance();

year = c.get(Calendar.YEAR);
month = c.get(Calendar.MONTH);
day = c.get(Calendar.DAY_OF_MONTH);

hour = c.get(Calendar.HOUR_OF_DAY);
minute = c.get(Calendar.MINUTE);

df = new SimpleDateFormat("HH:mm a");
datefrom = c.getTime();
startTime = String.valueOf(datefrom);

aTime = df.format(datefrom.getTime());
showFromTime.setText(aTime);

c.add(Calendar.HOUR_OF_DAY, 1);
dateto = c.getTime();
endTime = String.valueOf(dateto);

bTime = df.format(dateto.getTime());
showToTime.setText(bTime);

我创建了一个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_LOCATION = "location";
    private static final String KEY_DAY_OF_WEEK = "dayofweek";

    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" +  ")";
        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());

        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_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));

       }
       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.setLocation(cursor.getString(4));

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

        return conList;
    }
}

我尝试在 Activity 中使用 getAll 事件函数来获取数据。

编辑:这是我当前的代码。

@Override
public void onClick(View v) {
    eventTitle = title.getText().toString();
    EventTableHelper db = new EventTableHelper(getApplication());
    List<EventData> events;
    // events = db.getOverlappingEvents(startTime,endTime);

    if (eventTitle.length() == 0) {
        Toast.makeText(getApplicationContext(), "Title can not be empty.", Toast.LENGTH_LONG).show();
    }
    else if(db.doesEventOverlap(startTime,endTime))
    {
        Toast.makeText(getApplicationContext(), "Event exists", Toast.LENGTH_LONG).show();
    }
    else
    {
        db.addEvent(new EventData(eventTitle, startTime, endTime, dayOfWeek, location));
        setResult(RESULT_OK, i);
        finish();
    }
}

Answer 1

minutesTo、minutesFrom 将仅具有数据库中最后一行的值。

我会尝试在 EventTableHelper 中做一个新功能:

List<EventData> getOverlappingEvents(Date startTime, Date endTime) {
    List<EventData> conList = new ArrayList<EventData>();

    String selectQuery = "SELECT  * FROM " + TABLE
      + " WHERE (" + KEY_FROM_DATE + " < '" + startTime
      + "' AND '" + startTime + "' < " + KEY_TO_DATE + ") OR "
      + " (" + KEY_FROM_DATE + " < '" + endTime
      + "' AND '" + endTime + "' < " + KEY_TO_DATE + ") OR "

      + " (" + KEY_FROM_DATE + " < '" + startTime
      + "' AND '" + endTime + "' < " + KEY_TO_DATE + ") OR "
      + " ('" + startTime + "' < " + KEY_FROM_DATE
      + " AND " + KEY_TO_DATE + " < '" + endTime + "')";

    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.setLocation(cursor.getString(4));

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

在点击时:

events = db.getOverlappingEvents();
...

而不是:

else if(minutesFrom == minutesTo)

这个:

else if(events.size() > 0)

实际上它可以进一步优化，因为看起来您不需要重叠事件数据，如果至少有一个重叠事件，则只需要一个 boolean 值:

boolean doesEventOverlap(Date startTime, Date endTime) {
    String selectQuery = "SELECT COUNT(*) FROM " + TABLE
      + " WHERE (" + KEY_FROM_DATE + " < '" + startTime
      + "' AND '" + startTime + "' < " + KEY_TO_DATE + ") OR "
      + " (" + KEY_FROM_DATE + " < '" + endTime
      + "' AND '" + endTime + "' < " + KEY_TO_DATE + ") OR "

      + " (" + KEY_FROM_DATE + " < '" + startTime
      + "' AND '" + endTime + "' < " + KEY_TO_DATE + ") OR "
      + " ('" + startTime + "' < " + KEY_FROM_DATE
      + " AND " + KEY_TO_DATE + " < '" + endTime + "')";

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

    if (cursor.moveToFirst()) {
        do {
            EventData event = new EventData();
            return cursor.getInt(0) > 0;
        } while (cursor.moveToNext());
    }
    return false;
}

在 onClick 而不是:

else if(minutesFrom == minutesTo)

这个:

else if(db.doesEventOverlap())