Programming     Travel Logs     Life Is Good     Surfing Online     About Me
Judgement requires experience, but can be built faster by learning foundational skills.
-Naval Ravikant
2018-07-17 17:42:07

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.

/Images/20170930/01.jpg

/Images/20170930/02.jpg

/Images/20170930/03.jpg

/Images/20170930/04.jpg

/Images/20170930/05.jpg

/Images/20170930/06.jpg

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:

/Images/20170930/101.jpg

    II. select "New -> Package" from the popped menu, another dialog will be popped up:

/Images/20170930/102.jpg

    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:

/Images/20170930/103.jpg

    II. select "New -> Java Class" from the popped menu, a dialog will be popped up:

/Images/20170930/104.jpg

    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.

/Images/20170930/105.jpg

Yeah!