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?

Lesley

related: https://mcmap.net/q/619642/-limit-the-query-in-cursorloader – Lollard

related: #9898543 – Toro

Answer 1

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");

Tumbler

Thank you. I'd actually tried to use this and thought it wasn't working due to an outdated SQLQueryBuilder source version.. working now. – Lesley

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 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 :) – Tumbler

I've confirmed that putting the limit clause in the sort order does work. – Chaetognath

Otherwise the clause will be incorrectly appended to the underlying query resulting in an invalid SQL statement – Tumbler

This doesn't work for Android 11, java.lang.IllegalArgumentException: Invalid token limit – Groundsel

Not sure why the devs would call this 'abuse', // Some apps are abusing "ORDER BY" clauses to inject "LIMIT" from here – Groundsel

Answer 2

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.

Climatology

Thanks for the alternate solution, but I've solved the issue by now. :D – Lesley

Much better than the accepted answer. Piggy backing LIMIT in the sort order is a terrible hack and can easily break the provider implementation. – Asymptote

Answer 3

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:

1. http://laviefrugale.blogspot.com/2012/01/using-limit-with-android-contentprovider.html
2. http://www.sqlite.org/lang_select.html
3. https://mcmap.net/q/619642/-limit-the-query-in-cursorloader + @eocanha's answer

Lollard

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! – Lesley

@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. – Lollard

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; – Ankle

@nicolai, thanks for noticing, I have updated the answer to be less ambiguous. – Lollard

@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! https://mcmap.net/q/143613/-sqlite-limit-offset-query Otherwise, your answer is the best! – Comitia

@MnemonicFlow, seems like you're right, anyways I've updated the answer with much cleaner version of using syntax LIMIT <limit> OFFSET <offset>. I hope it's clearer now – Lollard

@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 android.googlesource.com/platform/frameworks/base/+/refs/heads/… The regex for the limit string is "\\s*\\d+\\s*(,\\s*\\d+\\s*)?" which fails to match – Comitia

Answer 4

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.

Fictive