SQLite Database Example

This is simple sqlite database tutorial without using External database. If you want to know how use external database in android then please click me!

To create and access a table in android using SQLite is very simple. The following method will show how to create a table and see column names of that table in android.

public void table1() {

  db = this.getWritableDatabase();

  db.execSQL("CREATE TABLE IF NOT EXISTS table1(id INTEGER PRIMARY KEY AUTOINCREMENT , description TEXT)");
  // to see colomn names in our table1
  Cursor t = db.rawQuery("PRAGMA TABLE_INFO(table1)", null);
  if (t.moveToFirst()) {
   do {
    Log.i("", t.getString(0) + " " + t.getString(1));
   } while (t.moveToNext());
  }
  t.close();
  db.close();

 }

And the following sample code is used to insert data into table.

public void inserIntoTable1(String desc) {

  db = this.getWritableDatabase();
  ContentValues cv = new ContentValues();
  try {
   db.beginTransaction();
   cv.put("description", desc);
   db.insert("table1", null, cv);
   db.setTransactionSuccessful();

  } catch (Exception ex) {

  } finally {
   db.endTransaction();
   db.close();
  }
 }

And the following code is used to access data from our table. The method will return data as list.

public List<String> getData() {
  db = this.getReadableDatabase();
  List<String> data = new ArrayList<String>();
  Cursor c = db.rawQuery("SELECT * FROM table1", null);
  while (c.moveToNext()) {
   data.add(c.getString(1));
  }
  c.close();
  db.close();
  return data;
 }

Now we can populate those data into our listview.

ArrayAdapter<String> adapter = new ArrayAdapter<String>(this,
    android.R.layout.simple_list_item_1, list); 
  mylist = (ListView) findViewById(R.id.listView1);
  mylist.setAdapter(adapter);

Full code. 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:orientation="vertical" >

    <TextView
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:text="Description" />

    <EditText
        android:id="@+id/editText1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" >
    </EditText>

    <Button
        android:id="@+id/button1"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:text="Save" />

    <ListView
        android:id="@+id/listView1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" >
    </ListView>

<LinearLayout>

DBHelper.java

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHelper extends SQLiteOpenHelper {

 final static String dbname = "Sample";
 SQLiteDatabase db;

 public DBHelper(Context context) {
  super(context, dbname, null, 1);
  // TODO Auto-generated constructor stub
 }

 public void table1() {

  db = this.getWritableDatabase();

  db.execSQL("CREATE TABLE IF NOT EXISTS table1(id INTEGER PRIMARY KEY AUTOINCREMENT , description TEXT)");
  // to see colomn names in our table1
  Cursor t = db.rawQuery("PRAGMA TABLE_INFO(table1)", null);
  if (t.moveToFirst()) {
   do {
    Log.i("", t.getString(0) + " " + t.getString(1));
   } while (t.moveToNext());
  }
  t.close();
  db.close();

 }

 public void inserIntoTable1(String desc) {

  db = this.getWritableDatabase();
  ContentValues cv = new ContentValues();
  try {
   db.beginTransaction();
   cv.put("description", desc);
   db.insert("table1", null, cv);
   db.setTransactionSuccessful();

  } catch (Exception ex) {

  } finally {
   db.endTransaction();
   db.close();
  }
 }

 public List<String> getData() {
  db = this.getReadableDatabase();
  List<String> data = new ArrayList<String>();
  Cursor c = db.rawQuery("SELECT * FROM table1", null);
  while (c.moveToNext()) {
   data.add(c.getString(1));
  }
  c.close();
  db.close();
  return data;
 }

 @Override
 public void onCreate(SQLiteDatabase db) {
 }

 @Override
 public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
  // TODO Auto-generated method stub

 }

}

SQLiteActivity.java

import java.util.List;

import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;

public class SQLiteActivity extends Activity {

 DBHelper dbhelper;
 Button save;
 EditText discription;
 ListView mylist;

 @Override
 public void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.main);

  dbhelper = new DBHelper(this);

  accessTable1();
  loadMyList();
 }

 private void accessTable1() {
  dbhelper.table1();
  discription = (EditText) findViewById(R.id.editText1);
  save = (Button) findViewById(R.id.button1);
  save.setOnClickListener(new OnClickListener() {

   @Override
   public void onClick(View v) {
    dbhelper.inserIntoTable1(discription.getText().toString());
    loadMyList();
   }
  });
 }

 private void loadMyList() {
  List<String> list = dbhelper.getData();
  ArrayAdapter<String> adapter = new ArrayAdapter<String>(this,
    android.R.layout.simple_list_item_1, list); 
  mylist = (ListView) findViewById(R.id.listView1);
  mylist.setAdapter(adapter);
 }
}

Reduce your work by using SQLiteHelper.jar

sample screen shot

 

Thank You