Example usage for org.hibernate SQLQuery addScalar

List of usage examples for org.hibernate SQLQuery addScalar

Introduction

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

Prototype

SQLQuery<T> addScalar(String columnAlias, Type type);

Source Link

Document

Declare a scalar query result.

Usage

From source file:com.cms.dao.TaxAuthorityDAO.java

License:Open Source License

public List<TaxAuthorityDTO> getListTaxAuthorityFromMineNameAndStaffCodeAndProvider(String mineName,
        String staffCode, String provider, String status) {
    if (DataUtil.isStringNullOrEmpty(staffCode)) {
        return getListTaxAuthorityFromMineNameAndProvider(mineName, provider, status);
    }//from w  ww  .  j  av  a2  s.  c o  m
    List<TaxAuthorityDTO> lstTaxAuthorities = null;
    StringBuilder sqlQuery = new StringBuilder();
    sqlQuery.append("              SELECT ID id,   ");
    sqlQuery.append("                MA_CQT maCqt,  ");
    sqlQuery.append("                MA_QUAN_HUYEN maQuanHuyen,  ");
    sqlQuery.append("                MA_TINH maTinh,  ");
    sqlQuery.append("                STATUS status,  ");
    sqlQuery.append("                TEN_CQT tenCqt  ");
    sqlQuery.append("              FROM TAX_AUTHORITY  ");
    sqlQuery.append("              WHERE MA_CQT IN  ");
    sqlQuery.append("                ( SELECT DISTINCT cs.TAX_AUTHORITY  ");
    sqlQuery.append("                FROM customer_status cs  ");
    if (!DataUtil.isStringNullOrEmpty(provider) || !DataUtil.isStringNullOrEmpty(mineName)) {
        sqlQuery.append("            JOIN term_information ti ON ti.TAX_CODE = cs.TAX_CODE ");
    }
    sqlQuery.append("                WHERE 1=1 ");
    if (!DataUtil.isStringNullOrEmpty(mineName)) {
        if (mineName.contains(",")) {
            sqlQuery.append("            AND cs.mine_name IN (:mineName)  ");
        } else {
            sqlQuery.append("            AND cs.mine_name = :mineName  ");
        }
    }
    if (!DataUtil.isStringNullOrEmpty(staffCode)) {
        sqlQuery.append("                AND cs.staff_code  = :staffCode ");
    }
    if (!DataUtil.isStringNullOrEmpty(provider)) {
        sqlQuery.append("            AND lower(ti.provider) = ANY (:provider)  ");
    }
    if (!DataUtil.isStringNullOrEmpty(status)) {
        sqlQuery.append("            AND cs.status = ANY (:status)  ");
    }
    sqlQuery.append("             ) ORDER BY maCqt");
    SQLQuery query;
    try {
        System.out.println(sqlQuery.toString());
        query = getSession().createSQLQuery(sqlQuery.toString());
        //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong            
        query.setResultTransformer(Transformers.aliasToBean(TaxAuthorityDTO.class));
        query.addScalar("id", new StringType());
        query.addScalar("maCqt", new StringType());
        query.addScalar("maQuanHuyen", new StringType());
        query.addScalar("maTinh", new StringType());
        query.addScalar("status", new StringType());
        query.addScalar("tenCqt", new StringType());

        if (!DataUtil.isStringNullOrEmpty(mineName)) {
            if (mineName.contains(",")) {
                query.setParameterList("mineName", DataUtil.parseInputListString(mineName));
            } else {
                query.setParameter("mineName", mineName);
            }
        }
        if (!DataUtil.isStringNullOrEmpty(provider)) {
            query.setParameterList("provider", DataUtil.parseInputListString(provider.toLowerCase()));
        }
        if (!DataUtil.isStringNullOrEmpty(status)) {
            query.setParameterList("status", DataUtil.parseInputListString(status));
        }
        if (!DataUtil.isStringNullOrEmpty(staffCode)) {
            query.setParameter("staffCode", staffCode);
        }

        lstTaxAuthorities = query.list();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return lstTaxAuthorities;
}

From source file:com.cms.dao.TaxAuthorityDAO.java

License:Open Source License

public List<TaxAuthorityDTO> getListTaxAuthorityFromMineName(String mineName) {
    List<TaxAuthorityDTO> lstTaxAuthorities = null;
    StringBuilder sqlQuery = new StringBuilder();
    sqlQuery.append("              SELECT ID id,   ");
    sqlQuery.append("                MA_CQT maCqt,  ");
    sqlQuery.append("                MA_QUAN_HUYEN maQuanHuyen,  ");
    sqlQuery.append("                MA_TINH maTinh,  ");
    sqlQuery.append("                STATUS status,  ");
    sqlQuery.append("                TEN_CQT tenCqt  ");
    sqlQuery.append("              FROM TAX_AUTHORITY  ");
    sqlQuery.append("              WHERE MA_CQT IN  ");
    sqlQuery.append("                ( SELECT DISTINCT cs.TAX_AUTHORITY  ");
    sqlQuery.append(/*from   w  w  w  .ja v  a2s  .  com*/
            "                FROM CUSTOMER cs INNER JOIN TERM_INFORMATION ti ON ti.TAX_CODE = cs.TAX_CODE ");
    sqlQuery.append("                WHERE 1=1 AND ti.IS_CONTACT_INFO is NULL ");
    if (!DataUtil.isStringNullOrEmpty(mineName)) {
        if (mineName.contains(",")) {
            sqlQuery.append("            AND ti.mine_name IN (:mineName)  ");
        } else {
            sqlQuery.append("            AND ti.mine_name = :mineName  ");
        }
    }
    sqlQuery.append("             ) ORDER BY maCqt");
    SQLQuery query;
    try {
        query = getSession().createSQLQuery(sqlQuery.toString());
        //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong            
        query.setResultTransformer(Transformers.aliasToBean(TaxAuthorityDTO.class));
        query.addScalar("id", new StringType());
        query.addScalar("maCqt", new StringType());
        query.addScalar("maQuanHuyen", new StringType());
        query.addScalar("maTinh", new StringType());
        query.addScalar("status", new StringType());
        query.addScalar("tenCqt", new StringType());

        if (!DataUtil.isStringNullOrEmpty(mineName)) {
            if (mineName.contains(",")) {
                query.setParameterList("mineName", DataUtil.parseInputListString(mineName));
            } else {
                query.setParameter("mineName", mineName);
            }
        }
        lstTaxAuthorities = query.list();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return lstTaxAuthorities;
}

From source file:com.cms.dao.TaxAuthorityDAO.java

License:Open Source License

public List<TaxAuthorityDTO> getListTaxAuthorityFromMineNameAndProvider(String mineName, String provider,
        String status) {/*  w w w .  j  a v a2  s.  c o m*/
    List<TaxAuthorityDTO> lstTaxAuthorities = null;
    StringBuilder sqlQuery = new StringBuilder();
    sqlQuery.append("              SELECT ID id,   ");
    sqlQuery.append("                MA_CQT maCqt,  ");
    sqlQuery.append("                MA_QUAN_HUYEN maQuanHuyen,  ");
    sqlQuery.append("                MA_TINH maTinh,  ");
    sqlQuery.append("                STATUS status,  ");
    sqlQuery.append("                TEN_CQT tenCqt  ");
    sqlQuery.append("              FROM TAX_AUTHORITY  ");
    sqlQuery.append("              WHERE MA_CQT IN  ");
    sqlQuery.append("                ( SELECT DISTINCT c.TAX_AUTHORITY  ");
    sqlQuery.append("                FROM CUSTOMER c ");
    if (!DataUtil.isStringNullOrEmpty(provider)) {
        sqlQuery.append("                INNER JOIN TERM_INFORMATION ti ON ti.TAX_CODE = c.TAX_CODE ");
    }
    if (!DataUtil.isStringNullOrEmpty(mineName) || !DataUtil.isStringNullOrEmpty(status)) {
        sqlQuery.append("                INNER JOIN CUSTOMER_STATUS cs ON c.TAX_CODE = cs.TAX_CODE ");
    }
    sqlQuery.append("                   WHERE 1=1 ");
    if (!DataUtil.isStringNullOrEmpty(provider)) {
        sqlQuery.append("                   AND ti.IS_CONTACT_INFO is NULL ");
    }
    if (!DataUtil.isStringNullOrEmpty(mineName)) {
        if (mineName.contains(",")) {
            sqlQuery.append("                   AND cs.mine_name IN (:mineName)  ");
        } else {
            sqlQuery.append("                   AND cs.mine_name = :mineName  ");
        }
    }
    if (!DataUtil.isStringNullOrEmpty(provider)) {
        sqlQuery.append("                   AND lower(ti.provider) = ANY (:provider)  ");
    }
    if (!DataUtil.isStringNullOrEmpty(status)) {
        sqlQuery.append("                   AND cs.STATUS = :status  ");
    }
    sqlQuery.append("                ) ORDER BY maCqt");
    SQLQuery query;
    try {
        query = getSession().createSQLQuery(sqlQuery.toString());
        //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong            
        query.setResultTransformer(Transformers.aliasToBean(TaxAuthorityDTO.class));
        query.addScalar("id", new StringType());
        query.addScalar("maCqt", new StringType());
        query.addScalar("maQuanHuyen", new StringType());
        query.addScalar("maTinh", new StringType());
        query.addScalar("status", new StringType());
        query.addScalar("tenCqt", new StringType());

        if (!DataUtil.isStringNullOrEmpty(mineName)) {
            if (mineName.contains(",")) {
                query.setParameterList("mineName", DataUtil.parseInputListString(mineName));
            } else {
                query.setParameter("mineName", mineName);
            }
        }
        if (!DataUtil.isStringNullOrEmpty(provider)) {
            query.setParameterList("provider", DataUtil.parseInputListString(provider.toLowerCase()));
        }
        if (!DataUtil.isStringNullOrEmpty(status)) {
            query.setParameter("status", status);
        }
        lstTaxAuthorities = query.list();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return lstTaxAuthorities;
}

From source file:com.corundumstudio.core.extensions.hibernate.InsertDeleteListResultTest.java

License:Apache License

private void checkQueryResult(SimpleEntity entity, List expected) {
    Session session = sessionFactory.openSession();
    SQLQuery query = session.createSQLQuery("SELECT id FROM SimpleEntity WHERE phone = :phone");
    query.addScalar("id", LongType.INSTANCE);
    query.setCacheable(true);//from  w  ww  .j av  a2  s .c om
    query.setCacheRegion(cacheRegion);
    query.setParameter("phone", entity.getPhone());
    List res = query.list();
    Assert.assertEquals(expected, res);
    session.close();
}

From source file:com.corundumstudio.core.extensions.hibernate.InsertDeleteListResultTest.java

License:Apache License

private List listQueryResult() {
    Session session = sessionFactory.openSession();
    SQLQuery query = session.createSQLQuery("SELECT id FROM SimpleEntity WHERE address = :address");
    query.addScalar("id", LongType.INSTANCE);
    query.setCacheable(true);/*from  w  w  w  .ja  v  a  2  s .  c  o m*/
    query.setCacheRegion(multiCacheRegion);
    query.setParameter("address", addressValue);
    List res = query.list();
    session.close();
    return res;
}

From source file:com.corundumstudio.core.extensions.hibernate.InsertDeleteUniqueResultTest.java

License:Apache License

private void checkQueryResult(SimpleEntity entity, Long expected) {
    Session session = sessionFactory.openSession();
    SQLQuery query = session.createSQLQuery("SELECT id FROM SimpleEntity WHERE phone = :phone");
    query.addScalar("id", LongType.INSTANCE);
    query.setCacheable(true);//from  ww w  .java 2 s  .  c o  m
    query.setCacheRegion(cacheRegion);
    query.setParameter("phone", entity.getPhone());
    Long res = (Long) query.uniqueResult();
    Assert.assertEquals(expected, res);
    session.close();
}

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) {/*w  w  w . j av a  2 s .  c o  m*/
    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();
}