Java SQLQuery.setResultTransformer方法代码示例

import org.hibernate.SQLQuery; //导入方法依赖的package包/类
@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;
}
 

import org.hibernate.SQLQuery; //导入方法依赖的package包/类
/**
 * 将field type 和 Hibernate的类型进行了对应。这里其实不是多余的，如果不进行一定的对应可能会有问题。 问题有两个： 1.
 * 在oracle中我们可能把一些字段设为NUMBER(%)，而在Bean中的字段定的是long。那么查询时可能会报：
 * java.math.BeigDecimal不能转换成long等错误 2.
 * 如果不这样写的话，可能Bean中的field就得是大写的，如：name就得写成NAME,userCount就得写成USERCOUNT
 * 这样是不是很扯(V_V)
 *
 * @param <T>
 * @param sqlQuery
 *            SQLQuery
 * @param clazz
 *            T.class
 */
public static <T> void addSclar(SQLQuery sqlQuery, Class<T> clazz) {
    if (clazz == null) {
        throw new NullPointerException("[clazz] could not be null!");
    }
    Field[] fields = clazz.getDeclaredFields();
    for (Field field : fields) {
        if ((field.getType() == long.class) || (field.getType() == Long.class)) {
            sqlQuery.addScalar(field.getName(), StandardBasicTypes.LONG);
        } else if ((field.getType() == int.class) || (field.getType() == Integer.class)) {
            sqlQuery.addScalar(field.getName(), StandardBasicTypes.INTEGER);
        } else if ((field.getType() == char.class) || (field.getType() == Character.class)) {
            sqlQuery.addScalar(field.getName(), StandardBasicTypes.CHARACTER);
        } else if ((field.getType() == short.class) || (field.getType() == Short.class)) {
            sqlQuery.addScalar(field.getName(), StandardBasicTypes.SHORT);
        } else if ((field.getType() == double.class) || (field.getType() == Double.class)) {
            sqlQuery.addScalar(field.getName(), StandardBasicTypes.DOUBLE);
        } else if ((field.getType() == float.class) || (field.getType() == Float.class)) {
            sqlQuery.addScalar(field.getName(), StandardBasicTypes.FLOAT);
        } else if ((field.getType() == boolean.class) || (field.getType() == Boolean.class)) {
            sqlQuery.addScalar(field.getName(), StandardBasicTypes.BOOLEAN);
        } else if (field.getType() == String.class) {
            sqlQuery.addScalar(field.getName(), StandardBasicTypes.STRING);
        } else if (field.getType() == Date.class) {
            sqlQuery.addScalar(field.getName(), StandardBasicTypes.TIMESTAMP);
        }
    }

    sqlQuery.setResultTransformer(Transformers.aliasToBean(clazz));
}
 

import org.hibernate.SQLQuery; //导入方法依赖的package包/类
/**
 * 根据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;
}
 

import org.hibernate.SQLQuery; //导入方法依赖的package包/类
@Override
public Map queryForMap(String querySql, Object... params) {
    Query query = entityManager.createNativeQuery(querySql);
    SQLQuery nativeQuery = query.unwrap(SQLQuery.class);
    nativeQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
    if (params != null) {
        for (int i = 0; i < params.length; i++) {
            nativeQuery.setParameter(i, params[i]);
        }
    }
    return (Map) nativeQuery.uniqueResult();
}
 

import org.hibernate.SQLQuery; //导入方法依赖的package包/类
@Override
public List<Map> queryForMapList(String querySql, Object... params) {
    Query query = entityManager.createNativeQuery(querySql);
    SQLQuery nativeQuery = query.unwrap(SQLQuery.class);
    nativeQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
    if (params != null) {
        for (int i = 0; i < params.length; i++) {
            nativeQuery.setParameter(i, params[i]);
        }
    }
    return nativeQuery.list();
}