How do I select a specific string from an SQL database?

Question

I've spent some hours on trying to solve this but the only answer I get is that I can't select from a string? Is that so? How do I do it otherwise? Here's my code:

public String getHotness(String l) { // TODO Auto-generated method stub

    String[] columns = new String []{ KEY_ROWID, KEY_NAME, KEY_HOTNESS};
    Cursor c = ourDatabase.query(DATABASE_TABLE, columns, KEY_ROWID + "=" + l, null, null, null, null); 
    int iHotness = c.getColumnIndex(KEY_HOTNESS);

    if (c != null){
        c.moveToFirst();
        String hotness = c.getString(iHotness);
        return hotness;
    }


    return null;
}

Where l is a name of a person in my database. How would I select and get information from this person by his name?

Thanks! You guys are the best!

http://www.w3schools.com/sql/sql_where.asp – m0skit0 Jun 12 '14 at 15:15 — m0skit0, Jun 12 '14 at 15:15

Emanuel · Answer 1 · 2014-06-12T15:24:02.813

0

LIKE is what you are looking for. % means anything BEFORE yourname is matching. ?yourname means only 1 char before. if you make sure that the name matches exactly you will need to use name = 'yourname' instead.

Cursor c = null;
try {
c = ourDatabase.rawQuery("SELECT * from yourtable where name LIKE '%yourname%', null);
if (c != null && c.getCount() > 0 && c.moveToFirst()) {
   do {  
     c.getString(....);
   } while (c.moveToNext());
}
} catch (Exception e) { e.printStackTrace(); } 
finally { 
 if (c != null) c.close(); 
}

edited Jun 12 '14 at 15:24

answered Jun 12 '14 at 15:18

Emanuel

8,027
2
37
56

Uselessly complicated. It's `Cursor`, `c` is never null, you don't need the `if`, and no need `do/while`. Also question never said it needs LIKE operator. – m0skit0 Jun 12 '14 at 15:20
No, it's not. Check my updated answer why you should validate this. – Emanuel Jun 12 '14 at 15:21
Yes it is. Check my comment again: c is never null, you don't need the if, and no need do/while. I won't downvote because it is correct (somehow). – m0skit0 Jun 12 '14 at 15:22

m0skit0 · Accepted Answer · 2014-06-12T15:43:25.307

0

You can select only records that fit a specific criteria using the WHERE clause for the select. In case of SQLiteDatabase#query() method, you need to use the third parameter.

Other considerations:

You should use parameters instead of concatenating the values to avoid common issues (like SQL injections).
No need to check if returned Cursor is null, it never is (as the documentation also states).
Also you should close your Cursor before returning from the method.

Example:

final String theName = "The person name";
Cursor c = null;
try {
    c = ourDatabase.query(DATABASE_TABLE, columns, KEY_NAME + " = ?", new String[]{theName}, null, null, null); 
    if (c.moveToNext()) {
        // Do your stuff
    }
} finally {
   if (c != null) {
       c.close();
   }
}`

Note that previous query will only match rows where KEY_NAME column is exactly theName value. If you want to search by names that include theName value, you need to use LIKE operator instead.

final String likeName = "%" + theName + "%";
final Cursor c = ourDatabase.query(DATABASE_TABLE, columns, KEY_NAME + " LIKE ?", new String[]{likeName}, null, null, null);

edited Jun 12 '14 at 15:43

answered Jun 12 '14 at 15:18

m0skit0

25,268
11
79
127

Wait, what do you mean with "You should use parameters instead of concatenating the values to avoid common issues (like SQL injections)."? – fluffow Jun 12 '14 at 15:41
I mean this: `KEY_ROWID + "=" + l` has security vulnerabilities, while this `KEY_NAME + " = ?"` hasn't (at least not in your code). – m0skit0 Jun 12 '14 at 15:42
You can check [this question](http://stackoverflow.com/questions/9516625/prevent-sql-injection-attacks-in-a-java-program) for more details. – m0skit0 Jun 12 '14 at 15:52
Why is that? I've not come so far yet – fluffow Jun 12 '14 at 15:55
I've added a link in previous comment for more details about SQL parameters, check it out. – m0skit0 Jun 12 '14 at 15:59
Ok it works now, I don't really know why since I don't understand what "SelectionArgs" does. – fluffow Jun 12 '14 at 16:15
Glad it's working. I already provided a link explaining SQL arguments 2 comments above. Here it is again: http://stackoverflow.com/questions/9516625/prevent-sql-injection-attacks-in-a-java-program – m0skit0 Jun 12 '14 at 16:16
Hey dude, I guess I'm just stupid. I'll comeback and thank you when I can understand what that link says! – fluffow Jun 12 '14 at 16:21
No, nobody is born with knowledge, but you're definitely a little blind ;) Good luck with it! – m0skit0 Jun 12 '14 at 16:26

How do I select a specific string from an SQL database?

2 Answers2