Insert And Retrieve Data in Database SQLite

Hello All,

Today I am going to share the database connection in simple insert and delete actions in database.
In android for database functionality Sqlite database browser is used.

So let's move to our tasks that is to create ,insert and retrieve the data from DB..so to do this we have to follow following steps:

-> create a Database in Sqlite browser named employee.db

->create a table in employee db named user  table. this table will have following fields: id,name,city
and all are String type.

->Now in Employee.db ,the user table having three columns : id,name,city has been created
(if you want ,you can insert some records manually).

->after all these above steps save this db file into you projects Assets folder

Let's  move to the coding part:

->first design a layout having two buttons one is for Retrieve and another is for Insert and also will have a listview to retrieve the result. I have named it db_layout.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"
    android:background="#C0C0C0" >

    <Button
        android:id="@+id/btn_retrieve"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Retrieve" />

    <Button
        android:id="@+id/btn_insert"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentTop="true"
        android:layout_marginLeft="36dp"
        android:layout_toRightOf="@+id/btn_retrieve"
        android:text="Insert" />

    <ListView
        android:id="@+id/list_dblayout"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_below="@+id/btn_retrieve"
        android:layout_marginTop="23dp"
      android:listSelector="@android:color/transparent" 
      android:divider="@android:color/transparent"
      android:cacheColorHint="@android:color/transparent">
    </ListView>

</RelativeLayout>


->Now copy and paste the dbhandler class from below.This class contains the codes to connect with the Db and also some methods to retrieve the data or insert the data.I have named it  DatabaseHandler.java


import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.List;

import android.annotation.SuppressLint;
import android.app.Activity;
import android.content.ContentValues;
import android.content.res.AssetManager;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHandler extends SQLiteOpenHelper {

public static final String TAG = "DatabaseHandler";

private SQLiteDatabase db;

private Activity activity;
String mydate;
     private String DB_PATH = "/data/data/com.example.demo" + "/databases/";

private static String DB_NAME = "employee.db";


public DatabaseHandler(Activity activity) throws IOException {
super(activity, DB_NAME, null, 1);
this.activity = activity;
boolean dbexist = checkdatabase();
if (dbexist) {
Log.d("Trong", "Database exists");
opendatabase();
} else {
System.out.println("Database doesn't exist");
createdatabase();
}
}

public void createdatabase() throws IOException {
boolean dbexist = checkdatabase();
if (dbexist) {
// System.out.println(" Database exists.");
} else {
this.getReadableDatabase();
try {
copydatabase();
} catch (IOException e) {
e.printStackTrace();
}
}
}

private boolean checkdatabase() {
// SQLiteDatabase checkdb = null;
boolean checkdb = false;
try {
String myPath = DB_PATH + DB_NAME;
Log.d("Trong", "DB_PATH + DB_NAME " + DB_PATH + DB_NAME);
File dbfile = new File(myPath);
// checkdb =
// SQLiteDatabase.openDatabase(myPath,null,SQLiteDatabase.OPEN_READWRITE);
checkdb = dbfile.exists();
} catch (SQLiteException e) {
Log.d("Trong", "Database doesn't exist");
}
return checkdb;
}


private void copydatabase() throws IOException {
        AssetManager am = activity.getAssets();
        OutputStream os = new FileOutputStream(DB_PATH + DB_NAME);
        byte[] b = new byte[1024];
        String[] files = am.list("");
        Arrays.sort(files);
        int r;
      
            InputStream is = am.open("employee.db");
            while ((r = is.read(b)) != -1) {
                os.write(b, 0, r);
            }
            Log.i("BABY_DATABASE_HELPER", "Copying the database (part " 
                    + " of 9)");
            is.close();
        
        os.close();
    }

public void opendatabase() throws SQLException {
// Open the database
String mypath = DB_PATH + DB_NAME;
db = SQLiteDatabase.openDatabase(mypath, null,
SQLiteDatabase.OPEN_READWRITE);

}

public synchronized void close() {
if (db != null) {
db.close();
}
super.close();
}




@Override
public void onCreate(SQLiteDatabase db) {

}

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

}

public void deleteAll()
{
   SQLiteDatabase db = this.getWritableDatabase();
   db.delete("user",null,null);
   db.close();
}

public int insertvalue(String id, String str_name, String str_city) {
Log.e("InsertValues","sdk");
ContentValues cvalue=new ContentValues();
cvalue.put("id",id);
cvalue.put("name",str_name);
cvalue.put("city",str_city);

           db.insert("user", null, cvalue);
return 1;
}

public ArrayList<DbEntity> getInputMonths() {
 
 ArrayList<DbEntity> ittoentity = new ArrayList<DbEntity>();
 
 String selectQuery = "SELECT  * FROM "+"user";
 Log.d("rawquery", "inputs"+selectQuery);
 Cursor cursor = db.rawQuery(selectQuery, null);
        if (cursor.moveToFirst()) {
  do {
  DbEntity itto = new DbEntity();
  itto.set_name(cursor.getString(1));
   itto.set_city(cursor.getString(2));
   ittoentity.add(itto);
  } while (cursor.moveToNext());
 }

 return ittoentity;
}

->Now we have to write the code to start activity and click on buttons etc. means MainActivity class.
I have named this class DbActivity.java. And following is the code: 

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;

import com.demo.db.DatabaseHandler;
import com.demo.db.DbEntity;



import android.app.Activity;
import android.app.Dialog;
import android.database.SQLException;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.view.Window;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.Toast;

public class DbActivity extends  Activity{

Button btn_retreive,btn_insert;
DatabaseHandler db;
EditText edttxt_name, edttxt_city ;
Dialog dialog;
     Button btn_submit;
     ListView list ;
@Override
protected void onCreate(Bundle savedInstanceState) {
// TODO Auto-generated method stub
super.onCreate(savedInstanceState);
setContentView(R.layout.db_layout);

btn_retreive = (Button)findViewById(R.id.btn_retrieve);
btn_insert= (Button)findViewById(R.id.btn_insert);
list = (ListView)findViewById(R.id.list_dblayout);

btn_retreive.setOnClickListener(new View.OnClickListener() {

@Override
public void onClick(View v) {
RetriveDataFromDB();
}
});
btn_insert.setOnClickListener(new View.OnClickListener() {

@Override
public void onClick(View v) {
dialog = new Dialog(DbActivity.this);
           dialog.getWindow();
         dialog.requestWindowFeature(Window.FEATURE_NO_TITLE);
           dialog.setContentView(R.layout.insert_dialog_layout);
          dialog.setCancelable(true);
           dialog.show();
           
     edttxt_name = (EditText)dialog.findViewById(R.id.edttxt_name_insertdialog);
    edttxt_city = (EditText)dialog.findViewById(R.id.edttxt_city_insertdialog);
           
           Button btn_submit = (Button)dialog.findViewById(R.id.btn_insert_insertdialog);
           
           btn_submit.setOnClickListener(new View.OnClickListener() {

@Override
public void onClick(View v) {
String str_name = "",str_city="";

str_city = edttxt_city.getText().toString();
str_name = edttxt_name.getText().toString();

if (str_city.equals("")||(str_name.equals(""))) {
Toast.makeText(DbActivity.this,"Please fill all the fields!!",Toast.LENGTH_LONG).show();
}
else
{
insertIntoDb("11",str_name,str_city);
}
}

private void insertIntoDb(String id,
String str_name, String str_city) {
try {
db = new DatabaseHandler(DbActivity.this);
} catch (IOException e) {
e.printStackTrace();
}
db.opendatabase();

int i = db.insertvalue(id, str_name, str_city);

if (i==1) {
Toast.makeText(DbActivity.this,"Infomation saved succesfully.",Toast.LENGTH_LONG).show();
dialog.dismiss();

}
else
{
Toast.makeText(DbActivity.this,"Something went wrong.Please try again later.",Toast.LENGTH_LONG).show();
dialog.dismiss();
}

try {
db.close();
} catch (Exception e) {
e.printStackTrace();
}

}
});
           
}
});
}
protected void RetriveDataFromDB() {
try {
db = new DatabaseHandler(DbActivity.this);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
   db.createdatabase();

  } catch (IOException ioe) {
   throw new Error("Unable to create database");
  }
  try {
   db.opendatabase();
  } catch (SQLException sqle) {
   throw sqle;
  }
  
  ArrayList<DbEntity>  menuItems = new  ArrayList<DbEntity>();
  menuItems.clear();
 
ArrayList<DbEntity> contacts = db.getInputMonths();  
 
 
Log.e("Array",">>"+menuItems.size());
list.setAdapter(null);
list.setAdapter(new DbRetrieveAdapter(DbActivity.this,contacts));
}

}

->In the above class .I have write code to click on insert ,retrieve button ..call functions from DatabaseHandler class to insert data and to retrive data in listview .I have used a dialog on click on Insert button ,this dialog will have two edit boxes which are used to insert name,city value from the user, the design is for the dialog to insert the values is as follows: insert_dialog_layout.xml


<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="300sp"
    android:layout_height="200sp"
    android:orientation="vertical" 
    android:background="#ff9900">

    <EditText
        android:id="@+id/edttxt_name_insertdialog"
        android:layout_width="match_parent"
        android:layout_height="50sp"
       android:layout_marginLeft="8sp"
       android:layout_marginRight="8sp"
       android:hint="Name"
       android:singleLine="true"
       android:layout_marginTop="15sp"
       android:textColor="#000000"
       >

    </EditText>
    
        <EditText
        android:id="@+id/edttxt_city_insertdialog"
        android:layout_width="match_parent"
        android:layout_height="50sp"
       android:layout_marginLeft="8sp"
       android:layout_marginRight="8sp"
       android:hint="City"
       android:singleLine="true"
       android:layout_marginTop="15sp"
       android:textColor="#000000"
       >

    </EditText>

        <Button
            android:id="@+id/btn_insert_insertdialog"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_gravity="center_horizontal"
               android:layout_marginTop="15sp"
            android:text="INSERT" />

</LinearLayout>

->Next I have used the adapter to retrieve the value from the DB in listview the design for the list view item is as follows:  layout_grid_item.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" >
    

    <LinearLayout android:layout_width="match_parent"
        android:layout_height="60sp"
        android:background="#FFFFFF"
        android:layout_margin="5sp"
        android:orientation="vertical">

        <TextView
            android:id="@+id/txt_name_griditemlayout"
            android:layout_width="match_parent"
            android:layout_marginLeft="5sp"
            android:layout_marginRight="5sp"
            android:layout_marginTop="5sp"
            android:layout_height="wrap_content"
            android:text="Name"
              android:textColor="#000000"
              android:textSize="13sp" />

        <TextView
            android:id="@+id/txt_city_griditemlayout"
           
            android:layout_height="wrap_content"
            android:text="City"
            android:textColor="#000000"
            android:textSize="13sp"
             android:layout_width="match_parent"
            android:layout_marginLeft="5sp"
            android:layout_marginRight="5sp"
         
            android:layout_marginTop="10sp" />

    </LinearLayout>

</LinearLayout>


-> The DbRetrieveAdapter.java will also be needed : 

import java.util.ArrayList;
import java.util.HashMap;

import com.demo.db.DbEntity;

import android.app.Activity;
import android.app.Dialog;
import android.content.Context;
import android.graphics.Bitmap;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.Button;
import android.widget.ImageView;
import android.widget.RelativeLayout;
import android.widget.TextView;


public class DbRetrieveAdapter extends BaseAdapter {

    private LayoutInflater mLayoutInflater = null;
private Activity activity;
private ViewHolder holder;
private String name,cat_id,str_flag="";

ArrayList<DbEntity>  Show;
 int i = 0 ;


private class ViewHolder {
public TextView tvName,tvCity;

}


public DbRetrieveAdapter(Activity activity2,
ArrayList<DbEntity> contacts) {
this.activity = activity2;
this.Show = contacts;
mLayoutInflater = (LayoutInflater)activity.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
 
}

@Override
public int getCount() {
return Show.size();
}

@Override
public Object getItem(int position) {
return Show.get(position);
}

@Override
public long getItemId(int position) {
return position;
}

@Override
public View getView(final int position, View convertView, ViewGroup parent) {

if (convertView == null) {
convertView = mLayoutInflater.inflate(R.layout.layout_grid_item, null);

holder = new ViewHolder();
holder.tvName = (TextView) convertView.findViewById(R.id.txt_name_griditemlayout);
holder.tvCity= (TextView) convertView.findViewById(R.id.txt_city_griditemlayout);


convertView.setTag(holder);
} else {
holder = (ViewHolder) convertView.getTag();
}

holder.tvName.setText("Name : "+Show.get(position).get_name()); 
holder.tvCity.setText("City : "+Show.get(position).get_city());

return convertView;
}

}


->Now in last DbEntity.java:  

public class DbEntity {

private String _name,_city;
public DbEntity() {
super();
}
        public DbEntity(String _name,String _city) {
super();
this._name = _name;
this._city = _city;

}
       public String get_name() {
return _name;
}
       public void set_name(String _name) {
this._name = _name;
}
        public String get_city() {
return _city;
}
public void set_city(String _city) {
this._city = _city;
}
}

->This is the code to insert and retrieve the data from the DB using SQLite .Below is the screen shot: