Android持久化----SQLiteOpenHelper
来源:互联网 发布:九零后网络郑州网站 编辑:程序博客网 时间:2024/06/05 19:08
开门见山的说,SQLiteOpenHelper的特点是简单。使用SQL语句进行事物操作,并未实现ORM。只要会SQL语句,便可驾轻就熟。
SQLiteOpenHelper中有两个抽象方法,因此,SQLiteOpenHelper是一个abstract class,在使用时需要对其进行继承。
两个抽象方法,分别是
public abstract void onCreate(SQLiteDatabase db);public abstract void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion);
前者,用于创建数据库。仅在数据库不存在时运行。
后者,用于数据库的更新,在数据库结构变化时运行。操作为原子操作。
可以通过SQLiteOpenHelper的两个方法,获取数据库对象,分别是
public SQLiteDatabase getWritableDatabase() public SQLiteDatabase getReadableDatabase()
前者用于写操作,后者用于读操作。
有了数据库对象,我们就可以进一步的进行事务操作。SQLiteDatabase提供了以下常用方法。
public long insert(String table, String nullColumnHack, ContentValues values) public int delete(String table, String whereClause, String[] whereArgs)public int update(String table, ContentValues values, String whereClause, String[] whereArgs) public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)
如果不习惯,还有最原始的sql方法
public void execSQL(String sql) throws SQLExceptionpublic Cursor rawQuery(String sql, String[] selectionArgs)
前者,用于增删改。后者,用于查。
当然,还有我们的事务原子性操作方法。
public void beginTransaction() public void setTransactionSuccessful()public void endTransaction()
beginTransaction开启一个事务。
endTransaction结束一个事务。
若endTransaction前,未触发setTransactionSuccessful,则回滚,否则提交事务。
接下来,我们用实例来演示SQLiteOpenHelper的增删改查。
实例设计主体需求
(1)创建名为Comparny的数据库,版本号为1
(2)创建表Person,包括id(主键,自增长),name(字符型),age(整数型),sex(布尔型,true为女性)
(3)创建表Salary,包括salaryDate(date型),userid(Person主键),amount(浮点型)
(4)添加员工A(“A”,22,true),员工B(“B”,23,false),员工(“C”,30,false)
(5)添加工资条(1,2017-12-17,12345.67),(2,2017-12-17,15000),(3,2017-12-17,23456.78)
(6)修改C的工资至25000.7
(7)查询C的工资。
(8)B离职,删除员工B,及工资条
(9)为Person表,添加职位列Title(字符型)。
(10)为A添加Title(“developer”),为C添加Title(“Leader”)
需要注意的是,sqlite无布尔型变量,因此使用int代替。
开工~
需求(1)在创建SQLiteOpenHelper子类时完成。
myHelper dbHelper=new myHelper(this,"Company",null,1);//1为版本号,this为Context,“company”为数据库名称。
需求(2)(3)在onCreate中完成。
myHelper核心代码如下
public static String TABLE_PERSON="PERSON"; public static String COLUMN_PERON_ID="id"; public static String COLUMN_NAME="name"; public static String COLUMN_AGE="age"; public static String COLUMN_SEX="sex"; public static String TABLE_SALARY="SALARY"; public static String COLUMN_USER_ID="userid"; public static String COLUMN_AMOUNT="money"; public static String COLUMN_SALARY_DATE="salaryDate"; private static String CREATE_PERSON_TABLE="create table "+TABLE_PERSON+ " (" +COLUMN_PERON_ID+" integer primary key autoincrement, " +COLUMN_NAME+" text, " +COLUMN_AGE+" int, " +COLUMN_SEX+" int)"; private static String CREATE_SALARY_TABLE="create table "+TABLE_SALARY+" (" +COLUMN_USER_ID+" integer, " +COLUMN_AMOUNT+" float, " +COLUMN_SALARY_DATE+" date)"; public myHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) { super(context, name, factory, version); } @Override public void onCreate(SQLiteDatabase db) { Log.i(TAG, "onCreate SQLite Database"); db.execSQL(CREATE_PERSON_TABLE); db.execSQL(CREATE_SALARY_TABLE); }
需求(4)(5)使用insert完成
private void addPersonAndSalary(){ ContentValues dataSet=new ContentValues(); Person person; SQLiteDatabase db=null; try { db= dbHelper.getWritableDatabase(); }catch (Exception ex){ Log.i(TAG, "addPersonAndSalary: execption"); } db.beginTransaction(); Log.i(TAG, "addPersonAndSalary: Add A"); person=new Person("A",22,true); dataSet.put(myHelper.COLUMN_NAME,person.getName()); dataSet.put(myHelper.COLUMN_AGE,person.getAge()); dataSet.put(myHelper.COLUMN_SEX,person.getSex()?1:0); db.insert(myHelper.TABLE_PERSON,null,dataSet); addSalaryInfo(db,person,12345.67f); dataSet.clear(); Log.i(TAG, "addPersonAndSalary: Add B"); person=new Person("B",23,false); dataSet.put(myHelper.COLUMN_NAME,person.getName()); dataSet.put(myHelper.COLUMN_AGE,person.getAge()); dataSet.put(myHelper.COLUMN_SEX,person.getSex()?1:0); db.insert(myHelper.TABLE_PERSON,null,dataSet); addSalaryInfo(db,person,15000f); dataSet.clear(); Log.i(TAG, "addPersonAndSalary: Add C"); person=new Person("C",30,false); dataSet.put(myHelper.COLUMN_NAME,person.getName()); dataSet.put(myHelper.COLUMN_AGE,person.getAge()); dataSet.put(myHelper.COLUMN_SEX,person.getSex()?1:0); db.insert(myHelper.TABLE_PERSON,null,dataSet); addSalaryInfo(db,person,23456.78f); db.setTransactionSuccessful(); db.endTransaction(); }
private void addSalaryInfo(SQLiteDatabase db,Person person, float amount){ ContentValues dataSet=new ContentValues(); Salary salary; int personID=findPersonID(db,person); salary=new Salary(personID,amount); dataSet.put(myHelper.COLUMN_USER_ID,salary.getUserid()); dataSet.put(myHelper.COLUMN_AMOUNT,salary.getAmount()); dataSet.put(myHelper.COLUMN_SALARY_DATE,salary.getSalaryDate()); db.insert(myHelper.TABLE_SALARY,null,dataSet); dataSet.clear(); }
private int findPersonID(SQLiteDatabase db,Person person){ Cursor cursor; String conditions; conditions=myHelper.COLUMN_NAME+"=? and "+myHelper.COLUMN_AGE+"=?"; cursor=db.query(true, myHelper.TABLE_PERSON, new String[]{myHelper.COLUMN_PERON_ID}, conditions, new String[]{person.getName(),String.valueOf(person.getAge())/*,String.valueOf(person.getSex())*/}, null,null,null,null); if(cursor.moveToFirst()){ if(cursor.getCount()>0){ return cursor.getInt(cursor.getColumnIndex(myHelper.COLUMN_PERON_ID)); } } return 0; }
需求(6)使用update完成。
private void updateCSalary(){ ContentValues dataSet=new ContentValues(); String conditions=myHelper.COLUMN_USER_ID+"=?"; SQLiteDatabase db=null; try{ db= dbHelper.getWritableDatabase(); }catch (Exception ex){ Log.i(TAG, "updateCSalary: exception"); } Person person=new Person("C",30,false);; Log.i(TAG, "update C Salary"); dataSet.put(myHelper.COLUMN_AMOUNT,25000.7f); db.update(myHelper.TABLE_SALARY,dataSet,conditions,new String []{String.valueOf(findPersonID(db,person))}); }
需求(7)使用delete完成。
private void deleteBInfo(){ SQLiteDatabase db=null; try{ db= dbHelper.getWritableDatabase(); }catch (Exception ex){ Log.i(TAG, "updateCSalary: exception"); } Person person=new Person("B",23,false); String conditions=myHelper.COLUMN_USER_ID+"=?"; int personID=findPersonID(db,person); db.beginTransaction(); db.delete(myHelper.TABLE_SALARY,conditions,new String[]{String.valueOf(personID)}); deletePerson(db,person); db.setTransactionSuccessful(); db.endTransaction(); }
需求(8)使用query完成。这里我们扩大一下,查询范围,搜索全部。
private void showAllInfo(){ SQLiteDatabase db=dbHelper.getReadableDatabase(); Cursor cursor; cursor=db.rawQuery("select * from "+myHelper.TABLE_PERSON, null); if(cursor.moveToFirst()){ if(cursor.getCount()>0){ do { Log.i(TAG, "showAllInfo: id ="+cursor.getInt(cursor.getColumnIndex(myHelper.COLUMN_PERON_ID))+ ", name = "+cursor.getString(cursor.getColumnIndex(myHelper.COLUMN_NAME))+ ", age = "+cursor.getInt(cursor.getColumnIndex(myHelper.COLUMN_AGE))+ (cursor.getInt(cursor.getColumnIndex(myHelper.COLUMN_SEX))==1?",female":",male")); }while (cursor.moveToNext()); } } cursor=db.rawQuery("select * from "+myHelper.TABLE_SALARY, null); if(cursor.moveToFirst()){ if(cursor.getCount()>0){ do { Log.i(TAG, "showAllInfo: id ="+cursor.getInt(cursor.getColumnIndex(myHelper.COLUMN_USER_ID))+ ", salary = "+cursor.getFloat(cursor.getColumnIndex(myHelper.COLUMN_AMOUNT))+ ", date = "+cursor.getString(cursor.getColumnIndex(myHelper.COLUMN_SALARY_DATE))); }while (cursor.moveToNext()); } } }
需求(9)在onUpgrade中完成。
public static String COLUMN_TITLE="title"; private static String INSERT_TITLE_COLUMN="alter table "+TABLE_PERSON+" add column "+COLUMN_TITLE+" text"; @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { Log.i(TAG, "onUpgrade SQLit Datebase to version "+newVersion); if(newVersion>oldVersion){ db.execSQL(INSERT_TITLE_COLUMN); } }
若要触发onUpgrade方法,需要在创建myHelper 实例时,将version值变大,例如2。
myHelper dbHelper=new myHelper(this,"Company",null,2);
我们可以看一下LOG
12-17 19:50:51.186 5180-5180/com.breakloop.sqlitehelperdemo I/com.breakloop.sqlitehelperdemo.myHelper: onUpgrade SQLit Datebase to version 2
表明onUpgrade被触发。
需求(10)使用insert完成。
Person person;person=new Person("A",22,true);addTitle(person,"Developer");person=new Person("C",30,true);addTitle(person,"Leader");showAllInfo();
private void addTitle(Person perso,String title){ ContentValues dataSet=new ContentValues(); String conditions=myHelper.COLUMN_NAME+"=? and "+myHelper.COLUMN_AGE+"=?"; SQLiteDatabase db=null; try { db= dbHelper.getWritableDatabase(); }catch (Exception ex){ Log.i(TAG, "addPersonAndSalary: execption"); } dataSet.put(myHelper.COLUMN_TITLE,title); db.update(myHelper.TABLE_PERSON,dataSet,conditions,new String[]{perso.getName(),String.valueOf(perso.getAge())}); }
日志结果如下:
12-17 20:05:57.160 6509-6509/com.breakloop.sqlitehelperdemo I/com.breakloop.sqlitehelperdemo.MainActivity: showAllInfo: id =1, name = A, age = 22, title = Developer,female12-17 20:05:57.160 6509-6509/com.breakloop.sqlitehelperdemo I/com.breakloop.sqlitehelperdemo.MainActivity: showAllInfo: id =3, name = C, age = 30, title = Leader,male
可见,数据未丢失,新数据也被添加。
至此,SQLiteOpenHelper简单使用小结完毕。
- Android持久化----SQLiteOpenHelper
- android数据持久化-SQLite数据库(SQLiteOpenHelper 例子)
- Android:SQLiteOpenHelper
- android:SQLiteOpenHelper
- android sqliteopenhelper
- Android-SQLiteOpenHelper
- Android SQLiteOpenHelper
- Android SQLiteOpenHelper
- Android:SQLiteOpenHelper,SqliteDatabase学习
- Android之SQLiteOpenHelper
- Android SQLiteOpenHelper使用示例
- Android:SQLiteOpenHelper 学习笔记
- Android如何使用SQLiteOpenHelper
- [android]SQLiteOpenHelper简介
- Android类参考---SQLiteOpenHelper
- Android SQLiteOpenHelper(手机数据库)
- android SQLiteOpenHelper使用示例
- android SQLiteOpenHelper详解
- Oracle三种循环方式与Oracle索引
- (ssl 2289)庆功会
- 写出高效率的sql和sql的优化
- PHP 移除、安装、编译
- 关于information_schema数据库
- Android持久化----SQLiteOpenHelper
- Run Maven Java Web Application in Jetty Maven Plugin
- 51Nod 1096 距离之和最小
- H5 页面适配所有 iPhone 和安卓机型的六个技巧
- LoRa与NB-IoT谁才是下一代的王者?
- 20171213
- eclipse创建java类时自动添加注释
- centos 安装 phpmyadmin
- VSCode的快捷键