@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 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 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();
}
public Object run(Connection connection, Object argument) throws Throwable {
String[] vals = (String[]) argument;
List<Integer> keys = new ArrayList<Integer>();
for (String val : vals) {
Integer key =
connection
.createQuery(
"insert into testRunInsideTransactionWithResultTable(value) values(:val)",
"runnerWithResultTester")
.addParameter("val", val)
.executeUpdate()
.getKey(Integer.class);
keys.add(key);
}
return keys;
}