Spring Data JPA 实现多表关联查询

使用 JPA 作为 ORM 框架。很多人对 JPA 抱有偏见，比如： JPA 只能处理简单的单表查询。下面总结下几种多表关联查询方法。

表结构

主表bz_package_index。

表 bz_package_index
子表bz_payeelist_bankexecut，该表的package_id字段关联主表的id字段。

表 bz_payeelist_bankexecut

第一种方法

使用 Spring 提供的 JdbcTemplate。核心代码：

List<Obj> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(Obj.class));

BeanPropertyRowMapper可以自动完成映射，也可以自定义实现特殊类型转换。另外还自动支持下划线转驼峰，只需要DTO和数据库模型的字段完全对应（字段名字一样，或者Java的驼峰式名称与数据库字段下划线式名称对应）。如果使用JdbcTemplate则可以使用这个BeanPropertyRowMapper实现DTO和数据库的映射。

定义返回数据的Model，前三个属性存储主表数据，后两个属性存储子表数据。

package com.pay.payee.model;


import lombok.Data;
import org.springframework.stereotype.Component;

import java.math.BigDecimal;

/**
 * 提交数据后，一批作为一个批次号，包号=批次号；建议由提交机构代码+时间构成(BzPackageIndex)表数据库model
 *
 * @author 郭秀志 jbcode@126.com
 * @since 2020-06-08 08:22:00
 */
@Data
@Component
public class BzPackageIndexAndExecuteListModel {
    //以下3个属性存储主表数据
    private BigDecimal moneySum;
    private BigDecimal totalIty;
    private String payerAccount;

    //以下2个属性存储子表数据。
    private String packageId;
    private BigDecimal money;
}

PayeelistBankexecutServiceImpl代码，Mapper将查询出来的数据转成上面定义的BzPackageIndexAndExecuteListModel。

package com.pay.payee.service.impl;

import com.pay.payee.entity.BzPayeelistBankexecut;
import com.pay.payee.model.BzPackageIndexAndExecuteListModel;
import com.pay.payee.repository.PayeelistBankexecutRepository;
import com.pay.payee.service.IPayeelistBankexecutService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.transaction.Transactional;
import java.util.List;

/**
 * @ClassName: PayeelistBankexecutServiceImpl
 * @Description:服务实现类
 * @author: 郭秀志 jbcode@126.com
 * @date: 2020年1月8日 上午10:44:11
 * @Copyright:
 */
@Service
@Transactional
public class PayeelistBankexecutServiceImpl implements IPayeelistBankexecutService {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private PayeelistBankexecutRepository payeelistBankexecutRepository;

 
    @Override
    public List findBzPackageIndexAndExecutListByState(String state) {
        String sql = "SELECT * FROM 
`bz_payeelist_bankexecut` list, bz_package_index idx 
WHERE idx.id = list.package_id AND idx.state =? 
limit 5";
        /**
         * 带条件查询
         */
        return jdbcTemplate.query(sql, new Object[]{state}, new BeanPropertyRowMapper(BzPackageIndexAndExecuteListModel.class));
    }

}

controller类调用service,由于使用了XML格式所以返回的数据变成了XML，通过produces = MediaType.APPLICATION_JSON_VALUE使其返回json

    @ApiVersion(5)
    @RequestMapping(value = "/findBzPackageIndexAndExecutListByState", produces = MediaType.APPLICATION_JSON_`VALUE)
    // http://localhost:8555/v5/packageIndex/findBzPackageIndexAndExecutListByState
    public List<BzPackageIndexAndExecuteListModel> findBzPackageIndexAndExecutListByState() throws InterruptedException {
        return payeelistBankexecutService.findBzPackageIndexAndExecutListByState("80");
    }

测试
访问url:http://localhost:8085/v5/packageIndex/findBzPackageIndexAndExecutListByState
返回json数据：

[
  {
    "moneySum": 29459.00,
    "totalIty": 26,
    "payerAccount": "622848002565612",
    "packageId": "-202004-1586060312402",
    "money": 802.00
  },
  {
    "moneySum": 29459.00,
    "totalIty": 26,
    "payerAccount": "622848002565612",
    "packageId": "-202004-1586060312402",
    "money": 2801.00
  },
  {
    "moneySum": 29459.00,
    "totalIty": 26,
    "payerAccount": "622848002565612",
    "packageId": "-202004-1586060312402",
    "money": 2801.00
  },
  {
    "moneySum": 29459.00,
    "totalIty": 26,
    "payerAccount": "622848002565612",
    "packageId": "-202004-1586060312402",
    "money": 2802.00
  },
  {
    "moneySum": 29459.00,
    "totalIty": 26,
    "payerAccount": "622848002565612",
    "packageId": "-202004-1586060312402",
    "money": 807.00
  }
]

第二种方法（推荐）

SpringBoot使用Jpa两张表联查返回自定义实体。

定义数据存储的Model，来接收两张表返回的数据，注意：此时创建的是一个interface，并且里面的字段是用get的形式创建的接收参数。

package com.pay.payee.model;

import java.math.BigDecimal;

/**
 * @ClassName: IPackageIndexAndExecuteList
 * @Description: 2张表级联查询。定义返回数据的接口。
 * @author: 郭秀志 jbcode@126.com
 * @date: 2020/6/8 11:41
 * @Copyright:
 */
public interface IPackageIndexAndExecuteList {

    //以下3个get方法存储主表对应数据
    BigDecimal getMoneySum();
    BigDecimal getTotalIty();
    String getPayerAccount();

    //以下2个get方法存储子表对应数据
    String getPackageId();
    BigDecimal getMoney();
}

在主表或者子表的Repository类定义原生sql,返回上面定义的接口类型数据List<IPackageIndexAndExecuteList>。注意sql字段的别名对应接口中的getXXX后面名称，如:getMoneySum跟sql中的money_sum as moneySum对应；money字段名与接口相同，则可以不用别名转换自动映射值。

/**
 * @ClassName: PayeelistBankexecutRepository
 * @Description: PayeelistBankexecutRepository持久层类，定义跟数据库操作的接口
 * @author: 郭秀志 jbcode@126.com
 * @date: 2020年1月8日 上午10:42:50
 * @Copyright:
 */
public interface PayeelistBankexecutRepository extends JpaRepository<BzPayeelistBankexecut, Long> {

    // 通过BzPackageIndex状态筛选待执行的银行收款数据。
    @Query(nativeQuery = true, value = "SELECT money_sum as moneySum,total_ity as totalIty,idx.payer_account as payerAccount,package_id as packageId,money FROM `bz_payeelist_bankexecut` list, bz_package_index idx WHERE idx.id = list.package_id AND idx.state =?1 limit 5")
    // 原生SQL方法
    List<IPackageIndexAndExecuteList> findPackageIndexAndExecuteList_State(String state);

Service调用Repository

@Service
@Transactional
public class PayeelistBankexecutServiceImpl implements IPayeelistBankexecutService {
    
    @Autowired
    private PayeelistBankexecutRepository payeelistBankexecutRepository;

    @Override
    public List<IPackageIndexAndExecuteList> findPackageIndexAndExecuteList_State(String state) {
        return payeelistBankexecutRepository.findPackageIndexAndExecuteList_State(state);
    }

Controller调用Service

    @ApiVersion(5)
    @RequestMapping(value = "/findPackageIndexAndExecuteList_State", produces = MediaType.APPLICATION_JSON_VALUE)
    // http://localhost:8555/v5/packageIndex/findPackageIndexAndExecuteList_State
    public List<IPackageIndexAndExecuteList> findPackageIndexAndExecuteList_State(String state) {
        List<IPackageIndexAndExecuteList> packageIndexAndExecuteList = payeelistBankexecutService.findPackageIndexAndExecuteList_State("80");
        packageIndexAndExecuteList.stream().forEach(dto -> {
            log.info("result: moneySum:{}, totalIty:{}, payerAccount:{}, packageId:{}", dto.getMoneySum(), dto.getTotalIty(), dto.getMoney(), dto.getPayerAccount(), dto.getPackageId());
        });

        return packageIndexAndExecuteList;
    }

测试
访问url:http://localhost:8085/v5/packageIndex/findPackageIndexAndExecuteList_State
返回json数据：

[
  {
    "moneySum": 29459.00,
    "totalIty": 26,
    "money": 802.00,
    "payerAccount": "622848002565612",
    "packageId": "-202004-1586060312402"
  },
  {
    "moneySum": 29459.00,
    "totalIty": 26,
    "money": 2801.00,
    "payerAccount": "622848002565612",
    "packageId": "-202004-1586060312402"
  },
  {
    "moneySum": 29459.00,
    "totalIty": 26,
    "money": 2801.00,
    "payerAccount": "622848002565612",
    "packageId": "-202004-1586060312402"
  },
  {
    "moneySum": 29459.00,
    "totalIty": 26,
    "money": 2802.00,
    "payerAccount": "622848002565612",
    "packageId": "-202004-1586060312402"
  },
  {
    "moneySum": 29459.00,
    "totalIty": 26,
    "money": 807.00,
    "payerAccount": "622848002565612",
    "packageId": "-202004-1586060312402"
  }
]

联查出来的数据结果，都存储在DTO中。控制台打印数据信息：

控制台循环打印dto对象

总结

接收的DTO一定要是interface，里面的参数要写成get形式的方法体，这样jpa在查询到数据后，会自动映射到interface里，通过调用get的方法体相当于调用了取参数值，这样把数据取出来。