基于Android SQLiteOpenHelper && CRUD 的使用
public class dbopenhelper extends sqliteopenhelper {
// 类没有实例化,不能用作父类构造器的参数,必须声明为静态
public dbopenhelper(context context, int version) {
super(context, "sqlite.db", null, version);
// 第三个参数cursorfactory指定在执行查询时获得一个游标实例的工厂类,设置为null代表使用系统默认的游标工厂;
}
@override
public void oncreate(sqlitedatabase db) {// 数据库第一次被创建的时候被调用,最开始创建数据库时赋的版本号值是0
// sqlitedatabase用于生成数据库表;数据库存放路径:<package name>/databases/
db.execsql("create table person(personid integer primary key autoincrement, name varchar(20))");
}
@override
public void onupgrade(sqlitedatabase db, int oldversion, int newversion) {// 版本号升级的时候进行调用;如果数据库不存在就不调用
db.execsql("alter table person add phone varchar(12) null");// 往表中增加一列
}
}
dbopenhelper dbopenhelper = new dbopenhelper(getcontext(), 1);
dbopenhelper.getwritabledatabase();// 会create数据库表or/open数据库表
public class personservice {
private dbopenhelper dbopenhelper;
public personservice(context context) {
super();
this.dbopenhelper = new dbopenhelper(context, 2);
}
public void save(person person) {
sqlitedatabase db = dbopenhelper.getwritabledatabase();
// sqlitedatabase db2 = dbopenhelper.getwritabledatabase();有缓存的功能,同一个helper用此方法得到的对象为db;
db.execsql("insert into person(name,phone) values(?,?)", new object[] {person.getname(), person.getphone() });
}
public void delete(integer id) {
sqlitedatabase db = dbopenhelper.getwritabledatabase();
db.execsql("delete from person where personid=?", new object[] { id });
}
public void update(person person) {
sqlitedatabase db = dbopenhelper.getwritabledatabase();
db.execsql("update person set name=?,phone=? where personid=?",new object[] { person.getname(), person.getphone(),person.getid() });
}
public person find(integer id) {
sqlitedatabase db = dbopenhelper.getreadabledatabase();
// 如果数据库磁盘空间未满,得到的实例即为getwritabledatabase()得到的对象;因为此方法会调用getwritabledatabase方法;
cursor cursor = db.rawquery("select * from person where personid=?",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;
}
/*
* 获取分页数据
*/
public list getscolldata(int offest, int maxresult) {
list persons = new arraylist();
sqlitedatabase db = dbopenhelper.getreadabledatabase();
cursor cursor = db.rawquery("select * from person order by personid asc limit ?,?",new string[] { string.valueof(offest),string.valueof(maxresult) });
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));
}
cursor.close();
return persons;
}
public long getcount() {
sqlitedatabase db = dbopenhelper.getreadabledatabase();
cursor cursor = db.rawquery("select count(*) from person", null);
cursor.movetofirst();
long result = cursor.getlong(0);
return result;
}
}
public class otherpersonservice {
private dbopenhelper dbopenhelper;
public otherpersonservice(context context) {
super();
this.dbopenhelper = new dbopenhelper(context, 2);
}
public void save(person person) {
sqlitedatabase db = dbopenhelper.getwritabledatabase();
contentvalues values = new contentvalues();// contentvalues 专门用于保存字段的值
values.put("name", person.getname());
values.put("phone", person.getphone());
db.insert("person", null, values);// 第三个参数为字段值,第二个参数为空值字段,如果第三个参数为null
}
public void delete(integer id) {
sqlitedatabase db = dbopenhelper.getwritabledatabase();
db.delete("person", "personid=?", new string[] { id.tostring() });
}
public void update(person person) {
sqlitedatabase db = dbopenhelper.getwritabledatabase();
contentvalues values = new contentvalues();
values.put("name", person.getname());
values.put("phone", person.getphone());
db.update("person", values, "personid=?", new string[] { person.getid().tostring() });
}
public person find(integer id) {
sqlitedatabase db = dbopenhelper.getreadabledatabase();
cursor cursor = db.query("person", null, "personid=?",new string[] { id.tostring() }, null, null, null);
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;
}
/*
* 获取分页数据
*/
public list getscolldata(int offest, int maxresult) {
list persons = new arraylist();
sqlitedatabase db = dbopenhelper.getreadabledatabase();
cursor cursor = db.query("person", null, null, null, null, null,"personid asc", offest + "," + maxresult);
//db.query(table, columns, selection, selectionargs, groupby, having, orderby, limit);
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));
}
cursor.close();
return persons;
}
public long getcount() {
sqlitedatabase db = dbopenhelper.getreadabledatabase();
cursor cursor = db.query("person", new string[] { "count(*)" }, null, null, null, null, null);
//db.query(table, columns, selection, selectionargs, groupby, having, orderby);
cursor.movetofirst();
long result = cursor.getlong(0);
return result;
}
}