Java Code Examples for android.database.sqlite.SQLiteOpenHelper#getReadableDatabase()

public boolean hasHistoryItems() {
  SQLiteOpenHelper helper = new DBHelper(activity);
  SQLiteDatabase db = null;
  Cursor cursor = null;
  try {
    db = helper.getReadableDatabase();
    cursor = db.query(DBHelper.TABLE_NAME, COUNT_COLUMN, null, null, null, null, null);
    cursor.moveToFirst();
    return cursor.getInt(0) > 0;
  } finally {
    close(cursor, db);
  }
}
 

public HistoryItem buildHistoryItem(int number) {
  SQLiteOpenHelper helper = new DBHelper(activity);
  SQLiteDatabase db = null;
  Cursor cursor = null;
  try {
    db = helper.getReadableDatabase();
    cursor = db.query(DBHelper.TABLE_NAME, COLUMNS, null, null, null, null, DBHelper.TIMESTAMP_COL + " DESC");
    cursor.move(number + 1);
    String text = cursor.getString(0);
    String display = cursor.getString(1);
    String format = cursor.getString(2);
    long timestamp = cursor.getLong(3);
    String details = cursor.getString(4);
    Result result = new Result(text, null, null, BarcodeFormat.valueOf(format), timestamp);
    return new HistoryItem(result, display, details);
  } finally {
    close(cursor, db);
  }
}
 

public List<HistoryItem> buildHistoryItems() {
  SQLiteOpenHelper helper = new DBHelper(activity);
  List<HistoryItem> items = new ArrayList<>();
  SQLiteDatabase db = null;
  Cursor cursor = null;
  try {
    db = helper.getReadableDatabase();
    cursor = db.query(DBHelper.TABLE_NAME, COLUMNS, null, null, null, null, DBHelper.TIMESTAMP_COL + " DESC");
    while (cursor.moveToNext()) {
      String text = cursor.getString(0);
      String display = cursor.getString(1);
      String format = cursor.getString(2);
      long timestamp = cursor.getLong(3);
      String details = cursor.getString(4);
      Result result = new Result(text, null, null, BarcodeFormat.valueOf(format), timestamp);
      items.add(new HistoryItem(result, display, details));
    }
  } finally {
    close(cursor, db);
  }
  return items;
}
 

public HistoryItem buildHistoryItem(int number) {
  SQLiteOpenHelper helper = new DBHelper(activity);
  SQLiteDatabase db = null;
  Cursor cursor = null;
  try {
    db = helper.getReadableDatabase();
    cursor = db.query(DBHelper.TABLE_NAME, COLUMNS, null, null, null, null, DBHelper.TIMESTAMP_COL + " DESC");
    cursor.move(number + 1);
    String text = cursor.getString(0);
    String display = cursor.getString(1);
    String format = cursor.getString(2);
    long timestamp = cursor.getLong(3);
    String details = cursor.getString(4);
    Result result = new Result(text, null, null, BarcodeFormat.valueOf(format), timestamp);
    return new HistoryItem(result, display, details);
  } finally {
    close(cursor, db);
  }
}
 

public HistoryItem buildHistoryItem(int number) {
  SQLiteOpenHelper helper = new DBHelper(activity);
  SQLiteDatabase db = null;
  Cursor cursor = null;
  try {
    db = helper.getReadableDatabase();
    cursor = db.query(DBHelper.TABLE_NAME, COLUMNS, null, null, null, null, DBHelper.TIMESTAMP_COL + " DESC");
    cursor.move(number + 1);
    String text = cursor.getString(0);
    String display = cursor.getString(1);
    String format = cursor.getString(2);
    long timestamp = cursor.getLong(3);
    String details = cursor.getString(4);
    Result result = new Result(text, null, null, BarcodeFormat.valueOf(format), timestamp);
    return new HistoryItem(result, display, details);
  } finally {
    close(cursor, db);
  }
}
 

public List<HistoryItem> buildHistoryItems() {
  SQLiteOpenHelper helper = new DBHelper(activity);
  List<HistoryItem> items = new ArrayList<>();
  SQLiteDatabase db = null;
  Cursor cursor = null;
  try {
    db = helper.getReadableDatabase();
    cursor = db.query(DBHelper.TABLE_NAME, COLUMNS, null, null, null, null, DBHelper.TIMESTAMP_COL + " DESC");
    while (cursor.moveToNext()) {
      String text = cursor.getString(0);
      String display = cursor.getString(1);
      String format = cursor.getString(2);
      long timestamp = cursor.getLong(3);
      String details = cursor.getString(4);
      Result result = new Result(text, null, null, BarcodeFormat.valueOf(format), timestamp);
      items.add(new HistoryItem(result, display, details));
    }
  } finally {
    close(cursor, db);
  }
  return items;
}
 

public List<HistoryItem> buildHistoryItems() {
  SQLiteOpenHelper helper = new DBHelper(activity);
  List<HistoryItem> items = new ArrayList<>();
  SQLiteDatabase db = null;
  Cursor cursor = null;
  try {
    db = helper.getReadableDatabase();
    cursor = db.query(DBHelper.TABLE_NAME, COLUMNS, null, null, null, null, DBHelper.TIMESTAMP_COL + " DESC");
    while (cursor.moveToNext()) {
      String text = cursor.getString(0);
      String display = cursor.getString(1);
      String format = cursor.getString(2);
      long timestamp = cursor.getLong(3);
      String details = cursor.getString(4);
      Result result = new Result(text, null, null, BarcodeFormat.valueOf(format), timestamp);
      items.add(new HistoryItem(result, display, details));
    }
  } finally {
    close(cursor, db);
  }
  return items;
}
 

public List<HistoryItem> buildHistoryItems() {
  SQLiteOpenHelper helper = new DBHelper(activity);
  List<HistoryItem> items = new ArrayList<>();
  SQLiteDatabase db = null;
  Cursor cursor = null;
  try {
    db = helper.getReadableDatabase();
    cursor = db.query(DBHelper.TABLE_NAME, COLUMNS, null, null, null, null, DBHelper.TIMESTAMP_COL + " DESC");
    while (cursor.moveToNext()) {
      String text = cursor.getString(0);
      String display = cursor.getString(1);
      String format = cursor.getString(2);
      long timestamp = cursor.getLong(3);
      String details = cursor.getString(4);
      Result result = new Result(text, null, null, BarcodeFormat.valueOf(format), timestamp);
      items.add(new HistoryItem(result, display, details));
    }
  } finally {
    close(cursor, db);
  }
  return items;
}
 

public HistoryItem buildHistoryItem(int number) {
  SQLiteOpenHelper helper = new DBHelper(activity);
  SQLiteDatabase db = null;
  Cursor cursor = null;
  try {
    db = helper.getReadableDatabase();
    cursor = db.query(DBHelper.TABLE_NAME, COLUMNS, null, null, null, null, DBHelper.TIMESTAMP_COL + " DESC");
    cursor.move(number + 1);
    String text = cursor.getString(0);
    String display = cursor.getString(1);
    String format = cursor.getString(2);
    long timestamp = cursor.getLong(3);
    String details = cursor.getString(4);
    Result result = new Result(text, null, null, BarcodeFormat.valueOf(format), timestamp);
    return new HistoryItem(result, display, details);
  } finally {
    close(cursor, db);
  }
}
 

public List<HistoryItem> buildHistoryItems() {
  SQLiteOpenHelper helper = new DBHelper(activity);
  List<HistoryItem> items = new ArrayList<>();
  SQLiteDatabase db = null;
  Cursor cursor = null;
  try {
    db = helper.getReadableDatabase();
    cursor = db.query(DBHelper.TABLE_NAME, COLUMNS, null, null, null, null, DBHelper.TIMESTAMP_COL + " DESC");
    while (cursor.moveToNext()) {
      String text = cursor.getString(0);
      String display = cursor.getString(1);
      String format = cursor.getString(2);
      long timestamp = cursor.getLong(3);
      String details = cursor.getString(4);
      Result result = new Result(text, null, null, BarcodeFormat.valueOf(format), timestamp);
      items.add(new HistoryItem(result, display, details));
    }
  } finally {
    close(cursor, db);
  }
  return items;
}
 

public boolean hasHistoryItems() {
  SQLiteOpenHelper helper = new DBHelper(activity);
  SQLiteDatabase db = null;
  Cursor cursor = null;
  try {
    db = helper.getReadableDatabase();
    cursor = db.query(DBHelper.TABLE_NAME, COUNT_COLUMN, null, null, null, null, null);
    cursor.moveToFirst();
    return cursor.getInt(0) > 0;
  } finally {
    close(cursor, db);
  }
}
 

public boolean hasHistoryItems() {
  SQLiteOpenHelper helper = new DBHelper(activity);
  SQLiteDatabase db = null;
  Cursor cursor = null;
  try {
    db = helper.getReadableDatabase();
    cursor = db.query(DBHelper.TABLE_NAME, COUNT_COLUMN, null, null, null, null, null);
    cursor.moveToFirst();
    return cursor.getInt(0) > 0;
  } finally {
    close(cursor, db);
  }
}
 

public boolean hasHistoryItems() {
  SQLiteOpenHelper helper = new DBHelper(activity);
  SQLiteDatabase db = null;
  Cursor cursor = null;
  try {
    db = helper.getReadableDatabase();
    cursor = db.query(DBHelper.TABLE_NAME, COUNT_COLUMN, null, null, null, null, null);
    cursor.moveToFirst();
    return cursor.getInt(0) > 0;
  } finally {
    close(cursor, db);
  }
}
 

public boolean hasHistoryItems() {
  SQLiteOpenHelper helper = new DBHelper(activity);
  SQLiteDatabase db = null;
  Cursor cursor = null;
  try {
    db = helper.getReadableDatabase();
    cursor = db.query(DBHelper.TABLE_NAME, COUNT_COLUMN, null, null, null, null, null);
    cursor.moveToFirst();
    return cursor.getInt(0) > 0;
  } finally {
    close(cursor, db);
  }
}
 

private Cursor getDisplayOptionsCursor(SQLiteOpenHelper helper) {
	SQLiteDatabase db = helper.getReadableDatabase();
	
	String sql = "select distinct topic._id as _id, topic.title as title from topic, topicvideo, video where video.download_status>? and topicvideo.video_id=video.readable_id and topicvideo.topic_id=topic._id group by title";
	String[] selectionArgs = {String.valueOf(Video.DL_STATUS_NOT_STARTED)};
	Cursor mainCursor = db.rawQuery(sql, selectionArgs);
	
	sql = "select '-1' as _id, 'All Videos' as title";
	Cursor headerCursor = db.rawQuery(sql, null);
	
	MergeCursor cursor = new MergeCursor(new Cursor[] {headerCursor, mainCursor});
	return cursor;
}
 

public boolean hasHistoryItems() {
  SQLiteOpenHelper helper = new DBHelper(activity);
  SQLiteDatabase db = null;
  Cursor cursor = null;
  try {
    db = helper.getReadableDatabase();
    cursor = db.query(DBHelper.TABLE_NAME, COUNT_COLUMN, null, null, null, null, null);
    cursor.moveToFirst();
    return cursor.getInt(0) > 0;
  } finally {
    close(cursor, db);
  }
}
 

/**
 * Tests that onUpgrade works by inserting 2 rows then calling onUpgrade and verifies that the
 * database has been successfully dropped and recreated by checking that the database is there
 * but empty
 * @throws Exception in case the constructor hasn't been implemented yet
 */
@Test
public void upgrade_database_test() throws Exception{

    /* Insert 2 rows before we upgrade to check that we dropped the database correctly */

    /* Use reflection to try to run the correct constructor whenever implemented */
    SQLiteOpenHelper dbHelper =
            (SQLiteOpenHelper) mDbHelperClass.getConstructor(Context.class).newInstance(mContext);

    /* Use WaitlistDbHelper to get access to a writable database */
    SQLiteDatabase database = dbHelper.getWritableDatabase();

    ContentValues testValues = new ContentValues();
    testValues.put(WaitlistContract.WaitlistEntry.COLUMN_GUEST_NAME, "test name");
    testValues.put(WaitlistContract.WaitlistEntry.COLUMN_PARTY_SIZE, 99);

    /* Insert ContentValues into database and get first row ID back */
    long firstRowId = database.insert(
            WaitlistContract.WaitlistEntry.TABLE_NAME,
            null,
            testValues);

    /* Insert ContentValues into database and get another row ID back */
    long secondRowId = database.insert(
            WaitlistContract.WaitlistEntry.TABLE_NAME,
            null,
            testValues);

    dbHelper.onUpgrade(database, 0, 1);
    database = dbHelper.getReadableDatabase();

    /* This Cursor will contain the names of each table in our database */
    Cursor tableNameCursor = database.rawQuery(
            "SELECT name FROM sqlite_master WHERE type='table' AND name='" +
                    WaitlistContract.WaitlistEntry.TABLE_NAME + "'",
            null);

    assertTrue(tableNameCursor.getCount() == 1);

    /*
     * Query the database and receive a Cursor. A Cursor is the primary way to interact with
     * a database in Android.
     */
    Cursor wCursor = database.query(
            /* Name of table on which to perform the query */
            WaitlistContract.WaitlistEntry.TABLE_NAME,
            /* Columns; leaving this null returns every column in the table */
            null,
            /* Optional specification for columns in the "where" clause above */
            null,
            /* Values for "where" clause */
            null,
            /* Columns to group by */
            null,
            /* Columns to filter by row groups */
            null,
            /* Sort order to return in Cursor */
            null);

    /* Cursor.moveToFirst will return false if there are no records returned from your query */

    assertFalse("Database doesn't seem to have been dropped successfully when upgrading",
            wCursor.moveToFirst());

    tableNameCursor.close();
    database.close();
}
 

/**
 * Tests that onUpgrade works by inserting 2 rows then calling onUpgrade and verifies that the
 * database has been successfully dropped and recreated by checking that the database is there
 * but empty
 * @throws Exception in case the constructor hasn't been implemented yet
 */
@Test
public void upgrade_database_test() throws Exception{

    /* Insert 2 rows before we upgrade to check that we dropped the database correctly */

    /* Use reflection to try to run the correct constructor whenever implemented */
    SQLiteOpenHelper dbHelper =
            (SQLiteOpenHelper) mDbHelperClass.getConstructor(Context.class).newInstance(mContext);

    /* Use WaitlistDbHelper to get access to a writable database */
    SQLiteDatabase database = dbHelper.getWritableDatabase();

    ContentValues testValues = new ContentValues();
    testValues.put(WaitlistContract.WaitlistEntry.COLUMN_GUEST_NAME, "test name");
    testValues.put(WaitlistContract.WaitlistEntry.COLUMN_PARTY_SIZE, 99);

    /* Insert ContentValues into database and get first row ID back */
    long firstRowId = database.insert(
            WaitlistContract.WaitlistEntry.TABLE_NAME,
            null,
            testValues);

    /* Insert ContentValues into database and get another row ID back */
    long secondRowId = database.insert(
            WaitlistContract.WaitlistEntry.TABLE_NAME,
            null,
            testValues);

    dbHelper.onUpgrade(database, 0, 1);
    database = dbHelper.getReadableDatabase();

    /* This Cursor will contain the names of each table in our database */
    Cursor tableNameCursor = database.rawQuery(
            "SELECT name FROM sqlite_master WHERE type='table' AND name='" +
                    WaitlistContract.WaitlistEntry.TABLE_NAME + "'",
            null);

    assertTrue(tableNameCursor.getCount() == 1);

    /*
     * Query the database and receive a Cursor. A Cursor is the primary way to interact with
     * a database in Android.
     */
    Cursor wCursor = database.query(
            /* Name of table on which to perform the query */
            WaitlistContract.WaitlistEntry.TABLE_NAME,
            /* Columns; leaving this null returns every column in the table */
            null,
            /* Optional specification for columns in the "where" clause above */
            null,
            /* Values for "where" clause */
            null,
            /* Columns to group by */
            null,
            /* Columns to filter by row groups */
            null,
            /* Sort order to return in Cursor */
            null);

    /* Cursor.moveToFirst will return false if there are no records returned from your query */

    assertFalse("Database doesn't seem to have been dropped successfully when upgrading",
            wCursor.moveToFirst());

    tableNameCursor.close();
    database.close();
}
 

/**
 * Tests that onUpgrade works by inserting 2 rows then calling onUpgrade and verifies that the
 * database has been successfully dropped and recreated by checking that the database is there
 * but empty
 * @throws Exception in case the constructor hasn't been implemented yet
 */
@Test
public void upgrade_database_test() throws Exception{

    /* Insert 2 rows before we upgrade to check that we dropped the database correctly */

    /* Use reflection to try to run the correct constructor whenever implemented */
    SQLiteOpenHelper dbHelper =
            (SQLiteOpenHelper) mDbHelperClass.getConstructor(Context.class).newInstance(mContext);

    /* Use WaitlistDbHelper to get access to a writable database */
    SQLiteDatabase database = dbHelper.getWritableDatabase();

    ContentValues testValues = new ContentValues();
    testValues.put(WaitlistContract.WaitlistEntry.COLUMN_GUEST_NAME, "test name");
    testValues.put(WaitlistContract.WaitlistEntry.COLUMN_PARTY_SIZE, 99);

    /* Insert ContentValues into database and get first row ID back */
    long firstRowId = database.insert(
            WaitlistContract.WaitlistEntry.TABLE_NAME,
            null,
            testValues);

    /* Insert ContentValues into database and get another row ID back */
    long secondRowId = database.insert(
            WaitlistContract.WaitlistEntry.TABLE_NAME,
            null,
            testValues);

    dbHelper.onUpgrade(database, 0, 1);
    database = dbHelper.getReadableDatabase();

    /* This Cursor will contain the names of each table in our database */
    Cursor tableNameCursor = database.rawQuery(
            "SELECT name FROM sqlite_master WHERE type='table' AND name='" +
                    WaitlistContract.WaitlistEntry.TABLE_NAME + "'",
            null);

    assertTrue(tableNameCursor.getCount() == 1);

    /*
     * Query the database and receive a Cursor. A Cursor is the primary way to interact with
     * a database in Android.
     */
    Cursor wCursor = database.query(
            /* Name of table on which to perform the query */
            WaitlistContract.WaitlistEntry.TABLE_NAME,
            /* Columns; leaving this null returns every column in the table */
            null,
            /* Optional specification for columns in the "where" clause above */
            null,
            /* Values for "where" clause */
            null,
            /* Columns to group by */
            null,
            /* Columns to filter by row groups */
            null,
            /* Sort order to return in Cursor */
            null);

    /* Cursor.moveToFirst will return false if there are no records returned from your query */

    assertFalse("Database doesn't seem to have been dropped successfully when upgrading",
            wCursor.moveToFirst());

    tableNameCursor.close();
    database.close();
}
 

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_drink);
    
    //Get the drink from the intent
    int drinkNo = (Integer)getIntent().getExtras().get(EXTRA_DRINKNO);

    //Create a cursor
    try {
        SQLiteOpenHelper starbuzzDatabaseHelper = new StarbuzzDatabaseHelper(this);
        SQLiteDatabase db = starbuzzDatabaseHelper.getReadableDatabase();
        Cursor cursor = db.query ("DRINK",
                                  new String[] {"NAME", "DESCRIPTION", "IMAGE_RESOURCE_ID", "FAVORITE"},
                                  "_id = ?",
                                  new String[] {Integer.toString(drinkNo)},
                                  null, null,null);

        //Move to the first record in the Cursor
        if (cursor.moveToFirst()) {

            //Get the drink details from the cursor
            String nameText = cursor.getString(0);
            String descriptionText = cursor.getString(1);
            int photoId = cursor.getInt(2);
            boolean isFavorite = (cursor.getInt(3) == 1);

            //Populate the drink name
            TextView name = (TextView)findViewById(R.id.name);
            name.setText(nameText);

            //Populate the drink description
            TextView description = (TextView)findViewById(R.id.description);
            description.setText(descriptionText);

            //Populate the drink image
            ImageView photo = (ImageView)findViewById(R.id.photo);
            photo.setImageResource(photoId);
            photo.setContentDescription(nameText);

            //Populate the favorite checkbox
            CheckBox favorite = (CheckBox)findViewById(R.id.favorite);
            favorite.setChecked(isFavorite);
        }
        cursor.close();
        db.close();
    } catch(SQLiteException e) {
        Toast toast = Toast.makeText(this, "Database unavailable", Toast.LENGTH_SHORT);
        toast.show();
    }
}