@Test
public void testLeftJoin() throws Exception {
Dao<Bar, Integer> barDao = createDao(Bar.class, true);
Dao<Baz, Integer> bazDao = createDao(Baz.class, true);
Bar bar1 = new Bar();
bar1.val = 2234;
assertEquals(1, barDao.create(bar1));
Bar bar2 = new Bar();
bar2.val = 324322234;
assertEquals(1, barDao.create(bar2));
Baz baz1 = new Baz();
baz1.bar = bar1;
assertEquals(1, bazDao.create(baz1));
Baz baz2 = new Baz();
baz2.bar = bar2;
assertEquals(1, bazDao.create(baz2));
Baz baz3 = new Baz();
// no bar
assertEquals(1, bazDao.create(baz3));
QueryBuilder<Bar, Integer> barQb = barDao.queryBuilder();
List<Baz> results = bazDao.queryBuilder().query();
assertEquals(3, results.size());
results = bazDao.queryBuilder().join(barQb).query();
assertEquals(2, results.size());
results = bazDao.queryBuilder().leftJoin(barQb).query();
assertEquals(3, results.size());
results = bazDao.queryBuilder().join(barQb, JoinType.LEFT, JoinWhereOperation.AND).query();
assertEquals(3, results.size());
}
@Test
public void testJoinGroup() throws Exception {
Dao<Bar, Integer> barDao = createDao(Bar.class, true);
Dao<Baz, Integer> bazDao = createDao(Baz.class, true);
Bar bar1 = new Bar();
bar1.val = 2234;
assertEquals(1, barDao.create(bar1));
Bar bar2 = new Bar();
bar2.val = 324322234;
assertEquals(1, barDao.create(bar2));
Baz baz1 = new Baz();
baz1.bar = bar1;
assertEquals(1, bazDao.create(baz1));
Baz baz2 = new Baz();
baz2.bar = bar2;
assertEquals(1, bazDao.create(baz2));
QueryBuilder<Bar, Integer> barQb = barDao.queryBuilder();
barQb.where().eq(Bar.VAL_FIELD, bar1.val);
barQb.groupBy(Bar.ID_FIELD);
List<Baz> results = bazDao.queryBuilder().query();
assertEquals(2, results.size());
results = bazDao.queryBuilder().groupBy(Baz.ID_FIELD).join(barQb).query();
assertEquals(1, results.size());
assertEquals(bar1.id, results.get(0).bar.id);
}
@Test
public void testJoinOrder() throws Exception {
Dao<Bar, Integer> barDao = createDao(Bar.class, true);
Dao<Baz, Integer> bazDao = createDao(Baz.class, true);
Bar bar1 = new Bar();
bar1.val = 2234;
assertEquals(1, barDao.create(bar1));
Bar bar2 = new Bar();
bar2.val = 324322234;
assertEquals(1, barDao.create(bar2));
Baz baz1 = new Baz();
baz1.bar = bar1;
assertEquals(1, bazDao.create(baz1));
Baz baz2 = new Baz();
baz2.bar = bar2;
assertEquals(1, bazDao.create(baz2));
QueryBuilder<Bar, Integer> barQb = barDao.queryBuilder();
barQb.orderBy(Bar.VAL_FIELD, true);
List<Baz> results = bazDao.queryBuilder().join(barQb).query();
assertEquals(2, results.size());
assertEquals(bar1.id, results.get(0).bar.id);
assertEquals(bar2.id, results.get(1).bar.id);
// reset the query to change the order direction
barQb.reset();
barQb.orderBy(Bar.VAL_FIELD, false);
results = bazDao.queryBuilder().join(barQb).query();
assertEquals(2, results.size());
assertEquals(bar2.id, results.get(0).bar.id);
assertEquals(bar1.id, results.get(1).bar.id);
}
@Test
public void testBaseClassComparison() throws Exception {
Dao<Bar, String> barDao = createDao(Bar.class, true);
Dao<Baz, String> bazDao = createDao(Baz.class, true);
BarSuperClass bar1 = new BarSuperClass();
bar1.val = 10;
assertEquals(1, barDao.create(bar1));
Baz baz1 = new Baz();
baz1.bar = bar1;
assertEquals(1, bazDao.create(baz1));
List<Baz> results = bazDao.queryBuilder().where().eq(Baz.BAR_FIELD, bar1).query();
assertEquals(1, results.size());
assertEquals(bar1.id, results.get(0).bar.id);
try {
// we allow a super class of the field but _not_ a sub class
results = bazDao.queryBuilder().where().eq(Baz.BAR_FIELD, new Object()).query();
fail("Should have thrown");
} catch (SQLException e) {
// expected
}
}
@Test
public void testSimpleJoinOr() throws Exception {
Dao<Bar, Integer> barDao = createDao(Bar.class, true);
Dao<Baz, Integer> bazDao = createDao(Baz.class, true);
Bar bar1 = new Bar();
bar1.val = 2234;
assertEquals(1, barDao.create(bar1));
Bar bar2 = new Bar();
bar2.val = 324322234;
assertEquals(1, barDao.create(bar2));
Baz baz1 = new Baz();
baz1.bar = bar1;
baz1.val = 423423;
assertEquals(1, bazDao.create(baz1));
Baz baz2 = new Baz();
baz2.bar = bar2;
baz2.val = 9570423;
assertEquals(1, bazDao.create(baz2));
QueryBuilder<Bar, Integer> barQb = barDao.queryBuilder();
barQb.where().eq(Bar.VAL_FIELD, bar1.val);
List<Baz> results = bazDao.queryBuilder().query();
assertEquals(2, results.size());
QueryBuilder<Baz, Integer> bazQb = bazDao.queryBuilder();
bazQb.where().eq(Baz.VAL_FIELD, baz2.val);
results = bazQb.joinOr(barQb).query();
assertEquals(2, results.size());
assertEquals(bar1.id, results.get(0).bar.id);
assertEquals(bar2.id, results.get(1).bar.id);
bazQb.reset();
bazQb.where().eq(Baz.VAL_FIELD, baz2.val);
results = bazQb.join(barQb, JoinType.INNER, JoinWhereOperation.OR).query();
assertEquals(2, results.size());
assertEquals(bar1.id, results.get(0).bar.id);
assertEquals(bar2.id, results.get(1).bar.id);
// now do join which should be an AND
bazQb.reset();
bazQb.where().eq(Baz.VAL_FIELD, baz2.val);
results = bazQb.join(barQb).query();
// should find no results
assertEquals(0, results.size());
}
@Test
public void testMultipleJoin() throws Exception {
Dao<Bar, Integer> barDao = createDao(Bar.class, true);
Dao<Baz, Integer> bazDao = createDao(Baz.class, true);
Dao<Bing, Integer> bingDao = createDao(Bing.class, true);
Bar bar1 = new Bar();
bar1.val = 2234;
assertEquals(1, barDao.create(bar1));
Bar bar2 = new Bar();
bar2.val = 324322234;
assertEquals(1, barDao.create(bar2));
Baz baz1 = new Baz();
baz1.bar = bar1;
assertEquals(1, bazDao.create(baz1));
Baz baz2 = new Baz();
baz2.bar = bar2;
assertEquals(1, bazDao.create(baz2));
Bing bing1 = new Bing();
bing1.baz = baz1;
assertEquals(1, bingDao.create(bing1));
Bing bing2 = new Bing();
bing2.baz = baz1;
assertEquals(1, bingDao.create(bing2));
QueryBuilder<Bar, Integer> barQb = barDao.queryBuilder();
barQb.where().eq(Bar.VAL_FIELD, bar1.val);
List<Baz> results = bazDao.queryBuilder().query();
assertEquals(2, results.size());
QueryBuilder<Bing, Integer> bingQb = bingDao.queryBuilder();
bingQb.where().eq(Bing.ID_FIELD, bing2.id);
List<Baz> bingResults = bazDao.queryBuilder().query();
assertEquals(2, bingResults.size());
results = bazDao.queryBuilder().join(barQb).join(bingQb).query();
assertEquals(1, results.size());
assertEquals(bar1.id, results.get(0).bar.id);
}
@Test
public void testSimpleJoinColumnArg() throws Exception {
Dao<Bar, Integer> barDao = createDao(Bar.class, true);
Dao<Baz, Integer> bazDao = createDao(Baz.class, true);
Bar bar1 = new Bar();
int val = 1313123;
bar1.val = val;
assertEquals(1, barDao.create(bar1));
Bar bar2 = new Bar();
bar2.val = val;
assertEquals(1, barDao.create(bar2));
Baz baz1 = new Baz();
baz1.bar = bar1;
baz1.val = val;
assertEquals(1, bazDao.create(baz1));
Baz baz2 = new Baz();
baz2.bar = bar2;
baz2.val = val + 1;
assertEquals(1, bazDao.create(baz2));
Baz baz3 = new Baz();
baz1.bar = bar2;
baz1.val = val;
assertEquals(1, bazDao.create(baz3));
Baz baz4 = new Baz();
baz2.bar = bar1;
baz2.val = val;
assertEquals(1, bazDao.create(baz4));
QueryBuilder<Bar, Integer> barQb = barDao.queryBuilder();
barQb.where().eq(Bar.VAL_FIELD, new ColumnArg("baz", Baz.VAL_FIELD));
List<Baz> results = bazDao.queryBuilder().query();
assertEquals(4, results.size());
results = bazDao.queryBuilder().join(barQb).query();
assertEquals(1, results.size());
assertEquals(bar1.id, results.get(0).bar.id);
}
