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

最新推荐文章于 2024-07-31 09:23:35 发布

LvQiFen

最新推荐文章于 2024-07-31 09:23:35 发布

阅读量1.7k

点赞数 2

分类专栏： Mybatis 文章标签：数据库 mysql java

本文链接：https://blog.csdn.net/LvQiFen/article/details/120687902

版权

Mybatis 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

`Mybatis` 一对多多字段作为主键或唯一时传参

前言：

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

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

一、bean (DO)

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;

/**
 * 项目关联交易结果类
 *
 * @author lvzb31988
 * @date 2021/09/24
 */
@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<SubjectRelatedInfoDO> 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;

/**
 * 关联交易结果主体信息类
 *
 * @author lvzb31988
 * @date 2021/09/24
 */
@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;
}

二、Mapper

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

/**
 * 关联交易结果信息映射
 *
 * @author lvzb31988
 * @date 2021/09/24
 */
@Mapper
public interface RelatedTransactionResultMapper extends BaseMapper<RelatedTransactionResultDO> {

    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;

/**
 * 关联交易结果主体信息映射
 *
 * @author lvzb31988
 * @date 2021/09/24
 */
@Mapper
public interface SubjectRelatedInfoMapper extends BaseMapper<SubjectRelatedInfoDO> {

    /**
     * @param projectId
     * @param ocDate
     * @return
     */
    SubjectRelatedInfoDO selectSubjectRelatedInfo(String projectId, Integer ocDate);

}

三、mapper.xml文件

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

<?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="bbbb.mapper.RelatedTransactionResultMapper">

    <resultMap id="ProjectRelatedTransactionResultMapper" type="aaaa.bean.RelatedTransactionResultDO">
        <id property="id" column="id" />
        <result property="projectId" column="project_id" />
        <result property="projectCode" column="project_code" />
        <result property="projectName" column="project_name" />
        <result property="projectTypeCode" column="project_type_code" />
        <result property="projectTypeName" column="project_type_name" />
        <result property="projectStatus" column="project_status" />
        <result property="relationTime" column="relation_time" />
        <result property="deptCode" column="dept_code" />
        <result property="deptName" column="dept_name" />
        <result property="orgCode" column="org_code" />
        <result property="ocDate" column="oc_date" />

        <collection property="subjectRelatedInfoList"
                    column="{projectId=project_id,ocDate=oc_date}"
                    javaType="java.util.ArrayList"
                    ofType="aaaa.bean.SubjectRelatedInfoDO"
                    select="bbbb.mapper.SubjectRelatedInfoMapper.selectSubjectRelatedInfo"
                    fetchType="lazy"/>
    </resultMap>

    <select id="selectOneWithSubjectInfo" parameterType="aaaa.bean.RelatedTransactionResultDO"
            resultMap="ProjectRelatedTransactionResultMapper">
        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
        <if test="null != id">
            and id = #{id}
        </if>
        <if test="null != projectId">
            and project_id = #{projectId}
        </if>
        <if test="null != projectCode">
            and project_code = #{projectCode}
        </if>
        <if test="null != projectName">
            and project_name = #{projectName}
        </if>
        <if test="null != projectTypeCode">
            and project_type_code = #{projectTypeCode}
        </if>
        <if test="null != projectTypeName">
            and project_type_name = #{projectTypeName}
        </if>
        <if test="null != projectStatus">
            and project_status = #{projectStatus}
        </if>
        <if test="null != relationTime">
            and relation_time = #{relationTime}
        </if>
        <if test="null != deptCode">
            and dept_code = #{deptCode}
        </if>
        <if test="null != deptName">
            and dept_name = #{deptName}
        </if>
        <if test="null != orgCode">
            and org_code = #{orgCode}
        </if>
        <if test="null != ocDate">
            and oc_date = #{ocDate}
        </if>

    </select>
</mapper>

<?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="bbbb.mapper.SubjectRelatedInfoMapper">

    <select id="selectSubjectRelatedInfo" resultType="aaaa.bean.SubjectRelatedInfoDO">
        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}
    </select>
</mapper>

四、表结构

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;