@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 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);
}
/** ************ 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;
}