移动应用开发 实验报告12 数据查询与Cursor接口

二、实验内容

使用Cursor接口并结合SQLiteDatabase类完成数据查询的操作，所有的查询结果将直接通过ListView进行显示。

三、实验原理

    当Android程序需要进行数据检索操作时,需要保存全部的查询结果,而保存查询结果可以使用android.database.Cursor接口完成,并且可以完成对结果集随机读写访问的操作。

四、实验代码及结果

MainActivity.java

package com.example.a;

import android.os.Bundle;

import android.app.Activity;

import android.view.Menu;

import android.database.sqlite.SQLiteOpenHelper;

import android.view.View;

import android.view.View.OnClickListener;

import android.widget.ArrayAdapter;

import android.widget.Button;

import android.widget.LinearLayout;

import android.widget.ListView;

public class MainActivity extends Activity {

    private Button findBut = null;

    private SQLiteOpenHelper helper = null;

    private LinearLayout mylayout = null;

    @Override

    protected void onCreate(Bundle   savedInstanceState) {

        super.onCreate(savedInstanceState);

        setContentView(R.layout.activity_main);

        this.findBut = (Button) super.findViewById(R.id.findBut);

        this.mylayout = (LinearLayout) super.findViewById(R.id.mylayout);

        this.findBut.setOnClickListener(new   OnClickListenerImpl());

    }

    private class OnClickListenerImpl implements   OnClickListener{

       @Override

       public void onClick(View v) {

           MainActivity.this.helper = new   MyDatabaseHelper(MainActivity.this);

           ListView listView = new   ListView(MainActivity.this);

           listView.setAdapter(

                  new   ArrayAdapter<String>(

                         MainActivity.this,

                  android.R.layout.simple_list_item_1,

                  new MytabCursor(

                         MainActivity.this.helper.getReadableDatabase())

                         .find()));

           MainActivity.this.mylayout.addView(listView);

       }

    }

    @Override

    public boolean onCreateOptionsMenu(Menu   menu) {

        // Inflate the menu; this adds items to the action bar if it is present.

        getMenuInflater().inflate(R.menu.main, menu);

        return true;

    }

}

MyDatabaseHelper.java

package com.example.a;

import android.content.Context;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteDatabase.CursorFactory;

import android.database.sqlite.SQLiteOpenHelper;

public class MyDatabaseHelper extends SQLiteOpenHelper {

    private static final String DATABASENAME = "aaa.db";

    private static final int DATABASERVERSION = 1;

    private static final String TABLENAME = "aaa_tab";

    public MyDatabaseHelper(Context   context) {

       super(context, DATABASENAME, null, DATABASERVERSION);

    }

    @Override

    public void onCreate(SQLiteDatabase db)   {

       String sql = "CREATE TABLE " + TABLENAME + "("

              + "id       INTEGER         PRIMARY KEY ,"

              + "name     VARCHAR(50)     NOT NULL ,"

                 + "birthday DATE NOT        NULL" + ")";

       db.execSQL(sql);

       System.out.println("*** 创建：onCreate().");

    }

    @Override

    public void onUpgrade(SQLiteDatabase db,   int oldVersion, int newVersion) {

       String sql = "DROP TABLE IF EXISTS " + TABLENAME ;

       db.execSQL(sql);

       System.out.println("*** 更新：onUpgrade().");

       this.onCreate(db);

    }

}

MytabCursor.java

package com.example.a;

import java.util.ArrayList;

import java.util.List;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

public class MytabCursor {

    private static final String TABLENAME = "aaa_tab";

    private SQLiteDatabase db = null;

    public MytabCursor(SQLiteDatabase   db) {

       this.db = db;

    }

    public List<String> find(){

       List<String> all = new   ArrayList<String>();

       String sql = "SELECT id,name,birthday FROM " + TABLENAME ;

       Cursor result = this.db.rawQuery(sql, null);

       for (result.moveToFirst();   !result.isAfterLast(); result.moveToNext()) {

           all.add("【" + result.getInt(0) + "】" + " " +   result.getString(1)

                  + "，" + result.getString(2));

       }

       this.db.close();

       return all;

    }

}

 MytabOperate.java

package com.example.a;

import android.content.ContentValues;

import android.database.sqlite.SQLiteDatabase;

public class MytabOperate {

    private static final String TABLENAME = "aaa_tab";

    private SQLiteDatabase db = null;

    public MytabOperate(SQLiteDatabase   db) {

       this.db = db;

    }

    public void insert(String name,String   birthday) {

       ContentValues cv = new ContentValues();

       cv.put("name", name);

       cv.put("birthday", birthday);

       this.db.insert(TABLENAME, null, cv);

       this.db.close();

    }

    public void update(int id, String name,   String birthday){

       ContentValues cv = new ContentValues();

       cv.put("name", name);

       cv.put("birthday", birthday);

       String whereClause = "id=?";

       String whereArgs[] = new String[]{String.valueOf(id)};

       this.db.update(TABLENAME, cv, whereClause,   whereArgs);

       this.db.close();

    }

    public void delete(int id){

       String whereClause = "id=?";

       String whereArgs[] = new String[]{String.valueOf(id)};

       this.db.delete(TABLENAME, whereClause, whereArgs);

       this.db.close();

    }

}

 activity_main.xml

<?xml version="1.0"   encoding="utf-8"?>

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"

    android:id="@+id/mylayout"

    android:layout_width="fill_parent"

    android:layout_height="fill_parent"

    android:orientation="vertical"   >

    <Button

        android:id="@+id/findBut"

        android:layout_width="fill_parent"

        android:layout_height="wrap_content"

        android:text="查询全部数据" />

</LinearLayout>

 结果：

五、试验分析（思考）

熟悉了Cursor接口的用法，掌握了数据查询的方法，使用getInt()或getString()等方法可以取得指定列的内容。