Mybatis 一对多 多字段作为主键或唯一时column传参设置

前言：

经常在 mybatis 查询对象时，对象A 和对象 B 是一对多的关系，此时可以利用返回类型是 resultMap的 collection方式来关联查询，这里记录下关联查询时多字段传值传参的方式（mysql数据库）

这里每个项目关联交易结果，对应一个或多个主体信息, 我这里的 bean还有 RelatedTransactionResultMapper、SubjectRelatedInfoMapper 集成了通用mapper 你们可忽略多余的注解和继承的东西

package aaaa.bean;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Transient;
import java.io.Serializable;
import java.math.BigInteger;
import java.util.List;


@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "prm_related_transaction_result")
public class RelatedTransactionResultDO implements Serializable {
    private static final long serialVersionUID = -8887683609372198234L;

    
    @Id
    @GeneratedValue(generator = "JDBC")
    private BigInteger id;

    private String projectId;

    private String projectCode;

    private String projectName;

    private String projectTypeCode;

    private String projectTypeName;

    private String projectStatus;

    private String relationTime;

    private String deptCode;

    private String deptName;

    private String orgCode;

    private Integer ocDate;

    @Transient
    private String operatorCode;

    @Transient
    private List subjectRelatedInfoList;

}

package aaaa.bean;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Transient;
import java.io.Serializable;
import java.math.BigDecimal;
import java.math.BigInteger;


@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "prm_subject_related_info")
public class SubjectRelatedInfoDO implements Serializable {

    private static final long serialVersionUID = 8024081739062988572L;
    
    @Id
    @GeneratedValue(generator = "JDBC")
    private BigInteger id;

    private String projectId;

    private String enterpriseId;

    private String enterpriseName;

    private String uniformCreditCode;

    private BigDecimal projectConnectedTransactBalance;

    private Integer ocDate;

    @Transient
    private String operatorCode;
}

package bbbb.mapper;
import aaa.RelatedTransactionResultDO;
import org.apache.ibatis.annotations.Mapper;
import tk.mybatis.mapper.common.baseMapper;


@Mapper
public interface RelatedTransactionResultMapper extends baseMapper {

    RelatedTransactionResultDO selectOneWithSubjectInfo(RelatedTransactionResultDO data);
}

package bbbb.mapper;
import aaa.SubjectRelatedInfoDO;
import org.apache.ibatis.annotations.Mapper;
import tk.mybatis.mapper.common.baseMapper;

import java.util.List;


@Mapper
public interface SubjectRelatedInfoMapper extends baseMapper {

    
    SubjectRelatedInfoDO selectSubjectRelatedInfo(String projectId, Integer ocDate);

}

关键点在这里的多字段传参：column="{projectId=project_id,ocDate=oc_date}"，对应select属性指定的查询的查询参数

    
        
        
        
        
        
        
        
        
        
        
        
        

        
    

    
        select id,
               project_id,
               project_code,
               project_name,
               project_type_code,
               project_type_name,
               project_status,
               relation_time,
               dept_code,
               dept_name,
               org_code,
               oc_date
        from prm_related_transaction_result
        where 1= 1
        
            and id = #{id}
        
        
            and project_id = #{projectId}
        
        
            and project_code = #{projectCode}
        
        
            and project_name = #{projectName}
        
        
            and project_type_code = #{projectTypeCode}
        
        
            and project_type_name = #{projectTypeName}
        
        
            and project_status = #{projectStatus}
        
        
            and relation_time = #{relationTime}
        
        
            and dept_code = #{deptCode}
        
        
            and dept_name = #{deptName}
        
        
            and org_code = #{orgCode}
        
        
            and oc_date = #{ocDate}
        

    

 





    
        select id,
               project_id,
               enterprise_id,
               enterprise_name,
               uniform_credit_code,
               project_connected_transact_balance,
               oc_date
        from prm_subject_related_info
        where project_id = #{projectId}
          and oc_date = #{ocDate}
    

CREATE TABLE `prm_related_transaction_result` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '唯一主键',
  `project_id` varchar(32) NOT NULL COMMENT '项目id',
  `project_code` varchar(32) NOT NULL COMMENT '项目编号',
  `project_name` varchar(256) DEFAULT NULL COMMENT '项目名称',
  `project_type_code` varchar(32) DEFAULT NULL COMMENT '项目类型编号',
  `project_type_name` varchar(128) DEFAULT NULL COMMENT '项目类型名称',
  `project_status` varchar(32) DEFAULT NULL COMMENT '项目状态(存续,已到期)',
  `relation_time` varchar(32) NOT NULL COMMENT '关联交易日期(yyyy-mm-dd)',
  `dept_code` varchar(64) DEFAULT NULL COMMENT '部门编号',
  `dept_name` varchar(128) DEFAULT NULL COMMENT '部门名称',
  `org_code` varchar(64) DEFAULT NULL COMMENT '机构编号',
  `oc_date` int(10) NOT NULL COMMENT '创建日期',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_pid_ocd` (`project_id`,`oc_date`)
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8;

CREATE TABLE `prm_subject_related_info` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '唯一主键',
  `project_id` varchar(32) NOT NULL COMMENT '项目id',
  `enterprise_id` varchar(32) NOT NULL COMMENT '外部关联方主体id',
  `enterprise_name` varchar(256) DEFAULT NULL COMMENT '外部关联方名称',
  `uniform_credit_code` varchar(32) NOT NULL COMMENT '外部关联方统一社会信用码',
  `project_connected_transact_balance` decimal(18,2) DEFAULT 0.00 COMMENT '项目关联交易金额',
  `oc_date` int(10) NOT NULL COMMENT '创建日期',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_pid_eid_ocd` (`project_id`,`enterprise_id`,`oc_date`)
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8;