Manage a Database in Android

Today we will learn how to manage a database in Android using native SQLite libraries.

Basically we need two classes for everyhing: 

1. SQLiteOpenHelper: (abstract class to derive) provides to manage database creation and versioning. 
2. SQLiteDatabase: provides methods to execute sql commands.

The first class launches our "create tables" and other stuffs..

//Some constants
private static final String DATABASE_NAME = "mydb";
private static final String DATABASE_TABLE = "settings";

public static final String KEY_SERVERIP = "server_ip";
public static final String KEY_SERVERPORT = "server_port";

private static final String DATABASE_CREATETABLE=
"create table "+DATABASE_TABLE+" ( "
  + KEY_SERVERIP+ " text null, "
  + KEY_SERVERPORT+" text null ); ";

private static final int DATABASE_VERSION = 2;

//Helper class
private static class DatabaseHelper extends SQLiteOpenHelper 
{
     DatabaseHelper(Context context) 
     {
          super(context, DATABASE_NAME, null, DATABASE_VERSION);
     }

     @Override
     public void onCreate(SQLiteDatabase db) 
     {
          db.execSQL(DATABASE_CREATETABLE);
     }

     @Override
     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
     {
          db.execSQL("DROP TABLE IF EXISTS "+DATABASE_TABLE);
          onCreate(db);
     }
}    

The second one let you choose to execute raw sql commands or use defined methods for insert, update and delete (see examples).

DatabaseHelper DBHelper = new DatabaseHelper(context);
//This method returns database and opens it. 
//It also check if create (onCreate) or upgrade (onUpgrade). 
SQLiteDatabase db = DBHelper.getWritableDatabase(); 

//...

//Method to update with method of SQLiteDatabase class
public Boolean insertServerData(String Ip,String port)
{
      DBHelper.getWritableDatabase(); //database is always to open 
      try
      {
            ContentValues args = new ContentValues();
            args.put(KEY_SERVERIP, Ip);
            args.put(KEY_SERVERPORT, port);
            return db.update(DATABASE_TABLE, args, null, null) > 0;
      }
      catch(Exception ex)
      {
            return false;
      }
      finally
      {
            DBHelper.close(); //and close it when we don't need it anymore
      }
}

//Method to select with method of SQLiteDatabaseClass
public Cursor SelectAll()
{
      Cursor mCursor =null;
      try
      {
            open();
            mCursor= db.query(true, DATABASE_TABLE, 
              new String[] {
              KEY_SERVERIP,
              KEY_SERVERPORT},
              null,
              null,
              null, 
              null, 
              null, 
              null);

            return mCursor;
      }
      catch(SQLException x)
      {
            //...
      }
      finally
      {
            close();
      }
      return null;
}

//Method to Execute a raw query 
public boolean insertNullRecord()
{
     try
     {
          open();
          //returs void
          db.execSQL("insert into "+DATABASE_TABLE+" SELECT NULL,NULL,NULL,NULL ");
          //db.rawQuery(sSql,null); returns a Cursor
          return true;
     }
     catch(Exception ex)
     {
          return false;
     }
     finally
     {
          close();
     }
}

We could create a wrapper to make work easier. It can contains SQLiteOpenHelper derived class and the methods to simplify the access to SQLiteDatabase object.

public class DBAdapter 
{    
     private final Context context; 
     private DatabaseHelper DBHelper;
     private SQLiteDatabase db;

     public DBAdapter(Context ctx) 
     {
          this.context = ctx;
          DBHelper = new DatabaseHelper(context);
     }

     //opens the database
     public DBAdapter open() throws SQLException 
     {
          db = DBHelper.getWritableDatabase();
          return this;
     }

     //closes the database  
     public void close() 
     {
          DBHelper.close();
     }

      /*

      - HERE MY METHODS TO EXECUTE QUERIES
      - HERE DATABASEHELPER CLASS WITH CONSTANTS

      */
}

Here we are! Just fix  (as we want)  our class and it's quite ready! Let's remember together how retrieve data from Cursor and we are done!

Collection<String> list=null;
Cursor resultSet=dbWr.GetData(); //retrieve cursor 

if(resultSet!=null && resultSet.getCount()>0)
{
     resultSet.moveToFirst();
     try
     {
          list = new ArrayList<String>();
          do
          {
               //0 is the first column
               list.add(resultSet.getString(0)); 
          }
          while(resultSet.moveToNext());
     }
     catch(SQLException ex)
     {
          throw ex;
     }
     finally
     {
          resultSet.close();   
     }
}

Happy coding guys! Thanks for reading :)
I hope you apreciate it.