• Do you like ORM (Object-Relational Mapping)?
• Do you like keep your database as simple as possible?
• Do you like Retrofit?
• Do you like and Json and Gson library?

If you said YES on all question then please read this blog post. If not then read it too because in future you won’t waste your hours on DB issues.

Currently I am working on application with questionnaire feature and we faced a problem how to resolve offline mode, how to save current answer, how to synchronize with server? There was few ideas on it.

Ideas:

• Save Json String in DB and every time parse it to object.
• Prepare own DatabaseHelper and save all object manually.
• Use ORMLite.

We chose third option because it was simple, quick, flexible and fast. First classes, simple annotations, singleton with DAOs initialized in Application onCreate(). In one day 60-70% of work was done. Then we wanted add Foreign keys to Database. True development was started.

QuestionnaireAnswer.class

@DatabaseTable(tableName = "questionnaire_answers")
public class QuestionnaireAnswers {
   @DatabaseField(id = true)
   @SerializedName("id")
   private String mId;
   @ForeignCollectionField(eager = true)
   @SerializedName("answers")
   private Collection mQuestionAnswers;
}

QuestionAnswer.class

@DatabaseTable(tableName = "question_answers")
public class QuestionAnswer {
   @DatabaseField(id = true)
   @SerializedName("id")
   private String mId;
   @DatabaseField("foreign = true)
   private QuestionnaireAnswers mQuestionnaire;
}

Everything is clear. In QuestionnaireAnswer object we have Collection of QuestionAnswers from different table and each QuestionAnswers has reference to QuestionnaireAnswers. In application I didn’t faced a problem with DAOs, updating, deleting etc. Problem appeared when I tried to send this answers to server. Crash from Retrofit converter. StackOverflow exception because I didn’t cleared foreign keys. Second try and another crash. LazyForeignCollection can’t be serialized to Json. I created additional field with List and synchronize objects. Also I enabled Expose annotation (excludeFieldsWithoutExposeAnnotation() in GsonBuilder) to ignore LazyForeignCollection.

QuestionnaireAnswer.class (Updated)

@DatabaseTable(tableName = "questionnaire_answers")
public class QuestionnaireAnswers {
   @Expose
   @DatabaseField(id = true)
   @SerializedName("id")
   private String mId;
   @ForeignCollectionField(eager = true)
   private Collection mQuestionAnswersCollection;
   @Expose
   @SerializedName("answers")
   private ArrayList mQuestionAnswers = new ArrayList<>();
}

QuestionAnswer.class

@DatabaseTable(tableName = "question_answers")
public class QuestionAnswer {
   @Expose
   @DatabaseField(id = true)
   @SerializedName("id")
   private String mId;
   @DatabaseField("foreign = true)
   private QuestionnaireAnswers mQuestionnaire;
}

You have to also update all getters and setters.

Benefits:

• clean source code
• your objects can be easily Parcelable
• GSon will ignore not required fields