移动架构之数据库分库和全版本升级

发表于 | 分类于 | |

在项目中，往往涉及到数据库的版本升级，通常会有两种升级方式，一种是纯代码实现，一种是脚本实现，这里使用脚本升级，这样的升级方式更便于维护

思路

全版本升级，重点在于数据的迁移，这里使用备份原数据库，新建数据库，增删字段，复制数据库的升级思路，其版本控制在脚本中声明，代码在写好之后基本是不会变动的，更利于维护

SQLite修改表语句

  • 创建表 CREATE TABLE 表名 (列名 数据类型 限定符...)

    1
    CREATE TABLE Table (ID INTEGER,NAME TEXT);

  • 修改表 ALTER TABLE ...
    命令允许用户重命名或添加新的字段在已有表中，不能从表中删除字段。并且只能在表的末尾添加字段

    1
    ALTER TABLE tTable RENAME TO MyTable;

  • 添加一列 ALTER TABLE 表名 ADD COLUMN 列名 数据类型 限定符

    1
    ALTER TABLE MyTable ADD COLUMN AGE INTEGER;

  • 删除表 DROP TABLE 表名

    1
    DROP TABLE MyTable;

更改表结构的方法：

  1. 当表中没有任何数据时
    删除表
    1
    DROP TABLE MyTable;

创建表

1
CREATE TABLE MyTable ...

  1. 当表中有数据时
    将表名改为临时表
    1
    ALTER TABLE MyTable RENAME TO _temp_MyTable;

创建新表

1
CREATE TABLE MyTable (....);

导入数据

1
INSERT INTO MyTable SELECT .., .. ,"用空来补充原来不存在的数据" FROM _temp_MyTable;

删除临时表

1
DROP TABLE _temp_MyTable;

实现

这里沿用设计的数据库，数据库分库已经在项目中包含了
修改BaseDaoFactory，使其支持数据库分库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
public class BaseDaoFactory {
    private static final String TAG = "BaseDaoFactory";
    private String sqliteDatabasePath;
    private SQLiteDatabase sqLiteDatabase;
    private SQLiteDatabase userDatabase;
    private Map<String, BaseDao> map = Collections.synchronizedMap(new HashMap<String, BaseDao>());
    private static BaseDaoFactory instance = new BaseDaoFactory();

    public static BaseDaoFactory getInstance() {
        return instance;
    }

    private BaseDaoFactory() {
        File file = new File(Environment.getExternalStorageDirectory(), "update");
        if (!file.exists()) {
            file.mkdirs();
        }
        sqliteDatabasePath = file.getAbsolutePath() + "/user.db";
        openDatabase();
    }

    public synchronized <T extends BaseDao<M>, M> T getDataHelper(Class<T> clazz, Class<M> entityClass) {
        BaseDao baseDao = null;
        if (map.get(clazz.getSimpleName()) != null) {
            return (T) map.get(clazz.getSimpleName());
        }
        try {
            baseDao = clazz.newInstance();
            baseDao.init(entityClass, sqLiteDatabase);
            map.put(clazz.getSimpleName(), baseDao);
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }

        return (T) baseDao;
    }

    public synchronized <T extends BaseDao<M>, M> T getUserHelper(Class<T> clazz, Class<M> entityClass) {
        openUserDatabase(getPath());
        BaseDao baseDao = null;
        //反射得到对象类型
        try {
            baseDao = clazz.newInstance();
            baseDao.init(entityClass, userDatabase);
            map.put(clazz.getSimpleName(), baseDao);
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        return (T) baseDao;
    }

    private void openDatabase() {
        //打开数据库，如果不存在则创建
        sqLiteDatabase = SQLiteDatabase.openOrCreateDatabase(sqliteDatabasePath, null);
    }

    public String getPath() {
        UserDao userDao = BaseDaoFactory.getInstance().getDataHelper(UserDao.class, User.class);
        if (userDao == null) {
            return null;
        }
        User currentUser = userDao.getCurrentUser();
        if (currentUser == null) {
            return null;
        }
        File file = new File(Environment.getExternalStorageDirectory(), "update");
        if (!file.exists()) {
            file.mkdirs();
        }
        File childFile = new File(file.getAbsolutePath(), currentUser.getUser_id());
        if(!childFile.exists()){
            childFile.mkdirs();
        }
        return file.getAbsolutePath() + "/" + currentUser.getUser_id() + "/logic.db";
}

    private void openUserDatabase(String userDBPath) {
        //打开数据库，如果不存在则创建
        userDatabase = SQLiteDatabase.openOrCreateDatabase(userDBPath, null);
    }
}

创建数据库脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
public class CreateDb {

    //数据库表名
    private String name;

    //创建表的sql语句集合
    private List<String> sqlCreates;

    public CreateDb(Element ele) {
        name = ele.getAttribute("name");
        sqlCreates = new ArrayList<String>();
        NodeList sqls = ele.getElementsByTagName("sql_createTable");
        for (int i = 0; i < sqls.getLength(); i++) {
            String sqlCreate = sqls.item(i).getTextContent();
            this.sqlCreates.add(sqlCreate);
        }
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<String> getSqlCreates() {
        return sqlCreates;
    }

    public void setSqlCreates(List<String> sqlCreates) {
        this.sqlCreates = sqlCreates;
    }
}

数据库升级创建表脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
public class CreateVersion {
    //版本信息
    private String version;

    //创建数据库表脚本
    private List<CreateDb> createDbs;

    public CreateVersion(Element ele) {
        version = ele.getAttribute("version");
        createDbs = new ArrayList<CreateDb>();
        NodeList cs = ele.getElementsByTagName("createDb");
        for (int i = 0; i < cs.getLength(); i++) {
            Element ci = (Element) (cs.item(i));
            CreateDb cd = new CreateDb(ci);
            this.createDbs.add(cd);
        }
    }

    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;
    }
}

复制单个文件(可更名复制)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
public class FileUtil {
    public static void CopySingleFile(String oldPathFile, String newPathFile) {
        try {
            int bytesum = 0;
            int byteread = 0;
            File oldfile = new File(oldPathFile);
            File newFile = new File(newPathFile);
            File parentFile = newFile.getParentFile();
            if (!parentFile.exists()) {
                parentFile.mkdirs();
            }
            //文件存在时
            if (oldfile.exists()) {
                InputStream inStream = new FileInputStream(oldPathFile);
                FileOutputStream fs = new FileOutputStream(newPathFile);
                byte[] buffer = new byte[1024];
                while ((byteread = inStream.read(buffer)) != -1) {
                    bytesum += byteread; //字节数 文件大小
                    fs.write(buffer, 0, byteread);
                }
                inStream.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

更新数据库脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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<String>();
        sqlAfters = new ArrayList<String>();
        NodeList sqlsBefore = ele.getElementsByTagName("sql_before");
        for (int i = 0; i < sqlsBefore.getLength(); i++) {
            String sql_before = sqlsBefore.item(i).getTextContent();
            this.sqlBefores.add(sql_before);
        }
        NodeList sqlsAfter = ele.getElementsByTagName("sql_after");
        for (int i = 0; i < sqlsAfter.getLength(); i++) {
            String sql_after = sqlsAfter.item(i).getTextContent();
            this.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;
    }
}

升级更新数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
public class UpdateDbXml {
    //升级脚本列表
    private List<UpdateStep> updateSteps;

    //升级版本
    private List<CreateVersion> createVersions;

    public UpdateDbXml(Document document) {
        // 获取升级脚本
        NodeList updateSteps = document.getElementsByTagName("updateStep");
        this.updateSteps = new ArrayList<UpdateStep>();
        for (int i = 0; i < updateSteps.getLength(); i++) {
            Element ele = (Element) (updateSteps.item(i));
            UpdateStep step = new UpdateStep(ele);
            this.updateSteps.add(step);
        }
        //获取各升级版本
        NodeList createVersions = document.getElementsByTagName("createVersion");
        this.createVersions = new ArrayList<CreateVersion>();
        for (int i = 0; i < createVersions.getLength(); i++) {
            Element ele = (Element) (createVersions.item(i));
            CreateVersion cv = new CreateVersion(ele);
            this.createVersions.add(cv);
        }
    }

    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;
    }
}

数据库升级脚本信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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<UpdateDb>();
        NodeList dbs = ele.getElementsByTagName("updateDb");
        for (int i = 0; i < dbs.getLength(); i++) {
            Element db = (Element) (dbs.item(i));
            UpdateDb updateDb = new UpdateDb(db);
            this.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;
    }
}

数据库脚本管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
public class UpdateManager {
    private static final String TAG = "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, true);
        } catch (Exception e) {
        }

    }

    //开始升级
    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() + "/update" + "/" + 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, false);
                Log.d(TAG, "第三步检查新表完成!");
                // 第四步:从备份表中恢复数据，恢复后删除备份表
                executeDb(updateDbs, 1);
            } catch (Exception e) {
                e.printStackTrace();
            }
            // 第五步:升级成功，删除备份数据库
            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.d(TAG, "升级成功");
        }
    }

    //根据建表脚本,核实一遍应该存在的表
    private void executeCreateVersion(CreateVersion createVersion, boolean isLogic) 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;");
            }
            if (!"logic".equals(cd.getName())) {
                continue;
            }
            // 创建数据库表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();
                }
            }
        }
    }


    //执行针对db升级的sql集合 type：小于0为建表前，大于0为建表后
    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 (null != sqlitedb) {
                    sqlitedb.close();
                }
            }
        }
    }

    //执行sql语句
    private void executeSql(SQLiteDatabase sqlitedb, List<String> sqls) throws Exception {
        // 检查参数
        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 {
                    // Logger.i(TAG, "执行sql：" + sql, false);
                    sqlitedb.execSQL(sql);
                } catch (SQLException e) {
                }
            }
        }
        sqlitedb.setTransactionSuccessful();
        sqlitedb.endTransaction();
    }


    //新表插入数据
    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;
    }

    //根据xml对象获取对应要修改的db文件
    private SQLiteDatabase getDb(UpdateDb db, String userId) {
        return getDb(db.getDbName(), userId);
    }

    private SQLiteDatabase getDb(CreateDb db, String userId) {
        return getDb(db.getName(), userId);
    }

    //创建数据库,获取数据库对应的SQLiteDatabase
    private SQLiteDatabase getDb(String dbname, String userId) {
        String dbfilepath = null;
        SQLiteDatabase sqlitedb = null;
        File file = new File(parentFile, userId);
        if (!file.exists()) {
            file.mkdirs();
        }
        if (dbname.equalsIgnoreCase("logic")) {
            dbfilepath = file.getAbsolutePath() + "/logic.db";// logic对应的数据库路径
        } else if (dbname.equalsIgnoreCase("user")) {
            dbfilepath = Environment.getExternalStorageDirectory().getAbsolutePath() + "/user.db";// service对应的数据库
        }
        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;
    }

    //读取升级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 (Exception 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;
    }

    //获取APK版本号
    public String getVersionName(Context context) {
        String versionName = null;
        try {
            PackageInfo info = context.getPackageManager().getPackageInfo(context.getPackageName(), 0);
            versionName = info.versionName;
        } catch (PackageManager.NameNotFoundException e) {
        }
        return versionName;
    }

    //保存下载APK版本信息
    public boolean saveVersionInfo(Context context, String newVersion) {
        boolean ret = false;
        FileWriter writer = null;
        try {
            writer = new FileWriter(new File(parentFile, "update.txt"), false);
            writer.write("V003" + INFO_FILE_DIV + "V002");
            writer.flush();
            ret = true;
        } catch (IOException e) {
        } finally {
            if (writer != null) {
                try {
                    writer.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return ret;
    }

    //获取本地版本相关信息
    private String existVersion;
    private String lastBackupVersion;

    private boolean getLocalVersionInfo() {
        boolean ret = false;
        File file = new File(parentFile, "update.txt");
        if (file.exists()) {
            int byteread = 0;
            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));
                }
                String[] infos = stringBuilder.toString().split(INFO_FILE_DIV);
                if (infos.length == 2) {
                    existVersion = infos[0];
                    lastBackupVersion = infos[1];
                    ret = true;
                }
            } catch (Exception e) {

            } finally {
                if (null != in) {
                    try {
                        in.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                    in = null;
                }
            }
        }
        return ret;
    }
}

模拟用户登录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
@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;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
public class UserDao extends BaseDao<User> {
    private static final String TAG = "UserDao";
    @Override
    public String createTable() {
        return "create table if not exists tb_user( name TEXT, password TEXT, user_id Text,status Integer);";
    }

    @Override
    public long insert(User entity) {
        List<User> list = query(new User());
        User where = null;
        for (User user : list) {
            where = new User();
            where.setUser_id(user.getUser_id());
            user.setStatus(0);
            update(user, where);
        }
        Log.d(TAG, "用户" + entity.getName() + "登录");
        entity.setStatus(1);
        return super.insert(entity);
    }

    //得到当前登录的User
    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;
    }
}

模拟用户下载数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
@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;
    }
}

1
2
3
4
5
6
7
8
9
10
11
public class PhotoDao extends BaseDao<Photo> {

    @Override
    public String createTable() {
        return "create table if not exists tb_photo(\n" +
                "                time TEXT,\n" +
                "                path TEXT,\n" +
                "                to_user TEXT\n" +
                "                )";
    }
}

测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
public class MainActivity extends AppCompatActivity {

    UpdateManager updateManager;
    UserDao baseDao;
    int i = 0;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        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("jack" + i);
        user.setUser_id("N000" + i);
        baseDao.insert(user);
        updateManager.checkThisVersionTable(this);
    }

    public void insert(View view) {
        Photo photo = new Photo();
        photo.setPath("data/data/my.jpg");
        SimpleDateFormat dateFormat = new 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);
    }

    public void write(View view) {
        //写入版本
        updateManager.saveVersionInfo(this, "V002");
    }

    public void update(View view) {
        updateManager.checkThisVersionTable(this);
        updateManager.startUpdateDb(this);
    }
}

在Assets文件夹加入升级脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
<!-- 请保证该文档一定是 UTF-8编码 -->
<updateXml>
    <createVersion version="V003">
        <createDb name="user">
            <!-- 设备与软件关联信息 -->
            <sql_createTable>
                create table if not exists tb_user(
                name TEXT,
                password TEXT,
                loginName TEXT,
                lastLoginTime,
                user_id Integer primary key
                );
            </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>
    <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_t_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_t_user;
            </sql_after>
        </updateDb>
    </updateStep>
</updateXml>

注意当前版本与升级脚本的一致

Donate comment here