【安卓】数据库基于脚本的"增量更新",每次更新时不需修改java代码、!

思路:

1.当然是基于SQLiteOpenHelper.onCreate(第一次安装程序时调用)、onUpdate(升级程序时调用)

2.用"脚本"(脚本制作具体方法问度娘)做数据库升级,文件名标识对应版本,java中根据“上一版本、当前版本”选择执行的脚本。

升级时,修改DB_VERSION(当前版本)即可。

DBManager.java:

package com.example.test;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBManager extends SQLiteOpenHelper {

	private final static String DBNAME = "mydata.db";

	private final static int DB_VERSION = 1;

	private static DBManager instance;

	public DBManager(Context context) {
		// TODO Auto-generated constructor stub
		super(context, DBNAME, null, DB_VERSION);
	}

	public static DBManager getThis() {
		if (instance == null)
			instance = new DBManager(MainActivity.getThis());

		return instance;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		initDb(db, 0, DB_VERSION);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		initDb(db, oldVersion, newVersion);
	}

	// 初始化db,执行脚本
	// 注:1>执行"(oldV,newV]"(全新安装时,oldV=0)间的脚本
	// 2>缺失的脚本会直接跳过
	private void initDb(SQLiteDatabase db, int oldVersion, int newVersion) {
		for (int i = oldVersion + 1; i <= newVersion; i++)
			execDBScript(db, "db/update" + i + ".sql");
	}

	// 执行脚本
	private void execDBScript(SQLiteDatabase db, String assetName) {
		try {
			InputStream is = MainActivity.getThis().getAssets().open(assetName);
			String stats = read(is);
			execSqls(db, stats);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}

	// 批执行,用";"隔开
	private void execSqls(SQLiteDatabase db, String stats) {
		// onCreate/onUpdate内部已带事务,多层嵌套无影响(最外层起作用)
		db.beginTransaction();
		String[] sqls = stats.split(";");
		for (int i = 0; i < sqls.length; i++) {
			String sqlStatement = sqls[i].trim();
			// TODO You may want to parse out comments here
			if (sqlStatement.length() > 0) {
				// 捕获错误,防止执行drop时 直接越过
				try {
					db.execSQL(sqlStatement + ";");
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}
			}
		}
		db.setTransactionSuccessful();
		db.endTransaction();
	}

	public static String read(InputStream instream) throws IOException {
		ByteArrayOutputStream bos = new ByteArrayOutputStream();
		byte[] buffer = new byte[1024];
		int len = 0;
		while ((len = instream.read(buffer)) != -1) {
			bos.write(buffer, 0, len);
		}
		String ret = new String(bos.toByteArray());
		return ret;
	}

}

db脚本: