Create SQLite tables from data classes on Android

Suppose you have a set of classes to hold your runtime data. To store your data locally you may want to generate database tables from the class definitions using Reflection.

Let’s start with the following data class.

MyDataClass.java:

package de.nitri.TableFromClass;
 
public class MyDataClass {
 
	@Attributes(primaryKey = true)
	private int myInteger;
	private String myFirstString;
	private String mySecondString;
 
 
	public int getMyInteger() {
		return myInteger;
	}
	public void setMyInteger(int myInteger) {
		this.myInteger = myInteger;
	}
	public String getMyFirstString() {
		return myFirstString;
	}
	public void setMyFirstString(String myFirstString) {
		this.myFirstString = myFirstString;
	}
	public String getMySecondString() {
		return mySecondString;
	}
	public void setMySecondString(String mySecondString) {
		this.mySecondString = mySecondString;
	}
 
}

As you can see, we have two string fields and one integer. The integer has been annotated as a primary key. Of course we need to define this annotation. We can add all the attributes we need to Attributes, starting with our primary key attribute.

Attributes.java:

package de.nitri.TableFromClass;
 
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
 
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Attributes {
 
	public boolean primaryKey();
 
}

To manipulate our database via Reflection we create a small tool class that can be instatiated with our database object. Let’s call it DFlex. We add the methods we need to this class, starting with createTableFromClass(String className), the one we need for our purpose here.

DFlex.java:

package de.nitri.TableFromClass;
 
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
 
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
 
public class DFlex {
 
	SQLiteDatabase db;
 
	public DFlex(SQLiteDatabase db) {
		this.db = db;
	}
 
	public void createTableFromClass(String className) {
 
		Field[] fields = null;
 
		StringBuilder queryBuilder = new StringBuilder();
 
		try {
			Class<?> clazz = Class.forName(className);
			fields = clazz.getDeclaredFields();
			String name = clazz.getSimpleName();
			queryBuilder.append("CREATE TABLE " + name + " (");
		} catch (Exception e) {
			Log.e("ERROR", e.getStackTrace().toString());
		}
 
		boolean firstField = true;
 
		for (Field field : fields) {
			if (!firstField) {
				queryBuilder.append(", ");
			}
 
			queryBuilder.append(field.getName() + " ");
 
			if (String.class.isAssignableFrom(field.getType())) {
				queryBuilder.append("TEXT");
			}
 
			if (field.getType() == Integer.TYPE) {
				queryBuilder.append("INTEGER");
			}
 
			Annotation annotation = field.getAnnotation(Attributes.class);
			if (annotation != null) {
				if (annotation instanceof Attributes) {
					Attributes attr = (Attributes) annotation;
					if (attr.primaryKey())
						queryBuilder.append(" PRIMARY KEY");
				}
			}
 
			firstField = false;
		}
 
		queryBuilder.append(");");
 
		String query = queryBuilder.toString();
		db.execSQL(query);
	}
}

Note that we check for String types with the rather awkward isAssignableFrom method. Primitive types are identified by simple constants like Integer.TYPE or Long.TYPE.

Now we can use our new DFlex tool in a traditional helper class. Remember that the onCreate method will only be called when de database version is increased or the database file is deleted.

MyData.java:

package de.nitri.TableFromClass;
 
import static android.provider.BaseColumns._ID;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
 
public class MyData extends SQLiteOpenHelper {
   private static final String DATABASE_NAME = "dflex.db";
   private static final int DATABASE_VERSION = 1;
 
   public MyData(Context ctx) {
      super(ctx, DATABASE_NAME, null, DATABASE_VERSION);
   }
 
   @Override
   public void onCreate(SQLiteDatabase db) {
	  DFlex dFlex = new DFlex(db);
	  dFlex.createTableFromClass("de.nitri.TableFromClass.MyDataClass");
   }
 
   @Override
   public void onUpgrade(SQLiteDatabase db, int oldVersion,
         int newVersion) {
 
      onCreate(db);
   }
 
}