import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DBOpenHelper extends SQLiteOpenHelper {
public DBOpenHelper(Context context) {
super(context, "people.db", null, 1);
}
@Override
public void onCreate(SQLiteDatabase db) {
//创建人员表
db.execSQL("drop table if exists people");
db.execSQL("create table people (id integer primary key autoincrement,name varchar(64),sex smallint,contact varchar(64))");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
import java.util.ArrayList;
import java.util.List;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.text.people.People;
import com.text.people.StrUtil;
public class PeopleService {
private DBOpenHelper dbOpenHelper;
private static PeopleService instance = null;
public PeopleService(Context context) {
this.dbOpenHelper = new DBOpenHelper(context);
}
public synchronized static PeopleService getInstance(Context ctx) {
if (null == instance) {
instance = new PeopleService(ctx);
}
return instance;
}
//增加数据
public void save(People people) {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name", people.getName());
values.put("sex", people.getSex());
values.put("contact", people.getContact());
db.insert("people", null, values);
}
//根据id删除日志
public void delete(Integer id) {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.delete("people", "id=?", new String[] { id.toString() });
}
//删除所有数据
public void deleteAll() {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.delete("people", "", null);
}
//根据id修改数据
public void update(People people) {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name", people.getName());
values.put("sex", people.getSex());
values.put("contact", people.getContact());
db.update("people", values, "id=?", new String[] { people.getId().toString() });
}
//根据id查询单条数据
public People find(Integer id) {
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.query("people",new String[] { "id,name,sex,contact" },
"id=?", new String[] { id.toString() }, null, null,
null, "1");
try {
if (cursor.moveToFirst()) {
People people = new People();
people.setId(id);
people.setName(cursor.getString(cursor.getColumnIndex("name")));
people.setSex(cursor.getInt(cursor.getColumnIndex("sex")));
people.setContact(cursor.getString(cursor.getColumnIndex("contact")));
return people;
}
return null;
} finally {
cursor.close();
}
}
//分页加载数据
public List<People> getScrollData(int currentPage, int lineSize,String whereStr) {
String firstResult = String.valueOf((currentPage - 1) * lineSize);
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = null;
try {
if(StrUtil.isNull(whereStr)){
cursor = db.query("people",
new String[] { "id,name,sex,contact" },
null, null, null, null, "id desc",
firstResult + "," + lineSize);
}else{
cursor = db.query("people",
new String[] { "id,name,sex,contact" },
"name like ? or contact like ?", new String[]{"%"+whereStr+"%","%"+whereStr+"%"}, null, null, "id desc",
firstResult + "," + lineSize);
}
List<People> peoples = new ArrayList<People>();
while (cursor.moveToNext()) {
People people = new People();
people.setId(cursor.getInt(cursor.getColumnIndex("id")));
people.setName(cursor.getString(cursor.getColumnIndex("name")));
people.setSex(cursor.getInt(cursor.getColumnIndex("sex")));
people.setContact(cursor.getString(cursor.getColumnIndex("contact")));
peoples.add(people);
}
return peoples;
} finally {
if(cursor!=null){
cursor.close();
}
}
}
//查询总记录数
public int getCount() {
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select count(1) from people", null);
try {
cursor.moveToFirst();
return cursor.getInt(0);
} finally {
cursor.close();
}
}
}