SQLite Database with Android

What is SQLite?

SQLite is an open source relational database, that is used to perform database operations on android device, such as Insert, Update, Delete and Retrieve.

It is a part of Data Persistence in Android, that means even if you close your app; whenever you open your app again the data will be there.

SQLite Database is embedded within Android, there is no need to install any third party applications or library.

Now, SQLite won't show you the data same way as MySQL. If you want to show your data in tabular manner just like MySQL, you'll have to install something called DB Browser. That gives support for viewing data in tabular format. For that you'll need an sql file. How to get that lets see.

First, look at Right Side in the bottom in Android Studio. You will see Device File Explorer. Go to this location;

data -> data -> "your application package name" -> databases -> your .sql file.

Now, simply download that file and open it in DB Browser. This is something JVS showed us in the class.

SQLiteOpenHelper This class is used for Database Creation and Version Management in Android. In order to perform any database related tasks you must provide an implementation of onCreate() and onUpgrade() methods of this class.

SQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version)

Constructor of class:

Context: Represents the context of the application.

Name: Represents the Name of Database.

Version: Represents the Version of Database.

Factory: Usually represented as Null, it has more significance when you host your app.

Methods of SQLiteOpenHelper

1. onCreate(SQLiteDatabase db): Called only once when database is created for the first time.

2. onUpgrade(SQLiteDatabase db, int oldV, int newV): Called when database needs to be upgraded.

3. close(): Closes the database object.

4. onDowngrade(same as 2nd): Called when database needs to be downgraded.

Let's see how we can implement SQLiteOpenHelper class in Android

public class database extends SQLiteOpenHelper {
 private static final String
DB_NAME="tushar.db";
 public database(@Nullable Context context) {
 super(context,DB_NAME, null, 1);
 }
 @Override
 public void onCreate(SQLiteDatabase sqLiteDatabase) {
 String q="Create Table login1(id integer primary
key autoincrement, name text, username text, password
text);";
 sqLiteDatabase.execSQL(q);
 // String q="Insert.....";
 //sqLiteDatabase.execSQL(q);
 }
 @Override
 public void onUpgrade(SQLiteDatabase sqLiteDatabase,
int i, int i1) {
 sqLiteDatabase.execSQL("drop table if exists
login1");
 onCreate(sqLiteDatabase);
 }

Create Database.java file which contains the class, that class inherits the properties of SQLiteOpenHelper. Create one final variable which holds the name of our Database.

When our database is created for the first time, onCreate() method will be called. In here we should implement those things which are needed to be done only once, like creation of table.

Look at the example.

@Override
 public void onCreate(SQLiteDatabase sqLiteDatabase) {
 String q="Create Table login1(id integer primary
key autoincrement, name text, username text, password
text);";
 sqLiteDatabase.execSQL(q);
 }

q variable contains our query, and execSQL is used for executing that query. This will be called only once. And if you wish to upgrade your database, onUpgrade() method should be called, but you should take the backup of your database first.

Types of Database in SQLite

1. getWritableDatabase(): This database is used when you want to make changes in your database, such as say, Inserting Data, Updating Data and then Deleting Data. Allows you to make changes.

2. getReadableDatabase(): Used when you want to retrieve data from the database.

Now here today, we will see only how to Insert and Read data from the database. We are skipping update and delete for now.

Implementing Insert method in our class. i.e Database.java file, where we wrote code for SQLiteOpenHelper class

public boolean insert_data(String name, String uname,
String pwd){
 SQLiteDatabase db= this.getWritableDatabase();
 ContentValues c= new ContentValues();
 c.put("name",name);
 c.put("username",uname);
 c.put("password", pwd);
 long r=db.insert("login1", null, c);
 if (r==-1)
 return false;
 else
 return true;
 }

insert_data() method contains three parameters, which will be passed by the user.

First of all, we are getting the writeable database because we want to make changes in the database.

Then we are using something called ContentValues which will group our data into the one bundle.

db.insert() function is used to store the data into database.

First parameter is name of the table, second parameter god knows what the heck it is and third parameter is the Bundle we created using ContentValues.

If method was not successful in inserting data it will return -1, so we can write if conditions in that case.

Upon finishing all the work, do not forget to close the database.

But how can we actually call this function?

In your MainActivity.java file, create the instance of the database class which resides inside Database.java file.

Let's say;

database g= new database(this);

And then call;

check=g.insert_data("tushar", "tush", "12344");

Easy right?

Let's see for Retrieving the data as well.

Create method in Database.java file and in database class, named "retrieve()"

public Cursor retrieve() {
 SQLiteDatabase db = this.getReadableDatabase();
 Cursor cursor = db.rawQuery("select * from
login1", null);
 return cursor;
 }
}

First, we are getting the readable database. Because we want to get the data from database.

db.rawQuery is used only for "Reading data from DB" purposes, in other case we have respected methods for each things, such as Insert, Update, Delete and etc.

This returns cursor, which contains the data it retrieved from the database. We can use this cursor in our MainActivity.java file and data can be represented to users.

Cursor cData=g.retrieve();

remember, "g" is the instance we created earlier to access the methods of database class.

cData cursor has all the data of the login1 table, now lets see how we can show that to users.

if (cData.getCount()<0){
 Toast.makeText(MainActivity.this, "No
record found", Toast.LENGTH_LONG).show();
 }
 else {
 StringBuffer buffer=new StringBuffer();
while (cData.moveToNext()) {
 buffer.append("Id: " +
cData.getString(0)+ "\n");
 buffer.append("Name: " +
cData.getString(1)+ "\n");
 buffer.append("User Name: " +
cData.getString(2)+ "\n");
 buffer.append("Password : " +
cData.getString(3)+ "\n\n");
 }
 AlertDialog.Builder builder = new
AlertDialog.Builder(MainActivity.this);
 builder.setCancelable(true);
builder.setTitle("User Entries.....");
builder.setMessage(buffer.toString());
builder.show();
 } 
}

If there is no data in the cursor, first condition will be true and nothing will be show. Otherwise;

while (cData.moveToNext()) is used for iterating through cursor one by one, and we are adding those data into the StringBuffer. And later those data we are displaying in AlertDialog box.

This is how you can Insert data and Retrieve data from the database. I hope this article helps you.