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