欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

Spring Data Jpa多表查詢返回自定義實(shí)體方式

 更新時(shí)間：2022年02月23日 09:46:28   作者：ZhuPengWei_  
這篇文章主要介紹了Spring Data Jpa多表查詢返回自定義實(shí)體方式，具有很好的參考價(jià)值，希望對大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

SpringDataJpa多表查詢返回自定義實(shí)體

比如來看一下這樣的一條SQL語句，這是一個(gè)三張表的多表查詢，顯然在JPA中用一個(gè)實(shí)體類是接受不了這些參數(shù)的

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

好下面到單元測試

/**
?* 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<Object[]> select = testRepository.select("4028b4816305ea91016305eec24f0000", "4028b4816305f1a6016305f423180000", "4028b4816306007b016306020bb80000");
? ? ? ? List<TestView> testViews = castEntity(select, TestView.class);
? ? ? ? Assert.assertTrue(testViews.size() > 0);
? ? }
? ? //轉(zhuǎn)換實(shí)體類
? ?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];
? ? ? ? //確定構(gòu)造方法
? ? ? ? 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;
? ? }

自定義實(shí)體

/**
?* 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) {
? ? ? ? this.chapterId = chapterId;
? ? ? ? this.chapterName = chapterName;
? ? ? ? this.unitId = unitId;
? ? ? ? this.unitName = unitName;
? ? ? ? this.lessonId = lessonId;
? ? ? ? this.lessonName = lessonName;
? ? }
}

需要注意的是 SQL語句的字段順序一定要與實(shí)體類字段的順序保持一致，否則會(huì)出現(xiàn)參數(shù)封裝錯(cuò)誤的情況 

Spring Data Jpa多表查詢返回自定義VO的問題

這兩天開了一個(gè)新項(xiàng)目，使用SpringBoot+SpringData，  剛做了一個(gè)小功能，都是一張表的操作沒什么問題，今天設(shè)計(jì)到了兩張表聯(lián)查，兩張表各取了幾個(gè)字段，組合成了一個(gè)vo， 當(dāng)我用原生sql查詢時(shí)報(bào)出 “找不到轉(zhuǎn)換器”，當(dāng)我用JPQL查詢時(shí)，報(bào)出 xxx is not mapped.。 著實(shí)浪費(fèi)了些時(shí)間。  

其實(shí)最大的根本原因在于， 當(dāng)使用JPQL 查詢時(shí)，我們 FROM 的表名就不能是實(shí)體上@Table 注解中的表名，而是對應(yīng)的實(shí)體的類名，記住是類名，并且查詢的字段要是 實(shí)體類的屬性，而不是數(shù)據(jù)庫字段。切記。

下面是我的代碼

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 = "數(shù)據(jù)源名稱不能為空！")
    private String name;
    
    @NotBlank(message = "ip地址不能為空！")
    private String ip;
    
    @NotBlank(message = "端口號(hào)不能為空！")
    private String port;
    
    @NotBlank(message = "服務(wù)名不能為空！")
    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;
/**
 * 表關(guān)系實(shí)體
 * @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層，重點(diǎn)

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來寫的， 區(qū)別就是 new 一個(gè)vo名，最好帶上全路徑，并且vo中要有有參構(gòu)造器，構(gòu)造器簽名要與查詢的字段一致, 查詢的屬性都是實(shí)體類中的屬性名，不是數(shù)據(jù)庫的字段名，這點(diǎn)切記。
    @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);    
}

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

相關(guān)文章

  • Java基于代理模式解決紅酒經(jīng)銷問題詳解

    Java基于代理模式解決紅酒經(jīng)銷問題詳解

    這篇文章主要介紹了Java基于代理模式解決紅酒經(jīng)銷問題,詳細(xì)描述了代理模式的概念、原理并結(jié)合實(shí)例形式分析了java基于代理模式解決紅酒經(jīng)銷問題的相關(guān)步驟、實(shí)現(xiàn)方法與操作注意事項(xiàng),需要的朋友可以參考下
    2018-04-04
  • Java集合系列之ArrayList源碼分析

    Java集合系列之ArrayList源碼分析

    這篇文章主要為大家詳細(xì)介紹了Java集合系列之ArrayList源碼，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下
    2018-02-02
  • Java AQS中CyclicBarrier回環(huán)柵欄的使用

    Java AQS中CyclicBarrier回環(huán)柵欄的使用

    這篇文章主要介紹了Java中的 CyclicBarrier詳解，CyclicBarrier沒有顯示繼承哪個(gè)父類或者實(shí)現(xiàn)哪個(gè)父接口, 所有AQS和重入鎖不是通過繼承實(shí)現(xiàn)的，而是通過組合實(shí)現(xiàn)的，下文相關(guān)內(nèi)容需要的小伙伴可以參考一下
    2023-02-02
  • feign post參數(shù)對象不加@RequestBody的使用說明

    feign post參數(shù)對象不加@RequestBody的使用說明

    這篇文章主要介紹了feign post參數(shù)對象不加@RequestBody的使用說明，具有很好的參考價(jià)值，希望對大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教
    2021-10-10
  • Idea插件MybatisX失效的問題解決

    Idea插件MybatisX失效的問題解決

    這篇文章主要介紹了Idea插件MybatisX失效的問題解決,詳細(xì)的介紹了4種問題的解決方法,具有一定的參考價(jià)值,感興趣的可以了解一下
    2025-05-05
  • Java項(xiàng)目中添加外部jar包的兩種方式(收藏版)

    Java項(xiàng)目中添加外部jar包的兩種方式(收藏版)

    這篇文章主要介紹了java項(xiàng)目中添加外部jar包的兩種方式,第二種方式是將外部jar包引入到本地maven倉庫中，本文給大家講解的非常詳細(xì)，需要的朋友可以參考下
    2023-03-03
  • SpringBoot整合EasyExcel實(shí)現(xiàn)復(fù)雜Excel表格的導(dǎo)入導(dǎo)出

    SpringBoot整合EasyExcel實(shí)現(xiàn)復(fù)雜Excel表格的導(dǎo)入導(dǎo)出

    這篇文章主要為大家詳細(xì)介紹了SpringBoot如何整合EasyExcel實(shí)現(xiàn)復(fù)雜Excel表格的導(dǎo)入導(dǎo)出功能,文中的示例代碼講解詳細(xì),感興趣的小伙伴可以參考下
    2023-11-11
  • javaWeb實(shí)現(xiàn)簡單文件上傳

    javaWeb實(shí)現(xiàn)簡單文件上傳

    這篇文章主要為大家詳細(xì)介紹了JAVAWeb實(shí)現(xiàn)簡單文件上傳，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下
    2022-06-06
  • java 單例模式和工廠模式實(shí)例詳解

    java 單例模式和工廠模式實(shí)例詳解

    這篇文章主要介紹了Java設(shè)計(jì)模式編程中的單例模式和簡單工廠模式以及實(shí)例,使用設(shè)計(jì)模式編寫代碼有利于團(tuán)隊(duì)協(xié)作時(shí)程序的維護(hù),需要的朋友可以參考下
    2017-04-04
  • SpringCloud啟動(dòng)失敗問題匯總

    SpringCloud啟動(dòng)失敗問題匯總

    這篇文章主要介紹了SpringCloud啟動(dòng)失敗問題匯總，具有很好的參考價(jià)值，希望對大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教
    2023-03-03

最新評論