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

	/**
	 * 數據庫第一次創建時調用
	 * */
	@Override
	public void onCreate(SQLiteDatabase db) {
		if (!tabIsExist("test", db)) {
			executeAssetsSQL(db, "test.sql");
			// db.execSQL(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());
			}
		}
	}

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

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

在activity中調用：

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

dbHelper.onCreate(dbHelper.getWritableDatabase());