Spring Data Jpa多表查询返回自定义实体方式

Spring Data Jpa多表查询返回自定义实体方式

目录SpringDataJpa多表查询返回自定义实体Repository好下面到单元测试自定义实体SpringDataJpa多表查询返回自定义VO的问题下面是我的代码下面是我的dao层，重点

SpringDataJpa多表查询返回自定义实体

比如来看一下这样的一条SQL语句，这是一个三张表的多表查询，显然在JPA中用一个实体类是接受不了这些参数的

select

t1.id as chapterId ,

t1.name as chapterName ,

t2.id as unitId,

t2.name as unitName ,

t3.id as lessonId,

t3.name as lessonName

from t_chapter t1

LEFT JOIN t_unit t2 on t1.id =t2.chapter_id

LEFT JOIN t_lession t3 on t3.unit_id =t2.id

where t1.id= '4028b4816305ea91016305eec24f0000'

and t2.id='4028b4816305f1a6016305f423180000'

and t3.id= '4028b4816306007b016306020bb80000';

Repository

/**

* Created by ZhuPengWei on 2018/5/11.

*/

public interface TestRepository extends JpaRepository {

@Query(

value = "select \n" +

"t1.id as chapterId ,t1.name as chapterName ,t2.id as unitId,t2.name as unitName ,t3.id as lessonId,t3.name as lessonName\n" +

"from t_chapter t1\n" +

"LEFT JOIN t_unit t2 on t1.id =t2.chapter_id\n" +

"LEFT JOIN t_lession t3 on t3.unit_id =t2.id \n" +

"where t1.id= :chapterId \n" +

"and t2.id=:unitId \n" +

"and t3.id= :lessonId",

nativeQuery = true

)

List

}

好下面到单元测试

/**

* Created by ZhuPengWei on 2018/5/11.

*/

@RunWith(SpringRunner.class)

@SpringBootTest

public class TestRepositoryTest {

@Autowired

private TestRepository testRepository;

@Test

public void select() throws Exception {

List

List testViews = castEntity(select, TestView.class);

Assert.assertTrue(testViews.size() > 0);

}

//转换实体类

public static List castEntity(List

List returnList = new ArrayList();

if(CollectionUtils.isEmpty(list)){

return returnList;

}

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

Class[] c2 = new Class[co.length];

//确定构造方法

for (int i = 0; i < co.length; i++) {

if(co[i]!=null){

c2[i] = co[i].getClass();

}else {

c2[i]=String.class;

}

}

for (Object[] o : list) {

Constructor constructor = clazz.getConstructor(c2);

returnList.add(constructor.newInstance(o));

}

return returnList;

}

自定义实体

/**

* Created by ZhuPengWei on 2018/5/11.

*/

@Data

public class TestView {

private String chapterId;

private String chapterName;

private String unitId;

private String unitName;

private String lessonId;

private String lessonName;

public TestView() {

}

public TestView(String chapterId, String chapterName, String unitId, String unitName, String lessonId, String lessonName) {http://

this.chapterId = chapterId;

this.chapterName = chapterName;

this.unitId = unitId;

this.unitName = unitName;

this.lessonId = lessonId;

this.lessonName = lessonName;

}

}

需要注意的是 SQL语句的字段顺序一定要与实体类字段的顺序保持一致，否则会出现参数封装错误的情况

Spring Data Jpa多表查询返回自定义VO的问题

这两天开了一个新项目，使用SpringBoot+SpringData，  刚做了一个小功能，都是一张表的操作没什么问题，今天设计到了两张表联查，两张表各取了几个字段，组合成了一个vo， 当我用原生sql查询时报出 “找不到转换器”，当我用JPQL查询时，报出 xxx is not mapped.。 着实浪费了些时间。

其实最大的根本原因在于， 当使用JPQL 查询时，我们 FROM 的表名就不能是实体上@Table 注解中的表名，而是对应的实体的类名，记住是类名，并且查询的字段要是 实体类的属性，而不是数据库字段。切记。

下面是我的代码

package com.wisdombud.dama.quality.datasource.pojo;

import java.io.Serializable;

import java.util.Date;

import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import javax.persistence.SequenceGenerator;

import javax.persistence.Table;

import javax.validation.constraints.NotBlank;

import com.fasterxml.jackson.annotation.jsonFormat;

import lombok.ToString;

@Entity

@Table(name = "DATA_SOURCE")

@SequenceGenerator(name = "ID_SEQ", sequenceName = "SEQ_DATA_SOURCE", allocationSize = 1)

@ToString

public class DataSourcePojo implements Serializable{

private static final long serialVersionUID = 4658654420795590006L;

private Long id;

@NotBlank(message = "数据源名称不能为空！")

private String name;

@NotBlank(message = "ip地址不能为空！")

private String ip;

@NotBlank(message = "端口号不能为空！")

private String port;

@NotBlank(message = "服务名不能为空！")

private String serviceName;

@NotBlank(message = "用户名不能为空！")

private String userName;

@NotBlank(message = "密码不能为空！")

private String password;

private String managerBranch;

private String leadingCadre;

private String phone;

private Long sortIndex;

private String remark;

private Long createUserId;

private String createUserName;

@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")

private Date createTime;

private Date lastUpdateTime;

private String re1;

private String re2;

private String re3;

private String re4;

@Id

@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "ID_SEQ")

@Column(name = "ID", unique = true, nullable = false, precision = 18, scale = 0)

public Long getId() {

return id;

}

public void setId(Long id) {

this.id = id;

}

@Column(name = "NAME")

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

@Column(name = "IP")

public String getIp() {

return ip;

}

public void setIp(String ip) {

this.ip = ip;

}

@Column(name = "PORT")

public String getPort() {

return port;

}

public void setPort(String port) {

this.port = port;

}

@Column(name = "SERVICE_NAME")

public String getServiceName() {

return serviceName;

}

public void setServiceName(String serviceName) {

this.serviceName = serviceName;

}

@Column(name = "USER_NAME")

public String getUserName() {

return userName;

}

public void setUserName(String userName) {

this.userName = userName;

}

@Column(name = "PASSWORD")

public String getPassword() {

return password;

}

public void setPassword(String password) {

this.password = password;

}

@Column(name = "MANAGER_BRANCH")

public String getManagerBranch() {

return managerBranch;

}

public void setManagerBranch(String managerBranch) {

this.managerBranch = managerBranch;

}

@Column(name = "LEADING_CADRE")

public String getLeadingCadre() {

return leadingCadre;

}

public void setLeadingCadre(String leadingCadre) {

this.leadingCadre = leadingCadre;

}

@Column(name = "PHONE")

public String getPhone() {

return phone;

}

public void setPhone(String phone) {

this.phone = phone;

}

@Column(name = "SORT_INDEX")

public Long getSortIndex() {

return sortIndex;

}

public void setSortIndex(Long sortIndex) {

this.sortIndex = sortIndex;

}

@Column(name = "REMARK")

public String getRemark() {

return remark;

}

public void setRemark(String remark) {

this.remark = remark;

}

@Column(name = "CREATE_USER_ID")

public Long getCreateUserId() {

return createUserId;

}

public void setCreateUserId(Long createUserId) {

this.createUserId = createUserId;

}

@Column(name = "CREATE_USER_NAME")

public String getCreateUserName() {

return createUserName;

}

public void setCreateUserName(String createUserName) {

this.createUserName = createUserName;

}

@Column(name = "CREATE_TIME")

public Date getCreateTime() {

return createTime;

}

public void setCreateTime(Date createTime) {

this.createTime = createTime;

}

@Column(name = "LAST_UPDATE_TIME")

public Date getLastUpdateTime() {

return lastUpdateTime;

}

public void setLastUpdateTime(Date lastUpdateTime) {

this.lastUpdateTime = lastUpdateTime;

}

@Column(name = "RE1")

public String getRe1() {

return re1;

}

public void setRe1(String re1) {

this.re1 = re1;

}

@Column(name = "RE2")

public String getRe2() {

return re2;

}

public void setRe2(String re2) {

this.re2 = re2;

}

@Column(name = "RE3")

public String getRe3() {

return re3;

}

public void setRe3(String re3) {

this.re3 = re3;

}

@Column(name = "RE4")

public String getRe4() {

return re4;

}

public void setRe4(String re4) {

this.re4 = re4;

}

}

package com.wisdombud.dama.quality.datasource.tablerelation.pojo;

import java.io.Serializable;

import java.util.Date;

import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import javax.persistence.SequenceGenerator;

import javax.persistence.Table;

import lombok.ToString;

/**

* 表关系实体

* @author qiaoyutao

* @Date: 2019年7月4日 上午11:40:10

*/

@Entity

@Table(name = "TABLE_RELATION")

@SequenceGenerator(name = "ID_SEQ", sequenceName = "SEQ_TABLE_RELATION", allocationSize = 1)

@ToString

public class TableRelationPojo implements Serializable{

private static final long serialVersionUID = -9058973373224769393L;

private Long id;

private Long dataSourceId;

private String tableName;

private String columnName;

private String relationTableName;

private String relationColumnName;

private String remark;

private Long createUserId;

private String createUserName;

private Date createTime;

private Date lastUpdateTime;

private String re1;

private String re2;

private String re3;

private String re4;

@Id

@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "ID_SEQ")

@Column(name = "ID", unique = true, nullable = false, precision = 18, scale = 0)

public Long getId() {

return id;

}

public void setId(Long id) {

this.id = id;

}

@Column(name = "DATA_SOURCE_ID")

public Long getDataSourceId() {

return dataSourceId;

}

public void setDataSourceId(Long dataSourceId) {

this.dataSourceId = dataSourceId;

}

@Column(name = "TABLE_NAME")

public String getTableName() {

return tableName;

}

public void setTableName(String tableName) {

this.tableName = tableName;

}

@Column(name = "COLUMN_NAME")

public String getColumnName() {

return columnName;

}

public void setColumnName(String columnName) {

this.columnName = columnName;

}

@Column(name = "RELATION_TABLE_NAME")

public String getRelationTableName() {

return relationTableName;

}

public void setRelationTableName(String relationTableName) {

this.relationTableName = relationTableName;

}

@Column(name = "RELATION_COLUMN_NAME")

public String getRelationColumnName() {

return relationColumnName;

}

public void setRelationColumnName(String relationColumnName) {

this.relationColumnName = relationColumnName;

}

@Column(name = "REMARK")

public String getRemark() {

return remark;

}

public void setRemark(String remark) {

this.remark = remark;

}

@Column(name = "CREATE_USER_ID")

public Long getCreateUserId() {

return createUserId;

}

public void setCreateUserId(Long createUserId) {

this.createUserId = createUserId;

}

@Column(name = "CREATE_USER_NAME")

public String getCreateUserName() {

return createUserName;

}

public void setCreateUserName(String createUserName) {

this.createUserName = createUserName;

}

@Column(name = "CREATE_TIME")

public Date getCreateTime() {

return createTime;

}

public void setCreateTime(Date createTime) {

this.createTime = createTime;

}

@Column(name = "LAST_UPDATE_TIME")

public Date getLastUpdateTime() {

return lastUpdateTime;

}

public void setLastUpdateTime(Date lastUpdateTime) {

this.lastUpdateTime = lastUpdateTime;

}

@Column(name = "RE1")

public String getRe1() {

return re1;

}

public void setRe1(String re1) {

this.re1 = re1;

}

@Column(name = "RE2")

public String getRe2() {

return re2;

}

public void setRe2(String re2) {

this.re2 = re2;

}

@Column(name = "RE3")

public String getRe3() {

return re3;

}

public void setRe3(String re3) {

this.re3 = re3;

}

@Column(name = "RE4")

public String getRe4() {

return re4;

}

public void setRe4(String re4) {

this.re4 = re4;

}

}

下面是我的dao层，重点

package com.wisdombud.dama.quality.datasource.tablerelation.dao;

import org.springframework.data.domain.Page;

import org.springframework.data.domain.Pageable;

import org.springframework.data.jpa.repository.JpaRepository;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

import org.springframework.data.jpa.repository.Query;

import com.wisdombud.dama.quality.datasource.tablerelation.pojo.TableRelationPojo;

import com.wisdombud.dama.quality.datasource.tablerelation.vo.TableRelationGridVo;

public interface TableRelationDao extends JpaRepository, JpaSpecificationExecutor{

// 这里使用JPQL来写的， 区别就是 new 一个vo名，最好带上全路径，并且vo中要有有参构造器，构造器签名要与查询的字段一致, 查询的属性都是实体类中的属性名，不是数据库的字段名，这点切记。

@Query(value = "SELECT new com.wisdombud.dama.quality.datasource.tablerelation.vo.TableRelationGridVo(re.id, "

+ "ds.name, re.tableName, re.columnName, "

+ "re.relationTableName, re.relationColumnName, "

+ "re.createTime) "

+ "FROM TableRelationPojo re left join DataSourcePojo ds on re.dataSourceId = ds.id")

public Page page(Pageable pageable);

}

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