mybatis - 1+N 问题解决

mybatis - 1+N 问题解决

这是 mybatis 官网提供的解决 n+1 问题方案，关联的多结果集, 需要 mybatis 版本 在 3.2.3 以后 !!

下面照葫芦画瓢

数据库脚本

CREATE TABLE `t_account` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) DEFAULT NULL,
  `passwd` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `t_account_detail` (
  `id` int NOT NULL AUTO_INCREMENT,
  `account_id` int DEFAULT NULL,
  `amount` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `t_account` (`id`, `user_name`, `passwd`) VALUES (1, 'test', 'test');
INSERT INTO `t_account` (`id`, `user_name`, `passwd`) VALUES (2, 'haha', 'haha');
INSERT INTO `t_account_detail` (`id`, `account_id`, `amount`) VALUES (1, 1, 10);
INSERT INTO `t_account_detail` (`id`, `account_id`, `amount`) VALUES (2, 2, 43);

model 文件

@Data
public class AccountModel implements Serializable {
    private Integer id;

    private String userName;

    private String passwd;

    private static final long serialVersionUID = 1L;
}

@Data
public class AccountDetailModel implements Serializable {
    private Integer id;

    private Integer accountId;

    private Integer amount;

    private static final long serialVersionUID = 1L;
}

@Data
public class AccountDto {


    private Integer id;

    private String userName;

    private String passwd;

    private AccountDetailModel accountDetailModel;
}

配置文件

// 其中 accounts,details 是任意命名的
<select id="getAccountList2" resultSets="accounts,details" resultMap="getAccountList2Map" 
																	statementType="CALLABLE">
    {call getCallAccountList()}
  </select>

  <resultMap id="getAccountList2Map" type="com.ddup.bootmybatis.AccountDto">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="user_name" jdbcType="VARCHAR" property="userName" />
    <result column="passwd" jdbcType="VARCHAR" property="passwd" />
    <association property="accountDetailModel" resultSet="details" 
                 javaType="com.ddup.bootmybatis.model.AccountDetailModel"
                 column="id" 
                 foreignColumn="account_id">         //t_account_detail 表中和t_account 关联的字段
      <id column="id" jdbcType="INTEGER" property="id"/>
      <result column="account_id" jdbcType="INTEGER" property="accountId"/>
      <result column="amount" jdbcType="INTEGER" property="amount"/>
    </association>
  </resultMap>

字段含义:

column	当使用多个结果集时，该属性指定结果集中用于与 foreignColumn 匹配的列（多个列名以逗号隔开），以识别关系中的父类型与子类型。
foreignColumn	指定外键对应的列名，指定的列将与父类型中 column 的给出的列进行匹配。
resultSet	指定用于加载复杂类型的结果集名字。

存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `getCallAccountList`()
BEGIN
	#Routine body goes here...
	select * from t_account;
 
	select * from t_account_detail;
END

测试用例

@Test
public void nPlusOneTest() throws JsonProcessingException {
    List<AccountDto> accountList2 = accountDao.getAccountList2();
    ObjectMapper mapper = new ObjectMapper();
    System.out.println(mapper.writeValueAsString(accountList2));
}

测试结果:

[
    {
        "id":1,
        "userName":"test",
        "passwd":"test",
        "accountDetailModel":{
            "id":1,
            "accountId":1,
            "amount":10
        }
    },
    {
        "id":2,
        "userName":"haha",
        "passwd":"haha",
        "accountDetailModel":{
            "id":2,
            "accountId":2,
            "amount":43
        }
    }
]

perfect , good luck !