I am working in an android app which stores data in room database from remote server and then data is shown from Room DB itself.
My problem is that I am using
autoIncrementfor primary key field and I want to replace old data with new data from remote server usingOnConflictStrategy.REPLACEand I have written the code for it.But how data will get replaced because always
autoIncrementid will be different and data will inserted everytime instead of replace.
CodePudding user response：
But how data will get replaced because always autoIncrement id will be different and data will inserted everytime instead of replace. Noting that the id will then be changed.
For the replace strategy to work you must ascertain the row associated with the data. You would do this based upon data other then the id that uniquely identifies the row.
However, then using replace is obsolete and inefficient as knowing how to uniquely identify the row means that you can use an UPDATE (not an @Update but an @Query("UPDATE thetable SET the_first_changed_column = the_new value, the_next_changed_column_if one = the the_new_value /* more comma separated columns */ WHERE a_where_clause_that_uniquely_identifies_row")).
An @Update is based upon the primary key value of the passed object.
REPLACE is inefficient as it works by deleting the existing row, which then requires the index the be altered accordingly, and then inserting a new row (which as shown below may have the same id if the id has been specified (see notes about the third block)), which requires the index to be altered accordingly.
As there is is this "other data" that should uniquely identify a row, it would be advantageous to have an index on those columns using the indicies = parameter of the @Entity annotation, obviously with the unique parameter of the respective Index being true.
- As an example where columns lastTime along with lastMessage uniquely identifies a row then you could have
indices = [Index(value = ["lastTime","lastMessage"], unique = true, name ="optional_name_of_index")]- name is optional
Working Example/Demo
The following is an example that demonstrates much of the above.
It is based upon a single @Entity annotated class Example :-
@Entity(
indices = [
Index( value = ["serverName","itemCode"], unique = true)
]
)
data class Example(
@PrimaryKey
var id: Long? = null,
val serverName: String, /* UID Part 1 */
val itemCode: String, /* UID part 2 */
var otherData1: String,
var otherData2: String
)
var used for id, otherData1 and otherData2 so the values can be changed.
as can be seen the index has been added for the two columns that should be unique.
note that there is no need for autogenerate = true, in fact this is inefficient as it introduces the SQLite AUTOINCREMENT keyword. However, you do need the id value to be NULL as opposed to 0.
- The very first paragraph of the link regrading AUTOINCREMENT says "The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed."
An @Dao annotated class AllDAO for insertion, the suggested @Query for updating according to the unique data and an @Query for extracting data :-
@Dao
interface AllDAO {
@Insert(onConflict = OnConflictStrategy.REPLACE)
fun insert(example: Example): Long
@Query("UPDATE example SET otherData1=:newOtherData1, otherData2=:newOtherData2 WHERE serverName=:serverName AND itemCode=:itemCode")
fun update(serverName: String, itemCode: String, newOtherData1: String, newOtherData2: String): Int
@Query("SELECT * FROM example")
fun getAllExamples(): List<Example>
}
- note the return values
- the long from the insert will be the generated id
- the Int from the update will be the number of updated rows, although this is not retrieved, it should be 1
An @Database annotated class so that an actual demo run can be run :-
@Database(entities = [Example::class], version = 1, exportSchema = false)
abstract class TheDatabase: RoomDatabase() {
abstract fun getAllDAO(): AllDAO
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()
.build()
}
return instance as TheDatabase
}
}
}
Finally activity code that:-
- inserts some data
- updates the data using REPLACE
- updates the data using the suggested UPDATE
- extracts the data from the database and in some cases from the current Example objects to give an idea of what is happening
:-
class MainActivity : AppCompatActivity() {
lateinit var db: TheDatabase
lateinit var dao: AllDAO
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
db = TheDatabase.getInstance(this)
dao = db.getAllDAO()
var ex1 = Example(serverName = "S1", itemCode = "I1", otherData1 = "AA", otherData2 = "BB")
var ex2 = Example(serverName = "S1", itemCode = "I2", otherData1 = "CC", otherData2 = "DD")
var ex3 = Example(serverName = "S1", itemCode = "I3", otherData1 = "EE", otherData2 = "FF")
ex1.id = dao.insert(ex1)
logAnExampleObject(ex1,"STAGE_1A") /* write the ex1 object to the log NOT from the db */
ex2.id = dao.insert(ex2)
logAnExampleObject(ex2,"STAGE_1B")
ex3.id = dao.insert(ex3)
logAnExampleObject(ex3,"STAGE_1C")
logAllExamples("_STAGE1Z") /* write all examples as extracted from the db */
ex1.otherData1 = "AAAA"
ex1.otherData2 = "BBBB"
dao.insert(ex1) /* NOTE ID AS PER INSERT due to updating value when inserted*/
logAllExamples("_STAGE2A")
ex1.id = null
ex1.otherData1 = "AAAAAA"
ex1.otherData2 = "BBBBBB"
dao.insert(ex1) /* NOTE ID set to NULL so as to use generated id */
logAllExamples("_STAGE2B")
dao.update("S1","I2","CCCCCC","DDDDDD")
logAllExamples("_STAGE3")
}
fun logAllExamples(prefix: String) {
for (ex in dao.getAllExamples()) {
logAnExampleObject(ex,prefix)
}
}
private fun logAnExampleObject(ex: Example, prefix: String) {
Log.d("USERINFO${prefix}","ID is ${ex.id} Server is ${ex.serverName} ItemCode is ${ex.itemCode} D1 is ${ex.otherData1} D2 is ${ex.otherData2}")
}
}
Results
When run for the first time, then the log includes :-
2022-04-30 15:32:30.017 6973-6973/a.a.so72006844kotlinroomrelations D/USERINFOSTAGE_1A: ID is 1 Server is S1 ItemCode is I1 D1 is AA D2 is BB
2022-04-30 15:32:30.019 6973-6973/a.a.so72006844kotlinroomrelations D/USERINFOSTAGE_1B: ID is 2 Server is S1 ItemCode is I2 D1 is CC D2 is DD
2022-04-30 15:32:30.024 6973-6973/a.a.so72006844kotlinroomrelations D/USERINFOSTAGE_1C: ID is 3 Server is S1 ItemCode is I3 D1 is EE D2 is FF
2022-04-30 15:32:30.033 6973-6973/a.a.so72006844kotlinroomrelations D/USERINFO_STAGE1Z: ID is 1 Server is S1 ItemCode is I1 D1 is AA D2 is BB
2022-04-30 15:32:30.033 6973-6973/a.a.so72006844kotlinroomrelations D/USERINFO_STAGE1Z: ID is 2 Server is S1 ItemCode is I2 D1 is CC D2 is DD
2022-04-30 15:32:30.033 6973-6973/a.a.so72006844kotlinroomrelations D/USERINFO_STAGE1Z: ID is 3 Server is S1 ItemCode is I3 D1 is EE D2 is FF
2022-04-30 15:32:30.037 6973-6973/a.a.so72006844kotlinroomrelations D/USERINFO_STAGE2A: ID is 1 Server is S1 ItemCode is I1 D1 is AAAA D2 is BBBB
2022-04-30 15:32:30.037 6973-6973/a.a.so72006844kotlinroomrelations D/USERINFO_STAGE2A: ID is 2 Server is S1 ItemCode is I2 D1 is CC D2 is DD
2022-04-30 15:32:30.037 6973-6973/a.a.so72006844kotlinroomrelations D/USERINFO_STAGE2A: ID is 3 Server is S1 ItemCode is I3 D1 is EE D2 is FF
2022-04-30 15:32:30.049 6973-6973/a.a.so72006844kotlinroomrelations D/USERINFO_STAGE2B: ID is 2 Server is S1 ItemCode is I2 D1 is CC D2 is DD
2022-04-30 15:32:30.049 6973-6973/a.a.so72006844kotlinroomrelations D/USERINFO_STAGE2B: ID is 3 Server is S1 ItemCode is I3 D1 is EE D2 is FF
2022-04-30 15:32:30.049 6973-6973/a.a.so72006844kotlinroomrelations D/USERINFO_STAGE2B: ID is 4 Server is S1 ItemCode is I1 D1 is AAAAAA D2 is BBBBBB
2022-04-30 15:32:30.054 6973-6973/a.a.so72006844kotlinroomrelations D/USERINFO_STAGE3: ID is 2 Server is S1 ItemCode is I2 D1 is CCCCCC D2 is DDDDDD
2022-04-30 15:32:30.054 6973-6973/a.a.so72006844kotlinroomrelations D/USERINFO_STAGE3: ID is 3 Server is S1 ItemCode is I3 D1 is EE D2 is FF
2022-04-30 15:32:30.054 6973-6973/a.a.so72006844kotlinroomrelations D/USERINFO_STAGE3: ID is 4 Server is S1 ItemCode is I1 D1 is AAAAAA D2 is BBBBBB
- The first block are the objects, with the id updated with the generated id from the insert.
- The second block the data as extracted from the database
- The third block when inserting using REPLACE strategy but with the id column specified, as the row is deleted, due to the UNIQUE constraint conflict, prior to the insert, the row is inserted with the same id.
- The fourth block shows the effect when the id is not specified, that is the original row is deleted and the new row inserted to REPLACE
- The fifth block showing that the UPDATE method works as expected.