Android Room - Select query with LIKE
You should enclose the % characters in your input query - not in the query itself.
E.g. try this:
@Query("SELECT * FROM hamster WHERE name LIKE :arg0")
fun loadHamsters(search: String?): Flowable<List<Hamster>>
Then your String search value should look like:
search = "%fido%";
loadHamsters(search);
Furthermore, the binding parameter name should match the variable name, so rather than arg0 it should look like:
@Query("SELECT * FROM hamster WHERE name LIKE :search")
fun loadHamsters(search: String?): Flowable<List<Hamster>>
Why Android Room @Query LIKE is not returning data known to exist
This is the solution provided by the Android Room development team:
@Query("SELECT CommentID FROM Comments WHERE Comment LIKE '%' || :value || '%'")
List<Integer> customSearchCommentsTable(String value);
The irony is that the development team could not get their posted code example to work. By the time they responded I had already created a workaround using a @RawQuery method to create the proper SQL syntax.
Android Room - Return Row from SELECT Query
I found a solution. The issue was caused by the positioning of line:
navController.popBackStack()
The right position for this line is just before navController.navigate("screen"), like this:
user?.let {
LaunchedEffect(key1 = true) {
//navController.popBackStack() //<- IN THIS POSITION CODE DOESN'T WORK!!!!
//Check if email used to log-in is already in Database
val isUserInDatabase = mUserViewModel.readAllData.value?.
find { user -> user.email == it.email }
//If it doesn't exist in DB go to FirstLoginScreen to create user
if (isUserInDatabase == null) {
navController.popBackStack() //<---HERE IS GOOD!
navController.navigate("firstlogin")
}
//If it exists load user details and continue to MainScreen
else {
mUserViewModel.findUserByEmail(it.email!!)
mUserViewModel.user.observe(lifecycleOwner) {
navController.popBackStack() //<---HERE IS GOOD!
navController.navigate("main")
}
}
}
I still don't understand why the old code doesn't work. In particular i don't understand why the code below popBackStack() method runs normally (i.e. the mUserViewModel.findUserByEmail(it.email!!) is called) but i don't get LiveData updates. I hope expert community members have an explanation.
Android Room Database like query not working
Like query will work with this syntax:
inputValue LIKE '%' || :fieldToMatch || '%'
& should be replaced with AND
Replace this:
@Query("SELECT * FROM post_info WHERE deleted=0 & postedBy LIKE :search || '%' OR postName LIKE :search || '%' OR postDes LIKE :search || '%' ORDER BY postid DESC ")
fun searchFilter(search:String):Flow<List<PostEntity>>
With this:
@Query("SELECT * FROM post_info WHERE deleted=0 AND (postedBy LIKE '%' || :search || '%' OR postName LIKE '%' || :search || '%' OR postDes LIKE '%' || :search || '%') ORDER BY postid DESC ")
fun searchFilter(search:String): Flow<List<PostEntity>>
how to use Android Room Database and query using SQLite's Case Expression
You can use something like :-
@Query("SELECT * FROM mediaentity ORDER BY " +
"CASE WHEN lower(:sortByColumn) = lower('_id') AND :sortDirection THEN _id END ASC," +
"CASE WHEN lower(:sortByColumn) = lower('_id') AND NOT :sortDirection THEN _id END DESC," +
"CASE WHEN lower(:sortByColumn) = lower('_SongId') AND :sortDirection THEN _SongId END ASC, " +
"CASE WHEN lower(:sortByColumn) = lower('_SongId') AND NOT :sortDirection THEN _SongId END DESC," +
"CASE WHEN lower(:sortByColumn) = lower('_title') AND :sortDirection THEN _title END ASC, " +
"CASE WHEN lower(:sortByColumn) = lower('_title') AND NOT :sortDirection THEN _title END DESC " +
// and so on, noting the comma except on the last
";"
)
abstract List<MediaEntity> getSortedMediaList(String sortByColumn, boolean sortDirection);
- using the in-built SQLite
lowerfunction could be omitted for both sides of the expression.
Or simpler, but less safe, you could use Something like :-
@RawQuery
abstract List<MediaEntity> rawQuery(SupportSQLiteQuery qry);
public List<MediaEntity> getSortedMediaListV2(String sortByColumn, boolean sortDirection) {
String sortOrder = " ASC ";
if (!sortDirection) {
sortOrder = " DESC";
}
String query = "SELECT * FROM mediaentity ORDER BY " + sortByColumn + sortOrder;
return rawQuery(new SimpleSQLiteQuery(query));
}
- However, if you pass an incorrect column name then it would fail.
Perhaps consider the following working example that utilises the above and, at the end, shows what happens if an incorrect column name is used:-
db = TheDatabase.getInstance(this);
dao = db.getAllDao();
dao.deleteAll();
dao.insert(new MediaEntity(100,"Breathe","Dark Side of the Moon",100100,"Pink Floyd",100,"path to DSOTM",5,180,"1971","Pink Floyd","2021-10-10"));
dao.insert(new MediaEntity(99,"Speak to Me","Dark Side of the Moon",100100,"Pink Floyd",100,"path to DSOTM",4,180,"1971","Pink Floyd","2021-10-10"));
dao.insert(new MediaEntity(98,"On the Run","Dark Side of the Moon",100100,"Pink Floyd",100,"path to DSOTM",3,180,"1971","Pink Floyd","2021-10-10"));
dao.insert(new MediaEntity(102,"Time","Dark Side of the Moon",100100,"Pink Floyd",100,"path to DSOTM",10,180,"1971","Pink Floyd","2021-10-10"));
dao.insert(new MediaEntity(97,"The Great Gig in the Sky","Dark Side of the Moon",100100,"Pink Floyd",100,"path to DSOTM",9,180,"1971","Pink Floyd","2021-10-10"));
dao.insert(new MediaEntity(101,"Money","Dark Side of the Moon",100100,"Pink Floyd",100,"path to DSOTM",8,180,"1971","Pink Floyd","2021-10-10"));
dao.insert(new MediaEntity(103,"Us and Them","Dark Side of the Moon",100100,"Pink Floyd",100,"path to DSOTM",7,180,"1971","Pink Floyd","2021-10-10"));
dao.insert(new MediaEntity(96,"Any Colour You Like","Dark Side of the Moon",100100,"Pink Floyd",100,"path to DSOTM",1,180,"1971","Pink Floyd","2021-10-10"));
dao.insert(new MediaEntity(104,"Brain Damage","Dark Side of the Moon",100100,"Pink Floyd",100,"path to DSOTM",6,180,"1971","Pink Floyd","2021-10-10"));
dao.insert(new MediaEntity(95,"Eclipse","Dark Side of the Moon",100100,"Pink Floyd",100,"path to DSOTM",2,180,"1971","Pink Floyd","2021-10-10"));
Log.d("MEDIAINFO","EXTRACT 1 - ID ascending");
for(MediaEntity m: dao.getSortedMediaList("_Id",true)) {
Log.d("MEDIAINFO","ID = " + m._Id + " SongId = " + m._SongId + " Title = " + m._title + " track# = " + m._trackNumber + " Album is " + m._album);
}
Log.d("MEDIAINFO","EXTRACT 2 - ID descending");
for(MediaEntity m: dao.getSortedMediaList("_Id",false)) {
Log.d("MEDIAINFO","ID = " + m._Id + " SongId = " + m._SongId + " Title = " + m._title + " track# = " + m._trackNumber + " Album is " + m._album);
}
Log.d("MEDIAINFO","EXTRACT 3 - Title ascending");
for(MediaEntity m: dao.getSortedMediaList("_title",true)) {
Log.d("MEDIAINFO","ID = " + m._Id + " SongId = " + m._SongId + " Title = " + m._title + " track# = " + m._trackNumber + " Album is " + m._album);
}
Log.d("MEDIAINFO","EXTRACT 4 - Title descending");
for(MediaEntity m: dao.getSortedMediaList("_title",false)) {
Log.d("MEDIAINFO","ID = " + m._Id + " SongId = " + m._SongId + " Title = " + m._title + " track# = " + m._trackNumber + " Album is " + m._album);
}
Log.d("MEDIAINFO","EXTRACT 5 - SongId ascending");
for(MediaEntity m: dao.getSortedMediaList("_SongId",true)) {
Log.d("MEDIAINFO","ID = " + m._Id + " SongId = " + m._SongId + " Title = " + m._title + " track# = " + m._trackNumber + " Album is " + m._album);
}
Log.d("MEDIAINFO","EXTRACT 6 - SongId descending");
for(MediaEntity m: dao.getSortedMediaList("_SongId",false)) {
Log.d("MEDIAINFO","ID = " + m._Id + " SongId = " + m._SongId + " Title = " + m._title + " track# = " + m._trackNumber + " Album is " + m._album);
}
Log.d("MEDIAINFO","EXTRACT 7 - TrackNumber ascending");
for(MediaEntity m: dao.getSortedMediaListV2("_tracknumber",true)) {
Log.d("MEDIAINFO","ID = " + m._Id + " SongId = " + m._SongId + " Title = " + m._title + " track# = " + m._trackNumber + " Album is " + m._album);
}
Log.d("MEDIAINFO","EXTRACT 8 - TrackNumber descending");
for(MediaEntity m: dao.getSortedMediaListV2("_tracknumber",false)) {
Log.d("MEDIAINFO","ID = " + m._Id + " SongId = " + m._SongId + " Title = " + m._title + " track# = " + m._trackNumber + " Album is " + m._album);
}
dao.getSortedMediaList("not a known column", true);
dao.getSortedMediaListV2("not a known column", true);
The Output to the log including :-
2021-10-18 11:42:48.938 D/MEDIAINFO: EXTRACT 1 - ID ascending
2021-10-18 11:42:48.946 D/MEDIAINFO: ID = 1 SongId = 100 Title = Breathe track# = 5 Album is Dark Side of the Moon
2021-10-18 11:42:48.946 D/MEDIAINFO: ID = 2 SongId = 99 Title = Speak to Me track# = 4 Album is Dark Side of the Moon
2021-10-18 11:42:48.946 D/MEDIAINFO: ID = 3 SongId = 98 Title = On the Run track# = 3 Album is Dark Side of the Moon
2021-10-18 11:42:48.946 D/MEDIAINFO: ID = 4 SongId = 102 Title = Time track# = 10 Album is Dark Side of the Moon
2021-10-18 11:42:48.946 D/MEDIAINFO: ID = 5 SongId = 97 Title = The Great Gig in the Sky track# = 9 Album is Dark Side of the Moon
2021-10-18 11:42:48.947 D/MEDIAINFO: ID = 6 SongId = 101 Title = Money track# = 8 Album is Dark Side of the Moon
2021-10-18 11:42:48.947 D/MEDIAINFO: ID = 7 SongId = 103 Title = Us and Them track# = 7 Album is Dark Side of the Moon
2021-10-18 11:42:48.947 D/MEDIAINFO: ID = 8 SongId = 96 Title = Any Colour You Like track# = 1 Album is Dark Side of the Moon
2021-10-18 11:42:48.947 D/MEDIAINFO: ID = 9 SongId = 104 Title = Brain Damage track# = 6 Album is Dark Side of the Moon
2021-10-18 11:42:48.947 D/MEDIAINFO: ID = 10 SongId = 95 Title = Eclipse track# = 2 Album is Dark Side of the Moon
2021-10-18 11:42:48.947 D/MEDIAINFO: EXTRACT 2 - ID descending
2021-10-18 11:42:48.949 D/MEDIAINFO: ID = 10 SongId = 95 Title = Eclipse track# = 2 Album is Dark Side of the Moon
2021-10-18 11:42:48.949 D/MEDIAINFO: ID = 9 SongId = 104 Title = Brain Damage track# = 6 Album is Dark Side of the Moon
2021-10-18 11:42:48.949 D/MEDIAINFO: ID = 8 SongId = 96 Title = Any Colour You Like track# = 1 Album is Dark Side of the Moon
2021-10-18 11:42:48.949 D/MEDIAINFO: ID = 7 SongId = 103 Title = Us and Them track# = 7 Album is Dark Side of the Moon
2021-10-18 11:42:48.949 D/MEDIAINFO: ID = 6 SongId = 101 Title = Money track# = 8 Album is Dark Side of the Moon
2021-10-18 11:42:48.949 D/MEDIAINFO: ID = 5 SongId = 97 Title = The Great Gig in the Sky track# = 9 Album is Dark Side of the Moon
2021-10-18 11:42:48.949 D/MEDIAINFO: ID = 4 SongId = 102 Title = Time track# = 10 Album is Dark Side of the Moon
2021-10-18 11:42:48.949 D/MEDIAINFO: ID = 3 SongId = 98 Title = On the Run track# = 3 Album is Dark Side of the Moon
2021-10-18 11:42:48.949 D/MEDIAINFO: ID = 2 SongId = 99 Title = Speak to Me track# = 4 Album is Dark Side of the Moon
2021-10-18 11:42:48.949 D/MEDIAINFO: ID = 1 SongId = 100 Title = Breathe track# = 5 Album is Dark Side of the Moon
2021-10-18 11:42:48.949 D/MEDIAINFO: EXTRACT 3 - Title ascending
2021-10-18 11:42:48.955 D/MEDIAINFO: ID = 8 SongId = 96 Title = Any Colour You Like track# = 1 Album is Dark Side of the Moon
2021-10-18 11:42:48.955 D/MEDIAINFO: ID = 9 SongId = 104 Title = Brain Damage track# = 6 Album is Dark Side of the Moon
2021-10-18 11:42:48.955 D/MEDIAINFO: ID = 1 SongId = 100 Title = Breathe track# = 5 Album is Dark Side of the Moon
2021-10-18 11:42:48.955 D/MEDIAINFO: ID = 10 SongId = 95 Title = Eclipse track# = 2 Album is Dark Side of the Moon
2021-10-18 11:42:48.955 D/MEDIAINFO: ID = 6 SongId = 101 Title = Money track# = 8 Album is Dark Side of the Moon
2021-10-18 11:42:48.955 D/MEDIAINFO: ID = 3 SongId = 98 Title = On the Run track# = 3 Album is Dark Side of the Moon
2021-10-18 11:42:48.955 D/MEDIAINFO: ID = 2 SongId = 99 Title = Speak to Me track# = 4 Album is Dark Side of the Moon
2021-10-18 11:42:48.955 D/MEDIAINFO: ID = 5 SongId = 97 Title = The Great Gig in the Sky track# = 9 Album is Dark Side of the Moon
2021-10-18 11:42:48.955 D/MEDIAINFO: ID = 4 SongId = 102 Title = Time track# = 10 Album is Dark Side of the Moon
2021-10-18 11:42:48.955 D/MEDIAINFO: ID = 7 SongId = 103 Title = Us and Them track# = 7 Album is Dark Side of the Moon
2021-10-18 11:42:48.956 D/MEDIAINFO: EXTRACT 4 - Title descending
2021-10-18 11:42:48.959 D/MEDIAINFO: ID = 7 SongId = 103 Title = Us and Them track# = 7 Album is Dark Side of the Moon
2021-10-18 11:42:48.959 D/MEDIAINFO: ID = 4 SongId = 102 Title = Time track# = 10 Album is Dark Side of the Moon
2021-10-18 11:42:48.959 D/MEDIAINFO: ID = 5 SongId = 97 Title = The Great Gig in the Sky track# = 9 Album is Dark Side of the Moon
2021-10-18 11:42:48.959 D/MEDIAINFO: ID = 2 SongId = 99 Title = Speak to Me track# = 4 Album is Dark Side of the Moon
2021-10-18 11:42:48.959 D/MEDIAINFO: ID = 3 SongId = 98 Title = On the Run track# = 3 Album is Dark Side of the Moon
2021-10-18 11:42:48.960 D/MEDIAINFO: ID = 6 SongId = 101 Title = Money track# = 8 Album is Dark Side of the Moon
2021-10-18 11:42:48.960 D/MEDIAINFO: ID = 10 SongId = 95 Title = Eclipse track# = 2 Album is Dark Side of the Moon
2021-10-18 11:42:48.960 D/MEDIAINFO: ID = 1 SongId = 100 Title = Breathe track# = 5 Album is Dark Side of the Moon
2021-10-18 11:42:48.960 D/MEDIAINFO: ID = 9 SongId = 104 Title = Brain Damage track# = 6 Album is Dark Side of the Moon
2021-10-18 11:42:48.960 D/MEDIAINFO: ID = 8 SongId = 96 Title = Any Colour You Like track# = 1 Album is Dark Side of the Moon
2021-10-18 11:42:48.960 D/MEDIAINFO: EXTRACT 5 - SongId ascending
2021-10-18 11:42:48.965 D/MEDIAINFO: ID = 10 SongId = 95 Title = Eclipse track# = 2 Album is Dark Side of the Moon
2021-10-18 11:42:48.965 D/MEDIAINFO: ID = 8 SongId = 96 Title = Any Colour You Like track# = 1 Album is Dark Side of the Moon
2021-10-18 11:42:48.965 D/MEDIAINFO: ID = 5 SongId = 97 Title = The Great Gig in the Sky track# = 9 Album is Dark Side of the Moon
2021-10-18 11:42:48.966 D/MEDIAINFO: ID = 3 SongId = 98 Title = On the Run track# = 3 Album is Dark Side of the Moon
2021-10-18 11:42:48.966 D/MEDIAINFO: ID = 2 SongId = 99 Title = Speak to Me track# = 4 Album is Dark Side of the Moon
2021-10-18 11:42:48.966 D/MEDIAINFO: ID = 1 SongId = 100 Title = Breathe track# = 5 Album is Dark Side of the Moon
2021-10-18 11:42:48.966 D/MEDIAINFO: ID = 6 SongId = 101 Title = Money track# = 8 Album is Dark Side of the Moon
2021-10-18 11:42:48.966 D/MEDIAINFO: ID = 4 SongId = 102 Title = Time track# = 10 Album is Dark Side of the Moon
2021-10-18 11:42:48.967 D/MEDIAINFO: ID = 7 SongId = 103 Title = Us and Them track# = 7 Album is Dark Side of the Moon
2021-10-18 11:42:48.967 D/MEDIAINFO: ID = 9 SongId = 104 Title = Brain Damage track# = 6 Album is Dark Side of the Moon
2021-10-18 11:42:48.967 D/MEDIAINFO: EXTRACT 6 - SongId descending
2021-10-18 11:42:48.971 D/MEDIAINFO: ID = 9 SongId = 104 Title = Brain Damage track# = 6 Album is Dark Side of the Moon
2021-10-18 11:42:48.972 D/MEDIAINFO: ID = 7 SongId = 103 Title = Us and Them track# = 7 Album is Dark Side of the Moon
2021-10-18 11:42:48.972 D/MEDIAINFO: ID = 4 SongId = 102 Title = Time track# = 10 Album is Dark Side of the Moon
2021-10-18 11:42:48.972 D/MEDIAINFO: ID = 6 SongId = 101 Title = Money track# = 8 Album is Dark Side of the Moon
2021-10-18 11:42:48.972 D/MEDIAINFO: ID = 1 SongId = 100 Title = Breathe track# = 5 Album is Dark Side of the Moon
2021-10-18 11:42:48.972 D/MEDIAINFO: ID = 2 SongId = 99 Title = Speak to Me track# = 4 Album is Dark Side of the Moon
2021-10-18 11:42:48.972 D/MEDIAINFO: ID = 3 SongId = 98 Title = On the Run track# = 3 Album is Dark Side of the Moon
2021-10-18 11:42:48.972 D/MEDIAINFO: ID = 5 SongId = 97 Title = The Great Gig in the Sky track# = 9 Album is Dark Side of the Moon
2021-10-18 11:42:48.972 D/MEDIAINFO: ID = 8 SongId = 96 Title = Any Colour You Like track# = 1 Album is Dark Side of the Moon
2021-10-18 11:42:48.973 D/MEDIAINFO: ID = 10 SongId = 95 Title = Eclipse track# = 2 Album is Dark Side of the Moon
2021-10-18 11:42:48.973 D/MEDIAINFO: EXTRACT 7 - TrackNumber ascending
2021-10-18 11:42:48.977 D/MEDIAINFO: ID = 8 SongId = 96 Title = Any Colour You Like track# = 1 Album is Dark Side of the Moon
2021-10-18 11:42:48.978 D/MEDIAINFO: ID = 10 SongId = 95 Title = Eclipse track# = 2 Album is Dark Side of the Moon
2021-10-18 11:42:48.978 D/MEDIAINFO: ID = 3 SongId = 98 Title = On the Run track# = 3 Album is Dark Side of the Moon
2021-10-18 11:42:48.978 D/MEDIAINFO: ID = 2 SongId = 99 Title = Speak to Me track# = 4 Album is Dark Side of the Moon
2021-10-18 11:42:48.978 D/MEDIAINFO: ID = 1 SongId = 100 Title = Breathe track# = 5 Album is Dark Side of the Moon
2021-10-18 11:42:48.979 D/MEDIAINFO: ID = 9 SongId = 104 Title = Brain Damage track# = 6 Album is Dark Side of the Moon
2021-10-18 11:42:48.979 D/MEDIAINFO: ID = 7 SongId = 103 Title = Us and Them track# = 7 Album is Dark Side of the Moon
2021-10-18 11:42:48.979 D/MEDIAINFO: ID = 6 SongId = 101 Title = Money track# = 8 Album is Dark Side of the Moon
2021-10-18 11:42:48.979 D/MEDIAINFO: ID = 5 SongId = 97 Title = The Great Gig in the Sky track# = 9 Album is Dark Side of the Moon
2021-10-18 11:42:48.980 D/MEDIAINFO: ID = 4 SongId = 102 Title = Time track# = 10 Album is Dark Side of the Moon
2021-10-18 11:42:48.980 D/MEDIAINFO: EXTRACT 8 - TrackNumber descending
2021-10-18 11:42:48.984 D/MEDIAINFO: ID = 4 SongId = 102 Title = Time track# = 10 Album is Dark Side of the Moon
2021-10-18 11:42:48.985 D/MEDIAINFO: ID = 5 SongId = 97 Title = The Great Gig in the Sky track# = 9 Album is Dark Side of the Moon
2021-10-18 11:42:48.985 D/MEDIAINFO: ID = 6 SongId = 101 Title = Money track# = 8 Album is Dark Side of the Moon
2021-10-18 11:42:48.985 D/MEDIAINFO: ID = 7 SongId = 103 Title = Us and Them track# = 7 Album is Dark Side of the Moon
2021-10-18 11:42:48.985 D/MEDIAINFO: ID = 9 SongId = 104 Title = Brain Damage track# = 6 Album is Dark Side of the Moon
2021-10-18 11:42:48.985 D/MEDIAINFO: ID = 1 SongId = 100 Title = Breathe track# = 5 Album is Dark Side of the Moon
2021-10-18 11:42:48.985 D/MEDIAINFO: ID = 2 SongId = 99 Title = Speak to Me track# = 4 Album is Dark Side of the Moon
2021-10-18 11:42:48.985 D/MEDIAINFO: ID = 3 SongId = 98 Title = On the Run track# = 3 Album is Dark Side of the Moon
2021-10-18 11:42:48.985 D/MEDIAINFO: ID = 10 SongId = 95 Title = Eclipse track# = 2 Album is Dark Side of the Moon
2021-10-18 11:42:48.985 D/MEDIAINFO: ID = 8 SongId = 96 Title = Any Colour You Like track# = 1 Album is Dark Side of the Moon
- The last line line
dao.getSortedMediaListV2("not a known column", true);fails due to the incorrect column name (the previous line doesn't fail but the result's order probably not be as expected due to the incorrect column name being passed). The exception (as excpected) being
:-
2021-10-18 11:42:48.989 E/SQLiteLog: (1) near "known": syntax error
2021-10-18 11:42:48.989 D/AndroidRuntime: Shutting down VM
2021-10-18 11:42:48.992 E/AndroidRuntime: FATAL EXCEPTION: main
Process: a.a.so69602045javaroomsortbycolumnsviacase, PID: 17538
java.lang.RuntimeException: Unable to start activity ComponentInfo{a.a.so69602045javaroomsortbycolumnsviacase/a.a.so69602045javaroomsortbycolumnsviacase.MainActivity}: android.database.sqlite.SQLiteException: near "known": syntax error (code 1 SQLITE_ERROR): , while compiling: SELECT * FROM mediaentity ORDER BY not a known column ASC
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2913)
Additional
A third Method would be to have individual Dao's called by method (perhaps in the @Dao class as is the case below) that selects the appropriate Dao e.g. :-
/* Method 3 */
@Query("SELECT * FROM mediaentity ORDER BY _id ASC")
abstract List<MediaEntity> getALlOrderBy_Id_ASC();
@Query("SELECT * FROM mediaentity ORDER BY _id DESC")
abstract List<MediaEntity> getALlOrderBy_Id_DESC();
@Query("SELECT * FROM mediaentity ORDER BY _SongId ASC")
abstract List<MediaEntity> getALlOrderBy_SongId_ASC();
@Query("SELECT * FROM mediaentity ORDER BY _SongId DESC")
abstract List<MediaEntity> getALlOrderBy_SongId_DESC();
@Query("SELECT * FROM mediaentity ORDER BY _title ASC")
abstract List<MediaEntity> getALlOrderBy_title_ASC();
@Query("SELECT * FROM mediaentity ORDER BY _title DESC")
abstract List<MediaEntity> getALlOrderBy_title_DESC();
@Query("SELECT * FROM mediaentity ORDER BY _trackNumber ASC")
abstract List<MediaEntity> getALlOrderBy_trackNumber_ASC();
@Query("SELECT * FROM mediaentity ORDER BY _trackNumber DESC")
abstract List<MediaEntity> getALlOrderBy_trackNumber_DESC();
/* and so on */
public List<MediaEntity> getSortedMediaListV3(String sortbyColumn, boolean sortDirection) {
List<MediaEntity> rv = new ArrayList(){};
if (sortDirection) {
switch ( sortbyColumn) {
case "_Id":
rv = getALlOrderBy_Id_ASC();
break;
case "_SongId":
rv = getALlOrderBy_SongId_ASC();
break;
case "_title":
rv = getALlOrderBy_title_ASC();
break;
case "_trackNumber":
return getALlOrderBy_trackNumber_ASC();
}
} else {
switch (sortbyColumn) {
case "_Id":
rv = getALlOrderBy_Id_DESC();
break;
case "_SongId":
rv = getALlOrderBy_SongId_DESC();
break;
case "_title":
rv = getALlOrderBy_title_DESC();
break;
case "_trackNumber":
rv = getALlOrderBy_trackNumber_DESC();
}
}
return rv;
}
Android room: query list items against string column
The use of IN is basically an = test of the expression on the the left of the IN clause against the list of values on the right. That is only exact matches are considered.
However, what you want is multiple LIKE's with wild characters, and an OR between each LIKE e.g question LIKE '%cat%' OR question LIKE '%flower%' or perhaps CASE WHEN THEN ELSE END or perhaps a recursive common table expression (CTE).
The former two (LIKEs or CASEs) would probably have to be done via an @RawQuery where the LIKE/CASE clauses are built at run time.
The Recursive CTE option would basically build a list of words (but could get further complicated if, anything other than spaces, such as punctuation marks were included.)
Another option could be to consider Full Text Search (FTS). You may wish to refer to https://www.raywenderlich.com/14292824-full-text-search-in-room-tutorial-getting-started
Working Example LIKE's
Here's an example of implementing the simplest, multiple LIKEs clauses separated with ORs:-
Objects (the Entity):-
@Entity
data class Objects(
@PrimaryKey
val id: Long? = null,
val question: String
)
AllDAO (the Daos):-
@Dao
interface AllDAO {
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(objects: Objects)
@RawQuery
fun getObjectsRawQuery(query: SupportSQLiteQuery): List<Objects>
fun getObjects(values: List<String>): List<Objects> {
var i = 0
val sb = StringBuilder().append("SELECT * FROM objects WHERE ")
for(v in values) {
if (i++ > 0) {
sb.append(" OR ")
}
sb.append(" question LIKE '%${v}%'")
}
sb.append(";")
return getObjectsRawQuery(SimpleSQLiteQuery(sb.toString()))
}
}
TheDatabase (not uses .allowMainThreadQueries for convenience and brevity):-
@Database(entities = [Objects::class], version = 1, exportSchema = false)
abstract class TheDatabase: RoomDatabase() {
abstract fun getAllDAO(): AllDAO
companion object {
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
}
}
}
Putting it all together, loading some test data and running some extracts:-
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()
dao.insert(Objects(question = "This is a cat."))
dao.insert(Objects(question = "This is a flower."))
dao.insert(Objects(question = "this is nothing."))
dao.insert(Objects(question = "The quick brown fox jumped over the lazy dog"))
logObjects(dao.getObjects(listOf("cat","dog")),"Extract1\t")
logObjects(dao.getObjects(listOf("flower","cat")),"Extract2\t")
logObjects(dao.getObjects(listOf("brown","nothing")),"Extract3\t")
}
fun logObjects(objects: List<Objects>,prefix: String) {
for (o in objects) {
Log.d("OBJECTINFO","$prefix Question is ${o.question} ID is ${o.id}")
}
}
}
Result
2022-04-18 04:58:05.471 D/OBJECTINFO: Extract1 Question is This is a cat. ID is 1
2022-04-18 04:58:05.471 D/OBJECTINFO: Extract1 Question is The quick brown fox jumped over the lazy dog ID is 4
2022-04-18 04:58:05.473 D/OBJECTINFO: Extract2 Question is This is a cat. ID is 1
2022-04-18 04:58:05.473 D/OBJECTINFO: Extract2 Question is This is a flower. ID is 2
2022-04-18 04:58:05.474 D/OBJECTINFO: Extract3 Question is this is nothing. ID is 3
2022-04-18 04:58:05.474 D/OBJECTINFO: Extract3 Question is The quick brown fox jumped over the lazy dog ID is 4
- Note in the above no consideration has been given to handling an empty list (a failure would occur due to the syntax error of
SELECT * FROM objects WHERE ;). That is the example is just intended to demonstrate the basic principle.
Android Room Query: Text Matches Exactly the Search String or Start With Search String
this seems to giving me only the exact match
select * from table where dna_sequence like :searchTerm
There is nothing wrong with your query. The problem is with your Java or Kotlin code. You are getting an exact match because you are passing an exact to searchTerm and not using any wildcards. The LIKE operator in SQL allows us to use wildcards in the string to match more than just strict equality. _ will match a single character while % will match zero or more characters. If you don't use these wildcards, then LIKE will be the exact same as =. So you need to pass a value for searchTerm that uses one of these wild card characters. For example, if you a DAO interface declared as
@Dao
public interface MyDao {
@Query("SELECT * FROM foo WHERE search like :searchTerm")
public Foo[] getFooSearch(String searchTerm);
}
You can call the method with something like:
MyDao dao;
dao.getFooSearch("abcdef%");
which will match the start of the string.
Note: This example is a bit contrived because you only provided your SQL query and didn't provide any of the Java or Kotlin code for your DAO or Data Entity.
How can I turn a list of strings into LIKE clauses in a Room Query?
You can use @RawQuery and build SimpleSQLiteQuery dynamically:
In dao:
@RawQuery(observedEntities = [Game::class])
fun getGames(query: SupportSQLiteQuery): DataSource.Factory<Int, Game>
Related Topics
Problem Inflating Custom View for Alertdialog in Dialogfragment
Send Post Request with Params Using Retrofit
Android How to Runonuithread in Other Class
How to Change Style of a Default Edittext
How to Read CPU Frequency on Android Device
Adb Got Two Same Serial Numbers When Connected to Two Smart Phones
Naming My Application in Android
How to Unregister Broadcastreceiver
Validation Allow Only Number and Characters in Edit Text in Android
Rotate an Yuv Byte Array on Android
Android Check Permission for Locationmanager
A Failure Occurred While Executing Org.Jetbrains.Kotlin.Gradle.Internal.Kaptexecution
Android Fragments: When to Use Hide/Show or Add/Remove/Replace
Start Activity with an Animation
How to Retrieve Key Alias and Key Password for Signed APK in Android Studio(Migrated from Eclipse)