Android SQLite Database Connectivity Example with Source code

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 belowSource 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);
	}
}
About these ads

2 Responses

  1. hi I really like your site blog .
    its look awesome and eye-catching.

    thanks

  2. it’s good blog for me because i firstly run database program by studying this example

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: