List of usage examples for org.hibernate SQLQuery addScalar
SQLQuery<T> addScalar(String columnAlias, Type type);
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(); }