This Example explains the complete use of SQLITE database in android
- Inserting Data into Database
- Getting Data from Database
- Showing Data into a Listview (Using Simple Adapter with multiple lines)
- Deleting Data from Database
Check this video to see the working.
click here for Simple Database Example
Source code:
Create a new Android Projects and create these files in your project.
- code for main.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:background="#ff330044"
android:orientation="vertical" >
<TextView
android:id="@+id/textView1"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:background="#77336588"
android:text="Database Tutorial"
android:textAppearance="?android:attr/textAppearanceLarge" />
<TextView
android:id="@+id/textView2"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:layout_marginTop="5dp"
android:background="#88555555"
android:text="Name" />
<EditText
android:id="@+id/ed1"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:inputType="textPersonName"
android:maxLength="30"
android:singleLine="true" >
<requestFocus />
</EditText>
<TextView
android:id="@+id/textView3"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:background="#88555555"
android:text="Phone" />
<EditText
android:id="@+id/ed2"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:inputType="phone"
android:maxLength="10"
android:singleLine="true" />
<LinearLayout
android:id="@+id/linearLayout1"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:gravity="center_horizontal" >
<Button
android:id="@+id/submit_btn"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Submit" />
<Button
android:id="@+id/refresh_btn"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Refresh" />
</LinearLayout>
<ListView
android:id="@+id/dblist"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:layout_marginTop="5dp"
android:cacheColorHint="#ff330044" >
</ListView>
</LinearLayout>
- Code for custom.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:orientation="vertical" >
<TextView
android:id="@+id/col1tv"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:background="#88444444"
android:paddingLeft="10dp"
android:text="Large Text"
android:textAppearance="?android:attr/textAppearanceLarge" />
<TextView
android:id="@+id/col2tv"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:background="#88555555"
android:gravity="right"
android:paddingRight="10dp"
android:text="Medium Text"
android:textAppearance="?android:attr/textAppearanceMedium" />
</LinearLayout>
- Code for DBclass.java
package fun.droiding.dbtutorial;
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 DBclass {
public static final String KEY_ROWID = "_id";
public static final String KEY_COL1 = "col1";
public static final String KEY_COL2 = "col2";
private static final String DATABASE_NAME = "mydb";
private static final String DATABASE_TABLE = "mytable";
private static final int DATABASE_VERSION = 1;
private final Context ourContext;
private DbHelper dbh;
private SQLiteDatabase odb;
private static final String USER_MASTER_CREATE =
"CREATE TABLE IF NOT EXISTS " + DATABASE_TABLE+ "("
+ KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ KEY_COL1 + " VARCHAR(15) UNIQUE, " + KEY_COL2 + " VARCHAR(15) )";
private static class DbHelper extends SQLiteOpenHelper {
public DbHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(USER_MASTER_CREATE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// if DATABASE VERSION changes
// Drop old tables and call super.onCreate()
}
}
public DBclass(Context c) {
ourContext = c;
dbh = new DbHelper(ourContext);
}
public DBclass open() throws SQLException {
odb = dbh.getWritableDatabase();
return this;
}
public void close() {
dbh.close();
}
public long insertmaster(String col1, String col2) throws SQLException{
Log.d("", col1);
Log.d("", col2);
ContentValues IV = new ContentValues();
IV.put(KEY_COL1, col1);
IV.put(KEY_COL2, col2);
return odb.insert(DATABASE_TABLE, null, IV);
// returns a number >0 if inserting data is successful
}
public void updateRow(long rowID, String col1, String col2) {
ContentValues values = new ContentValues();
values.put(KEY_COL1, col1);
values.put(KEY_COL2, col2);
try {
odb.update(DATABASE_TABLE, values, KEY_ROWID + "=" + rowID, null);
} catch (Exception e) {
}
}
public boolean delete() {
return odb.delete(DATABASE_TABLE, null, null) > 0;
}
public Cursor getAllTitles() {
// using simple SQL query
return odb.rawQuery("select * from " + DATABASE_TABLE, null);
}
public Cursor getallCols(String id) throws SQLException {
Cursor mCursor = odb.query(DATABASE_TABLE, new String[] { KEY_COL1,
KEY_COL2 }, null, null, null, null, null);
Log.e("getallcols zmv", "opening successfull");
return mCursor;
}
public Cursor getColsById(String id) throws SQLException {
Cursor mCursor = odb.query(DATABASE_TABLE, new String[] { KEY_COL1,
KEY_COL2 }, KEY_ROWID + " = " + id, null, null, null, null);
Log.e("getallcols zmv", "opening successfull");
return mCursor;
}
}
- Code for Main.java
package fun.droiding.dbtutorial;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import android.app.Activity;
import android.database.Cursor;
import android.database.SQLException;
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.ListView;
import android.widget.SimpleAdapter;
import android.widget.Toast;
public class MainActivity extends Activity {
private ListView list_lv;
private EditText col1_ed;
private EditText col2_ed;
private Button sub_btn;
private Button ref_btn;
private DBclass db;
private ArrayList<String> collist_1;
private ArrayList<String> collist_2;
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
collist_1 = new ArrayList<String>();
collist_2 = new ArrayList<String>();
items();
getData();
}
private void items() {
sub_btn = (Button) findViewById(R.id.submit_btn);
ref_btn = (Button) findViewById(R.id.refresh_btn);
col1_ed = (EditText) findViewById(R.id.ed1);
col2_ed = (EditText) findViewById(R.id.ed2);
list_lv = (ListView) findViewById(R.id.dblist);
ref_btn.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
getData();
}
});
sub_btn.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
submitData();
}
});
}
protected void submitData() {
String a = col1_ed.getText().toString();
String b = col2_ed.getText().toString();
db = new DBclass(this);
long num;
try {
db.open();
num = db.insertmaster(a, b);
db.close();
} catch (SQLException e) {
num = -5;
} finally {
getData();
}
if (num > 0)
Toast.makeText(this, "Row number: " + num, 2000).show();
else if (num == -1)
Toast.makeText(this, "Error Duplicate value", 4000).show();
else
Toast.makeText(this, "Error while inserting", 2000).show();
}
public void getData() {
collist_1.clear();
collist_2.clear();
db = new DBclass(this);
try {
db.open();
Cursor cur = db.getAllTitles();
while (cur.moveToNext()) {
String valueofcol1 = cur.getString(1);
String valueofcol2 = cur.getString(2);
// Log.e("---****---", "*********** col 1 = " + valueofcol1);
// Log.e("---****---", "*********** col 2 = " + valueofcol2);
collist_1.add(valueofcol1);
collist_2.add(valueofcol2);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
db.close();
}
printList();
setDataIntoList();
}
private void printList() {
for (int i = 0; i < collist_1.size(); i++) {
Log.e("***************",
collist_1.get(i) + " --- " + collist_2.get(i));
}
}
private void setDataIntoList() {
// create the list item mapping
String[] from = new String[] { "col_1", "col_2" };
int[] to = new int[] { R.id.col1tv, R.id.col2tv };
// prepare the list of all records
List<HashMap<String, String>> fillMaps = new ArrayList<HashMap<String, String>>();
for (int i = 0; i < collist_1.size(); i++) {
HashMap<String, String> map = new HashMap<String, String>();
map.put("col_1", collist_1.get(i));
map.put("col_2", collist_2.get(i));
fillMaps.add(map);
}
// fill in the grid_item layout
SimpleAdapter adapter = new SimpleAdapter(this, fillMaps,
R.layout.custom, from, to);
list_lv.setAdapter(adapter);
}
}
Filed under: Android Examples | Tagged: Android Example, Source Code |
fundroiding 
hi I really like your site blog .
its look awesome and eye-catching.
thanks
it’s good blog for me because i firstly run database program by studying this example