jpa框架下复杂sql查询自定义的一个工具方法

最近做了一个项目，底层采用jpa与数据库交互，对单表查询非常方便，如果多表查询时那会非常的不方便。
于是自己写了一个工具方法。
本方法根据需要执行的sql语句，截取第一个select和from之间的字段，根据字段名，将查询的结果存入map集合中。或者根据传入的不同class类型，转为对象数据。
本方法支持：将查询sql转为map集合、将查询sql转为对象集合、将查询sql转为对象分页查询，但是暂不支持带星号（*）的查询。

import com.yscredit.loan.common.response.PageResponse;
import com.yscredit.loan.util.DateUtil;
import com.yscredit.loan.util.StringUtil;
import org.apache.poi.ss.formula.functions.T;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigInteger;
import java.util.*;

/**
 * Created by wxb on 2018/12/19.
 */
@Component
public class SelectModule {
    private final Logger logger = LoggerFactory.getLogger(getClass());

    @Autowired
    private EntityManager entityManager;

    /**
     * 查询一条记录
     */
    public Object selectOne(String sql) {
        sql=sql.replaceAll("\r|\n"," ");
        logger.info("--->>>>selectOne sql:"+sql.replaceAll("\r",""));
        if(StringUtil.isEmptyOrNull(sql))return null;
        Query query = entityManager.createNativeQuery(sql);
        List list= query.getResultList();
        if(list.isEmpty())return null;
        return list.get(0);
    }

    /**
     * 查询列表
     */
    public List selectList(String sql) {
        sql=sql.replaceAll("\r|\n"," ");
        logger.info("--->>>>selectList sql:"+sql);
        if(StringUtil.isEmptyOrNull(sql))return null;
        Query query = entityManager.createNativeQuery(sql);
        List list= query.getResultList();
        return list;
    }
    /**
     * 将查询sql转为map集合
     * @param sql
     * @return
     */
    public List<Map<String,Object>> selectToMap(String sql) {
        sql=sql.replaceAll("\r|\n"," ");
        logger.info("--->>>>select sql:"+sql);
        if(StringUtil.isEmptyOrNull(sql))return null;
        List<String> keyList=generateKeyList(sql);
        Query query = entityManager.createNativeQuery(sql);
        List<Object[]> list=query.getResultList();
        if(list.isEmpty())return null;
        List<Map<String,Object>> resultList=new ArrayList<>();
        if(keyList.size()==1){
            for(Object data:list){
                Map<String,Object> map=new HashMap<>();
                map.put(keyList.get(0),data);
                resultList.add(map);
            }
        }else{
            for(Object[] data:list){
                Map<String,Object> map=generateMapData(data,keyList);
                resultList.add(map);
            }
        }
        return resultList;
    }


    private Map<String,Object> generateMapData(Object[] data,List<String> keyList){
        Map<String,Object> map=new HashMap<>();
        for(int i=0;i<keyList.size();i++){
            map.put(keyList.get(i),data[i]);
        }
        return map;
    }
    //截取查询语句返回的列字段列表
    private List<String> generateKeyList(String sql){
        sql=deleteBracketsStr(sql);
        List<String> keyList=new ArrayList<>();
        sql=sql.replaceAll("SELECT","select");
        sql=sql.replaceAll("FROM","from");
        String columnStr=sql.substring(sql.indexOf("select")+6,sql.indexOf("from"));
//        columnStr=columnStr.replaceAll("\\([^\\(^\\)]*\\)","()");
        String[] strArray=columnStr.split(",");
        for(String str:strArray){
            String key=generateKey(str);
            keyList.add(key);
        }
        return keyList;
    }
    //删除sql语句中包含括号的那一段
    private String deleteBracketsStr(String sql){
        if(!sql.contains("("))return sql;
        char[] arr=sql.toCharArray();
        int index=0;
        StringBuilder sb=new StringBuilder();
        for(char c:arr){
            if(c=='('){
                index++;
            }else if(c==')'){
                index--;
            }else if(index==0){
                sb.append(c);
            }
        }
        return sb.toString();
    }
    private String generateKey(String str){
        if(str.contains(" as ")){
            str=str.substring(str.indexOf("as")+2);
        }else if(str.contains(".")){
            str=str.substring(str.indexOf(".")+1);
        }
        if(str.contains(" ")){
            String[] array=str.split(" ");
            for(int i=array.length-1;i>0;i--){
                String arr=array[i].trim();
                if(!arr.equals(" ")){
                    str=arr;
                    break;
                }
            }
        }
        str=str.trim();
        if(!str.contains("_")) return str;
        String[] strArray=str.split("_");
        StringBuffer resultStr=new StringBuffer();
        for(int i=0;i<strArray.length;i++){
            if(i==0){
                resultStr.append(strArray[i]);
                continue;
            }
            char[] ch = strArray[i].toCharArray();
            if (ch[0] >= 'a' && ch[0] <= 'z') {
                ch[0] = (char) (ch[0] - 32);
            }
            resultStr.append(new String(ch));
        }
        return resultStr.toString();
    }
    /**
     * 将查询sql转为对象集合
     */
    public <T> List<T> selectToObject(String sql, Class<T> tClass)  {
        sql=sql.replaceAll("\r|\n"," ");
        logger.info("--->>>>select sql:"+sql);
        if(StringUtil.isEmptyOrNull(sql))return null;
        List<String> keyList=generateKeyList(sql);
        Query query = entityManager.createNativeQuery(sql);
        List<Object[]> list= query.getResultList();
        if(list.isEmpty())return null;
        List<T> resultList=new ArrayList<>();
        try {
            if(keyList.size()==1){
                for(Object data:list){
                    Map<String,Object> map=new HashMap<>();
                    map.put(keyList.get(0),data);
                    T obj = tClass.newInstance();
                    writeDataHandle(map,obj,tClass);
                    resultList.add(obj);
                }
            }else{
                for(Object[] data:list){
                    Map<String,Object> map=generateMapData(data,keyList);
                    T obj = tClass.newInstance();
                    writeDataHandle(map,obj,tClass);
                    resultList.add(obj);
                }
            }
            return resultList;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }
    //写入数据
    private void writeDataHandle(Map<String,Object> map,Object obj,Class clazz) throws IntrospectionException, IllegalAccessException, InstantiationException, InvocationTargetException {
        Field[] fields = clazz.getDeclaredFields();
        //写数据
        for(Field f : fields) {
            String fileName=f.getName();
            if(!map.containsKey(fileName))continue;
            PropertyDescriptor pd = new PropertyDescriptor(f.getName(), clazz);
            Method wM = pd.getWriteMethod();//获得写方法
            Object mapObj=map.get(fileName);
            if(f.getType()==String.class && mapObj instanceof Date ){
                String date= DateUtil.formatDateToString((Date)mapObj,"yyyy-MM-dd HH:mm:ss");
                map.put(fileName,date);
            }
            if(f.getType()==Date.class && mapObj instanceof String){
                Date date=DateUtil.getFormatDate((String)mapObj,"yyyy-MM-dd HH:mm:ss");
                map.put(fileName,date);
            }
            if(f.getType()==Integer.class && mapObj instanceof BigInteger){
                map.put(fileName,Integer.valueOf(mapObj.toString()));
            }
            if(map.get(fileName)!=null){
                Object value=map.get(fileName);
                wM.invoke(obj, value);//实际情况中需要判断下他的参数类型
            }
        }
    }
    /**
     * 将查询sql转为对象分页查询
     */
    public PageResponse selectByPage(String sql, Class<T> tClass)  {
        return selectByPage(sql,tClass,1,10);
    }
    public PageResponse selectByPage(String sql, Class tClass, Integer pageNum, Integer pageSize)  {
        if(StringUtil.isEmptyOrNull(sql))return null;
        pageNum=pageNum==null ||pageNum<=0 ? 1:pageNum;
        pageSize=pageSize==null || pageSize<=0 ?10:pageSize;
        String listSql=sql+" limit "+pageSize * (pageNum-1)+","+pageSize;
        List<T> list=selectToObject(listSql,tClass);
        String countSql="select count(*) "+sql.substring(sql.indexOf("from"));
        Object total=selectOne(countSql);//总的记录条数
        PageResponse pageResponse = new PageResponse(pageSize, pageNum, Integer.parseInt(total.toString()));
        pageResponse.setList(list);
        return pageResponse;
    }

}