1package com.xtremelabs.robolectric.shadows; 2 3import android.database.sqlite.SQLiteDatabase; 4import android.database.sqlite.SQLiteDoneException; 5import android.database.sqlite.SQLiteStatement; 6import com.xtremelabs.robolectric.WithTestDefaultsRunner; 7import org.junit.After; 8import org.junit.Before; 9import org.junit.Test; 10import org.junit.runner.RunWith; 11 12import java.sql.ResultSet; 13import java.sql.Statement; 14 15import static com.xtremelabs.robolectric.Robolectric.shadowOf; 16import static org.hamcrest.CoreMatchers.equalTo; 17import static org.junit.Assert.assertThat; 18 19@RunWith(WithTestDefaultsRunner.class) 20public class SQLiteStatementTest { 21 private SQLiteDatabase database; 22 23 @Before 24 public void setUp() throws Exception { 25 database = SQLiteDatabase.openDatabase("path", null, 0); 26 SQLiteStatement createStatement = database.compileStatement("CREATE TABLE `routine` (`id` INTEGER PRIMARY KEY AUTOINCREMENT , `name` VARCHAR , `lastUsed` INTEGER DEFAULT 0 , UNIQUE (`name`)) ;"); 27 createStatement.execute(); 28 29 SQLiteStatement createStatement2 = database.compileStatement("CREATE TABLE `countme` (`id` INTEGER PRIMARY KEY AUTOINCREMENT , `name` VARCHAR , `lastUsed` INTEGER DEFAULT 0 , UNIQUE (`name`)) ;"); 30 createStatement2.execute(); 31 } 32 33 @After 34 public void tearDown() throws Exception { 35 database.close(); 36 } 37 38 @Test 39 public void testExecuteInsert() throws Exception { 40 SQLiteStatement insertStatement = database.compileStatement("INSERT INTO `routine` (`name` ,`lastUsed` ) VALUES (?,?)"); 41 insertStatement.bindString(1, "Leg Press"); 42 insertStatement.bindLong(2, 0); 43 long pkeyOne = insertStatement.executeInsert(); 44 insertStatement.clearBindings(); 45 insertStatement.bindString(1, "Bench Press"); 46 insertStatement.bindLong(2, 1); 47 long pkeyTwo = insertStatement.executeInsert(); 48 49 assertThat(pkeyOne, equalTo(1L)); 50 assertThat(pkeyTwo, equalTo(2L)); 51 52 Statement statement = shadowOf(database).getConnection().createStatement(); 53 ResultSet resultSet = statement.executeQuery("SELECT COUNT(*) FROM `routine`"); 54 assertThat(resultSet.next(), equalTo(true)); 55 assertThat(resultSet.getInt(1), equalTo(2)); 56 57 statement = shadowOf(database).getConnection().createStatement(); 58 resultSet = statement.executeQuery("SELECT `id`, `name` ,`lastUsed` FROM `routine`"); 59 assertThat(resultSet.next(), equalTo(true)); 60 assertThat(resultSet.getInt(1), equalTo(1)); 61 assertThat(resultSet.getString(2), equalTo("Leg Press")); 62 assertThat(resultSet.getInt(3), equalTo(0)); 63 assertThat(resultSet.next(), equalTo(true)); 64 assertThat(resultSet.getLong(1), equalTo(2L)); 65 assertThat(resultSet.getString(2), equalTo("Bench Press")); 66 assertThat(resultSet.getInt(3), equalTo(1)); 67 } 68 69 @Test 70 public void simpleQueryTest() throws Exception { 71 72 SQLiteStatement stmt = database.compileStatement("SELECT count(*) FROM `countme`"); 73 assertThat(stmt.simpleQueryForLong(), equalTo(0L)); 74 assertThat(stmt.simpleQueryForString(), equalTo("0")); 75 76 SQLiteStatement insertStatement = database.compileStatement("INSERT INTO `countme` (`name` ,`lastUsed` ) VALUES (?,?)"); 77 insertStatement.bindString(1, "Leg Press"); 78 insertStatement.bindLong(2, 0); 79 insertStatement.executeInsert(); 80 assertThat(stmt.simpleQueryForLong(), equalTo(1L)); 81 assertThat(stmt.simpleQueryForString(), equalTo("1")); 82 insertStatement.bindString(1, "Bench Press"); 83 insertStatement.bindLong(2, 1); 84 insertStatement.executeInsert(); 85 assertThat(stmt.simpleQueryForLong(), equalTo(2L)); 86 assertThat(stmt.simpleQueryForString(), equalTo("2")); 87 } 88 89 @Test(expected = SQLiteDoneException.class) 90 public void simpleQueryForStringThrowsSQLiteDoneExceptionTest() throws Exception { 91 //throw SQLiteDOneException if no rows returned. 92 SQLiteStatement stmt = database.compileStatement("SELECT * FROM `countme` where `name`= 'cessationoftime'"); 93 94 assertThat(stmt.simpleQueryForString(), equalTo("0")); 95 } 96 97 @Test(expected = SQLiteDoneException.class) 98 public void simpleQueryForLongThrowsSQLiteDoneExceptionTest() throws Exception { 99 //throw SQLiteDOneException if no rows returned. 100 SQLiteStatement stmt = database.compileStatement("SELECT * FROM `countme` where `name`= 'cessationoftime'"); 101 assertThat(stmt.simpleQueryForLong(), equalTo(0L)); 102 103 } 104} 105