Java SQLQuery类代码示例

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
public void addToQuery(OrderBy orderBy, Expression<?> sqlExpression, SQLQuery<Tuple> sqlQuery) {
    if (sqlExpression instanceof ComparableExpressionBase) {
        ComparableExpressionBase comparable = (ComparableExpressionBase) sqlExpression;
        Expression<?> projection = sqlQuery.getMetadata().getProjection();
        if (projection instanceof QTuple) {
            QTuple qTuple = (QTuple) projection;
            List<Expression<?>> args = new ArrayList<>(qTuple.getArgs());
            args.add(comparable);
            sqlQuery.select(args.toArray(new Expression[args.size()]));
        }

        if (orderBy.getType() == OrderBy.OrderType.Ascending) {
            sqlQuery.orderBy(comparable.asc());
        } else {
            sqlQuery.orderBy(comparable.desc());
        }
    }
}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private Collection<SchemeDTO> listWorkSchemes() {
  return querydslSupport.execute((connection, configuration) -> {
    QWorkScheme qWorkScheme = QWorkScheme.workScheme;
    return new SQLQuery<SchemeDTO>(connection, configuration)
        .select(Projections.fields(SchemeDTO.class, qWorkScheme.workSchemeId.as("schemeId"),
            qWorkScheme.name.as("name")))
        .from(qWorkScheme)
        .where(qWorkScheme.scope.eq(WORK_SCHEME_SCOPE_GLOBAL))
        .orderBy(qWorkScheme.name.asc())
        .fetch();
  });
}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private static Expression<Long> replacementWeekdaySubSelect(final NumberPath<Long> workSchemeId,
    final Expression<Long> userId, final DatePath<Date> date) {

  QPublicHoliday qPublicHoliday = new QPublicHoliday("exp_work_repl_pubhday");
  QUserHolidayScheme qUserHolidayScheme = new QUserHolidayScheme("exp_work_repl_uhsch");
  QDateRange qDateRange = new QDateRange("exp_work_repl_uhschdr");

  SQLQuery<Long> query = new SQLQuery<Long>();
  query.select(weekdaySumForReplacementDay(workSchemeId, qPublicHoliday.date.dayOfWeek()))
      .from(qPublicHoliday)
      .innerJoin(qUserHolidayScheme)
      .on(qUserHolidayScheme.holidaySchemeId.eq(qPublicHoliday.holidaySchemeId))
      .innerJoin(qDateRange).on(qDateRange.dateRangeId.eq(qUserHolidayScheme.dateRangeId))
      .where(qPublicHoliday.replacementDate.eq(date)
          .and(qUserHolidayScheme.userId.eq(userId))
          .and(qDateRange.startDate.loe(date))
          .and(qDateRange.endDateExcluded.gt(date)));
  return query;
}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
public void addOrderbyToQuery(OrderBy orderBy, SQLQuery<Tuple> sqlQuery) {
    Expression<?> resultExpression = orderBy.getExpression().accept(this);
    if (resultExpression instanceof TimeIntervalExpression) {
        TimeIntervalExpression ti = (TimeIntervalExpression) resultExpression;
        addToQuery(orderBy, ti.getStart(), sqlQuery);
        addToQuery(orderBy, ti.getEnd(), sqlQuery);
    }
    if (resultExpression instanceof ConstantDurationExpression) {
        ConstantDurationExpression duration = (ConstantDurationExpression) resultExpression;
        addToQuery(orderBy, duration.getDuration(), sqlQuery);
    }
    if (resultExpression instanceof ListExpression) {
        for (Expression<?> sqlExpression : ((ListExpression) resultExpression).getExpressionsForOrder().values()) {
            addToQuery(orderBy, sqlExpression, sqlQuery);
        }
    } else {
        addToQuery(orderBy, resultExpression, sqlQuery);
    }
}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private SQLQuery<Long> moderatedGroups(final Collection<Long> includedGameSpeciesIds, final int huntingYear) {
    final SQBasicClubHuntingSummary bchs = new SQBasicClubHuntingSummary("bchs");
    final SQHarvestPermitSpeciesAmount hpsa = new SQHarvestPermitSpeciesAmount("hpsa");
    final SQOrganisation group = new SQOrganisation("group");

    return SQLExpressions
            .select(group.organisationId)
            .from(bchs)
            .join(hpsa).on(hpsa.harvestPermitSpeciesAmountId.eq(bchs.speciesAmountId))
            .join(group).on(
                    group.parentOrganisationId.eq(bchs.clubId),
                    group.organisationType.eq(Expressions.asString(OrganisationType.CLUBGROUP.name())),
                    group.gameSpeciesId.eq(hpsa.gameSpeciesId),
                    group.harvestPermitId.eq(hpsa.harvestPermitId))
            .where(hpsa.gameSpeciesId.in(includedGameSpeciesIds),
                    bchs.moderatorOverride.isTrue(),
                    bchs.huntingFinished.isTrue(),
                    sqlDateInsideHuntingYear(bchs.huntingEndDate, huntingYear),
                    group.huntingYear.eq(huntingYear));
}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private static SubQueryExpression<Long> harvestLinkedToGroupHuntingDay(final long huntingGroupId) {
    /*
    SELECT h3.harvest_id
    FROM harvest h3
    WHERE h3.group_hunting_day_id IN (
      SELECT group_hunting_day_id
      FROM group_hunting_day
      WHERE hunting_group_id = :huntingGroupId)
    */
    final SQHarvest harvest = new SQHarvest("h3");
    final SQGroupHuntingDay groupHuntingDay = SQGroupHuntingDay.groupHuntingDay;

    final SQLQuery<Long> groupHuntingDayIds = SQLExpressions.selectOne()
            .from(groupHuntingDay)
            .where(groupHuntingDay.huntingGroupId.eq(huntingGroupId))
            .select(groupHuntingDay.groupHuntingDayId);

    return SQLExpressions.select(harvest.harvestId)
            .from(harvest)
            .where(harvest.groupHuntingDayId.in(groupHuntingDayIds));
}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private static SubQueryExpression<Long> gameObservationLinkedToGroupHuntingDay(
        final HuntingClubGroup huntingClubGroup) {
    /*
    SELECT o3.game_observation_id
    FROM game_observation o3
    WHERE o3.group_hunting_day_id IN (
      SELECT group_hunting_day_id
      FROM group_hunting_day
      WHERE hunting_group_id = :huntingGroupId)
    */
    final SQObservation gameObservation = new SQObservation("o3");
    final SQGroupHuntingDay groupHuntingDay = SQGroupHuntingDay.groupHuntingDay;

    final SQLQuery<Long> groupHuntingDayIds = SQLExpressions.selectOne()
            .from(groupHuntingDay)
            .where(groupHuntingDay.huntingGroupId.eq(huntingClubGroup.getId()))
            .select(groupHuntingDay.groupHuntingDayId);

    return SQLExpressions.select(gameObservation.gameObservationId)
            .from(gameObservation)
            .where(gameObservation.groupHuntingDayId.in(groupHuntingDayIds));
}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private Map<String, Long> resolveProjectKeyIdMap(final Set<String> projectKeys) {
  Map<String, Long> result = querydslSupport.execute((connection, configuration) -> {
    List<Tuple> resultSet = new SQLQuery<>(connection, configuration)
        .select(QProject.project.pkey, QProject.project.id)
        .from(QProject.project)
        .where(QProject.project.pkey.in(projectKeys)).fetch();

    Map<String, Long> localResult = new HashMap<>();
    for (Tuple tuple : resultSet) {
      localResult.put(tuple.get(QProject.project.pkey), tuple.get(QProject.project.id));
    }
    return localResult;
  });

  checkNoProjectKeyMissing(projectKeys, result.keySet());
  return result;
}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private boolean hasOverlapping(final Long userId, final Date startDate,
    final Date endDateExcluded, final Long userHolidayAmountIdToExclude) {
  Long count = querydslSupport.execute((connection, configuration) -> {
    QDateRange qDateRange = QDateRange.dateRange;
    QUserHolidayAmount qUserHolidayAmount = QUserHolidayAmount.userHolidayAmount;
    SQLQuery<Long> query = new SQLQuery<>(connection, configuration)
        .select(qDateRange.dateRangeId)
        .from(qDateRange)
        .innerJoin(qUserHolidayAmount)
        .on(qDateRange.dateRangeId.eq(qUserHolidayAmount.dateRangeId));

    List<Predicate> predicates = new ArrayList<>();
    predicates.add(qUserHolidayAmount.userId.eq(userId));

    if (userHolidayAmountIdToExclude != null) {
      predicates.add(qUserHolidayAmount.userHolidayAmountId.ne(userHolidayAmountIdToExclude));
    }

    predicates.add(rangeOverlaps(qDateRange, startDate, endDateExcluded));

    query.where(predicates.toArray(new Predicate[predicates.size()]));
    return query.fetchCount();
  });
  return count > 0;
}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private void delete(final Long userSchemeId) {
  transactionTemplate.execute(() -> querydslSupport.execute((connection, configuration) -> {
    QDateRange qDateRange = QDateRange.dateRange;
    Long dateRangeId = new SQLQuery<Long>(connection, configuration)
        .select(qDateRange.dateRangeId)
        .from(qDateRange)
        .innerJoin(qUserSchemeEntityParameter.userSchemeEntityPath)
        .on(qUserSchemeEntityParameter.dateRangeId.eq(qDateRange.dateRangeId))
        .where(qUserSchemeEntityParameter.userSchemeId.eq(userSchemeId))
        .fetchOne();

    new SQLDeleteClause(connection, configuration,
        qUserSchemeEntityParameter.userSchemeEntityPath)
            .where(qUserSchemeEntityParameter.userSchemeId.eq(userSchemeId))
            .execute();

    new DateRangeUtil(connection, configuration).removeDateRange(dateRangeId);
    return null;
  }));
}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private void save(final long schemeId, final String userName, final Date startDate,
    final Date endDateExcluded) {
  transactionTemplate.execute(() -> querydslSupport.execute((connection, configuration) -> {
    QCwdUser qCwdUser = QCwdUser.cwdUser;
    Long userId = new SQLQuery<Long>(connection, configuration)
        .select(qCwdUser.id)
        .from(qCwdUser)
        .where(qCwdUser.userName.eq(userName)).fetchOne();

    Long dateRangeId =
        new DateRangeUtil(connection, configuration).createDateRange(startDate, endDateExcluded);

    new SQLInsertClause(connection, configuration,
        qUserSchemeEntityParameter.userSchemeEntityPath)
            .set(qUserSchemeEntityParameter.dateRangeId, dateRangeId)
            .set(qUserSchemeEntityParameter.userId, userId)
            .set(qUserSchemeEntityParameter.userSchemeSchemeId, schemeId).execute();

    return null;
  }));
}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private void update(final long recordId, final long schemeId, final long userId,
    final Date startDate, final Date endDateExcluded) {
  transactionTemplate.execute(() -> querydslSupport.execute((connection, configuration) -> {

    Long dateRangeId = new SQLQuery<Long>(connection, configuration)
        .select(qUserSchemeEntityParameter.dateRangeId)
        .from(qUserSchemeEntityParameter.userSchemeEntityPath)
        .where(qUserSchemeEntityParameter.userSchemeId.eq(recordId)).fetchOne();

    new DateRangeUtil(connection, configuration).modifyDateRange(dateRangeId, startDate,
        endDateExcluded);

    new SQLUpdateClause(connection, configuration,
        qUserSchemeEntityParameter.userSchemeEntityPath)
            .set(qUserSchemeEntityParameter.userId, userId)
            .where(qUserSchemeEntityParameter.userSchemeId.eq(recordId));
    return null;
  }));

}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private Set<WeekdayWorkDTO> getWeekdayWorks(final long workSchemeId) {

    List<WeekdayWorkDTO> resultSet = querydslSupport.execute((connection, configuration) -> {
      QWeekdayWork qWeekdayWork = QWeekdayWork.weekdayWork;
      return new SQLQuery<WeekdayWorkDTO>(connection, configuration)
          .select(
              Projections.fields(WeekdayWorkDTO.class, qWeekdayWork.weekdayWorkId,
                  qWeekdayWork.weekday, qWeekdayWork.startTime, qWeekdayWork.duration))
          .from(qWeekdayWork)
          .where(qWeekdayWork.workSchemeId.eq(workSchemeId))
          .fetch();
    });
    TreeSet<WeekdayWorkDTO> weekdayWorks = new TreeSet<WeekdayWorkDTO>(WEEKDAY_WORK_DTO_COMPARATOR);
    weekdayWorks.addAll(resultSet);

    return weekdayWorks;
  }
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private void removeAllUsersFromScheme(final long schemeId, final Connection connection,
    final Configuration configuration) {

  final int batchSize = 100;
  QUserWorkScheme qUserWorkScheme = QUserWorkScheme.userWorkScheme;

  SQLQuery<Long> sqlQuery = new SQLQuery<Long>(connection, configuration)
      .select(qUserWorkScheme.dateRangeId).from(qUserWorkScheme)
      .where(qUserWorkScheme.workSchemeId.eq(schemeId)).limit(batchSize);

  List<Long> dateRangeIds = sqlQuery.fetch();
  DateRangeUtil dateRangeUtil = new DateRangeUtil(connection, configuration);

  while (!dateRangeIds.isEmpty()) {
    new SQLDeleteClause(connection, configuration, qUserWorkScheme)
        .where(qUserWorkScheme.dateRangeId.in(dateRangeIds)).execute();

    dateRangeUtil.removeDateRange(dateRangeIds.toArray(new Long[dateRangeIds.size()]));

    dateRangeIds = sqlQuery.fetch();
  }

}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private Object synchronizeDateSequenceIfNecessary(final Connection connection,
    final Configuration configuration) {

  QDateSequence qDateSequence = QDateSequence.dateSequence;
  long dateSequenceCount = new SQLQuery<>(connection, configuration)
      .select(Expressions.ONE.count()).from(qDateSequence).fetchOne();

  if (dateSequenceCount == 0) {
    QDateRange qDateRange = QDateRange.dateRange;
    long dateRangeCount = new SQLQuery<>(connection, configuration)
        .select(Expressions.ONE.count()).from(qDateRange).fetchOne();
    if (dateRangeCount != 0) {
      fillDateSequenceFromDateRanges(connection, configuration, dateRangeCount);
    }
  }
  return null;
}