how to retrieve value from the SQLite database?

2019-09-04 07:59发布

I have a two tables that are Subject and chapter. I need to display chapter_name by passing
Subject_id. My question is that how to do that? When I pass value id doesn't return anything.
Please give some hint.
Here is my code for reference.

 public List<ObjectiveWiseQuestion> getAllChapter(long subId)
    {
     List<ObjectiveWiseQuestion>LocwiseProfileList=new ArrayList<ObjectiveWiseQuestion>();
     String selectQuery=("select chapterName from chapter where subject_id ='"+ subId +"'");
      db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        if (cursor.moveToFirst())
        {
            do {
                ObjectiveWiseQuestion owq= new ObjectiveWiseQuestion();

                owq.setChapterName(cursor.getString(2));
                LocwiseProfileList.add(owq);
            } while(cursor.moveToNext());
            db.close();

        }

        return LocwiseProfileList;
    }

It also shows illegalState Exception.

2条回答
劳资没心,怎么记你
2楼-- · 2019-09-04 08:07

change:

owq.setChapterName(cursor.getString(2));

to this:

/* read value from first column (chapterName) i.e. at index 0 */
owq.setChapterName(cursor.getString(0));

and dont forget to close your cursor too right before closing database:

cursor.close();
db.close();
查看更多
Fickle 薄情
3楼-- · 2019-09-04 08:21

Change your code to:

 public List<ObjectiveWiseQuestion> getAllChapter(long subId)
        {
         List<ObjectiveWiseQuestion>LocwiseProfileList=new ArrayList<ObjectiveWiseQuestion>();
         String selectQuery=("select chapterName from chapter where subject_id =?");

          db = this.getWritableDatabase();
            Cursor cursor = db.rawQuery(selectQuery, new String[]{subId}); //  The secure way of executing raw queries
            if (cursor.moveToFirst())
            {
                do {
                    ObjectiveWiseQuestion owq= new ObjectiveWiseQuestion();
    //Get column index like this
                    owq.setChapterName(cursor.getString(cursor.getColumnIndexOrThrow("subject_id")));  
                    LocwiseProfileList.add(owq);
                } while(cursor.moveToNext());
    cursor.close();                
    db.close();

                    }
 return LocwiseProfileList;
    }

I have changed you query to use the secure way.

Also, cursor.getString(0) or cursor.getString(2) should not be used . Beacause sqlite documentation states that it is not necessary that the query result would have the columns in the same order as in the time of table of creation. This would give you error sometimes but not everytime

查看更多
登录 后发表回答