Android SQLite数据库操作

Android定义了一个很方便的Sqlite操作类SQLiteOpenHelper...我们不再需要加载驱动而且要手写关闭一大堆PrepareStatement,Connection等等...

package com.example.db;
import android.content.Context;
import android.database.DatabaseErrorHandler;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;


public class PersonSQLiteOpenHelper extends SQLiteOpenHelper {


	/**
	 * 构造方法
	 * @param context 上下文	
	 * @param name 数据库名称
	 * @param factory 使用什么游标，传null是使用默认游标
	 * @param version 版本，最小是1
	 */
	public PersonSQLiteOpenHelper(Context context, String name,
			CursorFactory factory, int version) {
		super(context, name, factory, version);
		// TODO Auto-generated constructor stub
	}
	/**
	 * 数据库第一次被创建的时候调用的方法
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL("create table person (id integer primary key autoincrement, name varchar(20),number varchar(20),account varchar(20))");
	}
	/**
	 * 只有在数据库版本发生变化的时候调用
	 * @param db
	 * @param oldVersion
	 * @param newVersion
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
	}

}

定义personDataAccessObject..这是第一种写DAO的方法。---手写SQL语句

package com.example.dao;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.example.db.PersonSQLiteOpenHelper;
import com.example.domain.Person;

public class PersonDAO {
	private PersonSQLiteOpenHelper helper;

	// 在构造方法完成helper的初始化
	public PersonDAO(Context context) {
		super();
		this.helper = new PersonSQLiteOpenHelper(context, "person.db", null, 1);
	}

	/**
	 * 添加一条记录到数据库
	 * 
	 * @param name
	 *            姓名
	 * @param number
	 *            电话
	 */
	public void add(String name, String number) {
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL("insert into  person (name,number) values (?,?);",
				new Object[] { name, number });
		db.close();
	}

	/**
	 * 查询记录
	 * 
	 * @param name
	 * @return true 查询结果存在 , false 查询结果不存在
	 */
	public boolean findPersonByName(String name) {
		SQLiteDatabase db = helper.getWritableDatabase();
		Cursor cursor = db.rawQuery("select * from person where name = ?",
				new String[] { name });
		boolean result = cursor.moveToNext();
		cursor.close();
		db.close();
		return result;
	}

	/**
	 * 修改一条记录
	 * 
	 * @param name
	 *            修改人的姓名
	 * @param newnumber
	 *            修改后密码
	 */
	public void updatePersonByName(String name, String newnumber) {
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL("update person set number=? where name = ?", new Object[] {
				newnumber, name });
		db.close();

	}

	public void deletePersonByName(String name) {
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL("delete from person where name = ?", new Object[] { name });
		db.close();
	}

	public List<Person> getAll() {
		SQLiteDatabase db = helper.getWritableDatabase();
		List<Person> persons = new ArrayList<Person>();
		Cursor cursor = db.rawQuery("select * from person", null);
		while (cursor.moveToNext()) {
			int id = cursor.getInt(0);
			String name = cursor.getString(1);
			String number = cursor.getString(2);
			Person person = new Person(id, name, number);
			persons.add(person);
		}
		cursor.close();
		db.close();
		return persons;
	}
}

这是第二种写DAO的方法，Android已经为我们写好了SQL的操作，如果不记得SQL语句或者害怕出错，建议用这种方法

package com.example.dao;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.example.db.PersonSQLiteOpenHelper;
import com.example.domain.Person;

public class PersonDAO2 {
	private PersonSQLiteOpenHelper helper;

	// 在构造方法完成helper的初始化
	public PersonDAO2(Context context) {
		super();
		this.helper = new PersonSQLiteOpenHelper(context, "person.db", null, 1);
	}

	/**
	 * 添加一条记录到数据库
	 * 
	 * @param name
	 *            姓名
	 * @param number
	 *            电话
	 */
	public long add(String name, String number) {
		SQLiteDatabase db = helper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("name", name);
		long id = db.insert("person", null, values);
		db.close();
		return id;
	}

	/**
	 * 查询记录
	 * 
	 * @param name
	 * @return true 查询结果存在 , false 查询结果不存在
	 */
	public boolean findPersonByName(String name) {
		SQLiteDatabase db = helper.getWritableDatabase();
		Cursor cursor = db.query("person", null, "name=?",
				new String[] { name }, null, null, null);
		boolean result = cursor.moveToNext();
		cursor.close();
		db.close();
		return result;
	}

	/**
	 * 修改一条记录
	 * 
	 * @param name
	 *            修改人的姓名
	 * @param newnumber
	 *            修改后密码
	 */
	public int updatePersonByName(String name, String newnumber) {
		SQLiteDatabase db = helper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("number", newnumber);
		int number = db.update("person", values, "name=?", new String[] {});
		db.close();
		return number;
	}

	public int deletePersonByName(String name) {
		SQLiteDatabase db = helper.getWritableDatabase();
		int number = db.delete("person", "name=?", new String[] { name });
		db.close();
		return number;
	}

	public List<Person> getAll() {
		SQLiteDatabase db = helper.getWritableDatabase();
		Cursor cursor = db.query("person", null, null, null, null, null, null);
		List<Person> persons = new ArrayList<Person>();
		while (cursor.moveToNext()) {
			int id = cursor.getInt(0);
			String name = cursor.getString(1);
			String number = cursor.getString(2);
			Person person = new Person(id, name, number);
			persons.add(person);
		}
		return persons;
	}
}

其中的Person数据模型(Bean):

package com.example.domain;

public class Person {
	private int id;
	private String name;
	private String number;
	public Person(int id, String name, String number) {
		super();
		this.id = id;
		this.name = name;
		this.number = number;
	}

	public int getId() {
		return id;
	}

	public String getName() {
		return name;
	}

	public String getNumber() {
		return number;
	}

	public void setId(int id) {
		this.id = id;
	}

	public void setName(String name) {
		this.name = name;
	}

	public void setNumber(String number) {
		this.number = number;
	}

	@Override
	public String toString() {
		return "Person [id=" + id + ", name=" + name + ", number=" + number
				+ "]";
	}
}

Android单元测试:

package com.example.test; 
/**
 * 如果不想每次都getAll()
 * sqlite3の使用
 */
import java.util.List;

import android.test.AndroidTestCase;

import com.example.dao.PersonDAO;
import com.example.dao.PersonDAO2;
import com.example.db.PersonSQLiteOpenHelper;
import com.example.domain.Person;

public class TestPersonDB extends AndroidTestCase {
	public void testCreateDB() throws Exception
	{
		PersonSQLiteOpenHelper helper = new PersonSQLiteOpenHelper(getContext(),"person.db",null,1);
		helper.getWritableDatabase();
	}
	public void testAdd() throws Exception{
		PersonDAO dao = new PersonDAO(getContext());
		for(int i = 123131121;i<123131121+20;i++)
		dao.add("w5", i+"");
	}
	public void testFindPersonByName() throws Exception{
		PersonDAO dao = new PersonDAO(getContext());
		boolean result = dao.findPersonByName("w5");
		AndroidTestCase.assertEquals(result, true);
	}
	public void testGetAll() throws Exception{
		PersonDAO dao = new PersonDAO(getContext());
		List<Person> all = dao.getAll();
		System.out.println(all);
	}
	public void testUpdateByName() throws Exception{
		PersonDAO dao = new PersonDAO(getContext());
		dao.add("w5", "110" +
				"");
	}
	public void testDeleteByName() throws Exception{
		PersonDAO dao = new PersonDAO(getContext());
		dao.deletePersonByName("w5");
	}
}

为了方便的查看DataBase的值， ADB提供了sqlite3工具

使用方法adb shell

sqlite3 xxx.db