Example usage for org.hibernate SQLQuery list

List of usage examples for org.hibernate SQLQuery list

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. SQLQuery
  6. list

Introduction

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

Prototype

List<R> list();

Source Link

Document

Return the query results as a List.

Usage

From source file:com.dell.asm.asmcore.asmmanager.db.DeviceInventoryDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
private List<String> getRefIdsOfDevicesByGroupIds(final List<String> ids) {
    final String idsJoined = "'" + StringUtils.join(ids, "','") + "'";
    Session session = null;/*from w w  w. jav a 2  s.c  om*/
    Transaction tx;

    List<String> refIds = ListUtils.EMPTY_LIST;
    try {
        session = _dao._database.getNewSession();
        tx = session.beginTransaction();
        final String sql;
        if (idsJoined.equals("'" + ServiceTemplateSettingIDs.SERVICE_TEMPLATE_SERVER_POOL_GLOBAL_ID + "'")) {
            sql = "select distinct devices_inventory_seq_id from groups_device_inventory";
        } else {
            sql = "select devices_inventory_seq_id from groups_device_inventory where groups_seq_id in ("
                    + idsJoined + ")";
        }
        SQLQuery query = session.createSQLQuery(sql);
        refIds = query.list();
        tx.commit();
    } catch (Exception e) {
        logger.warn("Caught exception getting refIds of groups ids " + idsJoined, e);
        throw new AsmManagerInternalErrorException("Get Ref Ids by Group Ids", "DeviceInventoryDAO", e);
    } finally {
        try {
            if (session != null) {
                session.close();
            }
        } catch (Exception ex) {
            logger.warn("Unable to close session during ref Id retrieval: " + ex);
        }
    }

    return refIds;
}

From source file:com.dungnv.streetfood.business.ArticleBusiness.java

License:Open Source License

@Override
public List<ArticleDTO> getListArticleDTOLess(String userName, String localeCode, String countryCode,
        String token, ArticleDTO dto, int rowStart, int maxRow, boolean isCount, String sortType,
        String sortFieldList) {/*from   w  w w .j  a  va 2 s . c om*/
    StringBuilder sbQuery = new StringBuilder();
    List<Object> listParam = new ArrayList<>();
    List<Type> listType = new ArrayList<>();

    if (isCount) {
        sbQuery.append(" select count(a.id) as id from article a where 1=1 ");
    } else {
        sbQuery.append(" select a.id");
        sbQuery.append(" , a.title");
        if (dto == null || !"1".equals(dto.getIsGetOnlyIdentified())) {
            sbQuery.append(" , a.short_content shortContent");
            sbQuery.append(" , a.view_count viewCount");
            sbQuery.append(" , g.id imageId");
            sbQuery.append(" , g.url imageUrl");
        }

        sbQuery.append("  from article a left outer join img g on a.id = g.article_id and g.orders = 1");
        sbQuery.append(" where 1 = 1");
    }

    if (dto != null) {
        StringUtils.trimString(dto, false);

        if (!StringUtils.isNullOrEmpty(dto.getId())) {
            sbQuery.append(" AND  a.id = ?");
            listParam.add(Long.valueOf(dto.getId()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getRestaurantId())) {
            sbQuery.append(
                    " AND a.id in ( select article_id from restaurant_article where restaurant_id = ? ) ");
            listParam.add(Long.valueOf(dto.getRestaurantId()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getNotRestaurantId())) {
            sbQuery.append(
                    " AND a.id not in ( select article_id from restaurant_article where restaurant_id = ? ) ");
            listParam.add(Long.valueOf(dto.getNotRestaurantId()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getDishId())) {
            sbQuery.append(" AND a.id in ( select article_id from dish_article where dish_id = ? ) ");
            listParam.add(Long.valueOf(dto.getDishId()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getNotDishId())) {
            sbQuery.append(" AND a.id not in ( select article_id from dish_article where dish_id = ? ) ");
            listParam.add(Long.valueOf(dto.getNotDishId()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getTitle())) {
            sbQuery.append(" AND lower(a.title) like ? ");
            listParam.add("%" + dto.getTitle().toLowerCase() + "%");
            listType.add(StringType.INSTANCE);
        }
        if (!StringUtils.isNullOrEmpty(dto.getShortContent())) {
            sbQuery.append(" AND lower(a.short_content) like ? ");
            listParam.add("%" + dto.getShortContent().toLowerCase() + "%");
            listType.add(StringType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getContent())) {
            sbQuery.append(" AND lower(a.content) like ? ");
            listParam.add("%" + dto.getContent().toLowerCase() + "%");
            listType.add(StringType.INSTANCE);
        }

        if (dto.getListTag() != null && !dto.getListTag().isEmpty()) {
            sbQuery.append(" AND  a.id in (select article_id from tag_article where tag_id in ");
            sbQuery.append(QueryUtil.getParameterHolderString(dto.getListTag().size()));
            sbQuery.append(" )");
            List<String> listTag = dto.getListTag();
            for (String tagId : listTag) {
                listParam.add(Long.valueOf(tagId));
                listType.add(LongType.INSTANCE);
            }
        }
    }

    if (!isCount) {
        sbQuery.append(" order by a.title DESC");
        if (maxRow != 0) {
            sbQuery.append(" limit ?, ?");
            listParam.add(rowStart);
            listType.add(IntegerType.INSTANCE);
            listParam.add(maxRow);
            listType.add(IntegerType.INSTANCE);
        }

    }

    SQLQuery query = gettDAO().getSession().createSQLQuery(sbQuery.toString());
    query.addScalar("id", StringType.INSTANCE);
    if (!isCount) {
        query.addScalar("title", StringType.INSTANCE);
        if (dto == null || !"1".equals(dto.getIsGetOnlyIdentified())) {
            query.addScalar("shortContent", StringType.INSTANCE);
            query.addScalar("viewCount", StringType.INSTANCE);
            query.addScalar("imageId", StringType.INSTANCE);
            query.addScalar("imageUrl", StringType.INSTANCE);
        }
    }

    query.setResultTransformer(Transformers.aliasToBean(ArticleDTO.class));

    for (int i = 0; i < listParam.size(); i++) {
        query.setParameter(i, listParam.get(i), listType.get(i));
    }

    List<ArticleDTO> list = query.list();
    return list;
}

From source file:com.dungnv.streetfood.business.CategoryDishBusiness.java

License:Open Source License

@Override
public List<CategoryDTO> getListCategoryByDish(String userName, String localeCode, String countryCode,
        String token//
        , String id, boolean isIn) {

    StringBuilder sql = new StringBuilder("select c.id, c.name from Category c ");
    sql.append(" where c.id ");
    sql.append(isIn ? " in " : " not in ");
    sql.append(" (select distinct category_id from category_dish where dish_id = ? ) ");

    SQLQuery query = gettDAO().getSession().createSQLQuery(sql.toString());
    query.addScalar("id", StringType.INSTANCE);
    query.addScalar("name", StringType.INSTANCE);
    query.setLong(0, Long.valueOf(id));
    query.setResultTransformer(Transformers.aliasToBean(CategoryDTO.class));
    return query.list();
}

From source file:com.dungnv.streetfood.business.CategoryDishBusiness.java

License:Open Source License

@Override
public List<DishDTO> getListDishByCategory(String userName, String localeCode, String countryCode, String token//
        , String id, boolean isIn) {
    StringBuilder sql = new StringBuilder("select c.id, c.name from dish c ");
    sql.append(" where c.id ");
    sql.append(isIn ? "in" : " not in");
    sql.append(" (select distinct dish_id from category_dish where category_id = ? ) ");

    SQLQuery query = gettDAO().getSession().createSQLQuery(sql.toString());
    query.addScalar("id", StringType.INSTANCE);
    query.addScalar("name", StringType.INSTANCE);
    query.setLong(0, Long.valueOf(id));
    query.setResultTransformer(Transformers.aliasToBean(DishDTO.class));
    return query.list();
}

From source file:com.dungnv.streetfood.business.DishArticleBusiness.java

License:Open Source License

@Override
public List<ArticleDTO> getListArticleByDish(String userName, String localeCode, String countryCode,
        String token, String id) {
    SQLQuery query = gettDAO().getSession().createSQLQuery("select c.id, c.name from article c "
            + " inner join dish_article l on c.id = l.article_id" + " where l.dish_id = ? ");
    query.addScalar("id", StringType.INSTANCE);
    query.addScalar("name", StringType.INSTANCE);
    query.setLong(0, Long.valueOf(id));
    query.setResultTransformer(Transformers.aliasToBean(ArticleDTO.class));
    return query.list();
}

From source file:com.dungnv.streetfood.business.DishArticleBusiness.java

License:Open Source License

@Override
public List<DishDTO> getListDishByArticle(String userName, String localeCode, String countryCode, String token,
        String id) {/*from w  w w .  j  a v a  2 s  .c o m*/
    SQLQuery query = gettDAO().getSession().createSQLQuery("select c.id, c.name from dish c "
            + " inner join dish_article l on c.id =l.dish_id" + " where l.article_id = ? ");
    query.addScalar("id", StringType.INSTANCE);
    query.addScalar("name", StringType.INSTANCE);
    query.setLong(0, Long.valueOf(id));
    query.setResultTransformer(Transformers.aliasToBean(DishDTO.class));
    return query.list();
}

From source file:com.dungnv.streetfood.business.DishBusiness.java

License:Open Source License

@Override
public List<DishDTO> getListDishDTOLess(String userName, String localeCode, String countryCode, String token,
        DishDTO dto, int rowStart, int maxRow, boolean isCount, String sortType, String sortFieldList) {
    StringBuilder sbQuery = new StringBuilder();
    List<Object> listParam = new ArrayList<>();
    List<Type> listType = new ArrayList<>();

    if (isCount) {
        sbQuery.append(" select count(c.id) as id from dish c where 1=1 ");
    } else {/* w  w w  . j  a  v a2s.c o m*/
        sbQuery.append(" select c.id , c.name");
        if (dto == null || !"1".equals(dto.getIsGetOnlyIdentified())) {
            sbQuery.append(" , c.short_description shortDescription");
            sbQuery.append(" , c.dish_status dishStatus");
            sbQuery.append(" , c.view_count viewCount");
            sbQuery.append(" , c.comment_count commentCount ");
            sbQuery.append(" , c.share_count shareCount");
            sbQuery.append(" , c.rating ");
            sbQuery.append(" , g.id imageId");
            sbQuery.append(" , g.url imageUrl");
        }

        sbQuery.append(" from dish c left outer join img g on c.id = g.dish_id and g.orders = 1 ");
        sbQuery.append(" where 1=1");
    }

    if (dto != null) {
        StringUtils.trimString(dto, false);

        if (!StringUtils.isNullOrEmpty(dto.getId())) {
            sbQuery.append(" AND  c.id = ?");
            listParam.add(Long.valueOf(dto.getId()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getName())) {
            sbQuery.append(" AND lower(c.name) like ? ");
            listParam.add("%" + dto.getName().toLowerCase() + "%");
            listType.add(StringType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getCategoryId())) {
            sbQuery.append(" AND c.id in ( select dish_id from category_dish where category_id = ? ) ");
            listParam.add(Long.valueOf(dto.getCategoryId()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getNotCategoryId())) {
            sbQuery.append(" AND c.id not in ( select dish_id from category_dish where category_id = ? ) ");
            listParam.add(Long.valueOf(dto.getNotCategoryId()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getArticleId())) {
            sbQuery.append(" AND c.id in ( select dish_id from dish_article where article_id = ? ) ");
            listParam.add(Long.valueOf(dto.getArticleId()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getNotArticleId())) {
            sbQuery.append(" AND c.id not in ( select dish_id from dish_article where article_id = ? ) ");
            listParam.add(Long.valueOf(dto.getNotArticleId()));
            listType.add(LongType.INSTANCE);
        }
        if (!StringUtils.isNullOrEmpty(dto.getRestaurantId())) {
            sbQuery.append(
                    " AND c.id in ( select dish_id from restaurant_dish_detail where restaurant_id = ? ) ");
            listParam.add(Long.valueOf(dto.getRestaurantId()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getNotRestaurantId())) {
            sbQuery.append(
                    " AND c.id not in ( select dish_id from restaurant_dish_detail where restaurant_id = ? ) ");
            listParam.add(Long.valueOf(dto.getNotRestaurantId()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getDishStatus())) {
            sbQuery.append(" AND c.dish_status = ? ");
            listParam.add(Long.valueOf(dto.getDishStatus()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getShortDescription())) {
            sbQuery.append(" AND lower(c.description) like ? ");
            listParam.add("%" + dto.getShortDescription().toLowerCase() + "%");
            listType.add(StringType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getLongDescription())) {
            sbQuery.append(" AND lower(c.long_description) like ? ");
            listParam.add("%" + dto.getLongDescription().toLowerCase() + "%");
            listType.add(StringType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getViewCountFrom())) {
            sbQuery.append(" AND c.view_count >= ? ");
            listParam.add(Long.valueOf(dto.getViewCountFrom()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getViewCountTo())) {
            sbQuery.append(" AND c.view_count <= ? ");
            listParam.add(Long.valueOf(dto.getViewCountTo()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getCommentCountFrom())) {
            sbQuery.append(" AND c.comment_count >= ? ");
            listParam.add(Long.valueOf(dto.getCommentCountFrom()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getCommentCountTo())) {
            sbQuery.append(" AND c.comment_count <= ? ");
            listParam.add(Long.valueOf(dto.getCommentCountTo()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getShareCountFrom())) {
            sbQuery.append(" AND c.share_count >= ? ");
            listParam.add(Long.valueOf(dto.getShareCountFrom()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getShareCountTo())) {
            sbQuery.append(" AND c.share_count <= ? ");
            listParam.add(Long.valueOf(dto.getShareCountTo()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getRatingFrom())) {
            sbQuery.append(" AND c.rating >= ? ");
            listParam.add(Double.valueOf(dto.getRatingFrom()));
            listType.add(DoubleType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getRatingTo())) {
            sbQuery.append(" AND c.rating <= ? ");
            listParam.add(Double.valueOf(dto.getRatingTo()));
            listType.add(DoubleType.INSTANCE);
        }

        if (dto.getListTag() != null && !dto.getListTag().isEmpty()) {
            sbQuery.append(" AND  c.id in (select dish_id from tag_dish where tag_id in ");
            sbQuery.append(QueryUtil.getParameterHolderString(dto.getListTag().size()));
            sbQuery.append(" )");
            List<String> listTag = dto.getListTag();
            for (String tagId : listTag) {
                listParam.add(Long.valueOf(tagId));
                listType.add(LongType.INSTANCE);
            }
        }

        if (dto.getListNotLocale() != null && !dto.getListNotLocale().isEmpty()) {
            sbQuery.append(" AND not exists (select l.dish_id from dish_language l where l.language_code in ");
            sbQuery.append(QueryUtil.getParameterHolderString(dto.getListNotLocale().size()));
            sbQuery.append(" AND l.dish_id = c.id )");
            List<String> listNotLocale = dto.getListNotLocale();
            for (String notLocale : listNotLocale) {
                listParam.add(notLocale);
                listType.add(StringType.INSTANCE);
            }
        }
        if (dto.getListLocale() != null && !dto.getListLocale().isEmpty()) {

            for (String locale : dto.getListLocale()) {
                sbQuery.append(" AND exists (select l.dish_id from dish_language l where l.language_code = ? ");
                sbQuery.append(" AND l.dish_id = c.id )");
                listParam.add(locale);
                listType.add(StringType.INSTANCE);
            }
        }
    }

    if (!isCount) {
        sbQuery.append(" order by c.name DESC");
        if (maxRow != 0) {
            sbQuery.append(" limit ?, ?");
            listParam.add(rowStart);
            listType.add(IntegerType.INSTANCE);
            listParam.add(maxRow);
            listType.add(IntegerType.INSTANCE);
        }
    }

    SQLQuery query = gettDAO().getSession().createSQLQuery(sbQuery.toString());
    query.addScalar("id", StringType.INSTANCE);
    if (!isCount) {
        query.addScalar("name", StringType.INSTANCE);
        if (dto == null || !"1".equals(dto.getIsGetOnlyIdentified())) {
            query.addScalar("shortDescription", StringType.INSTANCE);
            query.addScalar("dishStatus", StringType.INSTANCE);
            query.addScalar("viewCount", StringType.INSTANCE);
            query.addScalar("commentCount", StringType.INSTANCE);
            query.addScalar("shareCount", StringType.INSTANCE);
            query.addScalar("rating", StringType.INSTANCE);
            query.addScalar("imageId", StringType.INSTANCE);
            query.addScalar("imageUrl", StringType.INSTANCE);
        }
    }

    query.setResultTransformer(Transformers.aliasToBean(DishDTO.class));

    for (int i = 0; i < listParam.size(); i++) {
        query.setParameter(i, listParam.get(i), listType.get(i));
    }

    List<DishDTO> list = query.list();
    return list;
}

From source file:com.dungnv.streetfood.business.RestaurantArticleBusiness.java

License:Open Source License

@Override
public List<ArticleDTO> getListArticleByRestaurant(String userName, String localeCode, String countryCode,
        String token, String id) {
    SQLQuery query = gettDAO().getSession().createSQLQuery("select c.id, c.name from article c "
            + " inner join restaurant_article l on c.id = l.article_id" + " where l.restaurant_id = ? ");
    query.addScalar("id", StringType.INSTANCE);
    query.addScalar("name", StringType.INSTANCE);
    query.setLong(0, Long.valueOf(id));
    query.setResultTransformer(Transformers.aliasToBean(ArticleDTO.class));
    return query.list();
}

From source file:com.dungnv.streetfood.business.RestaurantArticleBusiness.java

License:Open Source License

@Override
public List<RestaurantDTO> getListRestaurantByArticle(String userName, String localeCode, String countryCode,
        String token, String id) {
    SQLQuery query = gettDAO().getSession().createSQLQuery("select c.id, c.name from restaurant c "
            + " inner join restaurant_article l on c.id =l.restaurant_id" + " where l.article_id = ? ");
    query.addScalar("id", StringType.INSTANCE);
    query.addScalar("name", StringType.INSTANCE);
    query.setLong(0, Long.valueOf(id));
    query.setResultTransformer(Transformers.aliasToBean(RestaurantDTO.class));
    return query.list();
}

From source file:com.dungnv.streetfood.business.RestaurantBusiness.java

License:Open Source License

@Override
public List<RestaurantDTO> getListRestaurantDTOLess(String userName, String localeCode, String countryCode,
        String token, RestaurantDTO dto, int rowStart, int maxRow, boolean isCount, String sortType,
        String sortFieldList) {//from w  w w. jav a2s .com
    StringBuilder sbQuery = new StringBuilder();
    List<Object> listParam = new ArrayList<>();
    List<Type> listType = new ArrayList<>();

    if (isCount) {
        sbQuery.append(" select count(r.id) as id from restaurant r where 1=1 ");
    } else {
        sbQuery.append(" select ");
        sbQuery.append(" r.id");
        sbQuery.append(" , r.name");
        if (dto == null || !"1".equals(dto.getIsGetOnlyIdentified())) {
            sbQuery.append(" , r.address");
            sbQuery.append(" , r.restaurant_status restaurantStatus");
            sbQuery.append(" , r.view_count viewCount");
            sbQuery.append(" , r.comment_count commentCount");
            sbQuery.append(" , r.share_count shareCount");
            sbQuery.append(" , r.rating");
            sbQuery.append(" , g.id imageId");
            sbQuery.append(" , g.url imageUrl");
        }

        sbQuery.append(" from restaurant r left outer join img g on r.id = g.restaurant_id and g.orders = 1");
        sbQuery.append(" where 1 = 1");
    }

    if (dto != null) {
        StringUtils.trimString(dto, false);

        if (!StringUtils.isNullOrEmpty(dto.getId())) {
            sbQuery.append(" AND  r.id = ?");
            listParam.add(Long.valueOf(dto.getId()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getArticleId())) {
            sbQuery.append(
                    " AND r.id in ( select restaurant_id from restaurant_article where article_id = ? ) ");
            listParam.add(Long.valueOf(dto.getArticleId()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getNotArticleId())) {
            sbQuery.append(
                    " AND r.id not in ( select restaurant_id from restaurant_article where article_id = ? ) ");
            listParam.add(Long.valueOf(dto.getNotArticleId()));
            listType.add(LongType.INSTANCE);
        }
        if (!StringUtils.isNullOrEmpty(dto.getDishId())) {
            sbQuery.append(
                    " AND r.id in ( select restaurant_id from restaurant_dish_detail where dish_id = ? ) ");
            listParam.add(Long.valueOf(dto.getDishId()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getNotDishId())) {
            sbQuery.append(
                    " AND r.id not in ( select restaurant_id from restaurant_dish_detail where dish_id = ? ) ");
            listParam.add(Long.valueOf(dto.getNotDishId()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getName())) {
            sbQuery.append(" AND lower(r.name) like ? ");
            listParam.add("%" + dto.getName().toLowerCase() + "%");
            listType.add(StringType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getAddress())) {
            sbQuery.append(" AND lower(r.address) like ? ");
            listParam.add("%" + dto.getAddress().toLowerCase() + "%");
            listType.add(StringType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getIntroduce())) {
            sbQuery.append(" AND lower(r.introduce) like ? ");
            listParam.add("%" + dto.getIntroduce().toLowerCase() + "%");
            listType.add(StringType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getPhoneNumber())) {
            sbQuery.append(" AND lower(r.phone_number) like ? ");
            listParam.add("%" + dto.getPhoneNumber().toLowerCase() + "%");
            listType.add(StringType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getCapacity())) {
            sbQuery.append(" AND lower(r.capacity) like ? ");
            listParam.add("%" + dto.getCapacity().toLowerCase() + "%");
            listType.add(StringType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getRestaurantStatus())) {
            sbQuery.append(" AND r.restaurant_status = ? ");
            listParam.add(Long.valueOf(dto.getRestaurantStatus()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getCarParking())) {
            sbQuery.append(" AND r.car_parking = ? ");
            listParam.add(Long.valueOf(dto.getCarParking()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getMotobikeParking())) {
            sbQuery.append(" AND r.motobike_parking = ? ");
            listParam.add(Long.valueOf(dto.getMotobikeParking()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getViewCountFrom())) {
            sbQuery.append(" AND r.view_count >= ? ");
            listParam.add(Long.valueOf(dto.getViewCountFrom()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getViewCountTo())) {
            sbQuery.append(" AND r.view_count <= ? ");
            listParam.add(Long.valueOf(dto.getViewCountTo()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getCommentCountFrom())) {
            sbQuery.append(" AND r.comment_count >= ? ");
            listParam.add(Long.valueOf(dto.getCommentCountFrom()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getCommentCountTo())) {
            sbQuery.append(" AND r.comment_count <= ? ");
            listParam.add(Long.valueOf(dto.getCommentCountTo()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getShareCountFrom())) {
            sbQuery.append(" AND r.share_count >= ? ");
            listParam.add(Long.valueOf(dto.getShareCountFrom()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getShareCountTo())) {
            sbQuery.append(" AND r.share_count <= ? ");
            listParam.add(Long.valueOf(dto.getShareCountTo()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getRatingFrom())) {
            sbQuery.append(" AND r.rating >= ? ");
            listParam.add(Double.valueOf(dto.getRatingFrom()));
            listType.add(DoubleType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getRatingTo())) {
            sbQuery.append(" AND r.rating <= ? ");
            listParam.add(Double.valueOf(dto.getRatingTo()));
            listType.add(DoubleType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getWaitingTimeFrom())) {
            sbQuery.append(" AND r.waiting_time >= ? ");
            listParam.add(Long.valueOf(dto.getWaitingTimeFrom()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getWaitingTimeTo())) {
            sbQuery.append(" AND r.waiting_time <= ? ");
            listParam.add(Long.valueOf(dto.getWaitingTimeTo()));
            listType.add(LongType.INSTANCE);
        }

        if (!StringUtils.isNullOrEmpty(dto.getOperatingTimeStart())) {
            try {
                sbQuery.append(" AND r.operating_time_start >= ? ");
                listParam.add(DateTimeUtils.convertStringToTime(dto.getOperatingTimeStart(), ParamUtils.HHmm));
                listType.add(TimeType.INSTANCE);
            } catch (Exception ex) {
                Logger.getLogger(RestaurantBusiness.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        if (!StringUtils.isNullOrEmpty(dto.getOperatingTimeEnd())) {
            try {
                sbQuery.append(" AND r.operating_time_end <= ? ");
                listParam.add(DateTimeUtils.convertStringToTime(dto.getOperatingTimeEnd(), ParamUtils.HHmm));
                listType.add(TimeType.INSTANCE);
            } catch (Exception ex) {
                Logger.getLogger(RestaurantBusiness.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        if (dto.getListTag() != null && !dto.getListTag().isEmpty()) {
            sbQuery.append(" AND  r.id in (select restaurant_id from tag_restaurant where tag_id in ");
            sbQuery.append(QueryUtil.getParameterHolderString(dto.getListTag().size()));
            sbQuery.append(" )");
            List<String> listTag = dto.getListTag();
            for (String tagId : listTag) {
                listParam.add(Long.valueOf(tagId));
                listType.add(LongType.INSTANCE);
            }
        }

        if (dto.getListNotLocale() != null && !dto.getListNotLocale().isEmpty()) {
            sbQuery.append(
                    " AND not exists (select l.restaurant_id from restaurant_language l where l.language_code in ");
            sbQuery.append(QueryUtil.getParameterHolderString(dto.getListNotLocale().size()));
            sbQuery.append(" AND l.restaurant_id = r.id )");
            List<String> listNotLocale = dto.getListNotLocale();
            for (String notLocale : listNotLocale) {
                listParam.add(notLocale);
                listType.add(StringType.INSTANCE);
            }
        }
        if (dto.getListLocale() != null && !dto.getListLocale().isEmpty()) {

            for (String locale : dto.getListLocale()) {
                sbQuery.append(
                        " AND exists (select l.restaurant_id from restaurant_language l where l.language_code = ? ");
                sbQuery.append(" AND l.restaurant_id = r.id )");
                listParam.add(locale);
                listType.add(StringType.INSTANCE);
            }
        }
    }

    if (!isCount) {
        sbQuery.append(" order by r.name DESC");
        if (maxRow != 0) {
            sbQuery.append(" limit ?, ?");
            listParam.add(rowStart);
            listType.add(IntegerType.INSTANCE);
            listParam.add(maxRow);
            listType.add(IntegerType.INSTANCE);
        }

    }

    SQLQuery query = gettDAO().getSession().createSQLQuery(sbQuery.toString());
    query.addScalar("id", StringType.INSTANCE);
    if (!isCount) {
        query.addScalar("name", StringType.INSTANCE);
        if (dto == null || !"1".equals(dto.getIsGetOnlyIdentified())) {
            query.addScalar("address", StringType.INSTANCE);
            query.addScalar("restaurantStatus", StringType.INSTANCE);
            query.addScalar("viewCount", StringType.INSTANCE);
            query.addScalar("commentCount", StringType.INSTANCE);
            query.addScalar("shareCount", StringType.INSTANCE);
            query.addScalar("rating", StringType.INSTANCE);
            query.addScalar("imageId", StringType.INSTANCE);
            query.addScalar("imageUrl", StringType.INSTANCE);
        }

    }

    query.setResultTransformer(Transformers.aliasToBean(RestaurantDTO.class));

    for (int i = 0; i < listParam.size(); i++) {
        query.setParameter(i, listParam.get(i), listType.get(i));
    }

    List<RestaurantDTO> list = query.list();
    return list;
}