Sunday, 20 July 2014

Enum to build SQLite database in Android

Over the past few days, I have search a "cleanest" way to manage a SQLite database. If the database contains only one table the simplest solution is to put directly into the SQLiteOpenHelper a string query to create/update the table. With several tables, In my opinion, it isn't the smartest solution. So to build the sql query and manage the tables I have used Enum.

I have created two Enum types: Columns and Table.

The Columns is an Interface which contains an Enum for each table. This Enums are the list of the columns of a table, and have as a value the type of the the variable( INTEGER, REAL,TEXT...), in order to use it to get a piece of query with the getCreateQuery method.
public interface Columns {
 String getCreateQuery();

 public enum TableTest1 implements Columns {

  ID(INTEGER), COL1(TEXT), COL2(REAL)
  private final String value;

  private TableTest1(String value) {
   this.value = value;
  }

  @Override
  public String getCreateQuery() {
   return this.name() + SPACE + this.value;
  }
 }

 public enum TableTest2 implements Columns {

  ID(INTEGER), COL1(TEXT), COL2(REAL)
  private final String value;

  private TableTest1(String value) {
   this.value = value;
  }

  @Override
  public String getCreateQuery() {
   return this.name() + SPACE + this.value;
  }
 }

}

The second Enum is the list of tables. It contains as a value the corresponding elements in the Columns interface and an activity (I have put the activity because I have for each activity a table).
public enum Table {

 TABLE1(TableTest1.class, Activity1.class),
 TABLE2(TableTest2.class, Activity2.class),

 private Class value;
 private Class activity;

 private Table(Class columns,
   Class activity) {
  value = columns;
  this.activity = activity;
 }

 public Class getTableStrucrure() {
  return (Class) value;
 }

 public Class getActivity() {
  return activity;
 }

 private static final String CREATE = "CREATE TABLE ";
 private static final String FIRST = " ( ROWID  integer  primary key, ";
 private static final String LAST = ")";

 public String getColumns() {

  StringBuilder sb = new StringBuilder(CREATE + this.name() + SPACE);
  sb.append(FIRST);
  Colonne[] values = value.getEnumConstants();
  int length = values.length - 1;
  for (int i = 0; i < length; i++) {
   sb.append(values[i].getCreateQuery() + COMMA);
  }
  sb.append(values[length].getCreateQuery());
  sb.append(LAST);
  return sb.toString();
 }

 public static String[] getValues(int tipo2) {
  List vals = new ArrayList();
  for (Tabelle tb : Tabelle.values()) {
   if (tb.tipo == tipo2) {
    vals.add(tb.name());
   }
  }
  Object[] temp = vals.toArray();
  String[] names = new String[temp.length];
  for (int i = 0; i < names.length; i++) {
   names[i] = temp[i].toString();
  }

  return names;

 }
}
So in the DBHelper class, to create the table:
 public void onCreate(SQLiteDatabase db) {

  for (Table tb : Table.values()) {
   db.execSQL(tb.getColumns());
  }

 }

1 comment:

  1. Good job! I like how clear and auto-explicative your code is. A precious tool for people that want to learn to program with the Android OS like me!

    ReplyDelete