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. 
   
3. private static String DATA_BASE = "employee_database";
4. private static String TABLE_NAME = "employee";
5. private static int DATABASE_VERSION = 3;
6. String create_table = "CREATE TABLE  "+TABLE_NAME+" (id INTEGER PRIMARY   
7.               KEY ,  name VARCHAR )";
9. public DatabaseHelper(Context context) {
11. super(context,DATA_BASE,null,DATABASE_VERSION);
13. }
14. 
    
15. @Override
16. public void onCreate(SQLiteDatabase db) {
17. db.execSQL(create_table);
19. }
20. 
    
21. @Override
22. public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
24. db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME );
25. onCreate(db);
26. }
29. public void insertValues(.........)
30. {
31. write some Code to insert values
32. }
33. public void update(..........){
34. 
    
35. write some Code to update values 
36. }
38. public void delete(){
39. 
    
40. write some Code to delete values 
41. }
43. public returntype getValues(){
45. write some Code to get values 
46. }
47. }

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. 
   
3. public int id;
4. public String name;
5. 
   
6. public Employee_Class(int id, String name) {
7. super();
8. this.id = id;
9. this.name = name;
10.     }
11. }

 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){
3. SQLiteDatabase db = getWritableDatabase();
4. ContentValues values = new ContentValues();
5. values.put("id",obj.id );
6. values.put("name", obj.name);
7. db.update(TABLE_NAME, values, "id = ?",new String[]{String.valueOf(obj.id)});
9.          }

Now the logic for deleting :

1. public void delete(){
3. SQLiteDatabase db = getWritableDatabase();
4. db.delete(TABLE_NAME, null, null);
5. Log.v("done","deleted all rows ");
7. }

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 ");
6. cursor = db.rawQuery("SELECT * FROM "+TABLE_NAME, null);
7. if(!cursor.moveToFirst()){
8.  
9. }
10. else{
11. list_emp = new ArrayList<Employee_Class>();
12. Employee_Class emp_class ;
13.  
14. do {
15. emp_class = new Employee_Class(Integer.parseInt(cursor.getString(0)), 
16.                     cursor.getString(1));
17. list_emp.add(emp_class);
19. } while (cursor.moveToNext());
20.  
21. }
22. db.close();
23. cursor.close();
24. return list_emp;
25. }

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. 
   
3. Button mBTN_save, mBTN_get, mBTN_delete;
4. EditText mET_id;
5. EditText mET_name;
6. ArrayList<Employee_Class> list_emp;
7. DatabaseHelper db = null;
8. 
   
9. @Override
10. public void onCreate(Bundle savedInstanceState) {
11. super.onCreate(savedInstanceState);
12. setContentView(R.layout.main);
13. initializeUI();
14. mBTN_save.setOnClickListener(this);
15. 
    
16. mBTN_delete.setOnClickListener(this);
17. mBTN_get.setOnClickListener(this);
18. db = new DatabaseHelper(this);
19. }
20. 
    
21. private void initializeUI() {
22. 
    
23. mBTN_save = (Button) findViewById(R.id.main_BTN_submit);
24. mBTN_get = (Button) findViewById(R.id.main_BTN_get);
25. mBTN_delete = (Button) findViewById(R.id.main_BTN_delete);
26. mET_id = (EditText) findViewById(R.id.main_ET_id);
27. mET_name = (EditText) findViewById(R.id.main_ET_name);
28.           }
29.       // Initializes the views in the main.xml file 
30. public void onClick(View arg0) {
31. 
    
32. switch (arg0.getId()) { 
33.             //handling the submit button
34. case R.id.main_BTN_submit:
35. 
    
36. Log.v("clicked", "  " + mET_name.getText().toString() + "   "
37. + mET_id.getText().toString());
38. int id = Integer.parseInt(mET_id.getText().toString());
39. String name = mET_name.getText().toString();
40. Employee_Class obj = new Employee_Class(id, name);
41. db.insertValues(obj);
42.                           break;
43.              //handling the get all employees button
44. case R.id.main_BTN_get:
45. list_emp = db.getValues();
46. if (list_emp == null) {
47. Toast.makeText(this, "No data in the database ",Toast.LENGTH_LONG).show();
48. } else {
49. String emp_details = "";
50. for (int i = 0; i < list_emp.size(); i++) {
51. emp_details = emp_details+"\n" + list_emp.get(i).id+"    "+list_emp.get(i).name;
52. }
53. Toast.makeText(this, " "+emp_details,
54. Toast.LENGTH_LONG).show();
55. }
56.              break;
57.                  //handling the delete button
58. case R.id.main_BTN_delete:
59. 
    
60. db.delete();
61. break;
62. }
63.        }
64. }

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. 
   
6.     <EditText
7.         android:id="@+id/main_ET_id"
8.         android:layout_width="wrap_content"
9.         android:layout_height="wrap_content"
10.         android:layout_alignParentTop="true"
11.         android:layout_centerHorizontal="true"
12.         android:layout_marginTop="45dp"
13.         android:ems="10"
14.         android:hint="Enter the id"
15.         android:inputType="number">
16. 
    
17.     </EditText>
18. 
    
19.     <EditText
20.         android:id="@+id/main_ET_name"
21.         android:layout_width="wrap_content"
22.         android:layout_height="wrap_content"
23.         android:layout_alignLeft="@+id/main_ET_id"
24.         android:layout_below="@+id/main_ET_id"
25.         android:layout_marginTop="15dp"
26.         android:ems="10"
27.         android:hint="Enter the name" />
28. 
    
29.     <Button
30.         android:id="@+id/main_BTN_submit"
31.         android:layout_width="wrap_content"
32.         android:layout_height="wrap_content"
33.         android:layout_alignLeft="@+id/main_ET_name"
34.         android:layout_below="@+id/main_ET_name"
35.         android:layout_marginLeft="48dp"
36.         android:layout_marginTop="18dp"
37.         android:text="Save " />
38. 
    
39.     <Button
40.         android:id="@+id/main_BTN_get"
41.         android:layout_width="wrap_content"
42.         android:layout_height="wrap_content"
43.         android:layout_alignLeft="@+id/main_BTN_submit"
44.         android:layout_below="@+id/main_BTN_submit"
45.         android:layout_marginTop="17dp"
46.         android:text="Get all employees" />
47. 
    
48.     <Button
49.         android:id="@+id/main_BTN_delete"
50.         android:layout_width="wrap_content"
51.         android:layout_height="wrap_content"
52.         android:layout_above="@+id/main_BTN_get"
53.         android:layout_alignRight="@+id/main_BTN_get"
54.         android:text="delete all" />
56. </RelativeLayout>

The xml file looks like this : 

Any suggestions are welcomed.

Please comment if anyone has any issue with this post.