Copy this link when reproducing:
http://www.casperlee.com/en/y/blog/197
SQLite may be the most popular database engine in the Android world. Let's find out how to use it in the Android application. As an example, I'll store a title string in the SQLite database and then retrieve it in my application. Before start, let's take it easy and enjoy a few beautiful photos of Angel Wong first.
The Android operating system comes with a built-in SQLite implementation. To use SQLite to store data in the Android system, it mainly involves the following steps:
- Design the data model (database structure)
- Create the object model (entity classes)
- Extends SQLiteOpenHelper class
- Implement Add, Get, Update & Delete
- Using it in activity
Design the data model (database structure)
As an example, I only need one table:
- Database Name: PersonalExpenseBook
- Table Name: AppParam
| Column Name | Data Type | Key | |
|---|---|---|---|
| ParamName | TEXT | Primary Key | |
| ParamValue | TEXT |
Create the object model (entity classes)
1. Create a few necessary packages:
I. Expand "app -> java -> com.casperlee.personalexpense", right click on the item "com.casperlee.personalexpense", a context menu will be popped up:
II. select "New -> Package" from the popped menu, another dialog will be popped up:
III. Type in "bll" and click "OK" to continue, Android Studio will create the package "com.casperlee.personalexpense.bll" in the application.
IV. Following the steps above to create another 3 packages:
"com.casperlee.personalexpense.dal"
"com.casperlee.personalexpense.entities"
"com.casperlee.personalexpense.global"
2. Create the entity class "AppParamEntity":
I. Expand "app -> java -> com.casperlee.personalexpense", right click on the item "entities", a context menu will be popped up:
II. select "New -> Java Class" from the popped menu, a dialog will be popped up:
III. Type in "AppParamEntity" as the class name, leave other options to default, and click "OK" to continue to create the class. Here is the code of the class:
package com.casperlee.personalexpense.entities;
public class AppParamEntity {
private String paramName;
private String paramValue;
public AppParamEntity() {
}
public String getParamName() {
return paramName;
}
public void setParamName(String paramName) {
this.paramName = paramName;
}
public String getParamValue() {
return paramValue;
}
public void setParamValue(String paramValue) {
this.paramValue = paramValue;
}
@Override
public String toString() {
return this.getParamName() + "=" + this.getParamValue();
}
}
Extends SQLiteOpenHelper class
Create a class named "DBHelper" in the package "com.casperlee.personalexpense.dal", and put the following code in:
package com.casperlee.personalexpense.dal;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import com.casperlee.personalexpense.global.GlobalVars;
public class DBHelper extends SQLiteOpenHelper {
public static final String DatabaseName = "PersonalExpenseBook";
private static DBHelper instance = null;
public static DBHelper getInstance() {
if (DBHelper.instance == null) {
DBHelper.instance = new DBHelper();
}
return DBHelper.instance;
}
public DBHelper() {
super(GlobalVars.MainContext, DatabaseName, null, 1);
}
public DBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory,
int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
this.createTablesIfNotExists(db);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
private void createTablesIfNotExists(SQLiteDatabase db) {
// create AppParam table
String createTable ="create table if not exists AppParam ("
+ "ParamName TEXT PRIMARY KEY,"
+ "ParamValue TEXT)";
db.execSQL(createTable);
}
}
Implement Add, Get, Update & Delete
1. To make it more flexible (in case we want to use another kind of database engine in the future), define an abstract class first. Create a class named "AppParamDal" in the package "com.casperlee.personalexpense.dal", and put the following code in:
package com.casperlee.personalexpense.dal;
public abstract class AppParamDal {
public abstract void AddParam(String aName, String aValue) throws Exception;
public abstract Boolean UpdateParam(String aName, String aValue);
public abstract Boolean DeleteParam(String aName);
public abstract String GetParamValue(String aName, String aDefault);
}2. Create a class named "AppParamSQLiteDal" in the package "com.casperlee.personalexpense.dal" to extends the abstract class "AppParamDal", and put the following code in:
package com.casperlee.personalexpense.dal;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
public class AppParamSQLiteDal extends AppParamDal {
private SQLiteDatabase db = null;
public AppParamSQLiteDal() {
this.db = DBHelper.getInstance().getWritableDatabase();
}
@Override
public void AddParam(String aName, String aValue) throws Exception {
Cursor cursor = this.doGetParamValue(aName);
try {
if (cursor.getCount() <= 0) {
String sql = "insert into AppParam (ParamName, ParamValue) "
+ " values (?, ?)";
db.execSQL(
sql,
new String[] {
aName,
aValue});
} else {
throw new Exception("The parameter already exists!");
}
} finally {
cursor.close();
}
}
@Override
public Boolean UpdateParam(String aName, String aValue) {
Cursor cursor = this.doGetParamValue(aName);
try {
if (cursor.getCount() <= 0) {
return false;
}
String sql = "update AppParam"
+ " set ParamValue = ?"
+ " where ParamName = ?";
db.execSQL(
sql,
new String[]{
aValue,
aName});
return true;
} finally {
cursor.close();
}
}
@Override
public Boolean DeleteParam(String aName) {
Cursor cursor = this.doGetParamValue(aName);
try {
if (cursor.getCount() <= 0) {
return false;
}
String sql = "delete from AppParam where ParamName = ?";
db.execSQL(
sql,
new String[]{
aName});
return true;
} finally {
cursor.close();
}
}
@Override
public String GetParamValue(String aName, String aDefault) {
Cursor cursor = this.doGetParamValue(aName);
try {
if (cursor.getCount() <= 0) {
return aDefault;
} else {
cursor.moveToFirst();
return cursor.getString(0);
}
} finally {
cursor.close();
}
}
private Cursor doGetParamValue(String aName) {
String sql = "select ParamValue from AppParam where ParamName = ?";
Cursor cursor = this.db.rawQuery(
sql,
new String[] {
aName
});
return cursor;
}
}
Using it in activity
1. Create a class named "AppParamBll" in the package "com.casperlee.personalexpense.bll", and put the following code in:
package com.casperlee.personalexpense.bll;
import com.casperlee.personalexpense.dal.AppParamDal;
import com.casperlee.personalexpense.dal.AppParamSQLiteDal;
public class AppParamBll {
private static final String Title = "Title";
private AppParamDal dal;
private static AppParamBll instance = null;
public static AppParamBll getInstance() {
if (instance == null) {
instance = new AppParamBll();
}
return instance;
}
public AppParamBll() {
this.dal = new AppParamSQLiteDal();
}
public String getTitle() {
// for test purpose
try {
this.dal.AddParam(Title, "Wonderful");
} catch (Exception ex) {
}
return this.dal.GetParamValue(Title, "");
}
}2. Create a class named "GlobalVars" in the package "com.casperlee.personalexpense.global", and put the following code in:
package com.casperlee.personalexpense.global;
import android.content.Context;
public class GlobalVars {
public static Context MainContext;
}3. Expand "app -> res -> layout", open the file "activity_main.xml", and give the "Hello World!" Text View an ID, so that we can retrieve it in the code and change its caption later:
<TextView
...
android:id="@+id/titleTextView"
... />4. Here is the complete source code of the class "com.casperlee.personalexpense.MainActivity":
package com.casperlee.personalexpense;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.widget.TextView;
import com.casperlee.personalexpense.bll.AppParamBll;
import com.casperlee.personalexpense.global.GlobalVars;
public class MainActivity extends AppCompatActivity {
private Layout ui;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
GlobalVars.MainContext = getApplicationContext();
this.ui = new Layout();
this.ui.getTitleTextView().setText(AppParamBll.getInstance().getTitle());
}
private class Layout {
private TextView titleTextView;
public TextView getTitleTextView() {
return this.titleTextView;
}
public Layout() {
this.initialize();
}
private void initialize() {
this.titleTextView = (TextView)findViewById(R.id.titleTextView);
}
}
}5. Test and run the application in the cell phone.
Yeah!