mybatis的一对一与一对多
·
我现在有两个表，是一对多的关系，CREATE TABLE repayment_plan (
，针对一个还款计划可多次进行还款'; 下面请帮我映射成两个bean对象
ID bigint(20) NOT NULL COMMENT '主键',
CONSUMER_ID bigint(20) DEFAULT NULL COMMENT '发标人用户标识',
USER_NO varchar(50) DEFAULT NULL COMMENT '发标人用户编码',
PROJECT_ID bigint(20) DEFAULT NULL COMMENT '标的标识',
PROJECT_NO varchar(50) DEFAULT NULL COMMENT '标的编码',
NUMBER_OF_PERIODS int(11) DEFAULT NULL COMMENT '期数',
INTEREST decimal(10,2) DEFAULT NULL COMMENT '还款利息',
PRINCIPAL decimal(10,2) DEFAULT NULL COMMENT '还款本金',
AMOUNT decimal(10,2) DEFAULT NULL COMMENT '本息',
SHOULD_REPAYMENT_DATE datetime DEFAULT NULL COMMENT '应还时间',
REPAYMENT_STATUS varchar(50) DEFAULT NULL COMMENT '应还状态0.待还,1.已还， 2.部分还款',
CREATE_DATE datetime DEFAULT NULL COMMENT '计划创建时间',
COMMISSION decimal(10,2) DEFAULT NULL COMMENT '年化利率(平台佣金，利差)',
PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='借款人还款计划';
CREATE TABLE repayment_detail (
ID bigint(20) NOT NULL COMMENT '主键',
REPAYMENT_PLAN_ID bigint(20) DEFAULT NULL COMMENT '还款计划项标识',
AMOUNT decimal(10,2) DEFAULT NULL COMMENT '实还本息',
REPAYMENT_DATE datetime DEFAULT NULL COMMENT '实际还款时间',
REQUEST_NO varchar(50) DEFAULT NULL COMMENT '冻结用户资金请求流水号(用于解冻合并整体还款)，
STATUS tinyint(4) DEFAULT NULL COMMENT '可用状态',
PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='借款人还款明细以下是对应的Java Bean对象：
- RepaymentPlan.java
import java.math.BigDecimal;
import java.time.LocalDateTime;
public class RepaymentPlan {
private Long id;
private Long consumerId;
private String userNo;
private Long projectId;
private String projectNo;
private Integer numberOfPeriods;
private BigDecimal interest;
private BigDecimal principal;
private BigDecimal amount;
private LocalDateTime shouldRepaymentDate;
private String repaymentStatus;
private LocalDateTime createDate;
private BigDecimal commission;
// getter 和 setter 略
}
- RepaymentDetail.java
import java.math.BigDecimal;
import java.time.LocalDateTime;
public class RepaymentDetail {
private Long id;
private Long repaymentPlanId;
private BigDecimal amount;
private LocalDateTime repaymentDate;
private String requestNo;
private Integer status;
// getter 和 setter 略
}
我所使用的是mybatisPlus,我想查询RepaymentPlanDTO,这个DTO有一个属性List<RepaymentDetailDTO>, 一对多， java代码如何实现
好的，如果使用MybatisPlus，则需要先定义对应的MybatisPlus相关的Mapper接口和XML文件，考虑到你的需求，以下是对应的Mapper和XML的代码示例：
- RepaymentPlanMapper.java
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
public interface RepaymentPlanMapper extends BaseMapper<RepaymentPlan> {
// 定义方法查询RepaymentPlanDTO及对应的RepaymentDetailDTO列表
List<RepaymentPlanDTO> selectRepaymentPlanWithDetail();
}
- repayment_plan.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.example.mapper.RepaymentPlanMapper">
<resultMap id="repaymentPlanResultMap" type="com.example.dto.RepaymentPlanDTO" >
<!-- 主表字段映射 -->
<id property="id" column="ID" />
<result property="consumerId" column="CONSUMER_ID" />
<result property="userNo" column="USER_NO" />
<result property="projectId" column="PROJECT_ID" />
<result property="projectNo" column="PROJECT_NO" />
<result property="numberOfPeriods" column="NUMBER_OF_PERIODS" />
<result property="interest" column="INTEREST" />
<result property="principal" column="PRINCIPAL" />
<result property="amount" column="AMOUNT" />
<result property="shouldRepaymentDate" column="SHOULD_REPAYMENT_DATE" />
<result property="repaymentStatus" column="REPAYMENT_STATUS" />
<result property="createDate" column="CREATE_DATE" />
<result property="commission" column="COMMISSION" />
<!-- 一对多关联字段映射 -->
<collection property="repaymentDetailList" ofType="com.example.dto.RepaymentDetailDTO" >
<id property="id" column="DETAIL_ID" />
<result property="repaymentPlanId" column="RP_ID" />
<result property="amount" column="DETAIL_AMOUNT" />
<result property="repaymentDate" column="REPAYMENT_DATE" />
<result property="requestNo" column="REQUEST_NO" />
<result property="status" column="STATUS" />
</collection>
</resultMap>
<select id="selectRepaymentPlanWithDetail" resultMap="repaymentPlanResultMap" >
SELECT
rp.ID, rp.CONSUMER_ID, rp.USER_NO, rp.PROJECT_ID, rp.PROJECT_NO, rp.NUMBER_OF_PERIODS, rp.INTEREST,
rp.PRINCIPAL, rp.AMOUNT, rp.SHOULD_REPAYMENT_DATE, rp.REPAYMENT_STATUS, rp.CREATE_DATE, rp.COMMISSION,
rd.ID AS DETAIL_ID, rd.REPAYMENT_PLAN_ID AS RP_ID, rd.AMOUNT AS DETAIL_AMOUNT, rd.REPAYMENT_DATE,
rd.REQUEST_NO, rd.STATUS
FROM repayment_plan rp LEFT JOIN repayment_detail rd ON rd.REPAYMENT_PLAN_ID=rp.ID
</select>
</mapper>
- RepaymentPlanDTO.java
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
public class RepaymentPlanDTO {
private Long id;
private Long consumerId;
private String userNo;
private Long projectId;
private String projectNo;
private Integer numberOfPeriods;
private BigDecimal interest;
private BigDecimal principal;
private BigDecimal amount;
private LocalDateTime shouldRepaymentDate;
private String repaymentStatus;
private LocalDateTime createDate;
private BigDecimal commission;
// 一对多关联属性
private List<RepaymentDetailDTO> repaymentDetailList;
// getter 和 setter 略
}
- RepaymentDetailDTO.java
import java.math.BigDecimal;
import java.time.LocalDateTime;
public class RepaymentDetailDTO {
private Long id;
private Long repaymentPlanId;
private BigDecimal amount;
private LocalDateTime repaymentDate;
private String requestNo;
private Integer status;
// getter 和 setter 略
}
在Java代码中使用的话，调用RepaymentPlanMapper中定义的方法可以直接查询出RepaymentPlanDTO及对应的RepaymentDetailDTO列表，示例代码如下：
@Autowired
private RepaymentPlanMapper repaymentPlanMapper;
// 查询RepaymentPlanDTO及对应的RepaymentDetailDTO列表
List<RepaymentPlanDTO> repaymentPlanList = repaymentPlanMapper.selectRepaymentPlanWithDetail();
下面是一对一：
如果是一对一的关系，则需要对应修改Mapper接口、XML文件以及对应的DTO类。
下面以还款计划和借款人信息为例，示例代码如下：
- RepaymentPlanMapper.java
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
public interface RepaymentPlanMapper extends BaseMapper<RepaymentPlan> {
// 定义方法查询RepaymentPlanDTO及对应的UserInfoDTO
List<RepaymentPlanDTO> selectRepaymentPlanWithUserInfo();
}
- repayment_plan.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.example.mapper.RepaymentPlanMapper">
<resultMap id="repaymentPlanResultMap" type="com.example.dto.RepaymentPlanDTO" >
<!-- 主表字段映射 -->
<id property="id" column="ID" />
<result property="consumerId" column="CONSUMER_ID" />
<result property="userNo" column="USER_NO" />
<result property="projectId" column="PROJECT_ID" />
<result property="projectNo" column="PROJECT_NO" />
<result property="numberOfPeriods" column="NUMBER_OF_PERIODS" />
<result property="interest" column="INTEREST" />
<result property="principal" column="PRINCIPAL" />
<result property="amount" column="AMOUNT" />
<result property="shouldRepaymentDate" column="SHOULD_REPAYMENT_DATE" />
<result property="repaymentStatus" column="REPAYMENT_STATUS" />
<result property="createDate" column="CREATE_DATE" />
<result property="commission" column="COMMISSION" />
<!-- 一对一关联字段映射 -->
<association property="userInfo" javaType="com.example.dto.UserInfoDTO">
<id property="id" column="USER_ID"/>
<result property="userName" column="USER_NAME" />
<result property="mobileNo" column="MOBILE_NO" />
</association>
</resultMap>
<select id="selectRepaymentPlanWithUserInfo" resultMap="repaymentPlanResultMap" >
SELECT
rp.ID, rp.CONSUMER_ID, rp.USER_NO, rp.PROJECT_ID, rp.PROJECT_NO, rp.NUMBER_OF_PERIODS, rp.INTEREST,
rp.PRINCIPAL, rp.AMOUNT, rp.SHOULD_REPAYMENT_DATE, rp.REPAYMENT_STATUS, rp.CREATE_DATE, rp.COMMISSION,
ui.ID AS USER_ID, ui.USER_NAME, ui.MOBILE_NO
FROM repayment_plan rp LEFT JOIN user_info ui ON ui.ID=rp.USER_ID
</select>
</mapper>
- RepaymentPlanDTO.java
import java.math.BigDecimal;
import java.time.LocalDateTime;
public class RepaymentPlanDTO {
private Long id;
private Long consumerId;
private String userNo;
private Long projectId;
private String projectNo;
private Integer numberOfPeriods;
private BigDecimal interest;
private BigDecimal principal;
private BigDecimal amount;
private LocalDateTime shouldRepaymentDate;
private String repaymentStatus;
private LocalDateTime createDate;
private BigDecimal commission;
// 一对一关联属性
private UserInfoDTO userInfo;
// getter 和 setter 略
}
- UserInfoDTO.java
public class UserInfoDTO {
private Long id;
private String userName;
private String mobileNo;
// getter 和 setter 略
}
然后在Java代码中调用RepaymentPlanMapper中定义的方法即可查询出RepaymentPlanDTO和对应的UserInfoDTO，示例代码如下：
@Autowired
private RepaymentPlanMapper repaymentPlanMapper;
// 查询RepaymentPlanDTO和对应的UserInfoDTO
List<RepaymentPlanDTO> repaymentPlanList = repaymentPlanMapper.selectRepaymentPlanWithUserInfo();下面是mybatisplus的代码;
多实例
使用BeanUtils.copyProperties进行对象复制：
public List<PlanDTO> getPlanDTOList() {
List<PlanDTO> planDTOList = new ArrayList<>();
QueryWrapper<RepaymentPlan> planQueryWrapper = new QueryWrapper<>();
planQueryWrapper.select("id", "consumer_id", "user_no", "project_id", "project_no", "number_of_periods", "interest", "principal", "amount", "should_repayment_date", "repayment_status", "create_date", "commission")
.eq("t1.xxx", "xxx");
List<RepaymentPlan> planList = repaymentPlanMapper.selectList(planQueryWrapper);
for(RepaymentPlan plan : planList) {
PlanDTO planDTO = new PlanDTO();
List<DetailDTO> detailDTOList = new ArrayList<>();
BeanUtils.copyProperties(plan, planDTO);
QueryWrapper<RepaymentDetail> detailQueryWrapper = new QueryWrapper<>();
detailQueryWrapper.eq("repayment_plan_id", plan.getId());
List<RepaymentDetail> detailList = repaymentDetailMapper.selectList(detailQueryWrapper);
for(RepaymentDetail detail : detailList) {
DetailDTO detailDTO = new DetailDTO();
BeanUtils.copyProperties(detail, detailDTO);
detailDTOList.add(detailDTO);
}
planDTO.setDetailList(detailDTOList);
planDTOList.add(planDTO);
}
return planDTOList;
}
这样代码直观、简洁，同时还提高了代码的可读性。
是的，可以在同一个QueryWrapper中查询主表和子表，并使用左连接进行关联查询。这样可以避免拆分查询，提高代码的可维护性。修改后的代码如下：
public List<PlanDTO> getPlanDTOList() {
List<PlanDTO> planDTOList = new ArrayList<>();
QueryWrapper<RepaymentPlan> planQueryWrapper = new QueryWrapper<>();
planQueryWrapper.select("t1.id", "t1.consumer_id", "t1.user_no", "t1.project_id", "t1.project_no", "t1.number_of_periods", "t1.interest", "t1.principal", "t1.amount", "t1.should_repayment_date", "t1.repayment_status", "t1.create_date", "t1.commission", "t2.id as detail_id", "t2.repayment_plan_id", "t2.interest as detail_interest", "t2.principal as detail_principal", "t2.amount as detail_amount", "t2.should_repayment_date as detail_should_repayment_date", "t2.repayment_status as detail_repayment_status", "t2.overdue_days as detail_overdue_days", "t2.create_date as detail_create_date")
.eq("t1.xxx", "xxx")
.leftJoin("repayment_detail t2 on t1.id = t2.repayment_plan_id");
List<Map<String, Object>> mapList = repaymentPlanMapper.selectMaps(planQueryWrapper);
for(Map<String, Object> map : mapList) {
PlanDTO planDTO = new PlanDTO();
List<DetailDTO> detailDTOList = new ArrayList<>();
BeanUtils.populate(planDTO, map);
DetailDTO detailDTO = new DetailDTO();
BeanUtils.populate(detailDTO, map);
detailDTOList.add(detailDTO);
planDTO.setDetailList(detailDTOList);
planDTOList.add(planDTO);
}
return planDTOList;
}
其中，使用selectMaps()方法返回List<Map<String, Object>>类型的结果，再通过遍历将结果集转为对象。BeanUtils.populate()方法可以将Map中的数据自动赋值给对应的DTO对象，从而避免手动赋值的繁琐。同时，在查询中使用别名将主表和子表的字段进行区分，也有助于提高代码的可读性。
如果您用的是Mybatis Plus，您可以将QueryWrapper中的select方法省略掉，直接用lambda表达式表示要查询的字段，然后使用selectList方法，直接返回List<RepaymentPlan>类型的结果，就像下面这样：
public List<PlanDTO> getPlanDTOList() {
List<PlanDTO> planDTOList = new ArrayList<>();
QueryWrapper<RepaymentPlan> planQueryWrapper = new QueryWrapper<>();
planQueryWrapper.lambda().eq(RepaymentPlan::getXxx, xxx)
.leftJoin(RepaymentDetail.class, RepaymentPlan::getId, RepaymentDetail::getRepaymentPlanId)
.select(RepaymentPlan.class, i -> Objects.equals("id", i.getColumn()) || Objects.equals("consumer_id", i.getColumn()) || Objects.equals("user_no", i.getColumn()) || Objects.equals("project_id", i.getColumn()) || Objects.equals("project_no", i.getColumn()) || Objects.equals("number_of_periods", i.getColumn()) || Objects.equals("interest", i.getColumn()) || Objects.equals("principal", i.getColumn()) || Objects.equals("amount", i.getColumn()) || Objects.equals("should_repayment_date", i.getColumn()) || Objects.equals("repayment_status", i.getColumn()) || Objects.equals("create_date", i.getColumn()) || Objects.equals("commission", i.getColumn()))
.select(RepaymentDetail.class, i -> Objects.equals("id", i.getColumn()) || Objects.equals("repayment_plan_id", i.getColumn()) || Objects.equals("interest", i.getColumn()) || Objects.equals("principal", i.getColumn()) || Objects.equals("amount", i.getColumn()) || Objects.equals("should_repayment_date", i.getColumn()) || Objects.equals("repayment_status", i.getColumn()) || Objects.equals("overdue_days", i.getColumn()) || Objects.equals("create_date", i.getColumn()));
List<RepaymentPlan> planList = repaymentPlanMapper.selectList(planQueryWrapper);
for(RepaymentPlan plan: planList) {
PlanDTO planDTO = new PlanDTO();
BeanUtils.copyProperties(plan, planDTO);
List<DetailDTO> detailDTOList = new ArrayList<>();
List<RepaymentDetail> detailList = plan.getDetailList();
for(RepaymentDetail detail : detailList) {
DetailDTO detailDTO = new DetailDTO();
BeanUtils.copyProperties(detail, detailDTO);
detailDTOList.add(detailDTO);
}
planDTO.setDetailList(detailDTOList);
planDTOList.add(planDTO);
}
return planDTOList;
}
在这个例子中，我们使用lambda表达式表示要查询的字段，然后使用select方法进行选择，从而使代码看起来更加简洁和易于维护。同时，由于Mybatis Plus已经帮我们实现了Bean到Map的转换，我们可以直接使用BeanUtils.copyProperties()方法对对象进行复制，从而避免手动赋值的繁琐。
魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。
更多推荐
0
0- 0
已为社区贡献9条内容
所有评论(0)