SpringDataJpa多表查询返回自定义实体
ZhuPengWei_ 人气:0SpringDataJpa多表查询返回自定义实体
比如来看一下这样的一条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<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); } //转换实体类 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; }
自定义实体
/** * 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语句的字段顺序一定要与实体类字段的顺序保持一致,否则会出现参数封装错误的情况
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<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); }
以上为个人经验,希望能给大家一个参考,也希望大家多多支持。
加载全部内容