Simple example to explain you the need for database transactions and use of prepared statements etc.
While inserting large number of records i.e thousands of records or so, we run into the problem of “insert speed”. The usual insert command in Android is slow, so we can use a transaction and prepared statement.
In our case, we use INSERT OR REPLACE INTO on the insert query since we want to update a row if it already exists, based on the trigger (INDEX) created.
If you’re using INSERT OR REPLACE INTO command, you have to create a trigger. This SQL trigger is executed after the table has been created (see DatabaseHandler.java below)
Another important factor in speeding up your insert is using prepared statements.
You can find the example below :
MainActivity.java – contains the AsyncTask that will be executed to insert larger number of data to db when the user clicks a button.
public class MainActivity extends Activity {
final String TAG = "MainActivity.java";
EditText editTextRecordNum;
TextView tvStatus;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
View.OnClickListener handler = new View.OnClickListener() {
public void onClick(View v) {
switch (v.getId()) {
case R.id.buttonNormalInsert:
new AsyncInsertData("normal").execute();
break;
case R.id.buttonFastInsert:
new AsyncInsertData("fast").execute();
break;
}
}
};
// EditText for entering desired number of records to be inserted
editTextRecordNum = (EditText) findViewById(R.id.editTextRecordNum);
// Button for normal and fast insert
findViewById(R.id.buttonNormalInsert).setOnClickListener(handler);
findViewById(R.id.buttonFastInsert).setOnClickListener(handler);
// status TextView
tvStatus = (TextView) findViewById(R.id.textViewStatus);
}
// we used AsyncTask so it won't block the UI thread during inserts.
class AsyncInsertData extends AsyncTask<String, String, String> {
DatabaseHandler databaseHandler;
String type;
long timeElapsed;
protected AsyncInsertData(String type){
this.type = type;
this.databaseHandler = new DatabaseHandler(MainActivity.this);
}
// @type - can be 'normal' or 'fast'
@Override
protected void onPreExecute() {
super.onPreExecute();
tvStatus.setText("Inserting " + editTextRecordNum.getText() + " records...");
}
@Override
protected String doInBackground(String... aurl) {
try {
// get number of records to be inserted
int insertCount = Integer.parseInt(editTextRecordNum.getText().toString());
// empty the table
databaseHandler.deleteRecords();
// keep track of execution time
long lStartTime = System.nanoTime();
if (type.equals("normal")) {
databaseHandler.insertNormal(insertCount);
} else {
databaseHandler.insertFast(insertCount);
}
// execution finised
long lEndTime = System.nanoTime();
// display execution time
timeElapsed = lEndTime - lStartTime;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
protected void onPostExecute(String unused) {
tvStatus.setText("Done inserting " + databaseHandler.countRecords() + " records. Time elapsed: " + timeElapsed / 1000000 + " ms.");
}
}
}
DatabaseHandler.java – handles the database operations such as table creation, emptying the database, counting database records and the inserting data using a loop.
public class DatabaseHandler extends SQLiteOpenHelper {
// for our logs
public static final String TAG = "DatabaseHandler.java";
// database version
private static final int DATABASE_VERSION = 7;
// database name
protected static final String DATABASE_NAME = "DatabaseName";
// table details
public String tableName = "locations";
public String fieldObjectId = "id";
public String fieldObjectName = "name";
public String fieldObjectDescription = "description";
// constructor
public DatabaseHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
// creating table
@Override
public void onCreate(SQLiteDatabase db) {
String sql = "";
sql += "CREATE TABLE " + tableName;
sql += " ( ";
sql += fieldObjectId + " INTEGER PRIMARY KEY AUTOINCREMENT, ";
sql += fieldObjectName + " TEXT, ";
sql += fieldObjectDescription + " TEXT ";
sql += " ) ";
db.execSQL(sql);
// create the index for our INSERT OR REPLACE INTO statement.
// this acts as the WHERE name="name input" AND description="description input"
// if that WHERE clause is true, I mean, it finds the same name and description in the database,
// it will be REPLACEd.
// ELSE, what's in the database will remain and the input will be INSERTed (new record)
String INDEX = "CREATE UNIQUE INDEX locations_index ON "
+ tableName + " (name, description)";
db.execSQL(INDEX);
}
// When upgrading the database, it will drop the current table and recreate.
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
String sql = "DROP TABLE IF EXISTS " + tableName;
db.execSQL(sql);
onCreate(db);
}
// insert data using transaction and prepared statement
public void insertFast(int insertCount) {
// you can use INSERT only
String sql = "INSERT OR REPLACE INTO " + tableName + " ( name, description ) VALUES ( ?, ? )";
SQLiteDatabase db = this.getWritableDatabase();
/*
* According to the docs http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html
* Writers should use beginTransactionNonExclusive() or beginTransactionWithListenerNonExclusive(SQLiteTransactionListener)
* to start a transaction. Non-exclusive mode allows database file to be in readable by other threads executing queries.
*/
db.beginTransactionNonExclusive();
// db.beginTransaction();
SQLiteStatement stmt = db.compileStatement(sql);
for(int x=1; x<=insertCount; x++){
stmt.bindString(1, "Name # " + x);
stmt.bindString(2, "Description # " + x);
stmt.execute();
stmt.clearBindings();
}
db.setTransactionSuccessful();
db.endTransaction();
db.close();
}
// inserts the record without using transaction and prepare statement
public void insertNormal(int insertCount){
try{
SQLiteDatabase db = this.getWritableDatabase();
for(int x=1; x<=insertCount; x++){
ContentValues values = new ContentValues();
values.put(fieldObjectName, "Name # " + x);
values.put(fieldObjectDescription, "Description # " + x);
db.insert(tableName, null, values);
}
db.close();
}catch(Exception e){
e.printStackTrace();
}
}
// deletes all records
public void deleteRecords(){
SQLiteDatabase db = this.getWritableDatabase();
db.execSQL("delete from "+ tableName);
db.close();
}
// count records
public int countRecords(){
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery("SELECT count(*) from " + tableName, null);
cursor.moveToFirst();
int recCount = cursor.getInt(0);
cursor.close();
db.close();
return recCount;
}
}
activity_main.xml – the layout so we can enter the desired number of records to be inserted, choose whether we want it to be a ‘normal’ or ‘fast’ insert, and the status of the operation.
<RelativeLayout 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:paddingBottom="@dimen/activity_vertical_margin"
android:paddingLeft="@dimen/activity_horizontal_margin"
android:paddingRight="@dimen/activity_horizontal_margin"
android:paddingTop="@dimen/activity_vertical_margin"
tools:context=".MainActivity" >
<EditText
android:id="@+id/editTextRecordNum"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_alignParentTop="true"
android:inputType="number"
android:singleLine="true"
android:ems="10" >
<requestFocus />
</EditText>
<Button
android:id="@+id/buttonNormalInsert"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignLeft="@+id/editTextRecordNum"
android:layout_below="@+id/editTextRecordNum"
android:text="Normal Insert" />
<Button
android:id="@+id/buttonFastInsert"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignBaseline="@+id/buttonNormalInsert"
android:layout_alignBottom="@+id/buttonNormalInsert"
android:layout_toRightOf="@+id/buttonNormalInsert"
android:text="Fast Insert" />
<TextView
android:id="@+id/textViewStatus"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignLeft="@+id/buttonNormalInsert"
android:layout_below="@+id/buttonNormalInsert"
android:padding="10dp"
android:text="Status" />
