Android 根据sql文件创建数据库并插入数据

因为在开发客户端的时候，服务器端的有写数据是重复的，不需要再去访问服务器的，然后服务器端提供的是一个sql文件，里面包含了数据库和数据，我们这些开发客户端的不可能一行一行的进行手动入库吧？所以我就想到了直接读取sql文件进行创建数据并插入数据好了。

创建DBHelp并继承SQLiteOpenHelper

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137

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是一些常量

?

1 2 3 4 5 6 7

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中调用：

?

1 2 3

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