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 : --------------返回内容----------------
JPA 配置类实体映射示例
A: 两张表示例
- /**
- *
- * @author xiaofanku@live.cn
- */
- @Entity
- @Table(name="apo_config")
- public class SiteConfig implements Serializable {
- @Id
- @GeneratedValue(strategy = GenerationType.IDENTITY)
- private Long ID;
- private String caption;
- @ElementCollection(fetch = FetchType.LAZY)
- @MapKeyColumn(name="name")
- @Column(name="value")
- @CollectionTable(name="apo_config_attributes", joinColumns=@JoinColumn(name="ca_id"))
- private Map<String, String> attributes = new HashMap<String, String>();
- //GET/SET
- }
测试代码
- @Test
- public void test(){
- SiteConfig sc=new SiteConfig();
- sc.setID(1L);
- sc.setCaption("全局配置");
- Map<String, String> data=new HashMap<>();
- data.put("site", "csdn.net");
- data.put("account", "xiaofanku");
- sc.setAttributes(data);
- siteConfigDao.save(sc);
- }
- @Test
- public void getConfig(){
- SiteConfig config=siteConfigDao.findOne(1L);
- assertEquals(config.getAttributes().get("site"), "csdn.net");
- }
apo_config:表结构

apo_config_attributes:表结构

B: 三张表示例
- /**
- *
- * @author xiaofanku@live.cn
- */
- @Entity
- @Table(name="apo_config")
- public class SiteConfig implements Serializable {
- @Id
- @GeneratedValue(strategy = GenerationType.IDENTITY)
- private Long ID;
- private String caption;
- @OneToMany(cascade=CascadeType.ALL, orphanRemoval = true)
- @MapKey(name="name")
- @JoinTable(name = "apo_config_attributes")
- private Map<String, ConfigAttribute> attributes=new HashMap<>();
- //GET/SET
- }
- /**
- *
- * @author xiaofanku@live.cn
- */
- @Entity
- @Table(name="apo_attributes")
- public class ConfigAttribute implements Serializable {
- @Id
- @GeneratedValue(strategy = GenerationType.IDENTITY)
- private Long ID;
- @Column(name="name")
- private String name;
- private String value;
- //GET/SET
- }
测试代码
- @Test @Ignore
- public void test(){
- SiteConfig sc=new SiteConfig();
- sc.setID(1L);
- sc.setCaption("全局配置");
- Map<String, ConfigAttribute> data=new HashMap<>();
- ConfigAttribute ca1=new ConfigAttribute();
- ca1.setName("site");ca1.setValue("csdn.net");
- data.put("site", ca1);
- ConfigAttribute ca2=new ConfigAttribute();
- ca2.setName("account");ca2.setValue("xiaofanku");
- data.put("account", ca2);
- sc.setAttributes(data);
- siteConfigDao.save(sc);
- }
- @Test @Ignore
- public void getConfig(){
- SiteConfig config=siteConfigDao.findOne(1L);
- assertEquals(config.getAttributes().get("site").getValue(), "csdn.net");
- }
apo_config:表结构

apo_attributes:表结构

apo_config_attributes:中间表结构

C: 使用ASF Commons BeanUtils来构造一个Dynamic Class
- import java.util.ArrayList;
- import java.util.List;
- import java.util.Map;
- import java.util.Objects;
- import java.util.Set;
- import org.apache.commons.beanutils.BasicDynaClass;
- import org.apache.commons.beanutils.ConvertUtils;
- import org.apache.commons.beanutils.DynaBean;
- import org.apache.commons.beanutils.DynaProperty;
- /**
- * 使用Commons Beanutils实现动态类
- * @author xiaofanku@live.cn
- * @since 20171024
- */
- public class DynamicClass{
- private final DynaBean config;
- /**
- * 构造一个运态类型
- * @param attributeMeta key属性名,value为属性名的类型,例:java.lang.Boolean
- * @throws IllegalAccessException
- * @throws InstantiationException
- * @throws ClassNotFoundException
- */
- public DynamicClass(Map<String,String> attributeMeta) throws IllegalAccessException, InstantiationException, ClassNotFoundException{
- DynaProperty[] props=covert(attributeMeta).toArray(new DynaProperty[]{});
- BasicDynaClass dynaClass = new BasicDynaClass("CustomConfig", null, props);
- this.config = dynaClass.newInstance();
- }
- /**
- * 构造一个运态类型
- * @param attributes
- * @throws ClassNotFoundException
- * @throws IllegalAccessException
- * @throws InstantiationException
- */
- public DynamicClass(Set<Attribute> attributes) throws ClassNotFoundException, IllegalAccessException, InstantiationException{
- DynaProperty[] props=covert(attributes).toArray(new DynaProperty[]{});
- BasicDynaClass dynaClass = new BasicDynaClass("CustomConfig", null, props);
- this.config = dynaClass.newInstance();
- load(attributes);
- }
- /**
- * 获得属性值
- * @param attributeName 属性名
- * @return
- */
- public Object getValue(String attributeName){
- return config.get(attributeName);
- }
- /**
- * 获得属性值
- * @param attributeName 属性名
- * @param classType 属性类型
- * @param <T>
- * @return
- * @throws java.lang.ClassCastException
- */
- public <T> T getValue(String attributeName, Class<T> classType) throws java.lang.ClassCastException{
- return (T)getValue(attributeName);
- }
- /**
- * 设置属性
- * @param attributeName 属性名
- * @param attributeValue 属性值
- */
- public void setValue(String attributeName, String attributeValue){
- DynaProperty dp = config.getDynaClass().getDynaProperty(attributeName);
- config.set(attributeName, ConvertUtils.convert(attributeValue, dp.getType()));
- }
- /**
- * 设置属性
- * @param attribute 属性实例
- * @throws ClassNotFoundException
- */
- public void setValue(Attribute attribute) throws ClassNotFoundException {
- config.set(attribute.getName(), ConvertUtils.convert(attribute.getValue(), Class.forName(attribute.getClassName())));
- }
- /**
- * 装载属性集合,填充动态类实例
- * @param attributes
- */
- private void load(Set<Attribute> attributes){
- for(Attribute attr : attributes){
- try{
- config.set(attr.getName(), ConvertUtils.convert(attr.getValue(), Class.forName(attr.getClassName())));
- }catch(ClassNotFoundException e){
- }
- }
- }
- /**
- * 返回一个DynaProperty列表
- * @param attributes
- * @return
- * @throws ClassNotFoundException
- */
- private List<DynaProperty> covert(Set<Attribute> attributes) throws ClassNotFoundException{
- List<DynaProperty> attres=new ArrayList<>();
- for(Attribute attr : attributes){
- attres.add(new DynaProperty(attr.getName(), Class.forName(attr.getClassName())));
- }
- return attres;
- }
- /**
- * 返回一个DynaProperty列表
- * @param attributeMeta key属性名,value为属性名的类型,例:java.lang.Boolean
- * @return
- * @throws ClassNotFoundException
- */
- private List<DynaProperty> covert(Map<String,String> attributeMeta) throws ClassNotFoundException{
- List<DynaProperty> properties=new ArrayList<>();
- Set<String> attrSet=attributeMeta.keySet();
- for(String attrName : attrSet){
- String className=attributeMeta.get(attrName);
- properties.add(new DynaProperty(attrName, Class.forName(className)));
- }
- return properties;
- }
- public static enum Type{
- BOOLEAN("java.lang.Boolean"),
- INTEGER("java.lang.Integer"),
- LONG("java.lang.Long"),
- STRING("java.lang.String"),
- CHAR("java.lang.Character"),
- DOUBLE("java.lang.Double"),
- FLOAT("java.lang.Float");
- private final String name;
- private Type(String className){
- this.name=className;
- }
- public String getName() {
- return name;
- }
- }
- public static class Attribute{
- //属性名,例:show
- private final String name;
- //属性名的值,例:"true"
- private final String value;
- //属性名的类型,例:java.lang.Boolean
- private final String className;
- public Attribute(String name, String value, String className) {
- this.name = name;
- this.value = value;
- this.className = className;
- }
- public String getName() {
- return name;
- }
- public String getValue() {
- return value;
- }
- public String getClassName() {
- return className;
- }
- @Override
- public int hashCode() {
- int hash = 5;
- hash = 97 * hash + Objects.hashCode(this.name);
- hash = 97 * hash + Objects.hashCode(this.className);
- return hash;
- }
- @Override
- public boolean equals(Object obj) {
- if (this == obj) {
- return true;
- }
- if (obj == null) {
- return false;
- }
- if (getClass() != obj.getClass()) {
- return false;
- }
- final Attribute other = (Attribute) obj;
- if (!Objects.equals(this.name, other.name)) {
- return false;
- }
- if (!Objects.equals(this.className, other.className)) {
- return false;
- }
- return true;
- }
- }
- }
测试代码:
- @Test
- public void test(){
- Set<Attribute> sas=new HashSet<>();
- sas.add(new Attribute("logo", "logo.png", DynamicClass.Type.STRING.getName()));
- sas.add(new Attribute("pageSize", "50", DynamicClass.Type.INTEGER.getName()));
- sas.add(new Attribute("shortcut", "true", DynamicClass.Type.BOOLEAN.getName()));
- try{
- DynamicClass dc=new DynamicClass(sas);
- Integer ps = dc.getValue("pageSize", Integer.class);
- System.out.println(ps);
- dc.setValue("pageSize", "150");
- System.out.println(dc.getValue("pageSize"));
- }catch(Exception e){
- e.printStackTrace();
- }
- }
- @Test @Ignore
- public void base() {
- Map<String, String> am = new HashMap<>();
- am.put("logo", DynamicClass.Type.STRING.getName());
- am.put("pageSize", DynamicClass.Type.INTEGER.getName());
- am.put("shortcut", DynamicClass.Type.BOOLEAN.getName());
- try {
- DynamicClass dc = new DynamicClass(am);
- dc.setValue("pageSize", "150");
- System.out.println(dc.getValue("pageSize"));
- dc.setValue(new Attribute("shortcut", "true", DynamicClass.Type.BOOLEAN.getName()));
- System.out.println(dc.getValue("shortcut"));
- } catch (IllegalAccessException | InstantiationException | ClassNotFoundException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- }
最后说明:
- JPA 2.1 实现 EclipseLink 2.5.2
- JDK 1.8.x
- Mysql 5.5.x
- ASF Commons BeanUtils 1.8.3
以上为个人经验,希望能给大家一个参考,也希望大家多多支持w3xue。