I'm using Android Studio Room database to query some complicated works. My original SQLite query sentence like this:
SELECT DISTINCT mainCol as main, secondCol as sum FROM ADB WHERE
(mainCol LIKE 'A1%' OR mainCol LIKE 'A2%' OR mainCol LIKE 'A3%') AND
(secondCol LIKE 'B1%' OR secondCol LIKE 'B2%' OR secondCol LIKE 'B3%' )
ORDER BY dbtime DESC
the Room DAO suggests using List<String> , so I do this as follow.
in DAO
@Query
("SELECT DISTINCT mainCol as main, secondCol as sum FROM ADB WHERE"
" mainCol LIKE :rOne AND"
" secondCol LIKE :rTwo"
" ORDER BY dbtime DESC")
public Map<String,String> getCompare(List<String> rOne, List<String> rTwo);
but it responsed error like this
SELECT DISTINCT mainCol as main, secondCol as sum FROM ADB WHERE mainCol LIKE ?,?,? AND secondCol LIKE ?,?,? ORDER BY dbtime DESC
and it is wrong sentence of course.
Any kind answer to fix my problem?
Thank you
I've tried to use my original command query by room, but no result. I've tried only one value for each, that have correct result, but only one result. I want to query multiple value and using "OR", I'm struggling here.
CodePudding user response：
If you use a List then it simply converts the values to a comma separated list of the values, and then to ?'s for the values to be bound by SQLite, hence ?,?,? (good for use in IN () clause). In your case you want individual values placed throughout the SQL. So something like:-
@Query("SELECT DISTINCT mainCol as main, secondCol as sum FROM ADB WHERE
(mainCol LIKE :rOneA OR mainCol LIKE :rOneB OR mainCol LIKE :rOneC) AND
(secondCol LIKE :rTwoA OR secondCol LIKE :rTwoB OR secondCol LIKE :rTwoC )
ORDER BY dbtime DESC")
public Map<String,String> getCompare(String rOneA, String rOneB, String rOneC, String rTwoA, String rTwoB, String rTwoC);
- Note the above is in-principle code, it has not been compiled or run and thus may contain some typing errors.
Additional
Just curious if I want to query more values, such as 10rOne comparing 10rTwo, is that any way to make the Room query statement dynamic?
Yes. 3 potential ways.
Using recursive CTE's (Common Table Expressions which are basically temporary tables that exist just for the duration). Not exactly light hearted matter and the resultant SQL can be pretty long especially as you would probably be splitting CSV's.
To a limited fashion you can utilise CASE WHEN THEN END in the SQL (probably not suited to list handling but with adaptation to utilise
IN(the_list)there would be potential).Using an
@RawQuerywhere you can build the SQL. However, the query cannot be and therefore is not checked at compile time. Errors in the SQL will result in run time errors.
Here's an example/demo of using @RawQuery
First the ADB class (my version with 4 columns to be searched dynamically):-
@Entity
class ADB {
@PrimaryKey
Long id=null;
String mainCol;
String secondCol;
String thirdCol;
String fourthCol;
ADB(){}
@Ignore
ADB(String mainCol, String secondCol, String thirdCol, String fourthCol){
this.mainCol = mainCol;
this.secondCol = secondCol;
this.thirdCol = thirdCol;
this.fourthCol = fourthCol;
}
}
TheDatabase i.e. the @Database annotated class
@Database(entities = {ADB.class}, exportSchema = false, version = 1)
abstract class TheDatabase extends RoomDatabase {
abstract TheDAOs getTheDAOs();
private static volatile TheDatabase instance;
public static TheDatabase getInstance(Context context) {
if (instance==null) {
instance = Room.databaseBuilder(context,TheDatabase.class,"the_database.db")
.allowMainThreadQueries()
.build();
}
return instance;
}
}
- Note
.allowMainThreadQueriesused for brevity and convenience - Nothing special here.
TheDAOs (includes all the dynamic stuff, WARNING pretty long winded):-
@Dao
abstract class TheDAOs {
@Insert(onConflict = OnConflictStrategy.IGNORE)
abstract long insert(ADB adb);
@RawQuery
abstract List<ADB> rawQueryForADB(SimpleSQLiteQuery simpleSQLiteQuery);
List<ADB> dynamicQueryOfADB(String[] mainCol, String[] secondCol, String[] thirdCol, String[] fourthCol) {
boolean whereKeywordUsed = false;
StringBuilder sql = new StringBuilder().append("SELECT * FROM adb ");
if (mainCol != null && mainCol.length > 0) {
if (!whereKeywordUsed) sql.append("WHERE ");
sql.append(buildColumnWhereClause("maincol",mainCol,whereKeywordUsed));
whereKeywordUsed = true;
}
if (secondCol != null && secondCol.length > 0) {
if (!whereKeywordUsed) sql.append("WHERE ");
sql.append(buildColumnWhereClause("secondcol",secondCol,whereKeywordUsed));
whereKeywordUsed = true;
}
if (thirdCol != null && thirdCol.length > 0) {
if (!whereKeywordUsed) sql.append("WHERE ");
sql.append(buildColumnWhereClause("thirdcol",thirdCol,whereKeywordUsed));
whereKeywordUsed = true;
}
if (fourthCol != null && fourthCol.length > 0) {
if (!whereKeywordUsed) sql.append("WHERE ");
sql.append(buildColumnWhereClause("fourthcol",fourthCol,whereKeywordUsed));
whereKeywordUsed = true;
}
Log.d("SQLGEN","Generated SQL is\n\t" sql.toString());
return rawQueryForADB(new SimpleSQLiteQuery(sql.toString()));
}
private String buildColumnWhereClause(String columnName, String[] columnValues, boolean whereKeywordUsed) {
StringBuilder colSQL = new StringBuilder();
if (whereKeywordUsed) colSQL.append(" AND ");
boolean afterFirst = false;
for (String s: columnValues) {
if (afterFirst) colSQL.append(" OR ");
colSQL.append(columnName).append(" LIKE '").append(s).append("' ");
afterFirst = true;
}
return colSQL.toString();
}
}
First, note the use of an
abstract classas opposed to aninterfaceto allow methods with bodies.method
rawQueryForADBis a skeleton as such and is called indirectly and hence the use of an abstract class (otherwise an instance of TheDaos would have to be passed to the method).method
dynamicQueryOfADBis the method that actually builds the SQL with the help of thebuildColumnWhereClausemethod.- it needs to be passed String[] (or null) for each of the 4 columns.
- In this case it returns a List (unclear of dynamic meant covering extra columns). Of course it would be simple enough to get the 2 strings from this (so List gives more flexibility).
The generated SQL is written to the Log.
It should be noted that the above has only undergone minimal testing. It's intended as in-principle code. As such it may need refinement.
MainActivity i.e. to actually demonstrate the above by adding some data and then extracting data in various permutations to demonstrate the dynamicity:-
public class MainActivity extends AppCompatActivity {
TheDatabase dbi;
TheDAOs dao;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
dbi = TheDatabase.getInstance(this);
dao = dbi.getTheDAOs();
dao .insert(new ADB("A1aaaa","B2bbbb","C3cccc","D4dddd"));
dao .insert(new ADB("A2aaaa","B3bbbb","C4cccc","D5dddd"));
dao .insert(new ADB("A3aaaa","B4bbbb","C5cccc","D6dddd"));
dao .insert(new ADB("A4aaaa","B5bbbb","C6cccc","D7dddd"));
String[] mc1 = new String[]{"a1%","a2%"};
logQueryResults(dao.dynamicQueryOfADB(mc1,null,null,new String[]{}),"RUN1");
String[] sc1 = new String[]{"b4%","b5%"};
logQueryResults(dao.dynamicQueryOfADB(null,sc1,null,new String[]{}),"RUN2");
String[] tc1 = new String[]{"c3%","c6%"};
logQueryResults(dao.dynamicQueryOfADB(null,null,tc1,null),"RUN3");
String[] fc1 = new String[]{"D1%","d2%","D3%","D4%"};
logQueryResults(dao.dynamicQueryOfADB(null,null,null,fc1),"RUN4");
logQueryResults(dao.dynamicQueryOfADB(null,null,null,null),"RUN5");
logQueryResults(dao.dynamicQueryOfADB(mc1,sc1,tc1,fc1),"RUN6");
}
private void logQueryResults(List<ADB> result, String tagSuffix) {
for (ADB a: result) {
Log.d("DBINFO_" tagSuffix,"MC is " a.mainCol " SC is " a.secondCol " TC is " a.thirdCol " FC is " a.fourthCol );
}
}
}
When run (first time as the code is just intended to run once) then the log contains (generated SQL followed by the result):-
2023-01-14 07:42:47.786 D/SQLGEN: Generated SQL is
SELECT * FROM adb WHERE maincol LIKE 'a1%' OR maincol LIKE 'a2%'
2023-01-14 07:42:47.791 D/DBINFO_RUN1: MC is A1aaaa SC is B2bbbb TC is C3cccc FC is D4dddd
2023-01-14 07:42:47.792 D/DBINFO_RUN1: MC is A2aaaa SC is B3bbbb TC is C4cccc FC is D5dddd
2023-01-14 07:42:47.792 D/SQLGEN: Generated SQL is
SELECT * FROM adb WHERE secondcol LIKE 'b4%' OR secondcol LIKE 'b5%'
2023-01-14 07:42:47.793 D/DBINFO_RUN2: MC is A3aaaa SC is B4bbbb TC is C5cccc FC is D6dddd
2023-01-14 07:42:47.793 D/DBINFO_RUN2: MC is A4aaaa SC is B5bbbb TC is C6cccc FC is D7dddd
2023-01-14 07:42:47.793 D/SQLGEN: Generated SQL is
SELECT * FROM adb WHERE thirdcol LIKE 'c3%' OR thirdcol LIKE 'c6%'
2023-01-14 07:42:47.794 D/DBINFO_RUN3: MC is A1aaaa SC is B2bbbb TC is C3cccc FC is D4dddd
2023-01-14 07:42:47.794 D/DBINFO_RUN3: MC is A4aaaa SC is B5bbbb TC is C6cccc FC is D7dddd
2023-01-14 07:42:47.794 D/SQLGEN: Generated SQL is
SELECT * FROM adb WHERE fourthcol LIKE 'D1%' OR fourthcol LIKE 'd2%' OR fourthcol LIKE 'D3%' OR fourthcol LIKE 'D4%'
2023-01-14 07:42:47.797 D/DBINFO_RUN4: MC is A1aaaa SC is B2bbbb TC is C3cccc FC is D4dddd
2023-01-14 07:42:47.797 D/SQLGEN: Generated SQL is
SELECT * FROM adb
2023-01-14 07:42:47.799 D/DBINFO_RUN5: MC is A1aaaa SC is B2bbbb TC is C3cccc FC is D4dddd
2023-01-14 07:42:47.799 D/DBINFO_RUN5: MC is A2aaaa SC is B3bbbb TC is C4cccc FC is D5dddd
2023-01-14 07:42:47.799 D/DBINFO_RUN5: MC is A3aaaa SC is B4bbbb TC is C5cccc FC is D6dddd
2023-01-14 07:42:47.799 D/DBINFO_RUN5: MC is A4aaaa SC is B5bbbb TC is C6cccc FC is D7dddd
2023-01-14 07:42:47.799 D/SQLGEN: Generated SQL is
SELECT * FROM adb WHERE maincol LIKE 'a1%' OR maincol LIKE 'a2%' AND secondcol LIKE 'b4%' OR secondcol LIKE 'b5%' AND thirdcol LIKE 'c3%' OR thirdcol LIKE 'c6%' AND fourthcol LIKE 'D1%' OR fourthcol LIKE 'd2%' OR fourthcol LIKE 'D3%' OR fourthcol LIKE 'D4%'
2023-01-14 07:42:47.801 D/DBINFO_RUN6: MC is A1aaaa SC is B2bbbb TC is C3cccc FC is D4dddd