Android SQLite Database Example

MainActivity.java

MainActivity.java

package com.androidruler.databasesample;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.TextView;
import java.util.ArrayList;
import java.util.List;

public class MainActivity extends AppCompatActivity {

ListView allcontacts;
TextView totalcontacts;
ArrayList<String> allusers=new ArrayList<>();
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
allcontacts=(ListView)findViewById(R.id.contactlist);
totalcontacts=(TextView)findViewById(R.id.totalcount);

DbHandler db=new DbHandler(MainActivity.this);
//inserting contacts
db.addContact(new Contact("Ankur","Bansal","123456789"));
db.addContact(new Contact("Vibhor","Tayal","267755557"));
db.addContact(new Contact("Jatin","Garg","123456789"));

//getting total number of contacts
int total=db.getTotalContacts();
totalcontacts.setText("Total Contacts "+total);

//Reading all contacts and adding them to list
List<Contact> contacts=db.getAllContacts();
for (Contact ct: contacts)
{
allusers.add(ct.getFname()+" "+ct.getLname());
}

//setting the list to listview
ArrayAdapter<String> adapter=new ArrayAdapter<String>(this,android.R.layout.simple_list_item_1,android.R.id.text1,allusers);
allcontacts.setAdapter(adapter);
}

@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.menu_main, menu);
return true;
}

@Override
public boolean onOptionsItemSelected(MenuItem item) {
// Handle action bar item clicks here. The action bar will
// automatically handle clicks on the Home/Up button, so long
// as you specify a parent activity in AndroidManifest.xml.
int id = item.getItemId();

//noinspection SimplifiableIfStatement
if (id == R.id.action_settings) {
return true;
}

return super.onOptionsItemSelected(item);
}
}

Contact.java

package com.androidruler.databasesample;

//this is model class
public class Contact {

//variables
int id;
String fname;
String lname;
String contactnumber;

//simple empty Constructor
public Contact()
{

}
//Parameter constructor containing all three parameters
public Contact(int id,String fname,String lname,String contactnumber)
{
this.id=id;
this.fname=fname;
this.lname=lname;
this.contactnumber=contactnumber;
}

//Parameter constructor containing two parameters
public Contact(String fname,String lname,String contactnumber)
{
this.fname=fname;
this.lname=lname;
this.contactnumber=contactnumber;

}
//getting contactnumber
public String getContactnumber() {
return contactnumber;
}
//setting contactnumber
public void setContactnumber(String contactnumber) {
this.contactnumber = contactnumber;
}
//getting lname
public String getLname() {
return lname;

}
//setting lname
public void setLname(String lname) {
this.lname = lname;
}
//getting fname
public String getFname() {
return fname;
}
//setting fname
public void setFname(String fname) {
this.fname = fname;
}
//getting id
public int getId() {
return id;
}
//setting id
public void setId(int id) {
this.id = id;
}

}

DbHandler.java

package com.androidruler.databasesample;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;
import java.util.List;
public class DbHandler extends SQLiteOpenHelper {

//all constants as they are static and final(Db=Database)
//Db Version
private static final int Db_Version=1;
//Db Name
private static final String Db_Name="contactscontainer";
//table name
private static final String Table_Name="mycontacts";
//Creating mycontacts Columns
private static final String Contact_id="id";
private static final String Contact_fname="fname";
private static final String Contact_lname="lname";
private static final String Contact_number="contactnumber";

//constructor here
public DbHandler(Context context)
{
super(context,Db_Name,null,Db_Version);
}

//creating table
@Override
public void onCreate(SQLiteDatabase db) {
// writing command for sqlite to create table with required columns
String Create_Table="CREATE TABLE " + Table_Name + "(" + Contact_id
+ " INTEGER PRIMARY KEY," + Contact_fname + " TEXT," + Contact_lname +
" TEXT," + Contact_number + " TEXT" + ")";
db.execSQL(Create_Table);
}

//Upgrading the Db
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//Drop table if exists
db.execSQL("DROP TABLE IF EXISTS " + Table_Name);
//create the table again
onCreate(db);
}

//Add new Contact by calling this method
public void addContact(Contact contact)
{
// getting db instance for writing the contact
SQLiteDatabase db=this.getWritableDatabase();
ContentValues cv=new ContentValues();
cv.put(Contact_fname,contact.getFname());
cv.put(Contact_lname,contact.getLname());
cv.put(Contact_number,contact.getContactnumber());

//inserting row
db.insert(Table_Name, null, cv);
//close the database to avoid any leak
db.close();
}

//getting contact according to contact id
public Contact getContact(int id)
{
// getting db instance for reading the contact
SQLiteDatabase db=this.getReadableDatabase();
//writing query for getting contact according to id
Cursor cursor=db.query(Table_Name,new String[]{Contact_id,Contact_fname,Contact_lname,Contact_number},Contact_id + "=?" ,
new String[] { String.valueOf(id)},null,null,null,null);
if(cursor!=null)
cursor.moveToFirst();

Contact contact=new Contact(Integer.parseInt(cursor.getString(0)),cursor.getString(1),cursor.getString(2),
cursor.getString(3));
return contact;
}

//this will all the contact from the table
public List<Contact> getAllContacts()
{
List<Contact> contactList=new ArrayList<Contact>();
//query to select all contacts from table
String selectAllContact="SELECT * FROM " + Table_Name;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectAllContact, null);

// move cursor to first and use loop to add all the contacts to list
if(cursor.moveToFirst())
{
do {
Contact contact =new Contact();
contact.setId(Integer.parseInt(cursor.getString(0)));
contact.setFname(cursor.getString(1));
contact.setLname(cursor.getString(2));
contact.setContactnumber(cursor.getString(3));
//add contact to list
contactList.add(contact);
} while (cursor.moveToNext());
}

return contactList;
}

//get total contacts in the table
public int getTotalContacts()
{
String totalcontacts= "SELECT * FROM " + Table_Name;
SQLiteDatabase db=this.getReadableDatabase();
Cursor cursor=db.rawQuery(totalcontacts,null);

//getting total number of rows from cursor
return cursor.getCount();
}

// Updating contact
public int updateContact(Contact contact) {
SQLiteDatabase db = this.getWritableDatabase();

ContentValues values = new ContentValues();
values.put(Contact_fname, contact.getFname());
values.put(Contact_lname, contact.getLname());

// updating row with updated contact values
return db.update(Table_Name, values, Contact_id + " = ?",
new String[] { String.valueOf(contact.getId()) });
}

// Deleting contact
public void deleteContact(Contact contact) {
SQLiteDatabase db = this.getWritableDatabase();
db.delete(Table_Name, Contact_id + " = ?",
new String[] { String.valueOf(contact.getId()) });
db.close();
}
}

//layout for MainActivity.java

activity_main.xml

<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" 
 tools:context=".MainActivity">
<ListView
android:id="@+id/contactlist"
android:layout_width="fill_parent"
android:layout_height="wrap_content"></ListView>

<TextView
android:layout_below="@+id/contactlist"
android:layout_margin="10dp"
android:id="@+id/totalcount"
android:text="@string/hello_world"
android:layout_width="wrap_content"
android:layout_height="wrap_content" />

</RelativeLayout>