Reputation: 61
Room database with one-to-one relation like Address, City and State
I looked in the android documentation for an answer to my question, but I couldn't find it. To create a recyclerview using the information contained in these classes, how can I get a list of this information in Room
@Entity(
foreignKeys = [
ForeignKey(
entity = City::class,
parentColumns = arrayOf("id"),
childColumns = arrayOf("cityfk"),
onDelete = ForeignKey.NO_ACTION
)
]
)
data class Address(
@PrimaryKey
@ColumnInfo
var id: Long = 0
) : Serializable {
@ColumnInfo
var name: String = ""
@ColumnInfo(index = true)
var cityfk: Long = 0
}
@Entity(
foreignKeys = [
ForeignKey(
entity = State::class,
parentColumns = arrayOf("id"),
childColumns = arrayOf("statefk"),
onDelete = ForeignKey.NO_ACTION
)
]
)
data class City(
@PrimaryKey
@ColumnInfo
var id: Long = 0
) : Serializable {
@ColumnInfo
var name: String = ""
@ColumnInfo(index = true)
var statefk: Long = 0
}
@Entity
data class State(
@PrimaryKey
@ColumnInfo
var id: Long = 0
) : Serializable {
@ColumnInfo
var name: String = ""
}
How can I get a list of addresses listing the classes?
How to get a result like this in ANSI SQL:
select ADDRESS.NAME ADDRESS
, CITY.NAME CITY
, STATE.NAME STATE
from ADDRESS
join CITY
on CITY.ID = ADDRES.CITYFK
join STATE
on STATE.ID = CITY.STATEFK
Upvotes: 0
Views: 255
Answers (1)
Reputation: 57043
You would typically have a POJO to represent the combined data. You can then either have a field/variable for the extracted columns noting that values are matched to the liked named variable.
You can use @Embedded to include an entity in it's entirety so in theory embed Address City and State.
- see variable/column name issues
You can use @Embedded along with @Relation for the child (children) BUT not for grandchildren (e.g. State). You would need an underlying City with State POJO where City is embedded and State is related by an @Relation.
- variable/column names are not an issue when using @Relation as room builds underlying queries from the parent.
Variable/Column name issues
Room maps columns to variable according to variable names. So there will be issues with id's and name columns if using the simpler @Embedded for all three entities.
I would suggest always using unique names e.g. addressId, cityId, StateId, (at least for the column names e.g. @ColumnInfo(name = "addressId")) but simpler to just have var addressid.
An alternative is the use the @Embedded(prefix = "the_prefix") on some, this tells room to match the variable to column name with the prefix so you need to use AS in the SQL. Obviously the_prefix would be changed to suit.
The Dao's
if using @Embedded with @Relation then you simply need to get the parent so
@Query("SELECT * FROM address")
fun getAddressWithCityAndWithState(): List<AddressWithCityAndWithState>
- where AddressWithCityAndWithState is the POJO that has the Address @Embedded and the CityWithState with @Relation.
You would also need the accompanying CityWithState POJO with City @Embedded and State with @Relation.
If Embedding Address, City and State with City having a prefix of "city_" and state having a prefix of "state_" then you would use something like :-
@Query("SELECT address.*, city.id AS city_id, city.name AS city_name, state.id AS state_id, state.name AS state_name FROM address JOIN city ON address.cityfk = city.it JOIN state ON city.statefk = state.id")
fun getAddressWithCityAndWithState(): List<AddressWithCityAndWithState>
- where AddressWithCityAndWithState is the POJO that has Address, City and State @Embedded
Note the above is in-principle.
Working Example
The following is a working example based upon
- a) renaming the columns to avoid ambiguity and
- b) using @Embedded of all three classes in the POJO AddressWithCityWithState
First changes to the Address, City and State to rename the columns :-
Address :-
@Entity(
foreignKeys = [
ForeignKey(
entity = City::class,
parentColumns = arrayOf("city_id"), //<<<<<<<<<< CHANGED
childColumns = arrayOf("cityfk"),
onDelete = ForeignKey.NO_ACTION
)
]
)
data class Address(
@PrimaryKey
@ColumnInfo(name ="address_id") //<<<<<<<<<< ADDED name
var id: Long = 0
) : Serializable {
@ColumnInfo(name = "address_name") //<<<<<<<<<< ADDDED name
var name: String = ""
@ColumnInfo(index = true)
var cityfk: Long = 0
}
City :-
@Entity(
foreignKeys = [
ForeignKey(
entity = State::class,
parentColumns = arrayOf("state_id"), //<<<<<<<<<< changed
childColumns = arrayOf("statefk"),
onDelete = ForeignKey.NO_ACTION
)
]
)
data class City(
@PrimaryKey
@ColumnInfo(name = "city_id") // <<<<<<<<<< ADDED name
var id: Long = 0
) : Serializable {
@ColumnInfo(name = "city_name") //<<<<<<<<<< ADDED name
var name: String = ""
@ColumnInfo(index = true)
var statefk: Long = 0
}
State :-
@Entity
data class State(
@PrimaryKey
@ColumnInfo(name = "state_id") // ADDED name
var id: Long = 0
) : Serializable {
@ColumnInfo(name = "state_name") // ADDED name
var name: String = ""
}
Next the POJO AddressWithCityWithState :-
data class AddressWithCityWithState (
@Embedded
val address: Address,
@Embedded
val city: City,
@Embedded
val state: State
)
- due to unique column names no
prefix = ?required
A suitable DAO :-
@Query("SELECT * FROM address JOIN city on address.cityfk = city.city_id JOIN state ON city.statefk = state.state_id")
fun getAllAddressesWithCityAndWithState(): List<AddressWithCityWithState>
- simplified due to column renaming so * instead AS clauses for ambiguous column names
Using the above :-
allDao = db.getAllDao()
var state = State()
state.name = "State1"
var stateid = allDao.insert(state)
var city = City()
city.name = "City1"
city.statefk = stateid
var cityid = allDao.insert(city)
var address = Address()
address.name = "Address1"
address.cityfk = cityid
allDao.insert(address)
for(awcws: AddressWithCityWithState in allDao.getAllAddressesWithCityAndWithState()) {
Log.d("DBINFO","${awcws.address.name}, ${awcws.city.name}, ${awcws.state.name}")
}
The result in the log being :-
2021-11-22 07:43:28.574 D/DBINFO: Address1, City1, State1
Other working examples (without changing column names)
Without any changes to the Entities (Address, city and state). Here are working examples of the other options.
1- Get full address as a single string, all that is required is the query such as :-
@Query("SELECT address.name||','||city.name||','||state.name AS fullAddress FROM address JOIN city ON address.cityfk = city.id JOIN state ON city.statefk = state.id ")
fun getAddressesAsStrings(): List<String>
- of course not much use say for a drop down selector as you can't ascertain where in the database the rows came from.
2 - Basic POJO with unambiguous column names
The POJO :-
data class AddressWithCityWithState(
var address_id: Long,
var address_name: String,
var city_id: Long,
var city_name: String,
var state_id: Long,
var state_name: String
)
The query :-
/*
* Returns multiple columns renamed using AS clause to disambiguate
* requires POJO with matching column names
* */
@Query("SELECT " +
"address.id AS address_id, address.name AS address_name, " +
"city.id AS city_id, city.name AS city_name, " +
"state.id AS state_id, state.name AS state_name " +
"FROM address JOIN city ON address.cityfk = city.id JOIN state ON city.statefk = state.id")
fun getAddressesWithCityAndStateViaBasicPOJO(): List<AddressWithCityWithState>
3- POJO using EMBEDS
The POJO :-
data class AddressWithCityWithStateViaEmbeds(
@Embedded
var address: Address,
@Embedded(prefix = cityPrefix)
var city: City,
@Embedded(prefix = statePrefix)
var state: State
) {
companion object {
const val cityPrefix = "city_"
const val statePrefix = "state_"
}
}
The query :-
/*
* Returns multiple columns renamed according to the prefix=? coded in the
* @Embedded annotation
*
*/
@Query("SELECT address.*, " +
"city.id AS " + AddressWithCityWithStateViaEmbeds.cityPrefix + "id," +
"city.name AS " + AddressWithCityWithStateViaEmbeds.cityPrefix + "name," +
"city.statefk AS " + AddressWithCityWithStateViaEmbeds.cityPrefix + "statefk," +
"state.id AS " + AddressWithCityWithStateViaEmbeds.statePrefix + "id," +
"state.name AS " + AddressWithCityWithStateViaEmbeds.statePrefix + "name " +
"FROM address JOIN city ON address.cityfk = city.id JOIN state ON city.statefk = state.id")
fun getAddressesWithCityAndStateViaEmbedPOJO(): List<AddressWithCityWithStateViaEmbeds>
4- POJO's with parent EMBED and child RELATE
The POJO's :-
data class CityWithState(
@Embedded
var city: City,
@Relation(
entity = State::class,
parentColumn = "statefk",
entityColumn = "id"
)
var state: State
)
and :-
data class AddressWithCityWithStateViaRelations(
@Embedded
var address: Address,
@Relation(
entity = City::class, /* NOTE NOT CityWithState which isn't an Entity */
parentColumn = "cityfk",
entityColumn = "id"
)
var cityWithState: CityWithState
)
and the query :-
@Transaction
@Query("SELECT * FROM address")
fun getAddressesWithCityAndStateViaRelations(): List<AddressWithCityWithStateViaRelations>
- note the use of @Tranaction so the underlying queries, built by Room, are all done within a single database transaction.
Putting the above into use
The following code in an activity uses all 4 to output the same results :-
class MainActivity : AppCompatActivity() {
lateinit var db: TheDatabase
lateinit var dao: AllDao
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
val TAG: String = "DBINFO"
db = TheDatabase.getInstance(this)
dao = db.getAllDao()
var state = State(1)
state.name = "State1"
val state1Id = dao.insert(state)
state.id = 2
state.name = "State2"
val state2Id = dao.insert(state)
var city = City(10)
city.name = "City1"
city.statefk = state1Id
val city1Id = dao.insert(city)
city.id = 11
city.name = "City2"
city.statefk = state2Id
val city2Id = dao.insert(city)
city.id = 12
city.name = "City3"
city.statefk = state1Id
val city3Id = dao.insert(city)
var address = Address(100)
address.name = "Address1"
address.cityfk = city1Id
dao.insert(address)
address.id = address.id + 1
address.name = "Address2"
address.cityfk = city2Id
dao.insert(address)
address.id = address.id + 1
address.name = "Address3"
address.cityfk = city3Id
for (s: String in dao.getAddressesAsStrings()) {
Log.d(TAG + "STRG", s)
}
for (awcws: AddressWithCityWithState in dao.getAddressesWithCityAndStateViaBasicPOJO()) {
Log.d(TAG + "BASICPOJO", "${awcws.address_name}, ${awcws.city_name}, ${awcws.state_name}")
}
for (awcwsve: AddressWithCityWithStateViaEmbeds in dao.getAddressesWithCityAndStateViaEmbedPOJO()) {
Log.d(TAG + "EMBEDS","${awcwsve.address.name}, ${awcwsve.city.name}, ${awcwsve.state.name}")
}
for(awcwsvr: AddressWithCityWithStateViaRelations in dao.getAddressesWithCityAndStateViaRelations()) {
Log.d(TAG + "MIXED","${awcwsvr.address.name}, ${awcwsvr.cityWithState.city.name}, ${awcwsvr.cityWithState.state.name}")
}
}
}
The output to the log being :-
2021-11-22 12:33:54.322 D/DBINFOSTRG: Address1,City1,State1
2021-11-22 12:33:54.322 D/DBINFOSTRG: Address2,City2,State2
2021-11-22 12:33:54.324 D/DBINFOBASICPOJO: Address1, City1, State1
2021-11-22 12:33:54.324 D/DBINFOBASICPOJO: Address2, City2, State2
2021-11-22 12:33:54.326 D/DBINFOEMBEDS: Address1, City1, State1
2021-11-22 12:33:54.326 D/DBINFOEMBEDS: Address2, City2, State2
2021-11-22 12:33:54.332 D/DBINFOMIXED: Address1, City1, State1
2021-11-22 12:33:54.332 D/DBINFOMIXED: Address2, City2, State2
Upvotes: 1
Related Questions
- Implementing the Room Database (Android)
- Android Room One-to-Many Relationship
- One to many relationship in Room without Relation
- Query one to many relationship Android Room
- How to insert a "one to one" room database
- Android Room: Creating a one-to-one relationship entity
- Room database with one-to-one relation
- Android Room Relation One-to-Many-to-Many
- Room database with one-to-much relation
- One-to-many relation in Room