代码地址:https://gitee.com/YuBaoZi/AndroidShuJuKuFenKuYuQuanLiangGengXin
一、原理:
通常一个APP需要通过id登陆,app会保存一些信息,切换用户,显示和保存不同的用户信息;
我们会设计一套app的文件存储架构和用户登录体系来支持不同的业务需要,要求拓展性好,原理清晰简单;本文以实现多用户登陆为例,通过数据库分库和数据库全版本升级,来展示APP数据库架构设计与更新。
需求:
- APP支持多用户切换登陆;
- 多用户的信息通过数据库存储本地,数据库支持升级,拓展性强,不同用户信息不会相互影响;
技术实现:
1、新建一个总数据库,用来存储所有用户的列表;
2、不同用户登录时,新建一个该用户的文件夹(不是数据库,这样该用户拥有的其它类型数据也可以存到这个文件夹下);文件夹下新建用户对应的数据库(类似qq的实现)
3、不同用户登录时,查找总表,有用户数据,修改状态为登录,没有该用户,插入该用户;将其它用户的状态设置为未登陆,新建一个工具类,提供所有用户列表和当前登陆的用户(操作user数据库的dao类可以作为工具类);
4、app的有多个迭代版本,数据库的更新、新建语句复杂;将所有数据库操作语句分类存到一个脚本(xml)文件中去;解析文件,根据不同版本,来拼接数据库操作语句;
5、Android中数据库不支持直接修改列的字段名称,所以需要创建、拷贝来升级数据库;
数据库版本全量升级步骤:
1、备份数据库
2、修改数据库的表名
3、在数据库中创建新的表
4、将数据从旧的表中拷贝到新表中去
5、删除备份数据库,旧表
二、多用户登陆通过数据库分库实现
1、为什么要进行数据库分库?
tb_user:
name
password
status 0未登录 1登录
同一时刻不会有两个用户登录
张三status为1其他用户为0
->大项目中,降低耦合性,实现差异性功能
案例:58货运
A货主:拉货记录
B货主:拉货记录
司机:拉货记录
都是拉货记录,有相似之处,但具体不同,不能单纯的放到一起
为了避免混乱、重复,使用数据库分库。
常见情况,同一App,不同角色,如果用两个App,效果很差;但放到同一张表中,会产生角色耦合,业务逻辑变得赋值,为了降低耦合,减少项目的复杂性,提高拓展性。使用数据库分库.
2、如何分库?
User.db 管理所有表
角色A 角色B 角色C
注意:是一个角色对应一个文件夹,文件夹下定义一个数据库,use_id与文件夹名称对应。
3、怎么实现增、删、改、查?
1)UserDao -> getCurrentUser-> user_id
MainActivity调用层
| 查询记录、登陆状态(肯定是登陆)
数据库层(和每个user保持单向链接,openOrCreateDatabase)
|
数据库操作类Dao:不需要user_id即可操作
2)获取之前,先检查表;不应该在使用时创建表,在检查时创建表;
3)新用户-》 tb_user插入用户-》checkTable检查用户表-》获取到所有用户的List-》循环遍历
数据库 -》 每次查询带时间 -》 数据库恢复时,
恢复 (总共1000条,查询对应时间的条数) 不带时间,返回所有记录条数
三、代码实现
1、建立基础数据库框架
移动架构30_面向对象式手写数据库架构设计二(修改、删除、查询、拓展)
1)定义注解,拓展表名和列名的拓展性
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface DbTable {
String value();
}
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface DbFiled {
String value();
}
2)接口
public interface IBaseDao<T> {
/**
* 插入数据
* @param entity
* @return
*/
Long insert(T entity);
/**
*
* @param entity
* @param where
* @return
*/
int update(T entity, T where);
/**
* 删除数据
* @param where
* @return
*/
int delete(T where);
/**
* 查询数据
*/
List<T> query(T where);
List<T> query(T where, String orderBy, Integer startIndex, Integer limit);
List<T> query(String sql);
}
public abstract class BaseDao<T> implements IBaseDao<T> {
private boolean isInit = false;
protected SQLiteDatabase sqLiteDatabase;
private Class<T> entityClass;
private String tableName;
private Map<String, Field> cacheMap;
public String getTableName() {
return tableName;
}
@Override
public Long insert(T entity) {
ContentValues contentValues = getContentValues(entity);
long result = sqLiteDatabase.insert(tableName, null, contentValues);
return result;
}
private ContentValues getContentValues(T entity) {
ContentValues contentValues = new ContentValues();
try {
for (Map.Entry<String,Field> me:cacheMap.entrySet()){
if(me.getValue().get(entity)==null){
continue;
}
contentValues.put(me.getKey(),me.getValue().get(entity).toString());
}
} catch (IllegalAccessException e) {
e.printStackTrace();
}
return contentValues;
}
@Override
public int update(T entity, T where) {
ContentValues contentValues = getContentValues(entity);
Condition condition = new Condition(getContentValues(where));
int update = sqLiteDatabase.update(tableName, contentValues, condition.whereClause, condition.whereArgs);
return update;
}
@Override
public int delete(T where) {
Condition condition = new Condition(getContentValues(where));
int delete = sqLiteDatabase.delete(tableName, condition.whereClause, condition.whereArgs);
return delete;
}
@Override
public List<T> query(T where) {
return query(where,null,null,null);
}
@Override
public List<T> query(T where, String orderBy, Integer startIndex, Integer limit) {
String limitString = null;
if(startIndex!=null && limit!=null){
limitString = startIndex+","+limit;
}
Condition condition = new Condition(getContentValues(where));
Cursor cursor = null;
List<T> result =new ArrayList<>();
try
{
cursor = sqLiteDatabase.query(tableName, null,condition.getWhereClause(),condition.whereArgs,null,null,orderBy,limitString);
result=getResult(cursor,where);
}catch (Exception e){
e.printStackTrace();
}finally {
if(cursor!=null){
cursor.close();
}
}
return result;
}
protected List<T> getResult(Cursor cursor, T where) {
ArrayList list = new ArrayList();
Object item;
while(cursor.moveToNext()){
try {
item = where.getClass().newInstance();
Iterator<Map.Entry<String, Field>> iterator = cacheMap.entrySet().iterator();
while(iterator.hasNext()){
Map.Entry<String, Field> entry = iterator.next();
String colmunName = entry.getKey();
Field field = entry.getValue();
int columnIndex = cursor.getColumnIndex(colmunName);
Class type = field.getType();
if(columnIndex!=-1){
if(type==String.class){
field.set(item,cursor.getString(columnIndex));
}else if(type==Double.class){
field.set(item,cursor.getDouble(columnIndex));
}else if(type== Integer.class){
int value =cursor.getInt(columnIndex);
Log.i("dongnao","value="+value);
field.set(item,cursor.getInt(columnIndex));
}else if(type == Long.class){
field.set(item,cursor.getLong(columnIndex));
}else if(type == byte[].class){
field.set(item,cursor.getBlob(columnIndex));
}else{
/**
* 不支持的类型
*/
continue;
}
}
}
list.add(item);
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
return list;
}
protected synchronized boolean init(Class<T> entity, SQLiteDatabase sqLiteDatabase) {
if (!isInit) {
this.sqLiteDatabase = sqLiteDatabase;
this.entityClass = entity;
if (entity.getAnnotation(DbTable.class) == null) {
tableName = entity.getClass().getSimpleName();
} else {
tableName = entity.getAnnotation(DbTable.class).value();
}
if (!sqLiteDatabase.isOpen()) {
return false;
}
if (!TextUtils.isEmpty(createTable())) {
sqLiteDatabase.execSQL(createTable());
}
cacheMap = new HashMap<>();
initCatchMap();
isInit = true;
}
return isInit;
}
/**
* 维护映射关系
*/
private void initCatchMap() {
String sql = "select * from " + this.tableName + " limit 1,0";
Cursor cursor = null;
try {
cursor = sqLiteDatabase.rawQuery(sql, null);
/**
* 表的列名数组
*/
String[] columnNames = cursor.getColumnNames();
/**
* 拿到Filed数组
*/
Field[] columnFields = entityClass.getFields();
for (Field filed : columnFields) {
filed.setAccessible(true);
Field columnFiled = null;
String colmunName = null;
/**
* 开始找对应关系
*/
for (String cn : columnNames) {
String filedName = null;
if(filed.getAnnotation(DbFiled.class)!=null){
filedName = filed.getAnnotation(DbFiled.class).value();
}else {
filedName = filed.getName();
}
if(cn.equals(filedName)){
columnFiled = filed;
colmunName = cn;
break;
}
}
//找到了对应关系
if(columnFiled!=null){
cacheMap.put(colmunName,columnFiled);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
cursor.close();
}
}
public abstract String createTable();
class Condition{
/**
* 查询条件
* name=? && password =?
*/
private String whereClause;
private String[] whereArgs;
public Condition(ContentValues whereClause) {
ArrayList list=new ArrayList();
StringBuilder stringBuilder=new StringBuilder();
stringBuilder.append(" 1=1 ");
Set keys = whereClause.keySet();
Iterator iterator=keys.iterator();
while(iterator.hasNext())
{
String key= (String) iterator.next();
String value= (String) whereClause.get(key);
if (value!=null)
{
/*
拼接条件查询语句
1=1 and name =? and password=?
*/
stringBuilder.append(" and "+key+" =?");
/**
* ?----》value
*/
list.add(value);
}
}
this.whereClause=stringBuilder.toString();
this.whereArgs= (String[]) list.toArray(new String[list.size()]);
}
public String[] getWhereArgs() {
return whereArgs;
}
public String getWhereClause() {
return whereClause;
}
}
}
3)业务类
@DbTable("tb_user")
public class User {
public String name;
public String password;
public String user_id;
public Integer status;
public Integer getStatus() {
return status;
}
public void setStatus(Integer status) {
this.status = status;
}
public String getUser_id() {
return user_id;
}
public void setUser_id(String user_id) {
this.user_id = user_id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
public class UserDao extends BaseDao<User> {
@Override
public String createTable() {
return "create table if not exists tb_user( name TEXT, password TEXT, user_id Text not null unique,status Integer);";
}
public void login(User entity) {
//1.找到数据库中用户列表,判断用户是否有信息存储,没有就添加用户
List<User> list = query(new User());
User where = null;
if (!isUserSave(list, entity)) {
//本地数据库用户列表不包含当前用户
//当前用户插入数据库
entity.setStatus(UserState.login.getState());
insert(entity);
Log.i(TAG, "用户" + entity.getName() + "为登录状态并插入本地数据库");
//其它用户更新数据库状态
for (User user : list) {
where = new User();
where.setUser_id(user.getUser_id());
user.setStatus(UserState.unlogin.getState());
update(user, where);
Log.i(TAG, "用户" + user.getName() + "为未登录状态");
}
} else {
//本地数据库用户列表包含当前用户,修改用户状态
for (User user : list) {
if (user.getUser_id().equals(entity.getUser_id())) {
user.setStatus(UserState.login.getState());
Log.i(TAG, "用户" + user.getName() + "为登录状态");
}else{
user.setStatus(UserState.unlogin.getState());
Log.i(TAG, "用户" + user.getName() + "为未登录状态");
}
where = new User();
where.setUser_id(user.getUser_id());
update(user, where);
}
}
}
public boolean isUserSave(List<User> users, User entity) {
for (User user : users) {
if (entity.getUser_id().equals(user.getUser_id())) {
return true;
}
}
return false;
}
@Override
public List<User> query(String sql) {
return null;
}
/**
* 得到当前登录的User
* @return
*/
public User getCurrentUser() {
User user=new User();
user.setStatus(1);
List<User> list=query(user);
if(list.size()>0)
{
return list.get(0);
}
return null;
}
}
public enum UserState {
unlogin(0), login(1);
private int state;
UserState(int state) {
this.state = state;
}
public int getState() {
return state;
}
public void setState(int state) {
this.state = state;
}
}
public enum PrivateDataBaseEnums {
/**
* 存放本地数据库的路径
*/
database("local/data/database/");
/**
* 文件存储的文件路径
*/
private String value;
PrivateDataBaseEnums(String value )
{
this.value = value;
}
public String getValue()
{
UserDao userDao=BaseDaoFactory.getInstance().getDataHelper(UserDao.class,User.class);
if(userDao!=null)
{
User currentUser=userDao.getCurrentUser();
if(currentUser!=null)
{
File file=new File(Environment.getExternalStorageDirectory(),"update");
if(!file.exists())
{
file.mkdirs();
}
return file.getAbsolutePath()+"/"+currentUser.getUser_id()+"/logic.db";
}
}
return value;
}
}
2、将升级与更新的sql语句放到xml中,并解析
1、xml
<!-- 请保证该文档一定是 UTF-8编码 -->
<updateXml>
<!-- 应当存在的数据库:当前V003版本需要的数据库(如user)和数据库中的表(tb_user)-->
<createVersion version="V003">
<createDb name="user">
<!-- 设备与软件关联信息 -->
<sql_createTable>
create table if not exists tb_user(
name TEXT,
password TEXT,
loginName TEXT,
lastLoginTime Long,
user_id Integer primary key,
status Integer
);
</sql_createTable>
</createDb>
<createDb name="logic">
<!-- 设备与软件关联信息 -->
<sql_createTable>
create table if not exists tb_photo(
time TEXT,
path TEXT,
to_user TEXT,
sendTime TEXT
);
</sql_createTable>
</createDb>
</createVersion>
<!-- 应当更新的数据库:(条件:V002到V003) -->
<updateStep
versionFrom="V002"
versionTo="V003">
<updateDb name="logic">
<sql_before>alter table tb_photo rename to bak_tb_photo;</sql_before>
<sql_after>
insert into tb_photo(time,
path)
select time,path
from bak_tb_photo;
</sql_after>
<sql_after>
drop table if exists bak_tb_photo;
</sql_after>
</updateDb>
<updateDb name="user">
<sql_before>alter table tb_user rename to bak_tb_user;</sql_before>
<sql_after>
insert into tb_user(name,
password)
select name,password
from bak_tb_user;
</sql_after>
<sql_after>
drop table if exists bak_tb_user;
</sql_after>
</updateDb>
</updateStep>
</updateXml>
2)解析xml类
/**
* Created by Ray on 2017/11/20.
* 创建数据库脚本
*/
public class CreateDb {
/**
* 数据库表名
*/
private String name;
/**
* 创建表的sql语句集合
*/
private List<String> createTables;
public CreateDb(Element ele) {
name = ele.getAttribute("name");
{
createTables = new ArrayList<>();
NodeList sqls = ele.getElementsByTagName("sql_createTable");
for (int i = 0; i < sqls.getLength(); i++) {
String sqlCreate = sqls.item(i).getTextContent();
createTables.add(sqlCreate);
}
}
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<String> getSqlCreates() {
return createTables;
}
public void setSqlCreates(List<String> sqlCreates) {
this.createTables = sqlCreates;
}
}
/**
* Created by Ray on 2017/11/20.
* 数据库升级创建表脚本
*/
public class CreateVersion {
/**
* 版本信息
*/
private String version;
/**
* 创建数据库表脚本
*/
private List<CreateDb> createDbs;
public CreateVersion(Element ele) {
version = ele.getAttribute("version");
{
createDbs = new ArrayList<>();
NodeList nodeList = ele.getElementsByTagName("createDb");
for (int i = 0; i < nodeList.getLength(); i++) {
Element ci = (Element) nodeList.item(i);
CreateDb createDb = new CreateDb(ci);
this.createDbs.add(createDb);
}
}
}
public String getVersion() {
return version;
}
public void setVersion(String version) {
this.version = version;
}
public List<CreateDb> getCreateDbs() {
return createDbs;
}
public void setCreateDbs(List<CreateDb> createDbs) {
this.createDbs = createDbs;
}
}
/**
* Created by Ray on 2017/11/20.
* 更新数据库脚本
*/
public class UpdateDb {
/**
* 数据库名称
*/
private String dbName;
private List<String> sqlBefores;
private List<String> sqlAfters;
public UpdateDb(Element ele) {
dbName = ele.getAttribute("name");
sqlBefores = new ArrayList<>();
sqlAfters = new ArrayList<>();
{
NodeList sqls = ele.getElementsByTagName("sql_before");
for (int i = 0; i < sqls.getLength(); i++) {
String sql_before = sqls.item(i).getTextContent();
sqlBefores.add(sql_before);
}
}
{
NodeList sqls = ele.getElementsByTagName("sql_after");
for(int i = 0;i<sqls.getLength();i++){
String sql_after = sqls.item(i).getTextContent();
sqlAfters.add(sql_after);
}
}
}
public String getDbName()
{
return dbName;
}
public void setDbName(String dbName)
{
this.dbName = dbName;
}
public List<String> getSqlBefores()
{
return sqlBefores;
}
public void setSqlBefores(List<String> sqlBefores)
{
this.sqlBefores = sqlBefores;
}
public List<String> getSqlAfters()
{
return sqlAfters;
}
public void setSqlAfters(List<String> sqlAfters)
{
this.sqlAfters = sqlAfters;
}
}
/**
* Created by Ray on 2017/11/20.
* 升级更新数据库
*/
public class UpdateDbXml {
/**
* 升级脚本列表
*/
private List<UpdateStep> updateSteps;
/**
* 升级版本
*/
private List<CreateVersion> createVersions;
public UpdateDbXml(Document document) {
{
// 获取升级脚本
NodeList nodeList = document.getElementsByTagName("updateStep");
updateSteps = new ArrayList<>();
for (int i = 0; i < nodeList.getLength(); i++) {
Element ele = (Element) nodeList.item(i);
UpdateStep step = new UpdateStep(ele);
updateSteps.add(step);
}
}
{
/**
* 获取各升级版本
*/
NodeList nodeList = document.getElementsByTagName("createVersion");
createVersions = new ArrayList<>();
for (int i = 0; i < nodeList.getLength(); i++) {
Element ele = (Element) nodeList.item(i);
CreateVersion createVersion = new CreateVersion(ele);
createVersions.add(createVersion);
}
}
}
public List<UpdateStep> getUpdateSteps() {
return updateSteps;
}
public void setUpdateSteps(List<UpdateStep> updateSteps) {
this.updateSteps = updateSteps;
}
public List<CreateVersion> getCreateVersions() {
return createVersions;
}
public void setCreateVersions(List<CreateVersion> createVersions) {
this.createVersions = createVersions;
}
}
public class UpdateStep {
/**
* 旧版本
*/
private String versionFrom;
/**
* 新版本
*/
private String versionTo;
/**
* 更新数据库脚本
*/
private List<UpdateDb> updateDbs;
public UpdateStep(Element ele) {
versionFrom = ele.getAttribute("versionFrom");
versionTo = ele.getAttribute("versionTo");
updateDbs = new ArrayList<>();
NodeList nodeList = ele.getElementsByTagName("updateDb");
for (int i = 0; i < nodeList.getLength(); i++) {
Element db = (Element) (nodeList.item(i));
UpdateDb updateDb = new UpdateDb(db);
updateDbs.add(updateDb);
}
}
public List<UpdateDb> getUpdateDbs() {
return updateDbs;
}
public void setUpdateDbs(List<UpdateDb> updateDbs) {
this.updateDbs = updateDbs;
}
public String getVersionFrom() {
return versionFrom;
}
public void setVersionFrom(String versionFrom) {
this.versionFrom = versionFrom;
}
public String getVersionTo() {
return versionTo;
}
public void setVersionTo(String versionTo) {
this.versionTo = versionTo;
}
}
3、 组拼解析xml的结果,执行数据库的升级
public class FileUtil {
/**
* 复制单个文件(可更名复制)
* @param oldPathFile 准备复制的文件源
* @param newPathFile 拷贝到新绝对路径带文件名(注:目录路径需带文件名)
* @return
*/
public static void copySingleFile(String oldPathFile, String newPathFile) {
try {
int byteread;
File oldfile = new File(oldPathFile);
File newFile = new File(newPathFile);
File parentFile = newFile.getParentFile();
if (!parentFile.exists()) {
parentFile.mkdirs();
}
parentFile = oldfile.getParentFile();
if (!parentFile.exists()) {
parentFile.mkdirs();
}
InputStream inputStream = new FileInputStream(oldPathFile);
FileOutputStream fs = new FileOutputStream(newFile);
byte[] buffer = new byte[1024];
while ((byteread = inputStream.read(buffer)) != -1) {
fs.write(buffer, 0, byteread);
}
inputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
public class UpdateManager {
private static final String INFO_FILE_DIV = "/";
private List<User> userList;
private File parentFile = new File(Environment.getExternalStorageDirectory(), "update");
private File bakFile = new File(parentFile, "backDb");
public UpdateManager() {
if (!parentFile.exists()) {
parentFile.mkdirs();
}
if (!bakFile.exists()) {
bakFile.mkdirs();
}
}
public void checkThisVersionTable(Context context) {
UserDao userDao = BaseDaoFactory.getInstance().getDataHelper(UserDao.class, User.class);
userList = userDao.query(new User());
UpdateDbXml xml = readDbXml(context);
String thisVersion = getVersionName(context);
CreateVersion thisCreateVersion = analyseCreateVersion(xml, thisVersion);
try {
executeCreateVersion(thisCreateVersion);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 开始升级
*/
public void startUpdateDb(Context context) {
UpdateDbXml updateDbxml = readDbXml(context);
if (getLocalVersionInfo()) {
//拿到当前版本
String thisVersion = getVersionName(context);
//拿到上一个版本
String lastVersion = lastBackupVersion;
UpdateStep updateStep = analyseUpdateStep(updateDbxml, lastVersion, thisVersion);
if (updateStep == null) {
return;
}
List<UpdateDb> updateDbs = updateStep.getUpdateDbs();
CreateVersion createVersion = analyseCreateVersion(updateDbxml, thisVersion);
try {
//备份数据库,这里为了减少复杂性,直接写了数据库的名称
//更新每个用户的数据库
for (User user : userList) {
String logicDbDir = parentFile.getAbsolutePath() + "/" + user.getUser_id() + "/logic.db";
String logicCopy = bakFile.getAbsolutePath() + "/" + user.getUser_id() + "/logic.db";
FileUtil.copySingleFile(logicDbDir, logicCopy);
}
//备份"总数据库"
String user = parentFile.getAbsolutePath() + "/user.db";
String user_bak = bakFile.getAbsolutePath() + "/user.db";
FileUtil.copySingleFile(user, user_bak);
//旧表重命名,sql_before语句
executeDb(updateDbs, -1);
//根据版本创建新表
executeCreateVersion(createVersion);
//数据迁移并删除旧表
executeDb(updateDbs, 1);
} catch (Exception e) {
}
//删除备份的数据库
if (userList != null && !userList.isEmpty()) {
for (User user : userList) {
String logicDbDir = parentFile.getAbsolutePath() + "/update" + user.getUser_id() + ".db";
File file = new File(logicDbDir);
if (file.exists()) {
file.delete();
}
}
}
File userFileBak = new File(bakFile.getAbsolutePath() + "/user_bak.db");
if (userFileBak.exists()) {
userFileBak.delete();
}
Log.i("UpdateManager", "升级成功");
}
}
/**
* 保存数据库版本,这样更新后可以获取上一次的数据库版本
* 保存成功返回true,否则返回false
*/
public boolean saveVersionInfo(String version) {
boolean ret = false;
FileWriter writer = null;
try {
writer = new FileWriter(new File(parentFile, "update.txt"), false);
writer.write(version);
writer.flush();
ret = true;
} catch (IOException e) {
e.printStackTrace();
} finally {
if (writer != null) {
try {
writer.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return ret;
}
/**
* 执行针对db升级的sql集合
*
* @param updateDbs 数据库操作脚本集合
* @param type 小于0为建表前,大于0为建表后
* @throws Exception
* @throws throws [违例类型] [违例说明]
* @see
*/
private void executeDb(List<UpdateDb> updateDbs, int type) throws Exception {
if (updateDbs == null) {
throw new Exception("updateDbs is null");
}
for (UpdateDb db : updateDbs) {
if (db == null || db.getDbName() == null) {
throw new Exception("db or dbName is null;");
}
List<String> sqls = null;
if (type < 0) {
sqls = db.getSqlBefores();
} else if (type > 0) {
sqls = db.getSqlAfters();
}
SQLiteDatabase sqlitedb = null;
try {
// 逻辑层数据库要做多用户升级
if (userList != null && !userList.isEmpty()) {
// 多用户表升级
for (int i = 0; i < userList.size(); i++) {
sqlitedb = getDb(db, userList.get(i).getUser_id());
executeSql(sqlitedb, sqls);
sqlitedb.close();
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (sqlitedb != null && sqlitedb.isOpen()) {
sqlitedb.close();
}
}
}
}
private SQLiteDatabase getDb(UpdateDb db, String user_id) {
return getDb(db.getDbName(), user_id);
}
/**
* 新表插入数据
*
* @param xml
* @param lastVersion 上个版本
* @param thisVersion 当前版本
* @return
*/
private UpdateStep analyseUpdateStep(UpdateDbXml xml, String lastVersion, String thisVersion) {
if (lastVersion == null || thisVersion == null) {
return null;
}
UpdateStep thisStep = null;
if (xml == null) {
return null;
}
List<UpdateStep> steps = xml.getUpdateSteps();
if (steps == null || steps.size() == 0) {
return null;
}
for (UpdateStep step : steps) {
if (step.getVersionFrom() == null || step.getVersionTo() == null) {
} else {
// 升级来源以逗号分隔
String[] lastVersionArray = step.getVersionFrom().split(",");
if (lastVersionArray != null && lastVersionArray.length > 0) {
for (int i = 0; i < lastVersionArray.length; i++) {
// 有一个配到update节点即升级数据
if (lastVersion.equalsIgnoreCase(lastVersionArray[i]) && step.getVersionTo().equalsIgnoreCase(thisVersion)) {
thisStep = step;
break;
}
}
}
}
}
return thisStep;
}
private String lastBackupVersion;
private boolean getLocalVersionInfo() {
boolean ret = false;
File file = new File(parentFile, "update.txt");
if (file.exists()) {
int byteRead;
byte[] tempBytes = new byte[100];
StringBuilder stringBuilder = new StringBuilder();
InputStream in = null;
try {
in = new FileInputStream(file);
while ((byteRead = in.read(tempBytes)) != -1) {
stringBuilder.append(new String(tempBytes, 0, byteRead));
}
lastBackupVersion = stringBuilder.toString();
ret = true;
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (null != in) {
try {
in.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
return ret;
}
/**
* 根据建表脚本,核实一遍应该存在的表
*
* @param createVersion
* @throws Exception
*/
private void executeCreateVersion(CreateVersion createVersion) throws Exception {
if (createVersion == null || createVersion.getCreateDbs() == null) {
throw new Exception("createVersion or createDbs is null;");
}
for (CreateDb cd : createVersion.getCreateDbs()) {
if (cd == null || cd.getName() == null) {
throw new Exception("db or dbName is null when createVersion");
}
// 创建数据库表sql
List<String> sqls = cd.getSqlCreates();
SQLiteDatabase sqlitedb = null;
try {
// 逻辑层数据库要做多用户升级
if (userList != null && !userList.isEmpty()) {
// 多用户建新表
for (int i = 0; i < userList.size(); i++) {
// 获取db
sqlitedb = getDb(cd, userList.get(i).getUser_id());
executeSql(sqlitedb, sqls);
sqlitedb.close();
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (sqlitedb != null) {
sqlitedb.close();
}
}
}
}
/**
* 执行sql语句
*/
private void executeSql(SQLiteDatabase sqlitedb, List<String> sqls) {
Log.i("databaseUpdate", ">>> databaseUpdate xml语句创建");
if (sqls == null || sqls.size() == 0) {
return;
}
sqlitedb.beginTransaction();
for (String sql : sqls) {
sql = sql.replaceAll("\r\n", " ");
sql = sql.replaceAll("\n", " ");
if (!"".equals(sql.trim())) {
try {
sqlitedb.execSQL(sql);
} catch (SQLException e) {
}
}
}
sqlitedb.setTransactionSuccessful();
sqlitedb.endTransaction();
}
private SQLiteDatabase getDb(CreateDb cd, String user_id) {
return getDb(cd.getName(), user_id);
}
/**
* 创建数据库,获取数据库对应的SQLiteDatabase
* @return 设定文件
*/
private SQLiteDatabase getDb(String dbname, String user_id) {
String dbfilepath = null;
SQLiteDatabase sqlitedb = null;
File file = new File(parentFile, user_id);
if (!file.exists()) {
file.mkdirs();
}
if (dbname.equalsIgnoreCase("logic")) {
dbfilepath = file.getAbsolutePath() + "/logic.db";
} else if (dbname.equalsIgnoreCase("user")) {
dbfilepath = Environment.getExternalStorageDirectory().getAbsolutePath() + "/update/user.db";
}
if (dbfilepath != null) {
File f = new File(dbfilepath);
f.mkdirs();
if (f.isDirectory()) {
f.delete();
}
sqlitedb = SQLiteDatabase.openOrCreateDatabase(dbfilepath, null);
}
return sqlitedb;
}
/**
* 解析出对应版本的建表脚本
*/
private CreateVersion analyseCreateVersion(UpdateDbXml xml, String version) {
CreateVersion cv = null;
if (xml == null || version == null) {
return cv;
}
List<CreateVersion> createVersions = xml.getCreateVersions();
if (createVersions != null) {
for (CreateVersion item : createVersions) {
// 如果表相同则要支持xml中逗号分隔
String[] createVersion = item.getVersion().trim().split(",");
for (int i = 0; i < createVersion.length; i++) {
if (createVersion[i].trim().equalsIgnoreCase(version)) {
cv = item;
break;
}
}
}
}
return cv;
}
/**
* 获取APK版本号
*/
private String getVersionName(Context context) {
String versionName = null;
try {
PackageInfo info = context.getPackageManager().getPackageInfo(context.getPackageName(), 0);
versionName = info.versionName;
} catch (PackageManager.NameNotFoundException e) {
e.printStackTrace();
}
return versionName;
}
/**
* 读取升级xml
*/
private UpdateDbXml readDbXml(Context context) {
InputStream is = null;
Document document = null;
try {
is = context.getAssets().open("updateXml.xml");
DocumentBuilder builder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
document = builder.parse(is);
} catch (IOException e) {
e.printStackTrace();
} catch (ParserConfigurationException e) {
e.printStackTrace();
} catch (SAXException e) {
e.printStackTrace();
} finally {
if (is != null) {
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
if (document == null) {
return null;
}
UpdateDbXml xml = new UpdateDbXml(document);
return xml;
}
}
4、 调用
@DbTable("tb_photo")
public class Photo {
public String time;
public String path;
public String getTime() {
return time;
}
public void setTime(String time) {
this.time = time;
}
public String getPath() {
return path;
}
public void setPath(String path) {
this.path = path;
}
}
public class PhotoDao extends BaseDao<Photo>{
@Override
public List<Photo> query(String sql) {
return null;
}
@Override
public String createTable() {
return "create table if not exists tb_photo(\n" +
" time TEXT,\n" +
" path TEXT,\n" +
" to_user TEXT\n" +
" )";
}
}
public class MainActivity extends AppCompatActivity {
TextView textView;
UpdateManager updateManager;
UserDao baseDao;
int i = 0;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
textView = (TextView) findViewById(R.id.content);
updateManager = new UpdateManager();
baseDao = BaseDaoFactory.getInstance().getDataHelper(UserDao.class, User.class);
}
/**
* 模拟多用户登陆
*/
public void login(View view) {
User user = new User();
user.setName("V00" + (i++));
user.setPassword("123456");
user.setName("张三" + i);
user.setUser_id("N000" + i);
baseDao.login(user);
}
/**
* 切换用户后,插入数据验证,保证数据插入的是当前登陆客户的数据库
*/
int index = 0;
public void insert(View view) {
Photo photo = new Photo();
photo.setPath("data/data/" + (index++) + "my.jpg");
java.text.SimpleDateFormat dateFormat = new java.text.SimpleDateFormat("yyyy-MM-dd-HH-mm-ss");
photo.setTime(dateFormat.format(new Date()));
PhotoDao photoDao = BaseDaoFactory.getInstance().getUserHelper(PhotoDao.class, Photo.class);
photoDao.insert(photo);
}
/**
* ====================数据库升级=========================
* write写入上一般本的数据库信息V002(模拟升级)
* update根据当前版本V003,进行数据库的升级
*/
public void write(View view) {
/**
* 写入版本
*/
updateManager.saveVersionInfo("V002");
}
/**
* 验证结果:
*/
public void update(View view) {
updateManager.checkThisVersionTable(this);
updateManager.startUpdateDb(this);
}
}
activity_main.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:id="@+id/activity_main"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
>
<TextView
android:id="@+id/content"
android:layout_width="match_parent"
android:layout_height="wrap_content" />
<Button
android:layout_width="match_parent"
android:layout_height="50dp"
android:gravity="center"
android:text="登录用户"
android:onClick="login"
/>
<Button
android:layout_width="match_parent"
android:layout_height="50dp"
android:gravity="center"
android:text="插入用户照片数据"
android:onClick="insert"
/>
<Button
android:layout_width="match_parent"
android:layout_height="50dp"
android:gravity="center"
android:text="写入版本信息"
android:onClick="write"
/>
<Button
android:layout_width="match_parent"
android:layout_height="50dp"
android:gravity="center"
android:text="升级"
android:onClick="update"
/>
</LinearLayout>
注意:这里为了演示效果,新旧版本的比对放到一个app中了(通过修改配置文件)