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<Area> selectAllCities(SQLiteDatabase db) {List<Area> areas = new ArrayList<Area>();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<Area> selectAllAreas(SQLiteDatabase db,int parent_id) {List<Area> areas = new ArrayList<Area>();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());