Android 如何使用游标从数据库中检索数据？（安卓、SQLite）

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

提问by zeliboba_fett

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 Titlecolumn using this code:

此代码是否给我列的表格idfilm，Title，Director，Year，Rating？如果是这样，当我尝试Title使用以下代码从列中检索所有标题时仍然存在问题：

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!

为什么 DDMS 会抛出 NullPointerException？我应该如何从“标题”中获取数据？我尝试使用 c.getString()，但是当我要求 c.getString(1) 时，尽管设置 c.getString(2) 时它返回“Director”，但仍然存在 NullPointerException。请帮忙！

采纳答案by Tony the Pony

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

当您填充数据库（您的第一个代码段）时，您的问题就会发生。

You need to combine the INSERTstatements:

您需要合并以下INSERT语句：

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

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

否则，您最终会得到包含Title或的空值的行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).

（注意：不要使用上面的 SQL 表示法，因为它可能会让你容易受到 SQL 注入的影响。我只是为了简单起见才这样写的。在生产代码中，你应该使用参数或清理你的值。不要成为Little Bobby Tables的受害者）。

EDIT

编辑

Here are some threads regarding prepared statements:

以下是有关准备好的语句的一些主题：

• How do I use prepared statements in SQlite in Android?
• Queries with prepared statements in Android?

• 如何在 Android 的 SQlite 中使用准备好的语句？
• 在 Android 中使用准备好的语句进行查询？

回答by

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

如果您从 edittext 框中正确插入了值。然后执行这些步骤。检索值

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.

当该类扩展 ListActivity 时，该语句将其显示为列表。

回答by Hardik Chauhan

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());
        }