CODEWITHSUNDEEP
androidsqlsqliteandroid-sqlite
Don
Don

Reputation: 526

Using the WHERE clause in SQLite

I have a general question about using WHERE clauses in SQLite. I have worked with SQLite some and know my way around. However, I'm having trouble with WHERE clauses.

I have an Android application in which I'm needing to do a few simple operations with the SQLite WHERE clause. However, despite experimenting with the WHERE clause all day, I've come up empty. I am trying to perform very simple SQLite commands (e.g. SELECT name FROM tablename WHERE _id=2 or DELETE FROM tablename WHERE name='somename'). But, every time I use the where clause I either get 0 rows returned (in the case of a SELECT) or have 0 rows deleted.

The table name is correct. The column names are correct. (I haven't had any trouble selecting or inserting as long as I don't specify a WHERE clause.) I made sure that the queries/statements were well formed. I've tried both raw queries/statements as well as the methods (I made sure to use the correct methods for SELECT and DELETE {e.g. rawQuery() or query() for SELECT}) provided from the SQLiteDatabase class in Android, but nothing has worked.

All I need is to be able to perform simple queries/statements using the WHERE clause. Does anyone have any insight as to what's happening?

Here's the code to create the table that I'm using:

public static final String TABLE_WORKOUTS = "workouts"

public static final String W_ID = "_id";
public static final String W_WORKOUT_NAME = "workout_name";
public static final String W_EXERICSE_NAME = "exercise_name";
public static final String W_EXERCISE_SETS = "exercise_sets";
public static final String W_EXERCISE_FIRST_ATTRIBUTE = "first_attribute";
public static final String W_EXERCISE_SECOND_ATTRIBUTE = "second_attribute";
public static final String W_EXERCISE_TYPE = "exercise_type";

private static final String createTableWorkouts = "CREATE TABLE " + TABLE_WORKOUTS + " (" + W_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 
        W_WORKOUT_NAME + " TEXT, " + W_EXERICSE_NAME + " TEXT, " + W_EXERCISE_SETS + " TEXT, " +
        W_EXERCISE_FIRST_ATTRIBUTE + " TEXT, " + W_EXERCISE_SECOND_ATTRIBUTE + " TEXT, " + W_EXERCISE_TYPE + " TEXT);";

Example query: String workoutName = "Some Workout"; Cursor cursor = datasource.executeRawQuery("SELECT * FROM " + WorkoutDatabase.TABLE_WORKOUTS + " WHERE " + WorkoutDatabase.W_EXERICSE_NAME + "='" + workoutName + "'", null);

Example record (_id, workout_name, exercise_name, sets, first_attribute, second_attribute, exercise_type): 23, Upper Body, Bench Press, 5, 160, 5, Weight

Upvotes: 0

Views: 20634

Answers (4)

sudharsan chandrasekaran
sudharsan chandrasekaran

Reputation: 607

This one is workable source, which will retrieve list of values from SQLite database using where condition. By using cursor, to retrieve values and added into POJO class. I think this code will helps you lot.

public List<RationNamesPOJO> fetchRationMembers(String rationNumber) {

    ArrayList<RationNamesPOJO> RationDet = new ArrayList<RationNamesPOJO>();

    SQLiteDatabase db = this.getWritableDatabase();

    Cursor cur = db.rawQuery("SELECT * FROM " + TABLE_NAME + " where " + PEOPLE_RATION_NUMBER + "='" + rationNumber + "'", null);

    if (cur.moveToFirst()) {
        do {

            RationNamesPOJO rationPojo = new RationNamesPOJO();
            rationPojo.setPeople_rationNumber(cur.getString(cur.getColumnIndex(PEOPLE_RATION_NUMBER)));
            rationPojo.setPeople_aadhaarNumber(cur.getString(cur.getColumnIndex(PEOPLE_AADHAAR_NUMBER)));
            rationPojo.setPeopleName(cur.getString(cur.getColumnIndex(PEOPLE_NAME)));
            rationPojo.setPeopleBiometric(cur.getString(cur.getColumnIndex(PEOPLE_FINGER_IMAGE)));

            RationDet.add(rationPojo);

        } while (cur.moveToNext());
    }

    return RationDet;

}

Upvotes: 2

CL.
CL.

Reputation: 180280

The value in the exercise_name column is Bench Press, but you are trying to match it with Some Workout.

Upvotes: 1

Kalai.G
Kalai.G

Reputation: 1610

Hi you can try this with helper class

            Cursor cur1 = null, cur2 = null;
            if (mySQLiteAdapter == null || !mySQLiteAdapter.isOpen()) {
                mySQLiteAdapter = new SqliteAdapter(
                        LoginActivity.this);
            }

            try {
                cur1 = mySQLiteAdapter
                        .executeSQLQuery(
                                "select ifnull(max(login_id),0) as userCount from  table_login",
                                null);

                for (cur1.moveToFirst(); !(cur1.isAfterLast()); cur1
                        .moveToNext()) {
                    userCount = cur1.getString(cur1
                            .getColumnIndex("userCount"));

                }
            } catch (Exception ex) {
                Log.w("error while retrieving cursor values", ex.toString());
            } finally {
                cur1.close();
                if (mySQLiteAdapter != null || mySQLiteAdapter.isOpen()) {
                    mySQLiteAdapter.close();
                }
            }

==========================================

Here is your helper class

   public Cursor executeSQLQuery(String sql, String[] selectionArgs) {
    Cursor cursor = null;
    try {
        if (database != null) {
            cursor = database.rawQuery(sql, selectionArgs);
        }
    } catch (SQLiteException sqle) {
        Toast.makeText(context,
                "Unable to execute sql query \n" + sqle.getMessage(),
                Toast.LENGTH_SHORT).show(); 
    }
    return cursor;
}

Upvotes: 0

Srikanth Roopa
Srikanth Roopa

Reputation: 1790

u can try like this.

Implementation 1 

String sqlQuery = "select * from Appointment where start_date='"
                + startDate + "' order by start_date,time(start_date) ASC";

        cursor = getReadableDatabase().rawQuery(sqlQuery, null);
        // Log.e("Appointment ->","Total rows fetched for date "+
        // startDate+" "+cursor.getCount());
        if (cursor != null && cursor.moveToFirst()) {
            do {

            } while (cursor.moveToNext());

}

Implementation 2 

private boolean isRequestExists(int requestId) {
    Cursor cursor = null;
    boolean result = false;
    try {
        String[] args = { "" + requestId };
        StringBuffer sbQuery = new StringBuffer("SELECT * from ").append(
                TABLE_NAME).append(" where _id=?");
        cursor = getReadableDatabase().rawQuery(sbQuery.toString(), args);
        if (cursor != null && cursor.moveToFirst()) {
            result = true;
        }
    } catch (Exception e) {
        Log.e("Requestdbhelper", e.toString());
    }
    return result;
}

Upvotes: 0

Related Questions