I am upgrading my old database to room database, while conversion from plain sql statement to room sql statement. I am facing issues from following scenario.
Scenario I : I have stored sub query in variable like this
String subQueryLocalAddress = "SELECT * FROM localAddressTable where activeAddressId = 1";
String subQueryPermanentAddress = "SELECT * FROM permanentAddressTable where activeAddressId = 1";
Now, this will be conditional, like that.
public Cursor loadAllUserAdress(boolean isLocal){
String userAddressQuery = "SELECT * FROM userTable Where "
+ isLocal? subQueryLocalAddress : subQueryPermanentAddress;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(userAddressQuery, null);
return cursor;
}
Scenario II: I have filter with bunch of constant like
Constant.ORDER_BY_FIRST_NAME_DESC = "ORDER BY firstName DESC";
Constant.ORDER_BY_LAST_NAME_DESC = "ORDER BY lastName DESC";
Now, this flags set at UI level, and it will check in database class, as per respective flag my query will return respected data.
public Cursor loadAllUserDetails(){
String userDetailsQuery = "SELECT * FROM userTable Where " + Constant.ORDER_BY_LAST_NAME_DESC;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(userDetailsQuery, null);
return cursor;
}
I want to combine or merge two or more sub queries in single variable and then fire it. I have dynamic queries.
In short use @Rawquery annotation for dynamic queries.
Demonstration
Here's a before and after Room demo that uses a simple table TableX with id (long/INTEGER PRIMARY KEY), firstname (String/TEXT) and lastName (String/TEXT).
The demo inserts some data and then extracts it sorted via dynamically generated SQL (scenario II).
Before room TableX is embedded in the DBHelper with constants for names and also all ORDER BY permutations, also with some methods so DBHelper :-
class DBHelper extends SQLiteOpenHelper {
public static final String DBNAME = "mydb";
public static final int DBVERSION = 1;
private static volatile DBHelper instance = null;
SQLiteDatabase db;
private DBHelper(Context context) {
super(context,DBNAME,null,DBVERSION);
db = this.getWritableDatabase();
}
public static DBHelper getInstance(Context context) {
if (instance == null) {
instance = new DBHelper(context);
}
return instance;
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(TableX.createSQL);
db.execSQL(TableX.createFirstNameIndex);
db.execSQL(TableX.createLastNameIndex);
}
@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
}
public long insert(String firstName, String lastName) {
ContentValues cv = new ContentValues();
cv.put(TableX.COLUMN_FIRSTNAME,firstName);
cv.put(TableX.COLUMN_LASTNAME,lastName);
return db.insert(TableX.NAME,null,cv);
}
public Cursor loadAllDetails(int orderBy) {
StringBuilder sb = new StringBuilder("SELECT * FROM ").append(NAME);
switch (orderBy) {
case TableX.FIRSTNAME_DESCENDING:
sb.append(TableX.ORDER_BY_FIRSTNAME_DESC);
break;
case TableX.FIRSTNAME_ASCENDING:
sb.append(TableX.ORDER_BY_FIRSTNAME_ASC);
break;
case TableX.LASTNAME_DESCENDING:
sb.append(TableX.ORDER_BY_LASTNAME_DESC);
break;
case TableX.LASTNAME_ASCENDING:
sb.append(TableX.ORDER_BY_LASTNAME_ASC);
break;
default:
break;
}
sb.append(";");
return db.rawQuery(sb.toString(),null);
}
class TableX {
public static final String NAME = "tablex";
public static final String COLUMN_ID = BaseColumns._ID;
public static final String COLUMN_FIRSTNAME = "firstName";
public static final String COLUMN_LASTNAME = "lastName";
public static final String ORDER_BY_FIRSTNAME = " ORDER BY " + COLUMN_FIRSTNAME;
public static final String ORDER_BY_LASTNAME = " ORDER BY " + COLUMN_LASTNAME;
public static final String ORDER_BY_FIRSTNAME_DESC = ORDER_BY_FIRSTNAME + " DESC";
public static final String ORDER_BY_FIRSTNAME_ASC = ORDER_BY_FIRSTNAME + " ASC";
public static final String ORDER_BY_LASTNAME_DESC = ORDER_BY_LASTNAME + " DESC";
public static final String ORDER_BY_LASTNAME_ASC = ORDER_BY_LASTNAME + " ASC";
public static final int FIRSTNAME_DESCENDING = 0;
public static final int FIRSTNAME_ASCENDING = 1;
public static final int LASTNAME_DESCENDING = 2;
public static final int LASTNAME_ASCENDING = 3;
private static final String createSQL = "CREATE TABLE IF NOT EXISTS " + NAME + "(" +
COLUMN_ID + " INTEGER PRIMARY KEY"
+ "," + COLUMN_FIRSTNAME + " TEXT"
+ "," + COLUMN_LASTNAME + " TEXT"
+ ")";
private static final String createFirstNameIndex = "CREATE INDEX IF NOT EXISTS IDX_" + NAME + COLUMN_FIRSTNAME
+ " ON " + NAME + "("
+ COLUMN_FIRSTNAME
+ ")";
private static final String createLastNameIndex = "CREATE INDEX IF NOT EXISTS IDX" + NAME + COLUMN_LASTNAME
+ " ON " + NAME + "("
+ COLUMN_LASTNAME
+ ")";
public Cursor getSomeData(String query) {
return db.rawQuery(query,null);
}
}
}
Room equivalent
First the @Entity class TableXEntity :-
@Entity(tableName = DBHelper.TableX.NAME,
indices = {
@Index(value = DBHelper.TableX.COLUMN_FIRSTNAME),
@Index(value = DBHelper.TableX.COLUMN_LASTNAME)
}
)
class TableXEntity {
@PrimaryKey @ColumnInfo(name = DBHelper.TableX.COLUMN_ID)
Long id;
@ColumnInfo(name = DBHelper.TableX.COLUMN_FIRSTNAME)
String firstName;
@ColumnInfo(name = DBHelper.TableX.COLUMN_LASTNAME)
String lastName;
public TableXEntity(){}
@Ignore
public TableXEntity(String firstName, String lastName) {
this.firstName = firstName;
this.lastName = lastName;
}
}
The @Dao class TableXDao :-
@Dao
abstract class TableXDao {
@Insert(onConflict = OnConflictStrategy.IGNORE)
abstract long insert(TableXEntity tableXEntity);
@Query("SELECT * FROM " + DBHelper.TableX.NAME + DBHelper.TableX.ORDER_BY_FIRSTNAME_ASC)
abstract List<TableXEntity> getAllByFirstNameAscending();
// etc
/* cannot use the @Query below and commented out, as compile error
error: extraneous input ':order' expecting
{<EOF>, ';', K_ALTER, K_ANALYZE, K_ATTACH, K_BEGIN, K_COMMIT, K_CREATE, K_DELETE, K_DETACH,
K_DROP, K_END, K_EXPLAIN, K_INSERT, K_PRAGMA, K_REINDEX, K_RELEASE, K_REPLACE, K_ROLLBACK, K_SAVEPOINT,
K_SELECT, K_UPDATE, K_VACUUM, K_VALUES, K_WITH, UNEXPECTED_CHAR}
abstract List<TableXEntity> getAllByPassedOrder(String order);
Plus it wraps passed parameter in '' so is taken as a literal not an ORDER BY clause
*/
//@SkipQueryVerification
//@Query("SELECT * FROM " + DBHelper.TableX.NAME + " :order")
//abstract List<TableXEntity> getAllByPassedOrder(String order);
/* SO */
@RawQuery
abstract List<TableXEntity> rawq(SupportSQLiteQuery qry);
}
The @Database class TheDatabase (note different database name so the two can coexist for the demo):-
@Database(entities = {TableXEntity.class},version = 1)
abstract class TheDatabase extends RoomDatabase {
abstract TableXDao getTableXDao();
private static volatile TheDatabase instance = null;
public static TheDatabase getInstance(Context context) {
if (instance == null) {
instance = Room.databaseBuilder(
context, TheDatabase.class,"myroomdb"
)
.allowMainThreadQueries()
.build();
}
return instance;
}
}
Putting both into action is MainActivity :-
public class MainActivity extends AppCompatActivity {
DBHelper dbHelper;
TheDatabase roomDB;
TableXDao roomDao;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
dbHelper = DBHelper.getInstance(this);
dbHelper.insert("Mary","Bloggs");
dbHelper.insert("Francis","Frank");
dbHelper.insert("Jane","Doe");
logIt(dbHelper.loadAllDetails(DBHelper.TableX.LASTNAME_ASCENDING));
logIt(dbHelper.loadAllDetails(DBHelper.TableX.FIRSTNAME_ASCENDING));
logIt(dbHelper.loadAllDetails(DBHelper.TableX.LASTNAME_DESCENDING));
/* Room */
roomDB = TheDatabase.getInstance(this);
roomDao = roomDB.getTableXDao();
roomDao.insert(new TableXEntity("Mary","Bloggs"));
roomDao.insert(new TableXEntity("Francis","Frank"));
roomDao.insert(new TableXEntity("Jane","Doe"));
roomLogit(roomDao.getAllByFirstNameAscending());
roomLogit(getListByPassedOrder(DBHelper.TableX.FIRSTNAME_DESCENDING));
roomLogit(getListByPassedOrder(DBHelper.TableX.FIRSTNAME_ASCENDING));
roomLogit(getListByPassedOrder(DBHelper.TableX.LASTNAME_DESCENDING));
roomLogit(getListByPassedOrder(DBHelper.TableX.LASTNAME_ASCENDING));
}
void logIt(Cursor c) {
DatabaseUtils.dumpCursor(c);
}
void roomLogit(List<TableXEntity> thelist) {
for (TableXEntity t: thelist) {
Log.d("ROOMINFO","ID is " + t.id + " FirstName is " + t.firstName + " LastName is " + t.lastName);
}
}
private List<TableXEntity> getListByPassedOrder(int order) {
StringBuilder sb = new StringBuilder("SELECT * FROM ").append(DBHelper.TableX.NAME);
switch (order) {
case DBHelper.TableX.FIRSTNAME_DESCENDING:
sb.append(DBHelper.TableX.ORDER_BY_FIRSTNAME_DESC);
break;
case DBHelper.TableX.FIRSTNAME_ASCENDING:
sb.append(DBHelper.TableX.ORDER_BY_FIRSTNAME_ASC);
break;
case DBHelper.TableX.LASTNAME_DESCENDING:
sb.append(DBHelper.TableX.ORDER_BY_LASTNAME_DESC);
break;
case DBHelper.TableX.LASTNAME_ASCENDING:
sb.append(DBHelper.TableX.ORDER_BY_LASTNAME_ASC);
break;
default:
break;
}
sb.append(";");
return roomDao.rawq(new SimpleSQLiteQuery(sb.toString(),null));
}
}
First the pre-room database is populated and data extracted using the various dynamically generated sorts and the Cursor is dumped to the Log.
Then the room database basically mimics the above but obviously using Room, the output of the data to the log though is done via the extracted objects (TableXEntity's).
The Result in the log :-
2021-09-22 13:08:19.393 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@b9ccda0
2021-09-22 13:08:19.394 I/System.out: 0 {
2021-09-22 13:08:19.394 I/System.out: _id=1
2021-09-22 13:08:19.394 I/System.out: firstName=Mary
2021-09-22 13:08:19.394 I/System.out: lastName=Bloggs
2021-09-22 13:08:19.394 I/System.out: }
2021-09-22 13:08:19.394 I/System.out: 1 {
2021-09-22 13:08:19.394 I/System.out: _id=3
2021-09-22 13:08:19.394 I/System.out: firstName=Jane
2021-09-22 13:08:19.394 I/System.out: lastName=Doe
2021-09-22 13:08:19.395 I/System.out: }
2021-09-22 13:08:19.395 I/System.out: 2 {
2021-09-22 13:08:19.395 I/System.out: _id=2
2021-09-22 13:08:19.395 I/System.out: firstName=Francis
2021-09-22 13:08:19.395 I/System.out: lastName=Frank
2021-09-22 13:08:19.395 I/System.out: }
2021-09-22 13:08:19.395 I/System.out: <<<<<
2021-09-22 13:08:19.396 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@22a7d59
2021-09-22 13:08:19.396 I/System.out: 0 {
2021-09-22 13:08:19.396 I/System.out: _id=2
2021-09-22 13:08:19.396 I/System.out: firstName=Francis
2021-09-22 13:08:19.397 I/System.out: lastName=Frank
2021-09-22 13:08:19.397 I/System.out: }
2021-09-22 13:08:19.397 I/System.out: 1 {
2021-09-22 13:08:19.397 I/System.out: _id=3
2021-09-22 13:08:19.397 I/System.out: firstName=Jane
2021-09-22 13:08:19.397 I/System.out: lastName=Doe
2021-09-22 13:08:19.398 I/System.out: }
2021-09-22 13:08:19.398 I/System.out: 2 {
2021-09-22 13:08:19.398 I/System.out: _id=1
2021-09-22 13:08:19.398 I/System.out: firstName=Mary
2021-09-22 13:08:19.398 I/System.out: lastName=Bloggs
2021-09-22 13:08:19.398 I/System.out: }
2021-09-22 13:08:19.398 I/System.out: <<<<<
2021-09-22 13:08:19.398 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@a1ead1e
2021-09-22 13:08:19.399 I/System.out: 0 {
2021-09-22 13:08:19.399 I/System.out: _id=2
2021-09-22 13:08:19.399 I/System.out: firstName=Francis
2021-09-22 13:08:19.399 I/System.out: lastName=Frank
2021-09-22 13:08:19.399 I/System.out: }
2021-09-22 13:08:19.399 I/System.out: 1 {
2021-09-22 13:08:19.399 I/System.out: _id=3
2021-09-22 13:08:19.399 I/System.out: firstName=Jane
2021-09-22 13:08:19.400 I/System.out: lastName=Doe
2021-09-22 13:08:19.400 I/System.out: }
2021-09-22 13:08:19.400 I/System.out: 2 {
2021-09-22 13:08:19.400 I/System.out: _id=1
2021-09-22 13:08:19.400 I/System.out: firstName=Mary
2021-09-22 13:08:19.400 I/System.out: lastName=Bloggs
2021-09-22 13:08:19.400 I/System.out: }
2021-09-22 13:08:19.400 I/System.out: <<<<<
2021-09-22 13:08:19.456 D/ROOMINFO: ID is 2 FirstName is Francis LastName is Frank
2021-09-22 13:08:19.456 D/ROOMINFO: ID is 3 FirstName is Jane LastName is Doe
2021-09-22 13:08:19.456 D/ROOMINFO: ID is 1 FirstName is Mary LastName is Bloggs
2021-09-22 13:08:19.458 D/ROOMINFO: ID is 1 FirstName is Mary LastName is Bloggs
2021-09-22 13:08:19.458 D/ROOMINFO: ID is 3 FirstName is Jane LastName is Doe
2021-09-22 13:08:19.458 D/ROOMINFO: ID is 2 FirstName is Francis LastName is Frank
2021-09-22 13:08:19.460 D/ROOMINFO: ID is 2 FirstName is Francis LastName is Frank
2021-09-22 13:08:19.460 D/ROOMINFO: ID is 3 FirstName is Jane LastName is Doe
2021-09-22 13:08:19.460 D/ROOMINFO: ID is 1 FirstName is Mary LastName is Bloggs
2021-09-22 13:08:19.462 D/ROOMINFO: ID is 2 FirstName is Francis LastName is Frank
2021-09-22 13:08:19.462 D/ROOMINFO: ID is 3 FirstName is Jane LastName is Doe
2021-09-22 13:08:19.462 D/ROOMINFO: ID is 1 FirstName is Mary LastName is Bloggs
2021-09-22 13:08:19.463 D/ROOMINFO: ID is 1 FirstName is Mary LastName is Bloggs
2021-09-22 13:08:19.463 D/ROOMINFO: ID is 3 FirstName is Jane LastName is Doe
2021-09-22 13:08:19.463 D/ROOMINFO: ID is 2 FirstName is Francis LastName is Frank