1 package org.robolectric.shadows; 2 3 import static com.google.common.truth.Truth.assertThat; 4 import static org.junit.Assert.fail; 5 6 import android.database.Cursor; 7 import android.database.sqlite.SQLiteDatabase; 8 import android.database.sqlite.SQLiteDoneException; 9 import android.database.sqlite.SQLiteStatement; 10 import androidx.test.core.app.ApplicationProvider; 11 import androidx.test.ext.junit.runners.AndroidJUnit4; 12 import java.io.File; 13 import org.junit.After; 14 import org.junit.Before; 15 import org.junit.Test; 16 import org.junit.runner.RunWith; 17 18 @RunWith(AndroidJUnit4.class) 19 public class SQLiteStatementTest { 20 private SQLiteDatabase database; 21 22 @Before setUp()23 public void setUp() throws Exception { 24 final File databasePath = ApplicationProvider.getApplicationContext().getDatabasePath("path"); 25 databasePath.getParentFile().mkdirs(); 26 27 database = SQLiteDatabase.openOrCreateDatabase(databasePath.getPath(), null); 28 SQLiteStatement createStatement = database.compileStatement("CREATE TABLE `routine` (`id` INTEGER PRIMARY KEY AUTOINCREMENT , `name` VARCHAR , `lastUsed` INTEGER DEFAULT 0 , UNIQUE (`name`)) ;"); 29 createStatement.execute(); 30 31 SQLiteStatement createStatement2 = database.compileStatement("CREATE TABLE `countme` (`id` INTEGER PRIMARY KEY AUTOINCREMENT , `name` VARCHAR , `lastUsed` INTEGER DEFAULT 0 , UNIQUE (`name`)) ;"); 32 createStatement2.execute(); 33 } 34 35 @After tearDown()36 public void tearDown() throws Exception { 37 database.close(); 38 } 39 40 @Test testExecuteInsert()41 public void testExecuteInsert() throws Exception { 42 SQLiteStatement insertStatement = database.compileStatement("INSERT INTO `routine` (`name` ,`lastUsed` ) VALUES (?,?)"); 43 insertStatement.bindString(1, "Leg Press"); 44 insertStatement.bindLong(2, 0); 45 long pkeyOne = insertStatement.executeInsert(); 46 insertStatement.clearBindings(); 47 insertStatement.bindString(1, "Bench Press"); 48 insertStatement.bindLong(2, 1); 49 long pkeyTwo = insertStatement.executeInsert(); 50 51 assertThat(pkeyOne).isEqualTo(1L); 52 assertThat(pkeyTwo).isEqualTo(2L); 53 54 Cursor dataCursor = database.rawQuery("SELECT COUNT(*) FROM `routine`", null); 55 assertThat(dataCursor.moveToFirst()).isTrue(); 56 assertThat(dataCursor.getInt(0)).isEqualTo(2); 57 dataCursor.close(); 58 59 dataCursor = database.rawQuery("SELECT `id`, `name` ,`lastUsed` FROM `routine`", null); 60 assertThat(dataCursor.moveToNext()).isTrue(); 61 assertThat(dataCursor.getInt(0)).isEqualTo(1); 62 assertThat(dataCursor.getString(1)).isEqualTo("Leg Press"); 63 assertThat(dataCursor.getInt(2)).isEqualTo(0); 64 assertThat(dataCursor.moveToNext()).isTrue(); 65 assertThat(dataCursor.getLong(0)).isEqualTo(2L); 66 assertThat(dataCursor.getString(1)).isEqualTo("Bench Press"); 67 assertThat(dataCursor.getInt(2)).isEqualTo(1); 68 dataCursor.close(); 69 } 70 71 @Test testExecuteInsertShouldCloseGeneratedKeysResultSet()72 public void testExecuteInsertShouldCloseGeneratedKeysResultSet() throws Exception { 73 // NOTE: 74 // As a side-effect we will get "database locked" exception 75 // on rollback if generatedKeys wasn't closed 76 // 77 // Don't know how suitable to use Mockito here, but 78 // it will be a little bit simpler to test ShadowSQLiteStatement 79 // if actualDBStatement will be mocked 80 database.beginTransaction(); 81 try { 82 SQLiteStatement insertStatement = database.compileStatement("INSERT INTO `routine` " + 83 "(`name` ,`lastUsed`) VALUES ('test',0)"); 84 try { 85 insertStatement.executeInsert(); 86 } finally { 87 insertStatement.close(); 88 } 89 } finally { 90 database.endTransaction(); 91 } 92 } 93 94 @Test testExecuteUpdateDelete()95 public void testExecuteUpdateDelete() throws Exception { 96 97 SQLiteStatement insertStatement = database.compileStatement("INSERT INTO `routine` (`name`) VALUES (?)"); 98 insertStatement.bindString(1, "Hand Press"); 99 long pkeyOne = insertStatement.executeInsert(); 100 assertThat(pkeyOne).isEqualTo(1); 101 102 SQLiteStatement updateStatement = database.compileStatement("UPDATE `routine` SET `name`=? WHERE `id`=?"); 103 updateStatement.bindString(1, "Head Press"); 104 updateStatement.bindLong(2, pkeyOne); 105 assertThat(updateStatement.executeUpdateDelete()).isEqualTo(1); 106 107 Cursor dataCursor = database.rawQuery("SELECT `name` FROM `routine`", null); 108 assertThat(dataCursor.moveToNext()).isTrue(); 109 assertThat(dataCursor.getString(0)).isEqualTo("Head Press"); 110 } 111 112 @Test simpleQueryTest()113 public void simpleQueryTest() throws Exception { 114 115 SQLiteStatement stmt = database.compileStatement("SELECT count(*) FROM `countme`"); 116 assertThat(stmt.simpleQueryForLong()).isEqualTo(0L); 117 assertThat(stmt.simpleQueryForString()).isEqualTo("0"); 118 119 SQLiteStatement insertStatement = database.compileStatement("INSERT INTO `countme` (`name` ,`lastUsed` ) VALUES (?,?)"); 120 insertStatement.bindString(1, "Leg Press"); 121 insertStatement.bindLong(2, 0); 122 insertStatement.executeInsert(); 123 assertThat(stmt.simpleQueryForLong()).isEqualTo(1L); 124 assertThat(stmt.simpleQueryForString()).isEqualTo("1"); 125 insertStatement.bindString(1, "Bench Press"); 126 insertStatement.bindLong(2, 1); 127 insertStatement.executeInsert(); 128 assertThat(stmt.simpleQueryForLong()).isEqualTo(2L); 129 assertThat(stmt.simpleQueryForString()).isEqualTo("2"); 130 } 131 132 @Test(expected = SQLiteDoneException.class) simpleQueryForStringThrowsSQLiteDoneExceptionTest()133 public void simpleQueryForStringThrowsSQLiteDoneExceptionTest() throws Exception { 134 //throw SQLiteDOneException if no rows returned. 135 SQLiteStatement stmt = database.compileStatement("SELECT * FROM `countme` where `name`= 'cessationoftime'"); 136 137 assertThat(stmt.simpleQueryForString()).isEqualTo("0"); 138 } 139 140 @Test(expected = SQLiteDoneException.class) simpleQueryForLongThrowsSQLiteDoneExceptionTest()141 public void simpleQueryForLongThrowsSQLiteDoneExceptionTest() throws Exception { 142 //throw SQLiteDOneException if no rows returned. 143 SQLiteStatement stmt = database.compileStatement("SELECT * FROM `countme` where `name`= 'cessationoftime'"); 144 stmt.simpleQueryForLong(); 145 } 146 147 @Test testCloseShouldCloseUnderlyingPreparedStatement()148 public void testCloseShouldCloseUnderlyingPreparedStatement() throws Exception { 149 SQLiteStatement insertStatement = database.compileStatement("INSERT INTO `routine` (`name`) VALUES (?)"); 150 insertStatement.bindString(1, "Hand Press"); 151 insertStatement.close(); 152 try { 153 insertStatement.executeInsert(); 154 fail(); 155 } catch (Exception e) { 156 assertThat(e).isInstanceOf(IllegalStateException.class); 157 } 158 } 159 } 160