Я пишу SQL-запрос в своем приложении для Android, которое, хотя и будет работать, но не работает. Итак, это выглядит так:
@Query("SELECT * FROM ${AppDatabase.GAME_TABLE} WHERE :collectionId IN (themesIds)")
fun getAllGamesForCollection(collectionId: String): List<GameEntity>
В моем объекте Game_Table поле themeIds представлено следующим образом:
@ColumnInfo(name = "themesIds")
var themesIds: List<String> = mutableListOf()
Я хотел проверить, какие игры содержат «collectionId» (единственную строку, которую я отправляю в своем коде) в своих темах Ids (список строк, которые содержат разные идентификаторы и сохраняются в базе данных). Пользуюсь комнатной библиотекой (только для записей).
Почему это не работает?
@ user39950 извините, но я не вижу никакой связи между этими двумя вопросами. Более того, мой код не вызывает сбоев, он просто не возвращает никакого значения для этого запроса. Также я использую не список объектов, а список примитивных данных в моей сущности.
Вы можете использовать WHERE instr(themesIds,:collectionId)
или WHERE themesIds LIKE '%'||:collectionId||'%'
.
Это зависит от сохраненного представления списка (см. ниже), содержащего текстовые значения (если список преобразуется в поток байтов, то описанное выше не будет работать без преобразования байтов).
Однако оба склонны к другим нежелательным совпадениям; например, AAA
также найдет AAABBB
BBBAAACCC
, поскольку AAA
существует во всех них.
Вероятной причиной того, что IN не работает, является то, что он ожидает, что значение после IN будет либо списком значений, разделенных запятыми, либо результатом запроса на выборку. Маловероятно, что данные (список строк, содержащих разные идентификаторы и сохраненные в базе данных) являются именно таким CSV-файлом, это будет одно значение, содержащее все значения списка.
Если упомянутая выше слабость является проблемой, вам нужно будет более конкретно указать, какой именно список строк, которые содержат разные идентификаторы и сохраняются в базе данных, на самом деле; это не может быть список. То есть SQLite (база данных, оболочкой которой является Room) не хранит списки, а хранит отдельные значения для каждого столбца, которые могут отражать объект (список).
Затем 4 значения должны быть представлены одним значением.
Если бы это единственное значение было The,Quick,Brown,Fox
(CSV), то IN не работало бы, поскольку это единственное значение (если бы это были «The», «Quick», «Brown», «Fox», тогда это сработало бы, но чтобы получить это из столбца будет довольно сложным (рекурсивный подзапрос, который разбивает строку)).
Однако нет причин, по которым представление могло бы вместо этого быть The;Quick;Brown;Fox
, и в этом случае IN не работало бы.
Вы можете отредактировать свой вопрос, включив в него пример фактических данных, хранящихся в столбце themesIds (для этого можно использовать проверку приложений)
человек, это отличное объяснение ситуации. Я попробовал WHERE instr(themesIds,: collectionId), и это работает как шарм. Спасибо за помощь!
Вот еще один способ, который, с точки зрения реляционной базы данных (например, SQLite, который является оболочкой Room), считается лучшим/правильным (и на что пытается указать комментарий в вашем вопросе).
То есть вместо того, чтобы встраивать список, этот список действительно должен быть другой таблицей, которая имеет отношение к таблице, содержащей встроенный список. Как таковой, а не список themeId таблицы Game и таблицы Theme (которые у вас вполне могут быть). Поскольку тема может использоваться многими играми, и эта игра может иметь множество тем. Отношения будут отношениями многих-многих.
Связь «многие-многие» обычно имеет другую таблицу с двумя основными столбцами. Один для ссылки на одну таблицу, другой для ссылки на другую таблицу. Два столбца образуют составной уникальный индекс обоих столбцов (составной первичный ключ). Таким образом, любая игра может ссылаться (карта/ссылка....) на любую тему.
Такая таблица имеет множество названий: справочная таблица, таблица сопоставления, ассоциативная таблица....
С такой настройкой можно было бы использовать IN
.
Чтобы продемонстрировать, рассмотрим следующий код, который отражает часть того, что можно почерпнуть из вашего вопроса.
Сначала версия класса AppDatabase (расширенная для включения ВСЕХ имен компонентов, таблицы и столбцов, а также расширенная для обслуживания таблицы сопоставления, надеюсь, имена должны быть достаточно понятными): -
class AppDatabase {
companion object {
const val DATABASE_NAME = "game.db"
const val GAME_TABLE = "game"
const val GAME_ID_COL = "${GAME_TABLE}${BaseColumns._ID}"
const val GAME_THEMEIDS_COL = "${GAME_TABLE}_themids"
const val THEME_TABLE = "theme"
const val THEME_ID_COL = "${THEME_TABLE}${BaseColumns._ID}"
const val THEME_NAME_COL = "${ THEME_TABLE}_themename"
/* The Mapping table */
const val GAME_THEME_MAP_TABLE = "game_theme_map"
const val GAME_THEME_MAP_GAME_ID_COL = "${GAME_THEME_MAP_TABLE}_${GAME_ID_COL}"
const val GAME_THEME_MAP_THEME_ID_COL = "${GAME_THEME_MAP_TABLE}_${THEME_ID_COL}"
}
}
Обратите внимание, что приведенное выше гарантирует, что все имена столбцов уникальны (Room без жалоб может сопоставлять значения между столбцами с одинаковыми именами).
Как видно, 3 таблицы Game, Theme и таблица сопоставления между ними.
Для демонстрации игровой стол: -
@Entity(tableName = AppDatabase.GAME_TABLE)
data class Game(
@ColumnInfo(name = AppDatabase.GAME_ID_COL)
@PrimaryKey
val id: Long?=null,
//@ColumnInfo(name = AppDatabase.GAME_THEMEIDS_COL)
//val themeIds: List<String>
)
Таблица тем: -
@Entity(
tableName = AppDatabase.THEME_TABLE,
/* Make the Theme name unique */
indices = [
Index(AppDatabase.THEME_NAME_COL, unique = true)
]
)
data class Theme(
@ColumnInfo(name = AppDatabase.THEME_ID_COL)
@PrimaryKey
val id: Long?=null,
@ColumnInfo(name = AppDatabase.THEME_NAME_COL)
val themeName: String
)
Теперь таблица сопоставления GameThemeMap (карта используется только потому, что она короче): -
/* caters for a many-many relationship */
/* i.e. many games can map to a theme, many themes can map to a them */
/* no need to store lists*/
@Entity(tableName = AppDatabase.GAME_THEME_MAP_TABLE,
primaryKeys = [AppDatabase.GAME_THEME_MAP_GAME_ID_COL,AppDatabase.GAME_THEME_MAP_THEME_ID_COL],
/* Optional but enforces Referential Integrity */
foreignKeys = [
ForeignKey(
entity = Game::class,
parentColumns = [AppDatabase.GAME_ID_COL],
childColumns = [AppDatabase.GAME_THEME_MAP_THEME_ID_COL],
/* Optional within Foreign Key but helps to automatically maintain Referential Integrity */
onDelete = ForeignKey.CASCADE, /* deletes children if parent is deleted */
onUpdate = ForeignKey.CASCADE /* updates children if parent mapped column is updated */
),
ForeignKey(
entity = Theme::class,
parentColumns = [AppDatabase.THEME_ID_COL],
childColumns = [AppDatabase.GAME_THEME_MAP_THEME_ID_COL],
onDelete = ForeignKey.CASCADE,
onUpdate = ForeignKey.CASCADE
)
]
)
data class GameThemeMap(
@ColumnInfo(name = AppDatabase.GAME_THEME_MAP_GAME_ID_COL)
val gameIdMap: Long,
@ColumnInfo(index = true, name = AppDatabase.GAME_THEME_MAP_THEME_ID_COL)
/* Index will speed up access via themes id column, but will have an overhead when inserting */
val themeIdMao: Long
)
Поскольку вы, вероятно, ожидаете, что сможете получить игру с ее темами, тогда POJO для этого GameWithThemes: -
data class GameWithThemes(
@Embedded
val game: Game,
@Relation(
entity = Theme::class,
parentColumn = AppDatabase.GAME_ID_COL,
entityColumn = AppDatabase.THEME_ID_COL,
associateBy = Junction(
value = GameThemeMap::class,
parentColumn = AppDatabase.GAME_THEME_MAP_GAME_ID_COL,
entityColumn = AppDatabase.GAME_THEME_MAP_THEME_ID_COL
)
)
val themes: List<Theme>
)
Некоторые функции @Dao включают два способа выбора игр через collectionId: один с использованием IN, другой с использованием сравнения идентификатора темы, извлеченного с помощью JOIN, поэтому TheDAO:
@Dao
interface TheDAOs {
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(game: Game): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(theme: Theme): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(gameThemeMap: GameThemeMap): Long
@Transaction /* recommended as Room executes subqueries to get the Themes for the Game (note that it gets ALL themes for a game) */
@Query("SELECT ${AppDatabase.GAME_TABLE}.* " +
"FROM ${AppDatabase.GAME_TABLE} " +
"JOIN ${AppDatabase.GAME_THEME_MAP_TABLE} " +
"ON ${AppDatabase.GAME_THEME_MAP_TABLE}.${AppDatabase.GAME_THEME_MAP_GAME_ID_COL} = ${AppDatabase.GAME_TABLE}.${AppDatabase.GAME_ID_COL} " +
"AND :collectionId = ${AppDatabase.GAME_THEME_MAP_TABLE}.${AppDatabase.GAME_THEME_MAP_THEME_ID_COL}")
fun getGamesWithThemesViaJoin(collectionId: Long): List<GameWithThemes>
@Transaction
@Query("SELECT * FROM ${AppDatabase.GAME_TABLE} WHERE :collectionId IN(SELECT ${AppDatabase.GAME_THEME_MAP_THEME_ID_COL} FROM ${AppDatabase.GAME_THEME_MAP_TABLE} WHERE ${AppDatabase.GAME_THEME_MAP_GAME_ID_COL}=${AppDatabase.GAME_ID_COL})")
fun getGamesWithThemesViaIN(collectionId: Long): List<GameWithThemes>
@Query("SELECT ${AppDatabase.THEME_ID_COL} FROM ${AppDatabase.THEME_TABLE} WHERE ${AppDatabase.THEME_NAME_COL}=:themeName")
fun getThemeIdFromThemeName(themeName: String): Long
}
Чтобы связать все вышеперечисленное с аннотированным абстрактным классом @Database TheDatabase:
@Database(entities = [Game::class,Theme::class,GameThemeMap::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() {
abstract fun getTheDAOs(): TheDAOs
companion object {
var instance: TheDatabase? = null
fun getInstance(context: Context): TheDatabase {
if (instance==null) {
instance = Room.databaseBuilder(context,TheDatabase::class.java,AppDatabase.DATABASE_NAME)
.allowMainThreadQueries()
.build()
}
return instance as TheDatabase
}
}
}
.allowMainThreadQueries
для удобства и краткостиПоследний, чтобы фактически продемонстрировать некоторый код в действии, которое добавляет 5 тем, 5 игр и различные темы, сопоставленные с играми.
После вставки данных выполняются 2 набора по 5 запросов и извлеченные GameWithThemes выводятся в лог методом logGameWithThemes
.
Активность MainActivity: -
const val TAG = "DBINFO"
class MainActivity : AppCompatActivity() {
lateinit var db: TheDatabase
lateinit var dao: TheDAOs
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
db = TheDatabase.getInstance(this)
dao = db.getTheDAOs()
val t1Id = dao.insert(Theme(themeName = "Theme 1"))
val t2Id = dao.insert(Theme(themeName = "Theme 2"))
val t3Id = dao.insert(Theme(themeName = "Theme 3"))
val t4Id = dao.insert(Theme(themeName = "Theme 4"))
val t5Id = dao.insert(Theme(themeName = "Theme 5"))
val game1Id =dao.insert(Game())
val game2Id = dao.insert(Game())
val game3Id = dao.insert(Game())
val game4Id = dao.insert(Game())
val game5Id = dao.insert(Game())
dao.insert(GameThemeMap(game1Id,t1Id))
dao.insert(GameThemeMap(game1Id,t2Id))
dao.insert(GameThemeMap(game2Id,t3Id))
dao.insert(GameThemeMap(game2Id,t4Id))
dao.insert(GameThemeMap(game3Id,t1Id))
dao.insert(GameThemeMap(game3Id,t2Id))
dao.insert(GameThemeMap(game3Id,t3Id))
dao.insert(GameThemeMap(game3Id,t4Id))
dao.insert(GameThemeMap(game4Id,t5Id))
for (g in dao.getGamesWithThemesViaIN(t1Id)) {
logGameWithThemes(g,"_IN_TID_$t1Id")
}
for (g in dao.getGamesWithThemesViaIN(t2Id)) {
logGameWithThemes(g,"_IN_TID_$t2Id")
}
for (g in dao.getGamesWithThemesViaIN(t3Id)) {
logGameWithThemes(g,"_IN_TID_$t3Id")
}
for (g in dao.getGamesWithThemesViaIN(t4Id)) {
logGameWithThemes(g,"_IN_TID_$t4Id")
}
for (g in dao.getGamesWithThemesViaIN(t5Id)) {
logGameWithThemes(g,"_IN_TID_$t5Id")
}
for (g in dao.getGamesWithThemesViaJoin(t1Id)) {
logGameWithThemes(g,"_JN_TID_$t1Id")
}
for (g in dao.getGamesWithThemesViaJoin(t2Id)) {
logGameWithThemes(g,"_JN_TID_$t2Id")
}
for (g in dao.getGamesWithThemesViaJoin(t3Id)) {
logGameWithThemes(g,"_JN_TID_$t3Id")
}
for (g in dao.getGamesWithThemesViaJoin(t4Id)) {
logGameWithThemes(g,"_JN_TID_$t4Id")
}
for (g in dao.getGamesWithThemesViaJoin(t5Id)) {
logGameWithThemes(g,"_JN_TID_$t5Id")
}
}
fun logGameWithThemes(gameWithThemes: GameWithThemes, suffix: String) {
val sb = StringBuilder()
for(t in gameWithThemes.themes) {
sb.append("\n\tTheme ID is ${t.id} Theme name is ${t.themeName}")
}
Log.d(TAG+suffix,"Game ID is ${gameWithThemes.game.id} it has ${gameWithThemes.themes.size} THEMES. They are:-${sb}")
}
}
При запуске (предназначенном для запуска только один раз для демонстрации) вывод в журнал: -
2023-01-18 22:22:04.903 D/DBINFO_IN_TID_1: Game ID is 1 it has 2 THEMES. They are:-
Theme ID is 1 Theme name is Theme 1
Theme ID is 2 Theme name is Theme 2
2023-01-18 22:22:04.903 D/DBINFO_IN_TID_1: Game ID is 3 it has 4 THEMES. They are:-
Theme ID is 1 Theme name is Theme 1
Theme ID is 2 Theme name is Theme 2
Theme ID is 3 Theme name is Theme 3
Theme ID is 4 Theme name is Theme 4
2023-01-18 22:22:04.906 D/DBINFO_IN_TID_2: Game ID is 1 it has 2 THEMES. They are:-
Theme ID is 1 Theme name is Theme 1
Theme ID is 2 Theme name is Theme 2
2023-01-18 22:22:04.906 D/DBINFO_IN_TID_2: Game ID is 3 it has 4 THEMES. They are:-
Theme ID is 1 Theme name is Theme 1
Theme ID is 2 Theme name is Theme 2
Theme ID is 3 Theme name is Theme 3
Theme ID is 4 Theme name is Theme 4
2023-01-18 22:22:04.910 D/DBINFO_IN_TID_3: Game ID is 2 it has 2 THEMES. They are:-
Theme ID is 3 Theme name is Theme 3
Theme ID is 4 Theme name is Theme 4
2023-01-18 22:22:04.910 D/DBINFO_IN_TID_3: Game ID is 3 it has 4 THEMES. They are:-
Theme ID is 1 Theme name is Theme 1
Theme ID is 2 Theme name is Theme 2
Theme ID is 3 Theme name is Theme 3
Theme ID is 4 Theme name is Theme 4
2023-01-18 22:22:04.916 D/DBINFO_IN_TID_4: Game ID is 2 it has 2 THEMES. They are:-
Theme ID is 3 Theme name is Theme 3
Theme ID is 4 Theme name is Theme 4
2023-01-18 22:22:04.916 D/DBINFO_IN_TID_4: Game ID is 3 it has 4 THEMES. They are:-
Theme ID is 1 Theme name is Theme 1
Theme ID is 2 Theme name is Theme 2
Theme ID is 3 Theme name is Theme 3
Theme ID is 4 Theme name is Theme 4
2023-01-18 22:22:04.921 D/DBINFO_IN_TID_5: Game ID is 4 it has 1 THEMES. They are:-
Theme ID is 5 Theme name is Theme 5
2023-01-18 22:22:04.926 D/DBINFO_JN_TID_1: Game ID is 1 it has 2 THEMES. They are:-
Theme ID is 1 Theme name is Theme 1
Theme ID is 2 Theme name is Theme 2
2023-01-18 22:22:04.926 D/DBINFO_JN_TID_1: Game ID is 3 it has 4 THEMES. They are:-
Theme ID is 1 Theme name is Theme 1
Theme ID is 2 Theme name is Theme 2
Theme ID is 3 Theme name is Theme 3
Theme ID is 4 Theme name is Theme 4
2023-01-18 22:22:04.931 D/DBINFO_JN_TID_2: Game ID is 1 it has 2 THEMES. They are:-
Theme ID is 1 Theme name is Theme 1
Theme ID is 2 Theme name is Theme 2
2023-01-18 22:22:04.931 D/DBINFO_JN_TID_2: Game ID is 3 it has 4 THEMES. They are:-
Theme ID is 1 Theme name is Theme 1
Theme ID is 2 Theme name is Theme 2
Theme ID is 3 Theme name is Theme 3
Theme ID is 4 Theme name is Theme 4
2023-01-18 22:22:04.937 D/DBINFO_JN_TID_3: Game ID is 2 it has 2 THEMES. They are:-
Theme ID is 3 Theme name is Theme 3
Theme ID is 4 Theme name is Theme 4
2023-01-18 22:22:04.937 D/DBINFO_JN_TID_3: Game ID is 3 it has 4 THEMES. They are:-
Theme ID is 1 Theme name is Theme 1
Theme ID is 2 Theme name is Theme 2
Theme ID is 3 Theme name is Theme 3
Theme ID is 4 Theme name is Theme 4
2023-01-18 22:22:04.940 D/DBINFO_JN_TID_4: Game ID is 2 it has 2 THEMES. They are:-
Theme ID is 3 Theme name is Theme 3
Theme ID is 4 Theme name is Theme 4
2023-01-18 22:22:04.940 D/DBINFO_JN_TID_4: Game ID is 3 it has 4 THEMES. They are:-
Theme ID is 1 Theme name is Theme 1
Theme ID is 2 Theme name is Theme 2
Theme ID is 3 Theme name is Theme 3
Theme ID is 4 Theme name is Theme 4
2023-01-18 22:22:04.945 D/DBINFO_JN_TID_5: Game ID is 4 it has 1 THEMES. They are:-
Theme ID is 5 Theme name is Theme 5
то есть ожидаемые результаты (отмечая, что при использовании @Relation Room извлекает ВСЕ дочерние элементы родителя) и, что важно, оба метода (IN и JOIN) дают одинаковые результаты.
По сравнению с использованием instr
или LIKE %???%
описанный выше метод должен быть намного эффективнее (быстрее), так как избегается полное сканирование таблицы (при использовании instr
или LIKE %????%
нет другого способа, кроме полного сканирования таблицы, поскольку каждая строка должна быть обработана/проверена).
Кроме того, сравнения ТОЧНЫ.
Спасибо еще раз. Это больше касается деталей, хе-хе
См. stackoverflow.com/questions/75133104/…