@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 findRepostByUsers_Entity_単数_00() {
User usr1 = User.findBySerialCode("usr-goro").first();
List<RepostBase> lst = RepostBase.findRepostByUsers(usr1).fetch();
assertThat(lst.size(), is(4));
// DBからの取得リストの並び保証なし
}
// -------------------------------------+
@Test
public void findRepostByTags_Entity_単数_00() {
Tag tag1 = Tag.findBySerialCode("tag-goro-red").first();
List<RepostBase> lst = RepostBase.findRepostByTags(tag1).fetch();
assertThat(lst.size(), is(3));
// DBからの取得リストの並び保証なし
}
// #list-validate
public List<ValidationError> validate() {
List<ValidationError> errors = new ArrayList<ValidationError>();
if (User.byEmail(email) != null) {
errors.add(new ValidationError("email", "This e-mail is already registered."));
}
return errors.isEmpty() ? null : errors;
}
@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 findRepostByTags_String_単数_投稿者_00() {
Account acnt = Account.findByLoginName("goro_san").first();
List<RepostBase> lst = RepostBase.findRepostByTags("tag-goro-red").contributor(acnt).fetch();
assertThat(lst.size(), is(3));
// DBからの取得リストの並び保証なし
}
@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());
}
// -------------------------------------+
@Test
public void findRepostByCategories_Entity_単数_00() {
Category cat1 = Category.findBySerialCode("cat-biz").first();
List<RepostBase> lst = RepostBase.findRepostByCategories(cat1).fetch();
assertThat(lst.size(), is(2));
// DBからの取得リストの並び保証なし
}
@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 findRepostByAccounts_Entity_単数_00() {
Account acnt1 = Account.findByLoginName("goro_san").first();
List<RepostBase> lst = RepostBase.findRepostByAccounts(acnt1).fetch();
assertThat(lst.size(), is(17));
// DBからの取得リストの並び保証なし
}
// -------------------------------------+
@Test
public void findRepostByTweets_Entity_単数_00() {
Tweet twt1 = Tweet.findBySerialCode("twt-goro2").first();
List<RepostBase> lst = RepostBase.findRepostByTweets(twt1).fetch();
assertThat(lst.size(), is(4));
// DBからの取得リストの並び保証なし
}
// -------------------------------------+
@Test
public void findRepostByUsers_Entity_単数_投稿者_00() {
User usr1 = User.findBySerialCode("usr-goro").first();
Account acnt = Account.findByLoginName("goro_san").first();
List<RepostBase> lst = RepostBase.findRepostByUsers(usr1).contributor(acnt).fetch();
assertThat(lst.size(), is(2));
// DBからの取得リストの並び保証なし
}
// -------------------------------------+
@Test
public void findRepostByCategories_Entity_単数_投稿者_00() {
Category cat1 = Category.findBySerialCode("cat-biz").first();
Account acnt = Account.findByLoginName("goro_san").first();
List<RepostBase> lst = RepostBase.findRepostByCategories(cat1).contributor(acnt).fetch();
assertThat(lst.size(), is(2));
// DBからの取得リストの並び保証なし
}
@Test
public void findRepostByCategories_String_複数_投稿者_00() {
Account acnt = Account.findByLoginName("goro_san").first();
List<RepostBase> lst =
RepostBase.findRepostByCategories("cat-biz", "cat-enta").contributor(acnt).fetch();
assertThat(lst.size(), is(3));
// DBからの取得リストの並び保証なし
}
@Test
public void findRepostByTweets_String_複数_投稿者_00() {
Account acnt = Account.findByLoginName("goro_san").first();
List<RepostBase> lst =
RepostBase.findRepostByTweets("twt-goro2", "twt-jiro1").contributor(acnt).fetch();
assertThat(lst.size(), is(5));
// DBからの取得リストの並び保証なし
}
// -------------------------------------+
@Test
public void findRepostByTweets_Entity_単数_投稿者_00() {
Tweet twt1 = Tweet.findBySerialCode("twt-goro2").first();
Account acnt = Account.findByLoginName("goro_san").first();
List<RepostBase> lst = RepostBase.findRepostByTweets(twt1).contributor(acnt).fetch();
assertThat(lst.size(), is(2));
// DBからの取得リストの並び保証なし
}
@Test
public void findRepostByAccounts_String_複数_降順_00() {
List<RepostBase> lst =
RepostBase.findRepostByAccounts("usr-goro", "usr-jiro")
.orderBy(RepostBase.OrderBy.DATE_OF_REPOST_DESC)
.fetch();
assertThat(lst.size(), is(24));
assertThat(lst.get(0).contributor.loginUser.screenName, is("goro_san"));
}
@Test
public void findRepostByTags_Entity_複数_投稿者_00() {
Tag tag1 = Tag.findBySerialCode("tag-goro-red").first();
Tag tag2 = Tag.findBySerialCode("tag-jiro-hello").first();
Account acnt = Account.findByLoginName("goro_san").first();
List<RepostBase> lst = RepostBase.findRepostByTags(tag1, tag2).contributor(acnt).fetch();
assertThat(lst.size(), is(3));
// DBからの取得リストの並び保証なし
}
@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 findRepostByAccounts_Entity_単数_降順_00() {
Account acnt1 = Account.findByLoginName("goro_san").first();
List<RepostBase> lst =
RepostBase.findRepostByAccounts(acnt1)
.orderBy(RepostBase.OrderBy.DATE_OF_REPOST_DESC)
.fetch();
assertThat(lst.size(), is(17));
assertThat(lst.get(0).contributor.loginUser.screenName, is("goro_san"));
}
@Test
public void findRepostByCategories_String_複数_投稿者_降順_00() {
Account acnt = Account.findByLoginName("goro_san").first();
List<RepostBase> lst =
RepostBase.findRepostByCategories("cat-biz", "cat-enta")
.contributor(acnt)
.orderBy(RepostBase.OrderBy.DATE_OF_REPOST_DESC)
.fetch();
assertThat(lst.size(), is(3));
assertThat(lst.get(0).getLabel().serialCode, is("cat-biz"));
}
@Test
public void findRepostByTweets_String_複数_投稿者_降順_00() {
Account acnt = Account.findByLoginName("goro_san").first();
List<RepostBase> lst =
RepostBase.findRepostByTweets("twt-goro2", "twt-jiro1")
.contributor(acnt)
.orderBy(RepostBase.OrderBy.DATE_OF_REPOST_DESC)
.fetch();
assertThat(lst.size(), is(5));
assertThat(lst.get(0).getItem().serialCode, is("twt-jiro1"));
}
@Test
public void findRepostByTags_String_複数_投稿者_降順_00() {
Account acnt = Account.findByLoginName("goro_san").first();
List<RepostBase> lst =
RepostBase.findRepostByTags("tag-goro-red", "tag-jiro-hello")
.contributor(acnt)
.orderBy(RepostBase.OrderBy.DATE_OF_REPOST_DESC)
.fetch();
assertThat(lst.size(), is(3));
assertThat(lst.get(0).getLabel().serialCode, is("tag-goro-red"));
}
@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 findRepostByUsers_Entity_単数_投稿者_降順_00() {
User usr1 = User.findBySerialCode("usr-goro").first();
Account acnt = Account.findByLoginName("goro_san").first();
List<RepostBase> lst =
RepostBase.findRepostByUsers(usr1)
.contributor(acnt)
.orderBy(RepostBase.OrderBy.DATE_OF_REPOST_DESC)
.fetch();
assertThat(lst.size(), is(2));
assertThat(lst.get(0).getItem().serialCode, is("usr-goro"));
}
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;
}
@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 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 findRepostByUsers_String_単数_00() {
List<RepostBase> lst = RepostBase.findRepostByUsers("usr-goro").fetch();
assertThat(lst.size(), is(4));
// DBからの取得リストの並び保証なし
}
// =============================================*
// 問題なし
@Test
public void findRepostByUsers_例外_00() {
List<RepostBase> lst = RepostBase.findRepostByUsers((Object) null).fetch();
assertThat(lst.size(), is(10));
}