Example usage for org.hibernate SQLQuery list

List of usage examples for org.hibernate SQLQuery 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.dungnv.streetfood.business.RestaurantDishDetailBusiness.java

License:Open Source License

@Override
public List<RestaurantDTO> getListRestaurantByDish(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_dish_detail l on c.id = l.restaurant_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(RestaurantDTO.class));
    return query.list();
}

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

License:Open Source License

@Override
public List<DishDTO> getListDishByRestaurant(String userName, String localeCode, String countryCode,
        String token, String id) {
    SQLQuery query = gettDAO().getSession().createSQLQuery("select c.id, c.name from dish c "
            + " inner join restaurant_dish_detail l on c.id =l.dish_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(DishDTO.class));
    return query.list();
}

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

License:Open Source License

@Override
public List<SlideShowDTO> getListSlideShowDTOLess(String userName, String localeCode, String countryCode,
        String token, SlideShowDTO dto, int rowStart, int maxRow, boolean isCount, String sortType,
        String sortFieldList) {//from ww w . j a v  a 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 slide_show a where 1=1 ");
    } else {
        sbQuery.append(" select a.id ");
        sbQuery.append(" , a.url");
        sbQuery.append(" , a.name");
        sbQuery.append(" , a.description");
        sbQuery.append(" , a.orders as 'order'");
        sbQuery.append(" , a.valid_from_gmt validFromGmt ");
        sbQuery.append(" , a.valid_to_gmt validToGmt");
        sbQuery.append(" , g.id imageId");
        sbQuery.append(" , g.url imageUrl");
        sbQuery.append(" from slide_show a left outer join img g on a.id = g.slide_show_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.getName())) {
            sbQuery.append(" AND lower(a.name) like ? ");
            listParam.add("%" + dto.getName().toLowerCase() + "%");
            listType.add(StringType.INSTANCE);
        }
    }

    if (!isCount) {
        sbQuery.append(" order by a.orders 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("url", StringType.INSTANCE);
        query.addScalar("name", StringType.INSTANCE);
        query.addScalar("description", StringType.INSTANCE);
        query.addScalar("order", StringType.INSTANCE);
        query.addScalar("validFromGmt", StringType.INSTANCE);
        query.addScalar("validToGmt", StringType.INSTANCE);
        query.addScalar("imageId", StringType.INSTANCE);
        query.addScalar("imageUrl", StringType.INSTANCE);
    }

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

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

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

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

License:Open Source License

@Override
public List<String> getTagsListByArticle(String userName, String localeCode, String countryCode, String token,
        String id) {//from w  ww  .  jav a  2 s  . c  om
    SQLQuery query = gettDAO().getSession().createSQLQuery("select t.name id from tags t "
            + "inner join tag_article l on t.id =l.tag_id" + " where l.article_id = ? ");
    query.addScalar("id", StringType.INSTANCE);
    query.setLong(0, Long.valueOf(id));
    return query.list();
}

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

License:Open Source License

@Override
public List<String> getTagsListByCategory(String userName, String localeCode, String countryCode, String token,
        String id) {/* w ww  .  j  a v a  2  s  .  com*/
    SQLQuery query = gettDAO().getSession().createSQLQuery("select t.name id from tags t "
            + "inner join tag_category l on t.id =l.tag_id" + " where l.category_id = ? ");
    query.addScalar("id", StringType.INSTANCE);
    query.setLong(0, Long.valueOf(id));
    return query.list();
}

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

License:Open Source License

@Override
public List<String> getTagsListByDish(String userName, String localeCode, String countryCode, String token,
        String id) {//from w  w w.  ja  v a2s.  c o  m
    SQLQuery query = gettDAO().getSession().createSQLQuery("select t.name id from tags t "
            + "inner join tag_dish l on t.id =l.tag_id" + " where l.dish_id = ? ");
    query.addScalar("id", StringType.INSTANCE);
    query.setLong(0, Long.valueOf(id));
    return query.list();
}

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

License:Open Source License

@Override
public List<String> getTagsListByRestaurant(String userName, String localeCode, String countryCode,
        String token, String id) {
    SQLQuery query = gettDAO().getSession().createSQLQuery("select t.name id from tags t "
            + "inner join tag_restaurant l on t.id =l.tag_id" + " where l.restaurant_id = ? ");
    query.addScalar("id", StringType.INSTANCE);
    query.setLong(0, Long.valueOf(id));
    return query.list();
}

From source file:com.dungnv.streetfood.dao.CategoryDAO.java

License:Open Source License

public List<CategoryDTO> getListCategoryDTOLess(CategoryDTO categoryDTO, 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 Category c where 1=1 ");
    } else {//from w  ww .j ava2 s.  c o m
        sbQuery.append(" select c.id ");
        sbQuery.append(" , c.name");
        if (categoryDTO == null || !"1".equals(categoryDTO.getIsGetOnlyIdentified())) {
            sbQuery.append(" , c.description");
            sbQuery.append(" , c.category_status categoryStatus");
            sbQuery.append(" , g.id imageId");
            sbQuery.append(" , g.url imageUrl");
        }

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

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

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

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

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

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

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

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

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

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

            for (String locale : categoryDTO.getListLocale()) {
                sbQuery.append(
                        " AND exists (select l.dish_group_id from dish_group_langage l where l.language_code = ? ");
                sbQuery.append(" AND l.dish_group_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 = getSession().createSQLQuery(sbQuery.toString());
    query.addScalar("id", StringType.INSTANCE);
    if (!isCount) {
        query.addScalar("name", StringType.INSTANCE);
        if (categoryDTO == null || !"1".equals(categoryDTO.getIsGetOnlyIdentified())) {
            query.addScalar("description", StringType.INSTANCE);
            query.addScalar("categoryStatus", StringType.INSTANCE);
            query.addScalar("imageId", StringType.INSTANCE);
            query.addScalar("imageUrl", StringType.INSTANCE);
        }
    }

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

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

    List<CategoryDTO> list = query.list();
    StringUtils.escapeHTMLString(list);
    return list;
}

From source file:com.eastcom.hrmis.modules.emp.dao.impl.EmployeeDaoImpl.java

@SuppressWarnings("unchecked")
@Override/*from  w  ww. j  a v  a2 s .c  om*/
public List<Map<String, Object>> getEmployeeDeptPostCount() {
    List<Map<String, Object>> result = Lists.newArrayList();
    String sqlString = " select EMPLOYEE_DEPT_ID,EMPLOYEE_POST_ID,count(*) from t_employee "
            + " where RECORD_STATUS = 1 AND AUDIT_STATUS = 2 AND HAS_QUIT_COMPANY = 0 "
            + " GROUP BY EMPLOYEE_DEPT_ID,EMPLOYEE_POST_ID ";
    SQLQuery sqlQuery = createSqlQuery(sqlString);
    List<Object[]> temp = sqlQuery.list();
    if (CollectionUtils.isNotEmpty(temp)) {
        for (Object[] objects : temp) {
            Map<String, Object> item = Maps.newHashMap();
            item.put("deptId", objects[0]);
            item.put("postId", objects[1]);
            item.put("count", objects[2]);
            result.add(item);
        }
    }
    return result;
}

From source file:com.edgenius.wiki.dao.hibernate.SpaceDAOHibernate.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<Resource> getSpacePageResources(final String spaceUname) {
    SQLQuery query = getCurrentSesssion().createSQLQuery(NATIVE_GET_PAGE_RESOURCE_BY_SPACE)
            .addEntity(Resource.class);
    query.setString("spaceUname", spaceUname);
    return query.list();
}