Android开发—数据库应用—访问数据表（SQLite OpenHelper） —添加检索操作（Retrieve）

/* (程序头部注释开始)
* 程序的版权和版本声明部分
* Copyright (c) 2011, 烟台大学计算机学院学生
* All rights reserved.
* 文件名称：数据库应用—添加检索操作（Retrieve）

* 作 者： 雷恒鑫
* 完成日期： 2012 年 08 月 14 日
* 版 本 号： V1.0
* 对任务及求解方法的描述部分
* 输入描述：
* 问题描述：
* 程序输出：

* 程序头部的注释结束

*/

 

 

以前用过的“db.rawQuery”语句可以用来直接做SQL查询（程序如下），

public Cursor getall() {  
       return db.rawQuery("SELECT * FROM notes", null);  
  
    }  

并返回查询结果的指针，如果想实现检索功能，可以再程序中使用更具弹性的“query”方法，来替换直接执行“SQL”语句的“rawQuery”方法。

 

修改后的“DummyDbAdapter.java”文件如下：

package com.demo.android.dummynote;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;

public class NotesDbAdapter {
	private static final String DATABASE_NAME = "notes.db";
	private static final int DATABASE_VERSION = 1;
	private static final String DATABASE_TABLE = "notes";
	private static final String DATABASE_CREATE = "creat table notes("
			+ "_id INTEGER PRIMARY KEY," + "note TEXT," + "created INTEGER,"
			+ "modified INTEGER" + ");";

	private static class DatabaseHelper extends SQLiteOpenHelper {
		public DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
			// TODO Auto-generated constructor stub
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			db.execSQL(DATABASE_CREATE);

		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
			onCreate(db);
		}
	}

	private Context mCtx = null;
	private DatabaseHelper dbHelper;
	private SQLiteDatabase db;

	public NotesDbAdapter(Context ctx) {
		this.mCtx = ctx;
	}

	public NotesDbAdapter open() throws SQLException {
		dbHelper = new DatabaseHelper(mCtx);
		db = dbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		dbHelper.close();
	}

	private static final String KEY_ROWID = "_id";
	static final String KEY_NOTE = "note";
	private static final String KEY_CREATED = "created";
	
	String[] strCols = new String[]{
			KEY_ROWID,	
			KEY_NOTE,
			KEY_CREATED,
	};

	// get all entries
	public Cursor getall() {
		return db.query(DATABASE_TABLE, //Which table to Select
				strCols, //Which columns to return
				null, //WHERE clause
				null, //WHERE arguments
				null, //GROUP BY clause
				null, //HAVING clause
				null);//Order-by clause

	}
}

重构：

由于“”字件符串只会在“”语句中使用一次，因此为了省下内存空间，而少声明一个“”变量，即将“”与“”两段和写在一起，在其他程序中碰到次状况，也可以这样处理。

修改后的“DummyDbAdapter.java”文件如下：

package com.demo.android.dummynote;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;

public class NotesDbAdapter {
	private static final String DATABASE_NAME = "notes.db";
	private static final int DATABASE_VERSION = 1;
	private static final String DATABASE_TABLE = "notes";
	private static final String DATABASE_CREATE = "creat table notes("
			+ "_id INTEGER PRIMARY KEY," + "note TEXT," + "created INTEGER,"
			+ "modified INTEGER" + ");";

	private static class DatabaseHelper extends SQLiteOpenHelper {
		public DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
			// TODO Auto-generated constructor stub
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			db.execSQL(DATABASE_CREATE);

		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
			onCreate(db);
		}
	}

	private Context mCtx = null;
	private DatabaseHelper dbHelper;
	private SQLiteDatabase db;

	public NotesDbAdapter(Context ctx) {
		this.mCtx = ctx;
	}

	public NotesDbAdapter open() throws SQLException {
		dbHelper = new DatabaseHelper(mCtx);
		db = dbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		dbHelper.close();
	}

	private static final String KEY_ROWID = "_id";
	static final String KEY_NOTE = "note";
	private static final String KEY_CREATED = "created";
	
	// get all entries
	public Cursor getall() {
		return db.query(DATABASE_TABLE, //Which table to Select
				new String[]{KEY_ROWID,	KEY_NOTE,KEY_CREATED}, //Which columns to return
				null, //WHERE clause
				null, //WHERE arguments
				null, //GROUP BY clause
				null, //HAVING clause
				null);//Order-by clause

	}
}