Android Room: Order By not working

Question

I am using the new Android ORM Room. And I faced the following issue, queries that use ORDER BY with arguments don't work.

If I want to use the field populated from a parameter for ORDER BY it does not work. It just doesn't sort anything.

@Query("SELECT * FROM User ORDER BY :orderBY ASC")
List<User> sortedFind(String orderBY);

But, when I put the ORDER BY column directly in the query to sort the results, then it works as expected.

@Query("SELECT * FROM User ORDER BY name ASC")
List<User> sortedFind();

Is it a bug on Android Room, or am I doing something wrong?

Answer 1

What is happening

The only values you can pass as paramters to @Dao methods are values, not query strings. The reason for this (I beleive) is to prevent SQL injection issues.

Why this is the case

For example the query SELECT * emails WHERE uid = ? then setting the value as "1 OR WHERE isAdmin = true". This would allow people to run their own bespoke queries on your database and do what they want.

My Solution

I ran into this problem as well. Here is a link to my solution.

My solution has 2 parts. First DynamicQueryservice that generates the query string and value array based on input, then runs the raw query and returns a Cursor. Second, a Cursor2POJO mapper so I do not have to write out cursor mappings over and over for classes, nor introduce potential maintenance issues. I just add annotations to my classes (that match up with room column names) and the lib handles the rest.

I've separated my Cursor mapper into its own library for your benefit, feel free to use it (just made it so if the readme is not clear, or there are bugs hit me up in the comments).

P.S. My library cannot use Room @ColumnInfo to get column names as the annotation is currently set as RetentionPolicy.CLASS so cannot be accessed through reflection. (added to Google issue tracker https://issuetracker.google.com/issues/63720940)

Answer 2

If you are trying order by on string field then you may face string upper and lower case issue, Then you should try with LOWER(your_string_field_name) or UPPER(your_string_field_name) Like below:

@Query("SELECT * FROM User ORDER BY LOWER(name) ASC" 

Enjoy coding :)

Answer 3

You should use @RawQuery annotation and SupportSQLiteQuery class for runtime query

In Dao:

 @RawQuery
 List<Objects> runtimeQuery(SupportSQLiteQuery sortQuery);

To get data:

String query ="SELECT * FROM User ORDER BY " + targetField + " ASC";
List<Objects> users = appDatabase.daoUser().runtimeQuery(new SimpleSQLiteQuery(query));

Answer 4

you can only arrange data in ascending order in reference to name of any of column. Programatically you cannot pass any value to Dao methods