Am I using the right approach to get read from database

Question

I have a news app and the app has a custom gridView which consists of appTitle, appNewNewsCount.

In my Database Table ,I have a column which it's name is new and whenever a new field is added to table its value becomes 1 and whenever user clicks on a news ,the value changes to 0.

In my activity which has the gridView, I have use this function which points to my SQLiteOpenHelper Database Class:

I call the function using this piece of code:

Toast.makeText(getApplicationContext(), String.valueOf(getNumberofNews("Political")), Toast.LENGTH_LONG).show();

And the function's code is:

private int getNumberofNews(String categoryRequested) {
        DB_New_Handler obj = null;
        try{
            obj = new DB_News_Handler(getApplicationContext());
            int newNews = obj.getNumberOfNewNews(categoryRequested);
            if (newNews > 0) {
                return newRings;
            }
        }catch (Exception ex){
            Log.d("getNumberofNews: ", ex.toString());
        }finally{
            if (obj != null){
                obj.close();
            }
        }
        return 0;
    }

And this is the function I use in SQLiteOpenHelper to collect the number of new News:

public int getNumberOfNewNews(String categoryName) {
        SQLiteDatabase db = null;
        Cursor cursor = null;
        try{
            db = this.getReadableDatabase();

            cursor = db.query(TABLE_NEWS, new String[] { KEY_ID,
                KEY_NEWS_ID, KEY_NAME, KEY_CATEGORY, KEY_Package_Id,
                KEY_FAVORITE, KEY_DATE, KEY_NEW_ADDED }, KEY_CATEGORY + "=?",
                new String[] { categoryName }, null, null, null, null);

            if (cursor != null)
                cursor.moveToFirst();

            Log.d(categoryName + " has ", cursor.getCount() + " new News");
            return cursor.getCount();
        }catch (Exception ex){
            Log.d("getNumberOfNewNews: ", ex.toString());
        }finally{
            if (cursor != null ){
                cursor.close();
            }
            if (db != null){
                db.close();
            }
        }
        return 0;
    }

,Now the problem is getNumberOfNewNews function. it always returns 1 when it is suppose to return 0.

Answer 1

Arafat is right plus u have to query your database with two selection arguments ...e.g

public int getNumberOfNewNews(String categoryName) {
        SQLiteDatabase db = null;
        Cursor cursor = null;
        try{
            db = this.getReadableDatabase();

            cursor = db.query(TABLE_NEWS, new String[] { KEY_ID,
                KEY_NEWS_ID, KEY_NAME, KEY_CATEGORY, KEY_Package_Id,
                KEY_FAVORITE, KEY_DATE, KEY_NEW_ADDED }, KEY_CATEGORY + "=? AND ",
                KEY_NEW_ADDED + "=? ",new String[] { categoryName, "1" }, null, null, null, null);

            if (cursor != null)
                cursor.moveToFirst();

//Write the update method here


            Log.d(categoryName + " has ", cursor.getCount() + " new News");
            return cursor.getCount();
        }catch (Exception ex){
            Log.d("getNumberOfNewNews: ", ex.toString());
        }finally{
            if (cursor != null ){
                cursor.close();
            }
            if (db != null){
                db.close();
            }
        }

Answer 2

Write a database update statement just after you have queried the news count. Your update should change the KEY_NEW_ADDED field to 0. I have put a comment inside your method where you should write the update query.

public int getNumberOfNewNews(String categoryName) {
        SQLiteDatabase db = null;
        Cursor cursor = null;
        try{
            db = this.getReadableDatabase();

            cursor = db.query(TABLE_NEWS, new String[] { KEY_ID,
                KEY_NEWS_ID, KEY_NAME, KEY_CATEGORY, KEY_Package_Id,
                KEY_FAVORITE, KEY_DATE, KEY_NEW_ADDED }, KEY_CATEGORY + "=?",
                new String[] { categoryName }, null, null, null, null);

            if (cursor != null)
                cursor.moveToFirst();

//Write the update method here


            Log.d(categoryName + " has ", cursor.getCount() + " new News");
            return cursor.getCount();
        }catch (Exception ex){
            Log.d("getNumberOfNewNews: ", ex.toString());
        }finally{
            if (cursor != null ){
                cursor.close();
            }
            if (db != null){
                db.close();
            }
        }
        return 0;
    }