前言：數(shù)據(jù)庫(kù)：MySQL、Oracle绍刮、Sqlite
一. 復(fù)習(xí)SQL語(yǔ)句(結(jié)構(gòu)化查詢語(yǔ)言)
1.SQL語(yǔ)句分類
- DDL數(shù)據(jù)定義語(yǔ)言
用于創(chuàng)建放航、修改、和刪除數(shù)據(jù)庫(kù)內(nèi)的數(shù)據(jù)結(jié)構(gòu)段磨，如：
1.創(chuàng)建和刪除數(shù)據(jù)庫(kù)(CREATE DATABASE || DROP DATABASE)；
2.創(chuàng)建耗绿、修改苹支、重命名、刪除表(CREATE TABLE || ALTER TABLE|| RENAME TABLE||DROP TABLE)误阻；
3.創(chuàng)建和刪除索引(CREATEINDEX || DROP INDEX)
- DML數(shù)據(jù)操作語(yǔ)言
修改數(shù)據(jù)庫(kù)中的數(shù)據(jù)沐序，包括插入(INSERT)、更新(UPDATE)和刪除(DELETE)
- DCL數(shù)據(jù)控制語(yǔ)言
用于對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)堕绩，如：1：給用戶授予訪問(wèn)權(quán)限（GRANT）;2：取消用戶訪問(wèn)權(quán)限（REMOKE）
- DQL數(shù)據(jù)查詢語(yǔ)言
從數(shù)據(jù)庫(kù)中的一個(gè)或多個(gè)表中查詢數(shù)據(jù)(SELECT)
2.SQL語(yǔ)句
- (1)庫(kù)
create database aaa; //創(chuàng)建數(shù)據(jù)庫(kù)
drop database aaa; //刪除數(shù)據(jù)庫(kù)
use aaa; //切換使用數(shù)據(jù)庫(kù)
show databases; //顯示數(shù)據(jù)庫(kù)
- (2)表
create table abc(name text,sex varchar(100),age int(10));//創(chuàng)建表結(jié)構(gòu)
drop table abc; //刪除表
desc abc; //顯示表結(jié)構(gòu)
- (3)數(shù)據(jù)
insert into abc(name,sex,age) values('abc','nan',20); //插入數(shù)據(jù)
update abc set age = 101 【where name = 'abc'】 //更改數(shù)據(jù)
delete from abc 【where name = 'abc'】 //刪除數(shù)據(jù)
select * from abc 【where age > 18】 //查詢數(shù)據(jù)
- (4）約束
主鍵： primary key
自增： auto_increment
非空： not null
唯一： unique
create table abc(
id int primary key auto_increment, 主鍵自增
name varchar not null unique, 非空唯一
sex varchar,
);
- (5）查詢
一策幼、基本查詢
select * from emp;
select empno,ename,sal from emp;
select distinct deptno from emp;
select sal*1.5 from emp;
select concat('$',sal) from emp;
select concat(sal,'RMB') from emp;
select ifnull(comm,0)+1000 from emp;
select sal as 獎(jiǎng)金 from emp;
二、條件查詢
select * from emp where deptno = 20;
select * from emp where deptno != 20;
select * from emp where sal >=20000;
select * from emp where sal >=10000 and sal <=20000;
select * from emp where sal<=10000 or sal >=40000;
select * from emp where comm is null;
select * from emp where comm is not null;
select * from emp where sal between 20000 and 40000;
select * from emp where deptno in(10,30);
三奴紧、模糊查詢 _某一個(gè)字符 %多個(gè)字符
select * from emp where ename like '張_';
select * from emp where ename like '張%';
select * from emp where ename like '_一_';
四特姐、排序
select * from emp order by sal asc;
select * from emp order by sal desc;
五、聚合函數(shù)
select max(sal) from emp;
select min(sal) from emp;
select count(ename) from emp;
select sum(sal) from emp;
select avg(sal) from emp;
六黍氮、分組
select deptno,count(ename) from emp group by deptno;
總結(jié)：
select deptno,count(ename)
from emp
where sal >= 10000
group by deptno
order by deptno asc;
二.復(fù)習(xí)SQLliteDatabase
SQLiteDatabase的創(chuàng)建和實(shí)現(xiàn)的方法
SQLiteOpenHelper的使用
onCreate的調(diào)用機(jī)制唐含，onUpgrade的調(diào)用機(jī)制浅浮；建庫(kù)、建表
增刪改查（使用sql語(yǔ)句方式）
增刪改查（使用系統(tǒng)方法）
public class FlyDb extends SQLiteOpenHelper {
/**
* 構(gòu)造方法
* <p>
* ntext：上下文
* name：庫(kù)名
* factory：null
* version：庫(kù)版本號(hào)
*/
public FlyDb(@Nullable Context context) {
super(context, "student", null, 1);
}
/**
* 初始化數(shù)據(jù)庫(kù)：創(chuàng)建表
*
* @param db
*/
@Override
public void onCreate(SQLiteDatabase db) {
//SQL語(yǔ)句捷枯，建表
String sql = "create table stu(id Integer primary key autoincrement,name varchar,sex text,age Integer)";
db.execSQL(sql);
}
/**
* 升級(jí)數(shù)據(jù)庫(kù)
*
* @param db
* @param oldVersion
* @param newVersion
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
/**
* 數(shù)據(jù)庫(kù)插入
*
* @param name
* @param sex
* @param age
*/
public void insert(String name, String sex, int age) {
//獲取數(shù)據(jù)庫(kù)對(duì)象
SQLiteDatabase db = getWritableDatabase();
//獲取ContentValues對(duì)象并賦值
ContentValues contentValues = new ContentValues();
contentValues.put("name", name);
contentValues.put("sex", sex);
contentValues.put("age", age);
//db調(diào)用插入方法
db.insert("stu", null, contentValues);
//關(guān)閉數(shù)據(jù)庫(kù)
db.close();
}
/**
* 數(shù)據(jù)庫(kù)查詢
*
* @return：返回查詢到數(shù)據(jù)
*/
public ArrayList<StudentBean> query() {
//獲取數(shù)據(jù)庫(kù)對(duì)象
SQLiteDatabase db = getWritableDatabase();
ArrayList<StudentBean> list = new ArrayList<>();
Cursor cursor = db.query("stu", null, null, null, null, null, null);
while (cursor.moveToNext()) {//判斷是否有下一個(gè)
//通過(guò)列名獲取對(duì)應(yīng)的數(shù)據(jù)
String name = cursor.getString(cursor.getColumnIndex("name"));
String sex = cursor.getString(cursor.getColumnIndex("sex"));
int id = cursor.getInt(cursor.getColumnIndex("id"));
int age = cursor.getInt(cursor.getColumnIndex("age"));
StudentBean studentBean = new StudentBean(id, name, sex, age);
list.add(studentBean);
}
//關(guān)閉數(shù)據(jù)庫(kù)
db.close();
return list;
}
/**
* 刪除
*
* @param name
*/
public void delete(String name) {
//獲取數(shù)據(jù)庫(kù)對(duì)象
SQLiteDatabase db = getWritableDatabase();
db.delete("stu", "name=?", new String[]{name});
//關(guān)閉數(shù)據(jù)庫(kù)
db.close();
}
public void updata(String newName, String oldName) {
//獲取數(shù)據(jù)庫(kù)對(duì)象
SQLiteDatabase db = getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put("name", oldName);
db.update("stu", contentValues, "name=?", new String[]{newName});
//關(guān)閉數(shù)據(jù)庫(kù)
db.close();
}
三.GreenDao的概述以及特點(diǎn)
- 概述
①基于對(duì)象關(guān)系的映射方式來(lái)操作數(shù)據(jù)庫(kù)的框架滚秩，提供一個(gè)接口通過(guò)操作對(duì)象的方式操作數(shù)據(jù)庫(kù)
② 適用于 Android 的ORM 框架，現(xiàn)在市面上主流的框架有 OrmLite淮捆、SugarORM郁油、Active Android、Realm 與 GreenDAO攀痊。
③GreenDAO是一種Android數(shù)據(jù)庫(kù)ORM(對(duì)象映射關(guān)系（Object Relation Mapping）)框架桐腌，與OrmLite、ActiveOrm苟径、LitePal等數(shù)據(jù)庫(kù)相比案站，單位時(shí)間內(nèi)可以插入、更新和查詢更多的數(shù)據(jù)棘街，而且提供了大量的靈活通用接口蟆盐。 - 特點(diǎn)
①通常我們?cè)谑褂肎reenDao的時(shí)候，我們只需定義數(shù)據(jù)模型遭殉，GreenDao框架將創(chuàng)建數(shù)據(jù)對(duì)象(實(shí)體)和DAO(數(shù)據(jù)訪問(wèn)對(duì)象)舱禽，能夠節(jié)省部分代碼。
②不向性能妥協(xié)恩沽，使用了GreenDao，大多數(shù)實(shí)體可以以每秒幾千個(gè)實(shí)體的速率進(jìn)行插入翔始，更新和加載罗心。
③GreenDao支持加密數(shù)據(jù)庫(kù)來(lái)保護(hù)敏感數(shù)據(jù)。
④微小的依賴庫(kù)城瞎，GreenDao的關(guān)鍵依賴庫(kù)大小不超過(guò)100kb.
⑤如果需要渤闷，實(shí)體是可以被激活的。而活動(dòng)實(shí)體可以透明的解析關(guān)系（我們要做的只是調(diào)用getter即可）脖镀，并且有更新飒箭、刪除和刷新方法，以便訪問(wèn)持久性功能蜒灰。
⑥GreenDao允許您將協(xié)議緩沖區(qū)(protobuf)對(duì)象直接保存到數(shù)據(jù)庫(kù)中弦蹂。如果您通過(guò)protobuf通話到您的服務(wù)器，則不需要另一個(gè)映射强窖。常規(guī)實(shí)體的所有持久性操作都可以用于protobuf對(duì)象凸椿。所以，相信這是GreenDao的獨(dú)特之處翅溺。
⑦自動(dòng)生成代碼脑漫，我們無(wú)需關(guān)注實(shí)體類以及Dao,因?yàn)镚reenDao已經(jīng)幫我們生成了髓抑。
⑧開(kāi)源 有興趣的同學(xué)可以查看源碼，深入去了解機(jī)制优幸。
四.GreenDao配置依賴
配置:
- 工程配置:添加插件 更好支持GreenDao
buildscript {
repositories {
jcenter()
mavenCentral() // 添加的代碼
}
dependencies {
classpath 'com.android.tools.build:gradle:2.3.3'
classpath 'org.greenrobot:greendao-gradle-plugin:3.2.2' // add plugin
}
}
- 項(xiàng)目配置:添加插件
apply plugin: 'com.android.application'
apply plugin: 'org.greenrobot.greendao' // apply plugin
- 項(xiàng)目配置:添加依賴
dependencies {
//greendao
implementation 'org.greenrobot:greendao:3.2.2' // add library
}
- 初始化GreenDao配置
greendao{
schemaVersion 1 //數(shù)據(jù)庫(kù)版本號(hào)
daoPackage 'com.example.lizhengjun.dao' //數(shù)據(jù)庫(kù)全路徑
targetGenDir 'src/main/java' //存放位置
}
schemaVersion--> 指定數(shù)據(jù)庫(kù)schema版本號(hào)吨拍，遷移等操作會(huì)用到;
daoPackage --> dao的包名，包名默認(rèn)是entity所在的包网杆；
targetGenDir --> 生成數(shù)據(jù)庫(kù)文件的目錄;
五.GreenDao的使用思路
- 配置文件中的設(shè)置
- 設(shè)置Green中的DaoMaster/DaoSession/實(shí)體類Dao生成路徑
- 設(shè)置實(shí)體類
@Entity
public class Student {
@Id(autoincrement = true)
private Long id;
@Property
@NotNull
private String name;
@Property
private int age;
}
文件生成：
Build - > ReBuild Project-
在Application類中完成內(nèi)容配置(或者使用工具類)
Application有自己的生命周期羹饰，OnCreate方法必須首先被調(diào)用
①本類對(duì)象的獲取
②DaoMaster、DaoSession對(duì)象的獲取
③提供方法跛璧，獲取DaoSession對(duì)象需要注意：必須在AndroidManifest.xml文件完成配置
<application
android:name=".App">
</application> 獲取實(shí)體類Dao對(duì)象
XXXDao xxxdao = App.getInstance().getDaoSession().getXXXDao();
xxxdao.增刪改查();
六.GreenDao注解
@Entity 標(biāo)識(shí)實(shí)體類严里，greenDAO會(huì)映射成sqlite的一個(gè)表，表名為實(shí)體類名的大寫(xiě)形式
@Id 標(biāo)識(shí)主鍵追城，該字段的類型為long或Long類型刹碾，autoincrement設(shè)置是否自動(dòng)增長(zhǎng)
@Property 標(biāo)識(shí)該屬性在表中對(duì)應(yīng)的列名稱, nameInDb設(shè)置名稱
@Transient 標(biāo)識(shí)該屬性將不會(huì)映射到表中，也就是沒(méi)有這列
@NotNull 設(shè)置表中當(dāng)前列的值不可為空
@Convert 指定自定義類型(@linkPropertyConverter)
@Generated 運(yùn)行所產(chǎn)生的構(gòu)造函數(shù)或者方法座柱，被此標(biāo)注的代碼可以變更或者下次運(yùn)行時(shí)清除
@Index 使用@Index作為一個(gè)屬性來(lái)創(chuàng)建一個(gè)索引迷帜；
@JoinEntity 定義表連接關(guān)系
@JoinProperty 定義名稱和引用名稱屬性關(guān)系
@Keep 注解的代碼段在GreenDao下次運(yùn)行時(shí)保持不變
@OrderBy 指定排序方式
@ToMany 定義與多個(gè)實(shí)體對(duì)象的關(guān)系
@ToOne 定義與另一個(gè)實(shí)體（一個(gè)實(shí)體對(duì)象）的關(guān)系
@Unique 向數(shù)據(jù)庫(kù)列添加了一個(gè)唯一的約束
/**
* @Entity
* @Id(autoincrement = true) 標(biāo)志主鍵
* @NotNull 標(biāo)志這個(gè)字段不能是null
* @Property(nameInDb = "User")
* @Transient 表示不存儲(chǔ)在數(shù)據(jù)庫(kù)中
* @Index(unique = true)
* @Unique 用于標(biāo)志列的值的唯一性。
*/
七.GreenDao對(duì)外提供的核心類簡(jiǎn)介
1色洞，DaoMaster
DaoMaster保存數(shù)據(jù)庫(kù)對(duì)象（SQLiteDatabase）并管理特定模式的Dao類戏锹。它具有靜態(tài)方法來(lái)創(chuàng)建表或?qū)⑺麄儎h除。其內(nèi)部類OpenHelper和DevOpenHelper是在SQLite數(shù)據(jù)庫(kù)中創(chuàng)建模式的SQLiteOpenHelper實(shí)現(xiàn)火诸。
2锦针，DaoSession
管理特定模式的所有可用Dao對(duì)象，您可以使用其中一個(gè)getter方法獲取置蜀。DaoSession還為實(shí)體提供了一些通用的持久性方法奈搜，如插入，加載盯荤，更新馋吗，刷新和刪除。最后秋秤，DaoSession對(duì)象也跟蹤一個(gè)身份范圍宏粤。
3，XXXDao層
數(shù)據(jù)訪問(wèn)對(duì)象(Dao)持續(xù)存在并查詢實(shí)體灼卢。對(duì)于每個(gè)實(shí)體绍哎，GreenDao生成一個(gè)Dao,它比DaoSession有更多的持久化方法，例如：count,loadAll和insertInTx鞋真。
4蛇摸，實(shí)體
持久對(duì)象，通常實(shí)體是使用標(biāo)準(zhǔn)Java屬性(如POJO或JavaBean)來(lái)表示數(shù)據(jù)庫(kù)的對(duì)象灿巧。
1赶袄、DevOpenHelper：創(chuàng)建SQLite數(shù)據(jù)庫(kù)的SQLiteOpenHelper的具體實(shí)現(xiàn)揽涮。
2、DaoMaster：GreenDao的頂級(jí)對(duì)象饿肺，作為數(shù)據(jù)庫(kù)對(duì)象蒋困、用于創(chuàng)建表和刪除表。
3敬辣、DaoSession：管理所有的Dao對(duì)象雪标，Dao對(duì)象中存在著增刪改查等API。
八.GreenDao使用
1.核心使用步驟
//1.創(chuàng)建數(shù)據(jù)庫(kù)
DaoMaster.DevOpenHelper devOpenHelper = new DaoMaster.DevOpenHelper(MyApp.getMyApp(), "student.db");
//2.獲取讀寫(xiě)對(duì)象
DaoMaster daoMaster = new DaoMaster(devOpenHelper.getWritableDatabase());
//3.獲取管理器類
DaoSession daoSession = daoMaster.newSession();
//4.獲取表對(duì)象
studentDao = daoSession.getStudentDao();
daoSession.clear(); 清除整個(gè)session溉跃，沒(méi)有緩存對(duì)象返回
2.完整使用步驟（Application）
public class App extends Application {
private StudentDao studentDao;//Dao操作類
private static App app;
public static App getInstance() {
return app;
}
@Override
public void onCreate() {
super.onCreate();
//本類對(duì)象
app = this;
//創(chuàng)建數(shù)據(jù)庫(kù)以及創(chuàng)建數(shù)據(jù)表
createDataBase();
}
private void createDataBase() {
//1.創(chuàng)建數(shù)據(jù)庫(kù)
DaoMaster.DevOpenHelper helper = new DaoMaster.DevOpenHelper(this, "student.db");
//2.獲取讀寫(xiě)對(duì)象
DaoMaster daoMaster = new DaoMaster(helper.getWritableDatabase());
//3.獲取管理器類
DaoSession daoSession = daoMaster.newSession();
//4.獲取表對(duì)象
studentDao = daoSession.getStudentDao();
}
public StudentDao getStudentDao() {
return studentDao;
}
}
2.完整使用步驟（工具類）
- 提供全局上下文
public class GreenDaoApplication extends Application {
private static GreenDaoApplication app;
@Override
public void onCreate() {
super.onCreate();
app = this;
}
public static GreenDaoApplication getApp() {
return app;
}
}
- 注冊(cè)application
<application
android:name=".GreenDaoApplication"
...>
</application>
- 數(shù)據(jù)庫(kù)工具類
public class DbHelper {
private final StudentBeanDao studentBeanDao;
private static volatile DbHelper instance;
public static DbHelper getInstance() {
if (instance == null) {
synchronized (DbHelper.class) {
if (instance == null) {
instance = new DbHelper();
}
}
}
return instance;
}
private DbHelper() {
//創(chuàng)建數(shù)據(jù)庫(kù)
//DaoMaster.DevOpenHelper devOpenHelper = new DaoMaster.DevOpenHelper(GreenDaoApplication.getApp(), "student.db");
GreenDaoHelper devOpenHelper = new GreenDaoHelper(GreenDaoApplication.getApp(), "student.db");
//獲取讀寫(xiě)對(duì)象
DaoMaster daoMaster = new DaoMaster(devOpenHelper.getWritableDatabase());
//獲取管理器
DaoSession daoSession = daoMaster.newSession();
//獲取表對(duì)象
studentBeanDao = daoSession.getStudentBeanDao();
}
/**
* 判斷表中是否應(yīng)有了該對(duì)象
*
* @param studentBean
* @return
*/
private boolean isHased(StudentBean studentBean) {
List<StudentBean> list = studentBeanDao.queryBuilder() .where(StudentBeanDao.Properties.Title.eq(studentBean.getTitle())).list();
if (list.size() > 0) {
return true;
} else {
return false;
}
}
/**
* 插入數(shù)據(jù)
*
* @param studentBean
* @return
*/
public long insert(StudentBean studentBean) {
if (!isHased(studentBean)) {
long l = studentBeanDao.insertOrReplace(studentBean);
return l;
}
return -1;
}
/**
* 刪除數(shù)據(jù)
*
* @param studentBean
* @return
*/
public boolean delete(StudentBean studentBean) {
if (isHased(studentBean)) {
studentBeanDao.delete(studentBean);
return true;
}
return false;
}
/**
* 查詢?nèi)? *
* @return
*/
public List<StudentBean> queryAll() {
List<StudentBean> studentBeans = studentBeanDao.loadAll();
List<StudentBean> list = studentBeanDao.queryBuilder().list();
return list;
}
/**
* 查詢固定名字
*
* @param name
* @return
*/
public List<StudentBean> queryByName(String name) {
return studentBeanDao.queryBuilder().where(StudentBeanDao.Properties.Name.eq(name)).list();
}
/**
* 查詢固定名字和年齡的
*
* @param student
* @return
*/
public List<StudentBean> queryStudent(StudentBean student) {
return studentBeanDao.queryBuilder().where(StudentBeanDao.Properties.Name.eq(student.getName()), StudentBeanDao.Properties.Age.gt(student.getAge())).list();
}
/**
* 查詢第幾頁(yè)的多少條數(shù)據(jù)
*
* @param page
* @param count
* @return
*/
public List<StudentBean> queryPage(int page, int count) {
return studentBeanDao.queryBuilder().offset(page * count).limit(count).list();
}
/**
* @param studentDao
* @return
*/
public StudentBean query(StudentBean studentDao) {
StudentBean student = studentBeanDao.queryBuilder().where(StudentBeanDao.Properties.Id.eq(studentDao.getId())).build().unique();
return student;
}
/**
* 修改
*
* @param studentBean
*/
public void updata(StudentBean studentBean) {
if (isHased(studentBean)) {
studentBeanDao.update(studentBean);
return true;
}
return false;
}
}
九.GreenDao數(shù)據(jù)庫(kù)升級(jí)
升級(jí)背景
在版本迭代時(shí)村刨，我們經(jīng)常需要對(duì)數(shù)據(jù)庫(kù)進(jìn)行升級(jí)，而GreenDAO默認(rèn)的DaoMaster.DevOpenHelper在進(jìn)行數(shù)據(jù)升級(jí)時(shí)撰茎，會(huì)把舊表刪除嵌牺，然后創(chuàng)建新表，并沒(méi)有遷移舊數(shù)據(jù)到新表中龄糊，從而造成數(shù)據(jù)丟失逆粹。
這在實(shí)際中是不可取的，因此我們需要作出調(diào)整炫惩。下面介紹數(shù)據(jù)庫(kù)升級(jí)的步驟與要點(diǎn)僻弹。升級(jí)原理
①創(chuàng)建一張新表
②舊表數(shù)據(jù)拷貝一份到新表
注意：沒(méi)有的數(shù)據(jù)給默認(rèn)數(shù)據(jù)，修改他嚷、新加的字段必須為包裝類
③刪除舊表升級(jí)步驟
- 第一步：
復(fù)制MigrationHelper到項(xiàng)目蹋绽，網(wǎng)上有不少M(fèi)igrationHelper的源碼，這里采用的是https://github.com/yuweiguocn/GreenDaoUpgradeHelper中的MigrationHelper筋蓖，它主要是通過(guò)創(chuàng)建一個(gè)臨時(shí)表卸耘，將舊表的數(shù)據(jù)遷移到新表中，大家可以去看下源碼扭勉。
public class MigrationHelper {
public static boolean DEBUG = false;
private static String TAG = "MigrationHelper";
private static final String SQLITE_MASTER = "sqlite_master";
private static final String SQLITE_TEMP_MASTER = "sqlite_temp_master";
private static WeakReference<ReCreateAllTableListener> weakListener;
public interface ReCreateAllTableListener{
void onCreateAllTables(Database db, boolean ifNotExists);
void onDropAllTables(Database db, boolean ifExists);
}
public static void migrate(SQLiteDatabase db, Class<? extends AbstractDao<?, ?>>... daoClasses) {
printLog("【The Old Database Version】" + db.getVersion());
Database database = new StandardDatabase(db);
migrate(database, daoClasses);
}
public static void migrate(SQLiteDatabase db, ReCreateAllTableListener listener, Class<? extends AbstractDao<?, ?>>... daoClasses) {
weakListener = new WeakReference<>(listener);
migrate(db, daoClasses);
}
public static void migrate(Database database, ReCreateAllTableListener listener, Class<? extends AbstractDao<?, ?>>... daoClasses) {
weakListener = new WeakReference<>(listener);
migrate(database, daoClasses);
}
public static void migrate(Database database, Class<? extends AbstractDao<?, ?>>... daoClasses) {
printLog("【Generate temp table】start");
generateTempTables(database, daoClasses);
printLog("【Generate temp table】complete");
ReCreateAllTableListener listener = null;
if (weakListener != null) {
listener = weakListener.get();
}
if (listener != null) {
listener.onDropAllTables(database, true);
printLog("【Drop all table by listener】");
listener.onCreateAllTables(database, false);
printLog("【Create all table by listener】");
} else {
dropAllTables(database, true, daoClasses);
createAllTables(database, false, daoClasses);
}
printLog("【Restore data】start");
restoreData(database, daoClasses);
printLog("【Restore data】complete");
}
private static void generateTempTables(Database db, Class<? extends AbstractDao<?, ?>>... daoClasses) {
for (int i = 0; i < daoClasses.length; i++) {
String tempTableName = null;
DaoConfig daoConfig = new DaoConfig(db, daoClasses[i]);
String tableName = daoConfig.tablename;
if (!isTableExists(db, false, tableName)) {
printLog("【New Table】" + tableName);
continue;
}
try {
tempTableName = daoConfig.tablename.concat("_TEMP");
StringBuilder dropTableStringBuilder = new StringBuilder();
dropTableStringBuilder.append("DROP TABLE IF EXISTS ").append(tempTableName).append(";");
db.execSQL(dropTableStringBuilder.toString());
StringBuilder insertTableStringBuilder = new StringBuilder();
insertTableStringBuilder.append("CREATE TEMPORARY TABLE ").append(tempTableName);
insertTableStringBuilder.append(" AS SELECT * FROM `").append(tableName).append("`;");
db.execSQL(insertTableStringBuilder.toString());
printLog("【Table】" + tableName +"\n ---Columns-->"+getColumnsStr(daoConfig));
printLog("【Generate temp table】" + tempTableName);
} catch (SQLException e) {
Log.e(TAG, "【Failed to generate temp table】" + tempTableName, e);
}
}
}
private static boolean isTableExists(Database db, boolean isTemp, String tableName) {
if (db == null || TextUtils.isEmpty(tableName)) {
return false;
}
String dbName = isTemp ? SQLITE_TEMP_MASTER : SQLITE_MASTER;
String sql = "SELECT COUNT(*) FROM `" + dbName + "` WHERE type = ? AND name = ?";
Cursor cursor=null;
int count = 0;
try {
cursor = db.rawQuery(sql, new String[]{"table", tableName});
if (cursor == null || !cursor.moveToFirst()) {
return false;
}
count = cursor.getInt(0);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (cursor != null)
cursor.close();
}
return count > 0;
}
private static String getColumnsStr(DaoConfig daoConfig) {
if (daoConfig == null) {
return "no columns";
}
StringBuilder builder = new StringBuilder();
for (int i = 0; i < daoConfig.allColumns.length; i++) {
builder.append(daoConfig.allColumns[i]);
builder.append(",");
}
if (builder.length() > 0) {
builder.deleteCharAt(builder.length() - 1);
}
return builder.toString();
}
private static void dropAllTables(Database db, boolean ifExists, @NonNull Class<? extends AbstractDao<?, ?>>... daoClasses) {
reflectMethod(db, "dropTable", ifExists, daoClasses);
printLog("【Drop all table by reflect】");
}
private static void createAllTables(Database db, boolean ifNotExists, @NonNull Class<? extends AbstractDao<?, ?>>... daoClasses) {
reflectMethod(db, "createTable", ifNotExists, daoClasses);
printLog("【Create all table by reflect】");
}
/**
* dao class already define the sql exec method, so just invoke it
*/
private static void reflectMethod(Database db, String methodName, boolean isExists, @NonNull Class<? extends AbstractDao<?, ?>>... daoClasses) {
if (daoClasses.length < 1) {
return;
}
try {
for (Class cls : daoClasses) {
Method method = cls.getDeclaredMethod(methodName, Database.class, boolean.class);
method.invoke(null, db, isExists);
}
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
private static void restoreData(Database db, Class<? extends AbstractDao<?, ?>>... daoClasses) {
for (int i = 0; i < daoClasses.length; i++) {
DaoConfig daoConfig = new DaoConfig(db, daoClasses[i]);
String tableName = daoConfig.tablename;
String tempTableName = daoConfig.tablename.concat("_TEMP");
if (!isTableExists(db, true, tempTableName)) {
continue;
}
try {
// get all columns from tempTable, take careful to use the columns list
List<TableInfo> newTableInfos = TableInfo.getTableInfo(db, tableName);
List<TableInfo> tempTableInfos = TableInfo.getTableInfo(db, tempTableName);
ArrayList<String> selectColumns = new ArrayList<>(newTableInfos.size());
ArrayList<String> intoColumns = new ArrayList<>(newTableInfos.size());
for (TableInfo tableInfo : tempTableInfos) {
if (newTableInfos.contains(tableInfo)) {
String column = '`' + tableInfo.name + '`';
intoColumns.add(column);
selectColumns.add(column);
}
}
// NOT NULL columns list
for (TableInfo tableInfo : newTableInfos) {
if (tableInfo.notnull && !tempTableInfos.contains(tableInfo)) {
String column = '`' + tableInfo.name + '`';
intoColumns.add(column);
String value;
if (tableInfo.dfltValue != null) {
value = "'" + tableInfo.dfltValue + "' AS ";
} else {
value = "'' AS ";
}
selectColumns.add(value + column);
}
}
if (intoColumns.size() != 0) {
StringBuilder insertTableStringBuilder = new StringBuilder();
insertTableStringBuilder.append("REPLACE INTO `").append(tableName).append("` (");
insertTableStringBuilder.append(TextUtils.join(",", intoColumns));
insertTableStringBuilder.append(") SELECT ");
insertTableStringBuilder.append(TextUtils.join(",", selectColumns));
insertTableStringBuilder.append(" FROM ").append(tempTableName).append(";");
db.execSQL(insertTableStringBuilder.toString());
printLog("【Restore data】 to " + tableName);
}
StringBuilder dropTableStringBuilder = new StringBuilder();
dropTableStringBuilder.append("DROP TABLE ").append(tempTableName);
db.execSQL(dropTableStringBuilder.toString());
printLog("【Drop temp table】" + tempTableName);
} catch (SQLException e) {
Log.e(TAG, "【Failed to restore data from temp table 】" + tempTableName, e);
}
}
}
private static List<String> getColumns(Database db, String tableName) {
List<String> columns = null;
Cursor cursor = null;
try {
cursor = db.rawQuery("SELECT * FROM " + tableName + " limit 0", null);
if (null != cursor && cursor.getColumnCount() > 0) {
columns = Arrays.asList(cursor.getColumnNames());
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (cursor != null)
cursor.close();
if (null == columns)
columns = new ArrayList<>();
}
return columns;
}
private static void printLog(String info){
if(DEBUG){
Log.d(TAG, info);
}
}
private static class TableInfo {
int cid;
String name;
String type;
boolean notnull;
String dfltValue;
boolean pk;
@Override
public boolean equals(Object o) {
return this == o
|| o != null
&& getClass() == o.getClass()
&& name.equals(((TableInfo) o).name);
}
@Override
public String toString() {
return "TableInfo{" +
"cid=" + cid +
", name='" + name + '\'' +
", type='" + type + '\'' +
", notnull=" + notnull +
", dfltValue='" + dfltValue + '\'' +
", pk=" + pk +
'}';
}
private static List<TableInfo> getTableInfo(Database db, String tableName) {
String sql = "PRAGMA table_info(`" + tableName + "`)";
printLog(sql);
Cursor cursor = db.rawQuery(sql, null);
if (cursor == null)
return new ArrayList<>();
TableInfo tableInfo;
List<TableInfo> tableInfos = new ArrayList<>();
while (cursor.moveToNext()) {
tableInfo = new TableInfo();
tableInfo.cid = cursor.getInt(0);
tableInfo.name = cursor.getString(1);
tableInfo.type = cursor.getString(2);
tableInfo.notnull = cursor.getInt(3) == 1;
tableInfo.dfltValue = cursor.getString(4);
tableInfo.pk = cursor.getInt(5) == 1;
tableInfos.add(tableInfo);
// printLog(tableName + "：" + tableInfo);
}
cursor.close();
return tableInfos;
}
}
}
- 第二步：
新建一個(gè)類，繼承DaoMaster.DevOpenHelper苛聘，重寫(xiě)onUpgrade(Database db, int oldVersion, int newVersion)方法涂炎，在該方法中使用MigrationHelper進(jìn)行數(shù)據(jù)庫(kù)升級(jí)以及數(shù)據(jù)遷移。
public class MyOpenHelper extends DaoMaster.OpenHelper {
public MyOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory) {
super(context, name, factory);
}
@Override
public void onUpgrade(Database db, int oldVersion, int newVersion) {
//把需要管理的數(shù)據(jù)庫(kù)表DAO作為最后一個(gè)參數(shù)傳入到方法中
MigrationHelper.migrate(db, new MigrationHelper.ReCreateAllTableListener() {
@Override
public void onCreateAllTables(Database db, boolean ifNotExists) {
DaoMaster.createAllTables(db, ifNotExists);
}
@Override
public void onDropAllTables(Database db, boolean ifExists) {
DaoMaster.dropAllTables(db, ifExists);
}
}, BeanDao.class);
}
}
然后使用MyOpenHelper替代DaoMaster.DevOpenHelper來(lái)進(jìn)行創(chuàng)建數(shù)據(jù)庫(kù)等操作
mSQLiteOpenHelper = new MyOpenHelper(MyApplication.getInstance(), DB_NAME, null);//建庫(kù)
mDaoMaster = new DaoMaster(mSQLiteOpenHelper.getWritableDatabase());
mDaoSession = mDaoMaster.newSession();
第三步：
①在表實(shí)體中设哗，調(diào)整其中的變量（表字段）唱捣，一般就是新增/刪除/修改字段。
②將原本自動(dòng)生成的構(gòu)造方法以及getter/setter方法刪除网梢，重新Build—>Make Project進(jìn)行生成震缭。
注意：
①新增的字段或修改的字段，其變量類型應(yīng)使用基礎(chǔ)數(shù)據(jù)類型的包裝類战虏，如使用Integer而不是int拣宰，避免升級(jí)過(guò)程中報(bào)錯(cuò)党涕。
①根據(jù)MigrationHelper中的代碼，升級(jí)后巡社，新增的字段和修改的字段膛堤，都會(huì)默認(rèn)被賦予null值。第四步：
修改Module下build.gradle中數(shù)據(jù)庫(kù)的版本號(hào)schemaVersion 晌该，遞增加1即可肥荔，最后運(yùn)行app
greendao {
//數(shù)據(jù)庫(kù)版本號(hào)，升級(jí)時(shí)進(jìn)行修改
schemaVersion 2
daoPackage 'com.dev.base.model.db'
targetGenDir 'src/main/java'
}
