目录
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
以上为个人经验，希望能给大家一个参考，也希望大家多多支持。
做猪小侠源码的代理，提供一站式服务
如果你不懂得搭建网站或者服务器，小程序，源码之类的怎么办？ 第一通过本站学习各种互联网的技术 第二就是联系客服，我帮帮你搭建（当然要收取部分的费用） 第三成为我们的代理，我们提供整套的服务。