Room Database — Classes learnt from working with a number of tables | by Eric N | Jan, 2023 | Fantasy Tech

very practically Room Database — Classes learnt from working with a number of tables | by Eric N | Jan, 2023 will cowl the most recent and most present suggestion not far off from the world. entry slowly suitably you comprehend competently and appropriately. will deposit your data effectively and reliably

On this article, I will study three strategies for querying a number of tables without delay, particularly multi-table question, multi-map, and @Relation annotated knowledge class and their execs and cons by means of a pattern utility. of questions and solutions. I am going to begin by exhibiting how one can question solutions totally free textual content questions after which transfer on to a tougher use case: a number of selection solutions.

As of Room model 2.4.3, there are 3 strategies to question a number of database tables without delay

benefits

  • It has been obtainable since model 1.1
  • Dependable and fewer complicated mixture logic/magic underneath the hood
  • Easy

cons

  • Additional knowledge class required
  • The additional knowledge class could be giant if we want a number of columns

benefits

  • No want for further lessons
  • seemingly intuitive

cons

  • Comparatively new, since Room 2.4.0
  • Just a little logic/magic underneath the hood
  • It doesn’t work for 3 tables or extra. We are going to discover such limitations in our pattern app!

benefits

  • Extra knowledge lessons are required
  • Every class is small even when we want a number of columns (not like multi-table question)

cons

  • It has been obtainable since 1.0
  • reliable
  • It really works for 3 tables or extra.
  • It really works for nested relationships; nevertheless, warning ought to be exercised by way of efficiency.
  • Just a little logic/magic underneath the hood

What higher strategy to study by doing? With the intention to study the nuances of multi-table room session, we’ll use a easy query and reply (Q&A) utility.

Our Q&A app helps text-based and choice-based questions.

An instance of a text-based query is “What’s your favourite meals?” An instance of a a number of selection query is “Relationships (priorities?)” to which the consumer can reply “Communication”, “Compassion”, “Collaboration” and/or “Dedication”.

The information buildings and their relationships are as follows:

We’ll begin with the only use case of studying textual content solutions from our Room database, after which transfer on to a tougher use case: a number of selection solutions.

The supply code is at https://github.com/ericntd/myqa

The multi-table question matches effectively right here due to its simplicity and the small variety of columns we want.

@Question("SELECT query.textual content as query, reply.text_value as reply FROM query, reply WHERE query.id = reply.question_id AND reply.option_id = ''")
enjoyable readTextAnswers(): Circulation<Checklist<TextAnswer>>

With the additional class TextAnswer

knowledge class TextAnswer(
val query: String,
val reply: String?
)

The outcomes 🎊

Supply code: Take a look at the text-based questions department

In fact, we will obtain the identical outcomes with a number of maps and @Relationship, however these approaches could be too sophisticated for our easy use case.

Let’s begin by exhibiting what choices the consumer can select for every query.

And let’s begin with the newer and seemingly extra intuitive technique which is Multi-map.

@Question("SELECT * from query LEFT JOIN choice ON question_id")
enjoyable readMcqs(): Circulation<Map<QuestionEntity, Checklist<OptionEntity>>>

No further class wanted

The outcomes

Wow, the whole lot seems messy. Notice that the daring textual content is meant to be the questions like Relationships, however as an alternative, they look like the choices obtainable for these questions.

It is because Room Multi-Map can not help tables with the identical column names presently. In our case, each the query and choice tables have the identical major key “id”. The answer is to make the column names distinctive!

One we modify our knowledge lessons to

@Entity(tableName = "query")
knowledge class QuestionEntity(
@PrimaryKey @ColumnInfo(title = "question_id") val id: String,
@ColumnInfo(title = "question_text") val textual content: String
)

Y

@Entity(tableName = "choice")
knowledge class OptionEntity(
@PrimaryKey @ColumnInfo(title = "option_id") val id: String,
@ColumnInfo(title = "question_id") val questionId: String,
@ColumnInfo(title = "option_text") val textual content: String,
val humanId: String?
)

Now it really works!

Supply code: try a number of selection questions department, a number of maps, fastened duplicate columns

Underneath the hood, the Room compiler generates some good previous Cursor code that extracts the Query and Possibility objects from the outcomes of our SQL question:

@Override
public Circulation<Map<QuestionEntity, Checklist<OptionEntity>>> readMcqs() {
closing String _sql = "SELECT * from query INNER JOIN choice ON choice.question_id = query.question_id";
closing RoomSQLiteQuery _statement = RoomSQLiteQuery.purchase(_sql, 0);
return CoroutinesRoom.createFlow(__db, false, new String[]"query","choice", new Callable<Map<QuestionEntity, Checklist<OptionEntity>>>()
@Override
public Map<QuestionEntity, Checklist<OptionEntity>> name() throws Exception
closing Cursor _cursor = DBUtil.question(__db, _statement, false, null);
strive
closing int _cursorIndexOfId = CursorUtil.getColumnIndexOrThrow(_cursor, "question_id");
// different columns…
closing Map<QuestionEntity, Checklist<OptionEntity>> _result = new LinkedHashMap<QuestionEntity, Checklist<OptionEntity>>();
whereas (_cursor.moveToNext())
// further Query and Possibility and put into _result…

return _result;
lastly
_cursor.shut();


@Override
protected void finalize()
_statement.launch();

);
}

I’ve observed that there are some bizarre unused cursor indices, like closing int _cursorIndexOfId_1 = CursorUtil.getColumnIndexOrThrow(_cursor, “question_id”) however they most likely simply make the generated Room code tougher to learn slightly than trigger an precise useful downside.

Sadly, Multi-map does not appear to help this kind of utilization in the meanwhile. I hope future variations of Multi-map will help querying 3 associated objects.

I’ve tried the next:

@Question("SELECT * FROM (SELECT * from query INNER JOIN choice ON choice.question_id = query.question_id) AS q INNER JOIN reply ON reply.question_id = q.question_id")
enjoyable readMcqAnswers2(): Circulation<Map<Map<QuestionEntity, Checklist<OptionEntity>>, Checklist<AnswerEntity>>>

And Room was unable to generate code to satisfy the necessities:

error: Undecided how one can convert a Cursor to this technique's return kind (kotlinx.coroutines.movement.Circulation<java.util.Map<java.util.Map<app.ericn.myqa.QuestionEntity, java.util.Checklist<app.ericn.myqa.OptionEntity>>, java.util.Checklist<app.ericn.myqa.AnswerEntity>>>).
public summary kotlinx.coroutines.movement.Circulation<java.util.Map<java.util.Map<app.ericn.myqa.QuestionEntity, java.util.Checklist<app.ericn.myqa.OptionEntity>>, java.util.Checklist<app.ericn.myqa.AnswerEntity>>> readMcqAnswers2();

Equally, Room additionally does not know how one can generate code for the next:

@Question("SELECT * FROM query")
enjoyable readMcqAnswers5(): Circulation<Map<Map<QuestionEntity, Checklist<OptionEntity>>, Checklist<AnswerEntity>>>
@Question("SELECT * FROM query INNER JOIN (SELECT * FROM choice INNER JOIN reply ON reply.option_id = choice.option_id) as a ON a.question_id = query.question_id")
enjoyable readMcqAnswers3(): Circulation<Map<QuestionEntity, Map<OptionEntity, AnswerEntity>>>
@Question("SELECT * FROM query")
enjoyable readMcqAnswers4(): Circulation<Map<QuestionEntity, Map<OptionEntity, AnswerEntity>>>

Lastly, I resorted to the basic @Relation annotation technique and it met our necessities right here:

// the Entity
knowledge class QuestionWithRelations(
@Embedded
val query: QuestionEntity,
@Relation(
parentColumn = "question_id",
entityColumn = "question_id"
)
val choices: Checklist<OptionEntity>,
@Relation(
parentColumn = "question_id",
entityColumn = "question_id"
)
val solutions: Checklist<AnswerEntity>
)

// The DAO
@Question("SELECT * FROM query")
enjoyable readMcqAnswers1(): Circulation<Checklist<QuestionWithRelations>>

Supply code: See a number of selection questions and solutions department

Underneath the hood, Room additionally generates code that takes benefit of the nice previous SQLite Cursor, however I discover it extra readable in comparison with Multi-map’s code. I do not see any motive why future iterations of Multi-map cannot match the @Relation and @Embedded performance for 3 objects. It is unclear how frequent such a requirement is and the place it ranks on the Room improvement group’s precedence desk.

If every response features a listing of choice ids, would the logic be easier? Let me know your ideas in a reply.

A number of SQL queries are slower than a single SQL question

Concurrent SQL queries aren’t essentially quicker than sequential queries!

As all the time, there isn’t a silver bullet. You need to select the device that most closely fits your wants and limitations. In our utility we presently make use of the three totally different strategies

  1. Multi-table queries for easy text-based questions and solutions
  2. A number of map for two object relationships, eg profiles and photographs
  3. @Relation and @Embedded for 3 object relationships, eg Questions, Choices and Solutions

https://github.com/ericntd/myqa

Kudos to Christa Mabee for sharing the existence of the Room Multi-Map and her exploratory work on it. Thanks very a lot additionally for correcting this text.

I want the article roughly Room Database — Classes learnt from working with a number of tables | by Eric N | Jan, 2023 provides perception to you and is beneficial for surcharge to your data

Room Database — Lessons learnt from working with multiple tables | by Eric N | Jan, 2023

Leave a Reply