Spring Data JPA映射自定义实体类操作

Spring Data JPA映射自定义实体类操作

目录Spring Data JPA映射自定义实体类JPA 配置类实体映射示例

Spring Data JPA映射自定义实体类

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

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

sql很简单：【如果需要分页，需要自己手动分页，因为你使用分页工具他第一页查询不会查询rownum，第二页查询就会查询rownum，然而这个返回的List

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

然后就是实体类了：注意实体类中 必须包含构造函数，而且构造函数中的参数必须和你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 实体类

* @param model 实例化的实体类

* @return 实体类集合

*/

public static List castEntity(List

List returnList = new ArrayList();

if (list.isEmpty()) {

return returnList;

}

//获取每个数组集合的元素个数

Object[] co = list.get(0);

//获取当前实体类的属性名、属性值、属性类别

List

//创建属性类别数组

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 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

Field[] fields = model.getClass().getDeclaredFields();

List

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 selectTopSQLUntreated() {

System.out.println("============================TOPSQL START=================================");

List

List 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-

*/

@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 attributes = new HashMap();

//GET/SET

}

测试代码

@Test

public void test(){

SiteConfig sc=new SiteConfig();

sc.setID(1L);

sc.setCaption("全局配置");

Map data=new HashMap<>();

data.put("site", "csdn-");

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-");

}

apo_config:表结构

apo_config_attributes:表结构

B: 三张表示例

/**

*

* @author xiaofanku@live-

*/

@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 attributes=new HashMap<>();

//GET/SET

}

/**

*

* @author xiaofanku@live-

*/

@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 data=new HashMap<>();

ConfigAttribute ca1=new ConfigAttribute();

ca1.setName("site");ca1.setValue("csdn-");

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.getAttribuouWanbFbtes().get("site").getValue(), "csdn-");

}

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-

* @since 20171024

*/

public class DynamicClass{

private final DynaBean config;

/**

* 构造一个运态类型

* @param attributeMeta key属性名,value为属性名的类型,例:java.lang.Boolean

* @throws IllegalAccessException

* @throws InstantiationExceptionhttp://

* @throws ClassNotFoundException

*/

public DynamicClass(Map 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 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

* @return

* @throws java.lang.ClassCastException

*/

public T getValue(String attributeName, Class 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 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 covert(Set attributes) throws ClassNotFoundException{

List 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 covert(Map attributeMeta) throws ClassNotFoundException{

List properties=new ArrayList<>();

Set 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 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 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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。