Database Manager Guide

The Oracle CX Core SDK provides a database layer to deal with CRUD operations. The provided database layer is the wrapper around android SQLite database. All the database operations can be handled with the class ORADBManager

Creating the database

ORADBManager is an abstract class. You can subclass by implementing onCreateDB and onUpgradeDB to create & modify database with tables.

onCreateDB is the method where you can create the structure of the tables. This method will be called only once. You can write the table creation statements here. You can create multiple tables here. To create tables you need to pass the valid sqlite create table query. Below is the sample code.

@Override
publicvoid onCreateDB(SQLiteDatabase database) {
    database.execSQL("CREATE TABLE contacts ( contact_id integer PRIMARY KEY, first_name text NOT NULL);");
}

To access the database instantiate your subclass of ORADBManager with the help of any one of the provided constructor.

public ORADBManager(@Nullable Context context, @Nullable String databaseName, int databaseVersion)
public ORADBManager(@Nullable Context context, @Nullable String databaseName, CursorFactory factory, int databaseVersion)

The subclass will look like below.

publicclass ORADBManagerTestImp extends ORADBManager {

                                                                        

                                                                        
public ORADBManagerTestImp(@Nullable Context context, @Nullable String databaseName, int databaseVersion) {
        super(context, databaseName, databaseVersion);
    }

                                                                        
    @Override
publicvoid onCreateDB(SQLiteDatabase database) {
        database.execSQL("CREATE TABLE contacts ( contact_id integer PRIMARY KEY, first_name text NOT NULL);");
    }

                                                                        
    @Override
publicvoid onUpgradeDB(SQLiteDatabase database, int oldVersion, int newVersion) {

                                                                        
    }
}

Note: Refer Upgrading the database section for databaseVersion info in the constructor.

Access the database / tables

ORADBManager provides number of concrete methods to do read and write operations on the tables.

Insert

publicsynchronizedboolean insertOrReplace(String tableName, ContentValues contentValues)

The above method will insert a new row in to the table or replace the existing row based on primary key constraint.

Retrieve the data

getData is the method to fetch the table rows. There are multiple versions of getData method are available.

1. publicsynchronized List<ContentValues> getData(String tableName, int count)

   The above method will return top count number of rows from the table.

2. publicsynchronized List<ContentValues> getData(String tableName, String whereColumn, String whereArg)

   The above method return rows which are matching for the where criteria. Below is the example code.

   dbMgr.getData("tableName", "colName", "value");

   The above will return the all the rows with colName contains value

3. publicsynchronized List<ContentValues> getData(String tableName, String colName, String[] range)

   The above will return the rows between the rage. We need to pass array contains two values. Assume array contains n1 and n2, it will return all the rows with column value in between n1 and n2.

4. publicsynchronized List<ContentValues> getData(String query)

   The above method will return the table data based on the query. The query will any valid sqlite select query. This method is use to get table data from multiple tables.

Update data

To update the record, below is the method, which accepts string query as a parameter and returns true if record(s) updated successfully, else false

public synchronized boolean updateDataByQuery(String query)

Delete rows

deleteData is the method to delete the table rows. There are multiple versions of deleteData method are available.

1. publicsynchronizedboolean deleteData(String tableName, String colName, String value)

   The above method delete all the rows in which colName has value.

2. publicsynchronizedboolean deleteData(String tableName, String colName, String[] values)

   The above method delete all the rows in which colName has the values.

3. publicsynchronizedboolean deleteData(String tableName)

   This method will remove all the rows from the table.

4. publicsynchronizedboolean deleteDataByQuery(String sqlQuery)

   The above method will delete the table data based on the query. The query will any valid sqlite delete query.

Upgrading the database

Once you created database with tables, there may be any requirement to remove or add new tables, or need to alter the table structure. onUpgradeDB method will be used in such scenarios. Here database version will play a major role in upgrading the database.

If you see ORADBManager constructor, it has a parameter called databaseVersion. Every time you initiate ORADBManager, you have to pass the correct database version.

Assume at the time of database creation you passed the value as 1 for databaseVersion. So every time you access the ORADBManager you have to pass 1 only.

Now to upgrade the database you need to write the update queries in onUpgrade method.

publicvoid onUpgradeDB(SQLiteDatabase db, int oldVersion, int newVersion)

If you see onUpgrade signature it contains two integer values, oldVersion and newVersion. So when you implement onUpgrade method, to invoke this method, you have to pass new value to the ORADBManager constructor's databaseVersion. And this value should be higher than earlier one. So the onUpgrade method's oldVersion will take earlier value, and newVersion will take latest value. For the subsequence ORADBManager instantiation, you have to pass new value only.

Note: The onUpgrade will be called only when you pass new higher value to the ORADBManager constructor. Otherwise it won't be called.

Below is the sample code if you want to add new column to contacts table which is shown in "Creating the database" example

publicclass ORADBManagerTestImp extends ORADBManager {

                                                                        

                                                                        
public ORADBManagerTestImp(@Nullable Context context, @Nullable String databaseName, int databaseVersion) {
        super(context, databaseName, databaseVersion);
    }

                                                                        
    @Override
publicvoid onCreateDB(SQLiteDatabase database) {
        database.execSQL("CREATE TABLE contacts ( contact_id integer PRIMARY KEY, first_name text NOT NULL);");
    }

                                                                        
    @Override
publicvoid onUpgradeDB(SQLiteDatabase database, int oldVersion, int newVersion) {
        database.execSQL("ALTER TABLE contacts ADD COLUMN last_name string;");
    }

                                                                        
boolean clearData(){
        getWritableDatabase().execSQL("delete from contacts");
returntrue;
    }
}