List of usage examples for org.hibernate Query setParameterList
Query<R> setParameterList(int position, Object[] values);
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; }