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

2024-04-02 19:04:59 815人浏览独家记忆

Python 官方文档：入门教程 => 点击学习

摘要

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


public interface TestRepository extends JpaRepository<ProductInfo, String > {
    @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<Object[]> select(@Param("chapterId") String chapterId, @Param("unitId") String unitId, @Param("lessonId") String lessonId);
}

好下面到单元测试


@RunWith(SpringRunner.class)
@SpringBootTest
public class TestRepositoryTest {
    @Autowired
    private TestRepository testRepository;
    @Test
    public void select() throws Exception {
        List<Object[]> select = testRepository.select("4028b4816305ea91016305eec24f0000", "4028b4816305f1a6016305f423180000", "4028b4816306007b016306020bb80000");
        List<TestView> testViews = castEntity(select, TestView.class);
        Assert.assertTrue(testViews.size() > 0);
    }
    //转换实体类
   public static <T> List<T> castEntity(List<Object[]> list, Class<T> clazz) throws Exception {
        List<T> returnList = new ArrayList<T>();
        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<T> constructor = clazz.getConstructor(c2);
            returnList.add(constructor.newInstance(o));
        }
        return returnList;
    }

自定义实体


@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) {
        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;

@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<TableRelationPojo, Long>, JpaSpecificationExecutor<TableRelationPojo>{
    
    // 这里使用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<TableRelationGridVo> page(Pageable pageable);    
}

以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程网。

您可能感兴趣的文档:

点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: SpringDataJpa多表查询返回自定义实体方式

本文链接: https://www.lsjlt.com/news/140147.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档

去做题

猜你喜欢

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

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

99+

2022-11-13
Spring Data Jpa多表查询如何返回自定义实体

小编给大家分享一下Spring Data Jpa多表查询如何返回自定义实体，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！SpringDataJ...

99+

2023-06-29
JPA怎么使用nativequery多表关联查询返回自定义实体类

这篇文章主要介绍了JPA怎么使用nativequery多表关联查询返回自定义实体类，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。JPA nativequery多表关联查询返回...

99+

2023-06-25
JPA如何使用nativequery多表关联查询返回自定义实体类

目录JPA nativequery多表关联查询返回自定义实体类JPA多表关联的实现方式优缺点对比使用sql并返回自定义实体类JPA多表关联动态查询（自定义sql语句）实体类注解解释测...

99+

2022-11-12
springboot jpa 实现返回结果自定义查询

目录jpa 返回结果自定义查询第一种方法第二种方法使用jpa两张表联查返回自定义实体1、创建一个SpringBoot空白项目，引入pom依赖2、application.yml配置文件...

99+

2022-11-13
SpringBoot+JPA 分页查询指定列并返回指定实体方式

目录SpringBoot JPA分页查询指定列并返回指定实体实体类对应表来创建，举个例子SpringBoot JPA实现自定义语句分页查询1.JPA持久层 InvoiceReposi...

99+

2022-11-12
Spring Data JPA实现查询结果返回map或自定义的实体类

目录Spring Data JPA查询结果返回map或自定义的实体类1.工具类2.具体应用spingboot:jpa:Spring data jpa 返回map 结果集Spring ...

99+

2022-11-12
springboot jpa如何实现返回结果自定义查询

这篇文章主要讲解了“springboot jpa如何实现返回结果自定义查询”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“springboot jpa如何实现返回结果自定义...

99+

2023-06-29
spring data jpa 查询自定义字段,转换为自定义实体方式

目标：查询数据库中的字段，然后转换成 JSON 格式的数据，返回前台。环境：idea 2016.3.4， jdk 1.8， mysql 5.6， spring-boot 1.5.2...

99+

2022-11-12
mybatis-plus实现自定义SQL、多表查询与多表分页查询语句实例

目录前言1、自定义SQL2、多表查询3、多表分页查询4、多表分页条件查询总结前言本文介绍了在mybatis-plus中如何实现：自定义SQL语句，多表查询语句，多表分页查询语句在...

99+

2022-11-13
Spring Data Jpa 复杂查询方式总结(多表关联及自定义分页)

目录Spring Data Jpa复杂查询总结1.查找出Id小于3,并且名称带有shanghai的记录2.通过旅店名称分页查询旅店以及城市的所有信息3.HQL通过旅店名称查询旅店以及...

99+

2022-11-13
使用AOP+反射实现自定义Mybatis多表关联查询

目录一、需求二、核心代码MapToDoMapIDualMapperDualMapperDoMapAspect三、使用方法SysUserSysRoleSysPermissionSysU...

99+

2022-11-13
怎么使用AOP+反射实现自定义Mybatis多表关联查询

这篇“怎么使用AOP+反射实现自定义Mybatis多表关联查询”文章的知识点大部分人都不太理解，所以小编给大家总结了以下内容，内容详细，步骤清晰，具有一定的借鉴价值，希望大家阅读完这篇文章能有所收获，下面我们一起来看看这篇“怎么使用AOP+...

99+

2023-06-30