定义接口
package com.example.android_db.service;
import java.util.List;
import java.util.Map;
public interface PersonService {
public boolean addPerson(Object[] params);
public boolean deletePerson(Object[] params);
public boolean updatePerson(Object[] params);
public Map<String,String> viewPerson(String[] selectionArgs);
public List<Map<String,String>> listPersonMaps(String[] selectionArgs);
}
实现接口:
package com.example.android_db.dao;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.example.android_db.db.DbOpenHelper;
import com.example.android_db.service.PersonService;
public class PersonDao implements PersonService {
private DbOpenHelper helper = null;
public PersonDao(Context context) {
// TODO Auto-generated constructor stub
helper = new DbOpenHelper(context);
}
@Override
public boolean addPerson(Object[] params) {
// TODO Auto-generated method stub
boolean flag = false;
//实现对数据库的添加删除和修改查询的功能
SQLiteDatabase database = null;
try {
String sql = "insert into person(name,address,sex) values(?,?,?)";
database = helper.getWritableDatabase();//实现对数据库的写的操作
database.execSQL(sql, params);
flag = true;
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally{
if(database!=null){
database.close();
}
}
return flag;
}
@Override
public boolean deletePerson(Object[] params) {
// TODO Auto-generated method stub
boolean flag = false;
SQLiteDatabase database = null;
try {
String sql = "delete from person where id = ? ";
database = helper.getWritableDatabase();
database.execSQL(sql, params);
flag = true;
} catch (Exception e) {
// TODO: handle exception
}finally{
if(database!=null){
database.close();
}
}
return flag;
}
@Override
public boolean updatePerson(Object[] params) {
// TODO Auto-generated method stub
boolean flag = false;
SQLiteDatabase database = null;
try {
String sql = "update person set name = ? ,address = ?, sex = ? where id = ? ";
database = helper.getWritableDatabase();
database.execSQL(sql, params);
flag = true;
} catch (Exception e) {
// TODO: handle exception
}finally{
if(database!=null){
database.close();
}
}
return flag;
}
@Override
public Map<String, String> viewPerson(String[] selectionArgs) {
Map<String,String> map = new HashMap<String, String>();
SQLiteDatabase database = null;
try {
String sql = "select * from person where id = ? ";
database = helper.getReadableDatabase();
Cursor cursor = database.rawQuery(sql, selectionArgs);
//获得数据库的列的个数
int colums = cursor.getColumnCount();
while(cursor.moveToNext()){
for(int i=0;i<colums;i++){
String cols_name = cursor.getColumnName(i);
String cols_value = cursor.getString(cursor.getColumnIndex(cols_name));
if(cols_value==null){
cols_value = "";
}
map.put(cols_name, cols_value);
}
}
} catch (Exception e) {
// TODO: handle exception
}finally{
if(database!=null){
database.close();
}
}
return map;
}
@Override
public List<Map<String, String>> listPersonMaps(String[] selectionArgs) {
// TODO Auto-generated method stub
List<Map<String,String>> list = new ArrayList<Map<String,String>>();
String sql = "select * from person ";
SQLiteDatabase database = null;
try {
database = helper.getReadableDatabase();
Cursor cursor = database.rawQuery(sql, selectionArgs);
int colums = cursor.getColumnCount();
while(cursor.moveToNext()){
Map<String,String> map = new HashMap<String, String>();
for(int i=0;i<colums;i++){
String cols_name = cursor.getColumnName(i);
String cols_value = cursor.getString(cursor.getColumnIndex(cols_name));
if(cols_value==null){
cols_value="";
}
map.put(cols_name, cols_value);
}
list.add(map);
}
} catch (Exception e) {
// TODO: handle exception
}finally{
if(database!=null){
database.close();
}
}
return list;
}
}
实现SQLiteOpenHelper的子类
package com.example.android_db.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 DbOpenHelper extends SQLiteOpenHelper {
private static String name = "mydb.db";// 表示数据库的名称
private static int version = 2;// 表示数据库的版本号码
public DbOpenHelper(Context context) {
super(context, name, null, version);
}
// 当数据库创建的时候,是第一次被执行,完成对数据库的表的创建
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
//支持的数据类型:整型数据,字符串类型,日期类型,二进制的数据类型,
String sql = "create table person(id integer primary key autoincrement,name varchar(64),address varchar(64))";
db.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
String sql = "alter table person add sex varchar(8)";
db.execSQL(sql);
}
}
测试类:
package com.example.android_db.test;
import java.util.List;
import java.util.Map;
import com.example.android_db.dao.PersonDao;
import com.example.android_db.db.DbOpenHelper;
import com.example.android_db.service.PersonService;
import android.test.AndroidTestCase;
import android.util.Log;
public class MyTest extends AndroidTestCase {
public MyTest() {
// TODO Auto-generated constructor stub
}
public void createDb(){
DbOpenHelper helper = new DbOpenHelper(getContext());
helper.getWritableDatabase();
}
public void insertDB(){
PersonService service = new PersonDao(getContext());
Object[] params = {"李斯","广西","女"};
boolean flag = service.addPerson(params);
System.out.println("--->>"+flag);
}
public void deleteDB(){
PersonService service = new PersonDao(getContext());
Object[] params = {1};
boolean flag = service.deletePerson(params);
System.out.println("--->>"+flag);
}
public void updateDB(){
PersonService service = new PersonDao(getContext());
Object[] params = {"王五","上海","不祥","3"};
service.updatePerson(params);
}
public void viewDB(){
PersonService service = new PersonDao(getContext());
String[] selectionArgs = {"3"};
Map<String, String> map = service.viewPerson(selectionArgs);
Log.i("Test", "-->>"+map.toString());
}
public void listDB(){
PersonService service = new PersonDao(getContext());
List<Map<String,String>> list = service.listPersonMaps(null);
Log.i("Test", "-->>"+list.toString());
}
}