无废话Android 系列教程34 [使用系统api实现SQLite增删改查&&测试]

声明: 此代码依然在项目 “数据库_SqlLite” 项目基础上开发，如果没有此项目代码，请到33课教程的附件中下载！

1. com.andy.db.dao 包下新建立类 PersonDao2.java

package com.andy.db.dao;

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

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

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

public class PersonDao2 {
	private PersonSQLiteOpenHelper helper;
	public PersonDao2(Context context){
		helper = new PersonSQLiteOpenHelper(context);
	}
	
	/*
	 * 插入用户名和手机号到SQLite数据库
	 * 
	 * the row ID of the newly inserted row, or -1 if an error occurred
	 */
	public long addPerson(String name, String number){
		
		//此时就获取到了数据库
		SQLiteDatabase db = helper.getWritableDatabase();
		//db.execSQL("insert into person (name,number) values (?,?)", new Object[]{name,number});
		ContentValues values = new ContentValues();
		values.put("name", name);
		values.put("number", number);
		long row_id = db.insert("person", null, values);
		db.close();
		return row_id;
	}
	
	/**
	 * 删除一条数据，按用户名的条件
	 * @param name
	 * 
	 * WhereClause :the optional WHERE clause to apply when deleting. Passing null will delete all rows.
	 */
	public int deletePerson(String name){
		SQLiteDatabase db = helper.getWritableDatabase();
//		db.execSQL("delete from person where name =?", new Object[]{name});
		int row_id = db.delete("person", "name=?", new String[]{name});
		db.close();
		
		return row_id;
	}
	
	
	/**
	 * 修改一条数据,按用户名的条件
	 * @param name
	 * @param number
	 */
	public int updatePerson(String name, String number){
		SQLiteDatabase db = helper.getWritableDatabase();
//		db.execSQL("update person set number = ? where name = ?", new Object[]{name,number} );
		ContentValues values = new  ContentValues();
		values.put("number", number);
		
		int row_id = db.update("person", values, "name=?", new String[]{name});
		db.close();
		
		return row_id;
	}
	
	/**
	 * 查询一条数据,按用户名的条件
	 * @param name
	 * @param number
	 * selection : A filter declaring which rows to return, formatted as an SQL WHERE clause (excluding the WHERE itself). Passing null will return all rows for the given table.
	 * selectionArgs:  You may include ?s in selection, which will be replaced by the values from selectionArgs, in order that they appear in the selection. The values will be bound as Strings.
	 */
	public boolean findPerson(String name){
		SQLiteDatabase db = helper.getWritableDatabase();
//		Cursor cursor = db.rawQuery("select * from person where name=?", new String[]{name});
		Cursor cursor = db.query("person", new String[]{"id", "name", "number"}, "name=?", new String[]{name}, null, null, null);
		boolean result = cursor.moveToNext();
		cursor.close();
		db.close();
		return result;
	}
	
	
	public List<Person> findAllPerson(){
		SQLiteDatabase db = helper.getWritableDatabase();
//		Cursor cursor = db.rawQuery("select * from person", null);
		Cursor cursor = db.query("person", new String[]{"id","name","number"}, null, null, null, null, null);
		List<Person> persons = new ArrayList<Person>(); 
		Person person;
		while(cursor.moveToNext()){
			int id = cursor.getInt(cursor.getColumnIndex("id"));
			String name = cursor.getString(cursor.getColumnIndex("name"));
			String number = cursor.getString(cursor.getColumnIndex("number"));
			person = new Person(id,name,number);
			persons.add(person);
		}
		cursor.close();
		db.close();
		return persons;
	}

}

2.  com.andy.db.test 包下创建TestPersonDao2.java

package com.andy.db.test;

import java.util.List;

import com.andy.db.PersonSQLiteOpenHelper;
import com.andy.db.dao.PersonDao2;
import com.andy.db.domain.Person;

import android.test.AndroidTestCase;

public class TestPersonDao2 extends AndroidTestCase {
	
public PersonDao2 pd;
	
	/**
	 * getContext() 是测试框架给提供的上下文件
	 * 
	 * 来源:android.test.AndroidTestCase.getContext()
	 */
	public void testCreatePersonDb(){
		PersonSQLiteOpenHelper db = new PersonSQLiteOpenHelper(getContext());
		//打开或创建一个可写的数据库
		db.getWritableDatabase();
	}
	
	public void testAddPerson(){
		pd = new PersonDao2(getContext());
		pd.addPerson("张三", "123456789");
	}
	
	public void testDeletePerson(){
		pd = new PersonDao2(getContext());
		pd.deletePerson("张三");
	}
	
	public void testUpdatePerson(){
		pd = new PersonDao2(getContext());
		pd.updatePerson("李四", "987654321");
	}

	public void testFindPerson(){
		pd = new PersonDao2(getContext());
		boolean result = pd.findPerson("李四");
		assertEquals(true, result);
	}
	
	public void testFindAll(){
		pd = new PersonDao2(getContext());
		List<Person> persons = pd.findAllPerson();
		
		for(Person p: persons){
			System.out.println(p.toString());
		}
	}

}

3.  测试:  在DDMS模式下，将database数据库导出到桌面，然后使用Navicat或其它工具将其导入/打开, 这时就可以随时查看数据库了

   1) 将项目内的数据库导出到本地:

   2) 使用Navicat 工具导入Person.db数据库

   3) 查看Person 数据表

 5. 下载源代码，请参照附件 → [数据库_SqlLite.rar]