How to Perform an SQLite Query Within an Android Application

How to perform an SQLite query within an Android application?

This will return you the required cursor

Cursor cursor = db.query(TABLE_NAME, new String[] {"_id", "title", "title_raw"}, 
                "title_raw like " + "'%Smith%'", null, null, null, null);

How to run query in SQLite database in android?

final Cursor cursor = db.rawQuery("SELECT SUM(odometer) as odometer FROM tripmileagetable where date like '2012-07%';", null);
int sum = 0;
if (cursor != null) {
    try {
        if (cursor.moveToFirst()) {
            sum = cursor.getInt(0);
        }
    } finally {
        cursor.close();
    }
}

Run query in android SQLite database

To insert:

public void insertIntoTable(String E_NAME_VALUE, String E_AGE_VALUE,String E_DEPT_VALUE) {
            SQLiteDatabase db = this.getWritableDatabase();

                ContentValues cv = new ContentValues();
                cv.put(E_NAME, E_NAME_VALUE);
                cv.put(E_AGE, E_AGE_VALUE);
                cv.put(E_DEPT, E_DEPT_VALUE);
                db.insert(EMP_TABLE , null, cv);
        }

To Fetch stored value from database:

public String fetchValueFromTable(String E_NAME_VALUE,String E_DEPT_VALUE) {
String E_AGE_VALUE="" ;   
SQLiteDatabase db = this.getWritableDatabase();
 SELECT E_AGE FROM EMP_TABLE WHERE E_NAME=a1 AND E_DEPT=a2
        String query = "SELECT * FROM " + EMP_TABLE + " WHERE " + E_NAME+ "='" + E_NAME_VALUE+ "' ANd "+ E_DEPT+ "='" + E_DEPT_VALUE;
        Cursor cursor = db.rawQuery(query, null);
 if (cursor.moveToFirst()) {
    E_AGE_VALUE=cursor.getString(cursor.getColumnIndex(E_AGE));
    }
     return E_AGE_VALUE;
    }

Android SQLite Database : How to query for a specific data?

I assume DATABASE_TABLE is supposed to be a variable:

String query = "select * from " + DATABASE_TABLE + " where title=\""+ arg +"\"";

---

Also consider using parameterized queries to protect yourself from SQL Injection Attacks.

String query = "select * from " + DATABASE_TABLE + " where title=?";
Cursor c = ourDatabase.rawQuery(query, new String[] {arg});

Or use the built-in methods like SQLiteDatabase.query()

---

Addition

Revised code, still errors.

You should always post the LogCat errors along with the relevant code when your app crashes, but I can see an error right away. Cursors can hold more than record or be empty, you must tell the Cursor which row you want to read from and check if this row exists. To do this, simply add:

if (c.moveToFirst()) { /* return true if row exist, false if it doesn't */
    resulttwo = resulttwo + c.getString(iName);
}

If you want to read more than one row, use a loop:

while (c.moveToFirst()) { 
    resulttwo = resulttwo + c.getString(iName);
}

Also consider using a StringBuilder directly or just the += operator.

select query in sqlite android

There are SQL syntax problems and you'll need to use a Cursor to retrieve query results, for example with rawQuery():

String selectQuery = "SELECT lastchapter FROM Bookdetails WHERE bookpath=?";
Cursor c = db.rawQuery(selectQuery, new String[] { fileName });
if (c.moveToFirst()) {
    temp_address = c.getString(c.getColumnIndex("lastchapter"));
}
c.close();

SQLite SELECT query in Android external database - table error

I think by the external database, you meant a database which you want to be loaded from your asset directory. Here you need to copy the database in your application internal storage first to make this accessible from your code. So I would like to suggest you do the following when you run the application for the first time.

public static final String DB_PATH = "/data/data/" + "com.your.package.name" + "/databases/";

private void copyFromAssetsAndCreateDatabase() {
    InputStream yourDatabaseFromAsset;

    try {
        yourDatabaseFromAsset = getApplicationContext().getAssets().open("MyExternalDatabase1");

        File dir = new File(DataHelper.DB_PATH);
        if (!dir.exists()) dir.mkdir();
        File f = new File(DataHelper.DB_PATH + "MyExternalDatabase1" + ".sqlite");
        if (!f.exists()) {
            f.createNewFile();
        }

        OutputStream mOutput = new FileOutputStream(f);
        byte[] mBuffer = new byte[1024];
        int mLength;

        while ((mLength = yourDatabaseFromAsset.read(mBuffer)) > 0)
            mOutput.write(mBuffer, 0, mLength);

        mOutput.flush();
        mOutput.close();
        mInputEnglish.close();

    } catch (Exception e) {
        e.printStackTrace();
    }
}

Now when the database is copied from your external folder to your internal storage where the databases are located usually, it should find the database without any error. I do not know about the first database though. I think it was created in your application using the CREATE TABLE statement.

Hope that helps.

Android SQLite SELECT Query

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.

---

Related Topics