5.Mybatis复杂映射开发-白红宇

5.Mybatis复杂映射开发

阅读量：374 次

发布时间：2019-03-05

本文共 10687 字，大约阅读时间需要 35 分钟。

5.1 ⼀对⼀查询

5.1.1 ⼀对⼀查询的模型

⽤户表和订单表的关系为，⼀个⽤户有多个订单，⼀个订单只从属于⼀个⽤户

⼀对⼀查询的需求：查询⼀个订单，与此同时查询出该订单所属的⽤户

创建相应数据库，执行以下语句

DROP TABLE IF EXISTS `user`;

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

/*Data for the table `user` */

insert into `user`(`id`,`username`) values
(1,'lucy'),
(2,'tom');

DROP TABLE IF EXISTS `orders`;
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;

-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES ('1', '2019-12-12', '3000', '1');
INSERT INTO `orders` VALUES ('2', '2019-12-12', '4000', '1');
INSERT INTO `orders` VALUES ('3', '2019-12-12', '5000', '2');

5.1.2⼀对⼀查询的语句

对应的 sql 语句： select * from orders o,user u where o.uid=u.id;

查询的结果如下：

创建maven项目mybatis_mutiltable进行学习，

引入maven

 <properties>      <project.build.sourceEncoding> UTF-8 </project.build.sourceEncoding>      <maven.compiler.encoding> UTF-8 </maven.compiler.encoding>      <java.version> 1.8 </java.version>      <maven.compiler.source> 1.8 </maven.compiler.source>      <maven.compiler.target> 1.8 </maven.compiler.target>  </properties><dependencies>    <!--mybatis 坐标 -->    <dependency>        <groupId> org.mybatis </groupId>        <artifactId> mybatis </artifactId>        <version> 3.4.5 </version>    </dependency>    <!--mysql 驱动坐标 -->    <dependency>        <groupId> mysql </groupId>        <artifactId> mysql-connector-java </artifactId>        <version> 5.1.6 </version>        <scope> runtime </scope>    </dependency>    <!-- 单元测试坐标 -->    <dependency>        <groupId> junit </groupId>        <artifactId> junit </artifactId>        <version> 4.12 </version>        <scope> test </scope>    </dependency>    <!-- ⽇志坐标 -->    <dependency>        <groupId> log4j </groupId>        <artifactId> log4j </artifactId>        <version> 1.2.12 </version>    </dependency></dependencies>

创建对应的实体类在com.ch.pojo包下面

User.class

package com.ch.pojo;import java.util.Date;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;    }}

package com.ch.pojo;import java.util.Date;public class Order {    private Integer id;    private String orderTime;    private Double total;    //表明该订单属于哪个用户    private User user;    public Integer getId() {        return id;    }    public void setId(Integer id) {        this.id = id;    }    public String getOrderTime() {        return orderTime;    }    public void setOrderTime(String orderTime) {        this.orderTime = orderTime;    }    public Double getTotal() {        return total;    }    public void setTotal(Double total) {        this.total = total;    }    public User getUser() {        return user;    }    public void setUser(User user) {        this.user = user;    }}

创建查询订单的接口

package com.ch.mapper;import com.ch.pojo.Order;public interface IUserMapper {    //查询订单的同时查询该用户    public Order findOrderAndUser();}

mapper映射文件，

<?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.ch.mapper.IUserMapper">          <select id="findOrderAndUser" resultType="com.ch.pojo.Order">              select * from order o,user u where o.uid=u.id          </select>       </mapper>

此时resultType并不能完成order，user的查询结果集映射到Order类上面，必须使用resultMap

重新配置

<?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.ch.mapper.IUserMapper">    <!--private Integer id;    private String orderTime;    private Double total;-->       <!--  type表示当前要配置到哪个实体中-->          <resultMap id="orderMap" type="com.ch.pojo.Order">               <result property="id" column="id"></result>               <result property="orderTime" column="orderTime"></result>               <result property="total" column="total"></result>              <association property="user" javaType="com.ch.pojo.User">                   <result property="id" column="uid"></result>                   <result property="username" column="username"></result>              </association>          </resultMap>        <!--  resultMap：手动配置实体属性与表字段的映射关系-->          <select id="findOrderAndUser" resultMap="orderMap">              SELECT * FROM orders o,USER u WHERE o.uid=u.id          </select>       </mapper>

测试

@Testpublic void test() throws IOException {    InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");    SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);    SqlSession sqlSession = build.openSession();    IUserMapper mapper = sqlSession.getMapper(IUserMapper.class);    List<Order> orderAndUser = mapper.findOrderAndUser();    System.out.println("查询订单和用户信息"+orderAndUser);}

结果

查询订单和用户信息[Order{id=1, orderTime='2019-12-12', total=3000.0, user=com.ch.pojo.User@3e57cd70}, Order{id=2, orderTime='2019-12-12', total=4000.0, user=com.ch.pojo.User@9a7504c}, Order{id=3, orderTime='2019-12-12', total=5000.0, user=com.ch.pojo.User@2c039ac6}]

mapper在配置文件的另外一种配置方式，但是此时映射文件必须跟接口的包名一样。

5.2 ⼀对多查询

5.2.1 ⼀对多查询的模型

⽤户表和订单表的关系为，⼀个⽤户有多个订单，⼀个订单只从属于⼀个⽤户

⼀对多查询的需求：查询⼀个⽤户，与此同时查询出该⽤户具有的订单

5.2.2 ⼀对多查询的语句

对应的 sql语句：select u.*,o.id oid from user u left join orders o on u.id=o.uid; 由于order表的id和user表的id一样，故起别名

查询的结果如下

将原来的IUserMapper接口改成IOrderMapper

创建新的IUserMapper

package com.ch.mapper;import com.ch.pojo.User;import java.util.List;public interface IUserMapper {    //查询用户信息，查询出每个用户关联的所有订单    public List<User> findAll();}

映射文件

<?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.ch.mapper.IUserMapper">      <resultMap id="userMap" type="com.ch.pojo.User">          <id property="id" column="id"></id>          <result property="username" column="username"></result>          <collection property="orderList" ofType="com.ch.pojo.Order">              <id property="id" column="oid"></id>              <result property="orderTime" column="ordertime"></result>              <result property="total" column="total"></result>          </collection>      </resultMap>      <select id="findAll" resultMap="userMap">       SELECT u.*,o.id oid,o.`ordertime`,o.`total`,o.`uid` FROM USER u LEFT JOIN orders o ON u.id=o.uid      </select></mapper>

测试

@Testpublic void test2() throws IOException {    InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");    SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);    SqlSession sqlSession = build.openSession();    IUserMapper mapper = sqlSession.getMapper(IUserMapper.class);    List<User>userList=mapper.findAll();    for (int i = 0; i < userList.size(); i++) {        System.out.println("用户姓名"+userList.get(i).getUsername());        System.out.println("订单信息"+userList.get(i).getOrderList());    }}

测试结果：

用户姓名lucy
订单信息[Order{id=1, orderTime='2019-12-12', total=3000.0, user=null}, Order{id=2, orderTime='2019-12-12', total=4000.0, user=null}]
用户姓名tom
订单信息[Order{id=3, orderTime='2019-12-12', total=5000.0, user=null}]

5.3 多对多查询

5.3.1 多对多查询的模型

⽤户表和⻆⾊表的关系为，⼀个⽤户有多个⻆⾊，⼀个⻆⾊被多个⽤户使⽤

多对多查询的需求：查询⽤户同时查询出该⽤户的所有⻆⾊

创建数据库表

DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`rolename` varchar(255) DEFAULT NULL,
`roleDesc` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of sys_role
-- ----------------------------
INSERT INTO `sys_role` VALUES ('1', 'CTO', 'CTO');
INSERT INTO `sys_role` VALUES ('2', 'CEO', 'CEO');

-- ----------------------------
-- Table structure for sys_user_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_user_role`;
CREATE TABLE `sys_user_role` (
`userid` int(11) NOT NULL,
`roleid` int(11) NOT NULL,
PRIMARY KEY (`userid`,`roleid`),
KEY `roleid` (`roleid`),
CONSTRAINT `sys_user_role_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `user` (`id`),
CONSTRAINT `sys_user_role_ibfk_2` FOREIGN KEY (`roleid`) REFERENCES `sys_role` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of sys_user_role
-- ----------------------------
INSERT INTO `sys_user_role` VALUES ('1', '1');
INSERT INTO `sys_user_role` VALUES ('2', '1');
INSERT INTO `sys_user_role` VALUES ('1', '2');
INSERT INTO `sys_user_role` VALUES ('2', '2');

5.3.2 多对多查询的语句

对应的 sql语句：SELECT * FROM USER u LEFT JOIN sys_user_role sur ON u.id=sur.`userid` LEFT JOIN sys_role sr ON sur.`roleid`=sr.id

查询结果如下：

在IUserMapper中编写接口

//查询所有用户，同时查询每个用户关联的角色信息 public List<Role> findAllUserAndRole();

在IUserMapper.xml映射文件配置

<resultMap id="userRoleMap" type="com.ch.pojo.User">    <id property="id" column="userid"></id>    <result property="username" column="username"></result>    <collection property="roleList" ofType="com.ch.pojo.Role">       <result property="id" column="roleid"></result>        <result property="roleName" column="rolename"></result>        <result property="roleDesc" column="roleDesc"></result>    </collection></resultMap>  <select id="findAllUserAndRole" resultMap="userRoleMap">      SELECT * FROM USER u LEFT JOIN sys_user_role sur ON u.id=sur.`userid` LEFT JOIN sys_role sr ON sur.`roleid`=sr.id  </select>

编写测试方法

@Testpublic void test3() throws IOException {    InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");    SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);    SqlSession sqlSession = build.openSession();    IUserMapper mapper = sqlSession.getMapper(IUserMapper.class);    List<User> users= mapper.findAllUserAndRole();    System.out.println("用户角色信息"+users);}

运行结果

用户角色信息[User{id=1, username='lucy', roleList=[Role{id=1, roleName='CTO', roleDesc='CTO'}, Role{id=2, roleName='CEO', roleDesc='CEO'}]}, User{id=2, username='tom', roleList=[Role{id=1, roleName='CTO', roleDesc='CTO'}, Role{id=2, roleName='CEO', roleDesc='CEO'}]}]