Example usage for org.hibernate Query setResultTransformer

Introduction

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

Prototype

@Deprecated
Query<R> setResultTransformer(ResultTransformer transformer);

Source Link

Document

Set a strategy for handling the query results.

Usage

From source file:com.scopix.periscope.queuemanagement.dao.QueueManagementHibernateDAO.java

License:Open Source License

/**
 *
 * @param quantity//w w w .jav a 2 s.co  m
 * @param queueNameId
 * @return
 * @throws PeriscopeException
 */
public List<PendingEvaluationDTO> getNFirstElementOfQueue(int quantity, Integer queueNameId)
        throws ScopixException {
    log.info("start");
    List<PendingEvaluationDTO> pendingEvaluationDTOs = new ArrayList<PendingEvaluationDTO>();
    Session session = null;
    try {
        StringBuilder sql = new StringBuilder();

        sql.append("SELECT DISTINCT ");
        sql.append("st.name AS situationTemplate, ");
        sql.append("a.description AS area, ");
        sql.append("prod.description AS product, ");
        sql.append("pe.priority AS priority, ");
        sql.append("min(e.evidence_date) AS evidenceDate, ");
        sql.append("store.description AS store ");
        sql.append("FROM ");
        sql.append("pending_evaluation pe, ");
        sql.append("evidence e, ");
        sql.append("observed_situation os, ");
        sql.append("situation s, ");
        sql.append("metric m, ");
        sql.append("observed_metric om, ");
        sql.append("place a, ");
        sql.append("place store, ");
        sql.append("situation_template st, ");
        sql.append("product prod, ");
        sql.append("rel_observed_metric_evidence rel ");
        sql.append("WHERE ");
        sql.append("pe.evaluation_queue = 'OPERATOR' ");
        sql.append("AND pe.evaluation_state = 'ENQUEUED' ");
        sql.append("AND os.id = pe.observed_situation_id ");
        sql.append("and os.id = om.observed_situation_id ");
        sql.append("AND s.id = os.situation_id ");
        sql.append("AND s.id = m.situation_id ");
        sql.append("AND a.id = m.area_id ");
        sql.append("AND st.id = s.situation_template_id ");
        sql.append("AND store.id = m.store_id ");
        sql.append("AND m.id = om.metric_id ");
        sql.append("AND om.id = rel.observed_metric_id ");
        sql.append("AND e.id = rel.evidence_id ");
        sql.append("AND prod.id = st.product_id ");
        if (queueNameId != null && (queueNameId > 0 || queueNameId == -1)) {
            sql.append("AND pe.operator_queue_id = ").append(queueNameId).append(" ");
        } else {
            sql.append("AND pe.operator_queue_id is null ");
        }
        sql.append(
                "group by pe.id, st.name, st.id, a.description, a.id, prod.description, os.id, pe.evaluation_state, ");
        sql.append("pe.evaluation_queue, pe.priority, store.description, store.id ");
        sql.append("ORDER BY pe.priority ASC LIMIT ").append(quantity);

        session = this.getSession();

        Query query = session.createSQLQuery(sql.toString());
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        List<Map<String, Object>> list = query.list();

        int id = 0;
        PendingEvaluationDTO dto = null;
        for (Map m : list) {
            dto = new PendingEvaluationDTO();
            dto.setPendingEvaluationId(id);
            dto.setSituationTemplate((String) m.get("situationtemplate"));
            dto.setProduct((String) m.get("product"));
            dto.setArea((String) m.get("area"));
            dto.setPriority((Integer) m.get("priority"));
            dto.setEvidenceDate((Date) m.get("evidencedate"));
            dto.setDate(dto.getEvidenceDate());
            dto.setStore((String) m.get("store"));

            pendingEvaluationDTOs.add(dto);
        }

    } catch (Exception e) {
        log.error("Error " + e.getMessage(), e);
        //"periscopeexception.list.error", new String[]{
        throw new ScopixException("tab.queueManagement.pending");
    } finally {
        try {
            this.releaseSession(session);
        } catch (Exception e) {
            log.error("Error " + e.getMessage(), e);
        }
    }
    log.info("end, result = " + pendingEvaluationDTOs);
    return pendingEvaluationDTOs;
}

From source file:com.scopix.periscope.queuemanagement.dao.QueueManagementHibernateDAO.java

License:Open Source License

/**
 *
 * @param queueName/*from w ww. ja  v a 2 s. c  o  m*/
 * @return
 */
public int getFirstElementOfQueueId(String queueName) {
    log.info("start [queueName:" + queueName + "]");

    Integer peId = null;
    Session session = null;

    try {
        StringBuilder sql = new StringBuilder();
        //            sql.append("select pe.id, pe.priority ");
        //            sql.append("from pending_evaluation pe ");
        //            sql.append("where ");
        //            sql.append("pe.evaluation_queue = 'OPERATOR' ");
        //            sql.append("and pe.evaluation_state = 'ENQUEUED' ");
        //            if (queueName != null && queueName.length() > 0) {
        //                sql.append("and pe.operator_queue_id = (select id from operator_queue op where op.activo = true ");
        //                sql.append("and op.name = '").append(queueName).append("') ");
        //            } else {
        //                sql.append("and pe.operator_queue_id is null ");
        //            }
        //            sql.append("order by pe.priority ");
        //            sql.append("limit 1 ");
        boolean forQueue = queueName != null && queueName.length() > 0;
        sql.append("select pe.id, pe.priority, os.evidence_date ");
        sql.append("from pending_evaluation pe, observed_situation os, situation s, ");
        sql.append("situation_template st, observed_metric om, metric m, place store ");
        if (forQueue) {
            //add to table
            sql.append(", operator_queue oq ");
        }
        sql.append("where ");
        sql.append("pe.evaluation_queue = 'OPERATOR' ");
        sql.append("and pe.evaluation_state = 'ENQUEUED' ");
        if (forQueue) {
            //                sql.append("and pe.operator_queue_id = (select id from operator_queue op where op.activo = true ");
            //                sql.append("and op.name = '").append(queueName).append("') ");
            sql.append("and pe.operator_queue_id = oq.id ");
            sql.append("and oq.activo = true ");
            sql.append("and oq.name = '").append(queueName).append("' ");
        } else {
            sql.append("and pe.operator_queue_id is null ");
        }
        sql.append("and os.id = pe.observed_situation_id ");
        /**
         * add where for return pending evaluation not expired in situation live
         */
        sql.append("and s.id = os.situation_id ");
        sql.append("and st.id = s.situation_template_id ");
        sql.append("and om.observed_situation_id = os.id ");
        sql.append("and m.id = om.metric_id ");
        sql.append("and store.id = m.store_id ");
        sql.append("and  ");
        sql.append("   case  ");
        sql.append("      when (st.live = true) then ");
        sql.append(
                "      os.evidence_date >= ( cast((now() AT TIME ZONE store.time_zone_id) as timestamp without time zone) - interval '1 minute' * st.delay_in_minutes) ");
        sql.append("      else true ");
        sql.append("      end ");
        sql.append("order by pe.priority , os.evidence_date desc ");
        sql.append("limit 1");

        session = this.getSession();

        Query query = session.createSQLQuery(sql.toString());
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        List<Map<String, Object>> list = query.list();

        if (list == null || list.isEmpty()) {
            peId = 0;
        } else {
            peId = ((Integer) ((Map) list.get(0)).get("id"));
        }
        log.debug("peId: " + peId);
    } catch (DataAccessResourceFailureException e) {
        log.error("Error " + e.getMessage(), e);
    } catch (IllegalStateException e) {
        log.error("Error " + e.getMessage(), e);
    } catch (HibernateException e) {
        log.error("Error " + e.getMessage(), e);
    } finally {
        try {
            this.releaseSession(session);
        } catch (Exception e) {
            log.error("Error " + e.getMessage(), e);
        }
    }
    log.info("end [result:" + peId + "]");
    return peId;
}

From source file:com.scopix.periscope.queuemanagement.dao.QueueManagementHibernateDAO.java

License:Open Source License

/**
 *
 * @return @throws PeriscopeException//ww  w . java 2s .c  o m
 */
public List<OperatorQueueDTO> getOperatorQueues() throws ScopixException {
    log.info("start");
    List<OperatorQueueDTO> operatorQueueDTOs = new ArrayList<OperatorQueueDTO>();
    Session session = null;

    try {
        String sql = "SELECT DISTINCT name, id FROM operator_queue where activo = true ";

        session = this.getSession();

        Query query = session.createSQLQuery(sql.toString());
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        List<Map<String, Object>> list = query.list();

        OperatorQueueDTO dto = null;
        for (Map m : list) {
            dto = new OperatorQueueDTO();
            dto.setId((Integer) m.get("id"));
            dto.setName((String) m.get("name"));

            operatorQueueDTOs.add(dto);
        }
    } catch (Exception e) {
        log.error("Error " + e.getMessage(), e);
    } finally {
        try {
            this.releaseSession(session);
        } catch (Exception e) {
            log.error("Error " + e.getMessage(), e);
        }
    }
    log.info("end, result = " + operatorQueueDTOs);
    return operatorQueueDTOs;
}

From source file:com.scopix.periscope.queuemanagement.dao.QueueManagementHibernateDAO.java

License:Open Source License

/**
 * Looks on the database for available evidences to be processed the result is mapped on map and each key will be equivalent
 * to the queue name and the value is a Integer representing the total of evidences available to be processed for that
 * specific queue/*  ww w  . ja  va2  s  .  c  o  m*/
 *
 * @return Map<String, Integer> ex DSO,9
 * @throws PeriscopeException
 */
public Map<String, Integer> countAvailableEvidencesByQueue() throws ScopixException {
    log.info("start countAvailableEvidencesByQueue()");

    Session session = null;
    Map<String, Integer> resultMap = new HashMap<String, Integer>();

    try {
        StringBuilder sql = new StringBuilder();
        sql.append("select count(1) as total, data.name as queuename ");
        sql.append(" from ");
        sql.append("(");
        sql.append("  select distinct pe.id, oq.name ");
        sql.append("   from operator_queue oq, pending_evaluation pe, observed_situation os, ");
        sql.append("   situation s, situation_template st, observed_metric om, metric m, place store ");
        sql.append("   where ");
        sql.append("   oq.activo = true ");
        sql.append("   and pe.operator_queue_id = oq.id ");
        sql.append("   and pe.evaluation_state ='ENQUEUED' ");
        sql.append("   and pe.evaluation_queue = 'OPERATOR' ");
        sql.append("   and os.id = pe.observed_situation_id ");
        sql.append("   and s.id = os.situation_id ");
        sql.append("   and st.id = s.situation_template_id ");
        sql.append("   and st.active = true   ");
        sql.append("   and om.observed_situation_id = os.id ");
        sql.append("   and m.id = om.metric_id ");
        sql.append("   and store.id = m.store_id ");
        sql.append("   and st.live = true ");
        sql.append(
                "   and os.evidence_date >= (cast((now() AT TIME ZONE store.time_zone_id) as timestamp without time zone) - interval '1 minute' * st.delay_in_minutes) ");
        sql.append(") as data ");
        sql.append(" group by data.name ");
        sql.append(" union all");
        sql.append("  select count(1) as total, oq.name as queuename ");
        sql.append("   from operator_queue oq, pending_evaluation pe, observed_situation os, ");
        sql.append("   situation s, situation_template st");
        sql.append("   where ");
        sql.append("   oq.activo = true ");
        sql.append("   and pe.operator_queue_id = oq.id ");
        sql.append("   and pe.evaluation_state ='ENQUEUED' ");
        sql.append("   and pe.evaluation_queue = 'OPERATOR' ");
        sql.append("   and os.id = pe.observed_situation_id ");
        sql.append("   and s.id = os.situation_id ");
        sql.append("   and st.id = s.situation_template_id ");
        sql.append("   and st.active = true ");
        sql.append("   and st.live = false");
        sql.append(" group by oq.name");

        //            sql.append("select count(1) as total, data.name as queuename ");
        //            sql.append(" from ");
        //            sql.append("(");
        //            sql.append("  select distinct pe.id, oq.name ");
        //            sql.append("   from operator_queue oq, pending_evaluation pe, observed_situation os, ");
        //            sql.append("   situation s, situation_template st, observed_metric om, metric m, place store ");
        //            sql.append("   where ");
        //            sql.append("   oq.activo = true ");
        //            sql.append("   and pe.operator_queue_id = oq.id ");
        //            sql.append("   and pe.evaluation_state ='ENQUEUED' ");
        //            sql.append("   and pe.evaluation_queue = 'OPERATOR' ");
        //            sql.append("   and os.id = pe.observed_situation_id ");
        //            sql.append("   and s.id = os.situation_id ");
        //            sql.append("   and st.id = s.situation_template_id ");
        //            sql.append("   and om.observed_situation_id = os.id ");
        //            sql.append("   and m.id = om.metric_id ");
        //            sql.append("   and store.id = m.store_id ");
        //            sql.append("  and ");
        //            sql.append("    case ");
        //            sql.append("      when (st.live = true) then ");
        //            sql.append("      os.evidence_date >= (cast((now() AT TIME ZONE store.time_zone_id) as timestamp without time zone) - interval '1 minute' * st.delay_in_minutes) ");
        //            sql.append("      else true ");
        //            sql.append("     end ");
        //            sql.append(") as data ");
        //            sql.append(" group by data.name");
        //            sql.append("select count(1) as total, oq.name as queuename ");
        //            sql.append("from operator_queue oq, pending_evaluation pe ");
        //            sql.append("where ");
        //            sql.append("oq.activo = true ");
        //            sql.append("and pe.operator_queue_id = oq.id ");
        //            sql.append("and pe.evaluation_state ='ENQUEUED' ");
        //            sql.append("and pe.evaluation_queue = 'OPERATOR' ");
        //            sql.append("group by oq.name ");
        session = this.getSession();

        Query query = session.createSQLQuery(sql.toString());
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        List<Map<String, Object>> queueCountlist = query.list();

        if (queueCountlist != null && !queueCountlist.isEmpty()) {
            for (Map<String, Object> queueCount : queueCountlist) {
                resultMap.put((String) queueCount.get("queuename"),
                        ((BigInteger) queueCount.get("total")).intValue());
            }
        }
        log.debug("results processed");
    } catch (Exception e) {
        log.error("Error " + e.getMessage(), e);
        throw new ScopixException(e);
    } finally {
        try {
            this.releaseSession(session);
        } catch (Exception e) {
            log.error("Error " + e.getMessage(), e);
        }
    }
    log.info("end [results retrieved from database:" + resultMap.size() + "]");
    return resultMap;
}

From source file:com.scopix.periscope.subscription.dao.SubscriptionDAOImpl.java

License:Open Source License

@Override
public List<Subscription> getForUserNameSortedByCorporateIdAsc(String userName) {
    log.info("executing getAllByUserNameAndCorporateIdAsc()");
    StringBuilder sql = new StringBuilder();
    sql.append(//  www  . j ava 2 s .  c  o  m
            "select DISTINCT sub.id as id, sub.operatorsGroupId as operatorsGroupId, sub.operatorQueuePriority as operatorQueuePriority");
    sql.append(" from Subscription sub, OperatorsGroup og join og.users user ");
    sql.append(" where ((sub.userName = '");
    sql.append(userName);
    sql.append("'");
    sql.append(")or ( sub.operatorsGroupId = og.id and user.userName ='");
    sql.append(userName);
    sql.append("'))");
    sql.append(" order by sub.operatorQueuePriority.priority asc");
    Session s = this.getSession();
    log.info("end()");
    Query query = s.createQuery(sql.toString());
    List<Subscription> sub = query.setResultTransformer(Transformers.aliasToBean(Subscription.class)).list();
    return sub;
}

From source file:com.scopix.periscope.subscription.dao.SubscriptionDAOImpl.java

License:Open Source License

@Override
public List<Subscription> getAllByGroupNameAndCorporateIdAsc(String groupName) {
    StringBuilder sql = new StringBuilder();
    sql.append(//from  w  w w.j  av a  2s.  c  o  m
            "select sub.id as id, sub.operatorsGroupId as operatorsGroupId, sub.operatorQueuePriority as operatorQueuePriority");
    sql.append(" from Subscription sub, OperatorsGroup og ");
    sql.append(" where sub.operatorsGroupId =og.id and og.groupName = '");
    sql.append(groupName);
    sql.append("'");
    sql.append(" order by sub.operatorQueuePriority.priority asc, og.groupName asc");
    Session s = this.getSession();
    log.info("end()");
    Query query = s.createQuery(sql.toString());
    List<Subscription> sub = query.setResultTransformer(Transformers.aliasToBean(Subscription.class)).list();
    return sub;
}

From source file:com.stok.ramazan.dao.LisansDao.java

public List<Lisans> getLisansListbyFirmID(Long firmOid) {
    String hql = "select lisans.licenseKey as licenseKey "
            + " lisans.licenseFinishDate as licenseFinishDate , lisans.licenseStartDate as licenseStartDate "
            + " from Lisans as lisans \n" + " inner join lisans.firma as firma  \n"
            + "where lisans.entityState= :state and firma.entityState =:state \n"
            + " and lisans.licenseStartDate> :currentDate \n" + " and lisans.licenseFinishDate< :currentDate \n"
            + " and firma.oid = :firmOid ";
    Query query = currentSession().createQuery(hql);
    query.setParameter("currentDate", new Date());
    query.setParameter("state", EnumUtil.EntityState.ACTIVE);
    query.setParameter("firmOid", firmOid);
    query.setResultTransformer(Transformers.aliasToBean(Lisans.class));
    return query.list();
}

From source file:com.unistrong.tracker.dao.DeviceDao.java

@SuppressWarnings({ "rawtypes", "unchecked" })
public List<Map> findDevicesCount() {
    Query query = getCurrentSession().createSQLQuery(
            "SELECT ifnull(t.f_service_id,0) as sid, COUNT(DISTINCT t.f_sn) as count FROM us_device t group by t.f_service_id ");

    query.setResultTransformer(new ResultTransformer() {

        /**/*from  w  w w  . j ava 2  s.c  om*/
            * 
            */
        private static final long serialVersionUID = -1522516056075610048L;

        @Override
        public Object transformTuple(Object[] values, String[] columns) {
            Map<String, Object> map = new LinkedHashMap<String, Object>(1);
            int i = 0;
            for (String column : columns) {
                map.put(column, values[i++]);
            }
            return map;
        }

        @Override
        public List transformList(List list) {
            return list;
        }
    });

    return query.list();
}

From source file:com.zl.bgec.basicapi.shop.service.impl.ShopServiceImpl.java

@Override
@Transactional(readOnly = true)/* w  w w.j  av  a  2s  . c o  m*/
public Map<String, Object> getShopIndexInfo(String memberNo) throws Exception {
    String sql = "select tsi.shop_name shopName," + "tsi.shop_address shopAddress," + "tsi.shop_logo shopLogo,"
            + "tsi.status status, " + "tsi.shop_no shopNo "
            + "from tbl_shop_info tsi where tsi.merch_no=:shopNo and tsi.status!='3'";
    Query query = shopDao.createSQLQuery(sql);
    query.setParameter("shopNo", memberNo);
    query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
    List<Map<String, Object>> results = query.list();
    Map<String, Object> result = new HashMap<String, Object>();
    if (results != null && !results.isEmpty()) {
        result = results.get(0);
    } else {
        return null;
    }
    String shopNo = String.valueOf(result.get("shopNo"));
    Criteria criteria = commodityDao.createCriteria(Restrictions.eq("sellerNo", shopNo));
    criteria.add(Restrictions.eq("deleteFlag", (byte) 0));//
    criteria.add(Restrictions.eq("publishState", "1"));//
    int commodityCount = commodityDao.getRowCount(criteria);// ??
    result.put("commodityCount", String.valueOf(commodityCount));
    criteria = shopCollectDao.createCriteria(Restrictions.eq("shopNo", shopNo));
    int collectCount = shopCollectDao.getRowCount(criteria);
    result.put("collectCount", String.valueOf(collectCount));
    Criteria promotionCriteria = promotionDao.createCriteria(Restrictions.eq("shopNo", shopNo));
    promotionCriteria.add(Restrictions.ge("endTime", new Date()));//
    promotionCriteria.add(Restrictions.eq("status", "2"));//
    promotionCriteria.add(Restrictions.eq("lockFlag", "0"));//?
    promotionCriteria.add(Restrictions.ne("promotionType", "2"));//?
    int count = promotionDao.getRowCount(promotionCriteria);
    result.put("promotionCount", count);
    List<String> values = new ArrayList<String>();
    //      values.add(OrderConstants.BASIC_STATE_REFUND);
    //      values.add(OrderConstants.BASIC_STATE_ALREADY_RECEIVE);
    //      values.add(OrderConstants.BASIC_STATE_REFUND_APPLY);
    values.add(OrderConstants.BASIC_STATE_WAITING_DELIVERY);
    //      values.add(OrderConstants.BASIC_STATE_WAITING_RETURN);
    //      values.add(OrderConstants.BASIC_STATE_WAITING_PAY);
    //      values.add(OrderConstants.BASIC_STATE_ALREADY_DELIVERY);
    Criteria criteriaOrder = orderDao.createCriteria(Restrictions.in("basicState", values));
    criteriaOrder.add(Restrictions.eq("deleteFlag", (byte) 0));
    criteriaOrder.add(Restrictions.eq("shopNo", shopNo));
    String sqlGroupBuy = "select count(*) " + "  from tbl_promotion tp "
            + " left join tbl_product tpr on tp.ref_commo_no = tpr.commo_no"
            + " where tp.promotion_type='2' and tp.delete_flag='0' and tp.shop_no=:shopNo  and :curentTime between tp.start_time and tp.end_time ";
    Query groupBuyQuery = promotionDao.createSQLQuery(sqlGroupBuy);
    groupBuyQuery.setParameter("shopNo", shopNo);
    groupBuyQuery.setParameter("curentTime", new Date());
    BigInteger totalRows = (BigInteger) groupBuyQuery.uniqueResult();
    result.put("groupBuyCount", totalRows.intValue());
    result.put("orderCount", orderDao.getRowCount(criteriaOrder));
    String gradeSql = "select  avg(tcc.service_grade) serviceGrade," + " avg(tcc.delivery_grade) deliveryGrade"
            + " from tbl_commodity_comment tcc " + " where tcc.shop_no = :shopNo " + " group by(tcc.shop_no) ";
    Query queryGrade = shopDao.createSQLQuery(gradeSql);
    queryGrade.setParameter("shopNo", shopNo);
    queryGrade.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
    List<Map<String, Object>> list = queryGrade.list();
    if (list != null && !list.isEmpty()) {
        Double servGrade = list.get(0).get("serviceGrade") == null ? 0
                : Double.valueOf(list.get(0).get("serviceGrade").toString());
        BigDecimal serviceGrade = new BigDecimal(servGrade);
        serviceGrade = serviceGrade.setScale(1, BigDecimal.ROUND_HALF_EVEN);
        result.put("serviceGrade", serviceGrade.doubleValue());
        Double delGrade = list.get(0).get("deliveryGrade") == null ? 0
                : Double.valueOf(list.get(0).get("deliveryGrade").toString());
        BigDecimal deliveryGrade = new BigDecimal(delGrade);
        deliveryGrade = deliveryGrade.setScale(1, BigDecimal.ROUND_HALF_EVEN);
        result.put("deliveryGrade", deliveryGrade.doubleValue());
    } else {
        result.put("serviceGrade", "0");
        result.put("deliveryGrade", "0");
    }

    return result;
}

From source file:com.zl.bgec.basicapi.shop.service.impl.ShopServiceImpl.java

@Override
@Transactional(readOnly = true)/* ww  w.jav  a2  s  .co  m*/
public List<Map<String, Object>> getShop(String shopName, String shopTypeNo, String memberNo) throws Exception {
    String sql = "select " + " tsi.shop_no shopNo, " + " tsi.shop_name shopName, " + " tsi.shop_logo shopLogo, "
            + " tsi.shop_summary shopSummary, " + " tsi.is_recommend isRecommend, "
            + " tcc.serviceGrade serviceGrade, " + " tcc.deliveryGrade deliveryGrade, "
            + " if(tcs.shop_no is null,'0','1') isCollect " + " from tbl_shop_info tsi left join   "
            + " (select avg(tcc.service_grade) serviceGrade,avg(tcc.delivery_grade) deliveryGrade , "
            + " tcc.shop_no from tbl_commodity_comment tcc group by(tcc.shop_no) ) tcc on tsi.shop_no = tcc.shop_no "
            + " left join (select * from tbl_shop_collect tcs where tcs.member_no = :memberNo) tcs on tsi.shop_no = tcs.shop_no "
            + "where tsi.status=2 ";
    if (shopTypeNo != null && !shopTypeNo.equals("")) {
        sql = sql + "and tsi.shop_type_no=:shopTypeNo ";
    }
    if (shopName != null && !shopName.equals("")) {
        sql = sql + "and tsi.shop_name like :shopName ";
    }
    Query query = shopDao.createSQLQuery(sql);
    if (shopTypeNo != null && !shopTypeNo.equals("")) {
        query.setParameter("shopTypeNo", shopTypeNo);
    }
    if (shopName != null && !shopName.equals("")) {
        query.setParameter("shopName", "%" + shopName + "%");
    }
    query.setParameter("memberNo", memberNo);
    query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
    List<Map<String, Object>> results = query.list();
    if (results != null && !results.isEmpty()) {
        for (Map<String, Object> result : results) {
            Double servGrade = result.get("serviceGrade") == null ? 0
                    : Double.valueOf(result.get("serviceGrade").toString());
            BigDecimal serviceGrade = new BigDecimal(servGrade);
            serviceGrade = serviceGrade.setScale(1, BigDecimal.ROUND_HALF_EVEN);
            result.put("serviceGrade", serviceGrade.doubleValue());
            Double delGrade = result.get("deliveryGrade") == null ? 0
                    : Double.valueOf(result.get("deliveryGrade").toString());
            BigDecimal deliveryGrade = new BigDecimal(delGrade);
            deliveryGrade = deliveryGrade.setScale(1, BigDecimal.ROUND_HALF_EVEN);
            result.put("deliveryGrade", deliveryGrade.doubleValue());
        }
    }
    return results;
}