Thursday, 16 August 2012

SQLite Database

This tutorial will cover the basics of how to use  SQLite  database in android. Android uses SQLite database as one of the data storage options. 
Lets have a look how we can use SQLite to perform the CRUD (create, retrieve, update, delete) operations. Before going directly into the CRUD operations we will have a look on how we are creating a database , a table in the database etc.
For this we will be using a class that extends SQLiteOpenHelper class. In this class I am going to create a database and also a table in it and at the same time write logic for the CRUD operations.
Lets have a look at the code for the creation of database and table.



  1.      public class DatabaseHelper extends SQLiteOpenHelper {

  2. private static String DATA_BASE = "employee_database";
  3. private static String TABLE_NAME = "employee";
  4. private static int DATABASE_VERSION = 3;
  5. String create_table = "CREATE TABLE  "+TABLE_NAME+" (id INTEGER PRIMARY   
  6.               KEY ,  name VARCHAR )";
  7. public DatabaseHelper(Context context) {
  8. super(context,DATA_BASE,null,DATABASE_VERSION);
  9. }

  10. @Override
  11. public void onCreate(SQLiteDatabase db) {
  12. db.execSQL(create_table);
  13. }

  14. @Override
  15. public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  16. db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME );
  17. onCreate(db);
  18. }
  19. public void insertValues(.........)
  20. {
  21. write some Code to insert values
  22. }
  23. public void update(..........){

  24. write some Code to update values 
  25. }
  26. public void delete(){

  27. write some Code to delete values 
  28. }
  29. public returntype getValues(){
  30. write some Code to get values 
  31. }
  32. }

Now coming to the CRUD operations : 
We are going to insert values into the database by considering getting the values for the respective columns from a special class that is created say Employee_Class . It has the following format.

  1.     public class Employee_Class {

  2. public int id;
  3. public String name;

  4. public Employee_Class(int id, String name) {
  5. super();
  6. this.id = id;
  7. this.name = name;
  8.     }
  9. }
 Now the logic for inserting

  1.    public void insertValues(Employee_Class obj)
  2. {
  3. SQLiteDatabase db = getWritableDatabase();
  4. ContentValues values = new ContentValues();
  5. values.put("id", obj.id);
  6. values.put("name", obj.name);
  7. db.insert(TABLE_NAME, null, values);
  8. db.close();
  9. }

Now the logic for updating :
  1. public void update( Employee_Class obj){
  2. SQLiteDatabase db = getWritableDatabase();
  3. ContentValues values = new ContentValues();
  4. values.put("id",obj.id );
  5. values.put("name", obj.name);
  6. db.update(TABLE_NAME, values, "id = ?",new String[]{String.valueOf(obj.id)});
  7.          }
Now the logic for deleting :

  1. public void delete(){
  2. SQLiteDatabase db = getWritableDatabase();
  3. db.delete(TABLE_NAME, null, null);
  4. Log.v("done","deleted all rows ");
  5. }
Now the logic for retreiving :

  1. public ArrayList<Employee_Class> getValues(){
  2. Cursor cursor = null;
  3. SQLiteDatabase db = getReadableDatabase();
  4. Log.v("done","getting rows ");
  5. cursor = db.rawQuery("SELECT * FROM "+TABLE_NAME, null);
  6. if(!cursor.moveToFirst()){
  7.  
  8. }
  9. else{
  10. list_emp = new ArrayList<Employee_Class>();
  11. Employee_Class emp_class ;
  12.  
  13. do {
  14. emp_class = new Employee_Class(Integer.parseInt(cursor.getString(0)), 
  15.                     cursor.getString(1));
  16. list_emp.add(emp_class);
  17. } while (cursor.moveToNext());
  18.  
  19. }
  20. db.close();
  21. cursor.close();
  22. return list_emp;
  23. }
Finally the place where i am using this code is in the MainActivity which is coded as below : 

  1.  public class MainActivity extends Activity implements OnClickListener {

  2. Button mBTN_save, mBTN_get, mBTN_delete;
  3. EditText mET_id;
  4. EditText mET_name;
  5. ArrayList<Employee_Class> list_emp;
  6. DatabaseHelper db = null;

  7. @Override
  8. public void onCreate(Bundle savedInstanceState) {
  9. super.onCreate(savedInstanceState);
  10. setContentView(R.layout.main);
  11. initializeUI();
  12. mBTN_save.setOnClickListener(this);

  13. mBTN_delete.setOnClickListener(this);
  14. mBTN_get.setOnClickListener(this);
  15. db = new DatabaseHelper(this);
  16. }

  17. private void initializeUI() {

  18. mBTN_save = (Button) findViewById(R.id.main_BTN_submit);
  19. mBTN_get = (Button) findViewById(R.id.main_BTN_get);
  20. mBTN_delete = (Button) findViewById(R.id.main_BTN_delete);
  21. mET_id = (EditText) findViewById(R.id.main_ET_id);
  22. mET_name = (EditText) findViewById(R.id.main_ET_name);
  23.           }
  24.       // Initializes the views in the main.xml file 
  25. public void onClick(View arg0) {

  26. switch (arg0.getId()) { 
  27.             //handling the submit button
  28. case R.id.main_BTN_submit:

  29. Log.v("clicked", "  " + mET_name.getText().toString() + "   "
  30. + mET_id.getText().toString());
  31. int id = Integer.parseInt(mET_id.getText().toString());
  32. String name = mET_name.getText().toString();
  33. Employee_Class obj = new Employee_Class(id, name);
  34. db.insertValues(obj);
  35.                           break;
  36.              //handling the get all employees button
  37. case R.id.main_BTN_get:
  38. list_emp = db.getValues();
  39. if (list_emp == null) {
  40. Toast.makeText(this, "No data in the database ",Toast.LENGTH_LONG).show();
  41. } else {
  42. String emp_details = "";
  43. for (int i = 0; i < list_emp.size(); i++) {
  44. emp_details = emp_details+"\n" + list_emp.get(i).id+"    "+list_emp.get(i).name;
  45. }
  46. Toast.makeText(this, " "+emp_details,
  47. Toast.LENGTH_LONG).show();
  48. }
  49.              break;
  50.                  //handling the delete button
  51. case R.id.main_BTN_delete:

  52. db.delete();
  53. break;
  54. }
  55.        }
  56. }
And the main.xml file is as below

  1. <RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
  2.     xmlns:tools="http://schemas.android.com/tools"
  3.     android:layout_width="match_parent"
  4.     android:layout_height="match_parent" >

  5.     <EditText
  6.         android:id="@+id/main_ET_id"
  7.         android:layout_width="wrap_content"
  8.         android:layout_height="wrap_content"
  9.         android:layout_alignParentTop="true"
  10.         android:layout_centerHorizontal="true"
  11.         android:layout_marginTop="45dp"
  12.         android:ems="10"
  13.         android:hint="Enter the id"
  14.         android:inputType="number">

  15.     </EditText>

  16.     <EditText
  17.         android:id="@+id/main_ET_name"
  18.         android:layout_width="wrap_content"
  19.         android:layout_height="wrap_content"
  20.         android:layout_alignLeft="@+id/main_ET_id"
  21.         android:layout_below="@+id/main_ET_id"
  22.         android:layout_marginTop="15dp"
  23.         android:ems="10"
  24.         android:hint="Enter the name" />

  25.     <Button
  26.         android:id="@+id/main_BTN_submit"
  27.         android:layout_width="wrap_content"
  28.         android:layout_height="wrap_content"
  29.         android:layout_alignLeft="@+id/main_ET_name"
  30.         android:layout_below="@+id/main_ET_name"
  31.         android:layout_marginLeft="48dp"
  32.         android:layout_marginTop="18dp"
  33.         android:text="Save " />

  34.     <Button
  35.         android:id="@+id/main_BTN_get"
  36.         android:layout_width="wrap_content"
  37.         android:layout_height="wrap_content"
  38.         android:layout_alignLeft="@+id/main_BTN_submit"
  39.         android:layout_below="@+id/main_BTN_submit"
  40.         android:layout_marginTop="17dp"
  41.         android:text="Get all employees" />

  42.     <Button
  43.         android:id="@+id/main_BTN_delete"
  44.         android:layout_width="wrap_content"
  45.         android:layout_height="wrap_content"
  46.         android:layout_above="@+id/main_BTN_get"
  47.         android:layout_alignRight="@+id/main_BTN_get"
  48.         android:text="delete all" />
  49. </RelativeLayout>

The xml file looks like this : 




Any suggestions are welcomed.

Please comment if anyone has any issue with this post.

No comments:

Post a Comment