Spring+SpringMVC+MyBatis-教程、笔记-11

夏天的冬雪

已于 2023-07-30 15:19:23 修改

阅读量141

点赞数

分类专栏： Spring+SpringMVC+MyBatis-教程-笔记文章标签： spring mybatis 笔记

于 2023-07-30 15:18:09 首次发布

本文链接：https://blog.csdn.net/qq_48269763/article/details/132007298

版权

Spring+SpringMVC+MyBatis-教程-笔记专栏收录该内容

11 篇文章 0 订阅

订阅专栏

base黑马，视频链接：https://www.bilibili.com/video/BV1WZ4y1P7Bp?t=94.4
此系列文章可以当做视频的配套笔记，也可以当做自学SSM框架的教程。

mybatis的多表操作-一对一模型介绍

表与表的关系：一对一、一对多或多对一、多对多
一对一：主键值一样，共用同一个主键。在某一种表中有一个外键与另一张表的主键相关联。
一对多：在多的一方要有一个外键，这个外键要与一的一方的主键相关联
多对多：有一张中间表，去维护两张主表的主键。

一对多，多对一，只是看表的方向

业务需求：在查询订单时，与此同时要查询出与该订单相关的用户信息。

mybatis的多表操作-订单环境代码实现

order本身在sql语句中是一个关键字，是排序的意思。如果一个表的名字叫order的话，那就叫orders，避免与关键字冲突。

在test数据库中，创建orders表：

/*
Navicat MySQL Data Transfer

Source Server         : huaxiang
Source Server Version : 50725
Source Host           : localhost:3306
Source Database       : test

Target Server Type    : MYSQL
Target Server Version : 50725
File Encoding         : 65001

Date: 2023-03-24 21:39:32
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `orders`
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `ordertime` datetime DEFAULT NULL,
  `total` double(20,0) DEFAULT NULL,
  `uid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `uid` (`uid`),
  CONSTRAINT `uid` FOREIGN KEY (`uid`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES ('1', '2023-04-01 21:20:50', '2000', '1');

实体类：User

package com.lyh.domain;

import java.util.Date;

public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    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 getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    //方便测试，写上toString()方法

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

实体类：Order

package com.lyh.domain;

import java.util.Date;

public class Order {
    private int id;
    private Date ordertime;
    private double total;

    //Java讲究的是面向对象
    //表与表的关系是通过主外键进行相关联的
    //在Java中，实体与实体的关系，是通过实体的引用，去维系的。
    
    //当前订单所属的用户（外键）
    private User user;

    public int getId() {
        return id;
    }

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

    public Date getOrdertime() {
        return ordertime;
    }

    public void setOrdertime(Date 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;
    }

    @Override
    public String toString() {
        return "Order{" +
            "id=" + id +
            ", ordertime=" + ordertime +
            ", total=" + total +
            ", user=" + user +
            '}';
    }
}

接口：UserMapper

package com.lyh.mapper;

public interface UserMapper {

}

接口：OrderMapper

package com.lyh.mapper;

public interface OrderMapper {

}

OrderMapper.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.lyh.mapper.OrderMapper">

</mapper>

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.lyh.mapper.UserMapper">

</mapper>

sqlMapConfig.xml文件：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<!--通过properties加载外部properties文件-->
    <properties resource="jdbc.properties"/>

    <!--自定义别名-->
    <typeAliases>
        <typeAlias type="com.lyh.domain.User" alias="user"/>
        <typeAlias type="com.lyh.domain.Order" alias="order"/>
    </typeAliases>


	<!--数据源环境-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

	<!--加载映射文件-->
    <mappers>
        <mapper resource="com/lyh/mapper/UserMapper.xml"/>
        <mapper resource="com/lyh/mapper/OrderMapper.xml"/>
    </mappers>


</configuration>

mybatis的多表操作-一对一的配置实现

查询order表的同时查询user表

把表全部查出，把数据全部查出，才能进行封装。

接口：OrderMapper

package com.lyh.mapper;

import com.lyh.domain.Order;

import java.util.List;

public interface OrderMapper {
    public List<Order> findAll();
}

OrderMapper.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.lyh.mapper.OrderMapper">
  <!--orderMap主要封装order对象数据，所以type中要写order -->
  <!--因为在mybatis核心配置文件中定义了order对象的别名，所以type可以写order-->
  <resultMap id="orderMap" type="order">
    <!--手动指定字段与实体属性的映射关系-->
    <!--主键用id-->
    <!--column：数据表的字段名称   property：实体（Order）的属性名称-->
    <!--column="oid"，查询的时候，为order的id定义了别名oid，如果是id名字的话，order的id和user的id不好区分-->
    <id column="oid" property="id"></id>
    <!--普通属性用 result标签 -->
    <result column="ordertime" property="ordertime"></result>
    <result column="total" property="total"></result>
    <!--User往哪里封装？User接收哪儿的数据？-->
    <result column="uid" property="user.id"></result>
    <result column="username" property="user.username"></result>
    <result column="password" property="user.password"></result>
    <result column="birthday" property="user.birthday"></result>

  </resultMap>
  <!--
  resultType="order"，这里就不能这样写了，得换成resultMap=""，Map是键值对，resultMap中写上面封装的Map
  查询到的数据如果往order中封装，但是order中，没有username，password字段，内部只有一个user对象
  -->
  <!--Map是键值对，resultMap中写上面封装的Map-->
  <select id="findAll" resultMap="orderMap">
    select *,o.id oid from orders o,user u where o.uid = u.id
  </select>

</mapper>

测试类：MapperTest

package com.lyh.test;

//ibatis是mybatis的前身
import com.github.pagehelper.PageHelper;
import com.lyh.domain.Order;
import com.lyh.domain.User;
import com.lyh.mapper.OrderMapper;
import com.lyh.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MapperTest {

    //查询全部
    @Test
    public void test3() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        
        OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
        List<Order> orderList = orderMapper.findAll();
        for (Order order : orderList) {
            System.out.println(order);
        }

        sqlSession.close();

    }

}

效果：

mybatis的多表操作-一对一的配置实现2

OrderMapper.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.lyh.mapper.OrderMapper">
  <!--orderMap主要封装order对象数据，所以type中要写order -->
  <!--因为在mybatis核心配置文件中定义了order对象的别名，所以type可以写order-->
  <resultMap id="orderMap" type="order">
    <!--手动指定字段与实体属性的映射关系-->
    <!--主键用id-->
    <!--column：数据表的字段名称   property：实体（Order）的属性名称-->
    <!--column="oid"，查询的时候，为order的id定义了别名oid。如果是id的话，order的id和user的id不好区分-->
    <id column="oid" property="id"></id>
    <!--普通属性用 result标签 -->
    <result column="ordertime" property="ordertime"></result>
    <result column="total" property="total"></result>
      
    <!--User往哪里封装？User接收哪儿的数据？-->
    <!--<result column="uid" property="user.id"></result>-->
    <!--<result column="username" property="user.username"></result>-->
    <!--<result column="password" property="user.password"></result>-->
    <!--<result column="birthday" property="user.birthday"></result>-->
      
    <!--association这里的意思是匹配的意思，与.....相联系-->
    <!--
    property：当前实体（order）中的属性名称（private User user）
    javaType：当前实体（order）中的属性的类型（User）
    javaType="user"，中的user是因为定义了别名
    -->
    <association property="user" javaType="user">
      <!--property="id"：这里的id指的是User对象中的属性id-->
      <id column="uid" property="id"></id>
      <!--property="username"：这里的username指的是User对象中的属性username-->
      <result column="username" property="username"></result>
      <result column="password" property="password"></result>
      <result column="birthday" property="birthday"></result>
    </association>

  </resultMap>
  <!--
  resultType="order"，这里就不能这样写了，得换成resultMap=""，Map是键值对，resultMap中写上面封装的Map
  查询语句，查询到的数据往order中封装，
  但是order中，没有username，password字段，内部只有一个user对象
  -->
  <!--Map是键值对，resultMap中写上面封装的Map-->
  <select id="findAll" resultMap="orderMap">
    select *,o.id oid from orders o,user u where o.uid = u.id
  </select>

</mapper>

类：MapperTest

package com.lyh.test;

//ibatis是mybatis的前身
import com.github.pagehelper.PageHelper;
import com.lyh.domain.Order;
import com.lyh.domain.User;
import com.lyh.mapper.OrderMapper;
import com.lyh.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MapperTest {

    //查询全部
    @Test
    public void test3() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        
        OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
        List<Order> orderList = orderMapper.findAll();
        for (Order order : orderList) {
            System.out.println(order);
        }

        sqlSession.close();

    }

}

效果：

mybatis的多表操作-一对多配置实现

实体类：User

package com.lyh.domain;

import java.util.Date;
import java.util.List;

public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday; //新添加的Date

    //描述的是当前用户存在那些订单
    //一个用户可以存在很多订单，所以用List集合
    private List<Order> orderList;

    public List<Order> getOrderList() {
        return orderList;
    }

    public void setOrderList(List<Order> orderList) {
        this.orderList = orderList;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    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 getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    //方便测试，写上toString()方法

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

接口：UserMapper

package com.lyh.mapper;

import com.lyh.domain.User;
import java.util.List;

public interface UserMapper {
    public List<User> findAll();
}

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.lyh.mapper.UserMapper">

  <!--type="user"，定义了别名就是user-->
  <resultMap id="userMap" type="user">
    <id column="uid" property="id"></id>
    <result column="username" property="username"></result>
    <result column="password" property="password"></result>
    <result column="birthday" property="birthday"></result>

    <!--
    配置集合信息
    property：集合名称
    ofType：当前集合中的数据类型
    -->
    <!--ofType="order"，也是别名-->
    <collection property="orderList" ofType="order">
      <!--封装order的数据-->
      <id column="oid" property="id"></id>
      <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,orders o where u.id=o.uid
  </select>

</mapper>

测试类：MapperTest

package com.lyh.test;

//ibatis是mybatis的前身
import com.github.pagehelper.PageHelper;
import com.lyh.domain.Order;
import com.lyh.domain.User;
import com.lyh.mapper.OrderMapper;
import com.lyh.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MapperTest {
    
	//查询全部 User
    @Test
    public void test4() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.findAll();
        for (User user : userList) {
            System.out.println(user);
        }

        sqlSession.close();
    }

}

效果：

mybatis的多表操作-多对多配置实现

user表：

/*
Navicat MySQL Data Transfer

Source Server         : huaxiang
Source Server Version : 50725
Source Host           : localhost:3306
Source Database       : test

Target Server Type    : MYSQL
Target Server Version : 50725
File Encoding         : 65001

Date: 2023-03-25 17:50:16
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(10) DEFAULT NULL,
  `password` varchar(13) DEFAULT NULL,
  `birthday` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'zhansan', '1234', null);
INSERT INTO `user` VALUES ('2', 'lisi', '3434', null);
INSERT INTO `user` VALUES ('3', 'wangwu', '3445', null);
INSERT INTO `user` VALUES ('4', 'shaus', 'jskajsk', null);
INSERT INTO `user` VALUES ('5', 'aski', 'sajhsj', null);
INSERT INTO `user` VALUES ('6', 'yyds', '7654321', null);
INSERT INTO `user` VALUES ('7', 'ceshi', 'abc', null);
INSERT INTO `user` VALUES ('8', 'ceshi', 'abc', null);
INSERT INTO `user` VALUES ('9', 'ceshi', 'abc', null);

sys_role表：

/*
Navicat MySQL Data Transfer

Source Server         : huaxiang
Source Server Version : 50725
Source Host           : localhost:3306
Source Database       : test

Target Server Type    : MYSQL
Target Server Version : 50725
File Encoding         : 65001

Date: 2023-03-25 17:52:00
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `sys_role`
-- ----------------------------
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `roleName` varchar(50) DEFAULT NULL,
  `roleDesc` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of sys_role
-- ----------------------------
INSERT INTO `sys_role` VALUES ('1', '院长', '负责全面工作');
INSERT INTO `sys_role` VALUES ('2', '研究员', '课程研发工作');
INSERT INTO `sys_role` VALUES ('3', '讲师', '授课工作');
INSERT INTO `sys_role` VALUES ('5', '班主任', '负责学生的生活问题');
INSERT INTO `sys_role` VALUES ('8', '就业指导', '负责学生的就业工作');

sys_user_role表：

/*
Navicat MySQL Data Transfer

Source Server         : huaxiang
Source Server Version : 50725
Source Host           : localhost:3306
Source Database       : test

Target Server Type    : MYSQL
Target Server Version : 50725
File Encoding         : 65001

Date: 2023-03-25 17:53:27
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `sys_user_role`
-- ----------------------------
DROP TABLE IF EXISTS `sys_user_role`;
CREATE TABLE `sys_user_role` (
  `userId` bigint(20) NOT NULL,
  `roleId` bigint(20) NOT NULL,
  PRIMARY KEY (`userId`,`roleId`),
  KEY `roleId` (`roleId`),
  CONSTRAINT `sys_user_role_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `sys_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 ('1', '2');
INSERT INTO `sys_user_role` VALUES ('3', '2');
INSERT INTO `sys_user_role` VALUES ('3', '5');
INSERT INTO `sys_user_role` VALUES ('4', '8');

实体类：Role

package com.lyh.domain;

//不用去建立这个角色的RoleMapper包括RoleMapper.xml文件
//因为现在做的是查询用户，与此同时查询用户的角色
public class Role {
    private int id;
    private String roleName;
    private String roleDesc;

    public int getId() {
        return id;
    }

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

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

    public String getRoleDesc() {
        return roleDesc;
    }

    public void setRoleDesc(String roleDesc) {
        this.roleDesc = roleDesc;
    }

    @Override
    public String toString() {
        return "Role{" +
            "id=" + id +
            ", roleName='" + roleName + '\'' +
            ", roleDesc='" + roleDesc + '\'' +
            '}';
    }
}

实体类：User

package com.lyh.domain;

import java.util.Date;
import java.util.List;

public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday; 

    //描述的是当前的用户具备那些角色
    //一个角色是一个role实体，多个角色同样也用集合封装
    private List<Role> roleList;

    public List<Role> getRoleList() {
        return roleList;
    }

    public void setRoleList(List<Role> roleList) {
        this.roleList = roleList;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    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 getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    //方便测试，写上toString()方法
    @Override
    public String toString() {
        return "User{" +
            "id=" + id +
            ", username='" + username + '\'' +
            ", password='" + password + '\'' +
            ", birthday=" + birthday +
            ", roleList=" + roleList +
            '}';
    }
}

接口：UserMapper

package com.lyh.mapper;

import com.lyh.domain.User;
import java.util.List;

public interface UserMapper {

    public List<User> findUserAndRoleAll();//新定义的
}

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.lyh.mapper.UserMapper">
  
  <!--=========================================================-->
  <resultMap id="userRoleMap" type="user">
    <!--user中有个role的集合需要去封装-->
    <!--封装user的信息-->
    <!--user的id从表当中再去查找的时候获取的不是uid了，而是userId-->
    <id column="userId" property="id"></id>
    <result column="username" property="username"></result>
    <result column="password" property="password"></result>
    <result column="birthday" property="birthday"></result>
      
    <!--封装user内部的roleList的信息，同样Role实体定义了别名role-->
    <collection property="roleList" ofType="role">
      <id column="roleId" property="id"></id>
      <result column="roleName" property="roleName"></result>
      <result column="roleDesc" property="roleDesc"></result>
    </collection>
      
  </resultMap>
    
  <select id="findUserAndRoleAll" resultMap="userRoleMap">
    select * from user u,sys_user_role ur,sys_role r where u.id=ur.userId and ur.roleId=r.id
  </select>

</mapper>

测试类：MapperTest

package com.lyh.test;

//ibatis是mybatis的前身
import com.github.pagehelper.PageHelper;
import com.lyh.domain.Order;
import com.lyh.domain.User;
import com.lyh.mapper.OrderMapper;
import com.lyh.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MapperTest {

    @Test
    public void test5() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();

        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userAndRoleAll = userMapper.findUserAndRoleAll();
        for (User user : userAndRoleAll) {
            System.out.println(user);
        }

        sqlSession.close();

    }

}

效果：没有角色的user没有打印，如果想打印没有角色的user，就把内连接查询改成左外就可以了。

mybatis的多表操作-知识小结

一对一配置：或者+
一对多和多对多主要的区别在于，多对多有一张中间表，在查询时需要把中间表融入到查询语句中，才能查询出需要的数据。

mybatis注解开发-常用注解介绍

mybatis注解开发-xml完成基本的crud

pom.xml文件：

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.lyh</groupId>
  <artifactId>spring_demo_01</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>war</packaging>

  <name>spring_demo_01 Maven Webapp</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.7</maven.compiler.source>
    <maven.compiler.target>1.7</maven.compiler.target>
  </properties>

  <dependencies>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.32</version>
    </dependency>

    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.4.6</version>
    </dependency>

    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
    </dependency>

    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.17</version>
    </dependency>

    <dependency>
      <groupId>com.github.pagehelper</groupId>
      <artifactId>pagehelper</artifactId>
      <version>3.7.5</version>
    </dependency>

    <dependency>
      <groupId>com.github.jsqlparser</groupId>
      <artifactId>jsqlparser</artifactId>
      <version>0.9.1</version>
    </dependency>


  </dependencies>
</project>

jdbc.properties文件：

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test
jdbc.username=root
jdbc.password=1234

log4j.properties文件：

### direct log messages to stdout ###
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

### direct messages to file mylog.log ###
log4j.appender.file=org.apache.log4j.FileAppender
log4j.appender.file.File=c:/mylog.log
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

### set log levels - for more verbose logging change 'info' to 'debug' ###
### 级别是信息级别的info 改为debug ###
log4j.rootLogger=debug, stdout

实体类：User

package com.lyh.domain;

import java.util.Date;
import java.util.List;

public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday; 

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    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 getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }


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

接口：UserMapper

package com.lyh.mapper;


import com.lyh.domain.User;
import java.util.List;

public interface UserMapper {
    public void save(User user);
    public void update(User user);
    public void delete(int id);
    public User findById(int id);
    public List<User> findAll();

}

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.lyh.mapper.UserMapper">
  <insert id="save" parameterType="user">
    insert into user values(#{id},#{username},#{password},#{birthday})
  </insert>

  <delete id="delete" parameterType="int">
    delete from user where id=#{id}
  </delete>

  <update id="update" parameterType="user">
    update user set username=#{username},password=#{password} where id=#{id}
  </update>

  <select id="findById" parameterType="int" resultType="user">
    select * from user where id=#{id}
  </select>

  <select id="findAll" resultType="user">
    select * from user
  </select>

</mapper>

sqlMapConfig.xml文件：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
  <!--    通过properties加载外部properties文件-->
  <properties resource="jdbc.properties"/>

  <!--    自定义别名 -->
  <typeAliases>
    <typeAlias type="com.lyh.domain.User" alias="user"/>
  </typeAliases>

  <!--    数据源环境-->
  <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC"></transactionManager>
      <dataSource type="POOLED">
        <property name="driver" value="${jdbc.driver}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
      </dataSource>
    </environment>
  </environments>

  <!--    加载映射文件-->
  <mappers>
    <mapper resource="com/lyh/mapper/UserMapper.xml"/>
  </mappers>


</configuration>

测试类：MybatisTest

package com.lyh.test;

import com.lyh.domain.User;
import com.lyh.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MybatisTest {
    //抽取公用的代码
    private UserMapper mapper;
    @Before
    public void before() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession session = sqlSessionFactory.openSession(true);
        mapper = session.getMapper(UserMapper.class);
    }

    //添加
    @Test
    public void testSave(){
        User user = new User();
        user.setUsername("Robin");
        user.setPassword("abcd");
        mapper.save(user);
    }

    //修改
    @Test
    public void testUpdate(){
        User user = new User();
        user.setId(11);
        user.setUsername("Pony");
        user.setPassword("123");
        mapper.update(user);
    }

    //删除
    @Test
    public void testDelete(){
        mapper.delete(11);
    }

    //查询
    @Test
    public void testFindById(){
        User user = mapper.findById(6);
        System.out.println(user);
    }

    //查询
    @Test
    public void testFindAll(){
        List<User> userList = mapper.findAll();
        for (User user : userList) {
            System.out.println(user);
        }
    }



}

效果：
添加效果：

修改效果：

删除效果：

通过id查询用户：

查询所有用户：

mybatis注解开发-注解完成基本的crud

接口：UserMapper

package com.lyh.mapper;


import com.lyh.domain.User;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

public interface UserMapper {
    @Insert("insert into user values(#{id},#{username},#{password},#{birthday})")
    public void save(User user);

    @Update("update user set username=#{username},password=#{password} where id=#{id}")
    public void update(User user);

    @Delete("delete from user where id=#{id}")
    public void delete(int id);

    @Select("select * from user where id=#{id}")
    public User findById(int id);

    @Select("select * from user")
    public List<User> findAll();

}

sqlMapConfig.xml文件：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
  <!--通过properties加载外部properties文件-->
  <properties resource="jdbc.properties"/>

  <!--自定义别名 -->
  <typeAliases>
    <typeAlias type="com.lyh.domain.User" alias="user"/>
  </typeAliases>

  <!--数据源环境-->
  <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC"></transactionManager>
      <dataSource type="POOLED">
        <property name="driver" value="${jdbc.driver}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
      </dataSource>
    </environment>
  </environments>

  <!--加载映射关系-->
  <mappers>
    <!--指定接口所在的包-->
    <!--只要指定了接口所在的包，就会自动扫描这个包下的接口，接口内部有对应的注解，扫描到之后，也就相当于加载了映射关系-->
    <package name="com.lyh.mapper"/>
  </mappers>

</configuration>

测试类还是上一节的，MybatisTest 测试类，也能出现同样的效果。

mybatis注解开发-一对一的注解开发

创建实体类：Order

package com.lyh.domain;

import java.util.Date;

public class Order {
    private int id;
    private Date ordertime;
    private double total;

    //当前订单属于哪一个用户
    private User user;

    public int getId() {
        return id;
    }

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

    public Date getOrdertime() {
        return ordertime;
    }

    public void setOrdertime(Date 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;
    }

    @Override
    public String toString() {
        return "Order{" +
            "id=" + id +
            ", ordertime=" + ordertime +
            ", total=" + total +
            ", user=" + user +
            '}';
    }
}

接口：OrderMapper

package com.lyh.mapper;

import com.lyh.domain.Order;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface OrderMapper {

    @Select("select *,o.id oid from orders o,user u where o.uid=u.id")
    @Results({
        @Result(column = "oid",property = "id"),
        @Result(column = "ordertime",property = "ordertime"),
        @Result(column = "total",property = "total"),
        @Result(column = "uid",property = "user.id"),
        @Result(column = "username",property = "user.username"),
        @Result(column = "password",property = "user.password"),
        @Result(column = "birthday",property = "user.birthday")
    })
    public List<Order> findAll();
}

MybatisTest2

package com.lyh.test;

import com.lyh.domain.Order;
import com.lyh.domain.User;
import com.lyh.mapper.OrderMapper;
import com.lyh.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MybatisTest2 {
    //抽取公用的代码
    private OrderMapper mapper;
    @Before
    public void before() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession session = sqlSessionFactory.openSession(true);
        mapper = session.getMapper(OrderMapper.class);
    }


    //查询
    @Test
    public void testFindAll(){
        List<Order> orderList = mapper.findAll();
        for (Order order : orderList) {
            System.out.println(order);
        }
    }



}

效果：

mybatis注解开发-一对一的注解开发2

接口：OrderMapper

package com.lyh.mapper;

import com.lyh.domain.Order;
import com.lyh.domain.User;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface OrderMapper {

    //在进行多表查询时，第一种方式是两张表一起查、第二种方式是分表查询
    //可以先查询orders表，然后再根据uid（外键）的值，去查询user表
    //把uid作为一个参数，去查询user表获得数据，往user对象中封装
    //用这种方式比较多
    @Select("select * from orders") //这里查询的是orders表
    @Results({
        @Result(column = "id",property = "id"),
        @Result(column = "ordertime",property = "ordertime"),
        @Result(column = "total",property = "total"),
        @Result(
            //再往下封装是user
            //要封装的实体类型
            javaType = User.class, //（findById 返回值的结果user）
            //封装user的时候，得查询user的信息
            //要封装的属性名称
            property = "user",
            column = "uid",//根据那个字段 去 查询 user表 的数据 （findById 传递进去的值）
            //one是一对一
            //select属性 代表查询那个接口的方法获得数据
            //引用对方User的接口方法进行查询
            one = @One(select = "com.lyh.mapper.UserMapper.findById")
        )
    })
    public List<Order> findAll();


}

findById方法：

测试类：MybatisTest2

package com.lyh.test;

import com.lyh.domain.Order;
import com.lyh.domain.User;
import com.lyh.mapper.OrderMapper;
import com.lyh.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MybatisTest2 {
    //抽取公用的代码
    private OrderMapper mapper;
    @Before
    public void before() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession session = sqlSessionFactory.openSession(true);
        mapper = session.getMapper(OrderMapper.class);
    }

    //查询
    @Test
    public void testFindAll(){
        List<Order> orderList = mapper.findAll();
        for (Order order : orderList) {
            System.out.println(order);
        }
    }

}

效果：

mybatis注解开发-一对多的注解开发

实体类：User

package com.lyh.domain;

import java.util.Date;
import java.util.List;

public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;

    //描述的是当前用户具有的订单，一个用户可能有多个订单，所以是List
    private List<Order> orderList;

    public List<Order> getOrderList() {
        return orderList;
    }

    public void setOrderList(List<Order> orderList) {
        this.orderList = orderList;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    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 getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

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

接口：OrderMapper

package com.lyh.mapper;

import com.lyh.domain.Order;
import com.lyh.domain.User;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface OrderMapper {
    //一对多 新添加的
    //第二步语句
    @Select("select * from orders where uid=#{uid}")
    public List<Order> findByUid(int uid);

    
    @Select("select * from orders") //这里查询的是orders表
    @Results({
        @Result(column = "id",property = "id"),
        @Result(column = "ordertime",property = "ordertime"),
        @Result(column = "total",property = "total"),
        @Result(
            //再往下封装是user
            //要封装的实体类型
            javaType = User.class, //（findById 返回值的结果user）
            //封装user的时候，得查询user的信息
            //要封装的属性名称
            property = "user",
            column = "uid",//根据那个字段 去 查询 user表 的数据 （findById 传递进去的值）
            //one是一对一
            //select属性 代表查询那个接口的方法获得数据
            //引用对方User的接口方法进行查询
            one = @One(select = "com.lyh.mapper.UserMapper.findById")
        )
    })
    public List<Order> findAll();


}

接口：UserMapper

package com.lyh.mapper;


import com.lyh.domain.Order;
import com.lyh.domain.User;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface UserMapper {
    @Insert("insert into user values(#{id},#{username},#{password},#{birthday})")
    public void save(User user);

    @Update("update user set username=#{username},password=#{password} where id=#{id}")
    public void update(User user);

    @Delete("delete from user where id=#{id}")
    public void delete(int id);

    @Select("select * from user where id=#{id}")
    public User findById(int id);

    @Select("select * from user")
    public List<User> findAll();

    //第一步语句
    @Select("select * from user")
    @Results({
        //如果想要表明是主键id的话，可以加上 id = true 这个属性
        @Result(id = true,column = "id",property = "id"),
        @Result(column = "username",property = "username"),
        @Result(column = "password",property = "password"),
        @Result(column = "birthday",property = "birthday"),
        @Result(
            property="orderList",
            column = "id",
            javaType = List.class,
            many = @Many(select = "com.lyh.mapper.OrderMapper.findByUid")
        )

    })
    public List<User> findUserAndOrderAll();

}

测试类：MybatisTest3

package com.lyh.test;

import com.lyh.domain.Order;
import com.lyh.domain.User;
import com.lyh.mapper.OrderMapper;
import com.lyh.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MybatisTest3 {
    //抽取公用的代码
    private UserMapper mapper;
    @Before
    public void before() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession session = sqlSessionFactory.openSession(true);
        mapper = session.getMapper(UserMapper.class);
    }


    //查询
    @Test
    public void testFindAll(){
        List<User> userAndOrderAll = mapper.findUserAndOrderAll();
        for (User user : userAndOrderAll) {
            System.out.println(user);
        }
    }
}

效果：

mybatis注解开发-多对多注解开发

查询用户的同时，把用户所拥有的的角色也一并查出来。
一对多查询和多对多查询有些相似。
多对多查询存在一张中间表。

select * from user
select * from sys_user_role ur,sys_role r where ur.roleId=r.id and ur.userId=1(上一句sql结果的id的值)

select * from sys_user_role ur,sys_role r where ur.roleId=r.id and ur.userId=1(上一句sql结果的id的值)
select * from sys_user_role ur,sys_role r where ur.roleId=r.id and ur.userId=1
-- select * from sys_user_role ur,sys_role r where ur.roleId=r.id
-- 为sys_user_role表 相应地 添加 角色名称 和 角色描述
-- select * from sys_user_role ur,sys_role r where ur.roleId=r.id and ur.userId=1
-- 为sys_user_role表 相应地 添加 角色名称 和 角色描述 之后 显示出 userId为1的多个角色信息

实体类：Role

package com.lyh.domain;

//查询用户信息，与此同时查询出用户所对应的所有角色信息
public class Role {
    private int id;
    private String roleName;
    private String roleDesc;

    public int getId() {
        return id;
    }

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

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

    public String getRoleDesc() {
        return roleDesc;
    }

    public void setRoleDesc(String roleDesc) {
        this.roleDesc = roleDesc;
    }

    @Override
    public String toString() {
        return "Role{" +
            "id=" + id +
            ", roleName='" + roleName + '\'' +
            ", roleDesc='" + roleDesc + '\'' +
            '}';
    }
}

实体类：User

package com.lyh.domain;

import java.util.Date;
import java.util.List;

public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;

    //当前用户具备那些角色
    private List<Role> roleList;

    public List<Role> getRoleList() {
        return roleList;
    }

    public void setRoleList(List<Role> roleList) {
        this.roleList = roleList;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    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 getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

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

}

接口：RoleMapper

package com.lyh.mapper;

import com.lyh.domain.Role;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface RoleMapper {

    //第二步语句
    @Select("select * from sys_user_role ur,sys_role r where ur.roleId=r.id and ur.userId=#{uid}")
    public List<Role> findByUid(int uid);
}

接口：UserMapper

package com.lyh.mapper;


import com.lyh.domain.Order;
import com.lyh.domain.User;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface UserMapper {
    @Insert("insert into user values(#{id},#{username},#{password},#{birthday})")
    public void save(User user);

    @Update("update user set username=#{username},password=#{password} where id=#{id}")
    public void update(User user);

    @Delete("delete from user where id=#{id}")
    public void delete(int id);

    @Select("select * from user where id=#{id}")
    public User findById(int id);

    @Select("select * from user")
    public List<User> findAll();

   
    @Select("select * from user")
    @Results({
        //如果想要表明是主键id的话，可以加上 id = true 这个属性
        @Result(id = true,column = "id",property = "id"),
        @Result(column = "username",property = "username"),
        @Result(column = "password",property = "password"),
        @Result(column = "birthday",property = "birthday"),
        @Result(
            property="orderList",
            column = "id",
            javaType = List.class,
            many = @Many(select = "com.lyh.mapper.OrderMapper.findByUid")
        )

    })
    public List<User> findUserAndOrderAll();

    //第一步语句
    @Select("select * from user")
    @Results({
        @Result(id=true,column = "id",property = "id"),
        @Result(column = "username",property = "username"),
        @Result(column = "password",property = "password"),
        @Result(column = "birthday",property = "birthday"),
        @Result(
            property = "roleList",
            column = "id",
            javaType = List.class,
            many=@Many(select = "com.lyh.mapper.RoleMapper.findByUid")
        )
    })
    public List<User> findUserAndRoleAll();

}

测试类：MybatisTest4

package com.lyh.test;

import com.lyh.domain.User;
import com.lyh.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MybatisTest4 {
    //抽取公用的代码
    private UserMapper mapper;
    @Before
    public void before() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession session = sqlSessionFactory.openSession(true);
        mapper = session.getMapper(UserMapper.class);
    }

    //查询
    @Test
    public void testFindAll(){
        List<User> userAndRoleAll = mapper.findUserAndRoleAll();
        for (User user : userAndRoleAll) {
            System.out.println(user);
        }
    }
}