@Override public Optional<Long> getResourceId(final String principal) { return querydslSupport.execute((connection, configuration) -> { QSimpleSubject qSimpleSubject = QSimpleSubject.simpleSubject; Long resourceId = new SQLQuery(connection, configuration) .from(qSimpleSubject) .where(qSimpleSubject.principal.eq(principal)) .singleResult(qSimpleSubject.resourceId); return Optional.ofNullable(resourceId); }); }
@Override public String readEncryptedCredential(final String principal) { return querydslSupport.execute((connection, configuration) -> { QSimpleSubject qSimpleSubject = QSimpleSubject.simpleSubject; return new SQLQuery(connection, configuration) .from(qSimpleSubject) .where(qSimpleSubject.principal.eq(principal)) .singleResult(qSimpleSubject.encryptedCredential); }); }
@Nullable private Long selectDefaultProjectIcon(final DbConnection connection) { final QAvatar a = QAvatar.AVATAR; return connection.newSqlQuery() .from(a) .where(a.avatarType.eq(PROJECT_AVATAR) .and(a.systemAvatar.eq(TRUE)) .and(a.fileName.eq(DEFAULT_PROJECT_AVATAR_NAME))) .singleResult(a.id); }
@Override public SimpleSubject readSimpleSubjectByPrincipal(final String principal) { return querydslSupport.execute((connection, configuration) -> { QSimpleSubject qSimpleSubject = QSimpleSubject.simpleSubject; return new SQLQuery(connection, configuration) .from(qSimpleSubject) .where(qSimpleSubject.principal.eq(principal)) .singleResult(ConstructorExpression.create(SimpleSubject.class, qSimpleSubject.simpleSubjectId, qSimpleSubject.principal, qSimpleSubject.resourceId)); }); }
private Long prepareFallBackIconForIssueType(final DbConnection callback) { final QAvatar a = QAvatar.AVATAR; final Optional<Long> undefinedIconId = ofNullable(callback.newSqlQuery() .from(a) .where((a.avatarType.eq(ISSUE_TYPE_AVATAR)) .and(a.systemAvatar.eq(TRUE)) .and(a.fileName.eq("undefined.png"))) .orderBy(a.id.asc()) .singleResult(a.id)); return undefinedIconId.orElseGet( () -> callback.newSqlQuery() .from(a) .where(a.avatarType.eq(ISSUE_TYPE_AVATAR) .and(a.systemAvatar.eq(TRUE))) .singleResult(a.id)); }
@Override public Worklog getById(final Long id) { if (id == null) { return null; } final Tuple worklogTuple = dbConnectionManager.executeQuery(dc -> dc.newSqlQuery() .from(WORKLOG) .leftJoin(QProjectRole.PROJECT_ROLE) .on(QProjectRole.PROJECT_ROLE.id.eq(WORKLOG.rolelevel)) .where(WORKLOG.id.eq(id)) .singleResult(WORKLOG_ALIASED_PROJECT_ROLE_TUPLE)); return queryDSLWorklogFactory.createWorklogWithAliasedIdColumn(worklogTuple); }
@Override public Worklog update(final Worklog worklog) { final WorklogDTO worklogDTO = dbConnectionManager.executeQuery(dbConnection -> dbConnection.newSqlQuery() .from(WORKLOG) .where(WORKLOG.id.eq(worklog.getId())) .singleResult(WORKLOG)); if (worklogDTO != null) { dbConnectionManager.execute(dbConnection -> { setWorklogFields(dbConnection.update(WORKLOG), worklog) .where(WORKLOG.id.eq(worklog.getId())) .execute(); }); } else { throw new IllegalArgumentException("Could not find original worklog entity to update."); } return getById(worklog.getId()); }
/** * 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()); }
/** * 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); }
.and(a.systemAvatar.eq(TRUE)) .and(a.fileName.eq(DEFAULT_PROJECT_AVATAR_NAME))) .singleResult(a.id);