HomeAudienceDevelopersAndroid Application Development: Playing with SQLite Database

Android Application Development: Playing with SQLite Database

- Advertisement -

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.

- Advertisement -

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 — android.R.style.Theme_Translucent_NoTitleBar, 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, admin@gmail.com);";

    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(R.id.editPassword);

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, admin@gmail.com);";

	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!

- Advertisement -
Saketkumar Srivastav
Saketkumar Srivastavhttp://saketsrivastav.blogspot.com/
The author is an open source enthusiast, and an Android developer.


  1. 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: android.amitsuri@gmail.com

    Amit Suri

  2. 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

    • 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.

  3. 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…

  4. 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 ……

  5. 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 app.name 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.

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

      • 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 .

  6. 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

  7. 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 :)

  8. 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

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

  10. 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

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

  12. 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!

  13. 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

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

  14. 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. hemanthsaicharan@gmail.com

  15. 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…


Please enter your comment!
Please enter your name here

Thought Leaders

Open Journey

- Advertisement -