SQLiteDatabase (Using SqliteOpenHelper class)

In previous blog post, we were learn SQLiteDatabase (Without SqliteOpenHelper class), now we will learn SQLiteDatabase (Using SqliteOpenHelper class)

 SQLiteOpenHelper class will act as a database controller which will have methods to perform CRUD operations such as Create, Read, Update and Delete. This java class should override the methods onCreate() and onUpgrade().

onCreate() method will be called for the first time when the Android application is run. First the database instance should be created using the method like getReadableDatabase() or getWritableDatabase() based on the type of access required. Android supports this method by providing in-built methods.

 Example of SQLiteDatabase with SQLiteOpenHelper
In this example we will do following:
  1. Create gettersetter method to get and to set values
  2. Helper class which extends SQLiteOpenHelper
  3. Activity to perform CRUD operations
GetterSetter.java is a class to get and to set values of variables that you want to store in database. Here we are store 5 variables:
name, place, department, phonenumber, address. All are defined in String.

 After define variables, right click on screen goto Source then select Generate Getters and Setters.. Checked variables that you want to use. And then ok.

 GetterSetter.java is now like as below:
package com.example.databasewithhelper;
public class GetterSetter {
String name, place, department, phonenumber, address;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPlace() {
return place;
}
public void setPlace(String place) {
this.place = place;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
public String getPhonenumber() {
return phonenumber;
}
public void setPhonenumber(String phonenumber) {
this.phonenumber = phonenumber;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
Helper class DbHelper.java
package com.example.databasewithhelper;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DbHelper extends SQLiteOpenHelper {
   private static final String DBNAME = "example.db";
   public static final String TABLE = "detail";
   private static final int VERSION = 1;
   public Helper(Context context) {
super(context, DBNAME, null, VERSION);
// TODO Auto-generated constructor stub
   }
   @Override
   public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
db.execSQL("CREATE TABLE IF NOT EXISTS "
+ TABLE
+ " (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, PLACE TEXT, DEPARTMENT TEXT, PHONENUMBER NUMBER, ADDRESS TEXT);");
}
   @Override
   public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
onCreate(db);
   }
}

MainActivity
package com.example.databasewithhelper;

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

 import android.app.Activity;
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 place, department, phonenumber, address;
   AutoCompleteTextView name;
   Button sub, show, update, delete;
   List<String> list = new ArrayList<String>();
   ArrayAdapter<String> adapter;
   @Override
   protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

    name = (AutoCompleteTextView) findViewById(R.id.autoCompleteTextView1);
   place = (EditText) findViewById(R.id.editText2);
   department = (EditText) findViewById(R.id.editText3);
   phonenumber = (EditText) findViewById(R.id.editText4);
   address = (EditText) findViewById(R.id.editText5);
   sub = (Button) findViewById(R.id.button1);
   show = (Button) findViewById(R.id.button2);
   update = (Button) findViewById(R.id.button3);
   delete = (Button) findViewById(R.id.button4);

     name.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
Helper helper = new Helper(MainActivity.this);
SQLiteDatabase db = helper.getWritableDatabase();
Cursor allrows = db.rawQuery(
       "SELECT NAME FROM " + helper.TABLE, null);
if (allrows.moveToFirst()) {
   do {
 String one = allrows.getString(0);
 boolean m = true;
 if (one.equals(list)) {
    m = false;
 }
 if (m == true) {
    list.add(one);
 }
      } while (allrows.moveToNext());
 adapter = new ArrayAdapter<String>(getBaseContext(),
android.R.layout.simple_list_item_1, list);
 name.setThreshold(1);
 name.setAdapter(adapter);
}
db.close();
      }
   });

     sub.setOnClickListener(new OnClickListener() {
@SuppressWarnings("static-access")
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
Helper helper = new Helper(MainActivity.this);
SQLiteDatabase db = helper.getWritableDatabase();
        GetterSetter gs = new GetterSetter();
gs.setName(name.getText().toString());
        db.execSQL("INSERT INTO " + helper.TABLE
+ "(NAME, PLACE, DEPARTMENT, PHONENUMBER,
ADDRESS) VALUES('" + gs.getName() + "', 
                '" + place.getText().toString() + "', 
                '" + department.getText().toString() + "', 
                '" + phonenumber.getText() + "', 
                '" + address.getText().toString() + "')");
        db.close();
name.getText().clear();
place.getText().clear();
department.getText().clear();
phonenumber.getText().clear();
address.getText().clear();
      }
   });
     update.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
Helper helper = new Helper(MainActivity.this);
SQLiteDatabase db = helper.getWritableDatabase();
db.execSQL("UPDATE " + helper.TABLE + " SET NAME = '"
      + name.getText().toString() + "' WHERE
                    PLACE = '" + place.getText().toString + "'");
db.close();
      }
   });
    delete.setOnClickListener(new OnClickListener() {
@SuppressWarnings("static-access")
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
Helper helper = new Helper(MainActivity.this);
SQLiteDatabase db = helper.getReadableDatabase();
db.execSQL("DELETE FROM " + helper.TABLE + " WHERE PLACE = 
          '" + place.getText().toString() + "'");
String m = place.getText().toString();
list.remove(m);
adapter = new ArrayAdapter<String>(getBaseContext(),
android.R.layout.simple_list_item_1, list);
name.setThreshold(1);
name.setAdapter(adapter);
     }
  });
    show.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
Intent i = new Intent(MainActivity.this, Viewdata.class);
i.putExtra("h1", name.getText().toString());
startActivity(i);
     }
   });
}
   @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;
   }
}

 activity_main.xml
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="fill_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity" >
    <AutoCompleteTextView
        android:id="@+id/autoCompleteTextView1"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_alignParentLeft="true"
        android:layout_alignParentTop="true"
        android:hint="@string/hint1" />
    <EditText
        android:id="@+id/editText2"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_alignParentLeft="true"
        android:layout_below="@+id/autoCompleteTextView1"
        android:ems="10"
        android:hint="@string/hint2"
        android:inputType="textPersonName" />
    <EditText
        android:id="@+id/editText4"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_alignParentLeft="true"
        android:layout_below="@+id/editText3"
        android:ems="10"
        android:hint="@string/hint4" />
    <EditText
        android:id="@+id/editText3"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_alignParentLeft="true"
        android:layout_below="@+id/editText2"
        android:ems="10"
        android:hint="@string/hint3" />
    <EditText
        android:id="@+id/editText5"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_alignParentLeft="true"
        android:layout_below="@+id/editText4"
        android:ems="10"
        android:hint="@string/hint5" />
    <Button
        android:id="@+id/button1"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentLeft="true"
        android:layout_below="@+id/editText5"
        android:text="@string/Button1" />
    <Button
        android:id="@+id/button3"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignBaseline="@+id/button1"
        android:layout_alignBottom="@+id/button1"
        android:layout_marginLeft="16dp"
        android:layout_toRightOf="@+id/button1"
        android:text="@string/Button3" />
    <Button
        android:id="@+id/button4"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/button3"
        android:layout_below="@+id/button3"
        android:text="@string/Button4" />
    <Button
        android:id="@+id/button2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentLeft="true"
        android:layout_below="@+id/button1"
        android:text="@string/Button2" />

</RelativeLayout>

ViewData.java (for view list of data)
package com.example.databasewithhelper;

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

 import android.annotation.TargetApi;
import android.app.Activity;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Build;
import android.os.Bundle;
import android.util.Log;
import android.view.Menu;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.Toast;

 public class Viewdata extends Activity {

    ListView list;
   List<String> item = new ArrayList<String>();
   ArrayAdapter<String> adapter;

    @TargetApi(Build.VERSION_CODES.GINGERBREAD)
   @Override
   protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_view);
list = (ListView) findViewById(R.id.list1);
Helper helper = new Helper(this);
SQLiteDatabase db = helper.getWritableDatabase();
Intent i = getIntent();
String h2 = i.getStringExtra("h1");
Cursor allrows = db.rawQuery("SELECT * FROM " + helper.TABLE + " WHERE NAME = '"+ h2 +"'", null);
@SuppressWarnings("static-access")
//Cursor allrows = db.rawQuery("SELECT * FROM " + helper.TABLE, null);
// for all data
int count = allrows.getCount();
Toast.makeText(getBaseContext(), count + "", Toast.LENGTH_SHORT).show();
if (allrows.moveToFirst()) {
  do {
     String one = allrows.getString(0);
     String two = allrows.getString(1);
     String three = allrows.getString(2);
     String four = allrows.getString(3);
     String five = allrows.getString(4);
     String six = allrows.getString(5);
     String get = one + " " + two + " " + three + " " + four + " " + five + " " + six;
     item.add(get);
     adapter = new ArrayAdapter<String>(this,
      android.R.layout.simple_list_item_1, item);
     list.setAdapter(adapter);
    } while (allrows.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;
   }

}

 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: DatabaseWithHelper

Comments

Post a Comment

Popular posts from this blog

MVC Architecture in Android

Image
MVC represents Model-View-Controller Model : Manage the behavior and data of the application R esponds to requests for information about its state (usually from the view) Responds to instructions to change state (usually from the controller) Allows the views to produce updated output View : T ypically a user interface element O ne to one correspondence with a display surface Present the model to the user Generates the output representation to the user Controller : Controls both model and view Accepts input from the user and instructs the model and the view to perform actions based on that input Update view when model changes Update model when user manipulate the input
Read more

Get Current Location using GPS/Network Provider

Hello to all, Today I teach you that how can we get current location. In android's new SDK tools, LocationManager class does not give any network provider's status. It always gives null value. Still it works in some of the android devices rarely. So that's why I include code for both network provider and gps provider for getting current location. Create one service class name LocationFinder  and implements it LocationListener . All the code which is related to Location, is doing in that service class and from MainActivity , call its method through its instance in onResume() method. The reason you know, why its called in onResume(), because when you enable GPS and come back in activity, then its again check Location's status. In the code, if GPS not enable, I show settings alert pop-up, to go settings menu. When you enable GPS, you will get current location after 2-3 seconds. If not, come again to activity and surely get location. Make sure, must add persmi...
Read more

SQLiteDatabase (Without SqliteOpenHelper class)

Image
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: Using SQLiteOpenHelper class Without SQLiteOpenHelper class Here we are going to see the example of sqlitedatabase without SQLiteOpenHelper class. In this example, we will do following: Create a database Create a table Insert values into table Retrieve values Update values 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 ...
Read more