In Android, there are various options to store data, one of those options is using SQLite Database. SQLite is a very lightweight database which comes with Android OS. Some of the important points about SQLite are:
1. SQLite is an open source SQL database that stores data to a text file on a device. Android comes in with built-in SQLite database implementation.
2. SQLite Database has methods to create, delete, execute SQL commands, and perform other common database management tasks.
3. You don’t need to establish any kind of connections for it like JDBC, ODBC e.t.c.
In this tutorial, I’m going to discuss how to store data in SQLite Database by invoking a DbHandler class in the activity and storing and retrieving the data using a model class used to create objects of data. Well don’t be frightened by terms I used here, you are going to understand them in the coming section.
This tutorial can be divided into three major parts as represented in the diagram.
In this tutorial, I’m going to explain how to use SQLite Database, by using an example Of Record Object ( made of PRINCIPLE AMOUNT, RATE OF INTEREST and INTEREST) which will be used to store data into the database. Hence the table structure of SQLite Database is going to look like this:
Now, let’s start with the tutorial. Here I’m expecting that you know how to create a project in Android Studio and create all the activities and Java classes.
Step 1 DATABASE CREATION
First of all, we will create a class to handle database operations as follows:
- In your project, create a java file by Selecting app -> java -> Your Package Name and right-clicking on your package and selecting New -> Java Class. And name it as DbHandler.java.
- Now paste the below code in DbHandler.java.
Let’s discuss the above code step by step:
1. The DbHandler class extends SQLiteOpenHelper class which is a helper class to manage database creation and version management.
2. When you make an object of SQLiteHelper class, the Constructor calls super class constructor and creates the SQLite database.
3. SQLiteOpenHelper class has two abstract methods that you necessary to override in SQLiteHelper class.
onCreate() is only run when the database file did not exist and was just created. If
onCreate()returns successfully (doesn’t throw an exception), the database is assumed to be created with the requested version number. This is where the creation of tables should happen. If you wish to insert records at the time of creating tables, even the code to insert the records should be written here.
db.execSQL(): Execute a single SQL statement that is NOT a SELECT or any other SQL statement that returns data.
onUpgrade() is only called when the database file exists but the stored version number is lower than requested in the constructor. The
onUpgrade() should update the table schema to the requested version. Basically, onUpgrade() method is used when you place a new version of your app ( with updated database tables schema) for the users. When changing the table schema in code (
onCreate()), you should make sure the database is updated. The approach used here is :
- Increment the database version so that
onUpgrade()is invoked. For development time schema upgrades where data loss is not an issue, you can just use
execSQL("DROP TABLE IF EXISTS <tablename>")to remove your existing tables and call
onCreate()to recreate the database.
NOTE: For released versions, you should implement data migration in
onUpgrade() so your users don’t lose their data.
|oldVersion||The Old Database Version|
|newVersion||The New Database Version|
There one more method in SQLiteOpenHelper class ( onOpen() ) which is optional to override. It is called when the database has been opened. The implementation should check isReadOnly() before updating the database.
- SQLiteOpenHelper’s onCreate() and onUpgrade() callbacks are invoked when the database is actually opened, for example by a call to getWritabledatabase(). The database is not opened when the database helper object itself is created.
- SQLiteOpenHelper versions the database files. The version number is the int argument passed to the constructor. In the database file, the version number is stored in PRAGMA user_version.
Now before writing the methods for CRUD operations (Create, Read, Update and Delete), we need to define a model class (Record class) for the manipulation of data.
Step 2 CREATION OF MODEL RECORD CLASS
This model class will be used for the manipulation of data. Model is kind of skeleton of the data we want to use.
1. So let’s create a model class by navigating to app -> java -> Your Package Name and right-clicking on your package and selecting New -> Java Class. And name it as Record.java.
2. We need following three variables for the Record:
3. We will define constructors for the creation of Record Object and getter & setter methods for all the three variables.
4. Now the paste the below code in Record.java.
Step 3 WRITING METHODS FOR CRUD OPERATIONS
Now we will write methods for different CRUD ( Create, Read, Update, Delete) operations. We will be writing methods in following order for RECORDS table:
- Adding New Record
- Reading a Record
- Reading All Records
- Update a Record
- Delete a Record
- Getting Total Count Of Records
ADDING A RECORD
READING A RECORD
READING ALL RECORDS
UPDATING A RECORD
DELETING A RECORD
GETTING TOTAL COUNT OF RECORDS
- I have assumed Principle Amount as a unique identifier in above methods. You can assume that by your choice.
- I have only shown a mere representation of how different CRUD operations are implemented in a database. It is upon you, how you want to implement it.
- A cursor is used to fetch rows(data) from a table.
You must have puzzled by different commands used above to interact with the table in the database such as “where to use rawQuery , where to use Simple query or where to use Cursor ” and why null values are provided as arguments. All the commands used above are in accordance with the documentation provided by android. I have added a screenshot of different SQLiteDatabase commands below and for the complete reference, you can refer to Android Documentation page.
Step 4 USAGE OF ALL CLASSES IN ACTIVITY CLASS
Now we will instantiate an object of DbHandler class in activity class(the activity in which Database operation is to be performed) and will implement different database operations.
Paste the below code (by making changes according to your app) in your activity class to get your app working.
- I have not used any UI to display the RECORDS, so either you can use logcat to see the records in logcat screen in ANDROID STUDIO or I will be coming with my next post on using CardView in which you can see how data is fetched from a database into a CardView.
- If you find anything wrong, please do comment.