I'm using try catch like below code to avoid crash app when using database query :
AppExecutors.getsInstance().diskIO().execute(()->{
try{
database.classDao().insertClass(new ClassEntry(className,classDay));
}catch(SQLiteException e){
Log.e("TAG","saveClassToDatabase: " e.getMessage());
}
}
But I have many queries like this in my app and that means to I have to use many try catch.
My question : Is there a way to write try catch once and use it everywhere to handle queries error ?
Note : I use room database.
CodePudding user response：
Using try/catch and possibly finally should be not be needed that much, if at all, when using Room as:-
- SQL is typically checked for validity/correctness at compile time
- Dao methods/functions can be written to handle typical insert/update/delete conflicts using the onCoflict parameter of the respective annotation
- e.g.
@Insert(onConflict = OnConflictStrategy.IGNORE), will ignore NOT NULL, CHECK (not really applicable to Room) and UNIQUE constraint conflicts. - this can be effective if the appropriate value (long for inserts, int for updates and deletions) is returned e.g. (java)
long insert(TheObjectType the object);where the long returned will be the rowid (positive) if the row was inserted or -1 if not (an exception would be if forcing -1 as a value).int delete(TheObjectType object)where the value returned will be the number of affected rows, so if 0 nothing was deleted. Same for update except obviously 0 = nothing updated.
- e.g.
Note that the onConflict action (IGNORE as above) does not apply to Foreign Key constraints if used. These are perhaps a potential for using catch/try although checks could be undertaken prior to insertion (deletion and updates can maintain the referential integrity automatically using onDelete = ForeignKey.CASCADE and onUpdate = ForeignKey.CASCADE respectively).
If you really need to have a single catch/try then you could not utilise Room's convenience methods (@Insert, '@Update', '@Delete' and @Query but instead use (although many would say misuse) @RawQuery this is not a recommendation.
Perhaps consider this demo which covers some of the above:-
The @Entity annotated class School:-
@Entity(
indices = {
@Index(value = "schoolName", unique = true)
}
)
class School {
@PrimaryKey
Long schoolId=null;
String schoolName;
School(){}
@Ignore
School(String schoolName) {
this.schoolName = schoolName;
}
School(Long schoolId, String schoolName) {
this.schoolId = schoolId;
this.schoolName = schoolName;
}
}
- note the UNIQUE index on the School Name.
an @Dao annotated class AllDao :-
@Dao
abstract class AllDao {
@Insert(onConflict = OnConflictStrategy.IGNORE)
abstract long insert(School school);
@RawQuery
abstract Cursor rawQueryIntoCursor(SimpleSQLiteQuery simpleSQLiteQuery);
@RawQuery
abstract long rawQueryIntoLong(SimpleSQLiteQuery simpleSQLiteQuery);
@Query("SELECT * FROM school")
abstract List<School> getAllSchools();
}
- Note the use of onConflict (set to IGNORE, so duplicates would be ignored when inserting)
- Also note the 2
@RawQuerys - How these are used will be made apparent in the invoking activity (below)
an @Database annotated abstract class TheDatabase :-
@Database(entities = {School.class}, exportSchema = false, version = 1)
abstract class TheDatabase extends RoomDatabase {
abstract AllDao getAllDao();
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;
}
}
- Note allows running on the main thread for brevity.
Finally the activity code that demonstrates much that has been said above (not all though) in the answer; MainActivity :-
public class MainActivity extends AppCompatActivity {
static final String TAG = "DBINFO";
private static String insertSchoolSQL = "INSERT INTO School (schoolName) VALUES (?)";
private static String safeInsertSchoolSQL = "INSERT OR IGNORE INTO School (schoolName) VALUES(?)";
TheDatabase db;
AllDao dao;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
db = TheDatabase.getInstance(this);
dao = db.getAllDao();
/* Stage 1 insert some schools using the convenience method */
/* Note that a duplicate does not need try/catch due to IGNORE */
/* see below for the insertSchool method */
insertSchool(new School("School1"));
insertSchool(new School("School2"));
insertSchool(new School("School1")); /*<<<<<<<<< OOOPS duplicate */
/* get All the schools and write to the Log */
logAllSchools("-STAGE001");
/* Stage 2 insert some schools via the actionRawQuery method which can action many
SQL functions (limitation is obtaining results but could be enhanced)
*/
actionRawQuery(true,safeInsertSchoolSQL,new String[]{"School3"});
actionRawQuery(true,insertSchoolSQL,new String[]{"School4"});
actionRawQuery(true,safeInsertSchoolSQL,new String[]{"School3"}); /*<<<<<<<<<< OOOPS duplicate but NO EXCEPTION */
actionRawQuery(true,insertSchoolSQL,new String[]{"School3"}); /*<<<<<<<<<< OOOPS duplicate */
/* Again get All the schools and write to the Log */
logAllSchools("-STAG002");
/* other things that can be done with raw query */
Cursor csr = dao.rawQueryIntoCursor(new SimpleSQLiteQuery("PRAGMA user_version",null));
DatabaseUtils.dumpCursor(csr);
long queryResult = dao.rawQueryIntoLong(new SimpleSQLiteQuery("PRAGMA user_version",null));
Log.d(TAG,"Result from rawQueryIntoLong was " queryResult);
}
boolean insertSchool(School school) {
boolean rv = true;
if (dao.insert(school) != -1) {
Log.d(TAG,"School successfully inserted.");
} else {
Log.d(TAG,"School not inserted (duplicate or NOT NULL column is null)");
rv = false;
}
return rv;
}
void logAllSchools(String tagSuffix) {
for (School s: dao.getAllSchools()) {
Log.d(TAG tagSuffix,"SchoolName is " s.schoolName " ID is " s.schoolId);
}
}
void actionRawQuery(boolean returnAsLong,String SQL, String[] queryArgs) {
StringBuilder sb = new StringBuilder();
for (String s: queryArgs) {
sb.append("\n\t\t").append(s);
}
try {
if (returnAsLong) {
dao.rawQueryIntoLong(new SimpleSQLiteQuery(SQL,queryArgs));
} else {
dao.rawQueryIntoCursor(new SimpleSQLiteQuery(SQL,queryArgs));
}
} catch (Exception e) {
Log.d(TAG,"Issue encountered attempting RawQuery. \n\tSQL=" SQL "\n\tArguments passed=" sb "\n\tException Encountered:-" e.getMessage());
}
}
}
Results
2022-08-21 19:53:33.228 D/DBINFO: School successfully inserted.
2022-08-21 19:53:33.229 D/DBINFO: School successfully inserted.
2022-08-21 19:53:33.242 D/DBINFO: School not inserted (duplicate or NOT NULL column is null)
2022-08-21 19:53:33.251 D/DBINFO-STAGE001: SchoolName is School1 ID is 1
2022-08-21 19:53:33.251 D/DBINFO-STAGE001: SchoolName is School2 ID is 2
2022-08-21 19:53:33.256 E/SQLiteQuery: exception: UNIQUE constraint failed: School.schoolName (code 2067 SQLITE_CONSTRAINT_UNIQUE); query: INSERT INTO School (schoolName) VALUES (?)
2022-08-21 19:53:33.256 D/DBINFO: Issue encountered attempting RawQuery.
SQL=INSERT INTO School (schoolName) VALUES (?)
Arguments passed=
School3
Exception Encountered:-UNIQUE constraint failed: School.schoolName (code 2067 SQLITE_CONSTRAINT_UNIQUE)
2022-08-21 19:53:33.257 D/DBINFO-STAG002: SchoolName is School1 ID is 1
2022-08-21 19:53:33.258 D/DBINFO-STAG002: SchoolName is School2 ID is 2
2022-08-21 19:53:33.258 D/DBINFO-STAG002: SchoolName is School3 ID is 3
2022-08-21 19:53:33.258 D/DBINFO-STAG002: SchoolName is School4 ID is 4
2022-08-21 19:53:33.258 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@97d7d39
2022-08-21 19:53:33.259 I/System.out: 0 {
2022-08-21 19:53:33.259 I/System.out: user_version=1
2022-08-21 19:53:33.259 I/System.out: }
2022-08-21 19:53:33.259 I/System.out: <<<<<
2022-08-21 19:53:33.261 D/DBINFO: Result from rawQueryIntoLong was 1
As can be seen the first Stage insert 2 rows and the third was IGNORED and that no exceptions were encountered.
The second stage attempted to insert 4 rows using the actionRawQuery method (a single try/catch). Thus 2 duplicate attempts one shows the OR IGNORE (what Room codes when onConflict = OnConflictStategy.IGNORE is coded). The second duplicate, because OR IGNORE is not coded, results in a trapped/caught exception.
The following is 2 other uses of @RawQuery that retrieves the database version (user_version) using the PRAGMA user_version. The first returns the result as a Cursor the second as a long.
- This works, for long, because PRAGMA user_version will return just the one value. An exception would occur, if more than one value were returned.
Additional
It would be possible to extend the relatively simple actionRawQuery method so that it can handle multiple queries. For example:-
First a new class RawQuery :-
class RawQuery {
private boolean resultTypeLongElseCursor;
private Cursor resultCusror;
private Long resultLong;
private String actionSQL;
private String[] actionArgs;
private SimpleSQLiteQuery simpleSQLiteQuery;
private Exception exception;
RawQuery(
boolean resultTypeLongElseCusror,
String actionSQL,
String[] actionArgs
) {
this.resultTypeLongElseCursor = resultTypeLongElseCusror;
this.actionSQL = actionSQL;
this.actionArgs = actionArgs;
this.resultCusror = null;
this.resultLong = null;
}
public Cursor getResultCusror() {
return resultCusror;
}
public void setResultCusror(Cursor resultCusror) {
this.resultCusror = resultCusror;
}
public Long getResultLong() {
return resultLong;
}
public void setResultLong(Long resultLong) {
this.resultLong = resultLong;
}
public boolean isResultTypeLongElseCursor() {
return resultTypeLongElseCursor;
}
public void setResultTypeLongElseCursor(boolean resultTypeLongElseCursor) {
this.resultTypeLongElseCursor = resultTypeLongElseCursor;
}
public void setActionArgs(String[] actionArgs) {
this.actionArgs = actionArgs;
}
public String[] getActionArgs() {
return actionArgs;
}
public void setActionSQL(String actionSQL) {
this.actionSQL = actionSQL;
}
public String getActionSQL() {
return actionSQL;
}
public SimpleSQLiteQuery getSimpleSQLiteQuery() {
return new SimpleSQLiteQuery(actionSQL,actionArgs);
}
public void setException(Exception exception) {
this.exception = exception;
}
public Exception getException() {
return exception;
}
}
An array of these could then be actioned by a new method such as actionMultipleRawQueries
boolean actionMultipleRawQueries(
RawQuery[] queries,
boolean forceNotInTransaction,
TheDatabase db, //<<<<< SHOULD REFLECT the @Database annotated class
boolean logException,
String logTag,
boolean includeExceptionStacktrace
) {
SupportSQLiteDatabase suppDb = db.getOpenHelper().getWritableDatabase();
boolean successful = false;
if (!forceNotInTransaction) {
Log.d(logTag,"Beginning Transaction");
suppDb.beginTransaction();
}
try {
/* NOTE rawQueryIntoLong and rawQueryIntoCursor would be defined in the respective
@Dao annotated class
*/
for (RawQuery query: queries) {
StringBuilder sb = new StringBuilder();
if (query.getActionArgs() != null) {
for (String s : query.getActionArgs()) {
sb.append("\n\t\t Arg is ").append(s);
}
}
Log.d(logTag,"Actioning Raw Query " query.getActionSQL() " args are:" sb);
if (query.isResultTypeLongElseCursor()) {
query.setResultLong(dao.rawQueryIntoLong(query.getSimpleSQLiteQuery()));
} else {
query.setResultCusror(dao.rawQueryIntoCursor(query.getSimpleSQLiteQuery()));
}
}
/* Only set the transaction as successful IF all queries run OK */
if (!forceNotInTransaction) {
Log.d(logTag,"Setting Transaction Successful.");
suppDb.setTransactionSuccessful();
}
successful = true;
} catch (Exception e) {
if (logException) {
Log.d(logTag,"Action Multiple Raw Queries Exception:-\n\t" e.getMessage());
if (includeExceptionStacktrace) {
e.printStackTrace();
}
}
}
finally {
/* Always end the transaction NOTING THAT
if an exception occurred then doing so rolls back (undoes) any changes
otherwise changes will be applied (i.e. if no exception)
*/
if (!forceNotInTransaction) {
Log.d(logTag,"Ending Transaction.");
suppDb.endTransaction();
}
}
return successful;
}
To test/demonstrate the above, then Adding the following code to the activity:-
/* Example using actionMultipleRawQueries will be rolled back as deigned to try inserting duplicates*/
RawQuery[] manyQueries = new RawQuery[]{
new RawQuery(true,safeInsertSchoolSQL,new String[]{"S001"}),
new RawQuery(true,insertSchoolSQL,new String[]{"S001"}),
new RawQuery(true,safeInsertSchoolSQL, new String[]{"S002"}),
new RawQuery(true,insertSchoolSQL, new String[]{"S002"})
};
actionMultipleRawQueries(manyQueries,false,db,true,TAG "-MQ-FAIL",true);
logAllSchools("-STAGE003");
/* Switch so all queries use the safe OR IGNORE so duplicates are ignored */
for(RawQuery rq: manyQueries) {
rq.setActionSQL(safeInsertSchoolSQL);
}
actionMultipleRawQueries(manyQueries,true,db,true,TAG "MQ-WORK",true);
logAllSchools("-STAGE004");
actionMultipleRawQueries(new RawQuery[]{
new RawQuery(true,"DELETE FROM school WHERE schoolId % 2 = 1",null),
new RawQuery(true,"INSERT OR IGNORE INTO school VALUES(100,'School100')",null),
new RawQuery(true,"UPDATE school SET schoolName = 'NEW-'||schoolName WHERE schoolId % 2 = 0",null),
},false,db,true,TAG "MQSTG5",true);
logAllSchools("-STAGE005");
results in the log additionally containing:-
2022-08-22 11:53:49.598 D/DBINFO-MQ-FAIL: Beginning Transaction
2022-08-22 11:53:49.598 D/DBINFO-MQ-FAIL: Actioning Raw Query INSERT OR IGNORE INTO School (schoolName) VALUES(?) args are:
Arg is S001
2022-08-22 11:53:49.599 D/DBINFO-MQ-FAIL: Actioning Raw Query INSERT INTO School (schoolName) VALUES (?) args are:
Arg is S001
2022-08-22 11:53:49.600 E/SQLiteQuery: exception: UNIQUE constraint failed: School.schoolName (code 2067 SQLITE_CONSTRAINT_UNIQUE); query: INSERT INTO School (schoolName) VALUES (?)
2022-08-22 11:53:49.600 D/DBINFO-MQ-FAIL: Action Multiple Raw Queries Exception:-
UNIQUE constraint failed: School.schoolName (code 2067 SQLITE_CONSTRAINT_UNIQUE)
2022-08-22 11:53:49.600 W/System.err: android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: School.schoolName (code 2067 SQLITE_CONSTRAINT_UNIQUE)
2022-08-22 11:53:49.600 W/System.err: at android.database.sqlite.SQLiteConnection.nativeExecuteForCursorWindow(Native Method)
2022-08-22 11:53:49.608 W/System.err: at android.database.sqlite.SQLiteConnection.executeForCursorWindow(SQLiteConnection.java:1001)
2022-08-22 11:53:49.609 W/System.err: at android.database.sqlite.SQLiteSession.executeForCursorWindow(SQLiteSession.java:838)
2022-08-22 11:53:49.609 W/System.err: at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:62)
2022-08-22 11:53:49.609 W/System.err: at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:153)
2022-08-22 11:53:49.609 W/System.err: at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:140)
2022-08-22 11:53:49.610 W/System.err: at android.database.AbstractCursor.moveToPosition(AbstractCursor.java:231)
2022-08-22 11:53:49.610 W/System.err: at android.database.AbstractCursor.moveToFirst(AbstractCursor.java:270)
2022-08-22 11:53:49.610 W/System.err: at a.a.so73431456javaroomcatchtryexample.AllDao_Impl.rawQueryIntoLong(AllDao_Impl.java:109)
2022-08-22 11:53:49.610 W/System.err: at a.a.so73431456javaroomcatchtryexample.MainActivity.actionMultipleRawQueries(MainActivity.java:145)
2022-08-22 11:53:49.610 W/System.err: at a.a.so73431456javaroomcatchtryexample.MainActivity.onCreate(MainActivity.java:63)
2022-08-22 11:53:49.610 W/System.err: at android.app.Activity.performCreate(Activity.java:7994)
2022-08-22 11:53:49.610 W/System.err: at android.app.Activity.performCreate(Activity.java:7978)
2022-08-22 11:53:49.610 W/System.err: at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1309)
2022-08-22 11:53:49.611 W/System.err: at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:3422)
2022-08-22 11:53:49.611 W/System.err: at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3601)
2022-08-22 11:53:49.611 W/System.err: at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:85)
2022-08-22 11:53:49.611 W/System.err: at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:135)
2022-08-22 11:53:49.611 W/System.err: at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:95)
2022-08-22 11:53:49.611 W/System.err: at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2066)
2022-08-22 11:53:49.612 W/System.err: at android.os.Handler.dispatchMessage(Handler.java:106)
2022-08-22 11:53:49.612 W/System.err: at android.os.Looper.loop(Looper.java:223)
2022-08-22 11:53:49.612 W/System.err: at android.app.ActivityThread.main(ActivityThread.java:7656)
2022-08-22 11:53:49.612 W/System.err: at java.lang.reflect.Method.invoke(Native Method)
2022-08-22 11:53:49.612 W/System.err: at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:592)
2022-08-22 11:53:49.612 W/System.err: at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:947)
2022-08-22 11:53:49.612 D/DBINFO-MQ-FAIL: Ending Transaction.
2022-08-22 11:53:49.613 D/DBINFO-STAGE003: SchoolName is School1 ID is 1
2022-08-22 11:53:49.613 D/DBINFO-STAGE003: SchoolName is School2 ID is 2
2022-08-22 11:53:49.613 D/DBINFO-STAGE003: SchoolName is School3 ID is 3
2022-08-22 11:53:49.613 D/DBINFO-STAGE003: SchoolName is School4 ID is 4
2022-08-22 11:53:49.613 D/DBINFOMQ-WORK: Beginning Transaction
2022-08-22 11:53:49.613 D/DBINFOMQ-WORK: Actioning Raw Query INSERT OR IGNORE INTO School (schoolName) VALUES(?) args are:
Arg is S001
2022-08-22 11:53:49.616 D/DBINFOMQ-WORK: Actioning Raw Query INSERT OR IGNORE INTO School (schoolName) VALUES(?) args are:
Arg is S001
2022-08-22 11:53:49.617 D/DBINFOMQ-WORK: Actioning Raw Query INSERT OR IGNORE INTO School (schoolName) VALUES(?) args are:
Arg is S002
2022-08-22 11:53:49.618 D/DBINFOMQ-WORK: Actioning Raw Query INSERT OR IGNORE INTO School (schoolName) VALUES(?) args are:
Arg is S002
2022-08-22 11:53:49.626 D/DBINFOMQ-WORK: Setting Transaction Successful.
2022-08-22 11:53:49.626 D/DBINFOMQ-WORK: Ending Transaction.
2022-08-22 11:53:49.627 D/DBINFO-STAGE004: SchoolName is School1 ID is 1
2022-08-22 11:53:49.627 D/DBINFO-STAGE004: SchoolName is School2 ID is 2
2022-08-22 11:53:49.627 D/DBINFO-STAGE004: SchoolName is School3 ID is 3
2022-08-22 11:53:49.628 D/DBINFO-STAGE004: SchoolName is School4 ID is 4
2022-08-22 11:53:49.628 D/DBINFO-STAGE004: SchoolName is S001 ID is 5
2022-08-22 11:53:49.628 D/DBINFO-STAGE004: SchoolName is S002 ID is 6
2022-08-22 11:53:49.628 D/DBINFOMQSTG5: Beginning Transaction
2022-08-22 11:53:49.628 D/DBINFOMQSTG5: Actioning Raw Query DELETE FROM school WHERE schoolId % 2 = 1 args are:
2022-08-22 11:53:49.629 D/DBINFOMQSTG5: Actioning Raw Query INSERT OR IGNORE INTO school VALUES(100,'School100') args are:
2022-08-22 11:53:49.630 D/DBINFOMQSTG5: Actioning Raw Query INSERT OR IGNORE INTO school VALUES('1100','School100') args are:
2022-08-22 11:53:49.632 D/DBINFOMQSTG5: Actioning Raw Query UPDATE school SET schoolName = 'NEW-'||schoolName WHERE schoolId % 2 = 0 args are:
2022-08-22 11:53:49.635 D/DBINFOMQSTG5: Setting Transaction Successful.
2022-08-22 11:53:49.635 D/DBINFOMQSTG5: Ending Transaction.
2022-08-22 11:53:49.642 D/DBINFO-STAGE005: SchoolName is NEW-School2 ID is 2
2022-08-22 11:53:49.643 D/DBINFO-STAGE005: SchoolName is NEW-School4 ID is 4
2022-08-22 11:53:49.643 D/DBINFO-STAGE005: SchoolName is NEW-S002 ID is 6
2022-08-22 11:53:49.643 D/DBINFO-STAGE005: SchoolName is NEW-School100 ID is 100
As can be seen the first use of actionMultipleQueries, as expected, trapped the exception due to the UNIQUE constraint and the resultant exception.
Whilst the second use due to using OR IGNORE completed successfully.
The third use shows the flexibility by
deleting Schools with an id that is an odd number, leaving only schools with even id's.
inserts a new School named school100 with an id of 100 and does so successfully, then
tries to insert a new school also named school100 but with an id of 1100, as
OR IGNOREis used the duplicate is ignored.finally updating even number schools to prefix the school name with NEW-
The results should be obtainable, at least for those that store the RawQuery[] in a variable rather than building it on the fly (as per the last/third use).
Again, this is not a recommended approach, rather it just demonstrates that you could "use try/catch purposefully"