@Test
public void testRollback() {
sql2o
.createQuery(
"create table test_rollback_table(id integer identity primary key, value varchar(25))")
.executeUpdate();
sql2o
// first insert something, and commit it.
.beginTransaction()
.createQuery("insert into test_rollback_table(value) values (:val)")
.addParameter("val", "something")
.executeUpdate()
.commit()
// insert something else, and roll it back.
.beginTransaction()
.createQuery("insert into test_rollback_table(value) values (:val)")
.addParameter("val", "something to rollback")
.executeUpdate()
.rollback();
long rowCount =
(Long) sql2o.createQuery("select count(*) from test_rollback_table").executeScalar();
assertEquals(1, rowCount);
}
@Test
public void testInputStream() throws IOException {
String createSql = "create table blobtbl(id int identity primary key, data blob)";
sql2o.createQuery(createSql).executeUpdate();
String dataString = "test";
byte[] data = dataString.getBytes();
InputStream inputStream = new ByteArrayInputStream(data);
String insertSql = "insert into blobtbl(data) values(:data)";
sql2o.createQuery(insertSql).addParameter("data", inputStream).executeUpdate();
// select
String sql = "select id, data from blobtbl";
BlobPOJO1 pojo1 = sql2o.createQuery(sql).executeAndFetchFirst(BlobPOJO1.class);
BlobPOJO2 pojo2 = sql2o.createQuery(sql).executeAndFetchFirst(BlobPOJO2.class);
String pojo1DataString = new String(pojo1.data);
assertThat(dataString, is(equalTo(pojo1DataString)));
byte[] pojo2Data = IOUtils.toByteArray(pojo2.data);
String pojo2DataString = new String(pojo2Data);
assertThat(dataString, is(equalTo(pojo2DataString)));
}
@Test
public void testJodaTime() {
sql2o
.createQuery("create table testjoda(id int primary key, joda1 datetime, joda2 datetime)")
.executeUpdate();
sql2o
.createQuery("insert into testjoda(id, joda1, joda2) values(:id, :joda1, :joda2)")
.addParameter("id", 1)
.addParameter("joda1", new DateTime())
.addParameter("joda2", new DateTime().plusDays(-1))
.addToBatch()
.addParameter("id", 2)
.addParameter("joda1", new DateTime().plusYears(1))
.addParameter("joda2", new DateTime().plusDays(-2))
.addToBatch()
.addParameter("id", 3)
.addParameter("joda1", new DateTime().plusYears(2))
.addParameter("joda2", new DateTime().plusDays(-3))
.addToBatch()
.executeBatch();
List<JodaEntity> list =
sql2o.createQuery("select * from testjoda").executeAndFetch(JodaEntity.class);
assertTrue(list.size() == 3);
assertTrue(list.get(0).getJoda2().isBeforeNow());
}
/** ************ Helper stuff ***************** */
private void createAndFillUserTable() {
int rowCount = 10000;
sql2o
.createQuery(
"create table User(\n"
+ "id int identity primary key,\n"
+ "name varchar(20),\n"
+ "email varchar(255),\n"
+ "text varchar(100))")
.executeUpdate();
Query insQuery =
sql2o.createQuery("insert into User(name, email, text) values (:name, :email, :text)");
Date before = new Date();
for (int idx = 0; idx < rowCount; idx++) {
insQuery
.addParameter("name", "a name " + idx)
.addParameter("email", String.format("*****@*****.**", idx))
.addParameter("text", "some text")
.addToBatch();
}
insQuery.executeBatch();
Date after = new Date();
Long span = after.getTime() - before.getTime();
System.out.println(
String.format("inserted %d rows into User table. Time used: %s ms", rowCount, span));
insertIntoUsers += rowCount;
}
@Test
public void testUtilDate() {
sql2o
.createQuery("create table testutildate(id int primary key, d1 datetime, d2 timestamp)")
.executeUpdate();
sql2o
.createQuery("insert into testutildate(id, d1, d2) values(:id, :d1, :d2)")
.addParameter("id", 1)
.addParameter("d1", new Date())
.addParameter("d2", new Date())
.addToBatch()
.addParameter("id", 2)
.addParameter("d1", new Date())
.addParameter("d2", new Date())
.addToBatch()
.addParameter("id", 3)
.addParameter("d1", new Date())
.addParameter("d2", new Date())
.addToBatch()
.executeBatch();
List<UtilDateEntity> list =
sql2o.createQuery("select * from testutildate").executeAndFetch(UtilDateEntity.class);
assertTrue(list.size() == 3);
}
@Test
public void testCaseInsensitive() {
sql2o
.createQuery(
"create table testCI(id2 int primary key, value2 varchar(20), sometext varchar(20), valwithgetter varchar(20))")
.executeUpdate();
Query query =
sql2o.createQuery(
"insert into testCI(id2, value2, sometext, valwithgetter) values(:id, :value, :someText, :valwithgetter)");
for (int i = 0; i < 20; i++) {
query
.addParameter("id", i)
.addParameter("value", "some text " + i)
.addParameter("someText", "whatever " + i)
.addParameter("valwithgetter", "spaz" + i)
.addToBatch();
}
query.executeBatch();
List<CIEntity> ciEntities =
sql2o
.createQuery("select * from testCI")
.setCaseSensitive(false)
.executeAndFetch(CIEntity.class);
assertTrue(ciEntities.size() == 20);
// test defaultCaseSensitive;
sql2o.setDefaultCaseSensitive(false);
List<CIEntity> ciEntities2 =
sql2o.createQuery("select * from testCI").executeAndFetch(CIEntity.class);
assertTrue(ciEntities2.size() == 20);
}
@Test
public void testEnums() {
sql2o
.createQuery(
"create table EnumTest(id int identity primary key, enum_val varchar(10), enum_val2 int) ")
.executeUpdate();
sql2o
.createQuery("insert into EnumTest(enum_val, enum_val2) values (:val, :val2)")
.addParameter("val", TestEnum.HELLO)
.addParameter("val2", TestEnum.HELLO.ordinal())
.addToBatch()
.addParameter("val", TestEnum.WORLD)
.addParameter("val2", TestEnum.WORLD.ordinal())
.addToBatch()
.executeBatch();
List<EntityWithEnum> list =
sql2o
.createQuery("select id, enum_val val, enum_val2 val2 from EnumTest")
.executeAndFetch(EntityWithEnum.class);
assertThat(list.get(0).val, is(TestEnum.HELLO));
assertThat(list.get(0).val2, is(TestEnum.HELLO));
assertThat(list.get(1).val, is(TestEnum.WORLD));
assertThat(list.get(1).val2, is(TestEnum.WORLD));
TestEnum testEnum =
sql2o.createQuery("select 'HELLO' from (values(0))").executeScalar(TestEnum.class);
assertThat(testEnum, is(TestEnum.HELLO));
TestEnum testEnum2 =
sql2o.createQuery("select NULL from (values(0))").executeScalar(TestEnum.class);
assertThat(testEnum2, is(nullValue()));
}
@Test
public void testBatchNoTransaction() {
sql2o
.createQuery(
"create table User(\n"
+ "id int identity primary key,\n"
+ "name varchar(20),\n"
+ "email varchar(255),\n"
+ "text varchar(100))")
.executeUpdate();
String insQuery = "insert into User(name, email, text) values (:name, :email, :text)";
sql2o
.createQuery(insQuery)
.addParameter("name", "test")
.addParameter("email", "*****@*****.**")
.addParameter("text", "something exciting")
.addToBatch()
.addParameter("name", "test2")
.addParameter("email", "*****@*****.**")
.addParameter("text", "something exciting too")
.addToBatch()
.addParameter("name", "test3")
.addParameter("email", "*****@*****.**")
.addParameter("text", "blablabla")
.addToBatch()
.executeBatch();
deleteUserTable();
}
@Test
public void testExecuteAndFetchWithNulls() {
String sql =
"create table testExecWithNullsTbl ("
+ "id int identity primary key, "
+ "text varchar(255), "
+ "aNumber int, "
+ "aLongNumber bigint)";
sql2o.createQuery(sql, "testExecuteAndFetchWithNulls").executeUpdate();
Connection connection = sql2o.beginTransaction();
Query insQuery =
connection.createQuery(
"insert into testExecWithNullsTbl (text, aNumber, aLongNumber) values(:text, :number, :lnum)");
insQuery
.addParameter("text", "some text")
.addParameter("number", 2)
.addParameter("lnum", 10L)
.executeUpdate();
insQuery
.addParameter("text", "some text")
.addParameter("number", (Integer) null)
.addParameter("lnum", 10L)
.executeUpdate();
insQuery
.addParameter("text", (String) null)
.addParameter("number", 21)
.addParameter("lnum", (Long) null)
.executeUpdate();
insQuery
.addParameter("text", "some text")
.addParameter("number", 1221)
.addParameter("lnum", 10)
.executeUpdate();
insQuery
.addParameter("text", "some text")
.addParameter("number", 2311)
.addParameter("lnum", 12)
.executeUpdate();
connection.commit();
List<Entity> fetched =
sql2o.createQuery("select * from testExecWithNullsTbl").executeAndFetch(Entity.class);
assertTrue(fetched.size() == 5);
assertNull(fetched.get(2).text);
assertNotNull(fetched.get(3).text);
assertNull(fetched.get(1).aNumber);
assertNotNull(fetched.get(2).aNumber);
assertNull(fetched.get(2).aLongNumber);
assertNotNull(fetched.get(3).aLongNumber);
}
@Test
public void testExecuteScalar() {
createAndFillUserTable();
Object o = sql2o.createQuery("select text from User where id = 2").executeScalar();
assertTrue(o.getClass().equals(String.class));
Object o2 = sql2o.createQuery("select 10").executeScalar();
assertEquals(o2, 10);
deleteUserTable();
}
@Test
public void testDynamicExecuteScalar() {
Object origVal = sql2o.createQuery("select 1").executeScalar();
assertTrue(Integer.class.equals(origVal.getClass()));
assertEquals((Integer) 1, origVal);
Long intVal = sql2o.createQuery("select 1").executeScalar(Long.class);
assertEquals((Long) 1l, intVal);
Short shortVal = sql2o.createQuery("select 2").executeScalar(Short.class);
Short expected = 2;
assertEquals(expected, shortVal);
}
@Test
public void testTimeConverter() {
String sql = "select current_time as col1 from (values(0))";
Time sqlTime = sql2o.createQuery(sql).executeScalar(Time.class);
assertThat(sqlTime, is(notNullValue()));
assertTrue(sqlTime.getTime() > 0);
Date date = sql2o.createQuery(sql).executeScalar(Date.class);
assertThat(date, is(notNullValue()));
LocalTime jodaTime = sql2o.createQuery(sql).executeScalar(LocalTime.class);
assertTrue(jodaTime.getMillisOfDay() > 0);
assertThat(jodaTime.getHourOfDay(), is(equalTo(new LocalTime().getHourOfDay())));
}
@Test
public void testNullDate() {
sql2o
.createQuery("create table nullDateTest(id integer primary key, somedate datetime)")
.executeUpdate();
sql2o
.createQuery("insert into nullDateTest(id, somedate) values(:id, :date)")
.addParameter("id", 1)
.addParameter("date", (Date) null)
.executeUpdate();
Date d =
(Date) sql2o.createQuery("select somedate from nullDateTest where id = 1").executeScalar();
assertNull(d);
}
public Sql2oTest(Driver driverToRegister, String url, String user, String pass, String testName) {
if (driverToRegister != null) {
try {
DriverManager.registerDriver(driverToRegister);
} catch (SQLException e) {
throw new RuntimeException(
"could not register driver '" + driverToRegister.getClass().getName() + "'", e);
}
}
this.sql2o = new Sql2o(url, user, pass);
HashMap<String, String> defaultColumnMap = new HashMap<String, String>();
defaultColumnMap.put("ID", "id");
defaultColumnMap.put("NAME", "name");
defaultColumnMap.put("EMAIL", "email");
defaultColumnMap.put("TEXT", "text");
defaultColumnMap.put("ANUMBER", "aNumber");
defaultColumnMap.put("ALONGNUMBER", "aLongNumber");
sql2o.setDefaultColumnMappings(defaultColumnMap);
this.url = url;
this.user = user;
this.pass = pass;
if ("HyperSQL DB test".equals(testName)) {
sql2o.createQuery("set database sql syntax MSS true").executeUpdate();
}
}
@Test
public void testRunInsideTransactionWithResult() {
sql2o
.createQuery(
"create table testRunInsideTransactionWithResultTable(id integer identity primary key, value varchar(50))")
.executeUpdate();
}
@Test
public void testUpdateWithNulls() {
sql2o
.createQuery(
"create table testUpdateWithNulls_2(id integer identity primary key, value integer)")
.executeUpdate();
Integer nullInt = null;
sql2o
.createQuery("insert into testUpdateWithNulls_2(value) values(:val)")
.addParameter("val", 2)
.addToBatch()
.addParameter("val", nullInt)
.addToBatch()
.executeBatch();
}
@Test
public void testGetResult() {
sql2o
.createQuery("create table get_result_test(id integer primary key, value varchar(20))")
.executeUpdate();
String insertSql =
"insert into get_result_test(id, value) "
+ "select 1, 'hello' from (values(0)) union "
+ "select 2, 'hello2' from (values(0)) union "
+ "select 3, 'hello3' from (values(0))";
int result = sql2o.createQuery(insertSql).executeUpdate().getResult();
assertEquals(3, result);
}
@Test
public void testBooleanConverter() {
String sql = "select true as val1, false as val2 from (values(0))";
BooleanPOJO pojo = sql2o.createQuery(sql).executeAndFetchFirst(BooleanPOJO.class);
assertTrue(pojo.val1);
assertFalse(pojo.val2);
String sql2 = "select null as val1, null as val2 from (values(0))";
BooleanPOJO pojo2 = sql2o.createQuery(sql2).executeAndFetchFirst(BooleanPOJO.class);
assertFalse(pojo2.val1);
assertNull(pojo2.val2);
String sql3 = "select 'false' as val1, 'true' as val2 from (values(0))";
BooleanPOJO pojo3 = sql2o.createQuery(sql3).executeAndFetchFirst(BooleanPOJO.class);
assertFalse(pojo3.val1);
assertTrue(pojo3.val2);
}
@Test
public void testConversion() {
String sql =
"select cast(1 as smallint) as val1, 2 as val2 from (values(0)) union select cast(3 as smallint) as val1, 4 as val2 from (values(0))";
List<TypeConvertEntity> entities =
sql2o.createQuery(sql).executeAndFetch(TypeConvertEntity.class);
assertTrue(entities.size() == 2);
}
@Test
public void testSuperPojo() {
SuperPojo pojo =
sql2o
.createQuery("select 1 id, 'something' value from (values(0))")
.executeAndFetchFirst(SuperPojo.class);
assertEquals(1, pojo.getId());
assertEquals("something1", pojo.getValue());
}
@Test
public void testSetPrivateFields() {
EntityWithPrivateFields entity =
sql2o
.createQuery("select 1 id, 'hello' value from (values(0))")
.executeAndFetchFirst(EntityWithPrivateFields.class);
assertEquals(1, entity.getId());
assertEquals("hello1", entity.getValue());
}
@Test
public void testBindPojo() {
String createSql =
"create table bindtbl(id int identity primary key, data1 varchar(10), data2 timestamp, data3 bigint)";
sql2o.createQuery(createSql).executeUpdate();
BindablePojo pojo1 = new BindablePojo();
pojo1.setData1("Foo");
pojo1.setData2(new Timestamp(new Date().getTime()));
pojo1.setData3(789456123L);
pojo1.setData4(4.5f);
String insertSql = "insert into bindtbl(data1, data2, data3) values(:data1, :data2, :data3)";
sql2o.createQuery(insertSql).bind(pojo1).executeUpdate();
String selectSql = "select data1, data2, data3 from bindtbl";
BindablePojo pojo2 = sql2o.createQuery(selectSql).executeAndFetchFirst(BindablePojo.class);
assertTrue(pojo1.equals(pojo2));
}
@Test
public void testUpdateNoTransaction() throws SQLException {
String ddlQuery = "create table testUpdateNoTransaction(id int primary key, value varchar(50))";
Connection connection = sql2o.createQuery(ddlQuery).executeUpdate();
assertTrue(connection.getJdbcConnection().isClosed());
String insQuery = "insert into testUpdateNoTransaction(id, value) values (:id, :value)";
sql2o
.createQuery(insQuery)
.addParameter("id", 1)
.addParameter("value", "test1")
.executeUpdate()
.createQuery(insQuery)
.addParameter("id", 2)
.addParameter("value", "val2")
.executeUpdate();
assertTrue(connection.getJdbcConnection().isClosed());
}
@Test
public void testExecuteAndFetch() {
createAndFillUserTable();
Date before = new Date();
List<User> allUsers = sql2o.createQuery("select * from User").executeAndFetch(User.class);
Date after = new Date();
long span = after.getTime() - before.getTime();
System.out.println(String.format("Fetched %s user: %s ms", insertIntoUsers, span));
// repeat this
before = new Date();
allUsers = sql2o.createQuery("select * from User").executeAndFetch(User.class);
after = new Date();
span = after.getTime() - before.getTime();
System.out.println(String.format("Again Fetched %s user: %s ms", insertIntoUsers, span));
assertTrue(allUsers.size() == insertIntoUsers);
deleteUserTable();
}
@Test
public void testExecuteAndFetchResultSet() throws SQLException {
List<Integer> list =
sql2o
.createQuery(
"select 1 val from (values(0)) union select 2 from (values(0)) union select 3 from (values(0))")
.executeScalarList();
assertEquals((int) list.get(0), 1);
assertEquals((int) list.get(1), 2);
assertEquals((int) list.get(2), 3);
}
@Test
public void testComplexTypes() {
ComplexEntity pojo =
sql2o
.createQuery(
"select 1 id, 1 \"entity.id\", 'something' \"entity.value\" from (values(0))",
"testComplexTypes")
.executeAndFetchFirst(ComplexEntity.class);
assertEquals(1, pojo.id);
assertEquals(1, pojo.entity.getId());
assertEquals("something1", pojo.entity.getValue());
}
@Test
public void testBigDecimals() {
sql2o
.createQuery(
"create table bigdectesttable (id integer identity primary key, val1 numeric(5,3), val2 integer)")
.executeUpdate();
sql2o
.createQuery("insert into bigdectesttable(val1, val2) values(:val1, :val2)")
.addParameter("val1", 1.256)
.addParameter("val2", 4)
.executeUpdate();
BigDecimalPojo pojo =
sql2o
.createQuery("select * from bigdectesttable")
.executeAndFetchFirst(BigDecimalPojo.class);
assertEquals(new BigDecimal("1.256"), pojo.val1);
assertEquals(new BigDecimal("4.0"), pojo.val2);
}
@Test
public void testStringConversion() {
StringConversionPojo pojo =
sql2o
.createQuery(
"select '1' val1, '2 ' val2, '' val3, '' val4, null val5 from (values(0))")
.executeAndFetchFirst(StringConversionPojo.class);
assertEquals((Integer) 1, pojo.val1);
assertEquals(2l, pojo.val2);
assertNull(pojo.val3);
assertEquals(0, pojo.val4);
assertNull(pojo.val5);
}
@Test
public void testQueryDbMappings() {
Entity entity =
sql2o
.createQuery(
"select 1 as id, 'something' as caption, cast('2011-01-01' as date) as theTime from (values(0))")
.addColumnMapping("caption", "text")
.addColumnMapping("theTime", "time")
.executeAndFetchFirst(Entity.class);
assertEquals(1, entity.id);
assertEquals("something", entity.text);
assertEquals(new DateTime(2011, 1, 1, 0, 0, 0, 0).toDate(), entity.time);
}
@Test
public void testFetchTable() {
sql2o
.createQuery(
"create table tabletest(id integer identity primary key, value varchar(20), value2 decimal(5,1))")
.executeUpdate();
sql2o
.createQuery("insert into tabletest(value,value2) values (:value, :value2)")
.addParameter("value", "something")
.addParameter("value2", new BigDecimal("3.4"))
.addToBatch()
.addParameter("value", "bla")
.addParameter("value2", new BigDecimal("5.5"))
.addToBatch()
.executeBatch();
Table table = sql2o.createQuery("select * from tabletest order by id").executeAndFetchTable();
assertEquals(3, table.columns().size());
assertEquals("ID", table.columns().get(0).getName());
assertEquals("VALUE", table.columns().get(1).getName());
assertEquals("VALUE2", table.columns().get(2).getName());
assertEquals(2, table.rows().size());
Row row0 = table.rows().get(0);
Row row1 = table.rows().get(1);
assertTrue(0 <= row0.getInteger("ID"));
assertEquals("something", row0.getString(1));
assertEquals(new BigDecimal("3.4"), row0.getBigDecimal("VALUE2"));
assertTrue(1 <= row1.getInteger(0));
assertEquals("bla", row1.getString("VALUE"));
assertEquals(5.5D, row1.getDouble(2), 0.00001);
}