Do I have a way to search objects on Room Database

Question

I am now building an Android App with Local Database

The table structure is like following (coming from API)

@Entity
data class Person(
 name: Name,
 ... ... ...
 ... ... ...
)
data class Name(
 legalName: String.
 common: String
)

This is sql code I have tried to person with legal name

@Query("SELECT * FROM person WHERE name.legalName = :legalName")
suspend fun getPersonByName (legalName: String): Person?

This gave me compile error as we can't search by name.legalName on Room database In addition, we have static name list of person (only legal name) in Homepage (No ID or other reasonable fields to perform search)

DO we have proper way to search Users with legalName field?

Answer 1

The @Entity annotation is used by Room to determine the underlying SQLite table schema. A class so annotated is an object but the individual fields/members of the object are stored as columns in the table which are not objects.

Such columns can never be anything other than specific types being either:-

• integer type values (e.g. Int, Long .... Boolean) (column type of INTEGER)
• string type values (e.g. String) (column type of TEXT)
• decimal/floating point type values (e.g, Float, Double) (column type REAL)
• bytestream type values (e.g. ByteArray) (column type BLOB)
• null (column definition must not have NOT NULL constraint)

Thus, objects are NOT stored or storable directly SQLite has no concept/understanding of objects just columns grouped into tables.

In your case the name field is a Name object and Room will require 2 Type Converters:-

• One that converts the object into one of the above that can represent the object (typically a json representation of the object)
• The other to convert the stored data back into the Object.
• This allowing an object to be represented in a single column.

As such to query a field/member of the object you need to consider how it is represented and searched accordingly.

There will not be a name.legalName column just a name column and the representation depends upon the TypConverter as then would the search (WHERE clause).

Now consider the following based upon your code:-

@Entity
data class Person(
    @PrimaryKey
    var id: Long?=null,
    var name: Name,
    @Embedded /* Alternative */
    var otherName: Name
)
data class Name(
    var legalName: String,
    var common: String
)

• PrimaryKey added as required by Room
• @Embedded as an alternative that copies the fields/members (legalName and common as fields)

Thus the name column will require TypeConverters as per a class with each of the 2 annotated twith @TypeConverter (note singular), the class where the Type Converters are defined has to be defined (see the TheDatabase class below). So :-

class TheTypeConverters {
    /* Using Library as per dependency implementation 'com.google.code.gson:gson:2.10.1' */
    @TypeConverter
    fun convertFromNameToJSONString(name: Name): String = Gson().toJson(name)
    @TypeConverter
    fun convertFromJSONStringToName(jsonString: String): Name = Gson().fromJson(jsonString,Name::class.java)
}

• note that there are other Gson libraries that may offer better functionality.

The entities (just the one in this case) have to be defined in the @Database annotation for the abstract class that extends RoomDatabase(). so:-

@TypeConverters(value = [TheTypeConverters::class])
@Database(entities = [Person::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getTheDAOs(): TheDAOs

    companion object {
        private var instance: TheDatabase? = null
        fun getInstance(context: Context): TheDatabase {
            if (instance == null) {
                instance = Room.databaseBuilder(context,TheDatabase::class.java,"the_database.db")
                    .allowMainThreadQueries() /* For brevity convenience of the demo */
                    .build()
            }
            return  instance as TheDatabase
        }
    }
}

• The @TypeConverters annotation (plural) in addition to defining a class or classes where the TypeConverters are, also defines the scope (@Database being the most encompassing scope).

At this stage the project can be compiled (CTRL + F9) and the annotation processing will generate some code. Importantly TheDatabase_Impl in the java(generated) The name being the same as the @Database annotated class suffixed with _Impl. This includes a method createAllTables which is the SQL used when creatin the SQLite tables. The SQL for the person table is:-

CREATE TABLE IF NOT EXISTS `Person` (
    `id` INTEGER, 
    `name` TEXT NOT NULL, 
    `legalName` TEXT NOT NULL, 
    `common` TEXT NOT NULL, PRIMARY KEY(`id`)
)

As can be seen the id column as the primary key, the name column for the converted representation of the name object and then the legal and common columns due to the name object being @Embedded via the otherName field.

Just to finish matters with the following @Dao annotated interface (allowing some data to be added):-

@Dao
interface TheDAOs {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(person: Person): Long

    @Query("SELECT * FROM person")
    fun getAllPersonRows(): List<Person>
}

And with MainActivity as:-

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()
        dao.insert(Person(null, name =  Name("Frederick Bloggs","Fred Bloggs"), otherName = Name("Frederick ","Fred Bloggs")))
        dao.insert(Person(null, name =  Name("Jane Doe","Jane Doe"), otherName = Name("Jane Doe","Jane Doe")))
    }
}

and the project run and then App Inspection used to view the actual database then:-

The name column contains the string {"common":"Fred Bloggs","legalName":"Frederick Bloggs"}

So the WHERE clause to locate all legal names that start with Fred could be

WHERE instr(name,',\"legalName\":\"Fred') or WHERE name LIKE '%,\"legalName\":\"Fred%'

• it should be noted that both due to the search being within a column requires a full scan.

• Of course that assumes that there is no name that has the common name ,"legalName":"Fred or as part of the common name or some other part of entire string. i.e. it can be hard to anticipate what results may be in the future.

For the alternative @Embedded Name object, the legalName and common columns are more easily searched, the equivalent search for legal names starting with Fred could be

WHERE legalname LIKE 'Fred%'

There is no potential whatsoever for Fred appearing elsewhere meeting the criteria. The search just on the single column/value nothing else. Indexing the column would very likely improve the efficiency.

Amending the @Dao annotated interface TheDAOs to be:-

@Dao
interface TheDAOs {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(person: Person): Long

    @Query("SELECT * FROM person WHERE instr(name,',\"legalName\":\"Fred')")
    fun getPersonsAccordingToLegalNameInNameObject(): List<Person>

    @Query("SELECT * FROM person WHERE legalName LIKE 'Fred%'")
    fun getPersonsAccordingToLegalName(): List<Person>
}

And MainActivity to be:-

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()
        dao.insert(Person(null, name =  Name("Frederick Bloggs","Fred Bloggs"), otherName = Name("Frederick ","Fred Bloggs")))
        dao.insert(Person(null, name =  Name("Jane Doe","Jane Doe"), otherName = Name("Jane Doe","Jane Doe")))
        logPersonList(dao.getPersonsAccordingToLegalNameInNameObject(),"RUN1")
        logPersonList(dao.getPersonsAccordingToLegalName(),"RUN2")
    }

    private fun logPersonList(personList: List<Person>, suffix: String) {
        for (p in personList) {
            Log.d("DBINFO_${suffix}","Person ID is ${p.id} Name.legalName is ${p.name.legalName} Name.common is ${p.name.common} LegalName is ${p.otherName.legalName} Common is ${p.otherName.common}")
        }
    }
}

Then running (first time after install) the log contains:-

2023-01-14 11:26:03.738 D/DBINFO_RUN1: Person ID is 1 Name.legalName is Frederick Bloggs Name.common is Fred Bloggs LegalName is Frederick  Common is Fred Bloggs
2023-01-14 11:26:03.740 D/DBINFO_RUN2: Person ID is 1 Name.legalName is Frederick Bloggs Name.common is Fred Bloggs LegalName is Frederick  Common is Fred Bloggs

i.e. in this limited demo the expected results either way.

• Note that Name.legalName and Name.common is not how the data is accessed, it is just text used to easily distinguish then similar values.