Android creates a database and inserts data based on a SQL file

Because in the development of the client, the server side of the write data is duplicated, do not need to access the server, and then the server is provided with a SQL file, which contains the database and data, we develop the client can not be a line of manual storage it? So I thought about it. Read the SQL file directly to create the data and insert the data well.

Create Dbhelp and inherit sqliteopenhelper

public class DBHelper extends Sqliteopenhelper {private Context mcontext;public DBHelper (context context, String database Name,cursorfactory Factory, int version) {Super (context, DatabaseName, Factory, version); mcontext = context;} /** * * */@Overridepublic void OnCreate (Sqlitedatabase db) {if (!tabisexist ("Test", db)) {Executeassetssql (db) when the database is created for the first time , "Test.sql");//Db.execsql (SQL);//system.out.println ("CREATE TABLE");}} /** * * */@Overridepublic void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {//database does not upgrade if (newvers Ion <= Oldversion) {return;} Configuration.oldversion = Oldversion;int changecnt = newversion-oldversion;for (int i = 0; i < changecnt; i++) {// The execution of the updatei_i+1 file is updated from 1 to 2 [1-2],2 update to 3 [2-3]string schemaName = "Update" + (oldversion + i) + "_" + (oldversion + i + 1) + ". S QL "; Executeassetssql (db, SchemaName);}} /** * Read the database file (. sql) and execute the SQL statement * */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 ("Path:" + 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.G Etint (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;} /** * Determine if a table exists * @param tabname * @param db * @return */public boolean tabisexist (String tabname, Sqlitedatabase db) {bo Olean 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) {R Esult = True;}}} catch (Exception e) {}return result;}}

Configuration.java are some constants

public class Configuration {public static final string db_path = "Schema";p ublic static final String db_name = "Test.db";p ublic static final int db_version = 1;public static int oldversion =-1;}

The SQL file is placed in the Assets->schema->test.sql

In fact, this process is very simple to understand, is based on the path to read the file, and then read the contents of the file, and then according to the keyword, sqllite will automatically do the corresponding operation, so the SQL file in the SQL statement must be standardized, otherwise it will not be written.

Called in the activity:

DBHelper = new DBHelper (This, "test", NULL, 1);d bhelper.oncreate (Dbhelper.getwritabledatabase ());

Android creates a database and inserts data based on a SQL file