Database

DataBase:

We are going to implement database in android, so first we should know why and where we need it. So there are lots of situations where we need to create database like suppose you have to manage attendances record of students, or salary record of faculties, calculate companies profit and loss, or maintain record of material in industry, Handle patience list and treatment report, everywhere you need to maintain the database. I think now you understood the requirement of database.

Implementation:

Create a new Android Application project and name it “DataBaseDemo” then create a package with name “com.akanksha.databasedemo“.

Now create an Activity inside your package ”DataAccessActivity”. Now create an another class and name it “DBaseHandler”.

In  DBaseHandler class we are going to create a constructor, lets declare some fields, we will use these fields further.

// Database related varibles

       private final String DATABASE_NAME = "akanksha_db";

       private final int DATABASE_VERSION = 1;

       // Table realted Variables

       private final String TABLE_NAME = "information_table";

       private final String INDEX_COL = "Sl_no";

       private final String COLUMN_ONE = "first_col";

       private final String COLUMN_TWO = "second_col";

       // sql string

       private final String CREATE_TABLE = "create table " + TABLE_NAME

                     + "(Sl_no integer primary key autoincrement, " + COLUMN_ONE

                     + " text, " + COLUMN_TWO + " text );";

       private String[] column_Array = { INDEX_COL, COLUMN_ONE, COLUMN_TWO };

       public DBaseHandler(Context context) {

              this.context = context;

              OpenHelper myHelper = new OpenHelper(context, DATABASE_NAME, null,

                           DATABASE_VERSION);

              sdb = myHelper.getWritableDatabase();

       }

Then create a inner class inside your DBaseHandler class and extends it by SQLiteOpenHelper. Then implement unimplemented methods. Your inner class should look like this.

class OpenHelper extends SQLiteOpenHelper {

              public OpenHelper(Context context, String name, CursorFactory factory,

                           int version) {

                     super(context, name, factory, version);

              }

              @Override

              public void onCreate(SQLiteDatabase db) {

                     db.execSQL(CREATE_TABLE);

              }

              @Override

              public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

                    

              }

       }

Now create a object of DBaseHandler in your “DataAccessActivity”.

       DBaseHandler dbh = new DBaseHandler(this);

Now we are going to insert data in our Database, for this we have to create insert method in DBaseHandler class.

       public void insertRow(String a, String b) {

              ContentValues dv = new ContentValues();

              // insert into tabename col1 value,col2 value;

              dv.put(COLUMN_TWO, b);

              dv.put(COLUMN_ONE, a);

              sdb.insert(TABLE_NAME, null, dv);

       }

You can call this method from “DataAccessActivity” class.

dbh.insertRow("Akanksha", "Rathore");

               dbh.insertRow("Mukesh", "Bhadoria");

               dbh.insertRow("Akash", "Rathore");

               dbh.insertRow("Anjna", "sharma");

Enjoy your values are now inserted in your database. Do you want to see you inserted values in your database. For this you can find your database file inside “File Explorer” inside your package. Go to data/data/YourPackageName/databases/ if you are executing your project in emulator

But if you are using your device to run this project then you  have to retrieve those values from database.

So Let’s fire the retrieve query to fetch records from database.

       public ArrayList<ArrayList<Object>> reteriveRow() {

              ArrayList<ArrayList<Object>> tableData = new ArrayList<ArrayList<Object>>();

              Cursor cursor;

              try {

                     cursor = sdb.query(TABLE_NAME, column_Array, null, null, null,

                                  null, null);

                     cursor.moveToFirst();

                     if (!cursor.isAfterLast()) {

                           do {

                                  ArrayList<Object> rowData = new ArrayList<Object>();

                                  rowData.add(cursor.getInt(0));

                                  rowData.add(cursor.getString(1));

                                  rowData.add(cursor.getString(2));

                                  tableData.add(rowData);

                           } while (cursor.moveToNext());

                     }

                     cursor.close();

              } catch (SQLException e) {

                     // TODO: handle exception

                     e.printStackTrace();

                     System.out.println(e);

              }

              return tableData;

       }

Call this method from “DataAccessActivity”

ArrayList<ArrayList<Object>> abcd = dbh.reteriveRow();

               // System.out.println(abcd);

               //

               System.out.println("---------------------value of uper loop----------");

             

               for(int pos=0; pos<=abcd.size()-1;pos++){

             

               System.out.println("---------------------Inside for loop----------");

               System.out.println(abcd.get(pos).get(0));

               System.out.println(abcd.get(pos).get(1));

               System.out.println(abcd.get(pos).get(2));

               }

Create for loop to print value on console you can also set this values in any view to show on Activity in this case I am showing you to print the values on console.

When you run your project you will get all the values.

But what if you want any particular row.

Try this :

public ArrayList<Object> retriveOneRow(int rowId) {

              Cursor cursor;

              ArrayList<Object> rowArray=new ArrayList<Object>();

              try {

                    

                     cursor = sdb.query(TABLE_NAME, column_Array, INDEX_COL + "=" + rowId,

                                  null, null, null, null);

                    

                    

                     cursor.moveToFirst();

                    

                     if(!cursor.isAfterLast()){

                          

                           do{

                                  rowArray.add(cursor.getInt(0));

                                  rowArray.add(cursor.getString(1));

                                  rowArray.add(cursor.getString(2));

                           }while(cursor.moveToNext());

                          

                     }

                     cursor.close();

                    

              } catch (SQLException e) {

                     e.printStackTrace();

                     System.out.println(e);

              }

              return rowArray;

             

       }

To access this method call it from “DataAccessActivity” and use create a loop to print it on console. Here we are fetching records by the specific id of particular row.

ArrayList<Object> bbcda = dbh.retriveOneRow(4);

              for (int pos = 0; pos <= bbcda.size() - 1; pos++) {

                    

                     System.out.println(bbcda.get(pos));

                    

              }

 Run your project to check the output.

Till now we have inserted values and retrieve that now move on delete part. Its quite easy.

       public void deleteRow(int rowID) {

              // Delete from Table_name where sNo.=2;

              sdb.delete(TABLE_NAME, INDEX_COL + "=" + rowID, null);

       }

Pass the id of that row which you wants to delete, in your “DataAccessActivity”.

dbh.deleteRow(2);

If you want to delete all rows, not a big deal.write this method in your “DBaseHandler”.

public void deleteAllRow() {

              sdb.delete(TABLE_NAME, null, null);

       }

Like always call this method from “DataAccessActivity”.

dbh.deleteAllRow();

To update values use this:

public void updateById(int id, String fn, String ln) {

              // TODO Auto-generated method stub

              ContentValues cvu = new ContentValues();

              cvu.put(COL_TWO, fn);

              cvu.put(COL_THREE, ln);

              sld.update(TABLE_NAME, cvu, COL_ONE + "=" + id, null);

       }

Pass the id of that row which you want to update and values what you wants to update.

mdh.updateById(3, “Rinky”, “Rajput”);

Now you have learned all the basic and important concept of database. Use it in the way you want. Now you can create your own database, insert many records, retrieve records according to your choice, and you can delete them too.

you can find source code here