@Override @Transactional(readOnly = true) public List<Customer> findAll() { SQLQuery allCustomersQuery = qdslTemplate.newSqlQuery().from(qCustomer) .leftJoin(qCustomer._addressCustomerRef, qAddress); return qdslTemplate.query(allCustomersQuery, new CustomerListExtractor(), customerAddressProjection); }
private boolean lockOnResource(final Connection connection, final Configuration configuration, final long resourceId) { SQLQuery query = new SQLQuery(connection, configuration); QResource resource = QResource.resource; List<Long> results = query.from(resource).where(resource.resourceId.eq(resourceId)).forUpdate() .list(resource.resourceId); return !(results.size() == 0); }
@Override public Set<Worklog> getWorklogsForIds(final Set<Long> worklogIds, final int maxResults) { return dbConnectionManager.executeQuery(dbConnection -> dbConnection.newSqlQuery() .from(WORKLOG) .leftJoin(QProjectRole.PROJECT_ROLE) .on(QProjectRole.PROJECT_ROLE.id.eq(WORKLOG.rolelevel)) .where(WORKLOG.id.in(worklogIds)) .limit(maxResults) .list(WORKLOG_ALIASED_PROJECT_ROLE_TUPLE) ).stream() .map(queryDSLWorklogFactory::createWorklogWithAliasedIdColumn) .collect(Collectors.toSet()); }
@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); }); }
private AuditApplication selectAuditApplication(final String applicationName) { return querydslSupport.execute((connection, configuration) -> { QApplication qApplication = QApplication.application; return new SQLQuery(connection, configuration) .from(qApplication) .where(qApplication.applicationName.eq(applicationName)) .uniqueResult(Projections.fields(AuditApplication.class, qApplication.applicationId, qApplication.applicationName, qApplication.resourceId)); }); }
private List<Long> getMinusOneUserLocalePropertyIds(DbConnection dbConnection) { QOSPropertyEntry propertyEntry = QOSPropertyEntry.O_S_PROPERTY_ENTRY; QOSPropertyString propertyString = QOSPropertyString.O_S_PROPERTY_STRING; // MSSQL meet incompatible data type problem when we use equal operator (IN, EQUALS) so we have to use LIKE instead List<Long> propertyIds = dbConnection.newSqlQuery().from(propertyEntry).join(propertyString) .on(propertyEntry.id.eq(propertyString.id)) .where(propertyEntry.propertyKey.eq("jira.user.locale").and(propertyString.value.like("-1"))) .list(propertyString.id); return propertyIds; }
public ConstantsCache<Resolution> get() { final List<ResolutionDTO> resolutionList = dbConnectionManager.executeQuery(dbConnection -> dbConnection.newSqlQuery().from(RESOLUTION) .orderBy(RESOLUTION.sequence.asc()).list(RESOLUTION)); final ImmutableMap.Builder<String, Resolution> builder = ImmutableMap.builder(); resolutionList.stream().map(issueConstantFactory::createResolution).forEach(r -> builder.put(r.getId(), r)); return new ConstantsCache<>(builder.build()); } }
private List<AuditEventType> selectAuditEventTypes(final String applicationName, final List<String> eventTypeNames) { return querydslSupport.execute((connection, configuration) -> { QEventType qEventType = QEventType.eventType; QApplication qApplication = QApplication.application; List<AuditEventType> rval = new ArrayList<>(); int numberOfEventTypeNames = eventTypeNames.size(); for (int fromIndex = 0; fromIndex < numberOfEventTypeNames; fromIndex = fromIndex + SELECT_AUDIT_EVENT_TYPES_PAGE_SIZE) { int toIndex = fromIndex + SELECT_AUDIT_EVENT_TYPES_PAGE_SIZE; if (toIndex > numberOfEventTypeNames) { toIndex = numberOfEventTypeNames; } List<String> actualEventTypeNames = new ArrayList<>(eventTypeNames.subList(fromIndex, toIndex)); List<AuditEventType> auditEventTypes = new SQLQuery(connection, configuration) .from(qEventType) .innerJoin(qApplication).on(qEventType.applicationId.eq(qApplication.applicationId)) .where(qApplication.applicationName.eq(applicationName) .and(qEventType.eventTypeName.in(actualEventTypeNames))) .list(Projections.fields(AuditEventType.class, qEventType.eventTypeId, qEventType.eventTypeName, qEventType.resourceId)); rval.addAll(auditEventTypes); } return rval; }); }
public List<IssueType> getEditableSubTaskIssueTypes() { final List<String> ids = dbConnectionManager.executeQuery(dbConnection -> dbConnection.newSqlQuery() .from(ISSUE_TYPE) .where(ISSUE_TYPE.style.eq(SubTaskManager.SUB_TASK_ISSUE_TYPE_STYLE)) .list(bean(ISSUE_TYPE.id))); final ImmutableList.Builder<IssueType> listBuilder = ImmutableList.builder(); ids.stream().map(this::getIssueType).forEach(listBuilder::add); return listBuilder.build(); }
private List<QueuedEmailDTO> selectQueuedEmailForUpdate(final int limit) { return querydslSupport.execute((connection, configuration) -> { QEmailQueue qEmailQueue = QEmailQueue.emailQueue; return new SQLQuery(connection, configuration) .from(qEmailQueue) .orderBy(qEmailQueue.timestamp_.asc(), qEmailQueue.queuedEmailId.asc()) .limit(limit) .forUpdate() .list(Projections.fields(QueuedEmailDTO.class, qEmailQueue.queuedEmailId, qEmailQueue.storedEmailId)); }); }
private boolean readPermissionFromDatabase(final long authorizedResourceId, final long targetResourceId, final String action) { return querydslSupport.execute((connection, configuration) -> { SQLQuery query = new SQLQuery(connection, configuration); QPermission permission = QPermission.permission; return query.from(permission) .where(permission.authorizedResourceId.eq(authorizedResourceId) .and(permission.targetResourceId.eq(targetResourceId)) .and(permission.action.eq(action))) .exists(); }); }
@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 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); }
@VisibleForTesting List<Integer> findDowngradeTasksToRun() throws DowngradeException { // Get all upgrade tasks that have run from the UpgradeHistory table in the DB final List<UpgradeHistoryDTO> upgradeHistoryItems = dbConnectionManager.executeQuery( dbConnection -> dbConnection.newSqlQuery() .from(QUpgradeHistory.UPGRADE_HISTORY) .list(QUpgradeHistory.UPGRADE_HISTORY) ); return DowngradeUtil.findDowngradeTasksToRun(upgradeHistoryItems, buildUtilsInfo.getApplicationBuildNumber()); }
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)); }
/** * 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()); }
.from(a) .where(a.avatarType.eq(ISSUE_TYPE_AVATAR) .and(a.systemAvatar.eq(TRUE)) .and(a.fileName.in(issueTypeIconsToReplace.keySet()))) .groupBy(a.fileName) .map(a.fileName, a.id.min()); .from(a) .where(a.avatarType.eq(ISSUE_TYPE_AVATAR) .and(a.systemAvatar.eq(TRUE)) .and(a.fileName.in(issueTypeIconsToReplace.values()))) .groupBy(a.fileName) .map(a.fileName, a.id.min()); .from(a) .where(a.avatarType.eq(ISSUE_TYPE_AVATAR) .and(a.systemAvatar.eq(TRUE)) .and(a.fileName.eq(iconToAdd)) .and(a.contentType.eq(SVG_CONTENT_TYPE))) .count();