Sunday 27 September 2015

HB Blog 95: How To Compile SQL Statement Into Reusable Pre-compiled Statement Object???

Database transactions are slow and in situations where there is need for thousands of records have to be inserted, inserting each record takes a lot of time and valuable resources.
Basically, insert() is convenience method for inserting a row into the database. But, for saving time and valuable resources we can use compile statement().  It compiles an SQL statement into a reusable pre-compiled statement object.

SQLite provides methods in SQLiteDatabase class can be used to make all the insert calls in the same batch in a single transaction. Start a transaction by calling the beginTransaction() method. Perform the database operations and then call the setTransactionSuccessful() to commit the transaction. Once the transaction is complete call the endTransaction() function.
    beginTransaction();
    compileStatement(String sql)
    setTransactionSuccessful();
    endTransaction();
Here is the standard idiom for transactions:
db.compileStatement (String sql)
db.beginTransaction();
try {
...
db.setTransactionSuccessful();
} finally {
db.endTransaction();
}
Refer the below link for complete sample code:-
Download Sample Code
Download Apk File
Have a look on few code snippets,

MainActivity.java 
  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
package com.databasecompilestatement_as;

import android.app.Activity;
import android.content.Context;
import android.os.Bundle;
import android.os.Environment;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import com.dbUtils.DBManager;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Arrays;

public class MainActivity extends Activity {
    private ArrayAdapter<String> arrayAdapter;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        Button insert_raw_query = (Button) findViewById(R.id.insert_raw_query);
        Button insert_compile_statement = (Button) findViewById(R.id.insert_compile_statement);
        Button save_to_sdcard = (Button) findViewById(R.id.save_to_sdcard);
        ListView listView=(ListView)findViewById(R.id.listView);
        ArrayList<String> arrayList = new ArrayList<String>();
        arrayAdapter = new ArrayAdapter<String>(this,android.R.layout.simple_list_item_1, arrayList);
        listView.setAdapter(arrayAdapter);

        final DBManager dbManager = new DBManager(MainActivity.this);
        insert_raw_query.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                long startTime = System.currentTimeMillis();
                dbManager.insertRawQuery(MainActivity.this);
                long diff = System.currentTimeMillis() - startTime;
                String result="insertRawQuery time diff: " + diff;
                arrayAdapter.add(String.valueOf(result));
            }
        });

        insert_compile_statement.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                long startTime = System.currentTimeMillis();
                dbManager.insertCompileStatement(MainActivity.this);
                long diff = System.currentTimeMillis() - startTime;
                String result="insertCompileStatement time diff: " + diff;
                arrayAdapter.add(result);
            }
        });


        save_to_sdcard.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                saveDatabaseToSdcard(MainActivity.this);
            }
        });
    }

    /**
     * save Database To Sdcard
     *
     * @param context
     */
    public static void saveDatabaseToSdcard(Context context) {
        try {
            InputStream myInput = new FileInputStream("/data/data/com.databasecompilestatement_as/databases/" + "hbdemodb.db");

            File file = new File(Environment.getExternalStorageDirectory().getPath() + "/" + "hbdemodb.db");
            if (!file.exists()) {
                try {
                    file.createNewFile();
                } catch (IOException e) {
                }
            }

            OutputStream myOutput = new FileOutputStream(Environment.getExternalStorageDirectory().getPath() + "/" + "hbdemodb.db");

            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();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

ApplicationClass.java 
 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
package com.dbUtils;

import android.app.Application;
import android.database.sqlite.SQLiteDatabase;

/**
 * This is application class is used to declare global variable at application level.
 * Created by harshalb
 */
public class ApplicationClass extends Application {
    private SQLiteDatabase mSqLiteDatabase;

    @Override
    public void onCreate() {
        DBHelper dbHelper = new DBHelper(getApplicationContext());
        mSqLiteDatabase = dbHelper.getWritableDatabase();
        super.onCreate();
    }


    /**
     * This method is used to get database object.
     *
     * @return SQLiteDatabase
     */
    public SQLiteDatabase getReadableDatabase() {
        if (mSqLiteDatabase == null || mSqLiteDatabase.isOpen() == false) {
            DBHelper dbHelper = new DBHelper(getApplicationContext());
            mSqLiteDatabase = dbHelper.getReadableDatabase();
        }

        return mSqLiteDatabase;
    }

    /**
     * This method is used to get database object.
     *
     * @return SQLiteDatabase
     */
    public SQLiteDatabase getWritableDatabase() {
        if (mSqLiteDatabase == null || mSqLiteDatabase.isOpen() == false) {
            DBHelper dbHelper = new DBHelper(getApplicationContext());
            mSqLiteDatabase = dbHelper.getWritableDatabase();
        }
        return mSqLiteDatabase;
    }

    /**
     * This method is used to close database object.
     */
    public void closeDB() {
        if (mSqLiteDatabase != null)
            mSqLiteDatabase.close();
    }
}

DBHelper.java 
 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
package com.dbUtils;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * The DB helper class used to create database.
 * Created by harshalb
 */
public class DBHelper extends SQLiteOpenHelper {
    public static String DATABASE_NAME = "hbdemodb.db";
    static int DATABASE_VERSION = 1;

    public DBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public DBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(DatabasePojo.CREATE_TABLE_QUERY);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    @Override
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        super.onDowngrade(db, oldVersion, newVersion);
    }

    public void restoreDatabase() {
        SQLiteDatabase sqLiteDatabase = getWritableDatabase();
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + DatabasePojo.TABLENAME);
        sqLiteDatabase.execSQL(DatabasePojo.CREATE_TABLE_QUERY);
        sqLiteDatabase.close();
    }
}

DatabasePojo.java 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
package com.dbUtils;

/**
 * This is database pojo class.
 * Created by harshalb
 */
public class DatabasePojo {

    public static String TABLENAME = "hbdemoTable";
    public static String CREATE_TABLE_QUERY = "CREATE TABLE IF NOT EXISTS " + TABLENAME + " ("
//            + "_id INTEGER PRIMARY KEY, " //Don't remove this column.
            + "name VARCHAR, "
            + "surname VARCHAR, "
            + "result VARCHAR "
            + ")";
    public int name;
    public int surname;
    public int result;
}

DBManager.java 
 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
package com.dbUtils;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;


/**
 * This is a manager class used to manage database related information.
 * Created by harshalb
 */
public class DBManager {
    Context mContext;

    public DBManager(Context context) {
        this.mContext = context;
    }

    public void insertRawQuery(Context context) {
        ApplicationClass applicationClass = (ApplicationClass) context.getApplicationContext();
        SQLiteDatabase sqLiteDatabase = applicationClass.getWritableDatabase();

        for (int i = 0; i < 100; i++) {
            ContentValues values = new ContentValues();
            values.put("name", "rawname" + i);
            values.put("surname", "rawsurname" + i);
            values.put("result", i * i);
            sqLiteDatabase.insert(DatabasePojo.TABLENAME, null, values);
        }
    }

    public void insertCompileStatement(Context context) {
        ApplicationClass applicationClass = (ApplicationClass) context.getApplicationContext();
        SQLiteDatabase sqLiteDatabase = applicationClass.getWritableDatabase();
        String sql = "INSERT INTO " + DatabasePojo.TABLENAME + " VALUES (?,?,?);";
        SQLiteStatement statement = sqLiteDatabase.compileStatement(sql);
        sqLiteDatabase.beginTransaction();
        for (int i = 0; i < 100; i++) {
            statement.clearBindings();
            statement.bindString(1, "name" + i);
            statement.bindString(2, "surname" + i);
            statement.bindLong(3, i * i);
            statement.execute();
        }
        sqLiteDatabase.setTransactionSuccessful();
        sqLiteDatabase.endTransaction();
    }

}

activity_main.xml 
 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
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical">

    <Button
        android:id="@+id/insert_raw_query"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Insert Raw Query" />

    <Button
        android:id="@+id/insert_compile_statement"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Insert Compile Statement" />

    <Button
        android:id="@+id/save_to_sdcard"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Save To Sdcard" />

    <ListView
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/listView"
        android:layout_gravity="center_horizontal" />


</LinearLayout>

AndroidManifest.xml
 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
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.databasecompilestatement_as">

    <uses-sdk
        android:minSdkVersion="14"
        android:targetSdkVersion="21"></uses-sdk>

    <uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE" />

    <uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" />

    <application
        android:name="com.dbUtils.ApplicationClass"
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name"
        android:theme="@style/AppTheme">
        <activity
            android:name=".MainActivity"
            android:label="@string/app_name">
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

</manifest>
Tags: , , , , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)