SQLSubQuery subQuery = new SQLSubQuery(); subQuery = subQuery.from(t).join(t.fk462bdfe3e03a52d4, QClient.client); ListSubQuery clientByPaid = subQuery.list(t.id.as("id"), t.paidId.as("clientid"), QClient.client.name.as("clientname")); subQuery = new SQLSubQuery(); subQuery = subQuery.from(t).where(t.paidId.isNull(), t.clientname.isNotNull()); ListSubQuery clientByName = subQuery.list(t.id, Expressions.constant(-1L), t.clientname);
@Override public SQLSubQuery clone() { SQLSubQuery subQuery = new SQLSubQuery(configuration, getMetadata()); return subQuery; }
@Test @ExcludeIn({Target.DERBY, Target.POSTGRES}) public void Union4() { query().union(cat, new SQLSubQuery().from(cat).where(cat.name.eq("Beck")).distinct().list(cat.name, cat.id), new SQLSubQuery().from(cat).where(cat.name.eq("Kate")).distinct().list(cat.name, null)) .list(cat.name, cat.id); }
/** * 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 @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); } }
SQLSubQuery subQuery = new SQLSubQuery(); return subQuery.from(permission) .where(permission.targetResourceId.eq(targetResourceId).and( actionPredicate.and(authorizedResourceIdPredicate))) .exists();
.where(property.id.eq(new SQLSubQuery() .from(entry) .where(entry.propertyKey.eq(DEFAULT_PROJECT_AVATAR_KEY)) .unique(entry.id))) .set(property.value, defaultProjectAvatarId.toString()) .execute();
@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); }
/** * Build a predicate that will only include users if this directory is their canonical directory. * i.e. ignore users that are shadowed by a higher priority directory. * * This method can return null if a predicate is not necessary. QueryDSL will gracefully ignore the null predicate. * * @param u the user table * @return the predicate, or null if there are no higher priority directories. */ @Nullable private BooleanExpression userIsNotShadowedPredicate(final QUser u) { BooleanExpression userIsNotShadowedPredicate = null; if (!higherPriorityDirectoryIds.isEmpty()) { QUser u2 = new QUser("u2"); userIsNotShadowedPredicate = new SQLSubQuery() .from(u2) .where(u2.lowerUserName.eq(u.lowerUserName) .and(u2.directoryId.in(higherPriorityDirectoryIds))) .notExists(); } return userIsNotShadowedPredicate; } }
QAssetobject t = QAssetobject.assetobject; QAssetData ad = QAssetData.assetData; QAssetobjectParents p = QAssetobjectParents.assetobjectParents; List<Tuple> results = query.from(t) .leftJoin(ad).on(t.asdId.eq(ad.asdId)) .where(new SQLSubQuery().from(p) .where(t.asoId.eq(p.asoId), p.ctdId.eq(1)).exists()) .groupBy(t.asdId) .orderBy(Wildcard.count.desc(), t.asdId.asc()) .list(t.asdId, Wildcard.count);
@Test @SuppressWarnings("unchecked") public void Union() throws SQLException { SubQueryExpression<Integer> sq1 = sq().from(cat).unique(cat.id.max()); SubQueryExpression<Integer> sq2 = sq().from(cat).unique(cat.id.min()); List<Integer> list = query().union(sq1, sq2).list(); assertFalse(list.isEmpty()); }
@Test @ExcludeIn({Target.DERBY, Target.POSTGRES}) public void Union2() { List<Tuple> rows = query().union( new SQLSubQuery().from(cat).where(cat.name.eq("Beck")).distinct().list(cat.name, cat.id), new SQLSubQuery().from(cat).where(cat.name.eq("Kate")).distinct().list(cat.name, null)) .list(); assertEquals(2, rows.size()); for (Tuple row : rows) { System.err.println(row); } }
@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()); }
/** * This version of the query performs pretty well on all databases... except for MySql * @param dbConnection the database connection * @return the user count */ private Long runStandardQuery(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); // Predicate to find users that are a member of one or more of the nested groups, in this directory. // Use exists() to ensure we count each user only once, even if they are a member of multiple groups BooleanExpression userIsMemberOfGroups = new SQLSubQuery() .from(m).where(m.lowerChildName.eq(u.lowerUserName) .and(m.membershipType.eq("GROUP_USER")) .and(m.directoryId.eq(directoryId)) .and(sqlPredicates.partitionedIn(m.lowerParentName, lowerCaseGroupNames))) .exists(); // Query to count active users who are not shadowed, and are a member of at least one of these groups SQLQuery query = dbConnection.newSqlQuery() .from(u).where(u.active.eq(1).and(u.directoryId.eq(directoryId)) .and(userIsNotShadowed) .and(userIsMemberOfGroups)); // SQL Server has a limit of 2000 parameters per query. If we hit that, use literals instead if (dbConfig.isSqlServer() && lowerCaseGroupNames.size() > SQL_SERVER_USE_LITERALS_THRESHOLD) { query.setUseLiterals(true); } return query.singleResult(u.lowerUserName.count()); }
.where(it.avatar.isNull() .and(it.iconurl.eq(oldIconUrl))) .set(it.avatar, new SQLSubQuery() .from(a) .where(a.avatarType.eq(ISSUE_TYPE_AVATAR) .and(a.systemAvatar.eq(TRUE)) .and(a.fileName.eq(newIconFileName))) .unique(a.id)) .execute();
SQLSubQuery outerQuery = new SQLSubQuery() .from(table) .where(Expressions.list(column1, column2, ...).in(inneryQuery.list(projection))) .groupBy(contentcache1.programId, contentcache1.id);
private List<Tuple> getRemovedWorklogs(final Long sinceInMilliseconds, final int maxResults, final QChangeItem ci, final QChangeGroup cg, final QWorklog w, final NumberExpression<Long> oldValueExpression) { return dbConnectionManager.executeQuery(dbConnection -> dbConnection.newSqlQuery() .from(ci) .leftJoin(cg) .on(ci.group.eq(cg.id)) .where(ci.field.eq(IssueFieldConstants.WORKLOG_ID) .and(cg.created.goe(new Timestamp(sinceInMilliseconds)) .and(cg.created.loe(new Timestamp(minuteAgo())))) .and(oldValueExpression.notIn(new SQLSubQuery() .from(w) .list(w.id)) ) ) .groupBy(oldValueExpression) .orderBy(cg.created.max().asc()) .limit(maxResults) .list(oldValueExpression, cg.created.max())); }
query.from(customer).where( customer.status.in(new SQLSubQuery().from(status).where( status.level.lt(3)).list(status.id)) .list(customer.all())
subQuery = new SQLSubQuery(); Path innerUnion = Expressions.path(Void.class, "innernamequery"); subQuery = subQuery.from(subQuery.union(clientByPaid,clientByName).as(innerUnion));