mybatis中collection association优化使用及多参数传递

mybatis都会用，但要优雅的用就不是那么容易了

今天就简单举例，抛砖引玉，供大家探讨

1.主表

CREATE TABLE `test_one` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nickname` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

对应的java实体类如下（自动生成的代码，省略get set）

@JsonIgnoreProperties(ignoreUnknown = true, value = {"handler"})
public class TestOne implements Serializable {
    private static final long serialVersionUID = 1L;
    
    private Integer id;

    private String nickname;

    @JsonIgnoreProperties(ignoreUnknown = true, value = {"testOne"})
    private List<TestTwo> testTwos = new LinkedList<>();

注意：JsonIgnoreProperties请忽略，这是解决对象间循环依赖在json序列化时出错的，不在本次内容中

 

2.从表

CREATE TABLE `test_two` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nickname` varchar(255) NOT NULL,
  `one_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `test_two_ibfk_1` (`one_id`),
  CONSTRAINT `test_two_ibfk_1` FOREIGN KEY (`one_id`) REFERENCES `test_one` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

对应的java实体类如下（自动生成的代码，省略get set）

@JsonIgnoreProperties(ignoreUnknown = true, value = {"handler"})
public class TestTwo implements Serializable {
    private Integer id;

    private String nickname;

    private Integer oneId;

    @JsonIgnoreProperties(ignoreUnknown = true, value = {"testTwos"})
    private TestOne testOne;

注意：JsonIgnoreProperties请忽略，这是解决对象间循环依赖在json序列化时出错的，不在本次内容中

 

细心的同学发现，两个表用同名字段，后续会告诉为什么这么举例，而且这种情况项目中是非常常见的

3.TestOneMapper.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="info.zycloud.xcx.merchant.dao.TestOneMapper">
    <resultMap id="BaseResultMap" type="info.zycloud.xcx.merchant.model.TestOne">
        <constructor>
            <idArg column="id" javaType="java.lang.Integer" jdbcType="INTEGER"/>
            <arg column="nickname" javaType="java.lang.String" jdbcType="VARCHAR"/>
        </constructor>
    </resultMap>

 <!--一次查询查出collection-->
　　　　这里会一次查询就查询出主对象和关联的list对象， 查询语句是一个join语句
    <resultMap id="OnceQueryBaseResultMap" type="info.zycloud.xcx.merchant.model.TestOne" extends="BaseResultMap">
        <collection property="testTwos" resultMap="info.zycloud.xcx.merchant.dao.TestTwoMapper.BaseResultMap"
                    columnPrefix="two_"/> 由于两个表有同名字段，所以需要做区分，这里可以采用前缀，就可以共用之前的ResultMap了
    </resultMap>


    <select id="onceQuery4Collection" resultMap="OnceQueryBaseResultMap">
        SELECT
            one.*,　　为什么要用*，是为了防止主表字段变了，因为这里是引用的生成的baseresultMap
            two.id AS two_id,
            two.nickname AS two_nickname,
            two.one_id AS two_one_id
        FROM
            `test_one` one
        LEFT JOIN test_two two ON one.id = two.one_id
  </select>

    <!-- 多次查询查出collection-->
    <resultMap id="MultipleQueryBaseResultMap" type="info.zycloud.xcx.merchant.model.TestOne" extends="BaseResultMap">
        <collection property="testTwos" column="{oneId=id,nickname=nickname}"   多参数时在column中用"{}"将参数包起来， =左侧的为mapper中定义的param， =右侧为主查询的数据库字段名  
                    select="info.zycloud.xcx.merchant.dao.TestTwoMapper.selectByOneId"/>
    </resultMap>

    <select id="multipleQuery4Collection" parameterType="java.lang.Integer" resultMap="MultipleQueryBaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from test_one
        where id = #{id,jdbcType=INTEGER}
    </select>

</mapper>

对应的接口定义

public interface TestOneMapper {

    List<TestOne> onceQuery4Collection();

    TestOne multipleQuery4Collection(Integer id);
}

 

3.TestTwoMapper.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="info.zycloud.xcx.merchant.dao.TestTwoMapper">
    <resultMap id="BaseResultMap" type="info.zycloud.xcx.merchant.model.TestTwo">
        <constructor>
            <idArg column="id" javaType="java.lang.Integer" jdbcType="INTEGER"/>
            <arg column="nickname" javaType="java.lang.String" jdbcType="VARCHAR"/>
            <arg column="one_id" javaType="java.lang.Integer" jdbcType="INTEGER"/>
        </constructor>
    </resultMap>

    <select id="selectByOneId" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from test_two
        where one_id=#{oneId} and nickname =#{nickname}
    </select>
</mapper>

public interface TestTwoMapper {
    List<TestTwo> selectByOneId(@Param("oneId") Integer oneId, @Param("nickname") String nickname);
}

 

解释了然后我们执行看下效果：

onceQuery4Collection：

 

 

multipleQuery4Collection：