Android Sqlite Database Example

Hi welcome to my Android Blog, this is my first blog. You can find many Android Sqlite Database Examples in internet. I want to stand apart from them, so I choose this example as my first post in my android blog.

I followed some standards to open and close database and passing the values to be inserted to database in the form of Data Transfer Objects. 
Below is the Android Database Example using Sqlite.  User Registration and login are the two modules of this sample project.

User Registration : In this module, user can enter his personal details and application will save user details in database.

Login : In this module, user will enter his login credentials and application will authenticate the user by checking his login credentials in database.

Project structure :

Screens : 

   fig. 1) Login Screen

                                                           

                                                          
login_screen.xml :

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

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical" >

    <RelativeLayout
        android:layout_width="match_parent"
        android:layout_height="30dp"
        android:layout_alignParentTop="true"
        android:background="@android:color/darker_gray" >

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_centerHorizontal="true"
            android:layout_centerVertical="true"
            android:text="Login"
            android:textColor="@android:color/black" />
    </RelativeLayout>

    <RelativeLayout
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:layout_margin="20dp"
        android:gravity="center" >

        <EditText
            android:id="@+id/userId"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_alignParentTop="true"
            android:layout_centerHorizontal="true"
            android:ems="10"
            android:hint="Username" >
            <requestFocus />
        </EditText>

        <EditText
            android:id="@+id/password"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_below="@+id/userId"
            android:layout_centerHorizontal="true"
            android:layout_marginTop="10dp"
            android:ems="10"
            android:hint="Password"
            android:inputType="textPassword" />

        <LinearLayout
            android:id="@+id/linearLayout1"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_below="@+id/password"
            android:layout_centerHorizontal="true"
            android:layout_marginTop="10dp"
            android:orientation="horizontal" >

            <Button
                android:id="@+id/loginBtn"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="Login" />

            <Button
                android:id="@+id/registerBtn"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_marginLeft="5dp"
                android:text="Register" />
        </LinearLayout>
    </RelativeLayout>

</LinearLayout>

  fig.2) Registration Screen

                                                           

register_screen.xml :

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical" >

    <RelativeLayout
        android:layout_width="match_parent"
        android:layout_height="30dp"
        android:background="@android:color/darker_gray" >

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_centerHorizontal="true"
            android:layout_centerVertical="true"
            android:text="Register"
            android:textColor="@android:color/black" />
    </RelativeLayout>

    <RelativeLayout
        android:layout_width="match_parent"
        android:layout_height="fill_parent"
        android:layout_margin="20dp"
        android:gravity="center" >

        <EditText
            android:id="@+id/reg_userId"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_alignParentTop="true"
            android:layout_centerHorizontal="true"
            android:ems="10"
            android:hint="Username" >

            <requestFocus />
        </EditText>

        <EditText
            android:id="@+id/reg_password"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_below="@+id/reg_userId"
            android:layout_centerHorizontal="true"
            android:layout_marginTop="10dp"
            android:ems="10"
            android:hint="Password"
            android:inputType="textPassword" />

        <EditText
            android:id="@+id/reg_mobile"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_below="@+id/reg_password"
            android:layout_centerHorizontal="true"
            android:layout_marginTop="10dp"
            android:ems="10"
            android:hint="Mobile"
            android:inputType="phone" />

        <Button
            android:id="@+id/reg_registerBtn"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_below="@+id/reg_mobile"
            android:layout_centerHorizontal="true"
            android:layout_marginTop="10dp"
            android:text="Register" />
    </RelativeLayout>


</LinearLayout>

     fig.3) User Screen after login

                                                     

user_valid_screen.xml :

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical" >

    <RelativeLayout
        android:layout_width="match_parent"
        android:layout_height="30dp"
        android:layout_alignParentTop="true"
        android:background="@android:color/darker_gray" >

        <TextView
            android:id="@+id/logoutBtn"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_alignParentRight="true"
            android:layout_alignParentTop="true"
            android:layout_marginRight="10dp"
            android:layout_marginTop="5dp"
            android:clickable="true"
            android:text="Logout"
            android:textColor="@android:color/black" />
    </RelativeLayout>

    <RelativeLayout
        android:layout_width="match_parent"
        android:layout_height="fill_parent"
        android:layout_alignParentTop="true" >

        <TextView
            android:id="@+id/valid_text"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_centerInParent="true"
            android:textAppearance="?android:attr/textAppearanceLarge" />
    </RelativeLayout>


</RelativeLayout>

Android_manifest.xml : 

<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.shyam.databasesample"
    android:versionCode="1"
    android:versionName="1.0" >

    <uses-sdk
        android:minSdkVersion="10"
        android:targetSdkVersion="19" />

    <application
        android:allowBackup="true"
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name"
        android:theme="@style/AppTheme" >
        <activity
            android:name="com.shyam.dbsample.gui.LoginScreenActivity"
            android:label="@string/app_name"
            android:screenOrientation="portrait"
            android:theme="@android:style/Theme.NoTitleBar.Fullscreen" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
        <activity
            android:name="com.shyam.dbsample.gui.RegisterScreenActivity"
            android:label="@string/app_name"
            android:screenOrientation="portrait"
            android:theme="@android:style/Theme.NoTitleBar.Fullscreen" >
        </activity>
        <activity
            android:name="com.shyam.dbsample.gui.ValidActivity"
            android:label="@string/app_name"
            android:screenOrientation="portrait"
            android:theme="@android:style/Theme.NoTitleBar.Fullscreen" >
        </activity>
    </application>

</manifest>

Activity Files :

Note * : 1) getDbObject is a static method and will return the database object.
2) DbHandler.getInstance(this) is the statement where I am initializing the database.

LoginScreenActivity.java :

package com.shyam.dbsample.gui;
import static com.shyam.dbsample.database.ApplicationDatabase.getDbObject;
import com.shyam.databasesample.R;
import com.shyam.dbsample.dao.UserDao;
import com.shyam.dbsample.database.DbHandler;
import com.shyam.dbsample.dto.UserDto;
import android.app.Activity;
import android.content.Intent;
import android.os.AsyncTask;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class LoginScreenActivity extends Activity implements OnClickListener{
private EditText userId, password;
private Button loginBtn, registerBtn;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.login_screen);

DbHandler.getInstance(this);
userId = (EditText)findViewById(R.id.userId);
password = (EditText)findViewById(R.id.password);

loginBtn = (Button)findViewById(R.id.loginBtn);
registerBtn = (Button)findViewById(R.id.registerBtn);

registerBtn.setOnClickListener(this);
loginBtn.setOnClickListener(this);
}
@Override
public void onClick(View v) {
int viewId = v.getId();
Intent i ;
switch(viewId)
{
case R.id.loginBtn:
getLoginDetails();
new CheckAuth().execute(getLoginDetails());
break;
case R.id.registerBtn:
i = new Intent(LoginScreenActivity.this,RegisterScreenActivity.class);
startActivity(i);
break;
}
}

public UserDto getLoginDetails()
{
UserDto dto = new UserDto();
dto.setUserid(userId.getText().toString());
dto.setPassword(password.getText().toString());
return dto;
}

class CheckAuth extends AsyncTask<UserDto,String,String>
{
Boolean flag = false;
UserDto dto ;
@Override
protected String doInBackground(UserDto... params) {
dto = params[0];
flag = UserDao.getInstance().userAuth(dto, getDbObject(1));

return null;
}
@Override
protected void onPostExecute(String result) {
                      super.onPostExecute(result);
if(flag)
{
// Toast.makeText(LoginScreenActivity.this, " Valid", Toast.LENGTH_SHORT).show();
Intent i = new Intent(LoginScreenActivity.this,ValidActivity.class);
i.putExtra("username",dto.getUserid());
startActivity(i);
}
else
{
Toast.makeText(LoginScreenActivity.this, "Not Valid", Toast.LENGTH_SHORT).show();
}
}

}

}

RegisterScreenActivity.java

package com.shyam.dbsample.gui;
import com.shyam.databasesample.R;
import com.shyam.dbsample.dao.UserDao;
import com.shyam.dbsample.database.DbHandler;
import com.shyam.dbsample.dto.UserDto;
import static com.shyam.dbsample.database.ApplicationDatabase.getDbObject;
import android.app.Activity;
import android.os.AsyncTask;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class RegisterScreenActivity extends Activity implements OnClickListener{
private EditText userId, password, mobile;
private Button registerBtn;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.register_screen);

userId = (EditText)findViewById(R.id.reg_userId);
password = (EditText)findViewById(R.id.reg_password);
mobile = (EditText)findViewById(R.id.reg_mobile);

registerBtn = (Button)findViewById(R.id.reg_registerBtn);

registerBtn.setOnClickListener(this);
}
@Override
public void onClick(View v) {
int viewId = v.getId();
switch(viewId)
{
case R.id.reg_registerBtn:
new UserRegistration().execute(getUserDto());
break;
}

}
public UserDto getUserDto()
{
UserDto userDto = new UserDto();
userDto.setUserid(userId.getText().toString());
userDto.setPassword(password.getText().toString());
userDto.setMobile(mobile.getText().toString());
return userDto;
}

class UserRegistration extends AsyncTask<UserDto, String, String>
{
Boolean flag = false;
@Override
protected String doInBackground(UserDto... params) {
flag = UserDao.getInstance().userRegistration(params[0], getDbObject(1));

return null;
}
              @Override
protected void onPreExecute() {
// TODO Auto-generated method stub
super.onPreExecute();
}
@Override
protected void onPostExecute(String result) {
// TODO Auto-generated method stub
super.onPostExecute(result);
if(flag)
{
finish();
}
else
{
Toast.makeText(RegisterScreenActivity.this, "Not Registered, Try Again",      Toast.LENGTH_SHORT).show();
}
}
}

}

ValidActivity.java :

package com.shyam.dbsample.gui;

import com.shyam.databasesample.R;

import android.app.Activity;

import android.os.Bundle;

import android.view.View;

import android.view.View.OnClickListener;

import android.widget.TextView;

public class ValidActivity extends Activity implements OnClickListener{

private TextView logout;

private TextView welcomeText;

String username;

@Override

protected void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.user_valid_screen);

logout = (TextView)findViewById(R.id.logoutBtn);

welcomeText = (TextView)findViewById(R.id.valid_text);

username = getIntent().getExtras().getString("username");

welcomeText.setText("Welcome "+username);

logout.setOnClickListener(this);

}

@Override

public void onBackPressed() {

}

@Override

public void onClick(View v) {

int viewId = v.getId();

switch(viewId)

{

case R.id.logoutBtn:

finish();

break;

}

}

}

Database Handlers :

DbHandler.java:

package com.shyam.dbsample.database;

import android.content.Context;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

public class DbHandler extends SQLiteOpenHelper{

private static DbHandler dbHandler;

static String DATABASE_NAME = "SAMPLE";

static int VERSION = 1;

private Context context;

public DbHandler(Context context) {

super(context, DATABASE_NAME, null, VERSION);

this.context = context;

}

public static DbHandler getInstance(Context context)

{

if (dbHandler == null)

dbHandler = new DbHandler(context);

ApplicationDatabase.setDbHandler(dbHandler);

return dbHandler;

}

@Override

public void onCreate(SQLiteDatabase db) {

db.execSQL("CREATE TABLE users (userid TEXT, password TEXT, mobile TEXT)");

}

@Override

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

}

public SQLiteDatabase getDbObject(int isWritableDatabase) {

return (isWritableDatabase == 1) ? this.getWritableDatabase() : this.getReadableDatabase();

}

}

ApplicationDatabase.java :

package com.shyam.dbsample.database;

import android.database.sqlite.SQLiteDatabase;

public class ApplicationDatabase {

private static DbHandler dbhandler;

public static void setDbHandler(DbHandler dbhandler)

{

ApplicationDatabase.dbhandler = dbhandler;

}

public static SQLiteDatabase getDbObject(int isWritableDatabase)

{

return dbhandler.getDbObject(isWritableDatabase);

}

}

Data Access Logic Files :

UserDao.java :

package com.shyam.dbsample.dao;

import com.shyam.dbsample.dto.UserDto;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteStatement;

public class UserDao {

public static UserDao userDao;

private UserDao()

{

}

public static UserDao getInstance()

{

if(userDao == null)

{

userDao = new UserDao();

}

return userDao;

}

public boolean userRegistration(UserDto userDto, SQLiteDatabase dbObject)

{

SQLiteStatement stmnt = dbObject.compileStatement("INSERT INTO users (userid, password, mobile) VALUES (?,?,?)");

stmnt.bindString(1, userDto.getUserid());

stmnt.bindString(2, userDto.getPassword());

stmnt.bindString(3, userDto.getMobile());

return stmnt.executeInsert()>0 ? true : false;

}

public Boolean userAuth(UserDto userDto, SQLiteDatabase dbObject) {

Boolean result;

try {

Cursor c = dbObject.rawQuery("select userid from users WHERE userid = ? AND password = ?", new String[]{userDto.getUserid(),userDto.getPassword()});

if(c!=null && c.getCount() > 0){

result = true;

}

else

{

result = false;

}

c.close();

} catch (Exception e) {

e.printStackTrace();

result = false;

} finally {

dbObject.close();

}

return result;

}

}

Data Transfer Object :

UserDto.java :

package com.shyam.dbsample.dto;

public class UserDto {

private String userid;

private String password;

private String mobile;

public String getPassword() {

return password;

}

public void setPassword(String password) {

this.password = password;

}

public String getMobile() {

return mobile;

}

public void setMobile(String mobile) {

this.mobile = mobile;

}

public String getUserid() {

return userid;

}

public void setUserid(String userid) {

this.userid = userid;

}

}