Click here to Skip to main content
15,867,141 members
Articles / Mobile Apps / Android

Android dynamic Sqlite database management v.2

Rate me:
Please Sign up or sign in to vote.
4.91/5 (6 votes)
16 Oct 2014CPOL8 min read 39.2K   1.2K   21   4
Article about dynamic Sqlite database management on Android platform using Java class reflection.

Source

GitHub : https://github.com/Pavel-Durov/CodeProject---Android-dynamic-Sqlite-database-management-v.2
Direct:

Table of Contents

Introduction

In the following article I will make an example of management and implementation of Sqlite database on android platform by using reflection of the Java classes.

Requirements

1.Basic understanding of reflection.

2.Basic understanding in android application structure.

3.Basic Sqlite orientation.

Reflection in a nutshell

Reflection is a language ability that gives you the capability to inspect and dynamically call classes, methods, attributes etc, on specific objects based on their structure without knowing them in advance.

Since Reflection happens in the runtime, it makes your code very flexible and dynamic. You can determine given class even if you don’t know its formation at compile time.

Background

I was very frustrated at first when I started working with android sqlite database, because in every tutorial that I read, the integration with sqlite database was made by raw hardcoded string query without any dynamic capabilities.

Every little change in the database structure was very hard and took me a lot of time, because I was dealing with those hardcoded strings.

Android application class

Android application class is a base class for android application. After declaring application class in your AnadroidManifest.xml it will be instantiated when the process of your application/package is created.

Declaring your custom application class is necessary when you want to keep instances through all your process life time.

In our case we will crate DataBaseHandler in the application class once, and afterwards we will access it from any activity in our program.

Declaring an Application class

First, we need to create our base class which extends SDK application class, we will call it DyncamicSqliteApplication:

Java
 public class DyncamicSqliteApplication extends Application
{
    @Override
    public void onCreate()
    {
        super.onCreate();
    }
}

Then we will need to declare our class in the AnadroidManifest.xml under the <application> tag as value of “android:name” attribute:

XML
<application
        android:allowBackup="true"
        android:name="net.simplydone.main.DyncamicSqliteApplication"
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name"
        android:theme="@style/AppTheme" >
        <activity
            android:name="net.simplydone.gui.MainActivity"
            android:label="@string/app_name" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

If you’ll put a breakpoint in the onCreate() method and lunch your android program, you will see that the first thing that is called as your program process is launched, is the creation of your application class.

Implementing DataBaseHandler class

For sqlite integration purpose we will create a DataBaseHandler class in our application class.

In order to create our DataBaseHandler we will extend SQLiteOpenHelper android SDK class.

SQLiteOpenHelper - A helper class to manage database creation and version management.

More on:

http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html

Java
public class DataBaseHandler  extends SQLiteOpenHelper
{
    public DataBaseHandler (Context context)
    {
        super(context, ConstantsCollection.DATABASE_NAME, null,
        ConstantsCollection.DATABASE_VERSION);
    }
}

In DataBaseHandler constructor, we are specifying the database name and the database version, as we call the super class constructor.

Our DataBaseHandler class will be a mediator layer between our application and the Sqlite database.

Implementing DB_BASIC and TEST classes

All other methods in DataBaseHandler class will be based on Java reflection and according to passed class structure or type, will implement some sqlite functions such as ‘CREATE TABLE’, “UPDATE”, “INSERT” etc…

Creating DB_BASIC class:

Java
public class DB_BASIC
{
     public Long ID;
}

DB_BASIC class has only one Long type field, named ID. This class will be extended by every class that we will use for sqlite reflection based methods.

ID field is essential for each integration class to be implemented, because every sqlite raw has to contain that field as its primary key. This creates convention for future Sqlite operations.

Our TEST class will extend DB_BASIC class and contains various number fields with various types.

TEST class is supposed to be a very simple, clear class. Only primitive members that can be parsed to Sqlite format can be a part of it.

Java
public class TEST extends DB_BASIC
{
    public Double TEST_DOUBLE_FIELD_0;
    public Long TEST_LONG_FIELD_1;
    public Integer TEST_INTEGER_FIELD_2;
    public String TEST_STRING_FIELD_3;
}

Creating TEST table in Sqlite Database

In our Application class we will instantiate our DataBaseHandler  passing Application Context instance to its constructor, and afterwards we’ll call addObjectTable() method passing a new TEST class, in favor of creating compatible table for our TEST object storage:

Java
private void InitDB()
{
    //Initialize DB handler
    _dbhandler = new DataBaseHandler (this);
    //Adding table based on class TEST reflection
    _dbhandler.addObjectTable(new TEST());
}

addObjectTable() method will traverse on TEST class fields using reflection, and will create appropriate sqlite table, with structure accordingly to TEST class architecture.

The name of the table will be the same as the name of the passed class instance. The name of the records in the table as well as their types will be as specified in the object.

Inserting TEST class values to sqlite TEST table

After we instantiated DataBaseHandler class and created TEST table, we can perform an insert operation as shown here:

Creating instance on TEST class with some various values:

Java
TEST result = new TEST();
result.TEST_DOUBLE_FIELD_0 = 0.890;
result.TEST_INTEGER_FIELD_2 = 34;
result.TEST_STRING_FIELD_3 = "This is string field";

When using AddNewObject() method all we need is to pass the TEST class to the DataBaseHandler and the rest of the work is done automatically, depending on class implementation.

Creating dynamic table

First we need to create an object in our database, so press the “Add new object to DB” button on the main activity screen.

Image 1

That action will call the GetDB() method in our application class.

Java
public DataBaseHandler GetDB()
{
    if(_dbhandler == null)
    {
        InitDB();
    }
    
    return _dbhandler;
}

If our _dbhandler is still not initialized, this method will call the InitDB() method.

Java
private void InitDB()
{
    //Initialize DB handler
    _dbhandler = new DataBaseHandler(this);
    //Adding table based on class TEST reflection
    _dbhandler.CreateTable(new TEST());
}

The whole purpose of InitDB method is to instantiate our DataBaseHandler class and to create a table of type TEST in our database.

After GetDB method execution, we are calling AddNewObject() method from our activity. That will iterate on passed object fields and accordingly to their types and names will insert the data to the table.

After we created the handler class and the table with inserted values, we can pull out the Sqlite database from our Android device for examination, by running those adb commands:

If you are new to adb tool, please consider reading my related articles:

http://www.codeproject.com/Articles/825304/Accessing-internal-data-on-Android-device

http://www.codeproject.com/Tips/827908/Android-ADB-Commands-Using-Batch-File

 

cd %ANDROID_SDK%/platform-tools

adb shell su -c "chmod 777 /data"

adb shell su -c "chmod 777 /data/data"

adb shell su -c "chmod 777 /data/data/net.simplydone.dynamicsqlite"

adb shell su -c "chmod 777 /data/data/net.simplydone.dynamicsqlite/databases"

adb shell su -c "chmod 777 /data/data/net.simplydone.dynamicsqlite/databases/DynamicSqlite"

adb pull /data/data/net.simplydone.dynamicsqlite/databases/DynamicSqlite C:/Users/Pavel/Desktop

Notice that the pulled DynamicSqlite file does not have an extension. If your Sqlite browser doesn’t open it, try to add a db extension to it. Simply rename it from DynamicSqlite to DynamicSqlite.db.

Now that we have our file on our machine, we can examine it.

I am using DB Browser for SQLite but any other similar programs will do.

Image 2

As you can see, we have TEST table in our database with the columns names and types as our TEST object structure.

Notice that ID column is created, since it is a member of the DB_BASIC class. Also, that Long type is saved as INTEGER.

From Sqlite site:

INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

https://www.sqlite.org/datatype3.html

Java
public class TEST extends DB_BASIC
{
    public Double        TEST_DOUBLE_FIELD_0;
    public Long         TEST_LONG_FIELD_1;
    public Integer     TEST_INTEGER_FIELD_2;
    public String     TEST_STRING_FIELD_3;
}

Our DataBaseHandler decides which Sqlite type to choose to a Java type in the GetSqliteType(Class<?> c) method, it simply checks the Java type and accordingly returns Sqlite raw String type from our ConstantCollection class.

If you intended to use other types than those that I used for our objects, you can do it in GetSqliteType(Class<?> c) method.

Java
private String GetSqliteType(Class<?> c)
{
    String type = "TEXT";
    
    if (c.equals(String.class))
    {
        type = ConstantsCollection.SQLITE_TEXT;
    }
    else if (  c.equals(Integer.class)
            || c.equals(Long.class)
            || c.equals(Number.class)
            || c.equals(java.util.Date.class))
    {
        type = ConstantsCollection.SQLITE_INTEGER;
    }
    else if(c.equals(Double.class))
    {
        type = ConstantsCollection.SQLITE_DOUBLE;
    }
    return type;
}

If we explore the data of the created table, we’ll see that the inserted data to the table is the same as we instantiated our TEST object with.

Image 3

DataBaseHandler methods

AddNewObject method

As we mentioned before, our methods in DataBaseHandler class uses Java reflection.

Every public method in DataBaseHandler class gets a DB_BASIC object instance or a Class object as a parameter, since all method’s operation are defined by the Java class architecture.

Java
public long AddNewObject(DB_BASIC object)
{        
    long result = ConstantsCollection.INDEX_NOT_DEFINED;
    if(object != null)
    {
        SQLiteDatabase db = null;
        try
        {
            db = this.getWritableDatabase();
            ContentValues values = new ContentValues();
            Class<? extends DB_BASIC> c = object.getClass();
            Field[] fields = c.getFields();
            for (Field field : fields)
            {
                Object val = GetValue(field, object);
                if (val != null)
                {
                    String rawValue = null;
                    if (field.getType().equals(Date.class))
                    {
                        try
                        {
                            rawValue = DateUtils.DateToValue((Date) val);
                        }
                        catch (ParseException e)
                        {
                            Log.e(LOG_TAG, e.toString());
                        }
                    }
                    else
                    {
                        rawValue = val.toString();
                    }
                    String name = field.getName();
                    values.put(name, rawValue);
                }
            }
            String tableName = ReflectionUtils.GetClassName(object.getClass());
            if(values.size() > 0)
            {
                result = db.insert(tableName, null, values);
            }
        }
        finally
        {
            CloseDB(db);
        }
    }
    return result;
}

The concept is very simple. This method iterates on the class fields; it transforms their Java values to Sqlite types and adds the object to the appropriate database table which identifies by the class name.

CreateTable method

This method is called from our application class, it is an essential step in our database creation process, since it creates our DB_BASIC object tables.

Java
public void CreateTable(DB_BASIC object)
{
    if(object != null)
    {
        SQLiteDatabase db = null;
        try
        {
            db = getWritableDatabase();
        
            Class<? extends DB_BASIC> c = object.getClass();
            String tableName = c.getName();
            
            tableName = ReflectionUtils.GetClassName(c);
            
            //Fields of the object
            Field[] fields = c.getFields();
            
            StringBuilder sbCreateTable  = new StringBuilder();
            
            //Beginning of the CREATE raw query
            sbCreateTable.append(ConstantsCollection.SQLITE_CREATE_TABLE_IF_NOT_EXISTS);
            sbCreateTable.append(tableName);
            sbCreateTable.append(ConstantsCollection.SQLITE_OPENNING_BRACKET);
            
            //Iterates on the given object fields using reflection
            //and creates appropriate column definition
            for (int i = 0; i < fields.length; i++)
            {
                String  fieldName= fields[i].getName();
                
                if(fieldName.equalsIgnoreCase(ConstantsCollection.ID))
                {//Creates an auto increament index named ID
                    sbCreateTable.append(fieldName);
                    sbCreateTable.append(ConstantsCollection
                                            .SQLITE_INTEGER_PRIMARY_KEY_AUTOINCREMENT);
                }
                else
                {//Creates column declaration
                    String rowname = GetSqliteType(fields[i].getType());
                    
                    if(rowname != null)
                    {
                        sbCreateTable.append(fieldName);
                        sbCreateTable.append(ConstantsCollection.SQLITE_SPACE);
                        sbCreateTable.append(rowname);
                    }
                }
                
                if(i != fields.length - 1)
                {//Allways adds , in the end of each column declaration except the last one
                    sbCreateTable.append(ConstantsCollection.SQLITE_COMMA);
                    sbCreateTable.append(ConstantsCollection.SQLITE_SPACE);
                }
            }
        
            //Closing raw CREATE Query with }; characters
            sbCreateTable.append(ConstantsCollection.SQLITE_CLOSING_BRACKET);
            sbCreateTable.append(ConstantsCollection.SQLITE_CLOSING_SEMICOLUMN);
            
            //Executes raw SQlite statement
            db.execSQL(sbCreateTable.toString());
        }
        catch (SecurityException e)
        {
            Log.e(LOG_TAG, e.toString());
        }
        catch (Exception e)
        {
            Log.e(LOG_TAG, e.toString());
        }
        finally
        {
            //Closing the DB connection
            CloseDB(db);
        }
    }
}

As you can see, AddNewObject and CreateTable methods are not that different in their core, both of them get a DB_BASIC class as a parameter and by using reflection, they generate the appropriate raw string Sqlite command using iteration on the given object members.

Notice that we are using here StrinBuilder class instead of regular String.

ConvertCursorToObject method

ConvertCursorToObject method is called only from GetTableData method in our DataBasehandler class, it is responsible for converting given Cursor object that came to us as a result of Sqlite query to a passed Class<? extends DB_BASIC> instance parameter.

Cursor is an interface, which provides random read-write access to the result set returned by a database query.

Cursor documentation:

http://developer.android.com/reference/android/database/Cursor.html

First thing that we do in our method, is moving to the first record in the Coursor object, performed by calling moveToFirst() method.

After that, we’ll enter the do while loop that loops until we get to the last record in our Coursor, until method moveToNext() returns false.

Next, ConvertCursorToObject method instantiates the given class using ReflectionUtils class and performs iteration on the Cursor columns and accordingly sets values in the instantiated object.

It collects the objects to one List and returns it as a result.

Java
@SuppressWarnings("unchecked")
private <T> List<T> ConvertCursorToObjects(Cursor cursor, Class<?> clazz)
{
    List<T> list = new ArrayList<T>();
    //moves the cursor tyo the first row
    if (cursor.moveToFirst())
    {
        String[] ColumnNames = cursor.getColumnNames();
        do
        {
            Object obj = ReflectionUtils.GetObject(clazz);
            
            //iterates on column names
            for (int i = 0; i < ColumnNames.length; i++)
            {
                try {
                    
                    Field field = obj.getClass().getField(ColumnNames[i]);
                    Object objectValue = null;
                    String str = cursor.getString(i);
                    
                    if(str != null)
                    {
                        //Converting stored Sqlite data to java objects
                        if (field.getType().equals(java.util.Date.class))
                        {
                            Date date = DateUtils.ValueToDate(str);
                            objectValue = date;
                            field.set(obj, objectValue);
                        }
                        else if (field.getType().equals(Number.class))
                        {
                            objectValue = NumberFormat.getInstance().parse(str);
                        }
                        else if(field.getType().equals(Long.class) )
                        {
                            objectValue = NumberFormat.getInstance().parse(str);
                            long value = Long.parseLong(objectValue.toString());
                            field.set(obj, value);
                        }
                        else if(field.getType().equals(Integer.class) )
                        {
                            objectValue = NumberFormat.getInstance().parse(str);
                            int value = Integer.parseInt(str);
                            field.set(obj, value);
                        }
                        else if(field.getType().equals(Double.class) )
                        {
                            objectValue = NumberFormat.getInstance().parse(str);
                            double value = Double.parseDouble(objectValue.toString());
                            field.set(obj, value);
                        }
                        else
                        {
                            objectValue = str;
                            field.set(obj, objectValue);
                        }
                    }
                }
                catch (IllegalArgumentException e)
                {
                    Log.e(LOG_TAG, e.toString());
                }
                catch (IllegalAccessException e)
                {
                    Log.e(LOG_TAG, e.toString());
                }
                catch (ParseException e)
                {
                    Log.e(LOG_TAG, e.toString());
                }
                catch (SecurityException e)
                {
                    Log.e(LOG_TAG, e.toString());
                }
                catch (NoSuchFieldException e)
                {
                    Log.e(LOG_TAG, e.toString());
                }
            }
            if(obj  instanceof DB_BASIC)
            {
                list.add((T) obj);
            }
        } while (cursor.moveToNext());
    }
    return list;
}

UpdateRow

This method updates existing records in the relevant table with new values from the given object.

The update performed by ID identification field, which is located in the DB_BASIC class, which is why the first thing that is done is checking whether the object.Id is not null.

As before, it iterates on the object internal fields, and by their name and value performs a composition of the Sqlite query, for record update, using ContentValues class.

It returns the number of updated rows, which is always supposed to be 1 or 0, since the ID field is unique in our application database context.

Java
public int UpdateRow(DB_BASIC object)
{
    int result = ConstantsCollection.INDEX_NOT_DEFINED;
    
    if(object != null && object.ID != null)
    {
        SQLiteDatabase db = null;
        
        db = this.getWritableDatabase();
        String tableName = ReflectionUtils.GetClassName(object.getClass());
        
        StringBuilder sbWhereClause = new StringBuilder();
        
        sbWhereClause.append(ConstantsCollection.SQLITE_SPACE);
        sbWhereClause.append(ConstantsCollection.ID);
        sbWhereClause.append(ConstantsCollection.SQLITE_EQUAL_SIGN);
        sbWhereClause.append(String.valueOf(object.ID));
        
        ContentValues values = new ContentValues();
        
        //iterates on fields
        for(Field f : object.getClass().getFields())
        {
            String fieldValue = GetStringValue(f, object);
            String name = f.getName();
            
            if( fieldValue != null
                && !fieldValue.equals(StringUtils.EMPTY_STRING)
                && !name.equals(ConstantsCollection.ID) )
            {
                values.put(name, fieldValue);
            }
        }
        result = db.update(tableName, values, sbWhereClause.toString(), null);
    }

    return result;
}

Summary

In this article I showed you the basic functions that are implemented in the attached Android project.

There is much more functionality that can be added to DataBaseHandler class, that we did not discuss.

DataBaseHandler is an infrastructure class. After implementing it, you do not need to worry any more about sqlite integration, all you need to do is to create your business logic classes and when saving them to database, just convert them to DB_BASIC class, and pass them to an appropriate method in the DataBaseHandler class depending on your intention.

Using this dynamic approach based on java reflection, I saved a lot of time. Hope it will prove useful to you as well.

 

 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
Israel Israel
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionGetTableData Pin
ferkelrudolf19-Jan-15 1:54
ferkelrudolf19-Jan-15 1:54 
QuestionLink broken Pin
Mongy9617-Oct-14 3:13
professionalMongy9617-Oct-14 3:13 
AnswerRe: Link broken Pin
Pavel Durov18-Oct-14 6:00
professionalPavel Durov18-Oct-14 6:00 
QuestionGood work Pavel Pin
martin.nedopil16-Oct-14 22:50
martin.nedopil16-Oct-14 22:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.