Android Use Sqlite Database Example

Almost all android application need database for storing data like username, password and other. Now we are going to write simple code on how to INSERT, UPDATE, DELETE and RETRIEVE data from database. The most popular database on mobile is the SqliteDatabase. So, this database we will going to used in this example.

Below is the structure of the project codes.

We have 6 java file in this project which is :

1. CustomArrayAdapter.java -> This is the ArrayAdapter of our ListView that hold records.
2. Dog.java -> This is our object.
3. MainActivity.java -> Holds all our layout.
4. MyDatabaseHelper.java -> This is our SQLiteOpenHelper. Responsible for creating database, inserting data, updating data, retrieving data and deleting data.
5. PopupOfAction.java -> This is just a Dialog that show option either to delete or update the record.
6. PopupOfUpdateOrAdd -> This is just a Dialog used to edit or add new record. 

Here is the code in our MainActivity.java.

package com.example.createdatabaseexample;

import android.app.Activity;
import android.os.Bundle;
import android.text.Html;
import android.view.Gravity;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.Button;
import android.widget.LinearLayout;
import android.widget.ListView;
import android.widget.ScrollView;
import android.widget.TextView;

public class MainActivity extends Activity implements OnClickListener, OnItemClickListener {
 
 ScrollView contentView;
 LinearLayout linearLayout;
 ListView listView;
 TextView label;
 Button createNewRecordBtn;
    
   
 @Override
 protected void onCreate(Bundle savedInstanceState) {
   super.onCreate(savedInstanceState);
        
        
        // INIT DATABASE HELPER
        MyDatabaseHelper dbHelper = new MyDatabaseHelper(this);
        
        // GET THE RECORDS
        Dog[] dogs = dbHelper.getRecords();
        
        // INITIALIZED LAYOUT
        contentView = new ScrollView(this);
        
        linearLayout = new LinearLayout(this);
        linearLayout.setOrientation(LinearLayout.VERTICAL);
        
        createNewRecordBtn = new Button(this);
        createNewRecordBtn.setText("Create New Record");
        
        // SET CLICK LISTENER
        createNewRecordBtn.setOnClickListener(this);
        
        // OUR LISTVIEW
        listView = new ListView(this);
        listView.setOnItemClickListener(this);
        // SET ADAPTER
        listView.setAdapter(new CustomArrayAdapter(this, dogs));
        
        // LABEL
        label = new TextView(this);
        label.setText("Below Are The Dogs List\n' click item to delete or edit ' ");
        label.setText(Html.fromHtml("<b>Below Are The Dogs List</b><br><font color = 'red'><small>Below Are The Dogs List</small></font>"));
        label.setGravity(Gravity.CENTER);
        
        
        // ADD LAYOUTS TO CONTENTVIEW
        linearLayout.addView(createNewRecordBtn);
        linearLayout.addView(label);
        linearLayout.addView(listView);
        
        // SET CONTENT VIEW
        this.setContentView(linearLayout);
         
    }
 
 

 @Override
 public void onClick(View view) {
  
  // SHOW POPUP 
  new PopupOfUpdateOrAdd(this).show();
 }



 @Override
 public void onItemClick(AdapterView<?> parent, View view, int position,
   long id) {
  
  // GET THE TAG
  Dog dog = (Dog) view.getTag();
  
  // SHOW ACTION POPUP
  new PopupOfAction(this,dog).show();
  
 }
}

Here is the code in Dog.java.

package com.example.createdatabaseexample;

/**
 * Simple object
 * 
 */
public class Dog {
 private int id;
 private String name;

 public int getId() {
  return id;
 }

 public void setId(int id) {
  this.id = id;
 }

 public String getName() {
  return name;
 }

 public void setName(String name) {
  this.name = name;
 }

}

Here is the code in CustomArrayAdapter.java

package com.example.createdatabaseexample;

import android.content.Context;
import android.view.View;
import android.view.ViewGroup;
import android.widget.ArrayAdapter;
import android.widget.TextView;

public class CustomArrayAdapter extends ArrayAdapter<Dog>{
 
 Context context;
 
 public CustomArrayAdapter(Context context,Dog[] dogs ) {
  super(context,android.R.layout.simple_list_item_1, dogs);
  
  this.context = context;
 }

 @Override
 public View getView(int position, View convertView, ViewGroup parent) {
  
  Dog dog = this.getItem(position);
  
  TextView textView = (TextView) convertView;
  
  if(textView == null){
   // INFLATE TEXTVIEW IF NULL
   textView = (TextView) ((MainActivity)context).getLayoutInflater().inflate(android.R.layout.simple_list_item_1, null);
  }
  
  // SET TEXT
  textView.setText(dog.getName());
  // SET TAG THE DOG OBJECT
  textView.setTag(dog);
  
  return textView;
 }
}

Here is the code in MyDatabaseHelper.java

package com.example.createdatabaseexample;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class MyDatabaseHelper extends SQLiteOpenHelper {

 final static String DB_NAME = "my_database.db";
 final static String DOGS_TABLE = "dogs";
 final static int VERSION = 1;
 final SQLiteDatabase db ;
 
 public MyDatabaseHelper(Context context) {
  super(context, DB_NAME, null, VERSION);
  // OPEN DATABASE
  db = this.getReadableDatabase();
 }

 @Override
 public void onCreate(SQLiteDatabase db) {
  
  final String CREATE_QUERY = "CREATE TABLE IF NOT EXISTS "+DOGS_TABLE+
    "("+
    "id INTEGER primary key autoincrement,"+
    "name TEXT,"+
    "age FLOAT"+
    ")";
  
  db.execSQL(CREATE_QUERY);
  
 }

 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  // TODO Auto-generated method stub
  
  final String DROP_QUERY = "DROP TABLE IF EXISTS "+DOGS_TABLE;
  db.execSQL(DROP_QUERY);
  
  // CALL AGAIN THE onCreate METHOD
  this.onCreate(db);
  
 }
 /**
  * Get the records
  * @return
  */
 protected Dog[] getRecords(){
  
  final List<Dog> dogs = new ArrayList<Dog>();
  final String SELECT_QUERY = "SELECT * FROM "+DOGS_TABLE;
  final Cursor cursor = db.rawQuery(SELECT_QUERY, null);
  
  if(cursor.moveToFirst()){
   do{
    
    final Dog dog = new Dog();
    
    dog.setId(cursor.getInt(cursor.getColumnIndex("id")));
    dog.setName(cursor.getString(cursor.getColumnIndex("name")));
    
    dogs.add(dog);
    
    
   }while(cursor.moveToNext());
  }
  
  // CLOSE CURSOR
  cursor.close();
  
  
  return dogs.toArray(new Dog[dogs.size()]);
 }
 
 /**
  * Insert data
  * @param name
  */
 protected void insert(String name){
  
  final ContentValues values = new ContentValues();
  values.put("name", name);
  
  db.insert(DOGS_TABLE, null, values);
  
 
  
 }
 /**
  * Update the record
  * @param id
  * @param name
  */
 protected void update(int id , String name){
  final ContentValues values = new ContentValues();
  values.put("name", name);
  db.update(DOGS_TABLE, values, "id = ?", new String[]{id+""});
 }
 /**
  * Delete record
  * @param id
  */
 protected void delete(int id){
  
  db.delete(DOGS_TABLE, "id = ?", new String[]{id+""});
 }

}

Here is the code in PopupOfAction.java

package com.example.createdatabaseexample;

import android.app.Dialog;
import android.content.Context;
import android.view.View;
import android.widget.Button;
import android.widget.LinearLayout;
import android.widget.ListView;

public class PopupOfAction extends Dialog implements android.view.View.OnClickListener{

 final static String UPDATE = "Edit";
 final static String DELETE = "Delete";
 
 Context context;
 LinearLayout linearLayout;
 Button updateBtn;
 Button deleteBtn;
 Dog dog;
 
 
 public PopupOfAction(Context context,Dog dog) {
  super(context);
  
  
  this.setTitle("Select Action");
  
  this.dog = dog;
  this.context = context;
  
  
  // INSTANTIATE LAYOUTS
  
  linearLayout = new LinearLayout(context);
  linearLayout.setOrientation(LinearLayout.VERTICAL);
  
 
  updateBtn = new Button(context);
  deleteBtn = new Button(context);
  
  
  // SET TEXT AND TAG
  
  updateBtn.setText(UPDATE);
  updateBtn.setTag(UPDATE);
  
  deleteBtn.setText(DELETE);
  deleteBtn.setTag(DELETE);
  
  // SET CLICK LISTENER
  
  updateBtn.setOnClickListener(this);
  deleteBtn.setOnClickListener(this);
  
  // ADD TEXTVIEW TO LINEARLAYOUT
  
  linearLayout.addView(updateBtn);
  linearLayout.addView(deleteBtn);
  
  // SET CONTENT VIEW
  this.setContentView(linearLayout);
  
 }


 @Override
 public void onClick(View view) {
  
  
  if(view.getTag().toString().equalsIgnoreCase(UPDATE)){
   // UPDATE DATA
   new PopupOfUpdateOrAdd((MainActivity)context, dog).show();
   
  }else{
   // DELETE DATA
   // INSTANTIATE DB HELPER
   MainActivity mainActivity = (MainActivity) context;
   MyDatabaseHelper dbHelper = new MyDatabaseHelper(context);
   dbHelper.delete(dog.getId());
   
   
   // GET ALL RECORD
   Dog[] dogs = dbHelper.getRecords();
   
   dbHelper.db.close();
   // REMOVE LISTVIEW FROM LINEARLAYOUT
   mainActivity.linearLayout.removeView(mainActivity.listView);

   // REINSTANTIATE LISTVIEW
   mainActivity.listView = new ListView(mainActivity);
   mainActivity.listView.setOnItemClickListener(mainActivity);

   // SET ADAPTER
   mainActivity.listView.setAdapter(new CustomArrayAdapter(mainActivity,
     dogs));

   // ADD LISTVIEW TO LINEARLAYOUT
   mainActivity.linearLayout.addView(mainActivity.listView);
   
   
   
  }
  
  // DISMISS
  this.dismiss();
 }

}

Here is the code in PopupOfUpdateOrAdd.java.

package com.example.createdatabaseexample;

import android.app.AlertDialog;
import android.content.DialogInterface;
import android.util.Log;
import android.widget.EditText;
import android.widget.ListView;

public class PopupOfUpdateOrAdd extends AlertDialog.Builder implements DialogInterface.OnClickListener {

 MainActivity mainActivity;
 boolean isUpdate = false;
 EditText nameEditText;
 Dog dog;
 /**
  * Constructor for adding record
  * @param mainActivity
  */
 public PopupOfUpdateOrAdd(MainActivity mainActivity) {
  super(mainActivity);
  
  
  this.mainActivity = mainActivity;
  
  this.setTitle("Add New Record");
  
  this.nameEditText = new EditText(mainActivity);
  this.nameEditText.setHint("Enter name");
  
  this.setView(nameEditText);
  
 
  this.setPositiveButton("Add", this);
  this.setNegativeButton("Cancel", this);
  
 }

 /**
  * Constructor for updating record
  * @param mainActivity
  * @param dog
  */
 public PopupOfUpdateOrAdd(MainActivity mainActivity,Dog dog) {
  super(mainActivity);
  this.setTitle("Update Record");
  
  this.isUpdate = true;
  
  this.mainActivity = mainActivity;
  
  this.dog = dog;
  
  this.nameEditText = new EditText(mainActivity);
  this.nameEditText.setHint("Enter name");
  this.nameEditText.setText(dog.getName());
  
  this.setView(nameEditText);
  
 
  this.setPositiveButton("Update", this);
  this.setNegativeButton("Cancel", this);
  
 }
 

 @Override
 public void onClick(DialogInterface dialog, int which) {
  
  switch (which) {
  case DialogInterface.BUTTON_POSITIVE:
   
    if(isUpdate){
     // UPDATE
     updatingData();
     
    }else{
     // INSERT
     
     insertingRecord();
     
    }
   
    
   break;
  case DialogInterface.BUTTON_NEGATIVE:
    Log.e("negative","negative");
   break; 
  default:
   break;
  }
  
  
 }
 /**
  * 
  */
 private void insertingRecord(){
  // GET STRING IN INPUTTEXT
  String name = nameEditText.getText().toString();

  // INSTANTIATE DB HELPER
  MyDatabaseHelper dbHelper = new MyDatabaseHelper(mainActivity);

  // INSERT THE RECORD
  dbHelper.insert(name);

  // GET ALL RECORD
  Dog[] dogs = dbHelper.getRecords();

  // CLOSE DATABASE
  dbHelper.db.close();

  // REMOVE LISTVIEW FROM LINEARLAYOUT
  mainActivity.linearLayout.removeView(mainActivity.listView);

  // REINSTANTIATE LISTVIEW
  mainActivity.listView = new ListView(mainActivity);

  mainActivity.listView.setOnItemClickListener(mainActivity);
  
  // SET ADAPTER
  mainActivity.listView.setAdapter(new CustomArrayAdapter(mainActivity,dogs));

  // ADD LISTVIEW TO LINEARLAYOUT
  mainActivity.linearLayout.addView(mainActivity.listView);
 }
 /**
  * 
  */
 private void updatingData(){
  
  int id = dog.getId();
  String name = nameEditText.getText().toString();

  // INSTANTIATE DB HELPER
  MyDatabaseHelper dbHelper = new MyDatabaseHelper(mainActivity);
  
  // UPDATE
  dbHelper.update(id, name);
  
  
  // GET ALL RECORD
  Dog[] dogs = dbHelper.getRecords();

  // CLOSE DATABASE
  dbHelper.db.close();

  // REMOVE LISTVIEW FROM LINEARLAYOUT
  mainActivity.linearLayout.removeView(mainActivity.listView);

  // REINSTANTIATE LISTVIEW
  mainActivity.listView = new ListView(mainActivity);
  
  mainActivity.listView.setOnItemClickListener(mainActivity);

  // SET ADAPTER
  mainActivity.listView.setAdapter(new CustomArrayAdapter(mainActivity,dogs));

  // ADD LISTVIEW TO LINEARLAYOUT
  mainActivity.linearLayout.addView(mainActivity.listView);
 }

}

Download Source Code.

Hope it help..