Java Code Examples for org.hibernate.SQLQuery#list()

@Override
   public List<Policy> getAllPoliciesWithUserConsentsCount() {
final String LOAD_POLICIES_WITH_CONSENTS_COUNT = "SELECT policy.*, COUNT(policyConsent.uid) AS userConsentsCount "
	+ "FROM lams_policy AS policy "
	+ "LEFT JOIN lams_policy_consent AS policyConsent ON policyConsent.policy_uid = policy.uid "
	+ "GROUP BY policy.uid ORDER BY policy.last_modified ASC";

SQLQuery query = getSession().createSQLQuery(LOAD_POLICIES_WITH_CONSENTS_COUNT);
query.addEntity(Policy.class);
query.addScalar("userConsentsCount");
List<Object[]> resultQuery = query.list();

// this map keeps the insertion order
LinkedList<Policy> policies = new LinkedList<Policy>();
// make the result easier to process
for (Object[] entry : resultQuery) {
    Policy policy = (Policy) entry[0];
    int userConsentsCount = ((Number) entry[1]).intValue();
    policy.setUserConsentsCount(userConsentsCount);

    policies.add(policy);
}
return policies;
   }
 

public <N extends Object> List<N> listBySql(String sql, Object[] args, Map<String, Object> alias, Class<?> clz,
        boolean hasEntiry) {
    sql = initSort(sql);
    SQLQuery sq = getSession().createSQLQuery(sql);
    setAliasParameter(sq, alias);
    setParameter(sq, args);
    if (hasEntiry) {

        sq.addEntity(clz);

    } else {

        sq.setResultTransformer(Transformers.aliasToBean(clz));
    }

    return sq.list();
}
 

public <N extends Object>Pager<N> findBySql(String sql, Object[] args,
		Map<String, Object> alias, Class<?> clz, boolean hasEntity) {
	sql = initSort(sql);
	String cq = getCountHql(sql,false);
	SQLQuery sq = getSession().createSQLQuery(sql);
	SQLQuery cquery = getSession().createSQLQuery(cq);
	setAliasParameter(sq, alias);
	setAliasParameter(cquery, alias);
	setParameter(sq, args);
	setParameter(cquery, args);
	Pager<N> pages = new Pager<N>();
	setPagers(sq, pages);
	if(hasEntity) {
		sq.addEntity(clz);
	} else {
		sq.setResultTransformer(Transformers.aliasToBean(clz));
	}
	List<N> datas = sq.list();
	pages.setDatas(datas);
	long total = ((BigInteger)cquery.uniqueResult()).longValue();
	pages.setTotal(total);
	return pages;
}
 

@Override
public List<ForfeitInfo> getForfeitByReader(Reader reader) {
	// TODO Auto-generated method stub
	String hql = "SELECT f.borrowId,f.forfeit,f.isPay,f.aid FROM forfeitinfo  f,borrowinfo  b where  b.borrowId = f.borrowId and b.readerId =?";
	List list = null;
	try{
		Session session = this.getSession();
		SQLQuery createSQLQuery = session.createSQLQuery(hql);
		createSQLQuery.setInteger(0, reader.getReaderId());
		list = createSQLQuery.list();
		if(list!=null){
			List<ForfeitInfo> infos = new ArrayList<ForfeitInfo>();
			for(int i = 0;i<list.size();i++){
				Object[] objects = (Object[]) list.get(i);
				Integer borrowId = (Integer) objects[0];
				Double forfeit = (Double) objects[1];
				Integer isPay = (Integer) objects[2];
				Integer aid = (Integer) objects[3];
				Admin admin = new Admin();
				admin.setAid(aid);
				BorrowInfo info = new BorrowInfo();
				info.setBorrowId(borrowId);
				ForfeitInfo forfeitInfo = new ForfeitInfo();
				forfeitInfo.setAdmin(admin);
				forfeitInfo.setBorrowId(borrowId);
				forfeitInfo.setForfeit(forfeit);
				forfeitInfo.setIsPay(isPay);
				infos.add(forfeitInfo);
			}
			return infos;
		}
	}catch (Throwable e) {
		e.printStackTrace();
		throw new RuntimeException(e.getMessage());
	}
	return null;
}
 

@SuppressWarnings("unchecked")
public <T> List<T> queryList(Class<T> clazz, String sql, Object... args) {
	SQLQuery query = getSession().createSQLQuery(sql);
	query.addEntity(clazz);
	for(int i = 0; i < args.length; i++) {
		query.setParameter(i, args[i]);
	}
	return (List<T>)query.list();
}
 

public <N extends Object> Pager<N> findBySql(String sql, Object[] args, Map<String, Object> alias, Class<?> clz,
        boolean hasEntiry) {

    sql = initSort(sql);
    String cq = getCountHql(sql, false);
    // cq=initSort(cq);不需要再加上就多了一个Order

    SQLQuery sq = getSession().createSQLQuery(sql);
    SQLQuery cquery = getSession().createSQLQuery(cq);
    setAliasParameter(sq, alias);
    setAliasParameter(cquery, alias);
    setParameter(sq, args);
    setParameter(cquery, args);
    Pager<N> pages = new Pager<N>();
    setPagers(sq, pages);
    if (hasEntiry) {

        sq.addEntity(clz);
    } else {
        sq.setResultTransformer(Transformers.aliasToBean(clz));
    }
    List<N> datas = sq.list();
    pages.setDatas(datas);
    long total = ((BigInteger) cquery.uniqueResult()).longValue();
    pages.setTotal(total);

    return pages;

}
 

/**
 * Description: <br>
 * 
 * @author 王伟<br>
 * @taskId <br>
 * @param procedureSql
 * @param params
 * @return
 * @throws DaoException <br>
 */

@Override
public <T> List<T> executeProcedure(final String procedureSql, final Object... params) throws DaoException {
    Session session = getSession();
    session.flush();
    SQLQuery sqlQuery = session.createSQLQuery(procedureSql);

    for (int i = 0; i < params.length; i++) {
        sqlQuery.setParameter(i, params[i]);
    }
    return sqlQuery.list();
}
 

public <N extends Object>List<N> listBySql(String sql, Object[] args,
		Map<String, Object> alias, Class<?> clz, boolean hasEntity) {
	sql = initSort(sql);
	SQLQuery sq = getSession().createSQLQuery(sql);
	setAliasParameter(sq, alias);
	setParameter(sq, args);
	if(hasEntity) {
		sq.addEntity(clz);
	} else 
		sq.setResultTransformer(Transformers.aliasToBean(clz));
	return sq.list();
}
 

@SuppressWarnings("unchecked")
public List<Object[]> findObjectsBySql(String sql, Map<String, Object> map) {
	System.out.println("sql->" + sql);
	SQLQuery query = this.hibernateTemplate.getSessionFactory()
			.getCurrentSession().createSQLQuery(sql);
	setQuery(map, query);
	return query.list();
}
 

@SuppressWarnings("unchecked")
public List findBySql(String sql, Object[] params,
		PageBean pageBean, String order) {
	sql += (StringUtils.isEmpty(order)) ? "" : " order by " + order;
	System.out.println("sql->" + sql);
	SQLQuery query = hibernateTemplate.getSessionFactory()
			.getCurrentSession().createSQLQuery(sql);
	setPage(query, pageBean);
	setQuery(params, query);
	return query.list();
}
 

@SuppressWarnings("unchecked")
public List<T> findBySql(String sql, Map<String, Object> map,
		PageBean pageBean, String order) {
	//sql = filter(sql, map);
	sql += (StringUtils.isEmpty(order)) ? "" : " order by " + order;
	System.out.println("sql->" + sql);
	SQLQuery query = hibernateTemplate.getSessionFactory()
			.getCurrentSession().createSQLQuery(sql);
	setPage(query, pageBean);
	setQuery(map, query);
	return query.list();
}
 

public JoinType recognizeJoinType(Session session) {
    SQLQuery sqlQuery = session.createSQLQuery(NativeQueryUtil.CHECK_TESTCASE_TAGS_QUERY);
    List list = sqlQuery.list();
    if (list == null || list.isEmpty()) {
        return JoinType.MATRIX_RUN_TAGS;
    }
    sqlQuery = session.createSQLQuery(NativeQueryUtil.CHECK_MATRIX_TAGS_QUERY);
    list = sqlQuery.list();
    if (list == null || list.isEmpty()) {
        return  JoinType.TEST_CASE_RUN_TAGS;
    }
    return JoinType.TEST_CASE_AND_MATRIX_RUN_TAGS;
}
 

@Override
public List loadListMetaModelDomainsByRole(Integer roleId) throws SpagoBIRuntimeException {

	Session aSession = null;
	Transaction tx = null;

	List realResult = new ArrayList();
	try {
		aSession = getSession();
		tx = aSession.beginTransaction();
		SQLQuery query = aSession.createSQLQuery("select category_id from SBI_EXT_ROLES_CATEGORY where ext_role_id=" + roleId);
		List hibList = query.list();

		Iterator it = hibList.iterator();

		while (it.hasNext()) {
			// realResult.add(toDomain((SbiDomains) it.next()));
			Integer categoryId = Integer.getInteger(it.next().toString());
			realResult.add(categoryId);
		}
		tx.commit();
	} catch (HibernateException he) {
		logException(he);

		if (tx != null)
			tx.rollback();

		throw new SpagoBIRuntimeException(he.getMessage());

	} finally {
		if (aSession != null) {
			if (aSession.isOpen())
				aSession.close();
		}
	}

	return realResult;

}
 

/**
 * 根据SQL语句查询分页
 * @param sql
 * @param currentPage
 * @param pageSize
 * @return
 */
@SuppressWarnings({ "rawtypes", "unchecked" })
public PageModel queryForPageBySql(String sql,Integer currentPage,Integer pageSize){
	PageModel page = new PageModel();
	Integer totalCount = 0;
	Integer totalPage = 0;
	
	//EntityManager em = this.emf.createEntityManager();
	Query qry = em.createNativeQuery(sql);
	Query qry2 = em.createNativeQuery(sql);
	SQLQuery s = qry.unwrap(SQLQuery.class);
	if (currentPage != null && pageSize != null) {//判断是否有分页
		// 起始对象位置
		qry.setFirstResult(pageSize * (currentPage - 1));
		// 查询对象个数
		qry.setMaxResults(pageSize);
	}
	s.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
	List<Map> resultList=new ArrayList<Map>();
	List list = qry2.getResultList();
	totalCount = (list == null ? 0 : list.size());
	if(totalCount % pageSize == 0){
		totalPage = totalCount/pageSize;
	}else{
		totalPage = totalCount/pageSize + 1;
	}
	try {
		resultList=s.list();
		page.setCurrentPage(currentPage);
		page.setPageSize(pageSize);
		page.setList(resultList);
		page.setTotalCount(totalCount);
		page.setTotalPage(totalPage);
	} catch (Exception e) {
		e.printStackTrace();
	}finally{
		em.close();
	}		
	return page;
}
 

@SuppressWarnings("unchecked")
public List<Map<String, Object>> querySqlObjects(String sql, Object params, Integer currentPage,Integer rowsInPage){
	Query qry = em.createNativeQuery(sql);
	SQLQuery s = qry.unwrap(SQLQuery.class);
	
	//设置参数
	if(params != null){
		if(params instanceof List){
			List<Object> paramList = (List<Object>) params;
			for(int i = 0, size = paramList.size(); i < size; i++){
				qry.setParameter(i+1, paramList.get(i));
			}
		}else if(params instanceof Map){
			Map<String, Object> paramMap = (Map<String, Object>) params;
			for(String key : paramMap.keySet()){
				qry.setParameter(key, paramMap.get(key));
			}
		}
	}
	
	if (currentPage != null && rowsInPage != null) {//判断是否有分页
		// 起始对象位置
		qry.setFirstResult(rowsInPage * (currentPage - 1));
		// 查询对象个数
		qry.setMaxResults(rowsInPage);
	}
	s.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
	List<Map<String, Object>> resultList=new ArrayList<Map<String, Object>>();
	try {
		resultList=s.list();
	} catch (Exception e) {
	}finally{
		em.close();
	}
	return resultList;
}
 

@Override
   public List<PolicyDTO> getPolicyDtosByUser(Integer userId) {
final String LOAD_POLICIES_WITH_USER_CONSENTS = "SELECT policy.*, policyConsent.uid IS NOT NULL as isConsentedByUser, policyConsent.date_agreed_on as dateAgreedOn "
	+ "FROM lams_policy AS policy "
	+ "LEFT JOIN lams_policy_consent AS policyConsent ON policyConsent.policy_uid = policy.uid AND policyConsent.user_id = :userId "
	+ "WHERE policy.policy_state_id=1";

SQLQuery query = getSession().createSQLQuery(LOAD_POLICIES_WITH_USER_CONSENTS);
query.addEntity(Policy.class);
query.addScalar("isConsentedByUser");
query.addScalar("dateAgreedOn");
query.setInteger("userId", userId);
List<Object[]> resultQuery = query.list();

// this map keeps the insertion order
LinkedList<PolicyDTO> policyDtos = new LinkedList<PolicyDTO>();
// make the result easier to process
for (Object[] entry : resultQuery) {
    Policy policy = (Policy) entry[0];

    PolicyDTO policyDto = new PolicyDTO(policy);

    boolean isConsentedByUser = ((Number) entry[1]).intValue() == 1;
    policyDto.setConsentedByUser(isConsentedByUser);

    Date dateAgreedOn = (Date) entry[2];
    policyDto.setDateAgreedOn(dateAgreedOn);

    policyDtos.add(policyDto);
}
return policyDtos;
   }
 

@Override
public boolean isMigrationNeeded(Session session, String dbName) {
    SQLQuery conditionalSqlQuery = session.createSQLQuery(POSTGRES_CHECK_MIGRATION_QUERY);
    List<?> conditionalSqlQueryResult = conditionalSqlQuery.list();
    if (CollectionUtils.isEmpty(conditionalSqlQueryResult)) {
        return false;
    }
    return !StringUtils.equalsIgnoreCase("text", (String)conditionalSqlQueryResult.get(0));
}
 

@Override
public boolean isMigrationNeeded(Session session, String dbName) {
    SQLQuery conditionalSqlQuery = session.createSQLQuery(MYSQL_CHECK_MIGRATION_QUERY);
    conditionalSqlQuery.setParameter(0, dbName);
    List<?> conditionalSqlQueryResult = conditionalSqlQuery.list();
    if (CollectionUtils.isEmpty(conditionalSqlQueryResult)) {
        return false;
    }
    return !StringUtils.equalsIgnoreCase("longblob", (String)conditionalSqlQueryResult.get(0));
}
 

@Override
public PageBean<Integer> getBorrowIdList(String iSBN, String paperNO,int borrowId,int pageCode, int pageSize) {
	PageBean<Integer> pb = new PageBean<Integer>();	//pageBean对象，用于分页
	//根据传入的pageCode当前页码和pageSize页面记录数来设置pb对象
	pb.setPageCode(pageCode);//设置当前页码
	pb.setPageSize(pageSize);//设置页面记录数
	
	List<Integer> integers = new ArrayList<Integer>();
	StringBuilder sb = new StringBuilder();
	StringBuilder sb_sql = new StringBuilder();
	String sql = "select count(*) from BackInfo ba ,BorrowInfo bo,Book bk,Reader r "
			+"where ba.borrowId=bo.borrowId and Bk.bookId=Bo.bookId and bo.readerId=r.readerId ";
	//不支持limit分页
	String hql= "select ba.borrowId from BackInfo ba ,BorrowInfo bo,Book bk,Reader r "
			+"where ba.borrowId=bo.borrowId and Bk.bookId=Bo.bookId and bo.readerId=r.readerId ";
	sb.append(hql);
	sb_sql.append(sql);
	if(!"".equals(iSBN.trim())){
		sb.append(" and bk.ISBN like '%" + iSBN +"%'");
		sb_sql.append(" and bk.ISBN like '%" + iSBN +"%'");
	}
	if(!"".equals(paperNO.trim())){
		sb.append(" and r.paperNO like '%" + paperNO +"%'");
		sb_sql.append(" and r.paperNO like '%" + paperNO +"%'");
	}
	if(borrowId!=0){
		sb.append(" and bo.borrowId like '%" + borrowId +"%'");
		sb_sql.append(" and bo.borrowId like '%" + borrowId +"%'");
	}
	
	try {
		SQLQuery createSQLQuery1 = this.getSession().createSQLQuery(sb_sql.toString());
		List list = createSQLQuery1.list();
		int totalRecord = Integer.parseInt(list.get(0).toString()); //得到总记录数
		pb.setTotalRecord(totalRecord);	//设置总记录数
		this.getSession().close();
		
		//不支持limit分页
		//分页查询
		List list2 = doLimitBackInfo(sb.toString(),pageCode,pageSize);
		for(Object object : list2){
			Integer i = new Integer(object.toString());
			integers.add(i);
		}
		pb.setBeanList(integers);
	}catch (Throwable e1) {
		e1.printStackTrace();
		throw new RuntimeException(e1.getMessage());
	}
	

	return pb;
}
 

@SuppressWarnings({ "rawtypes", "unchecked" })
public PageModel queryForPageBySql(String sql,Map<String,Object> params,Integer currentPage,Integer pageSize){
	PageModel page = new PageModel();
	Integer totalCount = 0;
	Integer totalPage = 0;
	
	Query qry = em.createNativeQuery(sql);
	Query qry2 = em.createNativeQuery(sql);
	
	for(Map.Entry<String,Object> entry: params.entrySet()){
		qry.setParameter(entry.getKey(), entry.getValue());
		qry2.setParameter(entry.getKey(), entry.getValue());
	}
	
	SQLQuery s = qry.unwrap(SQLQuery.class);
	if (currentPage != null && pageSize != null) {//判断是否有分页
		// 起始对象位置
		qry.setFirstResult(pageSize * (currentPage - 1));
		// 查询对象个数
		qry.setMaxResults(pageSize);
	}
	s.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
	List<Map> resultList=new ArrayList<Map>();
	List list = qry2.getResultList();
	totalCount = (list == null ? 0 : list.size());
	if(totalCount % pageSize == 0){
		totalPage = totalCount/pageSize;
	}else{
		totalPage = totalCount/pageSize + 1;
	}
	try {
		resultList=s.list();
		page.setCurrentPage(currentPage);
		page.setPageSize(pageSize);
		page.setList(resultList);
		page.setTotalCount(totalCount);
		page.setTotalPage(totalPage);
	} catch (Exception e) {
		e.printStackTrace();
	}finally{
		em.close();
	}		
	return page;
}