Inser,Update Delete of SQLite Database in Android

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" android:gravity="center">

<Button android:id="@+id/insert" android:layout_width="wrap_content"

android:layout_height="wrap_content" android:text="Insert"

android:textStyle="bold" />

<Button android:id="@+id/update" android:layout_width="wrap_content"

android:layout_height="wrap_content" android:text="Update"

android:textStyle="bold" />

</LinearLayout>

insert.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" android:gravity="center">

<TextView android:layout_width="50dip" android:layout_height="wrap_content"

android:text="Name" />

<EditText android:id="@+id/name" android:layout_width="90dip"

android:layout_height="wrap_content" android:background="@android:drawable/editbox_background_normal" />

<TextView android:layout_width="50dip" android:layout_height="wrap_content"

android:text="Password" android:layout_marginTop="15dip" />

<EditText android:id="@+id/pass" android:layout_width="90dip"

android:layout_height="wrap_content" android:background="@android:drawable/editbox_background_normal" />

<TextView android:layout_width="50dip" android:layout_height="wrap_content"

android:text="city" android:layout_marginTop="15dip" />

<EditText android:id="@+id/city" android:layout_width="90dip"

android:layout_height="wrap_content" android:background="@android:drawable/editbox_background_normal" />

<TextView android:layout_width="50dip" android:layout_height="wrap_content"

android:text="Country" android:layout_marginTop="15dip" />

<EditText android:id="@+id/country" android:layout_width="90dip"

android:layout_height="wrap_content" android:background="@android:drawable/editbox_background_normal" />

<TextView android:layout_width="50dip" android:layout_height="wrap_content"

android:text="postal" android:layout_marginTop="15dip" />

<EditText android:id="@+id/postal" android:layout_width="90dip"

android:layout_height="wrap_content" android:background="@android:drawable/editbox_background_normal" />

<Button android:id="@+id/insert" android:layout_width="wrap_content"

android:layout_height="wrap_content" android:text="Insert"

android:textStyle="bold" />

</LinearLayout>

update.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" android:gravity="center">

<TextView android:layout_width="50dip" android:layout_height="wrap_content"

android:text="Enter ID :" android:textStyle="bold" />

<EditText android:id="@+id/idname" android:layout_width="90dip"

android:layout_height="wrap_content" android:background="@android:drawable/editbox_background_normal" />

<Button android:id="@+id/search" android:layout_width="wrap_content"

android:layout_height="wrap_content" android:text="Search"

android:textStyle="bold" />

<Button android:id="@+id/delete" android:layout_width="wrap_content"

android:layout_height="wrap_content" android:text="Delete"

android:textStyle="bold" />

<TextView android:layout_width="50dip" android:layout_height="wrap_content"

android:text="Name" android:layout_marginTop="20dip" />

<EditText android:id="@+id/name" android:layout_width="90dip"

android:layout_height="wrap_content" android:background="@android:drawable/editbox_background_normal" />

<TextView android:layout_width="50dip" android:layout_height="wrap_content"

android:text="Password" />

<EditText android:id="@+id/pass" android:layout_width="90dip"

android:layout_height="wrap_content" android:background="@android:drawable/editbox_background_normal" />

<TextView android:layout_width="50dip" android:layout_height="wrap_content"

android:text="city" />

<EditText android:id="@+id/city" android:layout_width="90dip"

android:layout_height="wrap_content" android:background="@android:drawable/editbox_background_normal" />

<TextView android:layout_width="50dip" android:layout_height="wrap_content"

android:text="Country" />

<EditText android:id="@+id/country" android:layout_width="90dip"

android:layout_height="wrap_content" android:background="@android:drawable/editbox_background_normal" />

<TextView android:layout_width="50dip" android:layout_height="wrap_content"

android:text="postal" />

<EditText android:id="@+id/postal" android:layout_width="90dip"

android:layout_height="wrap_content" android:background="@android:drawable/editbox_background_normal" />

<Button android:id="@+id/update" android:layout_width="wrap_content"

android:layout_height="wrap_content" android:text="Update"

android:textStyle="bold" />

</LinearLayout>

Main.java

package com.databse.example;

import android.app.Activity;

import android.content.Intent;

import android.os.Bundle;

import android.view.View;

import android.view.View.OnClickListener;

import android.widget.Button;

public class Main extends Activity {

private Button mInsert,mUpdate,mDelete,mSearch;

    /** Called when the activity is first created. */

    @Override

    public void onCreate(Bundle savedInstanceState) {

        super.onCreate(savedInstanceState);

        setContentView(R.layout.main);

        

        mInsert = (Button)findViewById(R.id.insert);

        mUpdate = (Button)findViewById(R.id.update);

        mDelete = (Button)findViewById(R.id.delete);

        mSearch = (Button)findViewById(R.id.search);

        mInsert.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View arg0) {

// TODO Auto-generated method stub

Intent intent = new Intent(Main.this, Insert.class);

startActivity(intent);

}

});

        mUpdate.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View arg0) {

// TODO Auto-generated method stub

Intent mIntent= new Intent(Main.this, Update.class);

startActivity(mIntent);

}

});

    }

}

Insert.java

package com.databse.example;

import android.app.Activity;

import android.os.Bundle;

import android.util.Log;

import android.view.View;

import android.view.View.OnClickListener;

import android.widget.Button;

import android.widget.EditText;

public class Insert extends Activity {

private SimpleDBAdapter mDbHelper;

EditText name, pass, country, city, postal;

/** Called when the activity is first created. */

@Override

public void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.insert);

mDbHelper = new SimpleDBAdapter(this);

mDbHelper.Open();

name = (EditText) findViewById(R.id.name);

pass = (EditText) findViewById(R.id.pass);

city = (EditText) findViewById(R.id.city);

country = (EditText) findViewById(R.id.country);

postal = (EditText) findViewById(R.id.postal);

Button submit = (Button) findViewById(R.id.insert);

submit.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View v) {

String username = name.getText().toString().trim();

String password = pass.getText().toString().trim();

String cityname = city.getText().toString().trim();

String countryname = country.getText().toString().trim();

String zipcode = postal.getText().toString().trim();

int zip_code = Integer.parseInt(zipcode);

mDbHelper.insertdata(username, password, cityname, countryname,

zip_code);

}

});

}

}

Update.java

//Search,Update,Delete Function

package com.databse.example;

import android.app.Activity;

import android.database.Cursor;

import android.os.Bundle;

import android.util.Log;

import android.view.View;

import android.view.View.OnClickListener;

import android.widget.Button;

import android.widget.EditText;

import android.widget.SimpleCursorAdapter;

public class Update extends Activity {

private SimpleDBAdapter mDbHelper;

EditText idname, name, pass, country, city, postal;

SimpleCursorAdapter cursorAdapter;

Cursor cursor;

Directory singleRecord;

String mName,mPass,mCountry,mCity; 

int mPostal;

String mName1,mPass1,mCountry1,mCity1; 

String mPostal1;

@Override

public void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.update);

mDbHelper = new SimpleDBAdapter(this);

mDbHelper.Open();

idname = (EditText) findViewById(R.id.idname);

name = (EditText) findViewById(R.id.name);

pass = (EditText) findViewById(R.id.pass);

city = (EditText) findViewById(R.id.city);

country = (EditText) findViewById(R.id.country);

postal = (EditText) findViewById(R.id.postal);

Button mSearch = (Button) findViewById(R.id.search);

Button mUpdate = (Button) findViewById(R.id.update);

Button mDelete = (Button)findViewById(R.id.delete);

mSearch.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View arg0) {

// TODO Auto-generated method stub

String id = idname.getText().toString();

int idInt = Integer.parseInt(id);

singleRecord = mDbHelper.getreccordfromid(idInt);

mName = singleRecord.getName();

mPass = singleRecord.getPass();

mCity = singleRecord.getCity();

mCountry = singleRecord.getCountry();

mPostal = singleRecord.getZip();

// Log.v("log_tag", "get data : " +mName );

name.setText(mName);

pass.setText(mPass);

city.setText(mCity);

country.setText(mCountry);

postal.setText(String.valueOf(mPostal)); 

}

});

mUpdate.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View arg0) {

// TODO Auto-generated method stub

String id = idname.getText().toString();

int idInt = Integer.parseInt(id);

// singleRecord = mDbHelper.getreccordfromid(idInt);

mName1 = name.getText().toString();

mPass1 = pass.getText().toString();

mCity1 = city.getText().toString();

mCountry1 = country.getText().toString();

mPostal1 = postal.getText().toString();

mDbHelper.updateNote(idInt, mName1, mPass1, mCity1, mCountry1, Integer.valueOf(mPostal1));

Log.v("log_tag", "On Update CLick : " +mName1+ " " +mPass1+ " " +mCity1+ " " +mCountry1+ " " +mPostal1);

}

});

mDelete.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View v) {

// TODO Auto-generated method stub

String id = idname.getText().toString();

int idInt = Integer.parseInt(id);

mDbHelper.deleteNote(idInt);

Log.v("log_tag", "id is : " +idInt);

}

});

}

}

SimpleDBAdapter.java

package com.databse.example;

import android.content.ContentValues;

import android.content.Context;

import android.database.Cursor;

import android.database.SQLException;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

import android.util.Log;

public class SimpleDBAdapter {

public static final String USERNAME = "username";

public static final String PASSWORD = "password";

public static final String CITY = "city";

public static final String COUNTRY = "country";

public static final String ZIP = "zip";

public static final String KEY_ROWID = "_id";

private static final String TAG = "SimpleDBAdapter";

private DataBaseHelper mDbhelper;

private SQLiteDatabase mDb;

private static final String DATABASE_CREATE = "create table submit(_id integer primary key autoincrement,"

+ "username text not null,password text not null,city text not null,"

+ "country text not null,zip integer not null)";

private static final String DATABASE_NAME = "info";

private static final String DATABASE_TABLE = "submit";

private static final int DATABESE_VERSION = 2;

private final Context mCtx;

private static class DataBaseHelper extends SQLiteOpenHelper {

DataBaseHelper(Context context) {

super(context, DATABASE_NAME, null, DATABESE_VERSION);

// TODO Auto-generated constructor stub

}

@Override

public void onCreate(SQLiteDatabase db) {

// TODO Auto-generated method stub

db.execSQL(DATABASE_CREATE);

}

@Override

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

// TODO Auto-generated method stub

Log.w(TAG, "Upgrading database from version " + oldVersion + " to "

+ newVersion + ", which will destroy all old data");

db.execSQL("DROP TABLE IF EXISTS submit");

onCreate(db);

}

}

public SimpleDBAdapter(Context ctx) {

this.mCtx = ctx;

}

public SimpleDBAdapter Open() throws SQLException {

mDbhelper = new DataBaseHelper(mCtx);

mDb = mDbhelper.getWritableDatabase();

return this;

}

public void close() {

mDbhelper.close();

}

public long insertdata(String username, String password, String city,

String country, int zip) {

ContentValues initialvalues = new ContentValues();

initialvalues.put(USERNAME, username);

initialvalues.put(PASSWORD, password);

initialvalues.put(CITY, city);

initialvalues.put(COUNTRY, country);

initialvalues.put(ZIP, zip);

return mDb.insert(DATABASE_TABLE, null, initialvalues);

}

public boolean deleteNote(long rowId) {

return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;

}

public Cursor fetchAllNotes() {

return mDb.query(DATABASE_TABLE, new String[] { KEY_ROWID, USERNAME,

PASSWORD, CITY, COUNTRY, ZIP }, null, null, null, null, null);

}

public Cursor fetchNote(long rowId) throws SQLException {

Cursor mCursor = mDb.query(true, DATABASE_TABLE, new String[] {

KEY_ROWID, USERNAME, PASSWORD, CITY, COUNTRY, ZIP }, KEY_ROWID

+ "=" + rowId, null, null, null, null, null);

if (mCursor != null) {

mCursor.moveToFirst();

}

return mCursor;

}

public boolean updateNote(long rowId, String username, String password,

String city, String country, int zip) {

ContentValues args = new ContentValues();

args.put(USERNAME, username);

args.put(PASSWORD, password);

args.put(CITY, city);

args.put(COUNTRY, country);

args.put(ZIP, zip);

return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;

}

public Directory getreccordfromid(int id) {

Log.v("log_tag", "id in database : " + id);

Directory dir = new Directory();

Cursor cur = mDb.query(DATABASE_TABLE, new String[] { KEY_ROWID,

USERNAME, PASSWORD, CITY, COUNTRY, ZIP }, KEY_ROWID + "='" + id

+ "'", null, null, null, null);

// Cursor cur=mDb.query("submit", new String[]{ USERNAME,

// PASSWORD, CITY, COUNTRY, ZIP }, KEY_ROWID + "='" + id + "'", null,

// null, null, null);

for (cur.isBeforeFirst(); cur.moveToNext(); cur.isAfterLast()) {

String fname = cur.getString(cur.getColumnIndex(KEY_ROWID));

Log.v("log_tag", "id: database" + fname);

dir.setName(cur.getString(cur.getColumnIndex(USERNAME)));

dir.setPass(cur.getString(cur.getColumnIndex(PASSWORD)));

dir.setCity(cur.getString(cur.getColumnIndex(CITY)));

dir.setCountry(cur.getString(cur.getColumnIndex(COUNTRY)));

dir.setZip(cur.getInt(cur.getColumnIndex(ZIP)));

Log.v("log_tag",

"ZIp code " + cur.getString(cur.getColumnIndex(USERNAME)));

}

cur.close();

return dir;

}

public void updateRow(long rowId, String name, String pass, String city,

String Country, int zip) {

ContentValues args = new ContentValues();

args.put(USERNAME, name);

args.put(PASSWORD, pass);

args.put(CITY, city);

args.put(COUNTRY, Country);

args.put(ZIP, zip);

mDb.update(DATABASE_TABLE, args, "_id=" + rowId, null);

}

}