Android SQLite Database Tutorial
Android have many method to store local data in android device. Android SQLite database is one of the best method to allow smart phone to stores data in a text file. It is a good and efficient approach to store the data in the smart phones. Besides that, SQLite database is a light weight database and supports all relational database features. By the use of SQLite Database in Android, the records and data can be easily to create, retrieve, delete and modify by just using the simple SQL statements. In this tutorial, I will give you some example on implement the SQLite database in Android device. I will give all the steps and explain how it works.
SQLite Database Demo
Creating an Empty Project
1. Open Android Studio IDE in your computer.
2. Create a new project and Edit the Application name to “SQLiteExample”.
(Optional) You can edit the company domain or select the suitable location for current project tutorial. After that click next button to proceed.
3. Select Minimum SDK (API 15:Android 4.0.3 (IceCreamSandwich) and Click Next button.
4. Choose “Empty Project” and Click Next button
5. Lastly, press finish button.
Prepare a Database table
Try to prepare a database table you will need to create a SQLite database later. I name my table name as DBExample and below are the structure and content of the table.
Add a New Class
Right click your package name > New > Java Class. You can type any java class name for example My class name is DBHelper.
Extends SQLiteOpenHelper and edit the class
The Following are the source code of DBHelper.java class, this class should extends SQLiteOpenHelper to enable the create, retrieve, update and delete features.
void onCreate(SQLiteDatabase db)
– This method will call when the database is create for the first time.
void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
– This method will activate when the Database version has been changed.
boolean insertContact (String name, String phone, String email)
– It allow to insert the contact name, phone and email to the contact table in the database.
Cursor getData(int id)
– Get the specific contact information from the contact table.
boolean updateContact (Integer id, String name, String phone, String email)
– It allow to update the contact name, phone and email to the contact table in the database.
Integer deleteContact (Integer id)
– It allow to delete specific contact record in the database.
ArrayList <String> getid()
– Get the all id from the contact table.
import java.util.ArrayList;
import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.Cursor;
import android.database.DatabaseUtils;
public class DBHelper extends SQLiteOpenHelper {
public static final String DATABASE_NAME = "DBExample.db";
public static final String CONTACTS_TABLE_NAME = "contacts";
public static final String CONTACTS_COLUMN_ID = "id";
public static final String CONTACTS_COLUMN_NAME = "name";
public static final String CONTACTS_COLUMN_EMAIL = "email";
public static final String CONTACTS_COLUMN_PHONE = "phone";
public DBHelper(Context context)
{
super(context, DATABASE_NAME , null, 1);
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
db.execSQL(
"create table contacts " +
"(id integer primary key, name text,phone text,email text)"
);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
db.execSQL("DROP TABLE IF EXISTS contacts");
onCreate(db);
}
public boolean insertContact (String name, String phone, String email)
{
SQLiteDatabase db = this.getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put("name", name);
contentValues.put("phone", phone);
contentValues.put("email", email);
db.insert("contacts", null, contentValues);
return true;
}
public Cursor getData(int id){
SQLiteDatabase db = this.getReadableDatabase();
Cursor res = db.rawQuery( "select * from contacts where id="+id+"", null );
return res;
}
public int numberOfRows(){
SQLiteDatabase db = this.getReadableDatabase();
int numRows = (int) DatabaseUtils.queryNumEntries(db, CONTACTS_TABLE_NAME);
return numRows;
}
public boolean updateContact (Integer id, String name, String phone, String email)
{
SQLiteDatabase db = this.getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put("name", name);
contentValues.put("phone", phone);
contentValues.put("email", email);
db.update("contacts", contentValues, "id = ? ", new String[] { Integer.toString(id) } );
return true;
}
public Integer deleteContact (Integer id)
{
SQLiteDatabase db = this.getWritableDatabase();
return db.delete("contacts",
"id = ? ",
new String[] { Integer.toString(id) });
}
public ArrayList<String> getid()
{
ArrayList<String> arrayList = new ArrayList<String>();
SQLiteDatabase db = this.getReadableDatabase();
Cursor res = db.rawQuery( "select * from contacts", null );
res.moveToFirst();
while(res.isAfterLast() == false){
arrayList.add(res.getString(res.getColumnIndex(CONTACTS_COLUMN_ID)));
res.moveToNext();
}
return arrayList;
}
}
Edit activity_main.xml Layout
The activity_main.xml is to display all the contacts list of database and the add button. This xml is the layout of the MainActivity.java.
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent"
android:layout_height="match_parent" android:paddingLeft="@dimen/activity_horizontal_margin"
android:paddingRight="@dimen/activity_horizontal_margin"
android:paddingTop="@dimen/activity_vertical_margin"
android:paddingBottom="@dimen/activity_vertical_margin" tools:context=".MainActivity">
<ListView
android:id="@+id/listView1"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_centerHorizontal="true"
android:layout_above="@+id/btnAdd"
android:layout_alignParentTop="true">
</ListView>
<Button
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Add"
android:id="@+id/btnAdd"
android:layout_alignParentBottom="true"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true"
android:layout_alignParentRight="true"
android:layout_alignParentEnd="true" />
</RelativeLayout>
Edit MainActivity.java
This is the code in the MainActivity.java. This activity display all the contacts and allow to navigate to the DisplayContacts activity when any contact is clicked.
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.content.Intent;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.Button;
import android.widget.ListView;
import java.util.ArrayList;
public class MainActivity extends AppCompatActivity implements View.OnClickListener{
public final static String EXTRA_MESSAGE = "MESSAGE";
private ListView obj;
DBHelper mydb;
Button btnAdd;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
btnAdd=(Button)findViewById(R.id.btnAdd);
btnAdd.setOnClickListener(this);
mydb = new DBHelper(this);
final ArrayList array_list = mydb.getid();
ArrayAdapter arrayAdapter=new ArrayAdapter(this,android.R.layout.simple_list_item_1, array_list);
obj = (ListView)findViewById(R.id.listView1);
obj.setAdapter(arrayAdapter);
obj.setOnItemClickListener(new OnItemClickListener(){
@Override
public void onItemClick(AdapterView<?> arg0, View arg1, int arg2,long arg3) {
// TODO Auto-generated method stub
int id_To_Search = Integer.parseInt(array_list.get(arg2).toString());
// Toast.makeText(getApplicationContext(),""+id_To_Search,Toast.LENGTH_LONG).show();
Bundle dataBundle = new Bundle();
dataBundle.putInt("id", id_To_Search);
Intent intent = new Intent(getApplicationContext(),DisplayContact.class);
intent.putExtras(dataBundle);
startActivity(intent);
}
});
}
@Override
public void onClick(View v) {
if (v.equals(btnAdd)){
Bundle dataBundle = new Bundle();
dataBundle.putInt("id", 0);
Intent intent = new Intent(getApplicationContext(),DisplayContact.class);
intent.putExtras(dataBundle);
startActivity(intent);
}
}
}
Create a new Activity to display contact
Add a new activity in your package (Right Click package name> New > Activity) and name it as “DisplayContact”. This activity is to display the contact information that selected by the user.
Edit the activity_display_contact.xml layout
Below are the activity_display_contact.xml to display the layout of DisplayContact.java. There have edit text to let user key in and the buttons to delete or edit the contact.
<ScrollView xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:id="@+id/scrollView1"
android:layout_width="match_parent"
android:layout_height="wrap_content"
tools:context=".DisplayContact" >
<RelativeLayout
android:layout_width="match_parent"
android:layout_height="370dp"
android:paddingBottom="@dimen/activity_vertical_margin"
android:paddingLeft="@dimen/activity_horizontal_margin"
android:paddingRight="@dimen/activity_horizontal_margin"
android:paddingTop="@dimen/activity_vertical_margin">
<EditText
android:id="@+id/editTextName"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_marginTop="5dp"
android:layout_marginLeft="82dp"
android:ems="10"
android:inputType="text" >
</EditText>
<EditText
android:id="@+id/editTextEmail"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginTop="22dp"
android:ems="10"
android:inputType="textEmailAddress"
android:layout_alignLeft="@+id/editTextName"
android:layout_below="@+id/editTextPhone" />
<TextView
android:id="@+id/textView1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignBottom="@+id/editTextName"
android:layout_alignParentLeft="true"
android:text="@string/name"
android:textAppearance="?android:attr/textAppearanceMedium" />
<Button
android:id="@+id/button1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:onClick="edit"
android:text="Save or Edit"
android:layout_below="@+id/editTextEmail"
android:layout_centerHorizontal="true"
android:layout_marginTop="56dp" />
<TextView
android:id="@+id/textView2"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignBottom="@+id/editTextEmail"
android:layout_alignLeft="@+id/textView1"
android:text="@string/email"
android:textAppearance="?android:attr/textAppearanceMedium" />
<TextView
android:id="@+id/textView5"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignBottom="@+id/editTextPhone"
android:layout_alignLeft="@+id/textView1"
android:text="@string/phone"
android:textAppearance="?android:attr/textAppearanceMedium" />
<EditText
android:id="@+id/editTextPhone"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_below="@+id/editTextName"
android:ems="10"
android:inputType="phone|text"
android:layout_alignLeft="@+id/editTextName" />
<Button
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="DELETE"
android:onClick="delete"
android:id="@+id/btndelete"
android:layout_alignParentBottom="true"
android:layout_alignLeft="@+id/button1"
android:layout_alignStart="@+id/button1"
android:layout_alignRight="@+id/button1"
android:layout_alignEnd="@+id/button1" />
</RelativeLayout>
</ScrollView>
Edit DisplayContact.java code
Go to displaycontact.java class to paste the following code, the purpose of this class to perform the edit and delete feature from the DBHelper.java.
import android.os.Bundle;
import android.app.AlertDialog;
import android.content.DialogInterface;
import android.content.Intent;
import android.database.Cursor;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.TextView;
import android.widget.Toast;
public class DisplayContact extends AppCompatActivity {
private DBHelper mydb ;
TextView name ;
TextView phone;
TextView email;
int id_To_Update = 0;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_display_contact);
name = (TextView) findViewById(R.id.editTextName);
phone = (TextView) findViewById(R.id.editTextPhone);
email = (TextView) findViewById(R.id.editTextEmail);
mydb = new DBHelper(this);
Bundle extras = getIntent().getExtras();
if(extras !=null)
{
int Value = extras.getInt("id");
if(Value>0){
//means this is the view part not the add contact part.
Cursor rs = mydb.getData(Value);
id_To_Update = Value;
rs.moveToFirst();
String nam = rs.getString(rs.getColumnIndex(DBHelper.CONTACTS_COLUMN_NAME));
String phon = rs.getString(rs.getColumnIndex(DBHelper.CONTACTS_COLUMN_PHONE));
String emai = rs.getString(rs.getColumnIndex(DBHelper.CONTACTS_COLUMN_EMAIL));
if (!rs.isClosed())
{
rs.close();
}
name.setText((CharSequence)nam);
phone.setText((CharSequence)phon);
email.setText((CharSequence)emai);
}
}
}
public void edit(View view) {
Bundle extras = getIntent().getExtras();
if (extras != null) {
int Value = extras.getInt("id");
if (Value > 0) {
if (mydb.updateContact(id_To_Update, name.getText().toString(), phone.getText().toString(), email.getText().toString())) {
Toast.makeText(getApplicationContext(), "Updated", Toast.LENGTH_SHORT).show();
Intent intent = new Intent(getApplicationContext(), MainActivity.class);
startActivity(intent);
} else {
Toast.makeText(getApplicationContext(), "not Updated", Toast.LENGTH_SHORT).show();
}
} else {
if (mydb.insertContact(name.getText().toString(), phone.getText().toString(), email.getText().toString())) {
Toast.makeText(getApplicationContext(), "done", Toast.LENGTH_SHORT).show();
} else {
Toast.makeText(getApplicationContext(), "not done", Toast.LENGTH_SHORT).show();
}
Intent intent = new Intent(getApplicationContext(), MainActivity.class);
startActivity(intent);
}
}
}
public void delete(View view)
{
AlertDialog.Builder builder = new AlertDialog.Builder(this);
builder.setMessage(R.string.deleteContact)
.setPositiveButton(R.string.yes, new DialogInterface.OnClickListener() {
public void onClick(DialogInterface dialog, int id) {
// int Value = extras.getInt("id");
mydb.deleteContact(id_To_Update);
Toast.makeText(getApplicationContext(), "Deleted Successfully", Toast.LENGTH_SHORT).show();
Intent intent = new Intent(getApplicationContext(),MainActivity.class);
startActivity(intent);
}
})
.setNegativeButton(R.string.no, new DialogInterface.OnClickListener() {
public void onClick(DialogInterface dialog, int id) {
// User cancelled the dialog
}
});
AlertDialog d = builder.create();
d.setTitle("Are you sure");
d.show();
}
}
Edit strings.xml file
The following is strings.xml in the res/values package. This will show string value for xml and java file to make source code more efficient. By using this approach, the string can be reuse in every file.
<?xml version="1.0" encoding="utf-8"?>
<resources>
<string name="app_name">SQLiteExample</string>
<string name="action_settings">Settings</string>
<string name="hello_world">Hello world!</string>
<string name="Add_New">Add New</string>
<string name="edit">Edit Contact</string>
<string name="delete">Delete Contact</string>
<string name="title_activity_display_contact">DisplayContact</string>
<string name="name">Name</string>
<string name="phone">Phone</string>
<string name="email">Email</string>
<string name="save">Save Contact</string>
<string name="deleteContact">Are you sure, you want to delete it.</string>
<string name="yes">Yes</string>
<string name="no">No</string>
</resources>
Run Your Project
You are done to coding the SQLite database, now you can run the project in your android device to test out the features.
3 Responses
[…] to https://questdot.com/2016/05/android-sqlite/ and get your sqlite database in your […]
[…] You can get sample SQLite project from this link SQLite Database Example. […]
[…] provide SQLite database for local database in the android application. SQLite is hard to setting up because it […]