Spring Data Jpa自定义sql查询返回

1.最近用到Spring Data Jpa虽然简化了数据层的开发，提升了效率，但是面对业务复杂时，多表关联很复杂，不能满足需要，因此自己写了一个dao实现，通过传统的sql，返回自    定义的对象。

2.由于Spring Data Jpa的entity实体需要与数据库表关联，要是多表关联查询性能极差，而且关联关系也很复杂，不利于扩展维护。因此考虑自己定义实体对象，自定义的实体对    象不需要与session关联，虽然Spring Data Jpa支持自定义查询也可以使用原生SQL但是这样返回的是Object数据，查询出来的结果不好处理。于是看了下Spring Data JPA的SimpleJpaRepository类，因此考虑到可以自己定制。

3.自定义代码如下：

package com.dean.app.service.system;

import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.transaction.Transactional;

import org.apache.commons.beanutils.ConvertUtils;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

@Transactional
@Repository
public class CommonDao {
	
	@PersistenceContext
	EntityManager entityManager;
	
	/**
	 * * 查询数据集合
	 * @param sql 查询sql sql中的参数用:name格式 
	 * @param params 查询参数map格式，key对应参数中的:name
	 * @param clazz 实体类型为空则直接转换为map格式
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<?> queryListEntity(String sql,Map<String, Object> params, Class<?> clazz){
		Session session = entityManager.unwrap(org.hibernate.Session.class);
		SQLQuery query = session.createSQLQuery(sql);
		if (params != null) {
			for (String key : params.keySet()) {
				query.setParameter(key, params.get(key));
			}
		}
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String, Object>> result =  query.list();
		if (clazz != null) {
			List<Object>  entityList = convert(clazz, result);
			return entityList;
		}
		return result;
	}

	private List<Object> convert(Class<?> clazz, List<Map<String, Object>> list) {
		List<Object> result;
		if (CollectionUtils.isEmpty(list)) {
		    return null;
		}
		result = new ArrayList<Object>();
		try {
			 PropertyDescriptor[] props = Introspector.getBeanInfo(clazz).getPropertyDescriptors();
			 for (Map<String, Object> map : list) {
		         Object obj = clazz.newInstance();
		         for (String key:map.keySet()) {
		             String attrName = key.toLowerCase();
		             for (PropertyDescriptor prop : props) {
		            	 attrName = removeUnderLine(attrName);
		                 if (!attrName.equals(prop.getName())) {
		                     continue;
		                 }
		                 Method method = prop.getWriteMethod();
		                 Object value = map.get(key);
		                 if (value != null) {
		                     value = ConvertUtils.convert(value,prop.getPropertyType());
		                 }
		                 method.invoke(obj,value);
		             }
		         }
		         result.add(obj);
		     }
		} catch (Exception e) {
			throw new RuntimeException("数据转换错误");
		}
		return result;
	}

	private String removeUnderLine(String attrName) {
		//去掉数据库字段的下划线
		 if(attrName.contains("_")) {
			String[] names = attrName.split("_");
			String firstPart = names[0];
			String otherPart = "";
			for (int i = 1; i < names.length; i++) {
				String word = names[i].replaceFirst(names[i].substring(0, 1), names[i].substring(0, 1).toUpperCase());
				otherPart += word;
			}
			attrName = firstPart + otherPart;
		 }
		return attrName;
	}
	
	/**
	 * 获取记录条数
	 * @param sql
	 * @param params
	 * @return
	 */
	public Integer getCountBy(String sql,Map<String, Object> params){
		Query query =  entityManager.createNativeQuery(sql);
		if (params != null) {
			for (String key : params.keySet()) {
				query.setParameter(key, params.get(key));
			}
		}
		BigInteger bigInteger  = (BigInteger) query.getSingleResult();
		return bigInteger.intValue();
	}
	
	/**
	 * 新增或者删除
	 * @param sql
	 * @param params
	 * @return
	 */
	public Integer deleteOrUpDate(String sql,Map<String, Object> params){
		Query query =  entityManager.createNativeQuery(sql);
		if (params != null) {
			for (String key : params.keySet()) {
				query.setParameter(key, params.get(key));
			}
		}
		return query.executeUpdate();
	}
}

1.上面代码中getCountBy（）方法返回的integer，因为数据库返回的是BigInteger因此需要转化一下

2.还有数据中要是存在下划线的字段与实体对应时对应不上因此需要将下划线去掉并且下划线后面的字符串首字母需要转换为大写，比如数据库中字段为login_name而自己定义的实体对象为loginName，这里转换只是为了做到能够通用。不转化的时候只需要写SQL的时候取个别名和实体对应即可。

3.如果不需要转化成具体的实体可以直接使用List<Map<String,Object>>集合，也可以直接使用query.setResultTransformer(Transformers.toBean(clazz))转化为指定的实体，但是要是数据库中的某些字段为bigint(我使用的是mysql数据库)那么自己定义的实体的对应属性需要用BigInterger类型，否则会报类型转换错误。其他数据库没有测试过，对照着改就可以了。