How do I retrieve data from database using cursor? (Android, SQLite)

Question

I am stuck. I can't find out what I do wrong, so please help anybody. Though it's quite simple task, I don't understand how actually database looks like.

Here's the code I use to insert data into table in database:

EditText title = (EditText)findViewById(R.id.Title); 
String Title = title.getText().toString();

EditText d = (EditText)findViewById(R.id.director); 
String Director = d.getText().toString();

SQLiteDatabase db = openOrCreateDatabase("Films", MODE_PRIVATE, null);
db.execSQL("CREATE TABLE IF NOT EXISTS MyFilms (idFilm INTEGER PRIMARY KEY AUTOINCREMENT, Title VARCHAR, Director VARCHAR, Year INTEGER, Rating INTEGER);");
db.execSQL("INSERT INTO MyFilms (Title) VALUES('" + Title + "');");
db.execSQL("INSERT INTO MyFilms (Director) VALUES('" + Director + "');");
db.close();

Does this code gives me a table with columns idfilm, Title, Director, Year, Rating? If it's so, there's still a problem when I try to retrieve all the titles from Title column using this code:

SQLiteDatabase db = openOrCreateDatabase("Films", MODE_PRIVATE, null);
Cursor c = db.rawQuery("SELECT * FROM MyFilms",null);
c.moveToFirst();

while (c.isAfterLast() == false) {
  String Title = c.getString(c.getColumnIndex("Director"));         
  stringList.add(Title); //This I use to create listlayout dynamically and show all the Titles in it
  c.moveToNext();
}

The error log:

05-21 17:50:14.666: E/AndroidRuntime(27045): FATAL EXCEPTION: main
05-21 17:50:14.666: E/AndroidRuntime(27045): java.lang.NullPointerException
05-21 17:50:14.666: E/AndroidRuntime(27045):    at android.widget.ArrayAdapter.createViewFromResource(ArrayAdapter.java:355)
05-21 17:50:14.666: E/AndroidRuntime(27045):    at android.widget.ArrayAdapter.getView(ArrayAdapter.java:323)
05-21 17:50:14.666: E/AndroidRuntime(27045):    at android.widget.AbsListView.obtainView(AbsListView.java:1409)
 05-21 17:50:14.666: E/AndroidRuntime(27045):   at android.widget.ListView.measureHeightOfChildren(ListView.java:1216)
05-21 17:50:14.666: E/AndroidRuntime(27045):    at android.widget.ListView.onMeasure(ListView.java:1127)
05-21 17:50:14.666: E/AndroidRuntime(27045):    at android.view.View.measure(View.java:8313)
05-21 17:50:14.666: E/AndroidRuntime(27045):    at android.widget.RelativeLayout.measureChild(RelativeLayout.java:566)
05-21 17:50:14.666: E/AndroidRuntime(27045):    at android.widget.RelativeLayout.onMeasure(RelativeLayout.java:381)
05-21 17:50:14.666: E/AndroidRuntime(27045):    at android.view.View.measure(View.java:8313)
05-21 17:50:14.666: E/AndroidRuntime(27045):    at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:3138)
05-21 17:50:14.666: E/AndroidRuntime(27045):    at android.widget.FrameLayout.onMeasure(FrameLayout.java:250)
05-21 17:50:14.666: E/AndroidRuntime(27045):    at android.view.View.measure(View.java:8313)
05-21 17:50:14.666: E/AndroidRuntime(27045):    at android.widget.LinearLayout.measureVertical(LinearLayout.java:531)
05-21 17:50:14.666: E/AndroidRuntime(27045):    at android.widget.LinearLayout.onMeasure(LinearLayout.java:309)
05-21 17:50:14.666: E/AndroidRuntime(27045):    at android.view.View.measure(View.java:8313)
05-21 17:50:14.666: E/AndroidRuntime(27045):    at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:3138)
05-21 17:50:14.666: E/AndroidRuntime(27045):    at android.widget.FrameLayout.onMeasure(FrameLayout.java:250)
05-21 17:50:14.666: E/AndroidRuntime(27045):    at android.view.View.measure(View.java:8313)
05-21 17:50:14.666: E/AndroidRuntime(27045):    at android.view.ViewRoot.performTraversals(ViewRoot.java:839)
05-21 17:50:14.666: E/AndroidRuntime(27045):    at android.view.ViewRoot.handleMessage(ViewRoot.java:1859)
05-21 17:50:14.666: E/AndroidRuntime(27045):    at android.os.Handler.dispatchMessage(Handler.java:99)
05-21 17:50:14.666: E/AndroidRuntime(27045):    at android.os.Looper.loop(Looper.java:123)
05-21 17:50:14.666: E/AndroidRuntime(27045):    at android.app.ActivityThread.main(ActivityThread.java:3683)
05-21 17:50:14.666: E/AndroidRuntime(27045):    at java.lang.reflect.Method.invokeNative(Native Method)
05-21 17:50:14.666: E/AndroidRuntime(27045):    at java.lang.reflect.Method.invoke(Method.java:507)
05-21 17:50:14.666: E/AndroidRuntime(27045):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:839)
05-21 17:50:14.666: E/AndroidRuntime(27045):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:597)
05-21 17:50:14.666: E/AndroidRuntime(27045):    at dalvik.system.NativeStart.main(Native Method)

Why does DDMS throw NullPointerException? How should I get the data from "Title"? I tried to use c.getString(), but when I ask for c.getString(1) there's still NullPointerException though it returns "Director" when I set c.getString(2). Please, help!

post the logcat so we can see the error, but I dont think you are creating your table correctly — tyczj, May 21 '12 at 17:48

score 7 · Answer 1 · answered May 21 '12 at 18:16

if your properly inserted values from edittext box. then do these steps . Retrieve values

Cursor c = db.rawQuery("SELECT * FROM MyFilms",null);

if (c != null ) {
        if  (c.moveToFirst()) {
              do {
              String dir = c.getString(c.getColumnIndex("Director"));
              test.add("" + dir );
              }while (c.moveToNext());
        }
   }

Display the values as a list

    this.setListAdapter(new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1,test));

The statement displays it as a list as the class extends a ListActivity.

score 3 · Accepted Answer · edited May 23 '17 at 12:32

3

Your problem happens when you populate the database (your first code segment).

You need to combine the INSERT statements:

db.execSQL("INSERT INTO MyFilms (Title, Director, Year) VALUES('" 
    + title + "','" + director + "'," + year + ");");

Otherwise, you wind up with rows that contain a null value for Title or Director.

(Note: Don't use the SQL notation above, because it may leave you vulnerable to SQL injection. I only wrote it this way for the sake of simplicity. In production code, you should use parameters or sanitize your values. Don't become a victim of Little Bobby Tables).

EDIT

Here are some threads regarding prepared statements:

edited May 23 '17 at 12:32

Community

1
1

answered May 21 '12 at 17:55

Tony the Pony

37,471
63
170
273

What do I use to separate values? ("+ Title + Director +") tries to input 'TitleDirector'. – zeliboba_fett May 21 '12 at 18:07
Oh, that's really really wise, I didn't even think about it! Thanks a lot! The only thing I'd like to ask you for is to give me a link to a tutorial where I can learn how to do what you said about parameters and sanitizing values! I'd be very helpful! Again, thanks a lot! P.S. Comic is hilarious! – zeliboba_fett May 21 '12 at 18:37
Sure! I'm glad I was able to help. – Tony the Pony May 22 '12 at 09:36

score 3 · Answer 3 · answered Sep 04 '14 at 05:47

Use this code.

c.moveToFirst();
        if (c != null) {
            do {
                for (int i = 0; i < c.getColumnCount(); i++) {

                    Log.e("", "" + c.getString(i));
                }
            }while (c.moveToNext());
        }

How do I retrieve data from database using cursor? (Android, SQLite)

3 Answers3