Java QueryParameter类代码示例

import nl.strohalm.cyclos.utils.hibernate.HibernateHelper.QueryParameter; //导入依赖的package包/类
private void appendMembersTransactionsDetailsReportSqlPart(final StringBuilder sql, final boolean useTT, final QueryParameter beginParameter, final QueryParameter endParameter, final boolean credits, final boolean notChargeBack) {
    final boolean flag = notChargeBack ? credits : !credits;
    final String account = flag ? "to_account_id" : "from_account_id";
    final String related = flag ? "from_account_id" : "to_account_id";
    sql.append(" select a.member_id, at.id as account_type_id, at.name account_type_name, t.id transfer_id, t.process_date date, " + (credits ? "" : "-1 * ") + "abs(t.amount) amount, t.description, ra.owner_name related_username, rm.name related_name, tt.name transfer_type_name, t.transaction_number");
    sql.append(" from transfers t inner join accounts a on t.").append(account).append(" = a.id inner join accounts ra on t.").append(related).append(" = ra.id inner join transfer_types tt on t.type_id = tt.id inner join account_types at on a.type_id = at.id left join members rm on ra.member_id = rm.id");
    sql.append(" where t.status = :processed");
    sql.append("   and t.chargeback_of_id is ").append(notChargeBack ? "" : "not ").append("null");
    if (useTT) {
        sql.append("   and t.type_id in (:ttIds)");
    }
    if (beginParameter != null) {
        sql.append("   and t.process_date " + beginParameter.getOperator() + " :beginDate");
    }
    if (endParameter != null) {
        sql.append("   and t.process_date " + endParameter.getOperator() + " :endDate");
    }
}
 

import nl.strohalm.cyclos.utils.hibernate.HibernateHelper.QueryParameter; //导入依赖的package包/类
private void appendMembersTransactionsSummaryReportSqlPart(final StringBuilder sql, final boolean useGroups, final boolean useTT, final QueryParameter beginParameter, final QueryParameter endParameter, final boolean credits, final boolean notChargeBack) {
    final boolean flag = notChargeBack ? credits : !credits;
    final String account = flag ? "to_account_id" : "from_account_id";

    sql.append(" select m.id as member_id, m.name as member_name, u.username, count(t.id) as count, sum(abs(t.amount)) as amount");
    sql.append(" from transfers t inner join accounts a on t.").append(account).append(" = a.id inner join members m on a.member_id = m.id inner join users u on m.id = u.id");
    sql.append(" where t.status = :processed");
    sql.append("   and t.chargeback_of_id is ").append(notChargeBack ? "null" : "not null");
    if (useGroups) {
        sql.append("   and m.group_id in (:groupIds)");
    }
    if (useTT) {
        sql.append("   and t.type_id in (:ttIds)");
    }
    if (beginParameter != null) {
        sql.append("   and t.process_date " + beginParameter.getOperator() + " :beginDate");
    }
    if (endParameter != null) {
        sql.append("   and t.process_date " + endParameter.getOperator() + " :endDate");
    }
    sql.append(" group by m.id, m.name, u.username");
}
 

import nl.strohalm.cyclos.utils.hibernate.HibernateHelper.QueryParameter; //导入依赖的package包/类
@Override
public List<InfoText> search(final InfoTextQuery query) {
    final Map<String, Object> namedParameters = new HashMap<String, Object>();
    final StringBuilder hql = HibernateHelper.getInitialQuery(InfoText.class, "info", query.getFetch());

    final String alias = StringUtils.trimToNull(query.getAlias());
    if (alias != null) {
        hql.append(" and :alias in elements(info.aliases) ");
        namedParameters.put("alias", alias);
    }

    if (query.getKeywords() != null) {
        hql.append(" and (info.subject like :keywords or info.body like :keywords) ");
        namedParameters.put("keywords", "%" + query.getKeywords().toUpperCase() + "%");
    }

    if (query.isWithBodyOnly()) {
        hql.append(" and length(info.body) > 0");
    }

    if (query.isOnlyActive()) {
        HibernateHelper.addParameterToQuery(hql, namedParameters, "info.enabled", Boolean.TRUE);
        final Period period = Period.day(Calendar.getInstance());
        QueryParameter beginParameter = HibernateHelper.getBeginParameter(period);
        if (beginParameter != null) {
            hql.append(" and (info.validity.end is null or info.validity.end " + beginParameter.getOperator() + " :_begin_)");
            namedParameters.put("_begin_", beginParameter.getValue());
        }
        QueryParameter endParameter = HibernateHelper.getEndParameter(period);
        if (endParameter != null) {
            hql.append(" and (info.validity.begin is null or info.validity.begin " + endParameter.getOperator() + " :_end_)");
            namedParameters.put("_end_", endParameter.getValue());
        }
    } else {
        HibernateHelper.addParameterToQuery(hql, namedParameters, "info.enabled", query.getEnabled());
        HibernateHelper.addPeriodParameterToQuery(hql, namedParameters, "info.validity.begin", query.getStartIn());
        HibernateHelper.addPeriodParameterToQuery(hql, namedParameters, "info.validity.end", query.getEndIn());
    }

    return list(query, hql.toString(), namedParameters);
}
 

import nl.strohalm.cyclos.utils.hibernate.HibernateHelper.QueryParameter; //导入依赖的package包/类
@Override
public IteratorList<AccountDailyDifference> iterateDailyDifferences(final MemberAccount account, final Period period) {
    Map<String, Object> params = new HashMap<String, Object>();
    params.put("accountId", account.getId());
    QueryParameter beginParameter = HibernateHelper.getBeginParameter(period);
    QueryParameter endParameter = HibernateHelper.getEndParameter(period);
    if (beginParameter != null) {
        params.put("begin", beginParameter.getValue());
    }
    if (endParameter != null) {
        params.put("end", endParameter.getValue());
    }
    StringBuilder sql = new StringBuilder();
    sql.append(" select type, date(d.date) as date, sum(amount) as amount ");
    sql.append(" from ( ");
    sql.append("     select 'B' as type, t.process_date as date, ");
    sql.append("         case when t.chargeback_of_id is null then ");
    sql.append("             case when t.from_account_id = :accountId then -t.amount else t.amount end ");
    sql.append("         else ");
    sql.append("             case when t.to_account_id = :accountId then t.amount else -t.amount end ");
    sql.append("         end as amount ");
    sql.append("      from transfers t ");
    sql.append("      where (t.from_account_id = :accountId or t.to_account_id = :accountId) ");
    sql.append("      and t.process_date is not null ");
    if (beginParameter != null) {
        sql.append("  and t.process_date " + beginParameter.getOperator() + " :begin");
    }
    if (endParameter != null) {
        sql.append("  and t.process_date " + endParameter.getOperator() + " :end");
    }
    sql.append("      union ");
    sql.append("      select 'R', r.date, r.amount ");
    sql.append("      from amount_reservations r ");
    sql.append("      where r.account_id = :accountId ");
    if (beginParameter != null) {
        sql.append("  and r.date " + beginParameter.getOperator() + " :begin");
    }
    if (endParameter != null) {
        sql.append("  and r.date " + endParameter.getOperator() + " :end");
    }
    sql.append(" ) d ");
    sql.append(" group by type, date(d.date) ");
    sql.append(" order by date(d.date) ");
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.addScalar("type", StandardBasicTypes.STRING);
    query.addScalar("date", StandardBasicTypes.CALENDAR_DATE);
    query.addScalar("amount", StandardBasicTypes.BIG_DECIMAL);
    getHibernateQueryHandler().setQueryParameters(query, params);
    ScrollableResults results = query.scroll(ScrollMode.SCROLL_INSENSITIVE);
    return new IteratorListImpl<AccountDailyDifference>(new DiffsIterator(results));
}
 

import nl.strohalm.cyclos.utils.hibernate.HibernateHelper.QueryParameter; //导入依赖的package包/类
@Override
public Iterator<MemberTransactionSummaryVO> membersTransactionSummaryReport(final Collection<MemberGroup> memberGroups, final PaymentFilter paymentFilter, final Period period, final boolean credits, final MemberResultDisplay order) {
    final Map<String, Object> parameters = new HashMap<String, Object>();
    final StringBuilder sql = new StringBuilder();

    // Get the transfer types ids
    final List<Long> ttIds = paymentFilter == null ? null : Arrays.asList(EntityHelper.toIds(paymentFilter.getTransferTypes()));

    // Get the member group ids
    List<Long> groupIds = null;
    if (CollectionUtils.isNotEmpty(memberGroups)) {
        groupIds = Arrays.asList(EntityHelper.toIds(memberGroups));
    }

    // Get the period
    final QueryParameter beginParameter = HibernateHelper.getBeginParameter(period);
    final QueryParameter endParameter = HibernateHelper.getEndParameter(period);

    // Set the parameters
    final boolean useGroups = CollectionUtils.isNotEmpty(groupIds);
    final boolean useTT = CollectionUtils.isNotEmpty(ttIds);
    if (useGroups) {
        parameters.put("groupIds", groupIds);
    }
    if (useTT) {
        parameters.put("ttIds", ttIds);
    }
    if (beginParameter != null) {
        parameters.put("beginDate", beginParameter.getValue());
    }
    if (endParameter != null) {
        parameters.put("endDate", endParameter.getValue());
    }
    parameters.put("processed", Payment.Status.PROCESSED.getValue());

    // Create the SQL query
    sql.append(" select member_id, sum(count) as count, sum(amount) as amount");
    sql.append(" from (");
    appendMembersTransactionsSummaryReportSqlPart(sql, useGroups, useTT, beginParameter, endParameter, credits, true);
    sql.append(" union");
    appendMembersTransactionsSummaryReportSqlPart(sql, useGroups, useTT, beginParameter, endParameter, credits, false);
    sql.append(" ) ts");
    sql.append(" group by member_id");
    sql.append(" order by ").append(order == MemberResultDisplay.NAME ? "member_name, member_id" : "username");

    final SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.addScalar("member_id", StandardBasicTypes.LONG);
    query.addScalar("count", StandardBasicTypes.INTEGER);
    query.addScalar("amount", StandardBasicTypes.BIG_DECIMAL);
    getHibernateQueryHandler().setQueryParameters(query, parameters);

    final Transformer<Object[], MemberTransactionSummaryVO> transformer = new Transformer<Object[], MemberTransactionSummaryVO>() {
        @Override
        public MemberTransactionSummaryVO transform(final Object[] input) {
            final MemberTransactionSummaryVO vo = new MemberTransactionSummaryVO();
            vo.setMemberId((Long) input[0]);
            vo.setCount((Integer) input[1]);
            vo.setAmount((BigDecimal) input[2]);
            return vo;
        }
    };

    return new ScrollableResultsIterator<MemberTransactionSummaryVO>(query, transformer);
}