Example usage for org.hibernate SQLQuery setParameterList

List of usage examples for org.hibernate SQLQuery setParameterList

Introduction

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

Prototype

@Override
    NativeQuery<T> setParameterList(String name, Object[] values);

Source Link

Usage

From source file:dao.AdDao.java

public LinkedHashMap<String, Integer> getCatsWithCountsBySearch(String wish, List<Long> catIds, Region region,
        List<Long> booleanIds, List<Long> booleanVals, List<Long> stringIds, List<String> stringVals,
        List<BilateralCondition> numVals, List<BilateralCondition> dateVals, List<Long> selIds,
        List<Long> selVals, String multyVals[], Double priceFrom, Double priceTo) {
    String sql = "select c.name,count(sel.ad_id) cc from (select ad.ad_id,ad.category_id from ad where ad.date_from<:now and :now<ad.date_to";
    if (wish == null) {
        wish = "";
    }//from w w w. j av  a2s  . c o  m
    List<String> splitted = splitted(wish);
    if (!splitted.isEmpty()) {
        sql += " and (1!=1";
        for (String st : splitted) {
            sql += " or (ad.name like :wish" + splitted.indexOf(st) + ")";
        }
        for (String st : splitted) {
            sql += " or (ad.description like :wish" + splitted.indexOf(st) + ")";
        }
        sql += ")";
    }

    if (!catIds.isEmpty()) {
        sql += " and (1!=1";
        for (Long id : catIds) {
            sql += " or ad.category_id=:catId" + catIds.indexOf(id);
        }
        sql += ")";
    }

    if (region != null && !region.isAllRussia()) {
        sql += " and ad.ad_id in (select ad_id from ads_at_locals where locality_id in (:localIds))";
    }

    /**
     * ?? ? *
     */

    if (priceFrom != null) {
        sql += " and ad.price>=:priceFrom";
    }
    if (priceTo != null) {
        sql += " and ad.price<=:priceTo";
    }
    Integer paramsCount = 0;
    Boolean queryWithParams = false;
    if ((stringVals != null && !stringVals.isEmpty()) || (booleanVals != null && !booleanVals.isEmpty())
            || (numVals != null && !numVals.isEmpty()) || (dateVals != null && !dateVals.isEmpty())
            || (selVals != null && !selVals.isEmpty()) || (multyVals != null && multyVals.length > 0)) {

        queryWithParams = true;
        sql += " and exists(select 1 from (select count(pv.ad_id) cnt,pv.ad_id id from parametr_value pv where (1!=1)";
        int i = 0;

        if (stringVals != null && !stringVals.isEmpty()) {
            i = 0;
            for (String val : stringVals) {
                sql += " or (parametr_id=:stringId" + i + " and string_value like '%:stringVal" + i + "%')";
                paramsCount++;
                i++;
            }

        }

        if (numVals != null && !numVals.isEmpty()) {
            i = 0;
            for (BilateralCondition c : numVals) {
                Double numFrom = (Double) c.getFrom();
                Double numTo = (Double) c.getTo();
                if (numFrom != null || numTo != null) {

                    if (numFrom != null && numTo != null) {
                        sql += " or (parametr_id=:numId" + i + " and number_value >= :numFrom" + i
                                + " and number_value <= :numTo" + i + ")";
                    } else {
                        if (numFrom != null) {
                            sql += " or (parametr_id=:numId" + i + " and number_value >= :numFrom" + i + ")";
                        }
                        if (numTo != null) {
                            sql += " or (parametr_id=:numId" + i + " and number_value <= :numTo" + i + ")";
                        }
                    }
                    paramsCount++;
                    i++;
                }
            }

        }

        if (dateVals != null && !dateVals.isEmpty()) {
            i = 0;
            for (BilateralCondition c : dateVals) {
                Date dateFrom = (Date) c.getFrom();
                Date dateTo = (Date) c.getTo();
                if (dateFrom != null || dateTo != null) {
                    if (dateFrom != null && dateTo != null) {
                        sql += " or (parametr_id=:dateId" + i + " and date_value >= :dateFrom" + i
                                + " and date_value <= :dateTo" + i + ")";
                    } else {
                        if (dateFrom != null) {
                            sql += " or (parametr_id=:dateId" + i + " and date_value >= :dateFrom" + i + ")";
                        }
                        if (dateTo != null) {
                            sql += " or (parametr_id=:dateId" + i + " and date_value <= :dateTo" + i + ")";
                        }
                    }
                    paramsCount++;
                    i++;
                }
            }
        }

        if (booleanVals != null && !booleanVals.isEmpty()) {
            i = 0;
            for (Long val : booleanVals) {
                if (val != null) {
                    sql += " or (parametr_id=:booleanId" + i + " and select_value=:booleanVal" + i + ")";
                    paramsCount++;
                    //ex+=i+":"+val+"; ";
                }
                i++;
            }
        }

        if (selVals != null && !selVals.isEmpty()) {
            i = 0;
            for (Long val : selVals) {
                if (val != null) {
                    sql += " or (parametr_id=:selId" + i + " and select_value=:selVal" + i + ")";
                    paramsCount++;
                    //ex+=i+":"+val+"; ";
                }
                i++;
            }
        }

        if (multyVals != null && multyVals.length > 0) {

            for (String rawVal : multyVals) {
                String idValArr[] = rawVal.split("_");
                if (idValArr.length == 2) {
                    String strId = idValArr[0];
                    String strVal = idValArr[1];
                    Long paramId = Long.valueOf(strId);
                    Long val = Long.valueOf(strVal);
                    if (val != null) {
                        sql += " or (parametr_id=" + paramId + " and select_value=" + val + ")";
                        paramsCount++;
                    }
                }
            }

        }

        sql += " group by pv.ad_id) as tmp where tmp.cnt=:paramsCount and tmp.id=ad.ad_id)";
    }
    /**
     * \?? ? *
     */
    sql += ") sel left join category c on sel.category_id=c.category_id group by sel.category_id order by cc desc";
    SQLQuery query = getCurrentSession().createSQLQuery(sql);

    if (!splitted.isEmpty()) {
        for (String st : splitted) {
            query.setParameter("wish" + splitted.indexOf(st), st);
        }
    }
    if (!catIds.isEmpty()) {
        for (Long id : catIds) {
            query.setParameter("catId" + catIds.indexOf(id), id);
        }
    }
    if (priceFrom != null) {
        query.setParameter("priceFrom", priceFrom);
    }
    if (priceTo != null) {
        query.setParameter("priceTo", priceTo);
    }
    if (stringVals != null && !stringVals.isEmpty()) {
        int i = 0;
        for (String s : stringVals) {
            query.setParameter("stringId" + i, stringIds.get(i));
            query.setParameter("stringVal" + i, stringVals.get(i));
            i++;
        }
    }
    if (numVals != null && !numVals.isEmpty()) {
        int i = 0;
        for (BilateralCondition c : numVals) {
            Long id = c.getId();
            Double numFrom = (Double) c.getFrom();
            Double numTo = (Double) c.getTo();
            query.setParameter("numId" + i, id);
            if (numFrom != null) {
                query.setParameter("numFrom" + i, numFrom);
            }
            if (numTo != null) {
                query.setParameter("numTo" + i, numTo);
            }
            i++;
        }
    }
    if (dateVals != null && !dateVals.isEmpty()) {
        int i = 0;
        for (BilateralCondition c : dateVals) {
            Long id = c.getId();
            Date dateFrom = (Date) c.getFrom();
            Date dateTo = (Date) c.getTo();
            query.setParameter("dateId" + i, id);
            if (dateFrom != null) {
                query.setParameter("dateFrom" + i, dateFrom);
            }
            if (dateTo != null) {
                query.setParameter("dateTo" + i, dateTo);
            }
            i++;
        }
    }
    if (booleanVals != null && !booleanVals.isEmpty()) {
        int i = 0;
        for (Long v : booleanVals) {
            if (v != null) {
                query.setParameter("booleanId" + i, booleanIds.get(i));
                query.setParameter("booleanVal" + i, v);
            }
            i++;
        }

    }
    if (selVals != null && !selVals.isEmpty()) {
        int i = 0;
        for (Long v : selVals) {
            if (v != null) {
                query.setParameter("selId" + i, selIds.get(i));
                query.setParameter("selVal" + i, v);
            }
            i++;
        }

    }
    if (queryWithParams) {
        query.setParameter("paramsCount", paramsCount);
    }
    query.setParameter("now", new Date());
    if (region != null && !region.isAllRussia()) {
        query.setParameterList("localIds", getLocIds(region));
    }
    List<Object[]> rawRes = query.list();
    LinkedHashMap<String, Integer> res = new LinkedHashMap();
    if (!rawRes.isEmpty()) {
        for (Object[] o : rawRes) {
            String catName = (String) o[0];
            Integer count = ((BigInteger) o[1]).intValue();
            if (0 != count) {
                res.put(catName, count);
            }
        }
    }
    return res;
}

From source file:dao.AdDao.java

public List<Ad> getAdsByWishInNameOrDescription(String wish, List<Long> catIds, Region region, String order,
        List<Long> booleanIds, List<Long> booleanVals, List<Long> stringIds, List<String> stringVals,
        List<BilateralCondition> numVals, List<BilateralCondition> dateVals, List<Long> selIds,
        List<Long> selVals, String multyVals[], Double priceFrom, Double priceTo) throws Exception {
    if (order == null) {
        order = "show_count desc";
    }/*from   w ww . ja va 2 s.co  m*/
    String sql = "select * from ad where date_from<:now and :now<date_to";
    if (wish == null) {
        wish = "";
    }
    List<String> splitted = splitted(wish);
    if (!splitted.isEmpty()) {
        sql += " and (1!=1";
        for (String st : splitted) {
            sql += " or (name like :wish" + splitted.indexOf(st) + ")";
        }
        for (String st : splitted) {
            sql += " or (description like :wish" + splitted.indexOf(st) + ")";
        }
        sql += ")";
    }

    if (!catIds.isEmpty()) {
        sql += " and (1!=1";
        for (Long id : catIds) {
            sql += " or category_id=:catId" + catIds.indexOf(id);
        }
        sql += ")";
    }

    if (region != null && !region.isAllRussia()) {
        sql += " and ad_id in (select ad_id from ads_at_locals where locality_id in (:localIds))";
    }

    /**
     * ?? ? *
     */

    if (priceFrom != null) {
        sql += " and price>=:priceFrom";
    }
    if (priceTo != null) {
        sql += " and price<=:priceTo";
    }
    Integer paramsCount = 0;
    Boolean queryWithParams = false;
    if ((stringVals != null && !stringVals.isEmpty()) || (booleanVals != null && !booleanVals.isEmpty())
            || (numVals != null && !numVals.isEmpty()) || (dateVals != null && !dateVals.isEmpty())
            || (selVals != null && !selVals.isEmpty()) || (multyVals != null && multyVals.length > 0)) {

        queryWithParams = true;
        sql += " and exists(select 1 from (select count(pv.ad_id) cnt,pv.ad_id id from parametr_value pv where (1!=1)";
        int i = 0;

        if (stringVals != null && !stringVals.isEmpty()) {
            i = 0;
            for (String val : stringVals) {
                sql += " or (parametr_id=:stringId" + i + " and string_value like '%:stringVal" + i + "%')";
                paramsCount++;
                i++;
            }

        }

        if (numVals != null && !numVals.isEmpty()) {
            i = 0;
            for (BilateralCondition c : numVals) {
                Double numFrom = (Double) c.getFrom();
                Double numTo = (Double) c.getTo();
                if (numFrom != null || numTo != null) {

                    if (numFrom != null && numTo != null) {
                        sql += " or (parametr_id=:numId" + i + " and number_value >= :numFrom" + i
                                + " and number_value <= :numTo" + i + ")";
                    } else {
                        if (numFrom != null) {
                            sql += " or (parametr_id=:numId" + i + " and number_value >= :numFrom" + i + ")";
                        }
                        if (numTo != null) {
                            sql += " or (parametr_id=:numId" + i + " and number_value <= :numTo" + i + ")";
                        }
                    }
                    paramsCount++;
                    i++;
                }
            }

        }

        if (dateVals != null && !dateVals.isEmpty()) {
            i = 0;
            for (BilateralCondition c : dateVals) {
                Date dateFrom = (Date) c.getFrom();
                Date dateTo = (Date) c.getTo();
                if (dateFrom != null || dateTo != null) {
                    if (dateFrom != null && dateTo != null) {
                        sql += " or (parametr_id=:dateId" + i + " and date_value >= :dateFrom" + i
                                + " and date_value <= :dateTo" + i + ")";
                    } else {
                        if (dateFrom != null) {
                            sql += " or (parametr_id=:dateId" + i + " and date_value >= :dateFrom" + i + ")";
                        }
                        if (dateTo != null) {
                            sql += " or (parametr_id=:dateId" + i + " and date_value <= :dateTo" + i + ")";
                        }
                    }
                    paramsCount++;
                    i++;
                }
            }
        }

        /*if (booleanVals != null && booleanVals.length > 0) {
        i = 0;
        while (i < booleanIds.length) {
            Long paramId = booleanIds[i];
            Long val = booleanVals[i];
            if (val != null) {
                sql += " or (parametr_id=" + paramId + " and select_value=" + val + ")";
                paramsCount++;
            }
            i++;
        }
        }
                
        if (selVals != null && selVals.length > 0) {
        i = 0;
        while (i < selIds.length) {
            Long paramId = selIds[i];
            Long val = selVals[i];
            if (val != null) {
                sql += " or (parametr_id=" + paramId + " and select_value=" + val + ")";
                paramsCount++;
            }
            i++;
        }
        }*/

        if (booleanVals != null && !booleanVals.isEmpty()) {
            i = 0;
            for (Long val : booleanVals) {
                if (val != null) {
                    sql += " or (parametr_id=:booleanId" + i + " and select_value=:booleanVal" + i + ")";
                    paramsCount++;
                    //ex+=i+":"+val+"; ";
                }
                i++;
            }
        }

        if (selVals != null && !selVals.isEmpty()) {
            i = 0;
            for (Long val : selVals) {
                if (val != null) {
                    sql += " or (parametr_id=:selId" + i + " and select_value=:selVal" + i + ")";
                    paramsCount++;
                    //ex+=i+":"+val+"; ";
                }
                i++;
            }
        }

        if (multyVals != null && multyVals.length > 0) {

            for (String rawVal : multyVals) {
                String idValArr[] = rawVal.split("_");
                if (idValArr.length == 2) {
                    String strId = idValArr[0];
                    String strVal = idValArr[1];
                    Long paramId = Long.valueOf(strId);
                    Long val = Long.valueOf(strVal);
                    if (val != null) {
                        sql += " or (parametr_id=" + paramId + " and select_value=" + val + ")";
                        paramsCount++;
                    }
                }
            }

        }

        sql += " group by pv.ad_id) as tmp where tmp.cnt=:paramsCount and tmp.id=ad.ad_id)";
    }
    /**
     * \?? ? *
     */
    sql += " order by status asc," + order;

    SQLQuery query = getCurrentSession().createSQLQuery(sql);

    if (!splitted.isEmpty()) {
        for (String st : splitted) {
            query.setParameter("wish" + splitted.indexOf(st), st);
        }
    }
    if (!catIds.isEmpty()) {
        for (Long id : catIds) {
            query.setParameter("catId" + catIds.indexOf(id), id);
        }
    }
    if (priceFrom != null) {
        query.setParameter("priceFrom", priceFrom);
    }
    if (priceTo != null) {
        query.setParameter("priceTo", priceTo);
    }
    if (stringVals != null && !stringVals.isEmpty()) {
        int i = 0;
        for (String s : stringVals) {
            query.setParameter("stringId" + i, stringIds.get(i));
            query.setParameter("stringVal" + i, stringVals.get(i));
            i++;
        }
    }
    if (booleanVals != null && !booleanVals.isEmpty()) {
        int i = 0;
        for (Long v : booleanVals) {
            if (v != null) {
                query.setParameter("booleanId" + i, booleanIds.get(i));
                query.setParameter("booleanVal" + i, v);
            }
            i++;
        }

    }
    if (selVals != null && !selVals.isEmpty()) {
        int i = 0;
        for (Long v : selVals) {
            if (v != null) {
                query.setParameter("selId" + i, selIds.get(i));
                query.setParameter("selVal" + i, v);
            }
            i++;
        }

    }
    if (numVals != null && !numVals.isEmpty()) {
        int i = 0;
        for (BilateralCondition c : numVals) {
            Long id = c.getId();
            Double numFrom = (Double) c.getFrom();
            Double numTo = (Double) c.getTo();
            query.setParameter("numId" + i, id);
            if (numFrom != null) {
                query.setParameter("numFrom" + i, numFrom);
            }
            if (numTo != null) {
                query.setParameter("numTo" + i, numTo);
            }
            i++;
        }
    }
    if (dateVals != null && !dateVals.isEmpty()) {
        int i = 0;
        for (BilateralCondition c : dateVals) {
            Long id = c.getId();
            Date dateFrom = (Date) c.getFrom();
            Date dateTo = (Date) c.getTo();
            query.setParameter("dateId" + i, id);
            if (dateFrom != null) {
                query.setParameter("dateFrom" + i, dateFrom);
            }
            if (dateTo != null) {
                query.setParameter("dateTo" + i, dateTo);
            }
            i++;
        }
    }
    if (queryWithParams) {
        query.setParameter("paramsCount", paramsCount);
    }
    query.setParameter("now", new Date());
    if (region != null && !region.isAllRussia()) {
        query.setParameterList("localIds", getLocIds(region));
    }
    query.addEntity(Ad.class);
    if (1 == 1) {
        //throw new Exception("e="+ex+"; bv:"+booleanVals.size()+"; sv:"+selVals.size()+"; pc:"+paramsCount);
    }
    //throw new Exception(sql);

    return query.list();
}

From source file:dao.ParametrDao.java

public List<Object[]> getParamIdsAndCountsByCatIds(List<Long> catIds) {
    String sql = "select parametr_id,count(parametr_id) c from param_category_link where category_id in (:catIds) group by parametr_id order by c desc,FIELD(param_type, 2,6,1,5,3,4)";
    SQLQuery query = getCurrentSession().createSQLQuery(sql);
    query.setParameterList("catIds", catIds);
    return query.list();
}

From source file:edu.uiowa.icts.bluebutton.dao.LabTestHome.java

License:Apache License

private List<LoincCode> labTestQuery(String sex, Double age, String loincCodeCsvList) {
    List<LoincCode> list = new ArrayList<LoincCode>();
    if (sex != null && sex.length() > 0 && age != null) {
        sex = sex.substring(0, 1).toUpperCase();

        String sql = "select loinc_code as \"loinc_code\", min_normal as \"min_normal\", max_normal as \"max_normal\""
                + "   from bluebutton.lab_test lt, bluebutton.lab_test_range ltr "
                + "   where lt.lab_test_id = ltr.lab_test_id " + "   and ltr.sex like :sex "
                + "   and :age >= ltr.min_age_years  " + "   and :age < ltr.max_age_years "
                + "   and loinc_code IS NOT NULL ";
        if (loincCodeCsvList != null) {
            sql += "and loinc_code in (:loincCodeCsvList) ";
        }/*from w  w w . j a va  2 s.c o  m*/
        SQLQuery query = this.sessionFactory.getCurrentSession().createSQLQuery(sql);
        query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
        query.setString("sex", "%" + sex + "%");
        query.setDouble("age", age);
        if (loincCodeCsvList != null) {
            query.setParameterList("loincCodeCsvList", loincCodeCsvList.split(","));
        }
        List data = query.list();

        for (Object object : data) {
            Map row = (Map) object;
            LoincCode lc = new LoincCode(row.get("loinc_code").toString(),
                    new Double((double) row.get("min_normal")), new Double((double) row.get("max_normal")));
            list.add(lc);
        }
    }
    return list;
}

From source file:gov.nih.nci.caarray.dao.AuditLogDaoImpl.java

License:BSD License

private SQLQuery buildQuery(AuditLogSearchCriteria criteria, StringBuffer sb) {
    SQLQuery q = getCurrentSession().createSQLQuery(sb.toString());
    q.setParameterList("GROUP_NAMES", getGroupNames());
    if (StringUtils.isNotBlank(criteria.getUsername())) {
        q.setParameter("username", criteria.getUsername());
    }/*w  ww  .j a  v  a  2  s  . c o m*/
    if (StringUtils.isNotBlank(criteria.getMessage())) {
        q.setParameter("message", "%" + criteria.getMessage().toLowerCase() + "%");
    }
    return q;
}

From source file:gov.nih.nci.caarray.security.SecurityUtils.java

License:BSD License

private static Map<Long, Privileges> getPermissionsWithMappingTable(String groupTableName, String userName,
        Collection<Long> protectableIds) {
    final String sql = " select distinct pe.attribute_value, p.privilege_name from " + groupTableName + " pe "
            + "inner join csm_user_group ug on pe.group_id = ug.group_id "
            + "inner join csm_privilege p on pe.privilege_id = p.privilege_id "
            + "inner join csm_user u on ug.user_id = u.user_id "
            + "where pe.attribute_value in (:attr_values) and u.login_name = :login_name "
            + "order by pe.attribute_value, p.privilege_name";
    final SQLQuery query = hibernateHelper.getCurrentSession().createSQLQuery(sql);
    query.setParameterList("attr_values", protectableIds);
    query.setString("login_name", userName);

    @SuppressWarnings("unchecked")
    final List<Object[]> results = query.list();
    return createPrivilegesMapFromResults(results);
}

From source file:gov.nih.nci.caarray.security.SecurityUtils.java

License:BSD License

private static Map<Long, Privileges> getPermissionsWithCanonicalTable(String userName, String className,
        String attributeName, Collection<Long> protectableIds, Application application) {
    final String sql = " select distinct cast(pe.attribute_value as unsigned), "
            + "p.privilege_name from csm_protection_element pe "
            + "inner join csm_pg_pe pgpe on pe.protection_element_id = pgpe.protection_element_id "
            + "inner join csm_user_group_role_pg ugrpg "
            + "on pgpe.protection_group_id = ugrpg.protection_group_id "
            + "inner join csm_role r on ugrpg.role_id = r.role_id "
            + "inner join csm_user_group ug on ugrpg.group_id = ug.group_id "
            + "inner join csm_role_privilege rp on r.role_id = rp.role_id "
            + "inner join csm_privilege p on rp.privilege_id = p.privilege_id "
            + "inner join csm_user u on ug.user_id = u.user_id "
            + "where pe.object_id = :class_name and pe.attribute = :attr_name "
            + "and pe.attribute_value in (:attr_values) and u.login_name = :login_name "
            + "and pe.application_id = :app_id order by pe.attribute_value, p.privilege_name";
    final SQLQuery query = hibernateHelper.getCurrentSession().createSQLQuery(sql);
    query.setParameterList("attr_values", protectableIds);
    query.setString("login_name", userName);
    query.setString("class_name", className);
    query.setString("attr_name", attributeName);
    query.setLong("app_id", application.getApplicationId());

    @SuppressWarnings("unchecked")
    final List<Object[]> results = query.list();
    return createPrivilegesMapFromResults(results);
}

From source file:gov.nih.nci.cabig.caaers.dao.CaaersDao.java

License:BSD License

/**
 * A paginated query. /*from w  w  w .jav  a 2s .  co m*/
 * @param query - The query to be executed. 
 * @param firstResult - The starting index
 * @param maxResults - The number of objects to be returned. 
 * @return - A list of objects returned by the query, matching the pagination criteria. 
 */
@SuppressWarnings("unchecked")
public List<?> search(final AbstractQuery query, final Integer firstResult, final Integer maxResults) {
    String queryString = query.getQueryString();
    if (log.isDebugEnabled())
        log.debug("::: " + queryString);
    return (List<?>) getHibernateTemplate().execute(new HibernateCallback() {

        public Object doInHibernate(final Session session) throws HibernateException, SQLException {
            if (query instanceof NativeSQLQuery) {
                org.hibernate.SQLQuery nativeQuery = session.createSQLQuery(query.getQueryString());
                setResultSetBoundaries(nativeQuery, firstResult, maxResults);
                Map<String, Type> scalarMap = ((NativeSQLQuery) query).getScalarMap();
                for (String key : scalarMap.keySet()) {
                    nativeQuery.addScalar(key, scalarMap.get(key));
                }
                Map<String, Object> queryParameterMap = query.getParameterMap();
                for (String key : queryParameterMap.keySet()) {
                    Object value = queryParameterMap.get(key);
                    if (value instanceof Collection) {
                        nativeQuery.setParameterList(key, (Collection) value);
                    } else {
                        nativeQuery.setParameter(key, value);
                    }
                }
                return nativeQuery.list();
            } else {
                org.hibernate.Query hibernateQuery = session.createQuery(query.getQueryString());
                setResultSetBoundaries(hibernateQuery, firstResult, maxResults);
                Map<String, Object> queryParameterMap = query.getParameterMap();
                for (String key : queryParameterMap.keySet()) {
                    Object value = queryParameterMap.get(key);
                    if (value instanceof Collection) {
                        hibernateQuery.setParameterList(key, (Collection) value);
                    } else {
                        hibernateQuery.setParameter(key, value);
                    }

                }
                return hibernateQuery.list();
            }
        }

    });
}

From source file:ispyb.server.common.services.shipping.external.External3ServiceBean.java

License:Open Source License

@Override
public List<Map<String, Object>> getAutoprocResultByDataCollectionIdList(
        ArrayList<Integer> dataCollectionIdList) {

    String mySQLQuery = this.getAutoprocResultByDataCollectionIdListQuery();
    Session session = (Session) this.entityManager.getDelegate();
    SQLQuery query = session.createSQLQuery(mySQLQuery);
    query.setParameterList("dataCollectionIdList", dataCollectionIdList);
    query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
    return executeSQLQuery(query);
}

From source file:models.BeginningBalance.java

public Double getSumBalance(String year, String accountNo, String calc) {
    Session session = DatabaseUtil.getSessionFactory().openSession();
    Transaction tx = null;//from  w ww.  j  av a 2 s. c  o m
    Double total = null;
    accountNo = accountNo.trim();
    String[] args;
    String calcSelect = "Debet - Credit".equals(calc) ? "debet - credit" : "credit - debet";
    try {
        tx = session.beginTransaction();
        if (accountNo.contains("to")) {
            args = Formula.args(accountNo.trim(), "to");
            String sql = "select sum(" + calcSelect + ") from beginning_balances bb "
                    + " where bb.year = :year and (bb.account_no >= :arg1 and bb.account_no<= :arg2) ";
            SQLQuery query = session.createSQLQuery(sql);
            query.setParameter("year", year);
            query.setParameter("arg1", args[0]);
            query.setParameter("arg2", args[1]);
            total = (Double) query.uniqueResult();
        } else {
            args = Formula.args(accountNo.trim(), "\\,");
            String sql = "select sum(" + calcSelect + ") from beginning_balances bb "
                    + " where bb.year = :year and bb.account_no in(:no) ";
            SQLQuery query = session.createSQLQuery(sql);
            query.setParameter("year", year);
            query.setParameterList("no", args);
            total = (Double) query.uniqueResult();
        }
        session.flush();
        tx.commit();
    } catch (HibernateException e) {
        System.out.println("Error :" + e.getMessage());
        if (tx != null) {
            tx.rollback();
        }
    } finally {
        session.close();
    }

    if (total == null) {
        total = 0.00;
    }
    return total;
}