Android Open Source - AndroidSqliteMultichoiceExample Data Access Object

• Back to SQLite/example  ↑

From Project

Back to project page AndroidSqliteMultichoiceExample.

License

The source code is released under:

Apache License

Java Source Code

/**
 * Copyright 2014 Yehezkel (Zack) Yovel//from   w  w  w  .  jav  a2  s  .c  o  m
 *
 *  Licensed under the Apache License, Version 2.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 */
package zack.examples.questiondb.database;

import java.util.ArrayList;
import java.util.List;

import zack.examples.qustiondb.entities.Question;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class DataAccessObject {

  private SQLiteDatabase dbRead;

  private static DataAccessObject instance;

  /**
   * Provides a singleton instance of DataAccessObject.
   * 
   * @param context
   * @return
   */
  public synchronized static DataAccessObject getInstance(Context context) {
    if (instance == null) {
      instance = new DataAccessObject(context);
    }
    return instance;
  }

  private DataAccessObject(Context context) {
    QuestionsOpenHelper helper = new QuestionsOpenHelper(context);
    this.dbRead = helper.getReadableDatabase();
  }

  /**
   * Queries the database for a list of subjects available (distinct list).
   * 
   * @return every subject that exist in the database.
   */
  public List<String> getSubjectList() {
    Cursor cursor = dbRead.query(true,
        DBContract.QuestionsTable.TABLE_NAME,
        new String[] { DBContract.QuestionsTable.COLUMN_SUBJECT },
        null, null, null, null, null, null);
    return cursorToSubjectList(cursor);
  }

  private List<String> cursorToSubjectList(Cursor cursor) {
    ArrayList<String> subjects = new ArrayList<String>();
    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
      subjects.add(cursor.getString(0));
      cursor.moveToNext();
    }
    return subjects;
  }

  /**
   * Queries the database for all questions that has the provided subject.
   * 
   * @param subject
   * @return all questions for this subject
   */
  public List<Question> getQuestionsForSubject(String subject) {

    String[] columns = { DBContract.QuestionsTable.COLUMN_ID,
        DBContract.QuestionsTable.COLUMN_QUESTION,
        DBContract.QuestionsTable.COLUMN_SUBJECT,
        DBContract.QuestionsTable.COLUMN_CORRECT_ANSWER_ID };

    String orderBy = DBContract.QuestionsTable.COLUMN_QUESTION + " DESC";

    Cursor cursor = dbRead.query(DBContract.QuestionsTable.TABLE_NAME,
        columns, DBContract.QuestionsTable.COLUMN_SUBJECT + " = ? ",
        new String[] { subject }, null, null, orderBy);
    return cursorToQuestionList(cursor);
  }

  /*
   * Collect data from the cursor to compile a list of Question objects
   */
  private List<Question> cursorToQuestionList(Cursor cursor) {
    ArrayList<Question> output = new ArrayList<Question>();
    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
      String questionString = cursor.getString(1);
      String subject = cursor.getString(2);
      List<String> answers = getAnswers(cursor.getLong(0),
          cursor.getLong(3));
      Question question = new Question(questionString, subject, answers);
      output.add(question);
      cursor.moveToNext();
    }
    return output;
  }

  /*
   * Queries the database for answers that belong to the provided question id
   * (reference it as a foreign key)
   */
  private List<String> getAnswers(long questionId, long correctAnswerId) {
    // Cursor cursor = dbRead.rawQuery(
    // "SELECT * FROM answers WHERE question_id = ? ",
    // new String[] { String.valueOf(questionId) });
    Cursor cursor = dbRead.query(DBContract.AnswersTable.TABLE_NAME,
        new String[] { DBContract.AnswersTable.COLUMN_ID,
            DBContract.AnswersTable.COLUMN_ANSWER },
        DBContract.AnswersTable.COLUMN_QUESTION_ID + " = ? ",
        new String[] { String.valueOf(questionId) }, null, null, null);
    return cursorToAnswers(cursor, correctAnswerId);
  }

  /*
   * Collect data from the cursor to compile a list of Question objects
   */
  private List<String> cursorToAnswers(Cursor cursor, long correctAnswerId) {
    ArrayList<String> output = new ArrayList<String>();
    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
      long id = cursor.getLong(0);
      String answer = cursor.getString(1);
      if (id == correctAnswerId) {
        output.add(0, answer);
      }
      output.add(answer);
      cursor.moveToNext();
    }
    return output;
  }
}

Java Source Code List

• Back to SQLite/example  ↑