Database manager - Android

• Database manager - Android
• Creating a database
• Accessing database / tables
• Database upgrade

The Core module provides a set of capabilities to handle the CRUD operations required by the mobile apps. These capabilities can be leveraged by using the Database manager in the Core module. The database layer is the wrapper created around the Android SQLite database. All the database operations can be handled with the class ORADBManager.

ORADBManager is implemented as an abstract class. You can create a subclass and leverage the onCreateDB and onUpgradeDB methods for your database operations.

Creating a database

You can use the onCreateDB method to create the tables for your data storage. This method needs to be called only once. You can add your table creation statements to the method. You can create multiple tables with this method. You need to pass the valid SQLite create table query.

To access the database instantiate the subclass with the help the constructor.

Subclass instantiation

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

Subclass

public class ORADBManagerTestImp extends ORADBManager {
 
				public ORADBManagerTestImp(@Nullable Context context, @Nullable String databaseName, int databaseVersion) {
				super(context, databaseName, databaseVersion);
				}
				@Override
				public void onCreateDB(SQLiteDatabase database) {
				database.execSQL("CREATE TABLE contacts ( contact_id integer PRIMARY KEY, first_name text NOT NULL);");
				}
 
				@Override
				public void onUpgradeDB(SQLiteDatabase database, int oldVersion, int newVersion) {
 
				}
				}
		

Accessing database / tables

The ORADBManager class provides methods to carryout read and write operations on the tables you have created.

Insert Records

insertOrReplace() This method adds a new record to the table or replaces an existing record based on primary key constraints for the record.

public synchronized boolean insertOrReplace(String tableName, ContentValues contentValues)
		

Retrieve records

getData() This method helps to fetch the records from a table.

Retrieve number of records from the table

public synchronized List<ContentValues> getData(String tableName, int count)
		

Retrieve records with matching criteria

public synchronized List<ContentValues> getData(String tableName, String[] columns, String selection,
				String[] selectionArgs, String groupBy, String having,
				String orderBy)
		

Example

dbMgr.getData("contacts_table", new String[]{"first_name"}, "last_name = ?", new String[]{"Doe"}, "contact_number", null, null)
		

Retrieve records with matching criteria

public synchronized List<ContentValues> getData(String tableName, String whereColumn, String whereArg)
				dbMgr.getData("tableName", "colName", "value");
		

Retrieve records for a range of matching criteria

We need to pass array contains two values in the arguments like, n1 and n2. The query returns all the records where the column values lie between n1 and n2.

public synchronized List<ContentValues> getData(String tableName, String colName, String[] range)
		

Retrieve records with custom SQLite query

You can pass any valid SQLite query to retrieve the required records from your database. For example, you can use this query to retrieve data from multiple tables.

public synchronized List<ContentValues> getData(String query)
		

Update Records

Update records matching a criteria

The following method updates all the records matching with where clause. To update all the records you need to pass null for where clause.

public synchronized boolean update(String tableName, ContentValues values, String whereClause, String[] whereArgs)
		

• whereClause: The optional WHERE clause to apply when updating. Passing null will update all rows.
• whereArgs: You may include ?s in the where clause, which will be replaced by the values from whereArgs. The values will be bound as Strings.

Update records by SQLite Query

The following method accepts a query to update the table. The method returns true if the record(s) is updated, and returns false otherwise.

public synchronized boolean updateDataByQuery(String query)
		

Delete Records

Delete records matching a criteria

This method helps to delete records matching a specific value in a column.

public synchronized boolean deleteData(String tableName, String colName, String value)
		

Delete records from matching multiple criteria

This method helps to delete records matching a multiple values in a column.

public synchronized boolean deleteData(String tableName, String colName, String[] values)
		

Delete all records in a table

This method helps to delete all the records in the table.

public synchronized boolean deleteData(String tableName)
		

Delete Records with custom SQLite query

You can pass any valid SQLite query to delete the required records from your database.

public synchronized boolean deleteDataByQuery(String sqlQuery)
		

Database upgrade

This capability helps to upgrade the SQLite database with the database manager. You can use the onUpgradeDB method for making the necessary updates. The database version is an important argument that needs to be passed during this operation. The ORADBManager constructor has an argument called the databaseVersion. The old and the latest version of the database must always be passed as arguments in this method.

public void onUpgradeDB(SQLiteDatabase db, int oldVersion, int newVersion)
		

Once the upgrade is done, you must pass the latest database version for instantiation of the ORADBManager class. > Note: The onUpgrade will be called only when you pass new higher value of the newVersion compared to the oldVersion in the ORADBManager constructor.

Update/Modify tables

Adding a new column to the table

This method can be used to add a new column to the existing table.

public class ORADBManagerTestImp extends ORADBManager {
 
				public ORADBManagerTestImp(@Nullable Context context, @Nullable String databaseName, int databaseVersion) {
				super(context, databaseName, databaseVersion);
				}
 
				@Override
				public void onCreateDB(SQLiteDatabase database) {
				database.execSQL("CREATE TABLE contacts ( contact_id integer PRIMARY KEY, first_name text NOT NULL);");
				}
 
				@Override
				public void onUpgradeDB(SQLiteDatabase database, int oldVersion, int newVersion) {
				database.execSQL("ALTER TABLE contacts ADD COLUMN last_name string;");
				}
 
				boolean clearData(){
				getWritableDatabase().execSQL("delete from contacts");
				return true;
				}
				}