mybatis复杂映射开发

1. 一对一查询

1. 模型 数据表创建

CREATE TABLE `user` (
	  `id` int(11) NOT NULL AUTO_INCREMENT,
	  `username` varchar(50) DEFAULT NULL,
	  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

CREATE TABLE `orders` (
	  `id` int(11) NOT NULL AUTO_INCREMENT,
	  `ordertime` varchar(255) DEFAULT NULL,
	  `total` double DEFAULT NULL,
	  `uid` int(11) DEFAULT NULL,
	  PRIMARY KEY (`id`),
	  KEY `uid` (`uid`),
	  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

创建实体

public class User {
    private Integer id;
    private String username;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                '}';
    }
}

public class Order {

    private int id;
    private Date ordertime;
    private double total;
    //当前订单属于哪个用户
    private User user;

    ...getter() setter()
}

一对一数据库查询： 查询当前订单属于哪个用户

select * from orders o,user u where o.uid=u.id;

查询结果如下： 对应的orderMapper.xml中配置

<mapper namespace="com.example.mapper.OrderMapper">
	<resultMap id="orderMap" type="com.example.domain.Order">
		<result property="id" column="id"></result>
		<result property="ordertime" column="ordertime"></result>
		<result property="total" column="total"></result>
		<association property="user" javaType="com.example.domain.User">
			<result column="uid" property="id"></result>
			<result column="username" property="username"></result>
		</association>
	</resultMap>
	<select id="findAll" resultMap="orderMap">
		select * from orders o,user u where o.uid=u.id
	</select>
</mapper>

<association></association>
用来配置一对一的实体
property: 对应的属性名称
javaType：属性对应的类型

2. 一对多查询

查询一个用户的所有订单 查询的sql语句：select *,o.id oid from user u left join orders o on u.id=o.uid; 对应的查询结果如下：

• 修改User实体

public class User {
    private Integer id;
    private String username;

    List<Order> orders;

    ...getter()  setter()
}

对应的UserMapper.xml

<mapper namespace="com.example.mapper.UserMapper">
	<resultMap id="userMap" type="com.example.domain.User">
		<result column="id" property="id"></result>
		<result column="username" property="username"></result>
		<collection property="orders" ofType="com.example.domain.Order">
			<result column="oid" property="id"></result>
			<result column="ordertime" property="ordertime"></result>
			<result column="total" property="total"></result>
		</collection>
	</resultMap>
	<select id="findAll" resultMap="userMap">
		select *,o.id oid from user u left join orders o on u.id=o.uid
	</select>
</mapper>

其中，collection标签用来配置一对多的实体
property: 对应的属性名称
javaType：属性对应的类型

3. 多对多查询

1. 模型
2. 应用场景： 查询所有用户以及角色

• 对应的sql语句： select u.*,r.*,r.id rid from user u left join user_role ur on u.id=ur.user_id inner join role r on ur.role_id=r.id;
• 对应的查询结果，如下图 3.修改User实体

public class User {
	private int id;
	private String username;
	//代表当前用户具备哪些订单
	private List<Order> orders;
	//代表当前用户具备哪些角色private List<Role> roleList;
}

4.添加Role实体

public class Role {
	private int id;
	private String rolename;
}

5.配置UserMapper.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="com.example.mapper.UserMapper">
	<resultMap id="userRoleMap" type="com.lagou.domain.User">
		<result column="id" property="id"></result>
		<result column="username" property="username"></result>
		<collection property="roleList" ofType="com.lagou.domain.Role">
			<result column="rid" property="id"></result>
			<result column="rolename" property="rolename"></result>
		</collection>
	</resultMap>
	<select id="findAllUserAndRole" resultMap="userRoleMap">
		select u.*,r.*,r.id rid from user u left join user_role ur on u.id=ur.user_id
		inner join role r on ur.role_id=r.id
	</select>
</mapper>