If using Integer then Room expects the uid to be null for a value to be generated. If a value, such as 0 is used then that value will be used for the uid.
As the insertAll method doesn't specify an onConflict strategy, then the default strategy is ABORT. As such the use of 0, more than once will result in a crash due to the UNIQUE constraint conflict (the value as it is a Primary Key must be unique).
an onConflict strategy of IGNORE (e.g. @Insert(onConflict = OnConflictStrategy.IGNORE)) will not result in a crash if there is a duplicate, but the row will not be inserted.
an onConflict strategy of REPLACE (e.g. @Insert(onConflict = OnConflictStrategy.REPLACE)), will replace the already existing row, by deleting the row and inserting another (as opposed to updating the row)
it is often useful to know what has been done. The @Insert function can return a long/Long value.
- If a row has been inserted then the value will be the rowid (for an integer type primary key, the column is an alias of the special, normally hidden rowid column)
- If a row has not been inserted (i.e. ignored) then the value will be -1 (note that it is in fact possible to specify -1 as a value, although )
As I understood from the documentation, @PrimaryKey(autoGenerate = true) should generate a new identifier for the uid.
This documentation is misleading/false. If you were to use autoGenerate false or as it is the default just @PrimaryKey then the uid will still be generated when a null uid is specified.
However, there is a subtle difference in that when autoGenerate is true, the SQLite AUTOINCREMENT keyword is included in the table definition.
The AUTOINCREMENT (autoGenerate = true) keyword adds a constraint(rule) that the generated value MUST be greater than any used value (even if the row with the highest value has been deleted). SQLite needs a way to keep a record of the highest ever used value, it does this by using a system table called sqlite_sequence which will be created by SQLite if required.
The sqlite_sequence table will have a row per table that has AUTOINCREMENT and that is where the value is stored. There are overheads accessing and maintaining this additional table. In fact the SQLite documentation 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.
I would suggest that the most efficient/correct option is to use:-
@Entity
public class User {
@PrimaryKey
public Long uid;
@ColumnInfo(name = "first_name")
public String firstName;
@ColumnInfo(name = "last_name")
public String lastName;
}
- Long as Integer does not have the capacity to hold a 64bit signed value, Long does.
And to always ensure that the uid is null when inserting a new row.
Demonstration
Perhaps consider the following that demonstrates much of what has been stated. Noting that autoGenerate has been left to the default of false (which is suggested as being better for majority of cases)
As such the User class as above has been used throughout.
However, the UserDao interface has been expanded to cater for using the 3 discussed/mentioned conflict strategies (ABORT, IGNORE and REPLACE):-
@Dao
public interface UserDao {
@Query("SELECT * FROM user")
List<User> getAll();
@Query("SELECT * FROM user WHERE uid IN (:userIds)")
List<User> loadAllByIds(int[] userIds);
@Query("SELECT * FROM user WHERE first_name LIKE :first AND " +
"last_name LIKE :last LIMIT 1")
User findByName(String first, String last);
@Insert
Long insertAll(User user);
@Insert(onConflict = OnConflictStrategy.REPLACE)
long insertAllWithReplace(User user);
@Insert(onConflict = OnConflictStrategy.IGNORE)
long insertAllWithIgnore(User user);
@Delete
void delete(User user);
}
To facilitate using the above an @Database annotated abstract class TheDatabase :-
@Database(entities = {User.class}, exportSchema = false, version = 1)
abstract class TheDatabase extends RoomDatabase {
abstract UserDao getUserDao();
private static volatile TheDatabase instance;
static TheDatabase getInstance(Context context) {
if (instance==null) {
instance= Room.databaseBuilder(context,TheDatabase.class,"the_database.db")
.allowMainThreadQueries()
.build();
}
return instance;
}
}
Lastly some activity code to demonstrate various inserts with functions to :-
- write the data currently in the database to the log logAllUsers, and
- to insert a row, specifying the conflict strategy and capturing any failure and also writing the result (returned rowid value) of the insert to the log.
MainActivity :-
public class MainActivity extends AppCompatActivity {
TheDatabase db;
UserDao dao;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
/* Prepare to use the database and daos */
db = TheDatabase.getInstance(this);
dao = db.getUserDao();
/* Prepare the 1 User that will be used throughout */
User newUser = new User();
newUser.firstName="Fred";
newUser.lastName="Bloggs";
/* Stage 1 insert with generated uid and ABORT conflict strategy */
dao.insertAll(newUser);
logAllUsers("_STAGE1");
/* AS uid is null then the following will add 3 new rows */
for (int i = 0; i < 3; i++ ) {
dao.insertAll(newUser);
}
logAllUsers("_STAGE2");
/* SHOULD NOT REALLY BE USED as the uid value will be 0*/
newUser.uid=0;
dao.insertAll(newUser);
logAllUsers("_STAGE3");
/* More in-depth debugging of inserts */
/* duplicate use of 0 uid with ABORT */
logAttemptedInsert(newUser,"_STAGE4", OnConflictStrategy.ABORT);
/* duplicate use of 0 uid with REPLACE */
logAttemptedInsert(newUser,"_STAGE5", OnConflictStrategy.REPLACE);
/* duplicate use of 0 uid with IGNORE */
logAttemptedInsert(newUser,"_STAGE6", OnConflictStrategy.IGNORE);
/* Negative uid ~~~ BEST TO NOT USE ~~~ */
newUser.uid = -100;
logAttemptedInsert(newUser,"_STAGE_7",OnConflictStrategy.IGNORE);
}
void logAllUsers(String tagSuffix) {
for(User u: dao.getAll()) {
Log.d("DBINFO" + tagSuffix,"User ID is " + u.uid + " FirstName is " + u.firstName + " LastName is " + u.lastName);
}
}
void logAttemptedInsert(User user, String tagSuffix, Integer onConflictStrategy) {
long insertedRowid= -888;
try {
switch(onConflictStrategy) {
case OnConflictStrategy.IGNORE:
insertedRowid = dao.insertAllWithIgnore(user);
break;
case OnConflictStrategy.REPLACE:
insertedRowid = dao.insertAllWithReplace(user);
break;
default:
insertedRowid = dao.insertAll(user);
break;
}
} catch (SQLiteException e) {
Log.d("DBINFO_" + tagSuffix,"SQLITE EXCEPTION TRAPPED. Exception Message=\n\t" + e.getMessage());
insertedRowid = -999;
} finally {
Log.d("DBINFO" + tagSuffix,"INSERT RESULT for USER WITH ID as " + user.uid + " FIRSTNAME as " + user.firstName + " and LASTNAME as " + user.lastName + " WAS " + insertedRowid);
logAllUsers(tagSuffix);
}
}
}
- Note that the above is designed to just be run the once.
Results of running the Demo
When run the output to the log includes:-
2023-04-27 11:02:00.108 D/DBINFO_STAGE1: User ID is 1 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.133 D/DBINFO_STAGE2: User ID is 1 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.134 D/DBINFO_STAGE2: User ID is 2 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.134 D/DBINFO_STAGE2: User ID is 3 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.134 D/DBINFO_STAGE2: User ID is 4 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.141 D/DBINFO_STAGE3: User ID is 0 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.141 D/DBINFO_STAGE3: User ID is 1 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.141 D/DBINFO_STAGE3: User ID is 2 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.141 D/DBINFO_STAGE3: User ID is 3 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.141 D/DBINFO_STAGE3: User ID is 4 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.144 D/DBINFO__STAGE4: SQLITE EXCEPTION TRAPPED. Exception Message=
UNIQUE constraint failed: User.uid (code 1555 SQLITE_CONSTRAINT_PRIMARYKEY)
2023-04-27 11:02:00.144 D/DBINFO_STAGE4: INSERT RESULT for USER WITH ID as 0 FIRSTNAME as Fred and LASTNAME as Bloggs WAS -999
2023-04-27 11:02:00.145 D/DBINFO_STAGE4: User ID is 0 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.145 D/DBINFO_STAGE4: User ID is 1 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.145 D/DBINFO_STAGE4: User ID is 2 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.145 D/DBINFO_STAGE4: User ID is 3 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.146 D/DBINFO_STAGE4: User ID is 4 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.148 D/DBINFO_STAGE5: INSERT RESULT for USER WITH ID as 0 FIRSTNAME as Fred and LASTNAME as Bloggs WAS 0
2023-04-27 11:02:00.150 D/DBINFO_STAGE5: User ID is 0 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.150 D/DBINFO_STAGE5: User ID is 1 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.151 D/DBINFO_STAGE5: User ID is 2 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.151 D/DBINFO_STAGE5: User ID is 3 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.151 D/DBINFO_STAGE5: User ID is 4 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.153 D/DBINFO_STAGE6: INSERT RESULT for USER WITH ID as 0 FIRSTNAME as Fred and LASTNAME as Bloggs WAS -1
2023-04-27 11:02:00.155 D/DBINFO_STAGE6: User ID is 0 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.155 D/DBINFO_STAGE6: User ID is 1 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.156 D/DBINFO_STAGE6: User ID is 2 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.156 D/DBINFO_STAGE6: User ID is 3 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.156 D/DBINFO_STAGE6: User ID is 4 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.157 D/DBINFO_STAGE_7: INSERT RESULT for USER WITH ID as -100 FIRSTNAME as Fred and LASTNAME as Bloggs WAS -100
2023-04-27 11:02:00.160 D/DBINFO_STAGE_7: User ID is -100 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.161 D/DBINFO_STAGE_7: User ID is 0 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.161 D/DBINFO_STAGE_7: User ID is 1 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.161 D/DBINFO_STAGE_7: User ID is 2 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.161 D/DBINFO_STAGE_7: User ID is 3 FirstName is Fred LastName is Bloggs
2023-04-27 11:02:00.161 D/DBINFO_STAGE_7: User ID is 4 FirstName is Fred LastName is Bloggs
- Stage 1 has inserted the single row and the uid has been generated with a value of 1 as explained/expected.
- Stage 2 has inserted the additional 3 rows with generated uid values (2-4 inclusive), as explained/expected.
- Stage 3 has inserted another row with a specific uid value of 0, as explained/expected.
- Stage 4 has NOT inserted another row with 0 as the specified uid value, the default onConflict strategy of ABORT resulted in an exception which was trapped and reported on. The 4 existing rows (0-4 inclusive) are the only rows that exist, as per the code -999 was returned to indicate the trapped exception.
- Stage 5 has REPLACED row 0 returning 0 as the inserted rowid (note not -1 indicating nothing inserted). As such again all 5 rows exist.
- Stage 6 has IGNORED the insert as a row with a uid of 0 already exists. Unlike Stage 4 there was no underlying exception so -1 has been returned, typically indicating nothing inserted. Still just the 5 rows exist.
- Stage 6 has inserted a row with a negative value of -100 and is the value returned from the insert (if -1 were used then you would not know if the row were inserted or not according to the returned -1). 6 rows now exist.
App Inspection
Using App Inspection then the end result appears to confirm the end result of 6 rows as per:-
using a New Query from App Inspection the absence of sqlite_master can be seen as per:-
To show this hidden aliased rowid then:-
Notes
- With Room only tables with rowid are supported via annotations. However, SQLite does support WITHOUT ROWID tables, in which case
AUTOINCREMENT cannot be used.
AUTOINCREMENT aka autoGenerate=true, due to the rule that a generated row MUST be greater than any existing row, will result in an SQLITE_FULL exception if the highest value allowable for the generated value (9223372036854775807), whilst without AUTOINCREMENT the algorithm will attempt to use an unused value (if a negative rowid has been used then this can be a negative value) thus it is unlikely that an SQLITE_FULL exception will result.
