Spring Data JPA映射自定义实体类

这个问题困扰了我2天=-=，好像也能使用 jpql解决

先说下自己的功能：查询oracle最近sql执行记录

sql很简单：【如果需要分页，需要自己手动分页，因为你使用分页工具他第一页查询不会查询rownum，第二页查询就会查询rownum，然而这个返回的List<Object[]>中的参数必须要和实体类中一一对应，所以这就有一个不可控制的属性rownum，所以我们不能使用Pageable入参接口了，需要自定义pageSize pageNum参数】

SELECT
	t.SQL_ID AS SQL的ID,
	t.SQL_TEXT AS SQL语句,
	t.HASH_VALUE AS 完整SQL哈希值,
	t.ELAPSED_TIME AS 解析执行总共时间微秒,
	t.EXECUTIONS AS 执行总共次数,
	t.LAST_ACTIVE_TIME AS 执行最后时间,
	t.CPU_TIME AS CPU执行时间微秒 
FROM 
	v$sqlarea t 
WHERE 
	t.PARSING_SCHEMA_NAME IN ( 'C##DBAAS' ) 
	AND t.EXECUTIONS > 10  
	AND t.LAST_ACTIVE_TIME > TO_DATE('0001-01-01 01:01:01', 'yyyy-MM-dd hh24:mi:ss') 
	AND t.ELAPSED_TIME > 0 
	AND t.CPU_TIME > 0 
ORDER BY 
	t.EXECUTIONS DESC;

但是我用的是Spring Data JPA。。。。这个网上说不能将查询结果自动映射到自定义的实体类。。。。这就比较蛋疼了，在网上就找了个轮子。先上一下自己的Dao层，查出来的是集合数组，所以使用List< Object [ ] >接收【我将sql简化了一下，主要先测试能不能成功】

@Query(value="SELECT\r\n" + 
			"	t.SQL_ID,\r\n" + 
			"	t.ELAPSED_TIME,\r\n" + 
			"	t.EXECUTIONS,\r\n" + 
			"	t.LAST_ACTIVE_TIME, \r\n" + 
			"	t.CPU_TIME \r\n" + 
			"FROM\r\n" + 
			"	v$sqlarea t \r\n" + 
			"WHERE\r\n" + 
			"	t.PARSING_SCHEMA_NAME IN ( 'C##DBAAS' )   AND t.EXECUTIONS > 100     \r\n" + 
			"ORDER BY\r\n" + 
			"	t.EXECUTIONS DESC",nativeQuery=true)
	public List<Object[]> findTopSQLS4();

 然后就是实体类了：注意实体类中 必须包含构造函数，而且构造函数中的参数必须和你SQL中 查询的参数 顺序保持一致

package com.befery.oams.entity;

import java.io.Serializable;
import java.math.BigInteger;
import java.security.Timestamp;
import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "v$sqlarea")
public class V$sqlarea implements Serializable {

	@Id
	private String sqlId;

	private Number elapsedTime; // 解析+执行sql 总时间 微秒

	private Number executions; // 执行次数

	private Date lastActiveTime;

	private Number cpuTime;

	public String getSqlId() {
		return sqlId;
	}

	public void setSqlId(String sqlId) {
		this.sqlId = sqlId;
	}

	public Number getElapsedTime() {
		return elapsedTime;
	}

	public void setElapsedTime(Number elapsedTime) {
		this.elapsedTime = elapsedTime;
	}

	public Number getExecutions() {
		return executions;
	}

	public void setExecutions(Number executions) {
		this.executions = executions;
	}

	public Date getLastActiveTime() {
		return lastActiveTime;
	}

	public void setLastActiveTime(Date lastActiveTime) {
		this.lastActiveTime = lastActiveTime;
	}

	public Number getCpuTime() {
		return cpuTime;
	}

	public void setCpuTime(Number cpuTime) {
		this.cpuTime = cpuTime;
	}

	public V$sqlarea() {
	}

	public V$sqlarea(String sqlId, Number elapsedTime, Number executions, Date lastActiveTime,Number cpuTime) {
		this.sqlId = sqlId;
		this.elapsedTime = elapsedTime;
		this.executions = executions;
		this.lastActiveTime = lastActiveTime;
		this.cpuTime = cpuTime;
	}

}

然后就是网上的轮子了

package com.befery.oams.util;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class EntityUtils {
    private static Logger logger = LoggerFactory.getLogger(EntityUtils.class);
 
    /**
     * 将数组数据转换为实体类
     * 此处数组元素的顺序必须与实体类构造函数中的属性顺序一致
     *
     * @param list           数组对象集合
     * @param clazz          实体类
     * @param <T>            实体类
     * @param model          实例化的实体类
     * @return 实体类集合
     */
    public static <T> List<T> castEntity(List<Object[]> list, Class<T> clazz, Object model) {
        List<T> returnList = new ArrayList<T>();
        if (list.isEmpty()) {
            return returnList;
        }
        //获取每个数组集合的元素个数
        Object[] co = list.get(0);
 
        //获取当前实体类的属性名、属性值、属性类别
        List<Map> attributeInfoList = getFiledsInfo(model);
        //创建属性类别数组
        Class[] c2 = new Class[attributeInfoList.size()];
        //如果数组集合元素个数与实体类属性个数不一致则发生错误
        if (attributeInfoList.size() != co.length) {
            return returnList;
        }
        //确定构造方法
        for (int i = 0; i < attributeInfoList.size(); i++) {
            c2[i] = (Class) attributeInfoList.get(i).get("type");
        }
        try {
            for (Object[] o : list) {
                Constructor<T> constructor = clazz.getConstructor(c2);
                returnList.add(constructor.newInstance(o));
            }
        } catch (Exception ex) {
            logger.error("实体数据转化为实体类发生异常：异常信息：{}", ex.getMessage());
            return returnList;
        }
        return returnList;
    }
 
    /**
     * 根据属性名获取属性值
     *
     * @param fieldName 属性名
     * @param modle     实体类
     * @return 属性值
     */
    private static Object getFieldValueByName(String fieldName, Object modle) {
        try {
            String firstLetter = fieldName.substring(0, 1).toUpperCase();
            String getter = "get" + firstLetter + fieldName.substring(1);
            Method method = modle.getClass().getMethod(getter, new Class[]{});
            Object value = method.invoke(modle, new Object[]{});
            return value;
        } catch (Exception e) {
            return null;
        }
    }
 
    /**
     * 获取属性类型(type)，属性名(name)，属性值(value)的map组成的list
     *
     * @param model 实体类
     * @return list集合
     */
    private static List<Map> getFiledsInfo(Object model) {
        Field[] fields = model.getClass().getDeclaredFields();
        List<Map> list = new ArrayList(fields.length);
        Map infoMap = null;
        for (int i = 0; i < fields.length; i++) {
            infoMap = new HashMap(3);
            infoMap.put("type", fields[i].getType());
            infoMap.put("name", fields[i].getName());
            infoMap.put("value", getFieldValueByName(fields[i].getName(), model));
            list.add(infoMap);
        }
        return list;
    }
}

最后的操作，调用 castEntity() 方法：

@GetMapping(value = "/list")
	@ResponseBody
	public List<V$sqlarea> selectTopSQLUntreated() {
		System.out.println("============================TOPSQL       START=================================");
		List<Object[]> list = v$sqlareaDao.findTopSQLS4();
		List<V$sqlarea> list1 =EntityUtils.castEntity(list, V$sqlarea.class,new V$sqlarea());
		System.out.println("============================TOPSQL       END=================================");

		return list1;

	}

看一下日志的输出

============================TOPSQL       START=================================
Hibernate: 
    SELECT
        t.SQL_ID,
        t.ELAPSED_TIME,
        t.EXECUTIONS,
        t.LAST_ACTIVE_TIME,
        t.CPU_TIME   
    FROM
        v$sqlarea t   
    WHERE
        t.PARSING_SCHEMA_NAME IN (
            'C##DBAAS' 
        )   
        AND t.EXECUTIONS > 100       
    ORDER BY
        t.EXECUTIONS DESC
============================TOPSQL       END=================================
2019-03-12 18:06:57.108  INFO 21076 --- [nio-8081-exec-7] com.befery.oams.config.LogAspectConfig   : --------------返回内容----------------
2019-03-12 18:06:57.114  INFO 21076 --- [nio-8081-exec-7] com.befery.oams.config.LogAspectConfig   : Response内容:[{"cpuTime":84731,"elapsedTime":183491,"executions":348,"lastActiveTime":1552385204000,"sqlId":"f05fn7j6rbcsj"},{"cpuTime":17827,"elapsedTime":33036,"executions":212,"lastActiveTime":1552385203000,"sqlId":"avc1jqzz04wpr"},{"cpuTime":9054,"elapsedTime":23874,"executions":174,"lastActiveTime":1552385204000,"sqlId":"b4xr1nw5vtk2v"},{"cpuTime":102017,"elapsedTime":97842,"executions":153,"lastActiveTime":1552313331000,"sqlId":"711b9thj3s4ug"},{"cpuTime":89011,"elapsedTime":90341,"executions":153,"lastActiveTime":1552313331000,"sqlId":"grqh1qs9ajypn"},{"cpuTime":58984,"elapsedTime":81214,"executions":135,"lastActiveTime":1552385214000,"sqlId":"d442vk7001fvw"},{"cpuTime":17260604818,"elapsedTime":41375561059,"executions":122,"lastActiveTime":1552297847000,"sqlId":"170am4cyckruf"},{"cpuTime":13194,"elapsedTime":31267,"executions":108,"lastActiveTime":1552383540000,"sqlId":"9q00dg3n0748y"}]
2019-03-12 18:06:57.114  INFO 21076 --- [nio-8081-exec-7] com.befery.oams.config.LogAspectConfig   : --------------返回内容----------------