有什么办法可以在房间数据库中运行嵌套查询 [英] is there any way to run nested query in room database

问题描述

我正在将旧的Android项目迁移到新标准，我陷入了room database之间，无法找到如何使用room database执行这2个SQLite命令的情况.我有2个以嵌套方式工作的查询，因此使用room达到相同的结果是有可能的.

I am migrating my old Android project to new standards, I got stuck in between room database, not able to find how to execute these 2 SQLite commands using room database. I have 2 queries which are working in a nested manner, so to achieve the same result using room is there any possibility.

    default public ArrayList getOfflineRoomsAndObjectsByParentIds(String parentId, String site_id, Context context) {

        databaseHandler = new DatabaseHandler(context);
        Cursor cr = null;
        ArrayList<Room> offlineRoomNamesList = new ArrayList<>();
        try {

           **sqLiteDatabase = databaseHandler.getReadableDatabase();
            String query = "SELECT * FROM " + Constants.TABLE_ROOM_NAMES
                    + " WHERE " + Constants.SITE_ID + " = '" + site_id + "' AND " + Constants.PARENT_ID + "='" + parentId + "'";**

            cr = sqLiteDatabase.rawQuery(query, null);
            if (cr.moveToFirst()) {

                do {

                    Room room = new Room();
                    **room.setId(cr.getString(cr.getColumnIndex(Constants.KEY_ROOM_ID)));
                    query = "SELECT * FROM " + Constants.TABLE_OFFLINE_ROOM_OBJECTS
                            + " WHERE " + Constants.KEY_ROOM_KEY + " = ?"
                            + " AND " + Constants.SITE_ID + " = ? ORDER BY " + Constants.KEY_OBJECT_NAME;**

                    Cursor cursor = sqLiteDatabase.rawQuery(query, new String[]{cr.getString(cr.getColumnIndex(Constants.KEY_ROOM_KEY)), site_id});
                    if (cursor.moveToFirst()) {
                        do {
                            RoomObject roomObject = new RoomObject();
                            roomObject.setRoomKey(cursor.getString(cursor.getColumnIndex(Constants.KEY_ROOM_KEY)));
                            roomObject.setId(cursor.getInt(cursor.getColumnIndex(Constants.KEY_ID)));
                            roomObject.setInstanceNumber(cursor.getInt(cursor.getColumnIndex(Constants.KEY_INSTANCE_NUMBER)));
                            roomObject.setObjectType(cursor.getString(cursor.getColumnIndex(Constants.KEY_OBJECT_TYPE)));


                            String mergeKey = AppSharedPreference.getString(AppSharedPreference.MERGE_KEY, "", context);
                            if (roomObject.getObjectName().contains(mergeKey) && !TextUtils.isEmpty(mergeKey)) {
                                room.addMergeableObjectsInList(roomObject);
                            } else {
                                room.addRoomObjectsInList(roomObject);
                            }


                        }
                        while (cursor.moveToNext());
                    }
                    if (room.getRoomObjectsList().size() > 0)
                        offlineRoomNamesList.add(room);
                    cursor.close();
                } while (cr.moveToNext());
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                cr.close();
                sqLiteDatabase.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return offlineRoomNamesList;
    }

此刻我所看到的唯一可能性是两个定义2个不同的查询并以嵌套方式执行它们.

The only possibility I am looking at this moment is two define 2 different query and execute them into a nested manner.

@Dao
public interface ControllerRoomObjectDao {



    @Query("SELECT * FROM room_names WHERE site_id=:siteId AND parentId=:parentId")
    Single<List<Room>>getOfflineRoomsAndObjectsByParentId(String siteId, String parentId);


    @Query("SELECT * FROM offline_objects WHERE site_id=:siteId AND parentId=:parentId")
    Single<List<RoomObject>>getOfflineObjectsByParentId(String siteId, String parentId);

}

推荐答案

不确定这是否完全符合您的要求，但可能会帮助具有相同一般要求的其他人.

Not sure if this meets your requirements exactly, but it may help others with the same general requirement.

基本上，我从此示例获取了建议，该示例显示了如何执行嵌套MySQL中的查询/子查询.

Basically, I took the advice from this example that shows how to perform a nested query / subquery in MySQL.

使用相同的语法，我可以从以下方式更改我的基本DAO查询:

With that same syntax I was able to change my basic DAO query from:

@Transaction
@Query("SELECT * FROM `events` WHERE `item_id` = :currentItemId AND `event_datetime` > :datetimeFrom ORDER BY `event_datetime` ASC LIMIT 1")
LiveData<Event> getNextEventWithSameItemId(long currentItemId, String datetimeFrom);

到

@Transaction
@Query("SELECT * FROM `events` WHERE `item_id` IN (SELECT `item_id` FROM `events` WHERE `_id` = :currentEventId) AND `event_datetime` > :datetimeFrom ORDER BY `event_datetime` ASC LIMIT 1")
LiveData<Event> getNextEventWithSameEventId(long currentEventId, String datetimeFrom);

我的事件实体所在的位置

@Entity(tableName = "events")
public class Event {

    @PrimaryKey
    @ColumnInfo(name = "_id")
    public long id;

    @ColumnInfo(name = "item_id") // Relates to `items`.`_id`
    public long itemId;

    @ColumnInfo(name = "event_datetime")
    public String eventDatetime;
    
    ...
}

这篇关于有什么办法可以在房间数据库中运行嵌套查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！