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

q456123

　　声明: 此代码依然在项目 “数据库_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]