SQLiteDatabase (Without SqliteOpenHelper class)

SQLite is a open-source relational database, that is used to perform database operations such as creating, storing, manipulating, retrieving and deleting data from the database.

SQLitedatabase is used in android in two way:

1. Using SQLiteOpenHelper class
2. Without SQLiteOpenHelper class

Here we are going to see the example of sqlitedatabase without SQLiteOpenHelper class.

In this example, we will do following:

1. Create a database
2. Create a table
3. Insert values into table
4. Retrieve values
5. Update values
6. Delete values

Create a database:

SQLiteDatabase db=openOrCreateDatabase(DB_NAME, Context.MODE_PRIVATE, null);

This opens a database defined in the constant DB_NAME, if it already exists. Else it creates a new database and open it.

Create a table:

db.execSQL("CREATE TABLE IF NOT EXISTS "

+ TABLE_NAME

+ " (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, DESIGNATION TEXT, CONTACT NUMBER, AREA TEXT, CITY TEXT);");

This creates a table name defined int the constant TABLE_NAME having columns ID, NAME, DESIGNATION, CONTACT, AREA

Insert values into table:

db.execSQL("INSERT INTO " + TABLE_NAME + "(NAME, DESIGNATION, CONTACT, AREA, CITY) VALUES('" + name.getText().toString() + "',

                            '" + des.getText().toString() + "',

                            '" + con.getText() + "',

                            '" + area.getText().toString() + "',

                            '" + city.getText().toString() + "')");

This inserts values into table from your edittext dynamically. Here name, des, con, area, city are edittext. From those edittext you can insert into table at runtime.

Retrieve values:

Cursor result = db.rawQuery("SELECT * FROM " + TABLE_NAME, null);

or

Cursor result = db.rawQuery("SELECT * FROM " + TABLE_NAME + "
                              WHERE NAME='" + name + "'", null);

First statement show you all values from table while second statement show you values whose name you have entered in 'name' edittext.

Update values:
db.execSQL("UPDATE " + TABLE_NAME + 
" SET DESIGNATION='" + des.getText().toString() + "'
WHERE NAME='" + name.getText().toString() + "'");

In above statement, you update the value of DESIGNATION depends on NAME. You can change the value of DESIGNATION of particular person whose name entered in edittext 'name'.

Delete values:
db.execSQL("DELETE FROM " + TABLE_NAME + " WHERE 
            NAME='" + name.getText().toString() + "'");

This statement deletes values whose name is entered in edittext 'name'.

activity_main.xml
<RelativeLayout
xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:layout_marginTop="10dp"
    tools:context=".MainActivity" >
    <AutoCompleteTextView
        android:id="@+id/edit1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="@string/name" />
    <EditText
        android:id="@+id/edit2"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_below="@+id/edit1"
        android:layout_marginTop="10dp"
        android:hint="@string/des" />
    <EditText
        android:id="@+id/edit3"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_below="@+id/edit2"
        android:layout_marginTop="10dp"
        android:hint="@string/con" />
    <EditText
        android:id="@+id/edit4"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_below="@+id/edit3"
        android:layout_marginTop="10dp"
        android:hint="@string/area" />
    <EditText
        android:id="@+id/edit5"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_below="@+id/edit4"
        android:layout_marginTop="10dp"
        android:hint="@string/city" />
    <Button
        android:id="@+id/button1"
        android:layout_width="80dp"
        android:layout_height="wrap_content"
        android:layout_below="@+id/edit5"
        android:layout_marginTop="16dp"
        android:text="@string/add" />
    <Button
        android:id="@+id/button2"
        android:layout_width="80dp"
        android:layout_height="wrap_content"
        android:layout_alignBaseline="@+id/button1"
        android:layout_toRightOf="@+id/button1"
        android:text="@string/show" />
    <Button
        android:id="@+id/button3"
        android:layout_width="85dp"
        android:layout_height="wrap_content"
        android:layout_alignBaseline="@+id/button2"
        android:layout_toRightOf="@+id/button2"
        android:text="@string/update" />
    <Button
        android:id="@+id/button4"
        android:layout_width="80dp"
        android:layout_height="wrap_content"
        android:layout_alignBaseline="@+id/button3"
        android:layout_toRightOf="@+id/button3"
        android:text="@string/delete" />
    <Button
        android:id="@+id/button5"
        android:layout_width="80dp"
        android:layout_height="wrap_content"
        android:layout_below="@+id/button1"
        android:text="@string/reset" />
</RelativeLayout>



activity_view.xml  (for listview of data)
<RelativeLayout
    xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:padding="20dp"
    tools:context=".ViewActivity" >
    <ListView
        android:id="@+id/listView1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_centerVertical="true" >
    </ListView>
</RelativeLayout>

MainActivity
package com.example.dynamicdatabasewithouthelper;

import java.util.ArrayList;
import java.util.List;
import android.app.Activity;
import android.content.Context;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.Menu;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.ArrayAdapter;
import android.widget.AutoCompleteTextView;
import android.widget.Button;
import android.widget.EditText;

public class MainActivity extends Activity {

  EditText des, con, area, city;
  AutoCompleteTextView name;
  Button add, show, update, delete, reset;

  SQLiteDatabase db;
  private static final String DB_NAME = "myDB.db";
  private static final String TABLE_NAME = "emp_details";

  List<String> item = new ArrayList<String>();
  ArrayAdapter<String> adapter;
  String s;
  Cursor cur;
  Boolean m = true;

  @Override
  protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
name = (AutoCompleteTextView) findViewById(R.id.edit1);
des = (EditText) findViewById(R.id.edit2);
con = (EditText) findViewById(R.id.edit3);
area = (EditText) findViewById(R.id.edit4);
city = (EditText) findViewById(R.id.edit5);
add = (Button) findViewById(R.id.button1);
show = (Button) findViewById(R.id.button2);
update = (Button) findViewById(R.id.button3);
delete = (Button) findViewById(R.id.button4);
reset = (Button) findViewById(R.id.button5);

    db = openOrCreateDatabase(DB_NAME, Context.MODE_PRIVATE, null);
    db.execSQL("CREATE TABLE IF NOT EXISTS "

+ TABLE_NAME

+ " (ID INTEGER PRIMARY KEY

      AUTOINCREMENT, NAME TEXT, DESIGNATION TEXT, CONTACT NUMBER,

      AREA TEXT, CITY TEXT);");

    db.close();

name.setOnClickListener(new OnClickListener() {
   @Override
   public void onClick(View v) {
   // TODO Auto-generated method stub
   item.clear();
   db = openOrCreateDatabase(DB_NAME, Context.MODE_PRIVATE, null);
   cur = db.rawQuery("SELECT NAME FROM " + TABLE_NAME, null);
   cur.moveToFirst();
   while (cur.isAfterLast() == false) {
   String logic = cur.getString(0);
         item.add(logic);
 cur.moveToNext();
    }

 adapter = new ArrayAdapter<String>(getBaseContext(),
android.R.layout.simple_list_item_1, item);
name.setThreshold(1);
name.setAdapter(adapter);
   db.close();
   }
});

add.setOnClickListener(new OnClickListener() {
   @Override
   public void onClick(View v) {
   // TODO Auto-generated method stub

   db = openOrCreateDatabase(DB_NAME, Context.MODE_PRIVATE, null);
   db.execSQL("INSERT INTO " + TABLE_NAME + "(NAME,
     DESIGNATION, CONTACT, AREA, CITY) VALUES(
                          '" + name.getText().toString() + "',

                          '" + des.getText().toString() + "',

                          '" + con.getText() + "',

                          '" + area.getText().toString() + "',

                          '" + city.getText().toString() + "')");

   db.close();
   name.getText().clear();
   des.getText().clear();
   con.getText().clear();
   area.getText().clear();
   city.getText().clear();
   }
});

show.setOnClickListener(new OnClickListener() {
   @Override
   public void onClick(View v) {
   // TODO Auto-generated method stub
   String abc = name.getText().toString();
   Intent i = new Intent(MainActivity.this, ViewActivity.class);
   i.putExtra("hello", abc);
   startActivity(i);
   name.getText().clear();
   }
});

update.setOnClickListener(new OnClickListener() {
   @Override
   public void onClick(View v) {
   // TODO Auto-generated method stub
   db = openOrCreateDatabase(DB_NAME, Context.MODE_PRIVATE, null);
   db.execSQL("UPDATE " + TABLE_NAME + 
    " SET DESIGNATION='" + des.getText().toString() + "'
    WHERE NAME='" + name.getText().toString() + "'");
   db.close();
   name.getText().clear();
   des.getText().clear();
   }
});

delete.setOnClickListener(new OnClickListener() {
   @Override
   public void onClick(View v) {
   // TODO Auto-generated method stub
   db = openOrCreateDatabase(DB_NAME, Context.MODE_PRIVATE, null);
   db.execSQL("DELETE FROM " + TABLE_NAME + " WHERE 
            NAME='" + name.getText().toString() + "'");
   db.close();
   name.getText().clear();
   }
});

reset.setOnClickListener(new OnClickListener() {
   @Override
   public void onClick(View v) {
   // TODO Auto-generated method stub
   name.getText().clear();
   des.getText().clear();
   con.getText().clear();
   area.getText().clear();
   city.getText().clear();
   }
});
}

  @Override
  public boolean onCreateOptionsMenu(Menu menu) {
  // Inflate the menu; this adds items to the action bar if it is
     present.
  getMenuInflater().inflate(R.menu.main, menu);
  return true;
  }
}

ViewActivity (for listview of data)

package com.example.dynamicdatabasewithouthelper;

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

import android.app.Activity;
import android.content.Context;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.Menu;
import android.widget.ArrayAdapter;
import android.widget.ListView;

public class ViewActivity extends Activity {

    SQLiteDatabase db;

    List<String> listItem = new ArrayList<String>();
    ListView listView;
    ArrayAdapter<String> adapter;
    String name, des;

    Cursor result;
    private static final String DB_NAME = "myDB.db";
    private static final String TABLE_NAME = "emp_details";

    @Override
    protected void onCreate(Bundle savedInstanceState) {
      super.onCreate(savedInstanceState);
      setContentView(R.layout.activity_view);
      listView = (ListView) findViewById(R.id.listView1);
      Intent i = getIntent();
      name = i.getStringExtra("hello");

      db = openOrCreateDatabase(DB_NAME, Context.MODE_PRIVATE, null);

      if (name.isEmpty() || name.compareTo(" ") == 0) {
        result = db.rawQuery("SELECT * FROM " + TABLE_NAME, null);
      } else {
result = db.rawQuery("SELECT * FROM " + TABLE_NAME + "
                              WHERE NAME='" + name + "'", null);
}
if (result.moveToFirst()) {
  do {
              String ID, NAME, DESIGNATION, CONTACT, AREA, CITY;
              ID = result.getString(0);
              NAME = result.getString(1);
              DESIGNATION = result.getString(2);
              CONTACT = result.getString(3);
              AREA = result.getString(4);
              CITY = result.getString(5);

              String get = ID + " " + NAME + " " + DESIGNATION +                          " " + CONTACT + " " + AREA + " " + CITY;
              listItem.add(get);
              adapter = new ArrayAdapter<String>(this,
android.R.layout.simple_list_item_1, listItem);
              listView.setAdapter(adapter);

              } while (result.moveToNext());
}
db.close();
}

@Override
public boolean onCreateOptionsMenu(Menu menu) {
  // Inflate the menu; this adds items to the action bar
             if it is present.
  getMenuInflater().inflate(R.menu.view, menu);
  return true;
}
}

Here 'name' is autocompletetext because whenever you enter name in autocompletetext, the name add in list. After second time when you enter name, after 2 character, you can see all names till your app run.

Note:
You must specify ViewActivity in Manifest file. Write this before </aplication>
<activity
 android:name="com.example.dynamicdatabasewithouthelper.ViewActivity"
 android:label="@string/title_activity_view" >
</activity>

If you want to view data in sqlite manager, download SqliteManager jar file from here, put this jar file into your eclipse->dropins folder

Then in eclipse, goto Windows->Show View->Other->File Explorer
Open it.

You found many fields like acct, cache, config, data, etc.
From that find data field, and inside data you found another data. Inside that data, you click on your package name like here com.example.databasewithhelper. Inside that there are 3 folders cache, database and lib. Inside database folder, you can see your database like here example.db click on it then you can see some options to the right side of that page with these icons:

Here 5th icon is for view data in sqlite manager. click on this icon and then you can see following image

Then click on Browse data, from Table select your table name, like here detail. You can see your data.

Source code: DataBaseWithoutHelper