当我们在使用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());
}
}
}
}