Mybatis

1.简介

1.1 环境说明以及课前预备知识

1.2 什么是MyBatis

1.3 持久化

1.4 持久层

1.5 为什么需要Mybatis

2.MyBatis第一个程序

2.1 导入相关的依赖

创建Maven工程并导入相关依赖：mybatis、mysql、junit.log4j

2.2 创建user数据表

CREATE TABLE USER(
id INT PRIMARY KEY,
username VARCHAR(20),
 PASSWORD VARCHAR(20));
 
INSERT INTO `user`
	VALUES (1,'zhangsan','abc'),
		(2,'lisi','987'),
		(3,'wangwu','123'),
		(4,'zhaoliu','5635'),
		(5,'tianqi','4454');

2.3 编写实体类

package com.lwb.pojo;

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

    public User() {
    }

    public User(int id, String username, String password) {
        this.id = id;
        this.username = username;
        this.password = password;
    }

    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 + '\'' +
                '}';
    }
}

2.4 编写UserMapper映射文件

<?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="UserMapper">
    <select id="findAll" resultType="com.lwb.pojo.User">
        select * from user
    </select>
</mapper>

2.5 编写核心配置文件

<?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>
    <!--数据源环境-->
    <environments default="development">
        <environment id="development">
            <!--事务管理-->
            <transactionManager type="JDBC"/>
            <!--数据源-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&amp;useUnicode=true&amp;characterEncoding=utf8"/>
                <property name="username" value="root"/>
                <property name="password" value="lwb"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/lwb/mapper/UserMapper.xml"></mapper>
    </mappers>
</configuration>

2.6 编写测试代码

@Test
    public void test1() throws IOException {
         //加载核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        //得到SqlSession工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        //获取SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //执行sql语句
        List<User> userList = sqlSession.selectList("UserMapper.findAll");
        //打印结果
        System.out.println(userList);
        //结果：[User{id=1, username='zhangsan', password='abc'}, User{id=2, username='lisi', password='987'}, User{id=3, username='wangwu', password='123'}, User{id=4, username='zhaoliu', password='5635'}, User{id=5, username='tianqi', password='4454'}]

        //释放资源
        sqlSession.close();
    }

3.MyBatis的映射文件概述

4.Mybatis的增删改查操作

4.1 插入操作

（1）编写UserMapper映射文件

<!--插入操作-->
    <insert id="add" parameterType="com.lwb.pojo.User">
        <!--参数为User类的属性名-->
        insert into user values(#{id},#{username},#{password})
    </insert>

（2） 编写插入实体User的代码

  @Test
    public void test2() throws IOException {
        //创建User类对象作为参数
        User user = new User(6,"linba","4567");

        //加载核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        //得到SqlSession工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        //获取SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //执行sql语句
        sqlSession.insert("UserMapper.add",user);
        //Mybatis默认事务不自动提交，要提交事务
        sqlSession.commit();
        //释放资源
        sqlSession.close();
    }

4.2 插入操作的注意点

4.3 修改操作

（1）编写UserMapper映射文件

 <!--修改操作-->
    <update id="update" parameterType="com.lwb.pojo.User">
        update user set username=#{username},password=#{password} where id=#{id}
    </update>

（2）编写修改实体User的代码

 //修改
    @Test
    public void test3() throws IOException {
        //创建User类对象作为参数
        User user = new User(6,"wangmou","abcd");

        //加载核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        //得到SqlSession工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        //获取SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //执行sql语句
        sqlSession.update("UserMapper.update",user);
        //Mybatis默认事务不自动提交，要提交事务
        sqlSession.commit();
        //释放资源
        sqlSession.close();
    }

4.4 修改操作的注意点

4.5 删除操作

（1）编写UserMapper映射文件

   <!--删除操作-->
    <delete id="delete" parameterType="java.lang.Integer">
        <!--#里面的id可以是任意的字符串，它只是相当于一个变量名-->
        delete from user where id=#{id}
    </delete>

（2）编写修改实体User的代码

    //删除
    @Test
    public void test4() throws IOException {
        //加载核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        //得到SqlSession工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        //获取SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //执行sql语句
        sqlSession.update("UserMapper.delete",6);
        //Mybatis默认事务不自动提交，要提交事务
        sqlSession.commit();
        //释放资源
        sqlSession.close();
    }

4.6 删除操作的注意点

5.核心配置文件

5.1 MyBatis核心配置文件层级关系

5.2 MyBatis常用配置解析

（1） environments标签


（2）mapper标签：常用的就第一种，后面三种用的比较少

（3）properties标签

properties文件

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=utf8
jdbc.username=root
jdbc.password=lwb

核心配置文件

<?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"></properties>

    <!--数据源环境-->
    <environments default="development">
        <environment id="development">
            <!--事务管理-->
            <transactionManager type="JDBC"/>
            <!--数据源-->
            <dataSource type="POOLED">
                <!--使用$可配置文件的key来读取配置文件-->
                <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/lwb/mapper/UserMapper.xml"></mapper>
    </mappers>
</configuration>

（4）typeAliases标签
为类设置别名，在核心配置文件中配置类的别名，该标签在properties之后

    <!--配置别名，这个标签写在properties标签后面-->
    <typeAliases>
        <typeAlias type="com.lwb.pojo.User" alias="user"></typeAlias>
    </typeAliases>

5.3 核心配置文件小结

6.Mybatis相应的API

6.1 SqlSession工厂构建器SqlSessionFactoryBuilder

6.2 SqlSession工厂对象SqlSessionFactory

6.3 SqlSession会话对象

7.Mybatis的dao层实现

7.1 传统开发模式

（1）结构：

（2）编写UserMapper接口

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

（3）编写接口实现类

public class UserMapperImpl implements UserMapper {
    public List<User> findAll() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        List<User> users = sqlSession.selectList("UserMapper.findAll");
        return users;
    }
}

（4）编写业务层调用Dao层

//创建测试单元代替业务层
public class TestMapper {
    public static void main(String[] args) throws IOException {
        UserMapperImpl userMapper = new UserMapperImpl();
        System.out.println(userMapper.findAll());
    }
}

7.2 代理开发模式

7.2.1 介绍

使用xml配置文件代替了实现类

7.2.2 代码实现

（1）结构

（2）编写UserMapper接口

package com.lwb.mapper;

import com.lwb.pojo.User;

import java.util.List;

public interface UserMapper {
    //查询所有用户
    public List<User> findAll();
    //通过id查询用户
    public User findById(int id);
}

（3）编写UserMapper.xml实现sql语句的执行

<?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.lwb.mapper.UserMapper">
    <!--查询操作-->
    <select id="findAll" resultType="user">
        select * from user
    </select>

    <!--通过id查询操作-->
    <select id="findById" resultType="user" parameterType="int">
        select * from user where id=#{id}
    </select>

</mapper>

（3）编写service层调用Mapper层

//使用测试单元代替了service层
public class TestMapper {
    public static void main(String[] args) throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = mapper.findAll();
        System.out.println(users);
        System.out.println(mapper.findById(1));
        sqlSession.close();
    }
}

8.MyBatis映射文件深入

8.1 动态 SQL 之

根据if的判断条件添加sql语句的条件

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

    <select id="findByConfigs" resultType="user" parameterType="user">
        select * from user
        <where>
            <if test="id!=0">
                and id=#{id}
            </if>

            <if test="username!=null">
                and username=#{username}
            </if>

            <if test="password!=null">
                and password=#{password}
            </if>
        </where>
    </select>
</mapper>

8.2 动态 SQL 之

    <select id="findByIds" parameterType="list" resultType="user">
        select * from user
        <where>
            <foreach collection="list" open="id in (" close=")" separator="," item="id">
                #{id}
            </foreach>
        </where>
    </select>

接口

public interface UserMapper {
    public List<User> findByConfigs(User user);
    public List<User> findByIds(List<Integer> list);
}

测试方法

    @Test
    public void test2() throws IOException {
        List<Integer> list = new ArrayList<Integer>();
        list.add(1);
        list.add(2);
        list.add(4);

        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = mapper.findByIds(list);
        System.out.println(users);
		sqlSession.close();
    }

8.3 sql片段的抽取

9.MyBatis核心配置文件深入

9.1 typeHandlers标签实现数据类型的转换

需求：数据库中生日的数据类型为bigint（毫秒值），java中User类的生日数据类型为Date类型，要实现两者交互时数据类型的转换。

（1）定义转换类继承类BaseTypeHandler并重写四个方法

package com.lwb.handle;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class DateHandle extends BaseTypeHandler<Date> {
    @Override
    //将java数据类型转换为数据库的数据类型
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException {
        long time = date.getTime();
        preparedStatement.setLong(i,time);
    }

    @Override
    //将数据库的数据类型转换为java类型
    public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
        long aLong = resultSet.getLong(s);
        Date date = new Date(aLong);
        return date;
    }

    @Override
    //将数据库的数据类型转换为java类型
    public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
        long aLong = resultSet.getLong(i);
        Date date = new Date(aLong);
        return date;
    }

    @Override
    //将数据库的数据类型转换为java类型
    public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        long aLong = callableStatement.getLong(i);
        Date date = new Date(aLong);
        return date;
    }
}

（2）在Mybatis核心配置文件中进行注册

    <!--注册自定义类型转换-->
    <typeHandlers>
        <typeHandler handler="com.lwb.handle.DateHandle"></typeHandler>
    </typeHandlers>

（3）UserMapper接口和测试类
UserMapper接口

public interface UserMapper {
    public void insert(User user);

    public User selectById(int id);
}

测试类

public class MapperTest {

    //插入用户
    @Test
    public void test1() throws IOException {
        User user = new User();
        user.setId(7);
        user.setUsername("tianmou");
        user.setPassword("4784");
        user.setBirthday(new Date());

        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.insert(user);
        sqlSession.commit();
        sqlSession.close();

    }

    //通过id查询用户
    @Test
    public void test2() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        System.out.println(mapper.selectById(1));
    }

}

9.2 plugins标签实现分页功能

（1）导入相关依赖

        <!--分页助手-->
        <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>

（2）在mybatis核心配置文件中配置PageHelper插件

    <!--配置分页助手-->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageHelper">
            <property name="dialect" value="mysql"/>
        </plugin>
    </plugins>

（3）测试分页数据的获取

//测试分页
    @Test
    public void test3() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        //设置分页相关的参数  当前的页数和条数
        PageHelper.startPage(2,2);

        List<User> users = mapper.findAll();
       for(User user:users){
           System.out.println(user);
       }

       //获取其他相关参数
        PageInfo<User> pageinfo = new PageInfo<User>(users);
        System.out.println("总条数："+pageinfo.getTotal());
        System.out.println("总页数："+pageinfo.getPages());
        System.out.println("当前页数："+pageinfo.getPageNum());
        System.out.println("每页显示条数："+pageinfo.getPageSize());
        System.out.println("上一页页数:"+pageinfo.getPrePage());
        System.out.println("下一页页数："+pageinfo.getNextPage());
        System.out.println("是否是第一页："+pageinfo.isIsFirstPage());
        System.out.println("是否是最后一页："+pageinfo.isIsLastPage());

        sqlSession.close();

    }

10.多表查询

10.1 一对一查询

（1）创建订单表和用户表和查询语句如下

SELECT *,user.id uid,orders.id oid FROM USER,orders WHERE user.id=orders.uid

（2）创建对应的bean对象

（3）在mapper层创建对应的mapper接口

package com.lwb.mapper;

import com.lwb.pojo.User;

import java.util.List;

public interface UserMapper {

}

package com.lwb.mapper;

import com.lwb.pojo.Orders;

import java.util.List;

public interface OrdersMapper {
    public List<Orders> findAll();
}

（3）一对一查询的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">

<!--OrderMapper接口的映射-->
<mapper namespace="com.lwb.mapper.OrdersMapper">
    <!--
        手动指定字体与属性类的映射关系
        column表示字段名称
        property表示实体的属性名称
    -->
    <resultMap id="orderMap" type="orders">
        <id column="oid" property="id"></id>
        <result column="ordertime" property="ordertime"></result>
        <result column="total" property="total"></result>
        <result column="uid" property="user.id"></result>
        <result column="username" property="user.username"></result>
        <result column="password" property="user.password"></result>
    </resultMap>
    <select id="findAll" resultMap="orderMap">
        SELECT *,user.id uid,orders.id oid FROM USER,orders WHERE user.id=orders.uid
    </select>
</mapper>

（4）在核心配置文件中配置加载OrderMapper.xml和UserMapper.xml

    	    <!--添加映射-->
    <mappers>
        <mapper resource="com/lwb/mapper/UserMapper.xml"></mapper>
        <mapper resource="com/lwb/mapper/OrderMapper.xml"></mapper>
    </mappers>

（5）编写测试类

    //测试一对一多表查询
    @Test
    public void test3() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class);

        List<Orders> orders = mapper.findAll();
        for(Orders orders1:orders){
            System.out.println(orders1);
        }
        sqlSession.close();
    }

运行结果

10.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">

<!--OrderMapper接口的映射-->
<mapper namespace="com.lwb.mapper.OrdersMapper">
    <!--
        手动指定字体与属性类的映射关系
        column表示字段名称
        property表示实体的属性名称
    -->
    <resultMap id="orderMap" type="orders">
        <id column="oid" property="id"></id>
        <result column="ordertime" property="ordertime"></result>
        <result column="total" property="total"></result>
        <!--第一个user表示order的属性，第二user是User类的别名-->
        <association property="user" javaType="user">
            <id column="uid" property="id"></id>
            <result column="username" property="username"></result>
            <result column="password" property="password"></result>
        </association>
    </resultMap>
    <select id="findAll" resultMap="orderMap">
        SELECT *,user.id uid,orders.id oid FROM orders,users WHERE orders.uid=user.id
    </select>
</mapper>

10.3 一对多查询

（1）在user类中新建一个集合存放订单

（2）UserMapper接口中定义方法

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

}

（3）映射配置

<?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.lwb.mapper.UserMapper">
    <!--自定义结果的映射-->
    <resultMap id="userMap" type="user">
        <id column="id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <collection property="ordersList" ofType="orders">
            <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 *,orders.id oid from user,orders where user.id=orders.uid
    </select>
</mapper>

（4）测试方法

    //测试一对多多表查询
    @Test
    public void test4() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        List<User> users = mapper.findAll();
        for(User user:users){
            System.out.println(user);
        }
        sqlSession.close();
    }

测试结果（截图太长截不完）

10.4 多对多查询

三个表如图
user表

role表

user_role表

（1）在User类中添加集合存放角色

（2）userMapper接口中定义方法

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

（3）映射

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

    <!--多对多结果映射-->
    <resultMap id="userAndRoleMap" type="user">
        <id column="userid" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>

        <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="findUserAndRole" resultMap="userAndRoleMap">
        select * from user u,sys_role r,sys_user_role ur where u.id=ur.userid and r.id=ur.roleid
    </select>
</mapper>

（4）测试方法

  //测试多对多多表查询
    @Test
    public void test5() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        List<User> users = mapper.findUserAndRole();
        for(User user:users){
            System.out.println(user);
        }
        sqlSession.close();
    }

测试结果

10.5 多表查询总结

11.mabatis注解开发

11.1 常用注解

11.2 mybatis的增删查改

（1）在mapper接口中使用注解完成增删改查的方法

package com.lwb.mapper;

import com.lwb.pojo.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})")
    public void insert(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();
}

（2）在核心配置文件中添加映射关系

    <!--添加映射-->
    <mappers>
        <package name="com.lwb.mapper"/>
    </mappers>

11.3 一对一查询注解开发

查询orders表以及对应订单的用户信息：通过两种注解的方式（详细看以下代码）

package com.lwb.mapper;

import com.lwb.pojo.Orders;
import com.lwb.pojo.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 OrdersMapper {
    //一对一查询方式1
    @Select("select *,o.id oid from orders o,user u where o.uid=u.id")
    @Results(value = {
            @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")
    })
    public List<Orders> findAll1();

    //一对一查询方式2
    @Select("select * from orders")
    @Results(value = {
            @Result(column = "oid", property = "id"),
            @Result(column = "ordertime", property = "ordertime"),
            @Result(column = "total",property = "total"),
            @Result(
                    property = "user",//要封装的属性名
                    column = "uid",//根据那一个字段去查询user表
                    javaType = User.class,//要封装的实体类型
                    //select属性 表示查询那个接口方法获得数据
                    one=@One(select = "com.lwb.mapper.UserMapper.findById")
            )
    })
    public List<Orders> findAll2();
}

11.4 一对多查询注解开发

查询user表以及用户的订单信息
（1）在OrdersMapper接口中定义通过id查询订单的方法

    //查询根据用户id订单
    @Select("select * from orders where uid=#{uid}")
    public List<Orders> findByUid(int uid);

（2）在UserMapper接口中定义查询用户以及对应订单的方法

    //查询用户信息和订单信息（一对多查询）
    @Select("select * from user")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(column = "username",property = "username"),
            @Result(column = "password",property = "password"),
            @Result(column = "id",property = "ordersList",
                    javaType = List.class,
                    many=@Many(select = "com.lwb.mapper.OrdersMapper.findByUid"))
    })
    public List<User> findUserAndOrders();
}

11.5 多对多查询

查询user表以及每个用户对应的所有角色
（1）多对多模型展示

（2）创建RoleMapper接口以及通过id查询

package com.lwb.mapper;

import com.lwb.pojo.Role;
import org.apache.ibatis.annotations.Select;

public interface RoleMapper {
    //根据id查询角色
    @Select("select * from sys_user_role ur,sys_role r where ur.roleId=r.id")
    public Role findById(int id);
}

（3）在UserMapper中完成多对多查询

    //查询用户及其角色
    @Select("select * from user")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(column = "username",property = "username"),
            @Result(column = "password",property = "password"),
            @Result(
                    column = "id",property = "roleList",javaType = List.class,
                    many = @Many(select = "com.lwb.mapper.RoleMapper.findById")
            )
    })
    public List<User> findUserAndRoleAll();
}