@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 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 testBatch() {
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)";
Connection con = sql2o.beginTransaction();
int[] inserted =
con.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()
.getBatchResult();
con.commit();
assertEquals(3, inserted.length);
for (int i : inserted) {
assertEquals(1, i);
}
deleteUserTable();
}