Execute SQL file into sqlite for Android:

Step 1 :

Copy and past your sql file into raw folder
src -> res -> raw -> yoursql

Note : Remove the extension e.g use yoursql only instead of yoursql.sql

Step 2 :

In DataBaseHelper class :

import java.io.BufferedReader;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class DataBaseHelper extends SQLiteOpenHelper{
 
 //The Android's default system path of your application database.
 private static final String DB_PATH = "/data/data/com.example.name/databases/";
 
 private static final String DB_NAME = "yoursqlite.sqlite";
 
 private SQLiteDatabase myDataBase; 
 
 private final Context myContext;
 
 /**
 * Constructor
 * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
 * @param context
 */
 public DataBaseHelper(Context context) {
 
 super(context, DB_NAME, null, 1);
 this.myContext = context;
 } 
 
 /**
 * Creates a empty database on the system and rewrites it with your own database.
 * */
 public void createDataBase() throws IOException{
 
 boolean dbExist = checkDataBase();
 
 if(dbExist){
 //do nothing - database already exist
 
 }else{
 
 //By calling this method and empty database will be created into the default system path
 //of your application so we are gonna be able to overwrite that database with our database.
 this.getReadableDatabase();
 
 try {
 
 copyDataBase();
 
 } catch (IOException e) {
 
 throw new Error("Error copying database");
 
 }
 }
 
 }
 /**
 * This reads a file from the given Resource-Id and calls every line of it as a SQL-Statement
 * 
 * @param context
 * 
 * @param resourceId
 * e.g. R.raw.food_db
 * 
 * @return Number of SQL-Statements run
 * @throws IOException
 */
 public int insertFromFile(Context context, int resourceId) throws IOException {
 // Reseting Counter
 int result = 0;
// Open the resource
 InputStream insertsStream = context.getResources().openRawResource(resourceId);
 BufferedReader insertReader = new BufferedReader(new InputStreamReader(insertsStream));
// Iterate through lines (assuming each insert has its own line and theres no other stuff)
 while (insertReader.ready()) {
 String insertStmt = insertReader.readLine();
 myDataBase.execSQL(insertStmt);
 result++;
 }
 insertReader.close();
// returning number of inserted rows
 return result;
 }
 /**
 * Check if the database already exist to avoid re-copying the file each time you open the application.
 * @return true if it exists, false if it doesn't
 */
 public boolean checkDataBase(){
 
 SQLiteDatabase checkDB = null;
 
 try{
 String myPath = DB_PATH + DB_NAME;
 checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
 
 }catch(SQLiteException e){
 
 //database does't exist yet.
 return false;
 }
 
 if(checkDB != null){
 
 checkDB.close();
 
 }
 
 return checkDB != null ? true : false;
 }
 
 /**
 * Copies your database from your local assets-folder to the just created empty database in the
 * system folder, from where it can be accessed and handled.
 * This is done by transfering bytestream.
 * */
 private void copyDataBase() throws IOException{
 
 //Open your local db as the input stream
 InputStream myInput = myContext.getAssets().open(DB_NAME);
 
 // Path to the just created empty db
 String outFileName = DB_PATH + DB_NAME;
 
 //Open the empty db as the output stream
 OutputStream myOutput = new FileOutputStream(outFileName);
 
 //transfer bytes from the inputfile to the outputfile
 byte[] buffer = new byte[1024];
 int length;
 while ((length = myInput.read(buffer))>0){
 myOutput.write(buffer, 0, length);
 }
 
 //Close the streams
 myOutput.flush();
 myOutput.close();
 myInput.close();
 
 }
 
 public SQLiteDatabase openDataBase() throws SQLException{
 
 //Open the database
 String myPath = DB_PATH + DB_NAME;
 try
 {
 myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
 }
 catch(Exception e)
 {
 //if it reaches here then the db is missing which means this a first run 
 return null;
 }
 return myDataBase;
 }
 
 @Override
 public synchronized void close() {
 
 if(myDataBase != null)
 myDataBase.close();
 
 super.close();
 
 }
 
 @Override
 public void onCreate(SQLiteDatabase db) {
 
 }
 
 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 
 }
 
 // Add your public helper methods to access and get content from the database.
 // You could return cursors by doing "return myDataBase.query(....)" so it'd be easy
 // to you to create adapters for your views.
 
}

step 3 :

Where you want to read the sql file paste the following

DataBaseHelper myDbHelper ;

try{
 int count_of_query = myDbHelper.insertFromFile(getApplicationContext(), R.raw.yoursql);
 Log.e("count_of_query", count_of_query +"");
 }catch(Exception e){
 e.printStackTrace();
 }
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s