经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置:技术经验 » Java相关 » Spring » 查看文章
Spring?Data?JPA映射自定义实体类操作
来源:jb51  时间:2021/11/22 13:41:49  对本文有异议

Spring Data JPA映射自定义实体类

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

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

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

  1. SELECT
  2. t.SQL_ID AS SQLID,
  3. t.SQL_TEXT AS SQL语句,
  4. t.HASH_VALUE AS 完整SQL哈希值,
  5. t.ELAPSED_TIME AS 解析执行总共时间微秒,
  6. t.EXECUTIONS AS 执行总共次数,
  7. t.LAST_ACTIVE_TIME AS 执行最后时间,
  8. t.CPU_TIME AS CPU执行时间微秒
  9. FROM
  10. v$sqlarea t
  11. WHERE
  12. t.PARSING_SCHEMA_NAME IN ( 'C##DBAAS' )
  13. AND t.EXECUTIONS > 10
  14. AND t.LAST_ACTIVE_TIME > TO_DATE('0001-01-01 01:01:01', 'yyyy-MM-dd hh24:mi:ss')
  15. AND t.ELAPSED_TIME > 0
  16. AND t.CPU_TIME > 0
  17. ORDER BY
  18. t.EXECUTIONS DESC;

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

  1. @Query(value="SELECT\r\n" +
  2. " t.SQL_ID,\r\n" +
  3. " t.ELAPSED_TIME,\r\n" +
  4. " t.EXECUTIONS,\r\n" +
  5. " t.LAST_ACTIVE_TIME, \r\n" +
  6. " t.CPU_TIME \r\n" +
  7. "FROM\r\n" +
  8. " v$sqlarea t \r\n" +
  9. "WHERE\r\n" +
  10. " t.PARSING_SCHEMA_NAME IN ( 'C##DBAAS' ) AND t.EXECUTIONS > 100 \r\n" +
  11. "ORDER BY\r\n" +
  12. " t.EXECUTIONS DESC",nativeQuery=true)
  13. public List<Object[]> findTopSQLS4();

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

  1. package com.befery.oams.entity;
  2. import java.io.Serializable;
  3. import java.math.BigInteger;
  4. import java.security.Timestamp;
  5. import java.util.Date;
  6. import javax.persistence.Column;
  7. import javax.persistence.Entity;
  8. import javax.persistence.Id;
  9. import javax.persistence.Table;
  10. @Entity
  11. @Table(name = "v$sqlarea")
  12. public class V$sqlarea implements Serializable {
  13. @Id
  14. private String sqlId;
  15. private Number elapsedTime; // 解析+执行sql 总时间 微秒
  16. private Number executions; // 执行次数
  17. private Date lastActiveTime;
  18. private Number cpuTime;
  19. public String getSqlId() {
  20. return sqlId;
  21. }
  22. public void setSqlId(String sqlId) {
  23. this.sqlId = sqlId;
  24. }
  25. public Number getElapsedTime() {
  26. return elapsedTime;
  27. }
  28. public void setElapsedTime(Number elapsedTime) {
  29. this.elapsedTime = elapsedTime;
  30. }
  31. public Number getExecutions() {
  32. return executions;
  33. }
  34. public void setExecutions(Number executions) {
  35. this.executions = executions;
  36. }
  37. public Date getLastActiveTime() {
  38. return lastActiveTime;
  39. }
  40. public void setLastActiveTime(Date lastActiveTime) {
  41. this.lastActiveTime = lastActiveTime;
  42. }
  43. public Number getCpuTime() {
  44. return cpuTime;
  45. }
  46. public void setCpuTime(Number cpuTime) {
  47. this.cpuTime = cpuTime;
  48. }
  49. public V$sqlarea() {
  50. }
  51. public V$sqlarea(String sqlId, Number elapsedTime, Number executions, Date lastActiveTime,Number cpuTime) {
  52. this.sqlId = sqlId;
  53. this.elapsedTime = elapsedTime;
  54. this.executions = executions;
  55. this.lastActiveTime = lastActiveTime;
  56. this.cpuTime = cpuTime;
  57. }
  58. }

然后就是网上的轮子了

  1. package com.befery.oams.util;
  2. import org.slf4j.Logger;
  3. import org.slf4j.LoggerFactory;
  4. import java.lang.reflect.Constructor;
  5. import java.lang.reflect.Field;
  6. import java.lang.reflect.Method;
  7. import java.util.ArrayList;
  8. import java.util.HashMap;
  9. import java.util.List;
  10. import java.util.Map;
  11. public class EntityUtils {
  12. private static Logger logger = LoggerFactory.getLogger(EntityUtils.class);
  13. /**
  14. * 将数组数据转换为实体类
  15. * 此处数组元素的顺序必须与实体类构造函数中的属性顺序一致
  16. *
  17. * @param list 数组对象集合
  18. * @param clazz 实体类
  19. * @param <T> 实体类
  20. * @param model 实例化的实体类
  21. * @return 实体类集合
  22. */
  23. public static <T> List<T> castEntity(List<Object[]> list, Class<T> clazz, Object model) {
  24. List<T> returnList = new ArrayList<T>();
  25. if (list.isEmpty()) {
  26. return returnList;
  27. }
  28. //获取每个数组集合的元素个数
  29. Object[] co = list.get(0);
  30. //获取当前实体类的属性名、属性值、属性类别
  31. List<Map> attributeInfoList = getFiledsInfo(model);
  32. //创建属性类别数组
  33. Class[] c2 = new Class[attributeInfoList.size()];
  34. //如果数组集合元素个数与实体类属性个数不一致则发生错误
  35. if (attributeInfoList.size() != co.length) {
  36. return returnList;
  37. }
  38. //确定构造方法
  39. for (int i = 0; i < attributeInfoList.size(); i++) {
  40. c2[i] = (Class) attributeInfoList.get(i).get("type");
  41. }
  42. try {
  43. for (Object[] o : list) {
  44. Constructor<T> constructor = clazz.getConstructor(c2);
  45. returnList.add(constructor.newInstance(o));
  46. }
  47. } catch (Exception ex) {
  48. logger.error("实体数据转化为实体类发生异常:异常信息:{}", ex.getMessage());
  49. return returnList;
  50. }
  51. return returnList;
  52. }
  53. /**
  54. * 根据属性名获取属性值
  55. *
  56. * @param fieldName 属性名
  57. * @param modle 实体类
  58. * @return 属性值
  59. */
  60. private static Object getFieldValueByName(String fieldName, Object modle) {
  61. try {
  62. String firstLetter = fieldName.substring(0, 1).toUpperCase();
  63. String getter = "get" + firstLetter + fieldName.substring(1);
  64. Method method = modle.getClass().getMethod(getter, new Class[]{});
  65. Object value = method.invoke(modle, new Object[]{});
  66. return value;
  67. } catch (Exception e) {
  68. return null;
  69. }
  70. }
  71. /**
  72. * 获取属性类型(type),属性名(name),属性值(value)的map组成的list
  73. *
  74. * @param model 实体类
  75. * @return list集合
  76. */
  77. private static List<Map> getFiledsInfo(Object model) {
  78. Field[] fields = model.getClass().getDeclaredFields();
  79. List<Map> list = new ArrayList(fields.length);
  80. Map infoMap = null;
  81. for (int i = 0; i < fields.length; i++) {
  82. infoMap = new HashMap(3);
  83. infoMap.put("type", fields[i].getType());
  84. infoMap.put("name", fields[i].getName());
  85. infoMap.put("value", getFieldValueByName(fields[i].getName(), model));
  86. list.add(infoMap);
  87. }
  88. return list;
  89. }
  90. }

最后的操作,调用 castEntity() 方法:

  1. @GetMapping(value = "/list")
  2. @ResponseBody
  3. public List<V$sqlarea> selectTopSQLUntreated() {
  4. System.out.println("============================TOPSQL START=================================");
  5. List<Object[]> list = v$sqlareaDao.findTopSQLS4();
  6. List<V$sqlarea> list1 =EntityUtils.castEntity(list, V$sqlarea.class,new V$sqlarea());
  7. System.out.println("============================TOPSQL END=================================");
  8. return list1;
  9. }

看一下日志的输出

============================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: 两张表示例

  1. /**
  2. *
  3. * @author xiaofanku@live.cn
  4. */
  5. @Entity
  6. @Table(name="apo_config")
  7. public class SiteConfig implements Serializable {
  8. @Id
  9. @GeneratedValue(strategy = GenerationType.IDENTITY)
  10. private Long ID;
  11. private String caption;
  12. @ElementCollection(fetch = FetchType.LAZY)
  13. @MapKeyColumn(name="name")
  14. @Column(name="value")
  15. @CollectionTable(name="apo_config_attributes", joinColumns=@JoinColumn(name="ca_id"))
  16. private Map<String, String> attributes = new HashMap<String, String>();
  17. //GET/SET
  18. }

测试代码

  1. @Test
  2. public void test(){
  3. SiteConfig sc=new SiteConfig();
  4. sc.setID(1L);
  5. sc.setCaption("全局配置");
  6. Map<String, String> data=new HashMap<>();
  7. data.put("site", "csdn.net");
  8. data.put("account", "xiaofanku");
  9. sc.setAttributes(data);
  10. siteConfigDao.save(sc);
  11. }
  12. @Test
  13. public void getConfig(){
  14. SiteConfig config=siteConfigDao.findOne(1L);
  15. assertEquals(config.getAttributes().get("site"), "csdn.net");
  16. }

apo_config:表结构

这里写图片描述

apo_config_attributes:表结构

这里写图片描述

B: 三张表示例

  1. /**
  2. *
  3. * @author xiaofanku@live.cn
  4. */
  5. @Entity
  6. @Table(name="apo_config")
  7. public class SiteConfig implements Serializable {
  8. @Id
  9. @GeneratedValue(strategy = GenerationType.IDENTITY)
  10. private Long ID;
  11. private String caption;
  12. @OneToMany(cascade=CascadeType.ALL, orphanRemoval = true)
  13. @MapKey(name="name")
  14. @JoinTable(name = "apo_config_attributes")
  15. private Map<String, ConfigAttribute> attributes=new HashMap<>();
  16. //GET/SET
  17. }
  1. /**
  2. *
  3. * @author xiaofanku@live.cn
  4. */
  5. @Entity
  6. @Table(name="apo_attributes")
  7. public class ConfigAttribute implements Serializable {
  8. @Id
  9. @GeneratedValue(strategy = GenerationType.IDENTITY)
  10. private Long ID;
  11. @Column(name="name")
  12. private String name;
  13. private String value;
  14. //GET/SET
  15. }

测试代码

  1. @Test @Ignore
  2. public void test(){
  3. SiteConfig sc=new SiteConfig();
  4. sc.setID(1L);
  5. sc.setCaption("全局配置");
  6. Map<String, ConfigAttribute> data=new HashMap<>();
  7. ConfigAttribute ca1=new ConfigAttribute();
  8. ca1.setName("site");ca1.setValue("csdn.net");
  9. data.put("site", ca1);
  10. ConfigAttribute ca2=new ConfigAttribute();
  11. ca2.setName("account");ca2.setValue("xiaofanku");
  12. data.put("account", ca2);
  13. sc.setAttributes(data);
  14. siteConfigDao.save(sc);
  15. }
  16. @Test @Ignore
  17. public void getConfig(){
  18. SiteConfig config=siteConfigDao.findOne(1L);
  19. assertEquals(config.getAttributes().get("site").getValue(), "csdn.net");
  20. }

apo_config:表结构

这里写图片描述

apo_attributes:表结构

这里写图片描述

apo_config_attributes:中间表结构

这里写图片描述

C: 使用ASF Commons BeanUtils来构造一个Dynamic Class

  1. import java.util.ArrayList;
  2. import java.util.List;
  3. import java.util.Map;
  4. import java.util.Objects;
  5. import java.util.Set;
  6. import org.apache.commons.beanutils.BasicDynaClass;
  7. import org.apache.commons.beanutils.ConvertUtils;
  8. import org.apache.commons.beanutils.DynaBean;
  9. import org.apache.commons.beanutils.DynaProperty;
  10. /**
  11. * 使用Commons Beanutils实现动态类
  12. * @author xiaofanku@live.cn
  13. * @since 20171024
  14. */
  15. public class DynamicClass{
  16. private final DynaBean config;
  17. /**
  18. * 构造一个运态类型
  19. * @param attributeMeta key属性名,value为属性名的类型,例:java.lang.Boolean
  20. * @throws IllegalAccessException
  21. * @throws InstantiationException
  22. * @throws ClassNotFoundException
  23. */
  24. public DynamicClass(Map<String,String> attributeMeta) throws IllegalAccessException, InstantiationException, ClassNotFoundException{
  25. DynaProperty[] props=covert(attributeMeta).toArray(new DynaProperty[]{});
  26. BasicDynaClass dynaClass = new BasicDynaClass("CustomConfig", null, props);
  27. this.config = dynaClass.newInstance();
  28. }
  29. /**
  30. * 构造一个运态类型
  31. * @param attributes
  32. * @throws ClassNotFoundException
  33. * @throws IllegalAccessException
  34. * @throws InstantiationException
  35. */
  36. public DynamicClass(Set<Attribute> attributes) throws ClassNotFoundException, IllegalAccessException, InstantiationException{
  37. DynaProperty[] props=covert(attributes).toArray(new DynaProperty[]{});
  38. BasicDynaClass dynaClass = new BasicDynaClass("CustomConfig", null, props);
  39. this.config = dynaClass.newInstance();
  40. load(attributes);
  41. }
  42. /**
  43. * 获得属性值
  44. * @param attributeName 属性名
  45. * @return
  46. */
  47. public Object getValue(String attributeName){
  48. return config.get(attributeName);
  49. }
  50. /**
  51. * 获得属性值
  52. * @param attributeName 属性名
  53. * @param classType 属性类型
  54. * @param <T>
  55. * @return
  56. * @throws java.lang.ClassCastException
  57. */
  58. public <T> T getValue(String attributeName, Class<T> classType) throws java.lang.ClassCastException{
  59. return (T)getValue(attributeName);
  60. }
  61. /**
  62. * 设置属性
  63. * @param attributeName 属性名
  64. * @param attributeValue 属性值
  65. */
  66. public void setValue(String attributeName, String attributeValue){
  67. DynaProperty dp = config.getDynaClass().getDynaProperty(attributeName);
  68. config.set(attributeName, ConvertUtils.convert(attributeValue, dp.getType()));
  69. }
  70. /**
  71. * 设置属性
  72. * @param attribute 属性实例
  73. * @throws ClassNotFoundException
  74. */
  75. public void setValue(Attribute attribute) throws ClassNotFoundException {
  76. config.set(attribute.getName(), ConvertUtils.convert(attribute.getValue(), Class.forName(attribute.getClassName())));
  77. }
  78. /**
  79. * 装载属性集合,填充动态类实例
  80. * @param attributes
  81. */
  82. private void load(Set<Attribute> attributes){
  83. for(Attribute attr : attributes){
  84. try{
  85. config.set(attr.getName(), ConvertUtils.convert(attr.getValue(), Class.forName(attr.getClassName())));
  86. }catch(ClassNotFoundException e){
  87. }
  88. }
  89. }
  90. /**
  91. * 返回一个DynaProperty列表
  92. * @param attributes
  93. * @return
  94. * @throws ClassNotFoundException
  95. */
  96. private List<DynaProperty> covert(Set<Attribute> attributes) throws ClassNotFoundException{
  97. List<DynaProperty> attres=new ArrayList<>();
  98. for(Attribute attr : attributes){
  99. attres.add(new DynaProperty(attr.getName(), Class.forName(attr.getClassName())));
  100. }
  101. return attres;
  102. }
  103. /**
  104. * 返回一个DynaProperty列表
  105. * @param attributeMeta key属性名,value为属性名的类型,例:java.lang.Boolean
  106. * @return
  107. * @throws ClassNotFoundException
  108. */
  109. private List<DynaProperty> covert(Map<String,String> attributeMeta) throws ClassNotFoundException{
  110. List<DynaProperty> properties=new ArrayList<>();
  111. Set<String> attrSet=attributeMeta.keySet();
  112. for(String attrName : attrSet){
  113. String className=attributeMeta.get(attrName);
  114. properties.add(new DynaProperty(attrName, Class.forName(className)));
  115. }
  116. return properties;
  117. }
  118. public static enum Type{
  119. BOOLEAN("java.lang.Boolean"),
  120. INTEGER("java.lang.Integer"),
  121. LONG("java.lang.Long"),
  122. STRING("java.lang.String"),
  123. CHAR("java.lang.Character"),
  124. DOUBLE("java.lang.Double"),
  125. FLOAT("java.lang.Float");
  126. private final String name;
  127. private Type(String className){
  128. this.name=className;
  129. }
  130. public String getName() {
  131. return name;
  132. }
  133. }
  134. public static class Attribute{
  135. //属性名,例:show
  136. private final String name;
  137. //属性名的值,例:"true"
  138. private final String value;
  139. //属性名的类型,例:java.lang.Boolean
  140. private final String className;
  141. public Attribute(String name, String value, String className) {
  142. this.name = name;
  143. this.value = value;
  144. this.className = className;
  145. }
  146. public String getName() {
  147. return name;
  148. }
  149. public String getValue() {
  150. return value;
  151. }
  152. public String getClassName() {
  153. return className;
  154. }
  155. @Override
  156. public int hashCode() {
  157. int hash = 5;
  158. hash = 97 * hash + Objects.hashCode(this.name);
  159. hash = 97 * hash + Objects.hashCode(this.className);
  160. return hash;
  161. }
  162. @Override
  163. public boolean equals(Object obj) {
  164. if (this == obj) {
  165. return true;
  166. }
  167. if (obj == null) {
  168. return false;
  169. }
  170. if (getClass() != obj.getClass()) {
  171. return false;
  172. }
  173. final Attribute other = (Attribute) obj;
  174. if (!Objects.equals(this.name, other.name)) {
  175. return false;
  176. }
  177. if (!Objects.equals(this.className, other.className)) {
  178. return false;
  179. }
  180. return true;
  181. }
  182. }
  183. }

测试代码:

  1. @Test
  2. public void test(){
  3. Set<Attribute> sas=new HashSet<>();
  4. sas.add(new Attribute("logo", "logo.png", DynamicClass.Type.STRING.getName()));
  5. sas.add(new Attribute("pageSize", "50", DynamicClass.Type.INTEGER.getName()));
  6. sas.add(new Attribute("shortcut", "true", DynamicClass.Type.BOOLEAN.getName()));
  7. try{
  8. DynamicClass dc=new DynamicClass(sas);
  9. Integer ps = dc.getValue("pageSize", Integer.class);
  10. System.out.println(ps);
  11. dc.setValue("pageSize", "150");
  12. System.out.println(dc.getValue("pageSize"));
  13. }catch(Exception e){
  14. e.printStackTrace();
  15. }
  16. }
  17. @Test @Ignore
  18. public void base() {
  19. Map<String, String> am = new HashMap<>();
  20. am.put("logo", DynamicClass.Type.STRING.getName());
  21. am.put("pageSize", DynamicClass.Type.INTEGER.getName());
  22. am.put("shortcut", DynamicClass.Type.BOOLEAN.getName());
  23. try {
  24. DynamicClass dc = new DynamicClass(am);
  25. dc.setValue("pageSize", "150");
  26. System.out.println(dc.getValue("pageSize"));
  27. dc.setValue(new Attribute("shortcut", "true", DynamicClass.Type.BOOLEAN.getName()));
  28. System.out.println(dc.getValue("shortcut"));
  29. } catch (IllegalAccessException | InstantiationException | ClassNotFoundException e) {
  30. // TODO Auto-generated catch block
  31. e.printStackTrace();
  32. }
  33. }

最后说明:

  • JPA 2.1 实现 EclipseLink 2.5.2
  • JDK 1.8.x
  • Mysql 5.5.x
  • ASF Commons BeanUtils 1.8.3

以上为个人经验,希望能给大家一个参考,也希望大家多多支持w3xue。

 友情链接:直通硅谷  点职佳  北美留学生论坛

本站QQ群:前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

W3xue 的所有内容仅供测试,对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接(目前9元/月)请联系QQ:27243702 沸活量
皖ICP备17017327号-2 皖公网安备34020702000426号