Android 根據sql檔案建立資料庫並插入資料

Android 根據sql檔案建立資料庫並插入資料

因為在開發用戶端的時候，伺服器端的有寫資料是重複的，不需要再去訪問伺服器的，然後伺服器端提供的是一個sql檔案，裡麵包含了資料庫和資料，我們這些開發用戶端的不可能一行一行的進行手動入庫吧？所以我就想到了直接讀取sql檔案進行建立資料並插入資料好了。

建立DBHelp並繼承SQLiteOpenHelper

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;}/** * 資料庫第一次建立時調用 * */@Overridepublic void onCreate(SQLiteDatabase db) {if (!tabIsExist("test", db)) {executeAssetsSQL(db, "test.sql");// db.execSQL(sql);//System.out.println("建立表");}}/** * 資料庫升級時調用 * */@Overridepublic 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());}}}public List selectAllCities(SQLiteDatabase db) {List areas = new ArrayList();Area area;String sql = "select * from test where area_level=?";Cursor cursor = db.rawQuery(sql, new String[] { "" + 0 });while(cursor.moveToNext()){area = new Area();area.setId(cursor.getInt(0));area.setArea_name(cursor.getString(2));areas.add(area);area = null;}cursor.close();return areas;}public List selectAllAreas(SQLiteDatabase db,int parent_id) {List areas = new ArrayList();Area area;String sql = "select * from test where parent_id=?";Cursor cursor = db.rawQuery(sql, new String[] { "" + parent_id });while(cursor.moveToNext()){area = new Area();area.setId(cursor.getInt(0));area.setArea_name(cursor.getString(2));areas.add(area);area = null;}cursor.close();return areas;}/** * 判斷是否存在某一張表 * @param tabName * @param db * @return */public boolean tabIsExist(String tabName, SQLiteDatabase db) {boolean result = false;if (tabName == null) {return false;}Cursor cursor = null;try {String sql = "select count(*) as c from sqlite_master where type ='table' and name ='" + tabName.trim() + "' ";cursor = db.rawQuery(sql, null);if (cursor.moveToNext()) {int count = cursor.getInt(0);if (count > 0) {result = true;}}} catch (Exception e) {}return result;}}

Configuration.java是一些常量

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

sql檔案是放在assets->schema->test.sql

其實這個過程非常的簡單易懂，就是根據路徑去讀取檔案，然後讀取檔案裡面的內容，再根據關鍵字，sqllite會自動進行相應的操作，所以這個sql檔案中的sql語句一定要規範，不然會寫入不了的。

在activity中調用：

dbHelper = new DBHelper(this, "test", null, 1);dbHelper.onCreate(dbHelper.getWritableDatabase());