基于Android SQLiteOpenHelper && CRUD 的使用

前端技术 2023/09/04 Android

复制代码 代码如下:

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;
 }
}

本文地址：https://www.stayed.cn/item/11255

转载请注明出处。

本站部分内容来源于网络,如侵犯到您的权益,请 联系我

我的博客

人生若只如初见，何事秋风悲画扇。

我的标签

随笔档案