前言
在java開發(fā)中，用Jpa框架做連表查詢時(需要返回兩張表的各自部分字段)，在返回對象的過程中感覺比較棘手，一直沒有一個好的解決方案，網(wǎng)上也有各種版本的方法，下面的方法本人感覺最方便使用
1、創(chuàng)建一個SpringBoot空白項目，引入pom依賴
先看項目結(jié)構(gòu)，為了簡化，沒有引入service層，直接使用controller調(diào)用dao層
1.png
pom.xml配置
<!-- web依賴 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- lombok依賴 -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.8</version>
</dependency>
<!-- jpa依賴 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<!-- mysql依賴 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
2、application.yml配置文件
server:
port: 13333
spring:
datasource:
url: jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=convertToNull
username: root
password: 12345678
driver-class-name: com.mysql.jdbc.Driver
jpa:
show-sql: true
hibernate:
ddl-auto: none
3、數(shù)據(jù)庫（有兩張表user/address）
2.png
我們現(xiàn)在需要聯(lián)查user和address表，address表中的user_id是和user表中id是做關(guān)聯(lián)查詢
4、User.java 和 Address.java
3.png
5、UserDaoRepository.java 和 AddressDaoRepository.java
4.png
附上UserDaoRepository.java的代碼
package com.lss.jpa.dao;
import com.lss.jpa.entity.dto.UserAddressDto;
import com.lss.jpa.entity.po.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import java.util.List;
import java.util.Map;
public interface UserDaoRepository extends JpaRepository<User, Integer> {
@Query(value = "select \"title\" as common, u.id as id, u.name as name, a.id as addressId, a.address as addressName from user u, address a where u.id = a.user_id", nativeQuery = true)
public List<UserAddressDto> findAllUserAddress();
@Query(value = "select \"title\" as common, u.id as id, u.name as name, a.id as addressId, a.address as addressName from user u, address a where u.id = a.user_id and u.id=1", nativeQuery = true)
public UserAddressDto findAllUserAddressById();
@Query(value = "select \"title\" as common, u.id as id, u.name as name, a.id as addressId, a.address as addressName from user u, address a where u.id = a.user_id and u.id=1", nativeQuery = true)
public Map<String, Object> findAllUserAddressByMap();
}
5、UserAddressDto.java代碼
package com.lss.jpa.entity.dto;
public interface UserAddressDto {
Integer getId();
String getName();
String getAddressName();
Integer getAddressId();
String getCommon();
}
此處我們自定義了UserAdressDto來接收兩張表返回的數(shù)據(jù)，注意：此時創(chuàng)建的是一個interface，并且里面的字段是用get的形式創(chuàng)建的接收參數(shù)
6、TestController.java
package com.lss.jpa.web;
import com.lss.jpa.dao.UserDaoRepository;
import com.lss.jpa.entity.dto.UserAddressDto;
import com.lss.jpa.entity.po.User;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
import java.util.Map;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;
@RestController
@Slf4j
public class TestController {
@Autowired
private UserDaoRepository userDaoRepository;
@GetMapping("test")
public String test(){
List<UserAddressDto> all = userDaoRepository.findAllUserAddress();
all.stream().forEach(dto -> {
log.info("result: id:{}, name:{}, addressId:{}, addressName:{}, common:{}", dto.getId(), dto.getName(), dto.getAddressId(), dto.getAddressName(), dto.getCommon());
});
UserAddressDto dto = userDaoRepository.findAllUserAddressById();
log.info("result: id:{}, name:{}, addressId:{}, addressName:{}, common:{}", dto.getId(), dto.getName(), dto.getAddressId(), dto.getAddressName(), dto.getCommon());
Map<String, Object> map = userDaoRepository.findAllUserAddressByMap();
log.info("map:{}", map);
List<User> userList = userDaoRepository.findAll();
log.info("userList:{}", userList);
return "ok";
}
}
最后，啟動項目，調(diào)用/test接口
curl http://localhost:13333/test
看console里打印結(jié)果
Hibernate: select "title" as common, u.id as id, u.name as name, a.id as addressId, a.address as addressName from user u, address a where u.id = a.user_id
2020-02-23 13:14:33.293 INFO 2816 --- [io-13333-exec-3] com.lss.jpa.web.TestController : result: id:1, name:zhangsan , addressId:1, addressName:beijing, common:title
2020-02-23 13:14:33.293 INFO 2816 --- [io-13333-exec-3] com.lss.jpa.web.TestController : result: id:2, name:lisi, addressId:2, addressName:tianjin, common:title
Hibernate: select "title" as common, u.id as id, u.name as name, a.id as addressId, a.address as addressName from user u, address a where u.id = a.user_id and u.id=1
2020-02-23 13:14:33.296 INFO 2816 --- [io-13333-exec-3] com.lss.jpa.web.TestController : result: id:1, name:zhangsan , addressId:1, addressName:beijing, common:title
Hibernate: select "title" as common, u.id as id, u.name as name, a.id as addressId, a.address as addressName from user u, address a where u.id = a.user_id and u.id=1
2020-02-23 13:14:33.299 INFO 2816 --- [io-13333-exec-3] com.lss.jpa.web.TestController : map:org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap@72cce81
Hibernate: select user0_.id as id1_1_, user0_.name as name2_1_ from user user0_
2020-02-23 13:14:33.305 INFO 2816 --- [io-13333-exec-3] com.lss.jpa.web.TestController : userList:[User(id=1, name=zhangsan ), User(id=2, name=lisi), User(id=3, name=wangwu), User(id=4, name=zhaoliu)]
我們可以拷到輸出的sql和聯(lián)查出來的數(shù)據(jù)結(jié)果，都被dto完美接收
