How to query a table with two WHERE clauses efficiently (Android Room)

Question

Supposed I have an object

class Person {
    String firstName;
    String lastName;
    ... other fields...
}

and a database that contains

@Entity
class DatabaseTable {
    String firstName;
    String lastName;
    ... other unrelated fields from person...
}

now in my Dao, I have

@Query("SELECT * FROM DatabaseTable WHERE " +
        " firstName = :firstName AND lastName = :lastName")
List<DatabaseTable> getAll(String firstName, String lastName);

I have list that I want to query DatabaseTable from.

Person("Apple", "Kohn", ...)
Person("Benny", "Lorie", ...)
Person("Cindy", "May", ...)
...

How would i go about extracting the Database table efficiently? It don't seem right to call the dao for every Person in my list.

EDIT:

I like to know how to use a single query to retrieve a list of results.

I know that under the hood Room uses ContentValue to loop though an entity list. Is there a way for us to create our own contentValue to pass into Room?

Is something like the following possible?

getAllWithPairs( List<String> firstNames, List<String> lastNames);

I think you should be more clear about what you are asking. Is query not working or you are looking for optimized code? — Pankaj Kumar, Oct 30 '17 at 04:07
@PankajKumar, I like to know if it is possible to use a single query to retrieve a list of results. (see edit) — Angel Koh, Oct 30 '17 at 04:21

score 12 · Answer 1 · edited Jun 20 '20 at 09:12

12

By adding AND after your first part of the WHERE statement you can append additional WHERE logic.

Sample

@Query("SELECT * FROM content WHERE timestamp >= :timeframe AND feedType = :feedType ORDER BY qualityScore DESC")
fun getMainContent(timeframe: Date, feedType: FeedType): DataSource.Factory<Int, Content>

edited Jun 20 '20 at 09:12

Community

1
1

answered Sep 10 '18 at 00:46

AdamHurwitz

9,758
10
72
134

N1234 · Answer 2 · 2019-01-01T22:26:35.760

3

If I understood your question correctly you are calling the

List<DatabaseTable> getAll(String firstName, String lastName);

for every person in your database one at a time. I think what you are looking for is:

@Query("SELECT * FROM DatabaseTable")
List<DatabaseTable> getAll();

This should get you every person the the table - DatabaseTable

For the edit part of the question try this:

@Query("SELECT * FROM DatabaseTable WHERE 
    firstName IN (:firstNames) AND secondName IN (:lastNames)")
getAllWithPairs(List<String> firstNames, List<String> lastNames);

edited Jan 01 '19 at 22:26

answered Jan 01 '19 at 22:20

N1234

468
5
12

The order of the list is important. assume I want "Peter Parker" and "Clark Kent". in your query statement - "Peter Kent" and "Clark Parker" will be selected. – Angel Koh Jan 03 '19 at 06:35
1

Ok, now I get you. I'll have a little experiment and get back to you (may take a week or so as I'm away for a week from tomorrow). Off the top of my head, I would say make the Person pojo an Entity and then Use a @ForeignKey to return your results. Then if you did not want to store the Person data you can always use the DAO you would create to delete everything in that table after the query returns results. Heres a couple of links that may help: https://developer.android.com/reference/android/arch/persistence/room/Relation https://stackoverflow.com/a/45990201/1017607 – N1234 Jan 03 '19 at 12:13

score 0 · Answer 3 · answered Dec 16 '21 at 06:26

By adding AND after your first part of the WHERE statement you can append additional WHERE logic.

@Query("SELECT * FROM DatabaseTable WHERE firstName=:firstName AND lastName=:lastName")
fun getAll(firstName : String, lastName : String) : List<DatabaseTable>

How to query a table with two WHERE clauses efficiently (Android Room)

3 Answers3

Sample

Linked