（二）Mybatis的Dao层接口实现+动态SQL实现+分页插件pagehelper

一.Mybatis代理开发（Mapper接口开发）实现DAO层

1.Mapper接口规范

• Mapper.xml文件中的namespace与mapper接口的全限定类名相同
• Mapper接口中的方法名与Mapper.xml中标签中的id相同（如< select >,< insert >,< delete >,< update >等标签）
• Mapper接口中的方法输入参数类型和mapper.xml中定义的每个sql语句的parameterType的类型相同
• Mapper接口中的方法输出参数类型和mapper.xml中定义的每个sql语句的resultType的类型相同

2.具体实现上述流程

• 定义UserMapper.java 接口

package com.mapper;
import com.domain.User;
import java.io.IOException;
import java.util.List;
public interface UserMapper {
    List<User> findAll() throws IOException;
}

• 为SqlMapConfig.xml添加如下代码，进行类名替换，以后的resultType和parameterType可以直接赋值user

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

• 定义一个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.mapper.UserMapper">
    <!--查询所有-->
    <select id="findAll" resultType="user">
         SELECT * FROM user
    </select>
</mapper>

• 进行接口的测试

@Test
    /**
     * 使用接口代理的方式实现mybatis的dao层
     */
    public void test7() throws Exception {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession session = sessionFactory.openSession();
       // 不需要手动实现Dao接口，由 mybatis 把 UserMapper.xml 生成接口的实现类
        UserMapper userMapper = session.getMapper(UserMapper.class);
        List<User> all = userMapper.findAll();

        for (User user : all) {
            System.out.println(user);
        }
    }

二.Mapper.xml文件中的动态sql语句的实现

• UserMapper.java 代码如下

package com.mapper;
import com.domain.User;
import java.io.IOException;
import java.util.List;
public interface UserMapper {
    List<User> findAll() throws IOException;
    List<User> findByCondition(User user) throws IOException;
    List<User> findByAges(List<Integer> list) throws IOException;
}

（1）if标签的使用

• 在UserMapper.xml中添加如下代码，根据条件查询用户，判断用户名是否为空，如果不为空，将搜索用户名的条件连接在where后；

<!--动态查询单个用户-->
    <select id="findByCondition" resultType="user" parameterType="user">
        SELECT * FROM user
        <where>
            <if test="username!=null">
                AND username = #{username}
            </if>
            <if test="password!=null">
                AND password = #{password}
            </if>
        </where>
    </select>

• 编写测试

@Test
    /**
     * 动态sql查询
     */
    public void test8() throws Exception {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession session = sessionFactory.openSession();
        UserMapper userMapper = session.getMapper(UserMapper.class);
        User condition = new User();
        condition.setUsername("xxx");
        List<User> all = userMapper.findByCondition(condition);
        for (User user : all) {
            System.out.println(user);
        }
    }

• 查询结果如下，黄色部分是具体执行的sql语句
  

（2）foreach标签的使用

• 在UserMapper.xml中添加如下代码

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

• collection->集合类型；open->开始位置；close->结束位置；item->用于取集合中的元素的变量；separator->集合中元素分隔符

• 编写测试如下代码，查询年龄为20，22，23的用户

@Test
    public void test10() throws Exception {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession session = sessionFactory.openSession();

        UserMapper userMapper = session.getMapper(UserMapper.class);
        List<Integer> list = new ArrayList<Integer>();
        list.add(20);
        list.add(22);
        list.add(23);
        List<User> all = userMapper.findByAges(list);
        for (User user : all) {
            System.out.println(user);
        }
    }

• 测试结果
  

（3）sql片段的提取

• 在UserMapper.xml中添加如下代码

 <!--sql语句的抽取-->
    <sql id="selectuser">SELECT * FROM user</sql>

• 此后可以用如下代码替换 SELECT * FROM user

<include refid="selectuser"></include>

三.分页插件pagehelper的使用

• 1.在.pom文件中导入分页助手的坐标

<!--导入分页助手坐标-->
      <dependency>
          <groupId>com.github.pagehelper</groupId>
          <artifactId>pagehelper</artifactId>
          <version>5.3.1</version>
      </dependency>
      <dependency>
          <groupId>com.github.jsqlparser</groupId>
          <artifactId>jsqlparser</artifactId>
          <version>4.4</version>
      </dependency>

• 2.在SqlMapConfig.xml文件中配置分页标签

   <!--配置分页助手插件-->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
    </plugins>

• 3.测试分页代码

public void test7() throws Exception {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession session = sessionFactory.openSession();
        UserMapper userMapper = session.getMapper(UserMapper.class);
        
        // 设置分页相关参数 当前页+每页显示的条数
        PageHelper.startPage(1,4);
        
        List<User> all = userMapper.findAll();
        for (User user : all) {
            System.out.println(user);
        }

        // 获得与分页相关的参数
        PageInfo<User> pageInfo = new PageInfo<User>(all);
        System.out.println("当前页："+pageInfo.getPageNum());
        System.out.println("每页显示条数："+pageInfo.getPageSize());
        System.out.println("总条数："+pageInfo.getTotal());
        System.out.println("总页数："+pageInfo.getPages());
        System.out.println("上一页："+pageInfo.getPrePage());
        System.out.println("下一页："+pageInfo.getNextPage());
        System.out.println("是否是第一页："+pageInfo.isIsFirstPage());
        System.out.println("是否是最后一页："+pageInfo.isIsLastPage());
    }