Zachary Powell

Software Programmer

Zachary Powell

Download my CV Send me a Message

Using SQLITE database to store data within Android App

Zachary Powell - 09/12/2015

In our last post, we explored using SharedPreferences to save data. This works well for simple primitive data, but if we have a more advanced data structure we will need a better way to store it. As with other programs we would look towards a database. Android supports SQLite Databases, so this is what we would use.

What is SQLite?

First and foremost, before we look at how we can use the SQLite database on Android, lets take a second to look at what SQLite actually is.

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private. SQLite is the most widely deployed database in the world, with more applications than we can count, including several high-profile projects.

SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers and views, is contained in a single disk file. The database file format is cross-platform – you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. These features make SQLite a popular choice as an Application File Format. Think of SQLite not as a replacement for Oracle but as a replacement for fopen()

From – https://www.sqlite.org/about.html

Or more simply put SQLite is a very light and easy to use implementation of a SQL database, which does not require a server to run and is perfect for running on a phone.

Java Object Class

Lets first look at an example, simple object class. This will be the object we save to the database and the object we build when retrieving data back from the database. In our example, we will look at a database which holds a range of buildings. Each building will have its name, description, long and lat stored.

public class Building {
    String Name;
    String Decription;
    double Long;
    double Lat;

    Building(){
    }
    Building(String n, String d, double lo, double la){
        Name = n;
        Decription = d;
        Long = lo;
        Lat = la;
    }
    public double getLong() {
        return Long;
    }
    public double getLat() {
        return Lat;
    }
    public String getName() {
        return Name;
    }
    public String getDecription() {
        return Decription;
    }
    public void setName(String name) {
        this.Name = name;
    }
    public void setDesc(String desc) {
        this.Decription = desc;
    }
    public void setLong(double aLong) {
        this.Long = aLong;
    }
    public void setLat(double lat) {
        this.Lat = lat;
    }
    public String toString(){
        return "Name: " + Name + " Decription: " + Decription + " Long: " + Long + " Lat: " + Lat;
    }
}

This is a pretty standard object class with getters and setters set up as well as two construction methods, one that is empty and another that takes all the arguments. These will make creating and reading data much easier. Of course this is just an example of a data structure, you may wish to save to a database.

Database Handler

The Database Handler is the class that we will use to create and access the SQLite database. Below is the complete example which we will then brake down and explore.

public class DBHelper extends SQLiteOpenHelper {

    public static final String DATABASE_NAME = "locations.db";
    SQLiteDatabase db;

    public DBHelper(Context context)
    {
        super(context, DATABASE_NAME , null, 1);
        db = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE LocationInfo (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, Name TEXT, Description TEXT, Longitude TEXT, Lat TEXT)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS LocationInfo");
        onCreate(db);
    }

    public void clearLocations(){
        db = this.getWritableDatabase();
        db.execSQL("delete from LocationInfo");
        db.close();
    }

    public boolean insertLocation  (Building place) {
        db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("Name", place.getName());
        contentValues.put("Description", place.getDecription());
        contentValues.put("Longitude", place.getLong());
        contentValues.put("Lat", place.getLat());
        db.insert("LocationInfo", null, contentValues);
        db.close();
        return true;
    }

    public ArrayList<Building> getAllLocations()
    {
        db = this.getWritableDatabase();
        ArrayList<Building> array_list = new ArrayList();
        Cursor res;
        res =  db.rawQuery( "select * from LocationInfo", null );
       
        res.moveToFirst();
        while(res.isAfterLast() == false){
            BuildingLocation place = new BuildingLocation();
            place.setName(res.getString(res.getColumnIndex("Name")));
            place.setDesc(res.getString(res.getColumnIndex("Description")));
            place.setLat(res.getDouble(res.getColumnIndex("Lat")));
            place.setLong(res.getDouble(res.getColumnIndex("Longitude")));
            array_list.add(place);
            res.moveToNext();
        }
        db.close();
        return array_list;
    }
}

Lets brake this example down a little and go over the important parts.

Firstly, we extent our helper class with SQLiteOpenHelper. This is the Android class that handles the database creation.

Next we have the Constructor method for the database, this uses the SQLiteOpenHelper Constructor to create a database file with the name variable using:

super(context, DATABASE_NAME , null, 1);

The other two methods that we override from the parent class are the below:

@Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE LocationInfo (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, Name TEXT, Description TEXT, Longitude TEXT, Lat TEXT)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS LocationInfo");
        onCreate(db);
    }

The onCreate method is called when the database file is created. Here we run the SQL to create the table within the database file and here we create fields for all the fields with the Building object class. As with all SQL databases we also create and ID field that is auto-incremented.

The onUpgrade method is called if we need to make changes to a pre-existing database on a users device. For example, if we wanted to release an upgrade to our app that adds another field to the table, we run this method, it drops the table and then re-runs the onCreate.

The Next method clearLocations() is a simple example of method to interact with the database. Here we just run a SQL statement to clear the table of all data. The method takes no arguments and does not return anything. It is important to note that we must first open the database again, and once we have executed the SQL, we need to close the database.

The next method (below) is an example of a method used to insert data into the database

public boolean insertLocation  (Building place) {
        db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("Name", place.getName());
        contentValues.put("Description", place.getDecription());
        contentValues.put("Longitude", place.getLong());
        contentValues.put("Lat", place.getLat());
        db.insert("LocationInfo", null, contentValues);
        db.close();
        return true;
    }

Here we take a Building object as a argument. The safest and easiest way to then insert this data into the database is to create a ContentValues object. This allows you to “put” each primitive data  object from the Building object into a form that can be inserted into the database. The .put() method takes two argument. The first is the name of the table column and the second is the data you wish to put into that column.

Using the Building objects getter classes we load all the data we plan to store in the database for that Building object. Then using the .insert() method, we can pass the ContentValues object along with the name of the database table. Finally once again we close the database.

public ArrayList<Building> getAllLocations()
    {
        db = this.getWritableDatabase();
        ArrayList<Building> array_list = new ArrayList();
        Cursor res;
        res =  db.rawQuery( "select * from LocationInfo", null );
       
        res.moveToFirst();
        while(res.isAfterLast() == false){
            BuildingLocation place = new BuildingLocation();
            place.setName(res.getString(res.getColumnIndex("Name")));
            place.setDesc(res.getString(res.getColumnIndex("Description")));
            place.setLat(res.getDouble(res.getColumnIndex("Lat")));
            place.setLong(res.getDouble(res.getColumnIndex("Longitude")));
            array_list.add(place);
            res.moveToNext();
        }
        db.close();
        return array_list;
    }

Finally we have an example of a method to get data back out of the database. Here we use a Cursor object to move over the retrieved data. The SQL query we run simply returns a Cursor object with all the data in the table, but we may want to be more specific with the data we get back.

Using a whole loop, we then move over each object within the Cursor, using the method  .getColumnIndex() to return a columns index based on its name and then using the respected .get method depending on the data type. Using out Building objects setter classes we can then build up a building object to add to the array.

Once we have looped over the whole cursor object we can simply close the database and return our array of Building objects.

Example of Use

With the above, you have all you need to start working with databases within Android, but lets just take a second to see how we might use the above code within another method, perhaps an activity where the user enters a building details and saves them to the database. In  Below example we just set the values but the data could be taken from EditText view etc.

DBHelper mydb = new DBHelper(this);

String name = "Church";
String desc = "The tallest building in the village";
double longitude = 1.458375;
double Lat = 52.105336;

Building map = new Building();
map.setName(name);
map.setDesc(desc);
map.setLong(longitude);
map.setLat(Lat);
mydb.insertLocation(map);

As always if you have any questions feel free to comment below.

Leave a Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

Tags: , ,

“ Any fool can write code that a computer can understand. Good programmers write code that humans can understand. ”

-Martin Fowler