Specifying limit / offset for ContentProvider queries

Question

I'm trying to get my ContentResolver to run this query:

select * from myTable limit 1 offset 2

The only query method in ContentResolver is:

resolver.query(uri, projection, selection, selectionArgs, sortOrder);

I've tried:

final Cursor c = resolver.query(
        MyTable.CONTENT_URI,
        MyTable.PROJECTION,
        " ? ?",
        new String[] {"1", "2"},
        null);

Which just throws an IllegaLArgumentException. What is the correct way of achieving this?

related: https://stackoverflow.com/questions/9898043/how-to-add-limit-clause-using-content-provider — Suragch, May 28 '18 at 08:42

score 26 · Accepted Answer · edited Jun 29 '15 at 17:49

26

I put the LIMIT clause in the sordOrder parameter, I've also seen the same thing done by others but not sure if its 100% correct:

final Cursor c = resolver.query(
        MyTable.CONTENT_URI,
        MyTable.PROJECTION,
        null,
        null,
        " limit 1 offset 2");

edited Jun 29 '15 at 17:49

Alex Bitek

6,529
5
47
77

answered Sep 28 '12 at 13:31

Ian Warwick

4,774
3
27
27

Thank you. I'd actually tried to use this and *thought* it wasn't working due to an outdated SQLQueryBuilder source version.. working now. – Buffalo Sep 28 '12 at 13:44
1

Your welcome, since your working with content providers I have a shameless plug! I am working on an open source DSL that makes it easy to write and maintain sqlite backed content providers, specific instructions here http://robotoworks.com/mechanoid-plugin/database-dsl/ visit the parent page for install instructions, I would warn its very alpha though but it still might be something of interest :) – Ian Warwick Sep 28 '12 at 15:13
I've confirmed that putting the limit clause in the sort order does work. – dldnh Oct 02 '13 at 01:38
8

Otherwise the clause will be incorrectly appended to the underlying query resulting in an invalid SQL statement – Ian Warwick Nov 21 '14 at 10:53
6

This doesn't work for Android 11, `java.lang.IllegalArgumentException: Invalid token limit` – Ben Butterworth Nov 09 '20 at 12:13
2

Not sure why the devs would call this 'abuse', `// Some apps are abusing "ORDER BY" clauses to inject "LIMIT"` from [here](https://android.googlesource.com/platform/packages/providers/MediaProvider/+/refs/heads/master/src/com/android/providers/media/MediaProvider.java#2200) – Ben Butterworth Nov 09 '20 at 12:14

score 26 · Answer 2 · answered Nov 21 '12 at 17:53

26

I put the limit clause as a query parameter using the syntax 'limit = offset, limit':

Cursor c = context.getContentResolver().query(
        MyTable.CONTENT_URI.buildUpon().encodedQuery("limit="+offset+","+limit).build(),
        MyTable.PROJECTION,
        null,
        null,
        null);

It works at least with MediaStore uris. Be careful of not encoding the "," or it won't work.

answered Nov 21 '12 at 17:53

eocanha

496
4
5

Thanks for the alternate solution, but I've solved the issue by now. :D – Buffalo Nov 22 '12 at 14:10
7

Much better than the accepted answer. Piggy backing LIMIT in the sort order is a terrible hack and can easily break the provider implementation. – mxk Apr 16 '13 at 14:47

score 25 · Answer 3 · edited May 23 '17 at 12:02

25

If you are providing your content provider, then you can use android.net.Uri.Builder#appendQueryParameter for providing limit and offset as query parameters, which the content provider can use while building the query.

public class MyProvider extends ContentProvider {
    public static final String QUERY_PARAMETER_LIMIT = "limit";
    public static final String QUERY_PARAMETER_OFFSET = "offset";

    public Cursor query(Uri uri, ...) {
        String limit = uri.getQueryParameter(QUERY_PARAMETER_LIMIT);
        String offset = uri.getQueryParameter(QUERY_PARAMETER_OFFSET);

        SQLiteQueryBuilder builder = new SQLiteQueryBuilder();

        // set the table name,...

        // leaving handling of null conditions as an exercise to the reader.
        String limitString = offset + "," + limit;

        Cursor c = qb.query(db, projection, selection, selectionArgs, null, null, sortOrder, limitString);

        //...

        return c;
    }
}

while building the query:

private static final Uri CONTENT_URI = MyProvider.CONTENT_URI.buildUpon()
        .appendQueryParameter(MyProvider.QUERY_PARAMETER_LIMIT,
                    String.valueOf(limit))
        .appendQueryParameter(MyProvider.QUERY_PARAMETER_OFFSET,
                    String.valueOf(offset))
        .build();

note that the android.net.Uri.Builder#appendQueryParameter encodes the value to prevent sql injection.

References:

edited May 23 '17 at 12:02

Community

1
1

answered Jun 05 '14 at 08:39

Avinash R

3,101
1
27
47

You're a couple years too late and I also have no way of verifying if this works, but thanks for the elaborate answer, I hope it helps someone! – Buffalo Jun 05 '14 at 13:10
1

@Buffalo, I am using this code in my apps, also I wrote here partly because, I can come back later just in case I was to "lose" the access to this code. There is simply too much to remember in android. – Avinash R Jun 05 '14 at 13:36
1

I like this approach the best, but it seems that limit and offset is reversed, so that building limitString should be written as: limitString = offset + "," + limit; – Nicolai Buch-Andersen Apr 01 '15 at 15:12
@nicolai, thanks for noticing, I have updated the answer to be less ambiguous. – Avinash R Apr 01 '15 at 15:45
@AvinashR Did you really update the answer? I don't see any edit. See the correct syntax for limit and offset. The offset should appear first! http://stackoverflow.com/a/3325580/313113 Otherwise, your answer is the best! – Alex Bitek Jun 29 '15 at 17:48
@MnemonicFlow, seems like you're right, anyways I've updated the answer with much cleaner version of using syntax `LIMIT OFFSET `. I hope it's clearer now – Avinash R Jun 29 '15 at 18:05
1

@AvinashR Yes, but that syntax is invalid. I've tried it and it gives `java.lang.IllegalArgumentException: invalid LIMIT clauses: LIMIT 10` because it accepts the OFFSET, LIMIT pattern . See line 208 and 40 in the SQLiteQueryBuilder source code https://android.googlesource.com/platform/frameworks/base/+/refs/heads/master/core/java/android/database/sqlite/SQLiteQueryBuilder.java The regex for the limit string is `"\\s*\\d+\\s*(,\\s*\\d+\\s*)?"` which fails to match – Alex Bitek Jun 29 '15 at 18:54

score 0 · Answer 4 · answered Mar 09 '15 at 06:44

When I tried to use the limit String [see limit String below] using the following:

 StringBuilder sbLimit = new StringBuilder().append(" ").append(i_offset).append(" , ").append(i_limit);
String limit = sbLimit .toString()

This gave me good results in combination with the select queries , sorting and grouping.

Specifying limit / offset for ContentProvider queries

4 Answers4

References:

Linked

Related