Database Demo

Here I am sharing code for using Database (SQLite) in android and I hope this post will be useful for newbees to learn android widgets.


Here I am going to use Expandable List view to populate Table Values to Activity.

This Sample is Developed in Android 2.3.3 SDK.


main.xml


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

<TextView android:layout_height="wrap_content"
android:id="@+id/Name"
android:layout_width="fill_parent"
android:text="Name">
</TextView>

<EditText android:layout_height="wrap_content"
android:layout_width="fill_parent"
android:id="@+id/EmpName"
android:inputType="text|textCapCharacters|textCapWords|textCapSentences|textAutoCorrect|textAutoComplete">
</EditText>

<TextView android:layout_height="wrap_content"
android:layout_width="fill_parent"
android:id="@+id/Age"
android:text="Age">
</TextView>

<EditText android:layout_height="wrap_content"
android:id="@+id/EmpAge"
android:inputType="number|numberSigned|numberDecimal"
android:layout_width="fill_parent">
</EditText>

<RelativeLayout android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:id="@+id/RelativeLayout">

<Button android:text="InsertValues"
android:id="@+id/AddDet"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginLeft="10px">
</Button>

<Button android:layout_height="wrap_content"
android:layout_width="wrap_content"
android:layout_toRightOf="@+id/AddDet"
android:text="ViewDetails"
android:id="@+id/ViewDet">
</Button>

</RelativeLayout>


</LinearLayout>



Main Activity (SQLiteTest.java)

This activity gets details from user and popups an alert dialog for successfull/failure in DB insertion and toast for the value inserted in DB. When user taps view details button displays Expandable list view activity.


import android.app.Activity;
import android.app.AlertDialog;
import android.app.Notification;
import android.app.NotificationManager;
import android.app.PendingIntent;
import android.content.Context;
import android.content.DialogInterface;
import android.content.Intent;
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 SQLiteTest extends Activity {
    /** Called when the activity is first created. */

    protected Button insertButton, viewButton;
    protected EditText nameText, ageText;
    DBHelper helper;
    Context mContext = this;
    String dispData;

   
    protected OnClickListener onClickListener = new OnClickListener() {

        @Override
        public void onClick(View v) {
            // TODO Auto-generated method stub
            if (v.getId() == R.id.AddDet) {
                Toast.makeText(
                        getApplicationContext(),
                        "Name  " + nameText.getText().toString() + "Age "
                                + ageText.getText().toString(),
                        Toast.LENGTH_LONG).show();
                Employee emp=new Employee(nameText.getText().toString(), Integer.parseInt(ageText.getText().toString()));
                int status =helper.insertTable(emp);
                if (status != -1) {
                    AlertDialog.Builder alert = new AlertDialog.Builder(
                            mContext);
                    alert.setMessage("Table Values Inserted");
                    alert.setNeutralButton("OK",
                            new DialogInterface.OnClickListener() {

                       
                       
                                @Override
                                public void onClick(DialogInterface dialog,
                                        int which) {
                                    // TODO Auto-generated method stub
                                    dialog.cancel();
                                }
                            });
                    AlertDialog al = alert.create();
                    al.show();
                } else {
                    AlertDialog.Builder alert = new AlertDialog.Builder(
                            mContext);
                    alert.setMessage("Table Values not Inserted");
                    alert.setNeutralButton("OK",
                            new DialogInterface.OnClickListener() {

                                @Override
                                public void onClick(DialogInterface dialog,
                                        int which) {
                                    // TODO Auto-generated method stub
                                    dialog.cancel();
                                }
                            });
                    AlertDialog ad = alert.create();
                    ad.show();
                }   
            } else if (v.getId() == R.id.ViewDet) {
                Intent intent=new Intent(SQLiteTest.this,sample.class);
                startActivity(intent);
            }

        }
    };

    protected void notifyTrigger(CharSequence Title, CharSequence msg) {
        CharSequence sequence = Title;
        CharSequence message = msg;
        NotificationManager nm = (NotificationManager) getSystemService(NOTIFICATION_SERVICE);
        Notification notification = new Notification(R.drawable.icon,
                "This is to Notify You", System.currentTimeMillis()) {
        };
        Intent notificationIntent = new Intent(this, SQLiteTest.class);
        PendingIntent pIntent = PendingIntent.getActivity(this, 0,
                notificationIntent, 0);
        notification.setLatestEventInfo(getApplicationContext(), sequence,
                message, pIntent);
        nm.notify(1, notification);
    }

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        helper = new DBHelper(getApplicationContext());
        // helper.createTable();
        insertButton = (Button) findViewById(R.id.AddDet);
        viewButton = (Button) findViewById(R.id.ViewDet);
        nameText = (EditText) findViewById(R.id.EmpName);
        ageText = (EditText) findViewById(R.id.EmpAge);
        insertButton.setOnClickListener(onClickListener);
        viewButton.setOnClickListener(onClickListener);

              
    }
}

DBConnection Class (DBHelper.java)

This class creates Database and tables and inserts the value from the user input taken from POJO


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.SQLiteFullException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHelper extends SQLiteOpenHelper {

    private static final String dbName = "SampleDB";
    public final String tableName="TestTable";
    public final String attribValue1="Name";
    public final String attribValue2="age";
    SQLiteTest sqltest;
    String[] nameVal;
    int[] ageVal;
    int size;
    int i=0;
   
    public DBHelper(Context context) {
        super(context, dbName, null, 33);
        sqltest = new SQLiteTest();
        // TODO Auto-generated constructor stub
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        String sql="";
        try {
            //sql ="create table if not exists " + tableName + "(" +attribValue1 + "varchar" + attribValue2+ "int(3))";
            sql="CREATE TABLE "+tableName+"(Name TEXT,age INTEGER)";
            db.execSQL(sql);
        } catch(SQLiteFullException e) {
            e.getMessage();
            Log.d("Test", sql);   
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub

    }
   
    public void createTable() {
        SQLiteDatabase db=this.getWritableDatabase();
        String query = "create table if not exists " + tableName + "("+ attribValue1 + "varchar(20)" + attribValue2    + "int(3))";
        db.execSQL(query);
        db.close();
    }
   
    public int insertTable(Employee emp){
        SQLiteDatabase db=this.getWritableDatabase();
        ContentValues cv=new ContentValues();
        cv.put("Name",emp.getName());
        cv.put("age", emp.getAge());
        int status=(int) db.insert(tableName, null, cv);
        db.close();
        return status;   
    }
   
    public List viewTable() {
        //String result="";
        List arrList=new ArrayList ();
        try {
            SQLiteDatabase db=this.getWritableDatabase();
            String query="select * from " + tableName;
            Log.d("query message  ", query);
            Cursor c = db.rawQuery(query, null);
             size=c.getCount();
            nameVal=new String[size];
            ageVal=new int[size];
           
            int col1 = c.getColumnIndex("Name");
            int col2 = c.getColumnIndex("age");
           
            c.moveToFirst();
            if (c != null) {
                do {
                    nameVal[i]=c.getString(col1);
                    ageVal[i]=c.getInt(col2);
                   
                   
                    //arrList.add(ageVal[i]+"");
                    //result=result+nameVal+" "+ageVal+" ";
                    System.out.println("Name     :"+nameVal[i]);
                    System.out.println("Age      :"+ageVal[i]);
                    i++;
                } while (c.moveToNext());
                arrList.add(nameVal);
                arrList.add(ageVal);
            }
           
            db.close();
        }catch(SQLiteFullException exp) {
             exp.getMessage();
             Log.d("Exception Cause", exp.getMessage());
         }
       
        return arrList;
       
    }

}


POJO for DB(Employee.java)

A POJO is simply a Java object that does not implement any special interfaces which is used to design simple Business Domains.

public class Employee {
   
    int _age;
    String _name;
   
    public Employee(String Name,int Age)
    {
        this._name=Name;
        this._age=Age;
    }
   
    public String getName()
    {
        return this._name;
    }
   
    public int getAge()
    {
        return this._age;
    }
   
    public void setName(String Name)
    {
        this._name=Name;
    }
    public void setAge(int Age)
    {
        this._age=Age;
    }
   

}

Activity to populate table values in Expandable List View


This Activity disaplays Expandable Listview that displays populated table values.

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import android.app.ExpandableListActivity;
import android.os.Bundle;
import android.widget.ExpandableListAdapter;
import android.widget.SimpleExpandableListAdapter;

public class sample extends ExpandableListActivity {
    private static final String NAME = "NAME";
    DBHelper helper;
    List dispData;
    Iterator it;
   
    String[] data;
    String[] name;
    int[] age;
    private ExpandableListAdapter mAdapter;
   
      
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        helper = new DBHelper(getApplicationContext());
        dispData=helper.viewTable();

        name=new String[dispData.size()];
       
        name=(String[])dispData.get(0);
        age=(int[]) dispData.get(1);
        for(int i=0;i<name.length;i++)
        {
            System.out.println("New data :"+name[i]);
        }
       
       /* data=new String[dispData.size()];
        it=dispData.iterator();
        i=0;
        while(it.hasNext()) {
            String value=it.next().toString();
            data[i]=value;
            i++;
        }*/
      
           
        List<Map<String, String>> groupData = new ArrayList<Map<String, String>>();
        List<List<Map<String, String>>> childData = new ArrayList<List<Map<String, String>>>();
      
        for (int i = 0; i < name.length; i++) {
            Map<String, String> curGroupMap = new HashMap<String, String>();
            groupData.add(curGroupMap);
            curGroupMap.put(NAME,"" +name[i]);
        
            List<Map<String, String>> children = new ArrayList<Map<String, String>>();
         
                Map<String, String> curChildMap = new HashMap<String, String>();
                children.add(curChildMap);
                curChildMap.put(NAME,"" +age[i]);
                                 
            childData.add(children);
          
        }
       
        // Set up our adapter
        mAdapter = new SimpleExpandableListAdapter(
                this,
                groupData,
                android.R.layout.simple_expandable_list_item_1,
                new String[] { NAME },
                new int[] { android.R.id.text1 },
                childData,
                android.R.layout.simple_expandable_list_item_2,
                new String[] { NAME },
                new int[] { android.R.id.text1 }
                );
        setListAdapter(mAdapter);
    }
    }