一. 操作对象
packagecom.example.model;
publicclassPerson {
privateInteger personId;
privateString name;
privateString phone;
publicInteger getPersonId() {
returnpersonId;
}
publicvoidsetPersonId(Integer personId) {
this.personId= personId;
}
publicString getName() {
returnname;
}
publicvoidsetName(String name) {
this.name= name;
}
publicString getPhone() {
returnphone;
}
publicvoidsetPhone(String phone) {
this.phone= phone;
}
publicPerson(Integer personId, String name, String phone) {
this.personId= personId;
this.name= name;
this.phone= phone;
}
publicPerson(String name, String phone) {
this.name= name;
this.phone= phone;
}
@Override
publicString toString() {
return"Person [personId="+ personId+ ", name="+ name + ",phone=" + phone+ "]";
}
}
二.操作类
packagecom.example.service;
importandroid.content.Context;
importandroid.database.sqlite.SQLiteDatabase;
importandroid.database.sqlite.SQLiteOpenHelper;
publicclassDBOpenHelper extendsSQLiteOpenHelper {
publicDBOpenHelper(Context context) {
/**
* context 上下文name数据库名称factory游标工厂,使用系统默认游标工厂可以用null表示version版本号
* 版本号要求不能为0
*/
//super(context, name, factory, version);
//数据库文件默认保存在当前应用包/database目录下
super(context,"andriod.db",null,2);
}
//在数据库每一次被创建的时候调用
/**
*dbOperate 数据库操作实例都可以通过SQLiteDatabase类操作数据库
*/
@Override
publicvoidonCreate(SQLiteDatabase dbOperate) {
String sql ="CREATE TABLE person(personidINTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(20))";
dbOperate.execSQL(sql);
}
//在数据库的版本号变化时被调用
@Override
publicvoidonUpgrade(SQLiteDatabase dbOperate, intoldVersion, intnewVersion) {
String sql = "ALTERTABLE person ADD phone VARCHAR(12) NULL";
dbOperate.execSQL(sql);
}
}
具体操作方法分两种情况
1.
packagecom.example.service;
importjava.util.ArrayList;
importjava.util.List;
importandroid.content.Context;
importandroid.database.Cursor;
importandroid.database.sqlite.SQLiteDatabase;
importcom.example.model.Person;
publicclassPersonService {
privateDBOpenHelper dbOpenHelper;
publicPersonService(Context context) {
this.dbOpenHelper= newDBOpenHelper(context);
}
publicvoidsave(Person person) {
SQLiteDatabasedbOperate = dbOpenHelper.getWritableDatabase();
//采用占位符,防止sql注入
String sql ="INSERT INTO person(name, phone)VALUES(?, ?)";
Object[] params =newObject[] { person.getName(), person.getPhone() };
dbOperate.execSQL(sql,params);
}
publicvoiddelete(Integer id) {
SQLiteDatabasedbOperate = dbOpenHelper.getWritableDatabase();
String sql ="DELETE FROM person WHERE personid= ?";
Object[] params =newObject[] { id };
dbOperate.execSQL(sql,params);
}
publicvoidupdate(Person person) {
SQLiteDatabasedbOperate = dbOpenHelper.getWritableDatabase();
String sql ="UPDATE person SET name = ?, phone= ? WHERE personid = ?";
Object[] params =newObject[] { person.getName(), person.getPhone(), person.getPersonId()};
dbOperate.execSQL(sql,params);
}
publicPerson find(Integer id) {
//当数据库存储文件满的时候用dbOpenHelper.getReadableDatabase()得到的数据库实例只能读而不能写
//如果数据库存储文件没有满,那么得到的数据库存储实例和dbOpenHelper.getWritableDatabase()得到的一样
SQLiteDatabasedbOperate = dbOpenHelper.getReadableDatabase();
String sql ="SELECT * FROM person WHEREpersonid = ?";
String[] params =newString[] { String.valueOf(id)};
Cursor cursor =dbOperate.rawQuery(sql, params);
if(cursor.moveToFirst()) {
intpersonId = cursor.getInt(cursor.getColumnIndex("personid"));
String name =cursor.getString(cursor.getColumnIndex("name"));
String phone =cursor.getString(cursor.getColumnIndex("phone"));
Person person =newPerson(personId, name, phone);
returnperson;
}
cursor.close();
returnnull;
}
publicList<Person> getScrollData(intoffset, intmaxResult) {
List<Person>persons = newArrayList<Person>();
SQLiteDatabasedbOperate = dbOpenHelper.getReadableDatabase();
String sql ="SELECT * FROM person ORDER BYpersonid ASC LIMIT ?, ?";
String[] params =newString[] { String.valueOf(offset),String.valueOf(maxResult)};
Cursor cursor =dbOperate.rawQuery(sql, params);
while(cursor.moveToNext()) {
intpersonId = cursor.getInt(cursor.getColumnIndex("personid"));
String name =cursor.getString(cursor.getColumnIndex("name"));
String phone =cursor.getString(cursor.getColumnIndex("phone"));
Person person =newPerson(personId, name, phone);
persons.add(person);
}
cursor.close();
returnpersons;
}
publiclonggetCount() {
SQLiteDatabasedbOperate = dbOpenHelper.getReadableDatabase();
String sql ="SELECT COUNT(*) FROM person";
Cursor cursor =dbOperate.rawQuery(sql, null);
cursor.moveToFirst();
longresult = cursor.getLong(0);
returnresult;
}
}
2.
packagecom.example.service;
importjava.util.ArrayList;
importjava.util.List;
importandroid.content.ContentValues;
importandroid.content.Context;
importandroid.database.Cursor;
importandroid.database.sqlite.SQLiteDatabase;
importcom.example.model.Person;
publicclassOtherPersonService {
privateDBOpenHelper dbOpenHelper;
publicOtherPersonService(Context context) {
this.dbOpenHelper= newDBOpenHelper(context);
}
publicvoidsave(Person person) {
SQLiteDatabasedbOperate = dbOpenHelper.getWritableDatabase();
ContentValuesvalues = newContentValues();
values.put("name",person.getName());
values.put("phone",person.getPhone());
//空值字段作用:当第三个参数为空或者Map为空时用上第二个参数
//insert into person () values ();语法错误
//insert into person( name) values (NULL); 语法正确
dbOperate.insert("person",null,values);
}
publicvoiddelete(Integer id) {
SQLiteDatabasedbOperate = dbOpenHelper.getWritableDatabase();
dbOperate.delete("person","personid = ?",newString[] { id.toString() });
}
publicvoidupdate(Person person) {
SQLiteDatabasedbOperate = dbOpenHelper.getWritableDatabase();
ContentValuesvalues = newContentValues();
values.put("name",person.getName());
values.put("phone",person.getPhone());
dbOperate.update("person",values, "personid = ?",newString[] { person.getPersonId().toString() });
}
publicPerson find(Integer id) {
//当数据库存储文件满的时候用dbOpenHelper.getReadableDatabase()得到的数据库实例只能读而不能写
//如果数据库存储文件没有满,那么得到的数据库存储实例和dbOpenHelper.getWritableDatabase()得到的一样
SQLiteDatabasedbOperate = dbOpenHelper.getReadableDatabase();
String[] columns =newString[] { "personid","name","phone"};
String[] params =newString[] { String.valueOf(id)};
//如果columns为null就表示查询所有字段
Cursor cursor =dbOperate.query("person",columns, "personid = ?",params, null,null,null);
if(cursor.moveToFirst()) {
intpersonId = cursor.getInt(cursor.getColumnIndex("personid"));
String name =cursor.getString(cursor.getColumnIndex("name"));
String phone =cursor.getString(cursor.getColumnIndex("phone"));
Person person =newPerson(personId, name, phone);
returnperson;
}
cursor.close();
returnnull;
}
publicList<Person> getScrollData(intoffset, intmaxResult) {
List<Person>persons = newArrayList<Person>();
SQLiteDatabasedbOperate = dbOpenHelper.getReadableDatabase();
Cursor cursor =dbOperate.query("person",null,null,null,"personid ASC",null,null,offset + ","
+maxResult);
while(cursor.moveToNext()) {
intpersonId = cursor.getInt(cursor.getColumnIndex("personid"));
String name =cursor.getString(cursor.getColumnIndex("name"));
String phone =cursor.getString(cursor.getColumnIndex("phone"));
Person person =newPerson(personId, name, phone);
persons.add(person);
}
cursor.close();
returnpersons;
}
publiclonggetCount() {
SQLiteDatabasedbOperate = dbOpenHelper.getReadableDatabase();
Cursor cursor =dbOperate.query("person",newString[] { "COUNT(*)"}, null,null,null,null,null);
cursor.moveToFirst();
longresult = cursor.getLong(0);
returnresult;
}
}
packagecom.example.test;
importjava.util.ArrayList;
importjava.util.List;
importcom.example.model.Person;
importcom.example.service.PersonService;
importandroid.test.AndroidTestCase;
importandroid.util.Log;
publicclassPersonServiceTest extendsAndroidTestCase {
privatestaticfinalString TAG= "PersonServiceTest";
privatePersonService personService;
/**
*为什么不把personService= new PersonService(getContext());这句代码提出来
*解答:提出来之后personService是类PersonServiceTest的成员变量,而PersonServiceTest要先去实例化,实例化之后才会有上下文对象
*而成员变量是在类实例化过程中执行的,而此时还没有上下文对象,所以此时的getContect为NULL,所以不能提出来
*放到构造方法里也是不行的原因和上述原因一样
*放到setUp方法里是可以的
*/
publicvoidtestSave() throwsException {
personService= newPersonService(getContext());
Person person = newPerson("vector","123");
personService.save(person);
}
publicvoidtestDelete() throwsException {
personService= newPersonService(getContext());
personService.delete(1);
this.testScrollData();
}
publicvoidtestUpdate() throwsException {
personService= newPersonService(getContext());
Person person =personService.find(1);
person.setName("lisinihao");
personService.update(person);
Log.i(TAG,person.toString());
}
publicvoidtestFind() throwsException {
personService= newPersonService(getContext());
Person person =personService.find(2);
Log.i(TAG,person.toString());
}
publicvoidtestScrollData() throwsException {
personService= newPersonService(getContext());
List<Person>persons = newArrayList<Person>();
persons =personService.getScrollData(0,3);
for(Person person : persons) {
Log.i(TAG,person.toString());
}
}
publicvoidtestCount() throwsException {
personService= newPersonService(getContext());
longresult = personService.getCount();
Log.i(TAG,"Person "+ String.valueOf(result));
}
/* @Override
protected voidsetUp() throws Exception {
// TODOAuto-generated method stub
super.setUp();
}*/
}