Databases are an essential tool to keep user data and the application in general safe, organized and accessible at all times; this last point is essential and is to be able to access the saved data at all times. In modern times we have seen how all applications today connect to an external API, server, essentially an external agent to bring data and present it. to the user in an organized way; of course, for this it is necessary to have a connection to the Internet, or at least to the Intranet; if this connection is lost then the application would be unusable since it would not be able to fetch the information and present it to the user.
What is a database in SQLite?
SQLite is a lightweight and open database engine, whose main attraction is that we can easily and securely keep user or application data in a very small file and that differs from other systems such as MySQL in that it does not require the support of a server, it does not require port configuration support and so on and everything, but everything is stored in a single and simple file that is our database and it is open source therefore, you will see it in multiple applications that are not Android only with Android Studio or other similar IDEs.
Using SQLite database to store data locally
This is where the database comes in to store information locally on Android; currently the database that Android supports is called SQLite and essentially it is a small file that contains all the data of our application, which we register to be more precise; We can make queries as if it were a relational database, because it is a full-fledged database and in this post we will talk about how to use this database in our Android applications.
Examples of creating a SQLite database in Android
The first thing we must do is logically create our database; For this, a Helper class is used in which we define the structure of our database; the tables, data types, and primary keys are defined in this Helper made with Kotlin as the programming language:
class ListSQLiteHelper(
context: Context, name: String,
factory: SQLiteDatabase.CursorFactory?, version: Int
) :
SQLiteOpenHelper(context, name, factory, version) {
private val TABLE_NOTIFICATIONS =
"CREATE TABLE notifications(id INTEGER PRIMARY KEY, notification_id INTEGER, created_at TEXT, proccess TEXT, text TEXT, operation TEXT, operation_id INT, user_id INT, avatar TEXT, user_name TEXT, user_surname TEXT, username TEXT, paid_id TEXT, product_id TEXT, singer TEXT, dedit_from TEXT, dedit_to TEXT, cost TEXT)"
private val TABLE_OPERATIONS =
"CREATE TABLE operations(id INTEGER PRIMARY KEY, operation_id INTEGER, province TEXT, location TEXT, province_id INTEGER, location_id INTEGER, place TEXT, created_at TEXT, proccess TEXT, day TEXT, user_id INT, user_name TEXT, user_surname TEXT, username TEXT, avatar TEXT, amount_to_paid TEXT, active TEXT)"
override fun onCreate(db: SQLiteDatabase) {
// se crear las tablas necesarias
db.execSQL(TABLE_NOTIFICATIONS)
db.execSQL(TABLE_OPERATIONS)
}
override fun onUpgrade(db: SQLiteDatabase, arg1: Int, arg2: Int) {
// Se elimina la versión anterior de la bd
db.execSQL("DROP TABLE IF EXISTS notifications")
db.execSQL("DROP TABLE IF EXISTS operations")
// se crear las tablas necesarias
db.execSQL(TABLE_NOTIFICATIONS)
db.execSQL(TABLE_OPERATIONS)
}
}In Java to create our database we use the same logic; In this piece of code we see that we extend the same class in which it has the same definition but with another syntax:
public class ListSQLiteHelper extends SQLiteOpenHelper {
final private String TABLE_PEDIDO = "CREATE TABLE pedido(pedido_id INTEGER PRIMARY KEY, creado DATETIME DEFAULT CURRENT_TIMESTAMP, procesado DATETIME, cliente_id INTEGER, establecimiento_id INTEGER, mesas_establecimiento_id INTEGER, token TEXT)";
public ListSQLiteHelper(Context context, String name,
SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
// se crear las tablas necesarias
db.execSQL(TABLE_PEDIDO);
}
@Override
public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
// Se elimina la versión anterior de la bd
db.execSQL("DROP TABLE IF EXISTS pedido");
// se crear las tablas necesarias
db.execSQL(TABLE_PEDIDO);
}
}As you can see they are the same methods and therefore the same logic is applied, the onCreate method to create the database and the onUpgrade method that is executed when we are going to delete the database.
To insert a record; in this case it is a static method, which means that we can instantiate the method without having to create a new instance each time we want to use the method, in the Kotlin way, we use a companion object that offers us a similar behavior to the static ones in Java:
public static Pedido getById(int pedido_id, Context context) {
Pedido pedido = NULL;
ListSQLiteHelper taskSQL = new ListSQLiteHelper(context, TABLE_NAME,
NULL, 1);
SQLiteDatabase db = taskSQL.getReadableDatabase();
Cursor cursor = db.rawQuery("SELECT " + selectData + " WHERE "
+ ID_NAME + " = " + pedido_id, NULL);
if (cursor.moveToFirst()) {
pedido = new Pedido();
pedido.setPedido_id(cursor.getInt(0));
try {
if (cursor.getString(1) != NULL)
pedido.setCreado(dateFormat.parse(cursor.getString(1)));
} catch (ParseException e) {
e.printStackTrace();
}
try {
if (cursor.getString(2) != NULL)
pedido.setProcesado(dateFormat.parse(cursor.getString(2)));
} catch (ParseException e) {
e.printStackTrace();
}
pedido.setCliente_id(cursor.getString(3));
pedido.setEstablecimiento_id(cursor.getInt(4));
pedido.setMesas_establecimiento_id(cursor.getInt(5));
pedido.setToken(cursor.getString(6));
}
cursor.close();
db.close();
return pedido;
}From this Helper file we will have all the tables of our application, which for this example consist of two tables; As we can see, the first section is in charge of defining the structure of the table, things like the number of columns, data types and the key or primary key. This is created in the onCreate method that is executed when the database is going to be created; that is, it is only executed once (unless for some reason you need to delete the database from the Android app you are developing); then we have the onUpgrade method that is responsible for making the queries to update our database, executing the statements, etc; in this case we simply delete the tables.
Defining the data access layer (DAO)
With our database created, the next thing we have to do is define a layer that in practice is another file that allows us to use the database, that is, all our queries, record updates, insertion and deletion of the same It is done through a file that in this case would be the Notifications file. This layer allows us to abstract from all the SQL queries that we must perform; in practice it has been the way in which we can access the data, of course we can apply all these rules from our activity, fragment, adapter or other component, but it is a good idea to centralize everything that we can centralize; and this is an example of some queries that you can perform; In general, it is typical, queries to delete, get record(s), insert and update; the typical thing we do in other web systems, but in Android:
class NotificationDao {
companion object {
val TABLE_NAME = "notifications"
val ID = "notification_id"
val TEXT = "text"
val CREATED_AT = "created_at"
val OPERATION = "operation"
val OPERATION_ID = "operation_id"
val USERNAME = "username"
val USER_NAME = "user_name"
val USER_SURNAME = "user_surname"
val USER_ID = "user_id"
val AVATAR = "avatar"
val PROCCESS = "proccess"
var PAID_ID = "paid_id"
var PRODUCT_ID = "product_id"
var COST = "cost"
var SINGER = "singer"
var FROM = "dedit_from"
var TO = "dedit_to"
internal var dateFormat = SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss", Locale.getDefault()
)
private val selectData = (ID + ", " + TEXT + ", "
+ CREATED_AT + ", " + PROCCESS + ", " + USERNAME + ", " + OPERATION + ", " + OPERATION_ID + ", " + USER_ID + ", " + AVATAR + ", "
+ USER_NAME + ", " + USER_SURNAME + ", " + PAID_ID + ", " + PRODUCT_ID + ", " + COST + ", " + SINGER + ", " + FROM + ", " + TO + " FROM " + TABLE_NAME)
fun getAllByOperationId(context: Context, id: Long): ArrayList<NotificationUser> {
var notification: NotificationUser
val notifications = ArrayList<NotificationUser>()
val taskSQL = ListSQLiteHelper(
context, TABLE_NAME,
NULL, 1
)
// traigo la date de BD
val db = taskSQL.getReadableDatabase()
val cursor = db.rawQuery("SELECT $selectData WHERE $OPERATION_ID = $id", NULL)
// guardo la data en un ArrayList
if (cursor.moveToFirst()) {
do {
var i = 0
notification = packOperation(cursor)
notifications.add(notification)
} while (cursor.moveToNext())
}
cursor.close()
db.close()
return notifications
}
fun getAll(context: Context): ArrayList<NotificationUser> {
var notification: NotificationUser
val notifications = ArrayList<NotificationUser>()
val taskSQL = ListSQLiteHelper(
context, TABLE_NAME,
NULL, 1
)
// traigo la date de BD
val db = taskSQL.getReadableDatabase()
val cursor = db.rawQuery("SELECT $selectData", NULL)
// guardo la data en un ArrayList
if (cursor.moveToFirst()) {
do {
var i = 0
notification = packOperation(cursor)
notifications.add(notification)
} while (cursor.moveToNext())
}
cursor.close()
db.close()
return notifications
}
fun getByID(context: Context, id: Long): NotificationUser? {
var notification: NotificationUser? = NULL
val taskSQL = ListSQLiteHelper(
context, TABLE_NAME,
NULL, 1
)
// traigo la date de BD
val db = taskSQL.getReadableDatabase()
val cursor = db.rawQuery("SELECT $selectData WHERE $ID = $id", NULL)
// guardo la data en un ArrayList
if (cursor.moveToFirst()) {
notification = packOperation(cursor)
}
cursor.close()
db.close()
return notification
}
fun update(
contentValues: ContentValues, id: Int,
context: Context
): Long {
// abrimos la base de datos 'tour' en modo escritura
val sqlHelper = ListSQLiteHelper(
context, NotificationDao.TABLE_NAME,
NULL, 1
)
val db = sqlHelper.writableDatabase
return db.update(
NotificationDao.TABLE_NAME, contentValues, NotificationDao.ID + "=?",
arrayOf(id.toString())
).toLong()
}
fun deleteAll(
context: Context
) {
// abrimos la base de datos 'tour' en modo escritura
val sqlHelper = ListSQLiteHelper(
context, NotificationDao.TABLE_NAME,
NULL, 1
)
val db = sqlHelper.writableDatabase
db.delete(
NotificationDao.TABLE_NAME, "1",
arrayOf()
)
}
fun insert(contentValues: ContentValues, context: Context): Long {
// abrimos la base de datos en modo escritura
val sqlHelper = ListSQLiteHelper(context, TABLE_NAME, NULL, 1)
val db = sqlHelper.writableDatabase
return db.insert(TABLE_NAME, NULL, contentValues)
}
fun packOperation(cursor: Cursor): NotificationUser {
var i = 0
var notification = NotificationUser()
notification.notification_id = cursor.getInt(i)
notification.text = cursor.getString(++i)
notification.created_at = cursor.getString(++i)
notification.proccess = cursor.getString(++i)
notification.username = cursor.getString(++i)
notification.operation = cursor.getString(++i)
notification.operation_id = cursor.getInt(++i)
notification.user_id = cursor.getString(++i)
notification.avatar = cursor.getString(++i)
notification.user_name = cursor.getString(++i)
notification.user_surname = cursor.getString(++i)
notification.paid_id = cursor.getString(++i)
notification.product_id = cursor.getString(++i)
notification.cost = cursor.getString(++i)
notification.singer = cursor.getString(++i)
notification.dedit_from = cursor.getString(++i)
notification.dedit_to = cursor.getString(++i)
return notification
}
fun populateContentValue(notification: NotificationUser): ContentValues {
var contentValues = ContentValues()
contentValues.put(ID, notification.notification_id)
contentValues.put(CREATED_AT, notification.created_at)
contentValues.put(TEXT, notification.text)
contentValues.put(USERNAME, notification.username)
contentValues.put(OPERATION, notification.operation)
contentValues.put(OPERATION_ID, notification.operation_id)
contentValues.put(USER_NAME, notification.user_name)
contentValues.put(USER_SURNAME, notification.user_surname)
contentValues.put(USER_ID, notification.user_id)
contentValues.put(AVATAR, notification.avatar)
contentValues.put(PROCCESS, notification.proccess)
contentValues.put(PAID_ID, notification.paid_id)
contentValues.put(PRODUCT_ID, notification.product_id)
contentValues.put(SINGER, notification.singer)
contentValues.put(FROM, notification.dedit_from)
contentValues.put(TO, notification.dedit_to)
return contentValues
}
}
}
Analyzing the above code: SQLite database queries: Cursors
In the case of queries, as we do in the getAll(), getById() and similar methods, we use cursors in Android; the cursors are a pointer to the values obtained from a query, as you can see in the previous code and we simply move with the moveToNext() method that returns true if there is more data and false otherwise.
Analyzing the above code: insert and update in SQLite database: Cursor
The code is pretty much self-explanatory; In general rules we access the database sqlHelper = ListSQLiteHelper(context, TABLE_NAME, NULL, 1), we indicate that we want to write on it with sqlHelper.writableDatabase and then we apply the queries, insert, delete, etc; in the case of the insert we use the ContentValues
To perform an insertion or update of records we have to use the ContentValues; This type of object allows defining pairs of values, like the Pair in Kotlin, but it is used in this scenario to define the column in the database that we want to affect and its value; The ContentValues are used for both the insertion and the update, as we can see in the following code:
Insert a record into a database in SQLite:
var id = NotificationDao.insert(
NotificationDao.populateContentValue(notification),
this@NotificationActivity
)Update a record in a database in SQLite:
var contentValues = ContentValues()
contentValues.put(NotificationDao.AVATAR, noti.avatar)
contentValues.put(NotificationDao.PROCCESS, noti.proccess)
contentValues.put(NotificationDao.COST, noti.cost)
contentValues.put(NotificationDao.PAID_ID, noti.paid_id)
contentValues.put(NotificationDao.PRODUCT_ID, noti.product_id)
NotificationDao.update(contentValues, noti.notification_id, this@NotificationActivity)And that's it with the access layer of our database; In general, all the queries should be in this layer, and the activities and other components should only call this layer, so the best idea that you can apply is to create these classes as Singleton, which we do in Java by applying static and in Kotlin with the companion objects.
Using an external SQLite database in Android
In this section we will explain how to use an external SQLite database, as you well know, SQLite is not an invention in Android, it is a technology that you can freely use in other types of applications included in our web applications and as we mentioned before, a database SQLite data consists of a simple file, so we can take this file that is the product of another application and use it in Android in a 100% safe and equally practical way and that is what we will see in this other section.
As we have mentioned SQLite is the main means of persistence par excellence in Android; generally, when we work with Android, we think that the database has to be internal to the Android application itself and this is due to how an Android app is composed, which is essentially all integrated, but it doesn't have to be like that, one of the things very interesting and what identifies SQLite is its portability, that is, a SQLite database is very small and consists of a single file that contains our entire database along with its structure and therefore we can export it (copy and paste ) in our project and connect to it.
SQLite not only on Android nor Android Studio
There are many applications that use databases in SQLite from PHP itself to client-side applications with JavaScript (First steps with SQLite in JavaScript: the openDatabase, executeSql and executeSql methods), so it is very likely that at some point we decided to export this entire structure from a web or other application to an Android application created in Android Studio or other technology.
Using our SQLite database on Android with Android Studio
If we decide to import a SQLite database from another application (whether Android or not) at first we might think that it is necessary to carry out some complex process of reading the file, creating the database within the Android application and reading table by table, record by record and copy it into a database in our Android application, this is not necessary, just copy the file from our SQLite database to a specific location in our Android Studio IDE:
We must copy the database to the assets/databases directory of our project.
Now we create a class like the following:
public class ListSQLiteHelper extends SQLiteAssetHelper {
private static final String DATABASE_NAME = "main";
private static final int DATABASE_VERSION = 1;
public ListSQLiteHelper(Context context, String name,
SQLiteDatabase.CursorFactory factory, int version) {
super(context, DATABASE_NAME, context.getExternalFilesDir(NULL).getAbsolutePath(), NULL, DATABASE_VERSION);
//super(context, name, factory, version);
}
}As you can see we used the SQLiteAssetHelper method instead of the SQLiteOpenHelper method which doesn't need us to override the methods onCreate(SQLiteDatabase db) and onUpgrade(SQLiteDatabase db, int arg1, int arg2) which are used for creating and updating the database that would apply in case of being a database generated from Android itself.
The DATABASE_NAME variable contains the name of our database.
With this we have the connection to our database ready and we can perform queries on it as if it were a database natively integrated by Android.
You can check the official documentation at the following link: Android SQLiteAssetHelper.
Develop with Laravel, Django, Flask, CodeIgniter, HTML5, CSS3, MySQL, JavaScript, Vue, Android, iOS, Flutter