For those who are in a hurry to learn SQLite in Android, I have written this in order to share my discoveries and hope to help those who are impatiently wanting to understand Android's database handling
In order to demonstrate database creation, we will create an An android project that will take the a name and an age data and save it in SQLite database.
Make sure that you already have Eclipse with Android plug-in installed.
Let's start by creating an Android project by clicking File->New-Project.
Select Android Project from the list (you may have to expand Android to see Android Project)
Click the Next button to proceed.
Now fill in the project details using the following values:
Project Name = Android SQLite DemoBuild Target = Android 2.1
Application Name = SQLite Demo
Package Name = com.demo.sqlite
Activity = Main
Click Finish to create the project
Let's start with construction the UI. Open main.xml located in res/layout.
Replace the content with the following:
<?xml version="1.0" encoding="utf-8"?>
<TableLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:stretchColumns="1">
<TableRow>
<TextView android:text="Name" />
<EditText android:id="@+id/name" />
</TableRow>
<TableRow>
<TextView android:text="Age" />
<EditText android:id="@+id/age" />
</TableRow>
<TableRow>
<Button android:id="@+id/saveButton"
android:layout_span="2"
android:text="Save"
/>
</TableRow>
</TableLayout>
Save main.xml then open Main.java in src/src/com.demo.sqlite.
Type in the following:
package com.demo.sqlite;
import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;
public class Main extends Activity {
private EditText name;
private EditText age;
private OnClickListener saveButtonClicked = new OnClickListener() {
public void onClick(View v) {
DBHandler dbHandler = new DBHandler(v.getContext());
Long id = dbHandler.insertRow(name.getText().toString(), Integer.parseInt(age.getText().toString()));
if (id != -1){
Toast toast = Toast.makeText(v.getContext(), "Your data has been saved, successfully", Toast.LENGTH_SHORT);
toast.show();
name.setText("");
age.setText("");
}
}
};
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
name = (EditText) findViewById(R.id.name);
age = (EditText) findViewById(R.id.age);
Button saveButton = (Button) findViewById(R.id.saveButton);
saveButton.setOnClickListener(saveButtonClicked);
}
}
Save Main.java.
Now, create a new class under com.demo.sqlite and name it DBHandler.java. Type the following in the newly created class:
package com.demo.sqlite;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
public class DBHandler extends SQLiteOpenHelper {
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "demo.db";
private static final String TABLE_NAME = "mytable";
private static final String QUERY_CREATE_TABLE =
"CREATE TABLE "
+ TABLE_NAME
+ "(id INTEGER PRIMARY KEY AUTOINCREMENT"
+ ",name TEXT"
+ ",age INTEGER);";
private static final String QUERY_INSERT_ROW =
"INSERT INTO "
+ TABLE_NAME
+ "(name, age)"
+ "values (?,?)";
SQLiteDatabase db;
SQLiteStatement stmtInsertRow;
public DBHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
db = this.getWritableDatabase();
stmtInsertRow = db.compileStatement(QUERY_INSERT_ROW);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(QUERY_CREATE_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
onCreate(db);
}
public long insertRow(String name, Integer age){
stmtInsertRow.bindString(1, name);
stmtInsertRow.bindLong(2, age);
return stmtInsertRow.executeInsert();
}
}
Let's trace the program execution to learn how Android handles SQLite databases first hand. Put breakpoints on the first line of all methods/functions of DBHandler. Now start debugging.
First Run:
Type in your name and age in the input boxes respectively. Click the save button when you are finished.
After clicking the save button the constructor for DBHandler is called when then DBHandler is initialized.
super(context, DATABASE_NAME, null, DATABASE_VERSION), Initializes DBHandler by calling SQLiteOpenHelperConstructor.
When the second line db = this.getWritableDatabase() is executed, the onCreate method is called. onCreate executes a query that will create your table.
After onCreate finishes, execution goes back to the constructor then creates the insert statement. Code continues to save the data that you have provided in the text boxes.
Notice that in this first run the onCreate method is called. It has been advised that you put your table creation query here. Do not worry thinking that every time the constructor is called the table creation query will be executed. You will know why it is best to put your table creation query here later.
Also notice onUpgrade method is not called during program execution.
2nd Run:
You may want to close the emulator and restart Eclipse. This is to demonstrate what will happen when Android phone or your Android application is restarted.
Now start debugging.
On the second run, constructor for DBHandler is again called.
When the second line db = this.getWritableDatabase() is executed, you will notice that onCreate method is NOT called. This just returns a reference to SQLiteDatabase object.
The next line creates the and insert statement and continues to run just like in the first run.
In this second run you will notice that onCreate method is never called. Android knows that the database is already created and thus skips calling the onCreate method.
Again the onUpgrade method is not executed.
3rd Run:
For the third run, edit DBHandler.java. Change the line
private static final int DATABASE_VERSION = 1;
to
private static final int DATABASE_VERSION = 2;
Save DBHandler.java. If you're the doubting kind, you may want to restart Eclipse and close the emulator.
Start the debug.
Step over the code until you reach
db = this.getWritableDatabase();
Notice that when you execute this line you will jump to onUpgrade method instead of onCreate method.
onUpgrade method deletes your table then calls onCreate to re-create your table.
After creating the table, execution goes back to DBHandler constructor. The codes continues to execute, inserting a data to the table and popping a notification the insert procedure went sucessfully.
Here, onCreate is only executed because onUpgrade called it. This is the recommended way of updating the database table.
In summary, the following should be noted:
- your database handler class should extend SQLiteOpenHelper.
- SQLiteOpenHelper's onCreate method is the recommended place to put your table creation query.
- SQLiteOpenHelper's onCreate method is called/executed only when the database is first created. Android ignores this if the database already exist.
- To delete, change, or update the database table structure, it is recommended that you put the code inside SQLiteOpenHelper's onUpgrade function. Note that you have to change the database version to force the execution of onUpgrade function. It does not matter whether you increment or decrement database version as long as the new version number is different from the previous number the application is running on.
I hope this helps. Good luck!
