Android: Retrieve data from sqlite Database

Question

I currently I have my database set up like the following:

db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" + KEY_ROWID
                    + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_LOCKERNUMBER
                    + " TEXT NOT NULL, " + KEY_STATUS + " INTEGER, " + KEY_PIN
                    + " INTEGER);");

And I am trying to write a method to get the pin code from the column for a specific locker number. Any ideas? I am very new I would like think I would need to use the query function and a cursor. I just one to get the integer value and store it into an int variable so I can compare the pin codes from what the user types in to the one in the database.

possible duplicate of [How to retrieve data from sqlite database in android and display it in TextView](http://stackoverflow.com/questions/12298835/how-to-retrieve-data-from-sqlite-database-in-android-and-display-it-in-textview) — ılǝ, Apr 02 '14 at 00:46

score 1 · Answer 1 · answered Apr 01 '14 at 02:21

Queries to database returns in a Cursor object. You should use the db.query() method to get a row(s). Pass the table name, an array of columns you want to get (or null if you want all of them), pass a selection string that should be like "id = ?" or "key > ?", etc, then pass a String array containing the value for those ? inside the previous string, and finally pass null for having, groupBy and orderBy unless you want to use them.

Cursor cursor = db.query(TABLE_NAME, new String[] { KEY_ROWID }, "id = ?", new String[] { Integer.toString(id) }, null, null, null);

After you get the Cursor, do cursor.moveToFirst() or cursor.moveToPosition(0) (can't remember the exact method, but the point is to move the cursor to the first retrieved row)

then you're going to iterate through the cursor with

while(cursor.moveToNext()) {
     int keyRowIdColumnIndex = cursor.getColumnIndex(KEY_ROWID);
     int yourValue = cursor.getInt(keyRowIdColumnIndex);

     int keyLockNumberColumnIndex = cursor.getColumnIndex(KEY_LOCKNUMBER);
     int pin = cursor.getInt(keyLockNumberColumnIndex);
}

score 0 · Answer 2 · edited May 23 '17 at 11:49

This is a pretty straight forward task and there is a bunch of tutorials, examples, similar questions on SO:

How to perform an SQLite query within an Android application?

In general - you need to query the database passing your search arguments. See the documentation.

It will return you a Cursor with the matching results, which you can then iterate over and manipulate as you wish.

Fyi - storing password in a database table is a bad idea. On Android databases can be accessed and easily read. You either need to encrypt your data or think of another way to store it if it's important.

score 0 · Answer 3 · answered Apr 01 '14 at 04:52

         //Try This code 

         public String getLabeId(String LockNo)
     {

    ArrayList<String> Key_Pin_array = new ArrayList<String>();
    Cursor cur = db.rawQuery("SELECT * FROM  DATABASE_TABLE where KEY_LOCKERNUMBER = '" + LockNo + "'", null);
    try {
             while (cur.moveToNext()) 
              {
                    System.out.println("Key_Pin" + cur.getString(3));
                    Key_Pin_array.add(cur.getString(3));
              }
        } 
        catch (Exception e)
        {
                System.out.println("error in getLabelID in DB() :" + e);
        }
        finally 
        {
                cur.close();
        }
    return id;
 }

Android: Retrieve data from sqlite Database

3 Answers3