Android SQLite SELECT Query

Question

I have taken String value from a EditText and set it inside SELECT QUERY after WHERE condition

As

TextView tv = (TextView) findViewById(R.id.textView3);
EditTextet2 et = (EditText) findViewById(R.id.editText1);

String name = et.getText().toString();

Cursor c = db.rawQuery("SELECT * FROM tbl1 WHERE name = '"+name+"'", null); 

c.moveToNext();

tv.setText(c.getString(c.getColumnIndex("email")));

But it doesn't work. Any suggestions?

print name and make sure you are getting valid name. use c.moveToFirst(); instead of C....next() and try. — kosa, Feb 14 '12 at 16:45

zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz · Accepted Answer · 2012-02-14T17:50:05.100

42

Try trimming the string to make sure there is no extra white space:

Cursor c = db.rawQuery("SELECT * FROM tbl1 WHERE TRIM(name) = '"+name.trim()+"'", null);

Also use c.moveToFirst() like @thinksteep mentioned.

edited Feb 14 '12 at 17:50

answered Feb 14 '12 at 16:49

zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz

5,711
8
54
70

9

This example is very bad and allows for SQL Injections. Why not use the second parameter correctly instead of inserting null? – Daniele Testa Jan 24 '16 at 10:13

score 38 · Answer 2 · edited Nov 04 '17 at 23:53

38

This is a complete code for select statements.

SQLiteDatabase db = this.getReadableDatabase();
Cursor c = db.rawQuery("SELECT column1,column2,column3 FROM table ", null);
if (c.moveToFirst()){
    do {
        // Passing values 
        String column1 = c.getString(0);
        String column2 = c.getString(1);
        String column3 = c.getString(2); 
        // Do something Here with values
    } while(c.moveToNext());
}
c.close();
db.close();

edited Nov 04 '17 at 23:53

James Vickery

732
3
10
23

answered Apr 11 '14 at 21:10

Vladimir Monsanto

532
5
13

4

Please note that using c.getString(c.getColumnIndex("column1")) is better than c.getString(0): you won't have to update indexes if you change your query. – Quentin S. Oct 07 '15 at 16:27
I would also escape name parameter to avoid SQL injection – saniales Nov 04 '17 at 23:58

score 15 · Answer 3 · answered May 05 '13 at 14:21

Try using the following statement:

Cursor c = db.rawQuery("SELECT * FROM tbl1 WHERE name = ?", new String[] {name});

Android requires that WHERE clauses compare to a ?, and you then specify an equal number of ? in the second parameter of the query (where you currently have null).

And as Nambari mentioned, you should use c.moveToFirst() rather than c.moveToNext()

Also, could there be quotations in name? That could screw things up as well.

score 1 · Answer 4 · answered Dec 06 '13 at 04:56

1

Here is the below code.

String areaTyp = "SELECT " +AREA_TYPE + "  FROM "
            + AREA_TYPE_TABLE + " where `" + TYPE + "`="
            + id;

where id is the condition on which result will be displayed.

answered Dec 06 '13 at 04:56

CoderDecoder

445
4
18

3

This has an SQL Injection Problem – M at Oct 11 '17 at 13:40

score 1 · Answer 5 · answered Jan 02 '19 at 14:58

 public User searchUser(String name) {
    User u = new User();
    SQLiteDatabase db = this.getWritableDatabase(); //get the database that was created in this instance
    Cursor c = db.rawQuery("select * from " + TABLE_NAME_User+" where username =?", new String[]{name});
    if (c.moveToLast()) {
        u.setUsername(c.getString(1));
        u.setEmail(c.getString(1));
        u.setImgUrl(c.getString(2));
        u.setScoreEng(c.getString(3));
        u.setScoreFr(c.getString(4));
        u.setScoreSpan(c.getString(5));
        u.setScoreGer(c.getString(6));
        u.setLevelFr(c.getString(7));
        u.setLevelEng(c.getString(8));
        u.setLevelSpan(c.getString(9));
        u.setLevelGer(c.getString(10));
        return u;

    }else {
        Log.e("error not found", "user can't be found or database empty");
        return u;
    }

}

this is my code to select one user and one only so you initiate an empty object of your class then you call your writable Database use a cursor in case there many and you need one here you have a choice Use : 1-

   if (c.moveToLast()) { } //it return the last element in that cursor

or Use : 2-

 if(c.moveToFirst()) {  } //return the first object in the cursor

and don't forget in case the database is empty you'll have to deal with that in my case i just return an empty object

Good Luck

score 1 · Answer 6 · edited Jan 25 '21 at 12:06

Detailed answer:

String[] selectionArgs = new String[]{name};
    Cursor c = db.rawQuery(
       "SELECT * FROM " + tabl1 + 
       " WHERE " + name + " = ? ", selectionArgs
    );

selectionArgs : this takes the 'name' you desire to compare with, as argrument.

Here note "A Cursor object, which is positioned before the first entry of the table you refer to".

So,to move to first entry :

c.moveToFirst();

getColumnIndex(String ColumnName) : this returns the zero-based column index for the given column name.

tv.setText(c.getString(c.getColumnIndex("email")));

In case, you want to go searching through multiple rows for a given name under 'name' column then use loop as below:

if (cursor.moveToFirst()){
    do{
        ////go traversing through loops
           
    }while(cursor.moveToNext());
}

This should solve the problem.

Android SQLite SELECT Query

6 Answers6

Linked