compare sqlite with string

Question

I saved Data in my SQL databank.

Now I want to compare this saved data, with a string

Something like this:

String example = "house";

Now I want to check, if "house" is already in the databank, with a if clause

something like this

if ( example == [SQL Data] ) {
}
else {
}

Now, how can I accomplish this ?

So this seems more like a sql issue. Take a look at these: http://stackoverflow.com/questions/3634984/insert-if-not-exists-else-update , http://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace — Greg Giacovelli, Oct 16 '12 at 17:01
Start with [SQLiteDatabase.html#query()](http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#query(java.lang.String,%20java.lang.String[],%20java.lang.String,%20java.lang.String[],%20java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String)) or one of its variants. — Code-Apprentice, Oct 16 '12 at 17:01

score 8 · Answer 1 · edited May 23 '17 at 12:10

8

Do something like

String sql = "SELECT * FROM your_table WHERE your_column = '" + example + "'";
Cursor data = database.rawQuery(sql, null);

if (cursor.moveToFirst()) {
// record exists
} else {
// record not found
}

stolen from here

edited May 23 '17 at 12:10

Community

1
1

answered Oct 16 '12 at 17:39

dorjeduck

7,006
10
45
62

Darwind · Accepted Answer · 2017-08-09T12:40:12.270

1

Writing my reply to Sharath's comment as an answer, as the code will be messed up in a comment:

Not saying your reply is wrong, but it's really inefficient to select everything from the table and iterate over it outside the database and it shouldn't be suggested as an answer to the question, because it's a bad habbit to do like that in general.

The way I usually do it, if I want to see if some record is present in the database, I do like this. Not gonna argue about using do-while over a normal while-loop, because that's about different preferences ;)

String query = "SELECT * FROM table_name WHERE column_name=" + the_example_string_to_find;              
Cursor cursor = db.rawQuery(query, null);

if(cursor.getCount() > 0) {
    cursor.moveToFirst();
    while(!cursor.isAfterLast()) {
        // Do whatever you like with the result.
        cursor.moveToNext();
    }
}

edited Aug 09 '17 at 12:40

answered Oct 16 '12 at 17:39

Darwind

6,975
3
43
46

1

Hi, thanks for your answer sadly i get a nullpointerexception here: Cursor cursor = db.rawQuery – user1750720 Oct 16 '12 at 21:45
the problem is, im on a broadcastreceiver – user1750720 Oct 16 '12 at 22:23
How do you create the database? – Darwind Oct 17 '12 at 20:17
**Do not append strings to the query!** Instead use the `query` methods like in [this answer](https://stackoverflow.com/questions/12919686/compare-sqlite-with-string/43574206#43574206) and add the string without quotation to the `selection_args`. – Burdui Oct 19 '18 at 13:19
@Burdui the only change between my answer and the other answer is the quotations. I don't understand what your point is. – Darwind Oct 19 '18 at 13:38
@Darwind Directly appending strings to the query opens a path for SQL injecetion if the string originates from userinput. – Burdui Oct 19 '18 at 14:52
@Burdui yes you're right, but the other answer doesn't help on that either. You should probably go with `Sqlite#compileStatement` of you wanna be on the safe side or just use Room now a days. compileStatement produces prepared statement which are precompiled before usage. – Darwind Oct 19 '18 at 15:27
@Darwind The CompiledStatement objects arne ["used to efficiently execute this statement multiple times"](https://developer.android.com/reference/java/sql/PreparedStatement). Every statement gets precompiled but __the selection_args should bei used__. Apparently this is also true for the rawQuery method. – Burdui Oct 19 '18 at 17:15

score 1 · Answer 3 · answered Apr 23 '17 at 17:18

// Getting Specific  Record by name. 
// in DB handler class make this function call it by sending search criteria. 
    Records getRecord(String name) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_NAME, new String[]{KEY_ID, KEY_NAME, KEY_Auth_Name, KEY_B_PRICE}, KEY_ID + "=?",
                new String[]{name}, null, null, null,null);
        if (cursor.getCount() > 0)
            cursor.moveToFirst();
        Records Records = new Records(Integer.parseInt(cursor.getString(0)),
                cursor.getString(1), cursor.getString(2),cursor.getString(3));
// return book
        return Records;
    }

G_S · Answer 4 · 2012-10-16T17:24:42.570

0

you need to first fetch all the data from the database and next check the data with what you obtained from the database.

Have a look at the link sample database example

suppose you got a cursor object from the database

cursor = db.rawQuery("SELECT yourColumnName FROM "+TABLE_NAME, null);
if(!cursor.moveToFirst()){

 }
 else{

do {
if(cursor.getString(0).equals(example)) 
 //do something which you want and break
 break;
 } while (cursor.moveToNext());

 }

edited Oct 16 '12 at 17:24

answered Oct 16 '12 at 17:02

G_S

6,484
2
17
46

3

Performancewise your approach is completely wrong. Your raw query should look like this instead: SELECT * FROM table_name WHERE row_name = the_example_string. Don't just select everything in the database and compare it in the programming language when the database can do it much more efficiently. Also the Android SQLite DB class has a method called getCount(), where you can get the number of rows returned, when doing a select. – Darwind Oct 16 '12 at 17:07
yes you are right but he didnt mention whether he need the data of some particular value so that we can use a where clause. His information just was to search whether the string is present in the database or not for which we need to get the whole data from the database. Is my approach wrong ? If so can you suggest any other way please – G_S Oct 16 '12 at 17:22

compare sqlite with string

4 Answers4