Android连接数据库sqlite并进行简单的表创建和增删改查功能参考代码,使用Android单元测试进行验证,首先新建项目进行配置单元测试环境请参考博客:http://blog.csdn.net/bq1073100909/article/details/37885457
项目资源目录图:
Person.java代码:(实体类)
package org.dyb.domain;
public class Person {
private Integer personid;
private String name;
private String phone;
public Person(){}
public Person(String name,String phone){
this.name=name;
this.phone=phone;
}
public Person(Integer personid,String name,String phone){
this.personid=personid;
this.name=name;
this.phone=phone;
}
public Integer getPersonid() {
return personid;
}
public void setPersonid(Integer personid) {
this.personid = personid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
}
DBOpenHelper.java 继承SQLiteOpenHelper抽象类,实现数据库的连接和创建表
package org.dyb.service;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
public class DBOpenHelper extends SQLiteOpenHelper {
public DBOpenHelper(Context context) {
//参数说明:第二个是数据库文件名称,第三个是游标工厂,第四个是版本号
super(context, "dyb.db", null, 2);//默认放在<包>/databases
}
//数据库第一次被创建的时候调用
@Override
public void onCreate(SQLiteDatabase db) {
String sql="create table person(personid integer primary key autoincrement,name varchar(20))";
db.execSQL(sql);
}
//super(context, "dyb.db", null, 1);数据库文件的版本号发生变更的时候调用(例如把上面1修改为2)
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
String sql="alter table person add phone varchar(12) null";
db.execSQL(sql);
}
}
对表内容进行增删改查:
PersonService.java代码如下:
package org.dyb.service;
import java.util.ArrayList;
import java.util.List;
import org.dyb.domain.Person;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
public class PersonService {
private DBOpenHelper dbOpenHelper;
public PersonService(Context context){
this.dbOpenHelper=new DBOpenHelper(context);
}
/**
* 保存用户
* @param person
*/
public void save(Person person){
SQLiteDatabase db=dbOpenHelper.getWritableDatabase();
String sql="insert into person(name,phone) values(?,?)";
db.execSQL(sql,new Object[]{person.getName(),person.getPhone()});
db.close();
}
/**
* 删除用户
* @param id
*/
public void delete(Integer id){
SQLiteDatabase db=dbOpenHelper.getWritableDatabase();
String sql="delete from person where personid=?";
db.execSQL(sql,new Object[]{id});
db.close();
}
/**
* 更新用户
* @param person
*/
public void update(Person person){
SQLiteDatabase db=dbOpenHelper.getWritableDatabase();
String sql="update person set name=?,phone=? where personid=?";
db.execSQL(sql,new Object[]{person.getName(),person.getPhone(),person.getPersonid()});
}
/**
* 通过ID查找用户
* @param id
* @return
*/
public Person find(Integer id){
SQLiteDatabase db=dbOpenHelper.getReadableDatabase();
String sql="select * from person where personid=?";
Cursor cursor = db.rawQuery(sql, new String[]{id.toString()});
if(cursor.moveToFirst()){
int personid = cursor.getInt(cursor.getColumnIndex("personid"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
return new Person(personid,name,phone);
}
cursor.close();
return null;
}
/**
* 分页查询
* @param offset 跳过前面多少条记录
* @param maxResult 每页获取多少条记录
* @return
*/
public List<Person> getScrollData(int offset,int maxResult){
List<Person> persons=new ArrayList<Person>();
SQLiteDatabase db=dbOpenHelper.getReadableDatabase();
String sql="select * from person order by personid asc limit ? offset ?";
Cursor cursor=db.rawQuery(sql, new String[]{String.valueOf(maxResult),String.valueOf(offset)});
while(cursor.moveToNext()){
int personid = cursor.getInt(cursor.getColumnIndex("personid"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
persons.add(new Person(personid,name,phone));
}
return persons;
}
/**
* 得到数据库数据总个数
* @return
*/
public long getCount(){
SQLiteDatabase db=dbOpenHelper.getReadableDatabase();
String sql="select count(*) from person ";
Cursor cursor = db.rawQuery(sql, null);
cursor.moveToFirst();
long result=cursor.getLong(0);
return result;
}
/**
* 事务测试,同时执行成功提交事务
*/
public void updateTransaction()throws Exception{
SQLiteDatabase db=dbOpenHelper.getWritableDatabase();
Person person1=this.find(1);
person1.setName("x1");
Person person2=this.find(2);
person2.setName("x2");
db.beginTransaction();//事务开启
try{
this.update(person1);
this.update(person2);
db.setTransactionSuccessful();//设置事务成功,这样关闭事务时候会提交数据
}finally{
db.endTransaction();//如果事务成功提交事务 否则回滚
}
db.close();
}
public DBOpenHelper getDbOpenHelper() {
return dbOpenHelper;
}
public void setDbOpenHelper(DBOpenHelper dbOpenHelper) {
this.dbOpenHelper = dbOpenHelper;
}
}
单元测试类 PersonServiceTest.java代码如下:
package org.dyb.test;
import java.util.List;
import org.dyb.domain.Person;
import org.dyb.service.DBOpenHelper;
import org.dyb.service.PersonService;
import android.test.AndroidTestCase;
import android.util.Log;
public class PersonServiceTest extends AndroidTestCase {
public void testCreateDB()throws Exception{
DBOpenHelper dbOpenHelper=new DBOpenHelper(getContext());
dbOpenHelper.getWritableDatabase();
}
public void testSave()throws Exception{
PersonService personService=new PersonService(this.getContext());
for(int i=0;i<20;i++){
Person person=new Person("zhangsan"+i,"123456789");
personService.save(person);
}
}
public void testUpdate()throws Exception{
PersonService personService=new PersonService(this.getContext());
Person person = personService.find(1);
person.setName("lisi");
personService.update(person);
}
public void testDelete()throws Exception{
PersonService personService=new PersonService(this.getContext());
personService.delete(12);
}
public void testFind()throws Exception{
PersonService personService=new PersonService(this.getContext());
Person person = personService.find(3);
Log.i("find", person.getName());
}
public void testScrollData()throws Exception{
PersonService personService=new PersonService(this.getContext());
List<Person> persons = personService.getScrollData(5, 4);
for(Person per:persons){
Log.i("fenye", per.getPersonid()+"");
}
}
public void testCount()throws Exception{
PersonService personService=new PersonService(this.getContext());
Log.i("count",personService.getCount()+"");
}
/**
* 测试事务,只有两个更新都成功才提交事务
* @throws Exception
*/
public void testUpdateTransaction()throws Exception{
PersonService personService=new PersonService(this.getContext());
personService.updateTransaction();
}
}
首先创建表,执行保存用户,id为1的name是zhangsan,然后执行更新测试,id为1的name是lisi,为了方便测试分页显示输出,循环添加数据,执行删除测试,删除掉id是12的用户,执行事务管理测试,把id是1和2的同时更新为x1和x2,事务提交。参考图如下:(我使用的是RE管理器打开的数据库文件dyb.db,数据库文件默认在项目包名的databases目录下)