CODEWITHSUNDEEP
databaseandroid-sqlite
sandeep K
sandeep K

Reputation: 55

How to get row data where column = ? also applying limit to rows?

I am getting an error on applying a raw query in sq-lite.The error is

Caused by: android.database.sqlite.SQLiteException: near "Limit": syntax error (code 1): ,
 while compiling: SELECT  * FROM quiz_questions WHERE Levels = "Level 1"+ Limit 2+

The query SELECT * FROM quiz_questions WHERE Levels = "Level 1" works and gives me multiple rows but when i am applying limit to query, it gives error.

This is code for it.

public ArrayList<Question> getLockedLevels(ArrayList<String> Level) {

        ArrayList<Question> questionList = new ArrayList<>();
        db = getReadableDatabase();

        for (int i = 0; i < Level.size(); i++)
        {

          String levelID = Level.get(i);
            String SELECT_TABLE_QUERY = "SELECT  * FROM " + TABLE_NAME + " WHERE " + COLUMN_LEVEL + " = \"" + levelID + "\"+ Limit 1";
        Cursor cursor = db.rawQuery(SELECT_TABLE_QUERY, null);
        if (cursor.moveToFirst()) {
            do {
                Question question = new Question();
                question.setmLevels(cursor.getString(cursor.getColumnIndex(COLUMN_LEVEL)));
                question.setmLevels_lockmanager(cursor.getInt(cursor.getColumnIndex(COLUMN_LEVEL_LOCKMANAGER)));
                 questionList.add(question);
            } while (cursor.moveToNext());
        }

        cursor.close();
    }
        return questionList;
    }

Upvotes: 1

Views: 35

Answers (1)

MikeT
MikeT

Reputation: 57103

Try

String SELECT_TABLE_QUERY = "SELECT  * FROM " + TABLE_NAME + " WHERE " + COLUMN_LEVEL + " = '" + levelID + "' Limit 1";

That should resovle to :-

SELECT  * FROM quiz_questions WHERE Levels = 'Level 1' Limit 1

However, the above is subject to potential SQL Injection.

Using :-

String levelID = Level.get(i);
String SELECT_TABLE_QUERY = "SELECT  * FROM " + TABLE_NAME + " WHERE " + COLUMN_LEVEL + "=? Limit 1";
Cursor cursor = db.rawQuery(SELECT_TABLE_QUERY, new String[]{levelID });

would remove the potential for SQL Injection and is therefore considered the more correct way.

Alternatively you could utilise the convenience query method that not only protects against SQL Injection but also generates much of the SQL.

So you could use :-

for (int i = 0; i < Level.size(); i++) {
    Cursor cursor = db.query(TABLE_NAME,null,COLUMN_LEVEL + "=?",new String[]{Level.get(i)},null,null,null,"1");
    while (cursor.moveToNext()) {
        Question question = new Question();
        question.setmLevels(cursor.getString(cursor.getColumnIndex(COLUMN_LEVEL)));
        question.setmLevels_lockmanager(cursor.getInt(cursor.getColumnIndex(COLUMN_LEVEL_LOCKMANAGER)));
        questionList.add(question);
    }
    cursor.close();
}

See SQliteDatabase - query

The above also uses the shorter while(cursor.moveToNext) { ..... } for looping through the Cursor (if there are no rows then the loop is not entered as moveToNext will return false if the move cannot be made)

Upvotes: 1

Related Questions