@Test @ExcludeIn({Target.DERBY, Target.ORACLE}) public void Union5() { SAnimal cat2 = new SAnimal("cat2"); List<Tuple> rows = query().union( new SQLSubQuery().from(cat).join(cat2).on(cat2.id.eq(cat.id.add(1))).list(cat.id, cat2.id), new SQLSubQuery().from(cat).join(cat2).on(cat2.id.eq(cat.id.add(1))).list(cat.id, cat2.id)) .list(); assertEquals(5, rows.size()); for (Tuple row : rows) { int first = row.get(cat.id).intValue(); int second = row.get(cat2.id).intValue(); assertEquals(first + 1, second); } }
@Test @ExcludeIn(Target.DERBY) public void Union3() { SAnimal cat2 = new SAnimal("cat2"); List<Tuple> rows = query().union( new SQLSubQuery().from(cat).innerJoin(cat2).on(cat2.id.eq(cat.id)).list(cat.id, cat2.id), new SQLSubQuery().from(cat).list(cat.id, null)) .list(); assertEquals(12, rows.size()); int nulls = 0; for (Tuple row : rows) { System.err.println(Arrays.asList(row)); if (row.get(1, Object.class) == null) nulls++; } assertEquals(6, nulls); }
/** * This version of the query runs better on MySql (but worse on other databases, particularly MS SQL Server) * @param dbConnection the database connection * @return the user count */ private Long runMySqlQuery(final DbConnection dbConnection) { QMembership m = new QMembership("m"); QUser u = new QUser("u"); // Predicate to only include users in their canonical directory (i.e. they are not shadowed) BooleanExpression userIsNotShadowed = userIsNotShadowedPredicate(u); // Query to find only active users that are not shadowed, and that are a member of one or more of the nested // groups in this directory. // Use groupBy() to ensure we count each user only once, even if they are a member of multiple groups final SQLSubQuery uniqueUsersSubQuery = new SQLSubQuery() .from(m).innerJoin(u).on(u.id.eq(m.childId)) .where(u.active.eq(1) .and(m.membershipType.eq("GROUP_USER")) .and(m.directoryId.eq(directoryId)) .and(userIsNotShadowed) .and(m.lowerParentName.in(lowerCaseGroupNames))) .groupBy(m.lowerChildName); final DslExpression<List<String>> uniqueUsers = uniqueUsersSubQuery.list(m.lowerChildName).as("uniqueUsers"); // An outer count(*) query SQLQuery countQuery = dbConnection.newSqlQuery(); countQuery.from(uniqueUsers); return countQuery.singleResult(Wildcard.count); }
@Test public void FunctionCall() { //select tab.col from Table tab join TableValuedFunction('parameter') func on tab.col not like func.col QSurvey table = new QSurvey("SURVEY"); RelationalFunctionCall<String> func = RelationalFunctionCall.create(String.class, "TableValuedFunction", "parameter"); PathBuilder<String> funcAlias = new PathBuilder<String>(String.class, "tokFunc"); SQLSubQuery sq = new SQLSubQuery(); SubQueryExpression<?> expr = sq.from(table) .join(func, funcAlias).on(table.name.like(funcAlias.getString("prop")).not()).list(table.name); Configuration conf = new Configuration(new SQLServerTemplates()); SQLSerializer serializer = new NativeSQLSerializer(conf, true); serializer.serialize(expr.getMetadata(), false); assertEquals("select SURVEY.NAME\n" + "from SURVEY SURVEY\n" + "join TableValuedFunction(?1) as tokFunc\n" + "on not (SURVEY.NAME like tokFunc.prop escape '\\')", serializer.toString()); }