Android comes in with built in SQLite database, which is an opensource relational database that can be used to perform database operations on android devices such as storing, manipulating or retrieving persistent data from the database.
In this tutorial, we are going to discuss following topics:
I used _id as primary key because when you use CursorAdapter, by default, it looks _id as primary key.
The corresponding Customer class for above table structure is as follows:Customer.java
I Written a healper class named ReturnMessage to deliver a message whether an operation was successful or failed.ReturnMessage.java
The android.database.sqlite.SQLiteOpenHelper class is used for database creation and version management. To perform any database operation, you have to write a class that extends SQLiteOpenHelper class and implement onCreate() and onUpgrade() methods of SQLiteOpenHelper class.
Write a class named SqliteHelper and extends android.database.sqlite.SQLiteOpenHelper class. Now we need to write a constructor that call super constructor and need to provide implementation of onCreate() and onUpgrade() methods.SqliteHelper.java
Now let's discuss each method one by one:
SqliteHelper(Context context) : this is constructor of class that calls super class constructor from its body.
onCreate(SQLiteDatabase sqLiteDatabase) : This method is called only once in its life time. So, in this method we perform all table creation task
onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) : This method is called when database version upgrade. Suppose in an live app our database has 5 tables, now we want to add a new table and want to alter few columns of old table.
For that, first we need to upgrade database version, that will force system to call onUpgrade method. Now, within onUpgrade method we will write create query to add a new table and alter query to alter the columns of old table.
getCustomerDao : For clear visibility I prefered to write a separate class CustomerDao that is responsible for handling all customer related operation.
getObjectFromCursor : This is utility method which accept cursor and class as parameter. It is responsible for processing cursor and return corresponding object.
This class is responsible for handling all customer related operations like create customer table, insert, update, delete and query customers.CustomerDao.java
createCustomerTable method of CustomerDao class is responsible for creating customer table. It is called from onCreate method of SqlHelper class only once in its life time. COL_ID, COL_FIRST_NAME, COL_LAST_NAME and TABLE_NAME are constant declared in CustomerDao class.
addCustomer method of CustomerDao is responsible for adding a customer and return an object of ReturnMessage so that caller can check whether operation was successful or not.
ContentValues is a name value pair, that is used to insert or update values into database tables. ContentValues object need to be passed to insert() and update() method of SQLiteDataBase objects.
Here we are creating ContentValues object from Customer object and passing it to insert method of SQLiteDataBase objects.
updateCustomer method of CustomerDao is responsible for updating a customer. Source of updateCustomer is given below:
deleteCustomer method of CustomerDao is used to delete a customer row from the customer table. Source of deleteCustomer method is given below:
getCustomers method of CustomerDao is responsible for querying the customers based on customer object passed as parameter and return Cursor object.
Cursor is a class which represents a 2 dimensional table of any database. When you try to retrieve some data using SELECT statement, then the database will first create a Cursor object and return its reference to you.
The pointer of this returned reference is pointing to the 0th location which is otherwise called as before first location of the Cursor, so when you want to retrive data from the cursor, you have to first move to the first record so we have to use moveToFirst
There are 3 easy step to populate a ListView with CursorAdapter:
Initialize the custom CursorAdapter object, and set that object as adapter of ListView.
Create a layout resource xml and define how row of listView will look like:
This is how customer row looks like:
Now we create a class named CustomerCursorAdapter that extends CursorAdapter and override bindView method of cursor adapter. Source of CustomerCursorAdapter class is given below:
Now, last step is to populate a ListView from cursor is given below:
We have already discussed all major points that was required to develop a Sqlite sample which perform insert, update, delete and query operation, and populate a Custom ListView using CursorAdapter.
So, lets develop a demo that demonstrate all the discussed points.
Step 1: Create a new project in Android Studio and name it Android Sqlite.
Step 2: Open res -> values -> strings.xml and add following string resource
Step 3: Open res -> layout -> activity_main.xml and add following code
Step 4: Open res -> layout -> customer_layout.xml and add following code
Step 5: Create a class named Customer.java and replace its code with below given code
Step 6: Create a class named CustomerCursorAdapter.java and replace its code with below given code
Step 7: Create a class named CustomerDao.java and replace its code with below given code
Step 8: Create a class named SqliteHelper.java and replace its code with below given code
Step 9: Add below given code to your MainActivity.java
Step 10: Create a class ReturnMessage.java and replace its code with below given code
Step 11: Clean and run the project to see the result.