如何查找列表中是否包含另一个列表中的任何元素

Question

我有一个sqlite表，其中有一个列selected_dates。此列包含CustomDates对象的列表。我想询问这个列是否包含任何其他类型的列表( customDates)。但我找不到任何办法。我试过：

    @Query("SELECT * FROM schedule_table WHERE (type_daily IS 1) OR (:monthOfYear IS 1) " +
            "OR (selectedDates IN (:customDates))" +
            "ORDER BY scheduleID DESC")
    LiveData<List<Schedule>> getSchedulesOfThisWeek(String monthOfYear, List<ScheduleType.Dates> customDates);

下面是我的示例数据(List<Date>对象以json格式存储) Image of data in sqlite browser

[{"dayOfMonth":1,"month":8},{"dayOfMonth":2,"month":8},{"dayOfMonth":3,"month":8},{"dayOfMonth":4,"month":8},{"dayOfMonth":5,"month":8},{"dayOfMonth":6,"month":8},{"dayOfMonth":7,"month":8},{"dayOfMonth":8,"month":8},{"dayOfMonth":9,"month":8},{"dayOfMonth":10,"month":8},{"dayOfMonth":11,"month":8},{"dayOfMonth":12,"month":8},{"dayOfMonth":13,"month":8},{"dayOfMonth":14,"month":8},{"dayOfMonth":15,"month":8},{"dayOfMonth":16,"month":8},{"dayOfMonth":17,"month":8},{"dayOfMonth":18,"month":8},{"dayOfMonth":19,"month":8},{"dayOfMonth":20,"month":8},{"dayOfMonth":21,"month":8},{"dayOfMonth":22,"month":8},{"dayOfMonth":23,"month":8},{"dayOfMonth":24,"month":8},{"dayOfMonth":25,"month":8},{"dayOfMonth":26,"month":8},{"dayOfMonth":27,"month":8},{"dayOfMonth":28,"month":8},{"dayOfMonth":29,"month":8}]

总之，如何找出两个列表在sqlite中是否相交？

Answer 1

基本上，房间数据库将数据存储在主数据类型中。所以您选择的日期是TEXT格式的。您可以执行LIKE操作来查找匹配的列。

Room还提供了一个注释@RawQuery。你可以用这个。对于您的问题，这可能是一个解决方案：

在Dao接口中：

@RawQuery(observedEntities = Schedule.class)
LiveData<List<Schedule>> getSchedule(SupportSQLiteQuery query);

以及使用getSchedule()

String query = "SELECT * FROM schedule_table WHERE " +
                "(type_daily IS 1) OR (" + monthOfYear + " IS 1) OR " +
                conditions + " ORDER BY scheduleID DESC";
        return mDao.getSchedule(new SimpleSQLiteQuery(query));

其中condition是您想要查找的日期的String。例如：

String condition = "selectedDates LIKE '%{\"dayOfMonth\":1,\"month\":8}%' OR selectedDates LIKE '%{\"dayOfMonth\":5,\"month\":8}%';

您可以创建一个基于List<ScheduleType.Dates>生成此字符串的方法。