Example usage for org.hibernate Query setParameterList

List of usage examples for org.hibernate Query setParameterList

Introduction

In this page you can find the example usage for org.hibernate Query setParameterList.

Prototype

Query<R> setParameterList(int position, Object[] values);

Source Link

Usage

From source file:org.bonitasoft.engine.test.persistence.repository.UserMembershipRepository.java

License:Open Source License

public Long getNumberOfUserMembersForUserOrManagerForActorMembers(long userId,
        final List<Long> actorMemberIds) {
    getSession().enableFilter("tenantFilter").setParameter("tenantId",
            PersistentObjectBuilder.DEFAULT_TENANT_ID);
    Query namedQuery = getNamedQuery("getNumberOfUserMembersForUserOrManagerForActorMembers");
    namedQuery.setParameter("userId", userId);
    namedQuery.setParameterList("actorMemberIds", actorMemberIds);
    return ((Number) namedQuery.uniqueResult()).longValue();
}

From source file:org.brushingbits.jnap.persistence.hibernate.QueryUtils.java

License:Apache License

/**
 * /*from w  w w.  j  a va2 s  .  c  o m*/
 * @param query
 * @param params
 */
public static void setNamedParameters(Query query, Map<String, ?> params) {
    if (params != null && !params.isEmpty()) {
        for (String name : params.keySet()) {
            Object paramValue = params.get(name);
            if (Collection.class.isAssignableFrom(paramValue.getClass())) {
                query.setParameterList(name, (Collection) paramValue);
            } else if (paramValue.getClass().isArray()) {
                query.setParameterList(name, (Object[]) paramValue);
            } else {
                query.setParameter(name, paramValue);
            }
        }
    }
}

From source file:org.candlepin.gutterball.curator.ComplianceSnapshotCurator.java

License:Open Source License

/**
 * Builds the Query object to be used by the getComplianceStatusCounts method.
 * <p></p>/*  w  w  w  .j a  v a 2  s. c  o  m*/
 * The Query object is constructed with HQL translated from the following SQL:
 * <p></p><pre>
 *  SELECT
 *    ConsumerState.uuid,
 *    ComplianceStatusSnap.status,
 *    ComplianceStatusSnap.date
 *
 *  FROM
 *    "gb_consumer_state" ConsumerState
 *
 *    INNER JOIN "gb_consumer_snap" ConsumerSnap
 *      ON ConsumerSnap.uuid = ConsumerState.uuid
 *
 *    INNER JOIN "gb_compliance_snap" ComplianceSnap
 *      ON ComplianceSnap.id = ConsumerSnap.compliance_snap_id
 *
 *    INNER JOIN "gb_compliance_status_snap" ComplianceStatusSnap
 *      ON ComplianceStatusSnap.compliance_snap_id = ComplianceSnap.id
 *
 *  WHERE (
 *      ConsumerState.deleted IS NULL
 *
 *      OR date_part('year', ComplianceSnap.date) < date_part('year', ConsumerState.deleted)
 *
 *      OR (
 *          date_part('year', ComplianceSnap.date) = date_part('year', ConsumerState.deleted)
 *          AND date_part('month', ComplianceSnap.date) < date_part('month', ConsumerState.deleted)
 *      )
 *
 *      OR (
 *          date_part('year', ComplianceSnap.date) = date_part('year', ConsumerState.deleted)
 *          AND date_part('month', ComplianceSnap.date) = date_part('month', ConsumerState.deleted)
 *          AND date_part('day', ComplianceSnap.date) < date_part('day', ConsumerState.deleted)
 *      )
 *    )
 *
 *    AND (ComplianceStatusSnap.date, ConsumerSnap.uuid) IN (
 *      SELECT
 *        max(ComplianceSnap2.date) AS maxdate,
 *        ConsumerState2.uuid
 *
 *      FROM
 *        "gb_consumer_state" ConsumerState2
 *
 *        INNER JOIN "gb_consumer_snap" ConsumerSnap2
 *          ON ConsumerSnap2.uuid = ConsumerState2.uuid
 *
 *        INNER JOIN "gb_compliance_snap" ComplianceSnap2
 *          ON ComplianceSnap2.id = ConsumerSnap2.compliance_snap_id
 *
 *        INNER JOIN "gb_compliance_status_snap" ComplianceStatusSnap2
 *          ON ComplianceStatusSnap2.compliance_snap_id = ComplianceSnap2.id
 *
 *      GROUP BY
 *        date_part('year', ComplianceSnap2.date),
 *        date_part('month', ComplianceSnap2.date),
 *        date_part('day', ComplianceSnap2.date),
 *        ConsumerState2.uuid
 *    )
 *
 *    -- Min date
 *    AND (
 *      date_part('year', ComplianceSnap.date) > 2014
 *
 *      OR (
 *        date_part('year', ComplianceSnap.date) = 2014
 *        AND date_part('month', ComplianceSnap.date) > 11
 *      )
 *
 *      OR (
 *        date_part('year', ComplianceSnap.date) = 2014
 *        AND date_part('month', ComplianceSnap.date) = 11
 *        AND date_part('day', ComplianceSnap.date) >= 25
 *      )
 *
 *      OR (ComplianceStatusSnap.date, ConsumerSnap.uuid) IN (
 *        SELECT
 *          max(ComplianceSnap3.date) AS maxdate,
 *          ConsumerState3.uuid
 *
 *        FROM
 *          "gb_consumer_state" ConsumerState3
 *
 *          INNER JOIN "gb_consumer_snap" ConsumerSnap3
 *            ON ConsumerSnap3.uuid = ConsumerState3.uuid
 *
 *          INNER JOIN "gb_compliance_snap" ComplianceSnap3
 *            ON ComplianceSnap3.id = ConsumerSnap3.compliance_snap_id
 *
 *          INNER JOIN "gb_compliance_status_snap" ComplianceStatusSnap3
 *            ON ComplianceStatusSnap3.compliance_snap_id = ComplianceSnap3.id
 *
 *        WHERE
 *          date_part('year', ComplianceSnap3.date) < 2014
 *
 *          OR (
 *            date_part('year', ComplianceSnap3.date) = 2014
 *            AND date_part('month', ComplianceSnap3.date) < 11
 *          )
 *
 *          OR (
 *            date_part('year', ComplianceSnap3.date) = 2014
 *            AND date_part('month', ComplianceSnap3.date) = 11
 *            AND date_part('day', ComplianceSnap3.date) < 25
 *          )
 *
 *        GROUP BY
 *          ConsumerState3.uuid
 *      )
 *    )
 *
 *    -- Max date
 *    AND (
 *      date_part('year', ComplianceSnap.date) < 2014
 *
 *      OR (
 *        date_part('year', ComplianceSnap.date) = 2014
 *        AND date_part('month', ComplianceSnap.date) < 11
 *      )
 *
 *      OR (
 *        date_part('year', ComplianceSnap.date) = 2014
 *        AND date_part('month', ComplianceSnap.date) = 11
 *        AND date_part('day', ComplianceSnap.date) <= 15
 *      )
 *    )
 *
 *    -- Checking for the SKU, Product Name or attributes
 *    AND ComplianceSnap.id IN (
 *      SELECT ConsumerSnapI.compliance_snap_id
 *        FROM "gb_consumer_snap" ConsumerSnapI
 *        LEFT JOIN "gb_entitlement_snap" EntitlementSnap
 *          ON EntitlementSnap.compliance_snap_id = ConsumerSnapI.compliance_snap_id
 *
 *        LEFT JOIN "gb_ent_attr_snap" EntitlementAttributeSnap
 *          ON EntitlementAttributeSnap.ent_snap_id = EntitlementSnap.id
 *
 *        WHERE
 *          ConsumerSnapI.uuid = ConsumerSnap.uuid
 *          --AND (
 *          --  EntitlementSnap.product_id = User-input SKU
 *          --  OR EntitlementSnap.product_name = User-input name (matches-like?)
 *          --  OR (
 *          --    EntitlementAttributeSnap.gb_ent_attr_name = 'management_enabled'
 *          --    AND EntitlementAttributeSnap.gb_ent_attr_value = 1
 *          --  )
 *          --)
 *    )
 *
 *  ORDER BY
 *    ComplianceStatusSnap.date ASC
 *  </pre>
 *
 * @param session
 *  The session to use to create the query.
 *
 * @param startDate
 *  The date at which the time span should begin. If null, all compliance statuses before the
 *  end date (if provided) will be used.
 *
 * @param endDate
 *  The date at which the time span should end. If null, all compliance statuses after the
 *  start date (if provided) will be used.
 *
 * @param sku
 *  A subscription sku to use to filter compliance status counts. If provided, only consumers
 *  using the specified sku will be counted.
 *
 * @param subscriptionName
 *  A product name to use to filter compliance status counts. If provided, only consumers using
 *  subscriptions which provide the specified product name will be counted.
 *
 * @param productName
 *  A product name to use to filter compliance status counts. If provided, only consumers with
 *  an installed product with the specified product name will be counted.
 *
 * @param attributes
 *  A map of entitlement attributes to use to filter compliance status counts. If provided, only
 *  consumers with entitlements having the specified values for the given attributes will be
 *  counted.
 *
 * @param ownerKey
 *  An owner key to use to filter compliance status counts. If provided, only consumers
 *  associated with the specified owner key/account will be counted.
 *
 * @return
 *  A Query object to be used for retrieving compliance status counts.
 */
@SuppressWarnings({ "checkstyle:methodlength", "checkstyle:indentation" })
private Query buildComplianceStatusCountQuery(Session session, Date startDate, Date endDate, String ownerKey,
        List<String> consumerUuids, String sku, String subscriptionName, String productName,
        Map<String, String> attributes) {

    List<Object> parameters = new LinkedList<Object>();
    int counter = 0;

    StringBuilder hql = new StringBuilder("SELECT " + "ConsumerState.uuid," + "ComplianceStatusSnap.status,"
            + "ComplianceStatusSnap.date," + "ConsumerState.deleted " +

            "FROM " + "Consumer AS ConsumerSnap " + "INNER JOIN ConsumerSnap.consumerState AS ConsumerState "
            + "INNER JOIN ConsumerSnap.complianceSnapshot AS ComplianceSnap "
            + "INNER JOIN ComplianceSnap.status AS ComplianceStatusSnap "
            + "LEFT JOIN ComplianceSnap.entitlements AS EntitlementSnap " +

            "WHERE (" + "ConsumerState.deleted IS NULL "
            + "OR year(ComplianceSnap.date) < year(ConsumerState.deleted) " + "OR ("
            + "year(ComplianceSnap.date) = year(ConsumerState.deleted) "
            + "AND month(ComplianceSnap.date) < month(ConsumerState.deleted) " + ") " + "OR ("
            + "year(ComplianceSnap.date) = year(ConsumerState.deleted) "
            + "AND month(ComplianceSnap.date) = month(ConsumerState.deleted) "
            + "AND day(ComplianceSnap.date) < day(ConsumerState.deleted)" + ")" + ") " +

            "AND (ComplianceStatusSnap.date, ConsumerSnap.uuid) IN (" + "SELECT "
            + "max(ComplianceSnap2.date) AS maxdate, " + "ConsumerState2.uuid " +

            "FROM " + "Consumer AS ConsumerSnap2 " + "INNER JOIN ConsumerSnap2.consumerState AS ConsumerState2 "
            + "INNER JOIN ConsumerSnap2.complianceSnapshot AS ComplianceSnap2 "
            + "INNER JOIN ComplianceSnap2.status AS ComplianceStatusSnap2 " +

            "GROUP BY " + "year(ComplianceSnap2.date)," + "month(ComplianceSnap2.date),"
            + "day(ComplianceSnap2.date)," + "ConsumerState2.uuid " + ") ");

    // Add our reporting criteria...
    if (sku != null || subscriptionName != null || (attributes != null && attributes.size() > 0)
            || ownerKey != null || (consumerUuids != null && consumerUuids.size() > 0) || productName != null) {

        List<String> criteria = new LinkedList<String>();
        StringBuffer inner = new StringBuffer("AND (");

        // TODO:
        // Owner, SKU, product name and should be replaced by the same mechanism we used for
        // --matches in Subscription-manager.
        if (ownerKey != null) {
            criteria.add("ConsumerState.ownerKey = ?" + ++counter);
            parameters.add(ownerKey);
        }

        if (sku != null) {
            criteria.add("EntitlementSnap.productId = ?" + ++counter);
            parameters.add(sku);
        }

        if (subscriptionName != null) {
            criteria.add("EntitlementSnap.productName = ?" + ++counter);
            parameters.add(subscriptionName);
        }

        if (productName != null) {
            criteria.add(String.format("?%d IN (" + "SELECT Installed.productName " + "FROM "
                    + "Consumer AS ConsumerSnapP " + "INNER JOIN ConsumerSnapP.installedProducts AS Installed "
                    + "INNER JOIN ConsumerSnapP.complianceSnapshot AS ComplianceSnapP "
                    + "INNER JOIN ComplianceSnapP.status AS ComplianceStatusSnapP "
                    + "LEFT JOIN ComplianceStatusSnapP.compliantProducts AS CProduct "
                    + "LEFT JOIN ComplianceStatusSnapP.nonCompliantProducts AS NCProduct "
                    + "LEFT JOIN ComplianceStatusSnapP.partiallyCompliantProducts AS PCProduct " + "WHERE "
                    + "ComplianceStatusSnapP.id = ComplianceStatusSnap.id " + "AND ("
                    + "Installed.productId = CProduct.productId "
                    + "OR Installed.productId = NCProduct.productId "
                    + "OR Installed.productId = PCProduct.productId " + ")" + ")", ++counter));

            parameters.add(productName);
        }

        if (attributes != null && attributes.size() > 0) {
            if (attributes.containsKey("management_enabled")) {
                boolean managementEnabledFilter = PropertyConverter
                        .toBoolean(attributes.get("management_enabled"));
                criteria.add("ComplianceStatusSnap.managementEnabled = ?" + ++counter);
                parameters.add(managementEnabledFilter);

                // Don't process this attribute as part of entitlement attributes,
                // as it has already been handled.
                attributes.remove("management_enabled");
            }

            for (Map.Entry<String, String> entry : attributes.entrySet()) {
                criteria.add(String.format(
                        "(?%d, ?%d) IN (" + "SELECT ENTRY(EntitlementSnapA.attributes) "
                                + "FROM Entitlement AS EntitlementSnapA "
                                + "WHERE EntitlementSnapA.id = EntitlementSnap.id" + ")",
                        ++counter, ++counter));

                parameters.add(entry.getKey());
                parameters.add(entry.getValue());
            }
        }

        if (consumerUuids != null && consumerUuids.size() > 0) {
            criteria.add("ConsumerState.uuid IN (?" + ++counter + ")");
            parameters.add(consumerUuids);
        }

        // Append the criteria to our where clause and close it.
        Iterator<String> ci = criteria.iterator();
        inner.append(ci.next());

        while (ci.hasNext()) {
            inner.append(" AND ");
            inner.append(ci.next());
        }

        hql.append(inner.append(") "));
    }

    // Add our date range, if necessary...
    if (startDate != null) {
        int year = startDate.getYear() + 1900;
        int month = startDate.getMonth() + 1;
        int day = startDate.getDate();

        hql.append(String.format("AND (" + "year(ComplianceStatusSnap.date) > ?%1$d " + "OR ("
                + "year(ComplianceStatusSnap.date) = ?%1$d " + "AND month(ComplianceStatusSnap.date) > ?%2$d"
                + ") " + "OR (" + "year(ComplianceStatusSnap.date) = ?%1$d "
                + " AND month(ComplianceStatusSnap.date) = ?%2$d "
                + " AND day(ComplianceStatusSnap.date) >= ?%3$d" + ") "
                + "OR (ComplianceStatusSnap.date, ConsumerSnap.uuid) IN (" + "SELECT "
                + "max(ComplianceStatusSnap2.date) AS maxdate, " + "ConsumerState2.uuid " +

                "FROM " + "Consumer AS ConsumerSnap2 "
                + "INNER JOIN ConsumerSnap2.consumerState AS ConsumerState2 "
                + "INNER JOIN ConsumerSnap2.complianceSnapshot AS ComplianceSnap2 "
                + "INNER JOIN ComplianceSnap2.status AS ComplianceStatusSnap2 " +

                "WHERE " + "year(ComplianceStatusSnap2.date) < ?%1$d " + "OR ("
                + "year(ComplianceStatusSnap2.date) = ?%1$d " + "AND month(ComplianceStatusSnap2.date) < ?%2$d"
                + ") " + "OR (" + "year(ComplianceStatusSnap2.date) = ?%1$d "
                + "AND month(ComplianceStatusSnap2.date) = ?%2$d "
                + "AND day(ComplianceStatusSnap2.date) < ?%3$d" + ") " +

                "GROUP BY " + "ConsumerState2.uuid" + ")" + ") ", ++counter, ++counter, ++counter));

        parameters.add(year);
        parameters.add(month);
        parameters.add(day);
    }

    if (endDate != null) {
        int year = endDate.getYear() + 1900;
        int month = endDate.getMonth() + 1;
        int day = endDate.getDate();

        hql.append(String.format("AND (" + "year(ComplianceStatusSnap.date) < ?%1$d " + "OR ("
                + "year(ComplianceStatusSnap.date) = ?%1$d " + "AND month(ComplianceStatusSnap.date) < ?%2$d "
                + ") " + "OR (" + "year(ComplianceStatusSnap.date) = ?%1$d "
                + "AND month(ComplianceStatusSnap.date) = ?%2$d "
                + "AND day(ComplianceStatusSnap.date) <= ?%3$d" + ")" + ") ", ++counter, ++counter, ++counter));

        parameters.add(year);
        parameters.add(month);
        parameters.add(day);
    }

    // Add our grouping...
    hql.append("ORDER BY ComplianceStatusSnap.date ASC");

    // log.debug("\nFINAL QUERY: {}", hql.toString());

    // Build our query object and set the parameters...
    Query query = session.createQuery(hql.toString());
    query.setReadOnly(true);

    for (int i = 1; i <= counter; ++i) {
        Object param = parameters.remove(0);

        if (param instanceof Collection) {
            query.setParameterList(String.valueOf(i), (Collection) param);
        } else {
            query.setParameter(String.valueOf(i), param);
        }
    }

    return query;
}