十、PersonService.java 业务类源码：
package com.example.service;
import java.util.ArrayList;
import java.util.List;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.example.domain.Person;
public class PersonService {
private DBOperateHelper dbOperateHelper;
public PersonService(Context context) {
this.dbOperateHelper = new DBOperateHelper(context);
}
/**
* 保存记录
* @param person
*/
public void save(Person person){
//得到数据库实例，里面封装了数据库操作方法
SQLiteDatabase sqldb = dbOperateHelper.getWritableDatabase();
//sqldb.execSQL("insert into person(name,phone) values('"+person.getName()+"','"+person.getPhone()+"')");
//利用占位符可以避免注入，但是注意数组参与与占位符对应的字段要一一对应
sqldb.execSQL("insert into person(name,phone,amount) values(?,?,?)",new Object[]{person.getName(),person.getPhone(),person.getAmount()});
//关闭数据库
sqldb.close();
}
/**
* 删除记录
* @param id
*/
public void delete(Integer id){
SQLiteDatabase sqldb = dbOperateHelper.getWritableDatabase();
sqldb.execSQL("delete from person where personid=?",new Object[]{id});
sqldb.close();
}
/**
* 更新记录
* @param person
*/
public void update(Person person){
SQLiteDatabase sqldb = dbOperateHelper.getWritableDatabase();
sqldb.execSQL("update person set name=?,phone=?,amount=? where personid=?",new Object[]{person.getName(),person.getPhone(),person.getAmount(),person.getId()});
sqldb.close();
}
/**
* 通过ID查询记录
* @param id
* @return
*/
public Person find(Integer id){
/**
* getWritableDatabase 与 getReadableDatabase 的区别：
* getReadableDatabase会先返回getWritableDatabase(可写)，如果调用getWritableDatabase失败
* 则才会调用getReadableDatabase后续方法，使数据库只读
* 当写入的数据超过数据库大小则调用getWritableDatabase会失败
* 所以只读时则可以使用此方法，其它情况(只要不是超过数据库大小)也可以使用此方法
*/
SQLiteDatabase sqldb = dbOperateHelper.getReadableDatabase();
Cursor cursor = sqldb.rawQuery("select * from person where personid=?", new String[]{String.valueOf(id)});
int personid;
String name;
String phone;
int amount;
Person person = null;
if(cursor.moveToFirst()){
personid = cursor.getInt(cursor.getColumnIndex("personid"));
name = cursor.getString(cursor.getColumnIndex("name"));
phone = cursor.getString(cursor.getColumnIndex("phone"));
amount = cursor.getInt(cursor.getColumnIndex("amount"));
person = new Person(personid,name,phone,amount);
}
cursor.close();
return person;
}
/**
* 返回指定长度记录，limit 3,5,适用于分页
* @param offset 起始
* @param maxResult 长度
* @return
*/
public List<Person> getScrollData(int offset,int maxResult){
SQLiteDatabase sqldb = dbOperateHelper.getReadableDatabase();
Cursor cursor = sqldb.rawQuery("select * from person order by personid asc limit ?,?", new String[]{String.valueOf(offset),String.valueOf(maxResult)});
int personid;
String name;
String phone;
int amount;
Person person = null;
List<Person> persons = new ArrayList<Person>();
while(cursor.moveToNext()){
personid = cursor.getInt(cursor.getColumnIndex("personid"));
name = cursor.getString(cursor.getColumnIndex("name"));
phone = cursor.getString(cursor.getColumnIndex("phone"));
amount = cursor.getInt(cursor.getColumnIndex("amount"));
person = new Person(personid,name,phone,amount);
persons.add(person);
}
cursor.close();
return persons;
}
/**
* 返回cursor
* @param offset 起始
* @param maxResult 长度
* @return
*/
public Cursor getCursorScrollData(int offset,int maxResult){
SQLiteDatabase sqldb = dbOperateHelper.getReadableDatabase();
Cursor cursor = sqldb.rawQuery("select personid as _id,name,phone,amount from person order by personid asc limit ?,?", new String[]{String.valueOf(offset),String.valueOf(maxResult)});
return cursor;
}
/**
* 返回总记录数
* @return
*/
public long getCount(){
SQLiteDatabase sqldb = dbOperateHelper.getReadableDatabase();
Cursor cursor = sqldb.rawQuery("select count(*) from person", null);
//该查询语句值返回一条语句
cursor.moveToFirst();
long result = cursor.getLong(0);
cursor.close();
return result;
}
public void payment(){
SQLiteDatabase sqldb = dbOperateHelper.getWritableDatabase();
sqldb.beginTransaction();//开启事务
try{
sqldb.execSQL("update person set amount = amount -10 where personid=1");
sqldb.execSQL("update person set amount = amount + 10 where personid=2");
sqldb.setTransactionSuccessful();//设置事务标志位true
} finally {
//结束事务：有两种情况：commitrollback，事务提交或者回滚是由事务的标识决定的
//事务为ture则提交，事务为flase则回滚，默认为false
sqldb.endTransaction();
}
}
}
