The Complete Magazine on Open Source

Android Application Development: Playing with SQLite Database

, / 890 63

Android application development

In this article, I will focus on building an Android app using an SQLite database; I assume readers are familiar with the basics of database operations and concepts. Here, we will also be using some threaded programming in Java, so I’m assuming readers are familiar with this concept.

The SQLite database is used in Android for storing and managing application data. The SQLite database is an open source lightweight transaction SQL database engine. There are other means of storing application data in Android rather than a database (they are known as Preferences), but those are more often used to store a very small amount of information, like usernames and passwords, so that users are not prompted for their credentials every time the application is started.

In this article, I will develop a simple log-in application, which stores user details in the database, and performs basic DDL (Data Definition Language, e.g., creation of tables, etc); DML (Data Manipulation Language, e.g., insert, delete, etc) transactions on the database. In this application, I have dealt with creating a user interface too: I have added a splash screen for the application, and navigation within the application is altered to enhance usability. Hence, this article also takes you to the next level of designing a user interface for your application.

Let’s get started with the design of the application (see Figures 1 and 2).

Design of our simple login application

Figure 1: Design of our simple login application

Table design

Figure 2: Table design

We will create a database that holds the credentials of all the users. There are two types of user log-ins:

  1. User login: Whenever someone tries to log in to the application, we query the credentials entered by that person against our database. If the entered credentials are correct, then we allow the person to log in, otherwise we display a suitable message.
  2. Admin login: We also allow the admin to see the credentials of all the registered users. The admin user account is created during the installation of the application.

Now, let’s get started with the implementation of the application. The completed Android application project will look like what’s shown in Figure 3.

Folder structure

Figure 3: Folder structure

We start with the creation of a splash screen for our application. Here, we will be using some threaded programming, as stated earlier.


try {
    int waited = 0;
    while(_active && (waited < _splashTime)){
        if(_active) {
            waited += 100;
} catch(InterruptedException e) {
    // do nothing
} finally {
    startActivity(new Intent(SplashScreen.this, DatabaseActivity.class));

The above snippet, from the “run” method, is the heart of a threaded program in Java, where all the work associated with a thread is done. The motive of this thread is to delay the functioning of the main thread for 5 seconds, and then return back to the main thread, which takes you to the log-in page of the application.

Thus, we create a splash screen. We set the theme of this activity as one of the built-in themes —, and set the layout of the activity to our custom-made layout, which is R.layout.splashscreen.

<?xml version="1.0" encoding="utf-8"?>

      android:text="A Simple Login Example Using Database"

In this layout, we have an image and text, which we display at the launch of the application for 5 seconds. The only new Android property you see from the previous article is android:scaleType; which is used while scaling the image. If the screen size changes, then the image will be scaled, always keeping it in the centre, so that the splash screen layout is always at the centre — even for different sizes of screen.

When the splash screen activity is completed, we fire an Intent to launch another activity called DatabaseActivity, which takes you to the log-in page. See examples of the splash screen and log-in page in Figures 4 and 5.

Splash screen

Figure 4: Splash screen

Login page

Figure 5: Login page

DatabaseActivity involves taking in the values, validating the input, and querying the database. The full code of this application can be found in my Github repository. I have already explained in my first article about Basic Android Application Development– how to take inputs and create layouts. Therefore, I will concentrate more on the core concept rather than on the UI and basic validations.

In this activity, we set the layout to main.xml, as shown in Figure 5; we write the basic code to get inputs from the EditBoxes for usernames and passwords. We also write code for the basic validation for null strings, as we had discussed in my first article. The main validation that is required here is that of the username and password against the database. I have written a method called validateLogin that does the task.

Button mLogin;
Button mNewUser;
Button mShowAll;
EditText mUsername;
EditText mPassword;
DbHelper mydb = null;

These are the instances that we will be using in this Activity class to accomplish our objective. I created the DBHelper class to create and upgrade the database. We will discuss DBHelper in detail, as we move ahead.

public boolean validateLogin(String uname, String pass, Context context) {
mydb = new DbHelper(context);
SQLiteDatabase db = mydb.getReadableDatabase();
String[] columns = {"_id"};
//WHERE clause
String selection = "username=? AND password=?";
//WHERE clause arguments
String[] selectionArgs = {uname,pass};
Cursor cursor = null;
    //SELECT _id FROM login WHERE username=uname AND password=pass
    cursor = db.query(DbHelper.SAKET_TABLE_NAME, columns, selection, selectionArgs, null, null, null);
} catch(Exception e){
int numberOfRows = cursor.getCount();
if(numberOfRows <= 0){
    Toast.makeText(getApplicationContext(), "Login Failed..\nTry Again", Toast.LENGTH_SHORT).show();
    return false;
    return true;

In this method, we will be using the built-in database APIs offered by Android to perform database operations. First, we need to create an instance of a class called DBHelper, to connect to our database, so that we can use it. To operate on a database, we need to follow the sequence given below:

  • Open a database
  • Read/write operations on a database
  • Commit
  • Close the database.

Following the same order, we open the database using the getReadableDatabase() or getWritableDatabase() methods, based on the required mode of operation. We can use a generic SQLiteDatabase instance, which is returned by the getReadableDatabase() method. Now that we have the database object, we need to perform a query on it to validate the user credentials in the database. Thus, we need to code the query as follows:

public Cursor query (String table,
String[] columns,
String selection,
String[] selectionArgs,
String groupBy,
String having,
String orderBy

These variables are for the following purposes:

  • columns — the columns that are requested in the SELECT statement.
  • selection — the WHERE clause of the statement.
  • selectionArgs — the values of the place-holders in the selection string.
  • groupBy — the SQL GROUP BY clause.
  • orderBy — the SQL ORDER BY clause.

Our query hence becomes what’s shown below:

selectionArgs, null, null, null);

    columns = _id
    selection = username=? AND password=?
    selectionArgs = uname,pass

In our query, we selected the table column _ID from the table SAKET_TABLE_NAME, which is the table I have created to store the user credentials. The selection parameter has the WHERE clause (the condition of the query that needs to be verified with the database); selectionArgs has the values of the place-holders in the selection parameter.

This query returns a CURSOR — a collection of data returned by the query. CURSOR is a database construct used to hold values when a query returns more than one row of data. It needs to be opened first, before you can issue a FETCH command to get the contents of the cursor, and last of all, you need to close the cursor. The task of managing a cursor is done by the API startManagingCursor(cursorname). This opens and closes the cursor.

After getting the cursor, we can get the data from the cursor using its built-in methods, such as:

  • getCount() — the count of rows returned by the query
  • getXXX(int columnIndex) — based on the data type of the column, you can retrieve the data. For example, getInt(int columnIndex), getDouble, getString, etc.
  • getColumnIndex(String columnName) — Returns you the column index for the named column.

Using these API methods, we can retrieve the data. In this scenario, usernames are assumed to be unique; if the username exists in the table, then the getCount() method should return only one row. If the row count is 0, that means that there is no such user record; then, we Toastthe user with the appropriate message. Now, when the users’ credentials are verified, they are taken to a ‘logged-in’ page, as shown below.

Logged in page

Figure 6: Logged in page

NewUserActivity is the activity that registers a new user. In this activity, we demonstrate how to insert a row of data in the database. When users click on the New User button, they are taken to the New User page. In this page, the users need to enter the new username, password, and email address. When that is done, they need to click the Registerbutton, as shown in Figure 7.

New user page

Figure 7: New user page

The Register button invokes a method addEntry() in the NewUserActivity class, which adds the entries of the users in the database, thus registering them.

public void addEntry(String uname, String pass, String email){
    SQLiteDatabase db = myDb.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put("username", uname);
    values.put("password", pass);
    values.put("email", email);

        db.insert(DbHelper.SAKET_TABLE_NAME, null, values);
        Toast.makeText(getApplicationContext(), "User Registered", Toast.LENGTH_SHORT).show();
    } catch(Exception e){

Here, we open the database for writing, by invoking the getWritableDatabase() method. In Android, the values that need to be inserted are entered using ContentValues. An instance of this class is passed in the INSERT statement, whose syntax is as follows:

public long insert (String table, String nullColumnHack, ContentValues values)

Here, the parameters are as follows:

  • table — table name
  • nullColumnHack — SQL doesn’t allow inserting a completely empty row, so if initialValues is empty, this column will explicitly be assigned a NULL value.
  • ContentValues — Holds the values that the INSERT statement seeks to insert in the database.

DBHelper is the class that creates the database, and it can also be configured to upgrade the database (if, in future, the developer changes the database design in later versions of the application).

public class DbHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "saket.db";
    private static final int DATABASE_VERSION = 1;
    public static final String SAKET_TABLE_NAME = "login";
    private static final String SAKET_TABLE_CREATE =
                    "CREATE TABLE " + SAKET_TABLE_NAME + "(" +
                    "_id INTEGER PRIMARY KEY AUTOINCREMENT,"+
                    "username TEXT NOT NULL, password TEXT NOT NULL, email             TEXT NOT NULL);";
    private static final String SAKET_DB_ADMIN = "INSERT INTO         "+SAKET_TABLE_NAME+"values(1, admin, password,;";

    public DbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        System.out.println("In constructor");

    // (non-Javadoc)
    // @see android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite.SQLiteDatabase)
    public void onCreate(SQLiteDatabase db) {        
            //Create Database

            //create admin account
            System.out.println("In onCreate");
        } catch(Exception e) {

    // (non-Javadoc)
    // @see android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite.SQLiteDatabase, int, int)
    public void onUpgrade(SQLiteDatabase arg0, int oldVersion, int newVersion) {


In this class, we create the database in the onCreate() method. This method is invoked when the application is installed on the device. The onUpgrade() method is invoked when there is an existing database, and if there are some changes that need to be done to it.

In Android, to use SQLiteDatabase, we need to extend the SQLiteOpenHelper class. It is mandatory to have a constructor, and it is also mandatory to have DATABASE NAME and a DATABASE VERSION that need to be passed to the SQLiteOpenHelper class constructor.

In the onCreate() method, we execute the CREATE statement, using the API method db.execSQL(String query). In this method, we also create a default admin account, with the default password set to “admin”.

The AdminPage activity is the admin page that the application presents. In this activity, we just verify the admin password in the class itself, because we already know the password — but it is for you as a developer to decide whether you want to verify the password of the admin account via a database query as well.

It is inherently insecure to have an unchangeable default admin password, so on security principles, for a real-life application, you should certainly provide the ability to change the admin password. However, this being only a demo application, we have not added in the extra code.

mPassword = (EditText)findViewById(;

String pass = mPassword.getText().toString();
if(pass.equals("") || pass == null){    
    Toast.makeText(getApplicationContext(), "Password Missing", Toast.LENGTH_SHORT).show();
        Toast.makeText(getApplicationContext(), "Retrieving Data", Toast.LENGTH_SHORT).show();
        Toast.makeText(getApplicationContext(), "Admin Login Failed", Toast.LENGTH_SHORT).show();

When the admin logs in, then he or she is shown a list of all the registered users — or we Toast the admin with a suitable error message, as shown in Figures 8 and 9.

public class DbHelper extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "saket.db";
	private static final int DATABASE_VERSION = 1;
    public static final String SAKET_TABLE_NAME = "login";
	private static final String SAKET_TABLE_CREATE =
	                "CREATE TABLE " + SAKET_TABLE_NAME + "(" +
	                "username TEXT NOT NULL, password TEXT NOT NULL, email TEXT NOT NULL);";
	private static final String SAKET_DB_ADMIN = "INSERT INTO "+SAKET_TABLE_NAME+"values(1, admin, password,;";

	public DbHelper(Context context) {

		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		System.out.println("In constructor");


	// (non-Javadoc)
	// @see android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite.SQLiteDatabase)

	public void onCreate(SQLiteDatabase db) {

			//Create Database

			//create admin account
			System.out.println("In onCreate");
		}catch(Exception e){

	// (non-Javadoc)
	// @see android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite.SQLiteDatabase, int, int)

	public void onUpgrade(SQLiteDatabase arg0, int oldVersion, int newVersion) {


All user page

Figure 8: All user page

Admin failed

Figure 9: Admin failed

In the admin page, the above-mentioned two methods are used to retrieve the entries from the database, and show them to the admin in a TextView. In the above code:

  • The retrieveEntries() method queries the database for all the users created.
  • The showDatabase() method displays all the data obtained in the CURSOR in a TextView. The data from the cursor is retrieved by processing the cursor in a while loop, querying the columns based on their index. A StringBuilder object is used to build the final string containing all the data from the cursor, which is then fed to the TextView for display.
  • The onBackPressed() method is used to alter the action of the back key on the device to suit our application. Developers can then fire intents to decide the action they want to perform.

There are a lot of changes that can be applied to the code regarding the authentication mechanism; there is a provision to add many other exceptions, and prompt the user with a suitable message. Readers are free to contribute to this piece of code for their own understanding. You can pull a clone of this project from my Github repository and play around with the code.

There is another improvement that can be made to the code, by introducing transaction APIs like beginTransaction(), setTransactionSuccessfull(), etc. You can play around with these APIs, and enhance your applications’ database transactions to be more secure and atomic. These APIs also help application transactions to be compliant with the ACID definition of a database transaction.

This is all I have for you in this article. We’ll explore another interesting concept of Android application development later. Till then, happy coding!

  • Amit Suri

    Hi SaketKumar
    I am new in ANDROID and i saw your post you have done very good work, which will be very useful for new comers, Go ahead we are ready to follow u.
    Very Soon i will need your help because i have plan to make an interesting app in Android.
    For futher my email id is:

    Amit Suri

  • Naresh

    Hello Saket,
    You have done a very good job and I like that you describe each and everything in your project.
    It help lot of guys who are coming in android and looking for some help.
    Naresh Sharma

  • Nilesh Borse

    Hi Saket,
    Very Good Job..
    Can u Send me Ur Source Code ?
    My email Id-

    Nilesh Borse

  • Thanx Saket you’ve been of great help :)

  • i can not manage to run this code. please provide us the source code

    i will be very thankful to u.

  • VN

    thanks man very much!

  • S.Dhanalakshmi

    really helpful…
    thank you…. :)

  • geerthan

    can u send this project folder. it’ll be very use full. My email adderess=

  • akash

    plz send me the code file as sson as possible:
    my email-id::-

  • Source code for the above demo can be found at

    • bidhu

      hi,i found ur code very useful but the thing is when i tried to open newuser signup or all user then the application crashes . plz help me.

  • lavanya

    HI Saketh,
    This tutorial is really good.. and the way explained is also good… thanks a lot.. and if u provide example to connect android with my sql in same way.. then it will be really very helpfull…. Can u Please do it???
    Thank You…

  • dipika

    hi…..m dipika i want login form with forgetpwd but when we click on fortpwd btn they show next link nd connected with data base ……

  • Anguis

    im having a error it says simple login example has stop T_T what went wrong

    • hi there please see my reply above (to Uttam’s question)

  • Uttam

    Hi Man,

    Your application is very usefull as a beginger in android. but i have a problem when i click on the new register button i receved error box i.e. “unvfortunately Example is stopped” plz tell me what to do? do i missing something? Any help will be appriciated

    • hi there, I am assuming that the app force closes when you click on New User? button and All Users button??

      I think it’s because that the startManagingCursor() is deprecated. if you want to prevent the error, you can try entering a wrong username and password, click on Login, then the a Toast message will say Login Failed.. Try Again. Then you click on New User? button or All Users button, this time round the app will not force close.

      Also please delete the stop() method as it is deprecated as well. I think the stop() method force closes the whole application.

      • stylich vinoth

        main class or main java file in the sense?..what java file u r talking about?

        • Barbara

          Have you declared your activities in Manifest? I think this error it’s because of that. If not declare activities!

      • bidhu

        Hi,i tried ur solution but still it crashed when click on the new user or all users.and i entered wrong username and password and when clicked login button then the application crashed .

    • sohan shetty

      i hve solution for it plz delete the destroy function in the main class you wil get the right output

  • Toufiq khan

    hi saket…thanks for the app tutorial….

    I wanna know how can i find the table structure and the values stored into it

  • toufiq khan

    thanks a lot saket…i have understood it well enough from ur reply.
    could u help me out to bring the database to online server

  • sohan shetty

    fantabulous mann
    i am new android and also to db so it touched me very well thank you bro !!!!!!!

  • sohan shetty

    hey your tutorial is really superb but i found one flaw in your app .That is in the main java class you have declared destroy method which should nt be done because while running the app the app getting forced closed when we click sign up page or show all page so i guess to delete the destroy function in the main class .but still its a good job thank you so much :)

  • karthick

    After opening this log n application,it automatically stopped,showing error in logcat in this line stop();,this line is cut in centre,and its depreceated

  • karthick

    Nice tutorial two doubts is there for me, in having stop(); line,that line giving some problem,and what is the admin password,how to change the admin password at all

    • sohan

      admin password is admin itself

  • karthick

    Can you tell me,how can we change the admin password,and more functionality i need,how to put the forgot password,can you explain any one of them

  • karthick

    How to use forgot password in this code?

  • stylish vinoth

    “Unfortunately simple login application was stopped” message was displayed…application is not running at all….pls help me

  • ikhlas

    Hi SaketKumar
    really interesting can you send me the code

  • Ibrahim

    Great job. Thanks a lot. Please can you send me the code . My email adress is

  • Ibrahim

    Great job. Thanks a lot. Please can you send me the code . My email adress is

  • Barbara

    Great job. Can anyone help me? I can load all users from the database in this code. But how can i load only a specific row? Something like ONLY info about the user XPTO? Thanks!

  • nida

    can u pls post code for “password recovery” i m beginner and not getting the way how to recover password of user via email in an android app

  • vaheed

    plz send the code folder my mail id is

  • vijayakumar

    please sent source code my mail

  • Man

    Dear Sir,
    It’s my pleasure if u can send me the source code..I need to learn for my final year project. This is my email

  • dilip

    please send me the source code my mail

  • srikanth

    can you please send me the source code

  • Suresh N

    plz send me the code file as soon as possible:

    My mailid is “”

  • martin

    I need the source code of this application pls send me @

  • Harshil Mistry

    hey saket send me this full code email id is
    I need this code

  • shinn

    hi, can u please send me the source code? thnx..

  • iqra

    please send me the source code of this at

  • Ram Kumar

    pls sir
    can u send me th source code of this at it will help for my project sir

  • Sheetal

    Can You please give a tutorial on how to create login and registration system using mysql and php

    • parvinder lamba

      hii give me your email id ,ll send you the source code and tutorials for login,registration,forgot password etc. with data base

  • hemanth

    sir i want to do a project on login with username and password.and view some data posted by admin…please send me the source code are steps to complete my project. please guide me in this project i am new to android development but made static apps….
    please mail me soon as possible.

  • Rajesh

    Hello Sir,

    Please Can u Help me.. One Interview is there for me.. they will ask JSON Parsing so Please Can u send me code for JSON parsing .. Please Send an Easy code so that i can Easily Remember…

  • fati

    please send me the source code of this at

  • Chahinez ch

    please send me the code source i really need it this id my email:

  • geshvic

    Great article. Please assist me with the entire source code if you dont mind and secondly do you do consultancy? Send to

  • rachita

    plzz email me the full code..

  • John Kennedy

    Please put a zip folder so that we can run the code on our device. Thanks!

  • Sueb Ahmed

    plz email me full code

  • krishnareddy

    i want this code…..pls send me this email

  • prashant shende

    plz send this code on

  • ranjith

    plz send this code on

  • Vijay Bheda

    Please Send me Code at : As soon As please. Advance Thanks.

  • Vishal Juneja VJ

    plz give me full code
    As soon as plz