Mybatis中关于resultMap

1.association（多对一关联）

1.1复杂的类型关联

1.2属性：

1.3依赖：

1.4实体类：

1.5mapper接口：

1.6 UserMapper.xml:

1.7测试：

2.collection (一对多集合)

2.1复杂类型集合

2.2属性

2.3 UserMapper.xml:

2.4测试

3.配置

3.1数据库表（库名：smbms）

3.2.Mybatis-config.xml

3.3user.projperties

1.association（多对一关联）

1.1复杂的类型关联
多对一内部嵌套，映射一个嵌套JavaBean属性

1.2属性：
property：映射数据库列的实体对象的属性

javaType：完整Java类名或者别名

resultMap：引用外部resultMap

子元素： id /result

property：映射数据库列的实体对象的属性

column：数据库列名或者别名

1.3依赖：


    
        junit
        junit
        4.11
        test
    
    
        mysql
        mysql-connector-java
        5.1.36
    
    
    
        org.mybatis
        mybatis
        3.5.2
    
    
        org.projectlombok
        lombok
        1.16.20
        compile

1.4实体类：

package pojo;
import lombok.Data;
import java.util.List;
@Data
public class User {
    private Integer id;
    private String userCode;
    private String userName;
    private String userPassword;
    private Integer gender;
    private String birthday;
    private String phone;
    private String adress;
    private Integer userRole;
    private Integer createdBy;
    private Integer creationDate;
    private Integer modifyBy;
    private Integer modifyDate;
    private Role role;

    private List addressesList;

    public User() {
    }

    public User(String userCode, String userName, String userPassword, Integer gender, String birthday,
                String phone, String adress, Integer userRole, Integer createdBy, Integer creationDate,
                Integer modifyBy, Integer modifyDate) {
        this.userCode = userCode;
        this.userName = userName;
        this.userPassword = userPassword;
        this.gender = gender;
        this.birthday = birthday;
        this.phone = phone;
        this.adress = adress;
        this.userRole = userRole;
        this.createdBy = createdBy;
        this.creationDate = creationDate;
        this.modifyBy = modifyBy;
        this.modifyDate = modifyDate;
    }

    public User(Integer id, String userCode, String userName, String userPassword, Integer gender,
                String birthday, String phone, String adress, Integer userRole, Integer createdBy,
                Integer creationDate, Integer modifyBy, Integer modifyDate) {
        this.id = id;
        this.userCode = userCode;
        this.userName = userName;
        this.userPassword = userPassword;
        this.gender = gender;
        this.birthday = birthday;
        this.phone = phone;
        this.adress = adress;
        this.userRole = userRole;
        this.createdBy = createdBy;
        this.creationDate = creationDate;
        this.modifyBy = modifyBy;
        this.modifyDate = modifyDate;
    }
}

package pojo;

import lombok.Data;
import java.util.Date;
@Data
public class Role {
    private Integer id;
    private String roleCode;
    private String roleName;
    private Integer createdBy;
    private Date creationDate;
    private Integer modifyBy;
    private Integer modifyDate;

    public Role() {
    }

    public Role(Integer id, String roleCode, String roleName, Integer createdBy, Date creationDate, 
                Integer modifyBy, Integer modifyDate) {
        this.id = id;
        this.roleCode = roleCode;
        this.roleName = roleName;
        this.createdBy = createdBy;
        this.creationDate = creationDate;
        this.modifyBy = modifyBy;
        this.modifyDate = modifyDate;
    }
}

package pojo;

import lombok.Data;

import java.util.Date;
@Data
public class Address {
    private Integer id;
    private String contact;
    private String addressDesc;
    private String postCode;
    private String tel;
    private Integer createdBy;
    private Date creationDate;
    private Integer modifyBy;
    private Date modifyDate;
    private Integer userId;
}

1.5mapper接口：

package mapper;
import pojo.User;
import java.util.List;

public interface UserMapper {
    //resultMap多对一
    public List findUserRole(Integer userRole);
    //resultMap一对多
    public List getAddressListByUserId(Integer id);
}

1.6 UserMapper.xml:






    

        select a.id,a.userCode,b.roleName from smbms_user a,smbms_role b where a.userRole = b.id and a.userRole=#{userRole}

1.7测试：

package mapper;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import pojo.Address;
import pojo.User;
import utils.MybatisUtil;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class UserMapperTest {

    //resultMap多对一
    @Test
    public void findUserRoleTest(){
        SqlSession sqlsession = MybatisUtil.getSqlsession();
        List list = sqlsession.getMapper(UserMapper.class).findUserRole(2);
        for (User user :list){
            System.out.println("用户id"+user.getId()+"用户编码"+user.getUserCode()+"角色名称"+user.getRole().getRoleName());
        }
        sqlsession.close();
    }
}

2.collection (一对多集合)

2.1复杂类型集合
一对多内部嵌套，映射一个嵌套结果集到一个列表

2.2属性
property：映射数据库列的实体对象的属性

ofType：完整Java类名或者别名(集合所包括的类型)

resultMap：引用外部resultMap

子元素：id /result

property：映射数据库列的实体对象的属性

column：数据库列名或者别名

2.3 UserMapper.xml:

2.4测试

package mapper;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import pojo.Address;
import pojo.User;
import utils.MybatisUtil;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class UserMapperTest {

    //resultMap一对多
@Test
public void getAddressListByUserId(){
    SqlSession sqlsession = MybatisUtil.getSqlsession();
    List list = sqlsession.getMapper(UserMapper.class).getAddressListByUserId(1);
    for (User user :list){
        System.out.println("用户id"+user.getId()+"用户编码"+user.getUserCode());
        List addressesList = user.getAddressesList();
        for (Address a :addressesList){
            System.out.println(    "用户地址"+a.getAddressDesc());
        }
    }
    sqlsession.close();
}
}

3.配置

3.1数据库表（库名：smbms）

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for smbms_address
-- ----------------------------
DROP TABLE IF EXISTS `smbms_address`;
CREATE TABLE `smbms_address`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `contact` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '联系人姓名',
  `addressDesc` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '收货地址明细',
  `postCode` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '邮编',
  `tel` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '联系人电话',
  `createdBy` bigint(20) NULL DEFAULT NULL COMMENT '创建者',
  `creationDate` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `modifyBy` bigint(20) NULL DEFAULT NULL COMMENT '修改者',
  `modifyDate` datetime NULL DEFAULT NULL COMMENT '修改时间',
  `userId` bigint(20) NULL DEFAULT NULL COMMENT '用户ID',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of smbms_address
-- ----------------------------
INSERT INTO `smbms_address` VALUES (1, '王丽', '北京市东城区东交民巷44号', '100010', '13678789999', 1, '2016-04-13 00:00:00', NULL, NULL, 1);
INSERT INTO `smbms_address` VALUES (2, '张红丽', '北京市海淀区丹棱街3号', '100000', '18567672312', 1, '2016-04-13 00:00:00', NULL, NULL, 1);
INSERT INTO `smbms_address` VALUES (3, '任志强', '北京市东城区美术馆后街23号', '100021', '13387906742', 1, '2016-04-13 00:00:00', NULL, NULL, 1);
INSERT INTO `smbms_address` VALUES (4, '曹颖', '北京市朝阳区朝阳门南大街14号', '100053', '13568902323', 1, '2016-04-13 00:00:00', NULL, NULL, 2);
INSERT INTO `smbms_address` VALUES (5, '李慧', '北京市西城区三里河路南三巷3号', '100032', '18032356666', 1, '2016-04-13 00:00:00', NULL, NULL, 3);
INSERT INTO `smbms_address` VALUES (6, '王国强', '北京市顺义区高丽营镇金马工业区18号', '100061', '13787882222', 1, '2016-04-13 00:00:00', NULL, NULL, 3);

SET FOREIGN_KEY_CHECKS = 1;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for smbms_user
-- ----------------------------
DROP TABLE IF EXISTS `smbms_user`;
CREATE TABLE `smbms_user`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `userCode` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '用户编码',
  `userName` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '用户名称',
  `userPassword` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '用户密码',
  `gender` int(10) NULL DEFAULT NULL COMMENT '性别（1:女、 2:男）',
  `birthday` date NULL DEFAULT NULL COMMENT '出生日期',
  `phone` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '手机',
  `address` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '地址',
  `userRole` int(10) NULL DEFAULT NULL COMMENT '用户角色（取自角色表-角色id）',
  `createdBy` bigint(20) NULL DEFAULT NULL COMMENT '创建者（userId）',
  `creationDate` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `modifyBy` bigint(20) NULL DEFAULT NULL COMMENT '更新者（userId）',
  `modifyDate` datetime NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 22 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of smbms_user
-- ----------------------------
INSERT INTO `smbms_user` VALUES (1, 'admin', '系统管理员', '1234567', 1, '1983-10-10', '13688889999', '北京市海淀区成府路207号', 1, 1, '2013-03-21 16:52:07', NULL, NULL);
INSERT INTO `smbms_user` VALUES (2, 'liming', '李明', '0000000', 2, '1983-12-10', '13688884457', '北京市东城区前门东大街9号', 2, 1, '0000-00-00 00:00:00', NULL, NULL);
INSERT INTO `smbms_user` VALUES (5, 'hanlubiao', '韩路彪', '0000000', 2, '1984-06-05', '18567542321', '北京市朝阳区北辰中心12号', 2, 1, '2014-12-31 19:52:09', NULL, NULL);
INSERT INTO `smbms_user` VALUES (6, 'zhanghua', '张华', '0000000', 1, '1983-06-15', '13544561111', '北京市海淀区学院路61号', 3, 1, '2013-02-11 10:51:17', NULL, NULL);
INSERT INTO `smbms_user` VALUES (7, 'wangyang', '王洋', '0000000', 2, '1982-12-31', '13444561124', '北京市海淀区西二旗辉煌国际16层', 3, 1, '2014-06-11 19:09:07', NULL, NULL);
INSERT INTO `smbms_user` VALUES (8, 'zhaoyan', '赵燕', '0000000', 1, '1986-03-07', '18098764545', '北京市海淀区回龙观小区10号楼', 3, 1, '2016-04-21 13:54:07', NULL, NULL);
INSERT INTO `smbms_user` VALUES (10, 'sunlei', '孙磊', '0000000', 2, '1981-01-04', '13387676765', '北京市朝阳区管庄新月小区12楼', 3, 1, '2015-05-06 10:52:07', NULL, NULL);
INSERT INTO `smbms_user` VALUES (11, 'sunxing', '孙兴', '0000000', 2, '1978-03-12', '13367890900', '北京市朝阳区建国门南大街10号', 3, 1, '2016-11-09 16:51:17', NULL, NULL);
INSERT INTO `smbms_user` VALUES (12, 'zhangchen', '张晨', '0000000', 1, '1986-03-28', '18098765434', '朝阳区管庄路口北柏林爱乐三期13号楼', 3, 1, '2016-08-09 05:52:37', 1, '2016-04-14 14:15:36');
INSERT INTO `smbms_user` VALUES (13, 'dengchao', '邓超', '0000000', 2, '1981-11-04', '13689674534', '北京市海淀区北航家属院10号楼', 3, 1, '2016-07-11 08:02:47', NULL, NULL);
INSERT INTO `smbms_user` VALUES (14, 'yangguo', '杨过', '0000000', 2, '1980-01-01', '13388886623', '北京市朝阳区北苑家园茉莉园20号楼', 3, 1, '2015-02-01 03:52:07', NULL, NULL);
INSERT INTO `smbms_user` VALUES (15, 'zhaomin', '赵敏', '0000000', 1, '1987-12-04', '18099897657', '北京市昌平区天通苑3区12号楼', 2, 1, '2015-09-12 12:02:12', NULL, NULL);
INSERT INTO `smbms_user` VALUES (17, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `smbms_user` VALUES (18, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `smbms_user` VALUES (19, NULL, '毛毛', '111111111', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `smbms_user` VALUES (20, NULL, 'doudou', '99999999', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `smbms_user` VALUES (21, NULL, '呆子', '666666', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

SET FOREIGN_KEY_CHECKS = 1;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for smbms_role
-- ----------------------------
DROP TABLE IF EXISTS `smbms_role`;
CREATE TABLE `smbms_role`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `roleCode` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '角色编码',
  `roleName` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '角色名称',
  `createdBy` bigint(20) NULL DEFAULT NULL COMMENT '创建者',
  `creationDate` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `modifyBy` bigint(20) NULL DEFAULT NULL COMMENT '修改者',
  `modifyDate` datetime NULL DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of smbms_role
-- ----------------------------
INSERT INTO `smbms_role` VALUES (1, 'SMBMS_ADMIN', '系统管理员', 1, '2016-04-13 00:00:00', NULL, NULL);
INSERT INTO `smbms_role` VALUES (2, 'SMBMS_MANAGER', '经理', 1, '2016-04-13 00:00:00', NULL, NULL);
INSERT INTO `smbms_role` VALUES (3, 'SMBMS_EMPLOYEE', '普通员工', 1, '2016-04-13 00:00:00', NULL, NULL);

SET FOREIGN_KEY_CHECKS = 1;

3.2.Mybatis-config.xml

3.3user.projperties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/smbms?useSSl=true&useUnicode=true&characterEncoding=utf-8
jdbc.username=root
jdbc.password=xxx

Mybatis中关于resultMap

1.association（多对一 关联 ）

1.1复杂的类型关联 多对一 内部嵌套 ，映射一个嵌套JavaBean属性

1.2属性： property：映射数据库列的实体对象的属性 javaType：完整Java类名或者别名 resultMap：引用外部resultMap 子元素： id /result property：映射数据库列的实体对象的属性 column：数据库列名或者别名

1.3依赖： junit junit 4.11 test mysql mysql-connector-java 5.1.36 org.mybatis mybatis 3.5.2 org.projectlombok lombok 1.16.20 compile

1.5mapper接口： package mapper; import pojo.User; import java.util.List; public interface UserMapper { //resultMap多对一 public List findUserRole(Integer userRole); //resultMap一对多 public List getAddressListByUserId(Integer id); }

1.6 UserMapper.xml: select a.id,a.userCode,b.roleName from smbms_user a,smbms_role b where a.userRole = b.id and a.userRole=#{userRole}

2.collection (一对多 集合)

2.1复杂类型集合 一对多 内部嵌套 ，映射一个嵌套结果集到一个列表

2.2属性 property：映射数据库列的实体对象的属性 ofType：完整Java类名或者别名(集合所包括的类型) resultMap：引用外部resultMap 子元素 ：id /result property：映射数据库列的实体对象的属性 column：数据库列名或者别名

2.3 UserMapper.xml: select a.id,a.userCode,b.addressDesc from smbms_user a,smbms_address b where a.id=b.userId and a.id = #{id}

3.配置

3.2.Mybatis-config.xml

3.3user.projperties jdbc.driver=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/smbms?useSSl=true&useUnicode=true&characterEncoding=utf-8 jdbc.username=root jdbc.password=xxx

Java相关栏目本月热门文章

1.association（多对一关联）

1.1复杂的类型关联
多对一内部嵌套，映射一个嵌套JavaBean属性

1.2属性：
property：映射数据库列的实体对象的属性

javaType：完整Java类名或者别名

resultMap：引用外部resultMap

子元素： id /result

property：映射数据库列的实体对象的属性

column：数据库列名或者别名

1.3依赖：
junit junit 4.11 test mysql mysql-connector-java 5.1.36 org.mybatis mybatis 3.5.2 org.projectlombok lombok 1.16.20 compile

1.5mapper接口：
package mapper; import pojo.User; import java.util.List; public interface UserMapper { //resultMap多对一 public List findUserRole(Integer userRole); //resultMap一对多 public List getAddressListByUserId(Integer id); }

1.6 UserMapper.xml:
select a.id,a.userCode,b.roleName from smbms_user a,smbms_role b where a.userRole = b.id and a.userRole=#{userRole}

2.collection (一对多集合)

2.1复杂类型集合
一对多内部嵌套，映射一个嵌套结果集到一个列表

2.2属性
property：映射数据库列的实体对象的属性

ofType：完整Java类名或者别名(集合所包括的类型)

resultMap：引用外部resultMap

子元素：id /result

property：映射数据库列的实体对象的属性

column：数据库列名或者别名

2.3 UserMapper.xml:

3.3user.projperties
jdbc.driver=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/smbms?useSSl=true&useUnicode=true&characterEncoding=utf-8 jdbc.username=root jdbc.password=xxx