开门见山的说,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 SQLException
public 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,female
12-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简单使用小结完毕。