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