In the previous tutorial, you have learned about Android Application Resources. In this tutorial, you will learn about SQLite database in Android.
SQL
SQL stands for Structured Query Language. SQL is the language designed to manage relational databases. You can use SQL to Select, Insert, Update and Delete operations in the database.
SQLite
SQLite is a software library for Android, which is used to connect your Android application with database. SQLite implements transactional SQL database. SQLite is a serverless library which is self-contained and the most important this is that it is serverless. It requires very minimal support with the external environment. Using SQLite, you can directly read and write the data from the database on disk. You don’t need to install anything to use SQLite database before using it. The SQLite database is efficient enough to roll back if something goes wrong during the transaction.
SQLite Requirements
There are no specific requirements or prerequisites for Android devices to use Android applications with the database. Also, there are no specific requirements for Android Studio to build and compile Applications with the database.
SQLite Supported Data Types
In SQLite, you have following datatypes.
- Text (As String in Java)
- Integer (As long in Java)
- Real (As double in Java)
All the other data types must be converted into the data types mentioned above types. SQLite does not validate the data types you specify i.e. If you put Integer in Text column SQLite will not validate the data type.
SQLite and Android
Android provides full support for SQLite. Any database you create will be accessible by name in any class of the application, but you can’t access the database outside the application. In Android project only thing you need to import is SQLite, this will be the only API you must import to use SQLite.
Package for SQLite
| 1 | android.database.sqlite |
Directory Path of SQLite in Android Device
/data/data//databases
How to Use Android SQLite Database
You can use SQLite in any Android application by defining the schema for the database and subclass of SQLiteOpenHelper.
SQLite Database Schema
The database schema is the formal declaration of structure for the database. SQLite database schema tells that how the database is organized.
SQLiteOpenHelper
You must create SQLite database under the subclass of SQLiteOpenHelper class. All the logic behind CRUD operations in SQLite databases is defined in SQLiteOpenHelper. Use SQLiteOpenHelper for the creation and the version management of SQLite database. Read the complete documentation of SQLiteOpenHandler.
Define SQLite Database Schema
SQLite database schema is reflected from the SQL statements which you will use to create SQLite database. You can specify the structure of your database in a supporting database contract file. Database contract file explicitly specifies the layout of SQLite database. A database contract contains database name with the names of the database tables and its columns. The concept of using database helper is to separate the schema of SQLite database from your logical Android application code.
Advantages of Using Separate Database Contract File
- The database contract class allows you to use same constants throughout the project within the same package. You can change the name of any column and helps to maintain the changed name all over the code within the same package.
- You can keep all the global definitions of the database which remain same, in the contract class. Place an inner class which contains the column definitions of the table.
Contract Class
By implementing the base columns for the table, your inner class can also inherit a primary key field called _ID that some Android cursor adapter classes expect. _ID is not always required, but it helps your Android application to run harmoniously.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | public final class DatabaseContract { // Contract class must be final so to prevent someone from accidentally instantiating the contract class, public DatabaseContract() { } /*Create inner class that defines the table contents */ public static abstract class Users implements BaseColumns { public static final String TABLE_NAME = "users"; public static final String COL_FULLNAME = "fullname"; public static final String COL_EMAIL = "email"; } } |
Create SQLite Database
Once you have defined structure of the database, you can now implement methods to create and maintain the database you created. There are a useful set of APIs available in SQLiteOpenHelper.
To use SQLiteOpenHelper (database helper), create a subclass of SQLiteOpenHelper and override onCreate(), onUpgrade(), onOpen(), and onDowngrade() callback methods.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | public class DatabaseHelper extends SQLiteOpenHelper { public static final int DATABASE_VERSION = 1; public static final String DATABASE_NAME = "EmailUser.db"; public DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { //this method is only called when the database is created // TODO Auto-generated method stub } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // TODO Auto-generated method stub } } |
Using SQLiteOpenHelper to Create Database and Tables
| 1 2 3 4 5 6 7 8 9 10 11 | public class DatabaseHelper extends SQLiteOpenHelper { public static final int DATABASE_VERSION = 1; //Database Version public static final String DATABASE_NAME = "EmailUser.db"; //Database Name private static final String CREATE_TBL_USERS; //query to create table CREATE_TBL_USERS = "CREATE TABLE " + Users.TABLE_NAME + " (" + Users._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + Users.COL_FULLNAME + " TEXT NOT NULL, " + Users.COL_EMAIL + " TEXT)"; public DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); //calling the instance of the super class SQLiteOpenHelper } } |
onCreate()
Add Tables by Executing SQL Using onCreate()
In overridden public void onCreate(SQLiteDatabase DB) method execute the query to create a table.
| 1 2 3 4 | public void onCreate(SQLiteDatabase DB) { DB.execSQL(CREATE_TBL_USERS); //private static final String CREATE_TBL_USERS } |
onUpgrade()
The onUpgrade() method is used when you want to update your database. You must indicate the Android System that database has been upgraded by changing the Android version.
Alter Table
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | public class DatabaseHelper extends SQLiteOpenHelper { public static final int DATABASE_VERSION = 1; public static final String DATABASE_NAME = "EmailUser.db"; private static final String CREATE_TBL_USERS; CREATE_TBL_USERS = "CREATE TABLE " + Users.TABLE_NAME + " (" + Users._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + Users.COL_FULLNAME + " TEXT NOT NULL, " + Users.COL_EMAIL + " TEXT)"; private static final String ALTER_TBL_USERS = "ALTER TABLE " + Users.TABLE_NAME + " ADD COLUMN " + Users.COL_TELEPHONE + " TEXT; //Alter table code private static final String ALTER_TBL_USERS; ALTER_TBL_USERS = "ALTER TABLE " + Users.TABLE_NAME + " ADD COLUMN " + Users.COL_TELEPHONE + " TEXT; //Alter table code } |
Now execute the alter table query in onCreate() method.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | public class DatabaseHelper extends SQLiteOpenHelper { @Override public void onCreate(SQLiteDatabase db) { db.execSQL(CREATE_TBL_USERS); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { if (oldVersion<2) { db.execSQL(ALTER_TBL_USERS); } } } |
Access Database in Other Classes
To access the previously created database, you must instantiate the subclass of SQLiteOpenHelper, which you created before.
| 1 | DatabaseHelper dbHelper = new DatabaseHelper(getContext()); |
Get Data from Database
To get data from the database you just need two methods, i.e. getWritableDatabase() or getReadableDatabase() methods of your subclass instance (dbHelper).
getWritableDatabase()
| 1 | SQLiteDatabase db = dbHelper.getWritableDatabase(); |
getReadableDatabase()
| 1 | SQLiteDatabase db = dbHelper.getReadableDatabase(); |
Insert Data in the Database
When you want to insert data in the database you can use insert method with three parameters, table of type String, nullColumnHack of type String and values of type ContentValues.
Parameters
table is the name of the table to insert rows into.
nullColumnHack is not the necessary field to insert values, you can put null, SQL does not allow to insert an empty row, you must at least place one column name. If you provide empty values, with no column names, then no row will be inserted in the database. If the values are not set to null, the nullColumnHack parameter provides the name of the nullable column name to explicitly insert a NULL into in the case where your values are empty.
Values contain the initial column values for the row. The keys should be the column names, and the values are the column values.
Return Values
The return value is the row ID of the newly inserted row. It also returns -1 if any error occurs.
Syntax
| 1 | public long insert (String table, String nullColumnHack, ContentValues values) |
Adding a Record in SQLite Database
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | String name="Jhon Doe"; String email="jhondoe@example.com"; SQLiteDatabase db = dbHelper.getWritableDatabase(); // Create a new map of values, where column names are the keys ContentValues values = new ContentValues(); values.put(Users.COL_FULLNAME, name); values.put(Users.COL_EMAIL, email); long newRowId = db.insert(Users.TABLE_NAME, null, values); //insert data in database if (newRowId>0) { Toast.makeText(this, "New Record Inserted: " + newRowId, Toast.LENGTH_SHORT).show(); } db.close(); // Closing database connection |
Database Query
A database query is a method to query the given table, returning a Cursor over the result set. A cursor object is positioned before the first entry.
Syntax
| 1 | public Cursor query (String table, String[] columns, String whereClause, String[] whereArgs, String groupBy, String having, String orderBy) |
Get Record from SQLite
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | List<String> usersList = new ArrayList<String>(); //String arraylist to store user record SQLiteDatabase db = dbHelper.getReadableDatabase(); //readable databse from SQLite database String[] columns = { Users._ID, Users.COL_FULLNAME, Users.COL_EMAIL }; //specifying column names String sortOrder = Users.COL_FULLNAME + " ASC"; Cursor c = db.query(Users.TABLE_NAME, columns, null, null, null, null, sortOrder); //cursor object and query while (c.moveToNext()) { usersList.add(c.getString(0) + ": " + c.getString(1) + "("+ c.getString(2) +")"); //adding records } c.close(); //close cursor db.close(); //close database connection |
Use Adapter to Set Records in ListView
All activities related to the views from the database must be performed after closing the cursor and before closing the database connection.
Read more about Adapters and How to set data in a View using ArrayAdapter.
| 1 2 3 4 5 6 7 8 9 10 | c.close(); //close cursor ListView listview=(ListView) findViewById(R.id.listView1); ArrayAdapter<String> adpter; //Array Adapter adapter = new ArrayAdapter<String>(this,android.R.layout.simple_list_item_1, usersList); listview.setAdapter(adapter); db.close(); //close database connection |
OnItemClickListener() for ListView Item Click
| 1 2 3 4 5 6 7 8 9 10 11 12 13 | c.close(); //close the cursor listview.setOnItemClickListener(new OnItemClickListener() { public void onItemClick(AdapterView<?> parent, View v, int position, long id) { TextView txt=(TextView) v; int sep = txt.getText().toString().indexOf(":"); String rid = txt.getText().toString().substring(0, sep); ManageUser(rid); } }); db.close();//close database connection |
Start a new Activity on Item Click
ManageUser Method
| 1 2 3 4 5 6 7 8 9 | public void ManageUser(String id) { Toast.makeText(this, "Manage Record: " + id, Toast.LENGTH_SHORT).show(); Intent intent=new Intent(this, ManageUserActivity.class); intent.putExtra("recordID",id); startActivity(intent); finish(); } |
Get Single Records from SQLite Database
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | public DatabaseHelper dbHelper; //database helper object public String rid; //string to store record ID public EditText fullname, email; //Edit text for full name protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_manage_user); //set content view to manage user activity Intent intent=getIntent(); rid=intent.getStringExtra("recordID”); dbHelper=new DatabaseHelper(this); SQLiteDatabase db = dbHelper.getReadableDatabase(); } String[] columns = { Users._ID, Users.COL_FULLNAME, Users.COL_EMAIL }; Cursor c = db.query(Users.TABLE_NAME, columns, Users._ID + " = ?", new String[] { rid }, null, null, null, null); fullname=(EditText) findViewById(R.id.editFullName); email=(EditText) findViewById(R.id.editEmail); if (c != null) { c.moveToFirst(); fullname.setText(c.getString(1)); email.setText(c.getString(2)); } c.close(); db.close(); |
Delete Data from SQLite Database
Use delete() method to delete rows from SQLite database with three parameters i.e. table as String, whereClause as String and whereArgs as String array.
Parameters
table to delete data from.
whereClause is optional when deleting the data. passing null values will delete the entire data from all the rows along with the 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 the Strings.
Return Value
The delete() method returns the number of rows affected if the where clause passes. Otherwise, it returns 0.
| 1 | public int delete (String table, String whereClause, String[] whereArgs) |
Delete Record Method
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | public void DeleteRecord(View view) { SQLiteDatabase db = dbHelper.getWritableDatabase(); //SQLite get writable database // Define 'where' part of query. String whereClause = Users._ID + " = ?"; // Specify arguments in placeholder order. String[] whereArgs = { id }; // Issue SQL statement. db.delete(Users.TABLE_NAME, whereClause, whereArgs); Toast.makeText(this, "Record Deleted: " + id, Toast.LENGTH_SHORT).show(); Intent intent=new Intent(this, UsersListActivity.class); startActivity(intent); finish(); } |
Update Record in SQLite Database
The update() method is used to update record in SQLite database. This method contains four parameters i.e. table of type String, values of type ContentValues, whereClause of type String and whereArgs of type String array.
Parameters
table is the name of the table to update data.
values are the map from column names to new column values. null is a valid value that is be translated to NULL at the time of updating.
whereClause is the optional WHERE clause to apply when updating. Passing null will update all the rows in the table.You can also include ?s in the where clause, which will be replaced by the values from whereArgs. The values will be bound as Strings.
Return Values
The update() method returns the number of rows affected.
Update Record Method
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | public void DeleteRecord(View view) { SQLiteDatabase db = dbHelper.getWritableDatabase(); //get writable database by using databaseHelper // Define 'where' part of query. String whereClause = Users._ID + " = ?"; // Specify arguments in placeholder order. String[] whereArgs = { id }; // Issue SQL statement. db.delete(Users.TABLE_NAME, whereClause, whereArgs); Toast.makeText(this, "Record Deleted: " + id, Toast.LENGTH_SHORT).show(); Intent intent=new Intent(this, UsersListActivity.class); startActivity(intent); finish(); } |
Summary
Define a Schema by using a Contract class also create an inner class (subclass of BaseColumns) for each table.
Create SQLite database using the subclass of SQLiteOpenHelper class.
Implment onCreate() and/or onUpgrade() methods to CREATE, UPDATE, DELETE, SELECT, etc. operations on SQLite database by using an instance of SQLiteDatabase class.
Get SQLiteDatabase object using getWritableDatabase() or getReadableDatabase() methods to perform CRUD operations.
Learn more about SQLite database.
Hope you like this tutorial, Stay tuned for more upcoming tutorials. Stay Blessed!
Don’t forget to give your feedback through comments.