Spring?Data?JPA映射自定義實體類操作

更新時間：2021年11月22日 12:04:46 作者：Mr_ZhangAdd

這篇文章主要介紹了Spring?Data?JPA映射自定義實體類操作，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教

Spring Data JPA映射自定義實體類

這個問題困擾了我2天=-=，好像也能使用 jpql解決

先說下自己的功能：查詢oracle最近sql執(zhí)行記錄

sql很簡單：【如果需要分頁，需要自己手動分頁，因為你使用分頁工具他第一頁查詢不會查詢rownum，第二頁查詢就會查詢rownum，然而這個返回的List<Object[]>中的參數(shù)必須要和實體類中一一對應，所以這就有一個不可控制的屬性rownum，所以我們不能使用Pageable入?yún)⒔涌诹?，需要自定義pageSize pageNum參數(shù)】

SELECT
 t.SQL_ID AS SQL的ID,
 t.SQL_TEXT AS SQL語句,
 t.HASH_VALUE AS 完整SQL哈希值,
 t.ELAPSED_TIME AS 解析執(zhí)行總共時間微秒,
 t.EXECUTIONS AS 執(zhí)行總共次數(shù),
 t.LAST_ACTIVE_TIME AS 執(zhí)行最后時間,
 t.CPU_TIME AS CPU執(zhí)行時間微秒 
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。。。。這個網(wǎng)上說不能將查詢結(jié)果自動映射到自定義的實體類。。。。這就比較蛋疼了，在網(wǎng)上就找了個輪子。先上一下自己的Dao層，查出來的是集合數(shù)組，所以使用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();

然后就是實體類了：注意實體類中必須包含構(gòu)造函數(shù)，而且構(gòu)造函數(shù)中的參數(shù)必須和你SQL中查詢的參數(shù) 順序保持一致

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; // 解析+執(zhí)行sql 總時間 微秒
 private Number executions; // 執(zhí)行次數(shù)
 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;
 } 
}

然后就是網(wǎng)上的輪子了

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); 
    /**
     * 將數(shù)組數(shù)據(jù)轉(zhuǎn)換為實體類
     * 此處數(shù)組元素的順序必須與實體類構(gòu)造函數(shù)中的屬性順序一致
     *
     * @param list           數(shù)組對象集合
     * @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;
        }
        //獲取每個數(shù)組集合的元素個數(shù)
        Object[] co = list.get(0);
 
        //獲取當前實體類的屬性名、屬性值、屬性類別
        List<Map> attributeInfoList = getFiledsInfo(model);
        //創(chuàng)建屬性類別數(shù)組
        Class[] c2 = new Class[attributeInfoList.size()];
        //如果數(shù)組集合元素個數(shù)與實體類屬性個數(shù)不一致則發(fā)生錯誤
        if (attributeInfoList.size() != co.length) {
            return returnList;
        }
        //確定構(gòu)造方法
        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("實體數(shù)據(jù)轉(zhuǎn)化為實體類發(fā)生異常：異常信息：{}", ex.getMessage());
            return returnList;
        }
        return returnList;
    }
 
    /**
     * 根據(jù)屬性名獲取屬性值
     *
     * @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;
    }
}

最后的操作，調(diào)用 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 : --------------返回內(nèi)容----------------
2019-03-12 18:06:57.114 INFO 21076 --- [nio-8081-exec-7] com.befery.oams.config.LogAspectConfig : Response內(nèi)容:[{"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 : --------------返回內(nèi)容----------------

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:表結(jié)構(gòu)

這里寫圖片描述

apo_config_attributes:表結(jié)構(gòu)

這里寫圖片描述

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:表結(jié)構(gòu)

這里寫圖片描述

apo_attributes:表結(jié)構(gòu)

這里寫圖片描述

apo_config_attributes：中間表結(jié)構(gòu)

這里寫圖片描述

C: 使用ASF Commons BeanUtils來構(gòu)造一個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實現(xiàn)動態(tài)類
 * @author xiaofanku@live.cn
 * @since 20171024
 */
public class DynamicClass{
    private final DynaBean config;
    /**
     * 構(gòu)造一個運態(tài)類型
     * @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();
    }
    /**
     * 構(gòu)造一個運態(tài)類型
     * @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())));
    }
    /**
     * 裝載屬性集合,填充動態(tài)類實例
     * @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();
        }
    }

最后說明: