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}/** * 数据库第一次被创建的时候调用的方法 */@Overridepublic void onCreate(SQLiteDatabase db) {// TODO Auto-generated method stubdb.execSQL("create table person (id integer primary key autoincrement, name varchar(20),number varchar(20),account varchar(20))");}/** * 只有在数据库版本发生变化的时候调用 * @param db * @param oldVersion * @param newVersion */@Overridepublic 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;}@Overridepublic 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