Friday, July 8, 2011

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);
}

}

3 comments:

  1. i get error is this part?
    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);

    ReplyDelete
    Replies
    1. Hi, i thing ur missing column name as same u used

      Delete