在項目中，往往涉及到數據庫的版本升級，通常會有兩種升級方式，一種是純代碼實現，一種是腳本實現，這里使用腳本升級，這樣的升級方式更便于維護

思路

全版本升級，重點在于數據的遷移，這里使用備份原數據庫，新建數據庫，增刪字段，復制數據庫的升級思路，其版本控制在腳本中聲明，代碼在寫好之后基本是不會變動的，更利于維護

SQLite修改表語句

• 創建表 CREATE TABLE 表名 (列名 數據類型 限定符...)

CREATE TABLE Table (ID INTEGER,NAME TEXT);

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

ALTER TABLE tTable RENAME TO MyTable;

• 添加一列 ALTER TABLE 表名 ADD COLUMN 列名 數據類型 限定符

ALTER TABLE MyTable ADD COLUMN AGE INTEGER;

• 刪除表 DROP TABLE 表名

DROP TABLE MyTable;

更改表結構的方法：

當表中沒有任何數據時
刪除表

DROP TABLE MyTable;

創建表

CREATE TABLE MyTable ...

當表中有數據時
將表名改為臨時表

ALTER TABLE MyTable RENAME TO _temp_MyTable;

創建新表

CREATE TABLE MyTable (....);

導入數據

INSERT INTO MyTable SELECT .., .. ,"用空來補充原來不存在的數據" FROM _temp_MyTable;

刪除臨時表

DROP TABLE _temp_MyTable;

實現

這里沿用設計的數據庫，數據庫分庫已經在項目中包含了
修改BaseDaoFactory，使其支持數據庫分庫

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

創建數據庫腳本

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

數據庫升級創建表腳本

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

復制單個文件(可更名復制)

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

更新數據庫腳本

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

升級更新數據庫

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

數據庫升級腳本信息

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

數據庫腳本管理

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;}// 創建數據庫表sqlList<String> sqls = cd.getSqlCreates();SQLiteDatabase sqlitedb = null;try {// 邏輯層數據庫要做多用戶升級if (userList != null && !userList.isEmpty()) {// 多用戶建新表for (int i = 0; i < userList.size(); i++) {// 獲取dbsqlitedb = 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);}//創建數據庫,獲取數據庫對應的SQLiteDatabaseprivate 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;}//讀取升級xmlprivate 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;} }

模擬用戶登錄

@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> {private static final String TAG = "UserDao";@Overridepublic String createTable() {return "create table if not exists tb_user( name TEXT, password TEXT, user_id Text,status Integer);";}@Overridepublic 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);}//得到當前登錄的Userpublic User getCurrentUser() {User user = new User();user.setStatus(1);List<User> list = query(user);if (list.size() > 0) {return list.get(0);}return null;} }

模擬用戶下載數據

@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> {@Overridepublic 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 {UpdateManager updateManager;UserDao baseDao;int i = 0;@Overrideprotected 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文件夾加入升級腳本

<!-- 請保證該文檔一定是 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><updateStepversionFrom="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,pathfrom 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,passwordfrom bak_tb_user;</sql_after><sql_after>drop table if exists bak_t_user;</sql_after></updateDb></updateStep> </updateXml>

注意當前版本與升級腳本的一致

轉載于:https://www.cnblogs.com/cj5785/p/10664611.html

創作挑戰賽新人創作獎勵來咯，堅持創作打卡瓜分現金大獎

總結

以上是生活随笔為你收集整理的移动架构-数据库分库和全版本升级的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。