7、Mybatis表关联----一（多）对一

环境的搭建：
表的创建：

-- ------------------------------ Table structure for `orders`-- ----------------------------DROP TABLE IF EXISTS `orders`;CREATE TABLE `orders` (  `id` INT(11) NOT NULL AUTO_INCREMENT,  `userId` INT(11) NOT NULL COMMENT '下单用户id',  `number` VARCHAR(32) NOT NULL COMMENT '订单号',  `createtime` DATETIME NOT NULL COMMENT '创建订单时间',  `note` VARCHAR(100) DEFAULT NULL COMMENT '备注',  PRIMARY KEY (`id`),  KEY `FK_orders_1` (`userId`),  CONSTRAINT `FK_orders_id` FOREIGN KEY (`userId`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;-- ------------------------------ Records of orders-- ----------------------------INSERT INTO `orders` VALUES ('3', '1', '1000010', '2015-02-04 13:22:35', NULL);INSERT INTO `orders` VALUES ('4', '1', '1000011', '2015-02-03 13:22:41', NULL);INSERT INTO `orders` VALUES ('5', '10', '1000012', '2015-02-12 16:13:23', NULL);-- ------------------------------ Table structure for `user`-- ----------------------------DROP TABLE IF EXISTS `user`;CREATE TABLE `user` (  `id` INT(11) NOT NULL AUTO_INCREMENT,  `username` VARCHAR(32) NOT NULL COMMENT '用户名称',  `birthday` DATE DEFAULT NULL COMMENT '生日',  `sex` CHAR(1) DEFAULT NULL COMMENT '性别',  `address` VARCHAR(256) DEFAULT NULL COMMENT '地址',  PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;-- ------------------------------ Records of user-- ----------------------------INSERT INTO `user` VALUES ('1', '王五', NULL, '2', NULL);INSERT INTO `user` VALUES ('10', '张三', '2014-07-10', '1', '北京市');INSERT INTO `user` VALUES ('16', '张小明', NULL, '1', '河南郑州');INSERT INTO `user` VALUES ('22', '陈小明', NULL, '1', '河南郑州');INSERT INTO `user` VALUES ('24', '张三丰', NULL, '1', '河南郑州');INSERT INTO `user` VALUES ('25', '陈小明', NULL, '1', '河南郑州');INSERT INTO `user` VALUES ('26', '王五', NULL, NULL, NULL);

POJO的创建：
User:

public class User {    private int id;    private String username;    private String sex;    private Date birthday;    private String address;    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public String getUsername() {        return username;    }    public void setUsername(String username) {        this.username = username;    }    public String getSex() {        return sex;    }    public void setSex(String sex) {        this.sex = sex;    }    public Date getBirthday() {        return birthday;    }    public void setBirthday(Date birthday) {        this.birthday = birthday;    }    public String getAddress() {        return address;    }    public void setAddress(String address) {        this.address = address;    }    public User() {        super();    }    public User(String username, String sex, Date date,            String address) {        super();        this.username = username;        this.sex = sex;        this.birthday = date;        this.address = address;    }    @Override    public String toString() {        return "User [id=" + id + ", username=" + username + ", sex=" + sex                + ", birthday=" + birthday + ", address=" + address + "]";    }}

Orders:

public class Orders  implements Serializable{    @Override    public String toString() {        return "Orders [id=" + id + ", userId=" + userId + ", number=" + number + ", createtime=" + createtime                + ", note=" + note + "]";    }    private static final long serialVersionUID = 1L;    private Integer id;    private Integer userId;    private String number;    private Date createtime;    private String note;    public Integer getId() {        return id;    }    public void setId(Integer id) {        this.id = id;    }    public Integer getUserId() {        return userId;    }    public void setUserId(Integer userId) {        this.userId = userId;    }    public String getNumber() {        return number;    }    public void setNumber(String number) {        this.number = number == null ? null : number.trim();    }    public Date getCreatetime() {        return createtime;    }    public void setCreatetime(Date createtime) {        this.createtime = createtime;    }    public String getNote() {        return note;    }    public void setNote(String note) {        this.note = note == null ? null : note.trim();    }  }

一对一

应用场景：首先根据订单 ID 读取一个订单信息(orders)，然后再读取这个订单所对应的用户(user)信息。

方法一：使用resultType

      使用resultType的话，有个前提就是查询结果要想映射到pojo中去，pojo中必须包括所有查询出的列名才行。这里不仅查询出order表中的所有字段，而且还查询出了用户表中的部分字段，所以我们要自己新建一个pojo来包含所有的这些查询出来的字段才行。
　　创建pojo的原则是继承包括查询字段较多的po类。

public class OrdersUser extends Orders {    private static final long serialVersionUID = 1L;    //我只显示user的名字和地址    private String username;    private String address;    public String getUsername() {        return username;    }    public void setUsername(String username) {        this.username = username;    }    public String getAddress() {        return address;    }    public void setAddress(String address) {        this.address = address;    }    @Override    public String toString() {        return "OrdersUser ["+super.toString() +"username=" + username + ", address=" + address                + "]";    }}

在UserMapper.xml中写sql：

<select id="queryOrderUserById" parameterType="int" resultType="cn.xpu.hcp.bean.OrdersUser">        SELECT         o.*,        u.username,        u.address        FROM         orders o        LEFT JOIN USER u ON o.userId = u.id        WHERE o.id=#{id}    </select>

测试：

public void TestQueryOrderUserById(){    SqlSession session = sqlSessionFactory.openSession();    UserMapper mapper = session.getMapper(UserMapper.class);    OrdersUser ordersUser = mapper.queryOrderUserById(3);    System.out.println(ordersUser);    session.commit();    session.close();}

方法二：使用resultMap

在orders类中加入user对象：

public class Orders  implements Serializable{    ......    private User user;    public User getUser() {        return user;    }    public void setUser(User user) {        this.user = user;    }    ......}

使用resultMap:

<resultMap type="orders" id="orderUserResultMap">    <id column="id" property="id"/>    <result column="userId" property="userId"/>    <result column="number" property="number"/>    <result column="createtime" property="createtime"/>    <result column="note" property="note"/>    <!--         association标签用于配置一对一        property:orders中user对象的属性名        javaType:user的类型     -->    <association property="user" javaType="user">        <!-- id:声明主键，column:所对应的外键字段名称 -->        <!--不能对应自己表里的id，否则得不到id值，因为SQL语句中没有查id-->        <!--如果SQL语句中查询了id则可以使用自己表里的id-->        <id property="id" column="userId"/>        <result property="username" column="username"/>        <result property="address" column="address"/>        <result property="sex" column="sex"/>        <result property="birthday" column="birthday"/>    </association></resultMap><select id="queryOrderUserById" resultMap="orderUserResultMap" parameterType="int">    SELECT     o.*,    u.*    FROM     orders o    LEFT JOIN USER u ON o.userId = u.id    WHERE o.id=#{id}</select>

注意：与前面输出映射所介绍resultMap不同的是在这里所有的result都要配置上，不配值的则显示为null。
测试：

public void TestQueryOrderUserById(){    SqlSession session = sqlSessionFactory.openSession();    OrdersMapper mapper = session.getMapper(OrdersMapper.class);    Orders orders= mapper.queryOrderUserById(5);    System.out.println(orders);    session.commit();    session.close();}