Android编程经验-在Assets文件夹中放入.sql文件实现创建SQlite表的操作

Android编程心得-在Assets文件夹中放入.sql文件实现创建SQlite表的操作

 

  当我们在使用SQLiteOpenHelper时，经常使用db.execSQL(String sql)方法写入对应语句实现创建表的操作，这样的确可以实现业务逻辑。与此同时还有一种更灵活的方法，从assets文件夹下读取对应的.sql文件，然后创建表。

  1.首先在工程的assets文件夹下，添加对应的.sql文件
   

 

 2.配置一个Configuration类，用于保存固定路径变量

 

public class Configuration {
	public static final String DB_PATH = "schema";
	public static final String DB_NAME = "test.db";
	public static final int DB_VERSION = 1;
	public static int oldVersion = -1;
	
}

 

3.逻辑实现类，executeAssetsSQL方法用于向Assets文件夹对应的路径读取SQL语句然后执行创建操作

public class DBHelper extends SQLiteOpenHelper {

	private Context mContext;

	public DBHelper(Context context, String databaseName,
			CursorFactory factory, int version) {
		super(context, databaseName, factory, version);
		mContext = context;
	}

	/**
	 * 数据库第一次创建时调用
	 * */
	@Override
	public void onCreate(SQLiteDatabase db) {
		executeAssetsSQL(db, "schema.sql");
		System.out.println("创建表");
	}

	/**
	 * 数据库升级时调用
	 * */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		//数据库不升级
		if (newVersion <= oldVersion) {
			return;
		}
		Configuration.oldVersion = oldVersion;

		int changeCnt = newVersion - oldVersion;
		for (int i = 0; i < changeCnt; i++) {
			// 依次执行updatei_i+1文件      由1更新到2 [1-2]，2更新到3 [2-3]
			String schemaName = "update" + (oldVersion + i) + "_"
					+ (oldVersion + i + 1) + ".sql";
			executeAssetsSQL(db, schemaName);
		}
	}

	/**
	 * 读取数据库文件（.sql），并执行sql语句
	 * */
	private void executeAssetsSQL(SQLiteDatabase db, String schemaName) {
		BufferedReader in = null;
		try {
			in = new BufferedReader(new InputStreamReader(mContext.getAssets()
					.open(Configuration.DB_PATH + "/" + schemaName)));
			
			System.out.println("路径:"+Configuration.DB_PATH + "/" + schemaName);
			String line;
			String buffer = "";
			while ((line = in.readLine()) != null) {
				buffer += line;
				if (line.trim().endsWith(";")) {
					db.execSQL(buffer.replace(";", ""));
					buffer = "";
				}
			}
		} catch (IOException e) {
			Log.e("db-error", e.toString());
		} finally {
			try {
				if (in != null)
					in.close();
			} catch (IOException e) {
				Log.e("db-error", e.toString());
			}
		}
	}

}