java - 如何将 SQL UNION 与三个独立的 Ignite 缓存一起使用

Question

我有一个点燃缓存并按如下方式执行 sql 查询。

IgniteCache<String, ClassName> cache = ignite.cache(CACHE_NAME);
private static final String sql = "Select timestamp from cache1 where  orderId = ? and timestamp <= ? and timestamp >= ? ";
SqlFieldsQuery sqlQ = new SqlFieldsQuery(sql).setArgs(id, t1, t2);
    try (QueryCursor<List<?>> cursor = cache.query(sqlQ)) {
        for (List<?> row : cursor) {
                timestamps.add((Long) row.get(0));          
        }

现在我想从三个不同的缓存中查询并获得联合。我能够如下在 SQL 引擎中成功执行 SQL 命令并获得结果。

Select starttime from "unconfirmed_event_mc_79".unconfirmedevent union all Select starttime from "unconfirmed_event_urgent_mc_79".unconfirmedevent union all Select starttime from "confirmed_event_mc_79".confirmedevent order by starttime desc limit 1;

我想将来自三个单独缓存的时间戳结果添加到单个数组列表中。我尝试了以下方法，但没有成功。

EVENT_GET_RHYTHM_BY_ID = "Select timestamp from ConfirmedEvent where orderId = ? and startTime < ? and endTime > ? and type = %s UNION ALL " +
                    "Select timestamp from " + String.format(UNCONFIRMED_NON_URGENT_EVENT_CACHE,mcId) + ".UnconfirmedEvent where orderId = ? and startTime < ? and endTime > ? and type = %s " +
                    "Select timestamp from " + String.format(UNCONFIRMED_URGENT_EVENT_CACHE,mcId) + ".UnconfirmedEvent where orderId = ? and startTime < ? and endTime > ? and type = %s " + " order by startTime";
sql_Afib = new SqlFieldsQuery(String.format(EVENT_GET_RHYTHM_BY_ID, AnnotationConverter.StringToRhythmValue(AFIB), AnnotationConverter.StringToRhythmValue(AFIB),
                            AnnotationConverter.StringToRhythmValue(AFIB))).setArgs(orderId, endTimestamp, startTimestamp,
                            orderId, endTimestamp, startTimestamp, orderId, endTimestamp, startTimestamp);
try (QueryCursor<List<?>> cursor = confirmedEventCache.query(sql_Afib)) {
                    for (List<?> row : cursor) {
                        
                        EventsEndTime.add(row.get(0));
                        
                    }
                }

我想知道如何使用查询游标？现在有三个缓存，这部分怎么做？(QueryCursor<List<?>> cursor = cache.query(sqlThreeCaches)) 以及如何在java代码中编写SQL字段？

或者有没有其他方法可以做到这一点？

这是我在 java 代码中定义缓存的方式。存在三种不同的缓存，但缓存表中的列名相同。

public static final String EVENT_VIEW_RESERVED_EVENT_CACHE = "event_view_reserved_event_mc_%d";
public static final String UNCONFIRMED_NON_URGENT_EVENT_CACHE = "unconfirmed_event_mc_%d";
public static final String UNCONFIRMED_URGENT_EVENT_CACHE = "unconfirmed_event_urgent_mc_%d";
IgniteCache<String, ConfirmedEvent> confirmedEventCache = ignite.cache(String.format(CONFIRMED_EVENT_CACHE, mcId));
IgniteCache<String, UnconfirmedEvent> unconfirmedEventCache = ignite.cache(String.format(UNCONFIRMED_NON_URGENT_EVENT_CACHE,mcId));
IgniteCache<String, UnconfirmedEvent> unconfirmedUrgentEventCache = ignite.cache(String.format(UNCONFIRMED_URGENT_EVENT_CACHE,mcId));

标签： javasqlcachingignitegridgain