This example will tell you how to encapsulate android SQLite database operations in the java class, then you can reuse them to operate different SQLite databases and tables.
1. Android SQLite Database Operations Reusable classes.
- There are three Java classes in this example as below.
./ ├── app │ ├── build.gradle │ ├── proguard-rules.pro │ └── src │ ├── main │ │ ├── AndroidManifest.xml │ │ ├── java │ │ │ └── com │ │ │ └── dev2qa │ │ │ └── example │ │ │ ├── storage │ │ │ │ └── sqlite │ │ │ │ └── util │ │ │ │ ├── DatabaseManager.java │ │ │ │ ├── SQLiteDatabaseHelper.java │ │ │ │ └── TableColumn.java
- SQLiteDatabaseHelper: This class is subclass of android.database.sqlite.SQLiteOpenHelper. It overwrite it’s onCreate() and onUpgrade() methods. This class’s main purpose is to create and upgrade the SQLite database and tables.
- DatabaseManager: This class includes methods to implement basic SQLite database and table operations, such as open database, close database, insert data, update data, delete data, and query data.
- TableColumn: This class represents a column in the table, it stores column name and value. It is used when inserting and updating tables. One table row is a list of TableColumn instances.
2. SQLite Database Create And Upgrade Java Class.
- SQLiteDatabaseHelper.java
package com.dev2qa.example.storage.sqlite.util; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.text.TextUtils; import android.widget.Toast; import java.util.ArrayList; import java.util.List; public class SQLiteDatabaseHelper extends SQLiteOpenHelper { // Contains all table name string list. private List<String> tableNameList = null; // Contains all create table sql command string list. private List<String> createTableSqlList = null; // This is the log tag in android monitor console. public static final String LOG_TAG_SQLITE_DB = "LOG_TAG_SQLITE_DB"; // This is the android activity context. private Context ctx = null; /* Constructor with all input parameter*/ public SQLiteDatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) { super(context, name, factory, version); ctx = context; } /* Generally run all create table sql in this method. */ @Override public void onCreate(SQLiteDatabase sqLiteDatabase) { if(createTableSqlList!=null) { int size = createTableSqlList.size(); for(int i = 0;i<size;i++) { // Loop all the create table SQL command string in the list. // each SQL will create a table in the SQLite database. String createTableSql = createTableSqlList.get(i); sqLiteDatabase.execSQL(createTableSql); Toast.makeText(ctx, "Run sql successfully, " + createTableSql, Toast.LENGTH_SHORT).show(); } } } /* When the new DB version is bigger than the current existing DB version, this method will be invoked. * It always drop all tables and then call onCreate() method to create all table again.*/ @Override public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) { if(tableNameList!=null) { // Loop and drop all exist sqlite table. int size = tableNameList.size(); for(int i = 0;i<size;i++) { String tableName = tableNameList.get(i); if(!TextUtils.isEmpty(tableName)) { sqLiteDatabase.execSQL("drop table if exists " + tableName); } } } // After drop all exist tables, create all tables again. onCreate(sqLiteDatabase); } public List<String> getTableNameList() { if(tableNameList==null) { tableNameList = new ArrayList<String>(); } return tableNameList; } public void setTableNameList(List<String> tableNameList) { this.tableNameList = tableNameList; } public List<String> getCreateTableSqlList() { if(createTableSqlList==null) { createTableSqlList = new ArrayList<String>(); } return createTableSqlList; } public void setCreateTableSqlList(List<String> createTableSqlList) { this.createTableSqlList = createTableSqlList; } }
3. SQLite Database Table Basic Operations Java Class.
- DatabaseManager.java
package com.dev2qa.example.storage.sqlite.util; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.text.TextUtils; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; public class DatabaseManager { // This is the activity context. private Context ctx = null; // Database helper class to manipulate sqlite database. private SQLiteDatabaseHelper dbHelper = null; // Represent connection with sqlite database. private SQLiteDatabase database = null; // Database name. private String dbName = ""; // Database version. private int dbVersion = 0; // All tables that this database contains. private List<String> tableNameList = null; // All table create sql that this db need. private List<String> createTableSqlList = null; // Create database manager instance. public DatabaseManager(Context ctx, String dbName, int dbVersion, List<String> tableNameList, List<String> createTableSqlList) { this.ctx = ctx; this.dbName = dbName; this.dbVersion = dbVersion; this.tableNameList = tableNameList; this.createTableSqlList = createTableSqlList; } // Open database connection. public DatabaseManager openDB() { // Create database helper instance. dbHelper = new SQLiteDatabaseHelper(ctx, this.dbName, null, this.dbVersion); dbHelper.setTableNameList(this.tableNameList); dbHelper.setCreateTableSqlList(this.createTableSqlList); // Get sqlite database connection. If not exist then create onw else return a database object. this.database = dbHelper.getWritableDatabase(); return this; } // close database connection. public void closeDB() { this.database.close(); this.dbHelper.close(); this.database = null; this.dbHelper = null; } /* Insert data into the table. * tableName: The table name. * columnList: A list of table column name-value pair. * */ public void insert(String tableName, List<TableColumn> columnList) { if(!TextUtils.isEmpty(tableName) && columnList!=null) { int size = columnList.size(); if(size > 0) { // Create a content values object. ContentValues contentValues = new ContentValues(); // Loop in the table column list for(int i=0;i<size;i++) { TableColumn tableColumn = columnList.get(i); // Put column name and value in content values. if(!TextUtils.isEmpty(tableColumn.getColumnName())) { contentValues.put(tableColumn.getColumnName(), tableColumn.getColumnValue()); } } // Insert data. this.database.insert(tableName, null, contentValues); } } } /* * Update data in table. * tableName : The table name. * columnList : Need update column name and value. * whereClause : The update rows meet condition. * */ public int update(String tableName, List<TableColumn> columnList, String whereClause) { int ret = 0; if(!TextUtils.isEmpty(tableName) && columnList!=null) { int size = columnList.size(); if(size > 0) { // Create a content values object. ContentValues contentValues = new ContentValues(); // Loop in the table column list for(int i=0;i<size;i++) { TableColumn tableColumn = columnList.get(i); // Put column name and value in content values. if(!TextUtils.isEmpty(tableColumn.getColumnName())) { contentValues.put(tableColumn.getColumnName(), tableColumn.getColumnValue()); } } // Update data.Return update row count. ret = this.database.update(tableName, contentValues, whereClause, null); } } return ret; } /* Delete rows in the table. * tableName : Delete data table name. * whereClause : Deleted rows meet condition. */ public void delete(String tableName, String whereClause) { if(!TextUtils.isEmpty(tableName)) { this.database.delete(tableName, whereClause, null); } } /* * Query all rows in SQLite database table. * tableName: The table name. * Return a list of a Map object. Each map object represents a row of data in the table. * */ public List<Map<String, String>> queryAllReturnListMap(String tableName) { List<Map<String, String>> ret = new ArrayList<Map<String, String>>(); // Query all rows in the table. Cursor cursor = this.database.query(tableName, null, null, null, null, null, null); if(cursor!=null) { // Get all column names in an array. String columnNamesArr[] = cursor.getColumnNames(); // Move to first cursor. cursor.moveToFirst(); do { // Create a map object represent a table row data. Map<String, String> rowMap = new HashMap<String, String>(); // Get column count. int columnCount = columnNamesArr.length; for(int i=0;i<columnCount;i++) { // Get each column name. String columnName = columnNamesArr[i]; // This is the column value. String columnValue = ""; // Get column index value. int columnIndex = cursor.getColumnIndex(columnName); // Get current column data type. int columnType = cursor.getType(columnIndex); if(Cursor.FIELD_TYPE_STRING == columnType) { columnValue = cursor.getString(columnIndex); }else if(Cursor.FIELD_TYPE_INTEGER == columnType) { columnValue = String.valueOf(cursor.getInt(columnIndex)); }else if(Cursor.FIELD_TYPE_FLOAT == columnType) { columnValue = String.valueOf(cursor.getFloat(columnIndex)); }else if(Cursor.FIELD_TYPE_BLOB == columnType) { columnValue = String.valueOf(cursor.getBlob(columnIndex)); }else if(Cursor.FIELD_TYPE_NULL == columnType) { columnValue = "null"; } rowMap.put(columnName, columnValue); ret.add(rowMap); } }while(cursor.moveToNext()); cursor.close(); } return ret; } /* * Query all rows in SQLite database table. * tableName: The table name. * Return a Cursor object. * */ public Cursor queryAllReturnCursor(String tableName) { // Query all rows in the table. Cursor cursor = this.database.query(tableName, null, null, null, null, null, null); if(cursor!=null) { // Move to first cursor. cursor.moveToFirst(); } return cursor; } }
4. SQLite Database Table Column Java Class.
- TableColumn.java
package com.dev2qa.example.storage.sqlite.util; /* This class represent one table column*/ public class TableColumn { // This is the table column name. private String columnName; // This is the table column value. private String columnValue; public String getColumnName() { return columnName; } public void setColumnName(String columnName) { this.columnName = columnName; } public String getColumnValue() { return columnValue; } public void setColumnValue(String columnValue) { this.columnValue = columnValue; } }
