Using the LIMIT statement in a SQLite query

Question

I have a query that selects rows in a ListView without having a limit. But now that I have implemented a SharedPreferences that the user can select how much rows will be displayed in the ListView, my SQLite query doesn't work. I'm passing the argument this way:

return wDb.query(TABELANOME, new String[] {IDTIT, TAREFATIT, SUMARIOTIT}, CONCLUIDOTIT + "=1", null, null, null, null, "LIMIT='" + limite + "'");

Answer 1

The equals (=) operator is not used with the LIMIT clause. Remove it.

Here's an example LIMIT query:

SELECT column FROM table ORDER BY somethingelse LIMIT 5, 10

Or:

SELECT column FROM table ORDER BY somethingelse LIMIT 10

In your case, the correct statement would be:

return wDb.query(TABELANOME, new String[] {IDTIT, TAREFATIT, SUMARIOTIT}, CONCLUIDOTIT + "=1", null, null, null, null, String.valueOf(limite));

Take a look here at the SQLite select syntax: http://www.sqlite.org/syntaxdiagrams.html#select-stmt

This image is rather useful: http://www.sqlite.org/images/syntax/select-stmt.gif

Answer 2

For anyone stumbling across this answer looking for a way to use a LIMIT clause with an OFFSET, I found out from this bug that Android uses the following regex to parse the limit clause of a query:

From <framework/base/core/java/android/database/sqlite/SQLiteQueryBuilder.java>

LIMIT clause is checked with following sLimitPattern.

private static final Pattern sLimitPattern = Pattern.compile("\\s*\\d+\\s*(,\\s*\\d+\\s*)?");

Note that the regex does accept the format offsetNumber,limitNumber even though it doesn't accept the OFFSET statement directly.

Answer 3

Due to this bug which also doesn't allow for negative limits

8,-1

I had to use this workaround

SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
builder.setTables(table);
String query = builder.buildQuery(projection, selection, null, null, null, sortOrder, null);
query+=" LIMIT 8,-1";