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