@Test
public void testDoubleDbOpen() throws Exception {
clearDatabases();
ConnectionSource cs =
new JdbcConnectionSource(
"jdbc:h2:file:" + DATABASE_DIR + "/" + DATABASE_NAME_PREFIX + ".1");
TableUtils.createTable(cs, Foo.class);
Dao<Foo, Integer> dao = Instances.getDaoManager().createDao(cs, Foo.class);
Foo foo1 = new Foo();
foo1.val = 12312;
assertEquals(1, dao.create(foo1));
Foo result = dao.queryForId(foo1.id);
assertNotNull(result);
assertEquals(foo1.val, result.val);
// ==================================
cs =
new JdbcConnectionSource(
"jdbc:h2:file:" + DATABASE_DIR + "/" + DATABASE_NAME_PREFIX + ".2");
Instances.getDaoManager().clearCache();
TableUtils.createTable(cs, Foo.class);
dao = Instances.getDaoManager().createDao(cs, Foo.class);
Foo foo2 = new Foo();
foo2.val = 12314;
assertEquals(1, dao.create(foo2));
result = dao.queryForId(foo2.id);
assertNotNull(result);
assertEquals(foo2.val, result.val);
}
@Test
public void testQueryRawColumns() throws Exception {
Dao<Foo, Integer> dao = createDao(Foo.class, true);
Foo foo1 = new Foo();
foo1.val = 1;
foo1.equal = 10;
assertEquals(1, dao.create(foo1));
Foo foo2 = new Foo();
foo2.val = 10;
foo2.equal = 5;
assertEquals(1, dao.create(foo2));
QueryBuilder<Foo, Integer> qb = dao.queryBuilder();
qb.selectRaw("COUNT(*)");
GenericRawResults<String[]> rawResults = dao.queryRaw(qb.prepareStatementString());
List<String[]> results = rawResults.getResults();
assertEquals(1, results.size());
// 2 rows inserted
assertEquals("2", results.get(0)[0]);
qb = dao.queryBuilder();
qb.selectRaw("MIN(val)", "MAX(val)");
rawResults = dao.queryRaw(qb.prepareStatementString());
results = rawResults.getResults();
assertEquals(1, results.size());
String[] result = results.get(0);
assertEquals(2, result.length);
// foo1 has the maximum value
assertEquals(Integer.toString(foo1.val), result[0]);
// foo2 has the maximum value
assertEquals(Integer.toString(foo2.val), result[1]);
}
@Test
public void testHaving() throws Exception {
Dao<Foo, Integer> dao = createDao(Foo.class, true);
Foo foo = new Foo();
int val1 = 243342;
foo.val = val1;
assertEquals(1, dao.create(foo));
foo = new Foo();
foo.val = val1;
assertEquals(1, dao.create(foo));
foo = new Foo();
// only one of these
int val2 = 6543;
foo.val = val2;
assertEquals(1, dao.create(foo));
QueryBuilder<Foo, Integer> qb = dao.queryBuilder();
qb.selectColumns(Foo.VAL_COLUMN_NAME);
qb.groupBy(Foo.VAL_COLUMN_NAME);
qb.having("COUNT(VAL) > 1");
GenericRawResults<String[]> results = dao.queryRaw(qb.prepareStatementString());
List<String[]> list = results.getResults();
// only val2 has 2 of them
assertEquals(1, list.size());
assertEquals(String.valueOf(val1), list.get(0)[0]);
qb.having("COUNT(VAL) > 2");
results = dao.queryRaw(qb.prepareStatementString());
list = results.getResults();
assertEquals(0, list.size());
}
@Test
public void testCountOf() throws Exception {
Dao<Foo, Integer> dao = createDao(Foo.class, true);
QueryBuilder<Foo, Integer> qb = dao.queryBuilder();
assertEquals(0, qb.countOf());
Foo foo1 = new Foo();
int val = 123213;
foo1.val = val;
assertEquals(1, dao.create(foo1));
assertEquals(1, qb.countOf());
Foo foo2 = new Foo();
foo2.val = val;
assertEquals(1, dao.create(foo2));
assertEquals(2, qb.countOf());
String distinct = "DISTINCT(" + Foo.VAL_COLUMN_NAME + ")";
assertEquals(1, qb.countOf(distinct));
qb.setCountOf(distinct);
assertEquals(1, dao.countOf(qb.prepare()));
distinct = "DISTINCT(" + Foo.ID_COLUMN_NAME + ")";
assertEquals(2, qb.countOf(distinct));
qb.setCountOf(distinct);
assertEquals(2, dao.countOf(qb.prepare()));
}
@Test
public void testOrderByRawArg() throws Exception {
Dao<Foo, Integer> dao = createDao(Foo.class, true);
Foo foo1 = new Foo();
foo1.val = 1;
assertEquals(1, dao.create(foo1));
Foo foo2 = new Foo();
foo2.val = 2;
assertEquals(1, dao.create(foo2));
List<Foo> results =
dao.queryBuilder()
.orderByRaw(
"(" + Foo.VAL_COLUMN_NAME + " = ? ) DESC", new SelectArg(SqlType.INTEGER, 2))
.query();
assertEquals(2, results.size());
assertEquals(foo2.id, results.get(0).id);
assertEquals(foo1.id, results.get(1).id);
results =
dao.queryBuilder()
.orderByRaw("(" + Foo.VAL_COLUMN_NAME + " = ? )", new SelectArg(SqlType.INTEGER, 2))
.query();
assertEquals(2, results.size());
assertEquals(foo1.id, results.get(0).id);
assertEquals(foo2.id, results.get(1).id);
}
@Test
public void testMaxJoin() throws Exception {
Dao<Foo, Object> dao = createDao(Foo.class, true);
Foo foo1 = new Foo();
foo1.val = 10;
assertEquals(1, dao.create(foo1));
Foo foo2 = new Foo();
foo2.val = 20;
assertEquals(1, dao.create(foo2));
Foo foo3 = new Foo();
foo3.val = 30;
assertEquals(1, dao.create(foo3));
Foo foo4 = new Foo();
foo4.val = 40;
assertEquals(1, dao.create(foo4));
QueryBuilder<Foo, Object> iqb = dao.queryBuilder();
iqb.selectRaw("max(id)");
QueryBuilder<Foo, Object> oqb = dao.queryBuilder();
Foo result = oqb.where().in(Foo.ID_COLUMN_NAME, iqb).queryForFirst();
assertNotNull(result);
assertEquals(foo4.id, result.id);
}
@Test
public void testHavingOrderBy() throws Exception {
Dao<Foo, Object> fooDao = createDao(Foo.class, true);
Foo foo1 = new Foo();
foo1.val = 10;
assertEquals(1, fooDao.create(foo1));
Foo foo2 = new Foo();
foo2.val = 20;
assertEquals(1, fooDao.create(foo2));
Foo foo3 = new Foo();
foo3.val = 30;
assertEquals(1, fooDao.create(foo3));
Foo foo4 = new Foo();
foo4.val = 40;
assertEquals(1, fooDao.create(foo4));
QueryBuilder<Foo, Object> qb = fooDao.queryBuilder();
qb.groupBy(Foo.ID_COLUMN_NAME);
qb.orderBy(Foo.VAL_COLUMN_NAME, false);
qb.having("val < " + foo3.val);
List<Foo> results = qb.query();
assertEquals(2, results.size());
assertEquals(foo2.val, results.get(0).val);
assertEquals(foo1.val, results.get(1).val);
}
@Test
public void testQueryForForeign() throws Exception {
Dao<Foo, Integer> fooDao = createDao(Foo.class, true);
Dao<Foreign, Object> foreignDao = createDao(Foreign.class, true);
Foo foo = new Foo();
foo.val = 1231;
assertEquals(1, fooDao.create(foo));
Foreign foreign = new Foreign();
foreign.foo = foo;
assertEquals(1, foreignDao.create(foreign));
// use the auto-extract method to extract by id
List<Foreign> results =
foreignDao.queryBuilder().where().eq(Foreign.FOO_COLUMN_NAME, foo).query();
assertEquals(1, results.size());
assertEquals(foreign.id, results.get(0).id);
// query for the id directly
List<Foreign> results2 =
foreignDao.queryBuilder().where().eq(Foreign.FOO_COLUMN_NAME, foo.id).query();
assertEquals(1, results2.size());
assertEquals(foreign.id, results2.get(0).id);
}
@Test
public void testOrderByRawAndOrderBy() throws Exception {
Dao<Foo, Integer> dao = createDao(Foo.class, true);
Foo foo1 = new Foo();
foo1.val = 1;
foo1.equal = 10;
assertEquals(1, dao.create(foo1));
Foo foo2 = new Foo();
foo2.val = 5;
foo2.equal = 7;
assertEquals(1, dao.create(foo2));
Foo foo3 = new Foo();
foo3.val = 7;
foo3.equal = 5;
assertEquals(1, dao.create(foo3));
List<Foo> results =
dao.queryBuilder()
.orderByRaw("(" + Foo.VAL_COLUMN_NAME + "+" + Foo.EQUAL_COLUMN_NAME + ") DESC")
.query();
assertEquals(3, results.size());
assertEquals(foo2.id, results.get(0).id);
assertEquals(foo3.id, results.get(1).id);
assertEquals(foo1.id, results.get(2).id);
results =
dao.queryBuilder()
.orderByRaw("(" + Foo.VAL_COLUMN_NAME + "+" + Foo.EQUAL_COLUMN_NAME + ") DESC")
.orderBy(Foo.VAL_COLUMN_NAME, false)
.query();
assertEquals(3, results.size());
assertEquals(foo3.id, results.get(0).id);
assertEquals(foo2.id, results.get(1).id);
assertEquals(foo1.id, results.get(2).id);
results =
dao.queryBuilder()
.orderBy(Foo.VAL_COLUMN_NAME, true)
.orderByRaw("(" + Foo.VAL_COLUMN_NAME + "+" + Foo.EQUAL_COLUMN_NAME + ") DESC")
.query();
assertEquals(3, results.size());
assertEquals(foo1.id, results.get(0).id);
assertEquals(foo2.id, results.get(1).id);
assertEquals(foo3.id, results.get(2).id);
}
@Test
public void testColumnArg() throws Exception {
Dao<Foo, Integer> fooDao = createDao(Foo.class, true);
Foo foo1 = new Foo();
int val = 3123123;
foo1.val = val;
foo1.equal = val;
assertEquals(1, fooDao.create(foo1));
Foo foo2 = new Foo();
foo2.val = val;
foo2.equal = val + 1;
assertEquals(1, fooDao.create(foo2));
QueryBuilder<Foo, Integer> qb = fooDao.queryBuilder();
qb.where().eq(Foo.VAL_COLUMN_NAME, new ColumnArg(Foo.EQUAL_COLUMN_NAME));
List<Foo> results = qb.query();
assertNotNull(results);
assertEquals(1, results.size());
assertEquals(foo1.id, results.get(0).id);
}
@Test
public void testGetFirstResult() throws Exception {
Dao<Foo, Integer> dao = createDao(Foo.class, true);
Foo foo1 = new Foo();
foo1.val = 342;
assertEquals(1, dao.create(foo1));
Foo foo2 = new Foo();
foo2.val = 9045342;
assertEquals(1, dao.create(foo2));
QueryBuilder<Foo, Integer> qb = dao.queryBuilder();
qb.selectRaw("MAX(" + Foo.VAL_COLUMN_NAME + ")");
GenericRawResults<String[]> results = dao.queryRaw(qb.prepareStatementString());
String[] result = results.getFirstResult();
int max = Integer.parseInt(result[0]);
if (foo1.val > foo2.val) {
assertEquals(foo1.val, max);
} else {
assertEquals(foo2.val, max);
}
}
@Test
public void testQueryRawMax() throws Exception {
Dao<Foo, Object> dao = createDao(Foo.class, true);
Foo foo1 = new Foo();
foo1.stringField = "1";
foo1.val = 10;
assertEquals(1, dao.create(foo1));
Foo foo2 = new Foo();
foo2.stringField = "1";
foo2.val = 20;
assertEquals(1, dao.create(foo2));
Foo foo3 = new Foo();
foo3.stringField = "2";
foo3.val = 30;
assertEquals(1, dao.create(foo3));
Foo foo4 = new Foo();
foo4.stringField = "2";
foo4.val = 40;
assertEquals(1, dao.create(foo4));
QueryBuilder<Foo, Object> qb = dao.queryBuilder();
qb.selectRaw("string, max(val) as val");
qb.groupBy(Foo.STRING_COLUMN_NAME);
GenericRawResults<Foo> results =
dao.queryRaw(qb.prepareStatementString(), dao.getRawRowMapper());
assertNotNull(results);
CloseableIterator<Foo> iterator = results.closeableIterator();
try {
assertTrue(iterator.hasNext());
assertEquals(foo2.val, iterator.next().val);
assertTrue(iterator.hasNext());
assertEquals(foo4.val, iterator.next().val);
assertFalse(iterator.hasNext());
} finally {
iterator.close();
}
}
@Test
public void testInnerCountOf() throws Exception {
Dao<Foo, String> fooDao = createDao(Foo.class, true);
Dao<Bar, String> barDao = createDao(Bar.class, true);
Bar bar1 = new Bar();
int val = 12;
bar1.val = val;
assertEquals(1, barDao.create(bar1));
Bar bar2 = new Bar();
bar2.val = val + 1;
assertEquals(1, barDao.create(bar2));
Foo foo1 = new Foo();
foo1.val = bar1.id;
assertEquals(1, fooDao.create(foo1));
Foo foo2 = new Foo();
foo2.val = bar1.id;
assertEquals(1, fooDao.create(foo2));
Foo foo3 = new Foo();
foo3.val = bar1.id + 1;
assertEquals(1, fooDao.create(foo3));
QueryBuilder<Bar, String> barQb = barDao.queryBuilder();
barQb.selectColumns(Bar.ID_FIELD);
barQb.where().eq(Bar.VAL_FIELD, val);
QueryBuilder<Foo, String> fooQb = fooDao.queryBuilder();
List<Integer> idList = new ArrayList<Integer>();
idList.add(foo1.id);
idList.add(foo2.id);
idList.add(foo3.id);
fooQb.where().in(Foo.ID_COLUMN_NAME, idList).and().in(Foo.VAL_COLUMN_NAME, barQb);
fooQb.setCountOf(true);
assertEquals(2, fooDao.countOf(fooQb.prepare()));
}
@Test
public void testJoinTwoColumns() throws Exception {
Dao<Foo, Integer> fooDao = createDao(Foo.class, true);
Dao<StringColumnArg, Integer> scaDao = createDao(StringColumnArg.class, true);
Foo foo1 = new Foo();
foo1.val = 123213213;
foo1.stringField = "stuff";
fooDao.create(foo1);
Foo foo2 = new Foo();
foo2.stringField = "not stuff";
fooDao.create(foo2);
StringColumnArg sca1 = new StringColumnArg();
sca1.str1 = foo1.stringField;
scaDao.create(sca1);
StringColumnArg sca2 = new StringColumnArg();
sca2.str1 = foo2.stringField;
scaDao.create(sca2);
StringColumnArg sca3 = new StringColumnArg();
sca3.str1 = "some other field";
scaDao.create(sca3);
QueryBuilder<Foo, Integer> fooQb = fooDao.queryBuilder();
fooQb.where().eq(Foo.VAL_COLUMN_NAME, foo1.val);
QueryBuilder<StringColumnArg, Integer> scaQb = scaDao.queryBuilder();
scaQb.join(StringColumnArg.STR1_FIELD, Foo.STRING_COLUMN_NAME, fooQb);
List<StringColumnArg> results = scaQb.query();
assertNotNull(results);
assertEquals(1, results.size());
assertEquals(sca1.id, results.get(0).id);
fooQb.reset();
fooQb.where().eq(Foo.VAL_COLUMN_NAME, foo2.val);
scaQb.reset();
scaQb.join(StringColumnArg.STR1_FIELD, Foo.STRING_COLUMN_NAME, fooQb);
results = scaQb.query();
assertNotNull(results);
assertEquals(1, results.size());
assertEquals(sca2.id, results.get(0).id);
}
@Test
public void testLeftJoinTwoColumns() throws Exception {
Dao<Foo, Integer> fooDao = createDao(Foo.class, true);
Dao<StringColumnArg, Integer> scaDao = createDao(StringColumnArg.class, true);
Foo foo1 = new Foo();
foo1.val = 123213213;
foo1.stringField = "stuff";
fooDao.create(foo1);
StringColumnArg sca1 = new StringColumnArg();
sca1.str1 = foo1.stringField;
scaDao.create(sca1);
StringColumnArg sca2 = new StringColumnArg();
sca2.str1 = "something eles";
scaDao.create(sca2);
QueryBuilder<Foo, Integer> fooQb = fooDao.queryBuilder();
QueryBuilder<StringColumnArg, Integer> scaQb = scaDao.queryBuilder();
scaQb.join(StringColumnArg.STR1_FIELD, Foo.STRING_COLUMN_NAME, fooQb);
List<StringColumnArg> results = scaQb.query();
assertNotNull(results);
assertEquals(1, results.size());
assertEquals(sca1.id, results.get(0).id);
scaQb.reset();
scaQb.join(
StringColumnArg.STR1_FIELD,
Foo.STRING_COLUMN_NAME,
fooQb,
JoinType.LEFT,
JoinWhereOperation.AND);
results = scaQb.query();
assertNotNull(results);
assertEquals(2, results.size());
assertEquals(sca1.id, results.get(0).id);
assertEquals(sca2.id, results.get(1).id);
}
@Test
public void testQueryRaw() throws Exception {
Dao<Foo, Integer> dao = createDao(Foo.class, true);
Foo foo = new Foo();
foo.val = 1;
foo.equal = 10;
assertEquals(1, dao.create(foo));
QueryBuilder<Foo, Integer> qb = dao.queryBuilder();
qb.where().eq(Foo.VAL_COLUMN_NAME, new SelectArg());
GenericRawResults<String[]> rawResults =
dao.queryRaw(qb.prepareStatementString(), Integer.toString(foo.val));
List<String[]> results = rawResults.getResults();
assertEquals(1, results.size());
boolean found = false;
String[] columnNames = rawResults.getColumnNames();
for (int i = 0; i < rawResults.getNumberColumns(); i++) {
if (columnNames[i].equalsIgnoreCase(Foo.ID_COLUMN_NAME)) {
assertEquals(Integer.toString(foo.id), results.get(0)[0]);
found = true;
}
}
assertTrue(found);
}
