Code example for SQLiteDatabase

Methods: insertqueryupdate

0
        addChuck(); 
        addJulie(); 
 
        assertThat(updateName("Belvedere")).isEqualTo(2); 
 
        Cursor cursor = database.query("table_name", new String[]{"id", "name"}, null, null, null, null, null);
        assertThat(cursor.moveToFirst()).isTrue();
        assertThat(cursor.getCount()).isEqualTo(2);
 
        assertIdAndName(cursor, 1234L, "Belvedere");
        assertThat(cursor.moveToNext()).isTrue();
 
        assertIdAndName(cursor, 1235L, "Belvedere");
        assertThat(cursor.isLast()).isTrue();
        assertThat(cursor.moveToNext()).isFalse();
        assertThat(cursor.isAfterLast()).isTrue();
        assertThat(cursor.moveToNext()).isFalse();
    } 
 
    @Test 
    public void testDelete() throws Exception { 
        addChuck(); 
 
        int deleted = database.delete("table_name", "id=1234", null);
        assertThat(deleted).isEqualTo(1);
 
        assertEmptyDatabase(); 
    } 
 
    @Test 
    public void testDeleteNoMatch() throws Exception { 
        addChuck(); 
 
        int deleted = database.delete("table_name", "id=5678", null);
        assertThat(deleted).isEqualTo(0);
 
        assertNonEmptyDatabase(); 
    } 
 
    @Test 
    public void testDeleteAll() throws Exception { 
        addChuck(); 
        addJulie(); 
 
        int deleted = database.delete("table_name", "1", null);
        assertThat(deleted).isEqualTo(2);
 
        assertEmptyDatabase(); 
    } 
 
 
    @Test 
    public void testExecSQL() throws Exception { 
        Statement statement;
        ResultSet resultSet;
 
        database.execSQL("INSERT INTO table_name (id, name) VALUES(1234, 'Chuck');");
 
        statement = shadowOf(database).getConnection().createStatement();
        resultSet = statement.executeQuery("SELECT COUNT(*) FROM table_name");
        assertThat(resultSet.next()).isTrue();
        assertThat(resultSet.getInt(1)).isEqualTo(1);
 
        statement = shadowOf(database).getConnection().createStatement();
        resultSet = statement.executeQuery("SELECT * FROM table_name");
        assertThat(resultSet.next()).isTrue();
        assertThat(resultSet.getInt(1)).isEqualTo(1234);
        assertThat(resultSet.getString(4)).isEqualTo("Chuck");
    } 
 
    @Test 
    public void testExecSQLParams() throws Exception { 
        Statement statement;
        ResultSet resultSet;
 
        database.execSQL("CREATE TABLE `routine` (`id` INTEGER PRIMARY KEY AUTOINCREMENT , `name` VARCHAR , `lastUsed` INTEGER DEFAULT 0 ,  UNIQUE (`name`)) ", new Object[]{});
        database.execSQL("INSERT INTO `routine` (`name` ,`lastUsed` ) VALUES (?,?)", new Object[]{"Leg Press", 0});
        database.execSQL("INSERT INTO `routine` (`name` ,`lastUsed` ) VALUES (?,?)", new Object[]{"Bench Press", 1});
 
        statement = shadowOf(database).getConnection().createStatement();
        resultSet = statement.executeQuery("SELECT COUNT(*) FROM `routine`");
        assertThat(resultSet.next()).isTrue();
        assertThat(resultSet.getInt(1)).isEqualTo(2);
 
        statement = shadowOf(database).getConnection().createStatement();
        resultSet = statement.executeQuery("SELECT `id`, `name` ,`lastUsed` FROM `routine`");
        assertThat(resultSet.next()).isTrue();
        assertThat(resultSet.getInt(1)).isEqualTo(1);
        assertThat(resultSet.getString(2)).isEqualTo("Leg Press");
        assertThat(resultSet.getInt(3)).isEqualTo(0);
        assertThat(resultSet.next()).isTrue();
        assertThat(resultSet.getLong(1)).isEqualTo(2L);
        assertThat(resultSet.getString(2)).isEqualTo("Bench Press");
        assertThat(resultSet.getInt(3)).isEqualTo(1);
    } 
 
    @Test(expected = android.database.SQLException.class) 
    public void testExecSQLException() throws Exception { 
        database.execSQL("INSERT INTO table_name;");    // invalid SQL
    } 
 
    @Test(expected = IllegalArgumentException.class) 
    public void testExecSQLException2() throws Exception { 
        database.execSQL("insert into exectable (first_column) values (?);", null);
    } 
 
    @Test(expected = IllegalArgumentException.class) 
    public void testExecSQLException4() throws Exception { 
        database.execSQL("insert into exectable (first_column) values ('sdfsfs');", null);
    } 
 
    @Test(expected = Exception.class) 
    public void testExecSQLException5() throws Exception { 
        //TODO: make this throw android.database.SQLException.class 
        database.execSQL("insert into exectable (first_column) values ('kjhk');", new String[]{"xxxx"});
    } 
 
    @Test(expected = Exception.class) 
    public void testExecSQLException6() throws Exception { 
        //TODO: make this throw android.database.SQLException.class 
        database.execSQL("insert into exectable (first_column) values ('kdfd');", new String[]{null});
    } 
 
    @Test 
    public void testExecSQL2() throws Exception { 
        database.execSQL("insert into exectable (first_column) values ('eff');", new String[]{});
    } 
 
    @Test 
    public void testExecSQLInsertNull() throws Exception { 
        String name = "nullone";
 
        database.execSQL("insert into exectable (first_column, name) values (?,?);", new String[]{null, name});
 
        Cursor cursor = database.rawQuery("select * from exectable WHERE `name` = ?", new String[]{name});
        cursor.moveToFirst();
        int firstIndex = cursor.getColumnIndex("first_column");
        int nameIndex = cursor.getColumnIndex("name");
        assertThat(cursor.getString(nameIndex)).isEqualTo(name);
        assertThat(cursor.getString(firstIndex)).isEqualTo((String) null);
 
    } 
 
    @Test(expected = Exception.class) 
    public void testExecSQLInsertNullShouldBeException() throws Exception { 
        //this inserts null in android, but it when it happens it is likely an error.  H2 throws an exception.  So we'll make Robolectric expect an Exception so that the error can be found. 
 
        database.delete("exectable", null, null);
 
        Cursor cursor = database.rawQuery("select * from exectable", null);
        cursor.moveToFirst();
        assertThat(cursor.getCount()).isEqualTo(0);
 
        database.execSQL("insert into exectable (first_column) values (?);", new String[]{});
        Cursor cursor2 = database.rawQuery("select * from exectable", new String[]{null});
        cursor.moveToFirst();
        assertThat(cursor2.getCount()).isEqualTo(1);
 
    } 
 
    @Test 
    public void testExecSQLAutoIncrementSQLite() throws Exception { 
        database.execSQL("CREATE TABLE auto_table (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(255));");
 
        ContentValues values = new ContentValues();
        values.put("name", "Chuck");
 
        long key = database.insert("auto_table", null, values);
        assertThat(key).isNotEqualTo(0L);
 
        long key2 = database.insert("auto_table", null, values);
        assertThat(key2).isNotEqualTo(key);
    } 
 
    @Test(expected = IllegalStateException.class) 
    public void testClose() throws Exception { 
        database.close();
 
        database.execSQL("INSERT INTO table_name (id, name) VALUES(1234, 'Chuck');");
    } 
 
    @Test 
    public void testIsOpen() throws Exception { 
        assertThat(database.isOpen()).isTrue();
        database.close();
        assertThat(database.isOpen()).isFalse();
    } 
 
    @Test 
    public void shouldStoreGreatBigHonkinIntegersCorrectly() throws Exception { 
        database.execSQL("INSERT INTO table_name(big_int) VALUES(1234567890123456789);");
        Cursor cursor = database.query("table_name", new String[]{"big_int"}, null, null, null, null, null);
        cursor.moveToFirst();
        assertEquals(1234567890123456789L, cursor.getLong(0));
    } 
 
    @Test 
    public void testSuccessTransaction() throws Exception { 
        assertThat(shDatabase.isTransactionSuccess()).isFalse();
        database.beginTransaction();
        assertThat(shDatabase.isTransactionSuccess()).isFalse();
        database.execSQL("INSERT INTO table_name (id, name) VALUES(1234, 'Chuck');");
        assertThat(shDatabase.isTransactionSuccess()).isFalse();
        database.setTransactionSuccessful();
        assertThat(shDatabase.isTransactionSuccess()).isTrue();
        database.endTransaction();
        assertThat(shDatabase.isTransactionSuccess()).isFalse();
 
        Statement statement = shadowOf(database).getConnection().createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT COUNT(*) FROM table_name");
        assertThat(resultSet.next()).isTrue();
        assertThat(resultSet.getInt(1)).isEqualTo(1);
    } 
 
    @Test 
    public void testFailureTransaction() throws Exception { 
        assertThat(shDatabase.isTransactionSuccess()).isFalse();
        database.beginTransaction();
        assertThat(shDatabase.isTransactionSuccess()).isFalse();
 
        database.execSQL("INSERT INTO table_name (id, name) VALUES(1234, 'Chuck');");
 
        Statement statement = shadowOf(database).getConnection().createStatement();
        final String select = "SELECT COUNT(*) FROM table_name";
 
        ResultSet rs = statement.executeQuery(select);
        assertThat(rs.next()).isTrue();
        assertThat(rs.getInt(1)).isEqualTo(1);
        rs.close();
 
        assertThat(shDatabase.isTransactionSuccess()).isFalse();
        database.endTransaction();
 
        statement = shadowOf(database).getConnection().createStatement();
        rs = statement.executeQuery(select);
        assertThat(rs.next()).isTrue();
        assertThat(rs.getInt(1)).isEqualTo(0);
 
        assertThat(shDatabase.isTransactionSuccess()).isFalse();
    } 
 
    @Test 
    public void testSuccessNestedTransaction() throws Exception { 
        assertThat(shDatabase.isTransactionSuccess()).isFalse();
        database.beginTransaction();
        database.execSQL("INSERT INTO table_name (id, name) VALUES(1234, 'Chuck');");
        assertThat(shDatabase.isTransactionSuccess()).isFalse();
        database.beginTransaction();
        assertThat(shDatabase.isTransactionSuccess()).isFalse();
        database.execSQL("INSERT INTO table_name (id, name) VALUES(12345, 'Julie');");
        database.setTransactionSuccessful();
        assertThat(shDatabase.isTransactionSuccess()).isTrue();
        database.endTransaction();
        assertThat(shDatabase.isTransactionSuccess()).isFalse();
        database.setTransactionSuccessful();
        assertThat(shDatabase.isTransactionSuccess()).isTrue();
        database.endTransaction();
        assertThat(shDatabase.isTransactionSuccess()).isFalse();
        Statement statement = shadowOf(database).getConnection().createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT COUNT(*) FROM table_name");
        assertThat(resultSet.next()).isTrue();
        assertThat(resultSet.getInt(1)).isEqualTo(2);
    } 
 
    @Test 
    public void testFailureNestedTransaction() throws Exception { 
        assertThat(shDatabase.isTransactionSuccess()).isFalse();
        database.beginTransaction();
        assertThat(shDatabase.isTransactionSuccess()).isFalse();
        database.execSQL("INSERT INTO table_name (id, name) VALUES(1234, 'Chuck');");
        database.beginTransaction();
        assertThat(shDatabase.isTransactionSuccess()).isFalse();
        database.execSQL("INSERT INTO table_name (id, name) VALUES(12345, 'Julie');");
        database.endTransaction();
        assertThat(shDatabase.isTransactionSuccess()).isFalse();
        database.setTransactionSuccessful();
        assertThat(shDatabase.isTransactionSuccess()).isFalse();
        database.endTransaction();
        assertThat(shDatabase.isTransactionSuccess()).isFalse();
        Statement statement = shadowOf(database).getConnection().createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT COUNT(*) FROM table_name");
        assertThat(resultSet.next()).isTrue();
        assertThat(resultSet.getInt(1)).isEqualTo(0);
    } 
 
    @Test 
    public void testTransactionAlreadySuccessful() { 
        database.beginTransaction();
        database.setTransactionSuccessful();
        try { 
            database.setTransactionSuccessful();
            fail("didn't receive the expected IllegalStateException"); 
        } catch (IllegalStateException e) {
            assertThat(e.getMessage()).isEqualTo("transaction already successfully");
        } 
    } 
     
    @Test 
    public void testInTransaction() throws Exception { 
        assertThat(database.inTransaction()).isFalse();
    	database.beginTransaction();
        assertThat(database.inTransaction()).isTrue();
    	database.endTransaction();
        assertThat(database.inTransaction()).isFalse();
    } 
 
    protected long addChuck() { 
        return addPerson(1234L, "Chuck"); 
    } 
 
    protected long addJulie() { 
        return addPerson(1235L, "Julie"); 
    } 
 
    protected long addPerson(long id, String name) {
        ContentValues values = new ContentValues();
        values.put("id", id);
        values.put("name", name);
        return database.insert("table_name", null, values);
    } 
 
    protected int updateName(long id, String name) {
        ContentValues values = new ContentValues();
        values.put("name", name);
        return database.update("table_name", values, "id=" + id, null);
    } 
 
    protected int updateName(String name) {
        ContentValues values = new ContentValues();
        values.put("name", name);
        return database.update("table_name", values, null, null);
    } 
 
    protected void assertIdAndName(Cursor cursor, long id, String name) {
        long idValueFromDatabase;
        String stringValueFromDatabase;