溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
小編給大家分享一下Spring Data Jpa多表查詢?nèi)绾畏祷刈远x實(shí)體，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
比如來看一下這樣的一條SQL語句，這是一個三張表的多表查詢，顯然在JPA中用一個實(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';
/**
* 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;
}/**
* 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í)體類字段的順序保持一致，否則會出現(xiàn)參數(shù)封裝錯誤的情況
這兩天開了一個新項目，使用SpringBoot+SpringData， 剛做了一個小功能，都是一張表的操作沒什么問題，今天設(shè)計到了兩張表聯(lián)查，兩張表各取了幾個字段，組合成了一個vo， 當(dāng)我用原生sql查詢時報出 “找不到轉(zhuǎn)換器”，當(dāng)我用JPQL查詢時，報出 xxx is not mapped.。 著實(shí)浪費(fèi)了些時間。
其實(shí)最大的根本原因在于， 當(dāng)使用JPQL 查詢時，我們 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 = "端口號不能為空！")
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;
}
}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 一個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);
}以上是“Spring Data Jpa多表查詢?nèi)绾畏祷刈远x實(shí)體”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識，歡迎關(guān)注億速云行業(yè)資訊頻道！
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機(jī)網(wǎng)站二維碼