移动架构35_数据库架构：数据库升级及分库实现多用户登陆

代码地址: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类

移动架构34_Android中解析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中了（通过修改配置文件）