Mybatis实现查询分页、使用注解开发

Mybatis实现查询分页、使用注解开发

一、MySQL分页查询语句

select * from user limit #{startIndex}，#{pageSize}；
# startIndex ： 起始位置 ，默认是0开始
# pageSize ：页面大小

例如：

select * from user limit 0,3

查询结果：

二、Mybatis中使用limit实现分页

1. 接口：

   //查询全部用户实现分页
   List<User> selectUserByLimit(Map<String,Integer> map);
   

2. 编写对应mapper映射文件的方法

   <select id="selectUserByLimit" parameterType="Map" resultType="User">
       select * from mybatis.user limit #{startIndex},#{pageSize}
   </select>
   

3. 测试模拟分页

   @Test
   public void selectUserByLimit(){
   
       //创建sqlSession
       SqlSessionFactory sqlSessionFactory = MyBatisUtils.getSqlSessionFactory();
       SqlSession sqlSession = sqlSessionFactory.openSession();
   
       //模拟前端数据数据
       int currentPage = 2;//当前是第几页
       int pageSize = 2; //页面大小
   
       Map<String, Integer> map = new HashMap<String, Integer>();
       map.put("startIndex",(currentPage-1)*pageSize);
       map.put("pageSize",pageSize);
   
       //测试
       UserDao mapper = sqlSession.getMapper(UserDao.class);
       List<User> users = mapper.selectUserByLimit(map);
   
       for (User user : users) {
           System.out.println(user);
       }
   
       sqlSession.close();//关闭连接
   
   }
   

三、Mybatis中使用RowBounds实现分页

1. 接口

   //查询全部用户实现分页使用RowBounds
   List<User> selectUserByRowBounds();
   

2. Mapper映射文件

   <select id="selectUserByRowBounds" resultType="User">
       select * from mybatis.user
   </select>
   

3. 测试模拟分页

   @Test
   public void selectUserByRowBounds(){
       //创建sqlSession
       SqlSessionFactory sqlSessionFactory = MyBatisUtils.getSqlSessionFactory();
       SqlSession sqlSession = sqlSessionFactory.openSession();
   
       int currentPage = 2; //当前页
       int pageSize = 2; //页面大小
   
       RowBounds rowBounds = new RowBounds((currentPage - 1) * pageSize, pageSize);
   
       //注意点；使用RowBounds就不能使用getMapper了
       
       //selectList: 接收一个List
       //selectMap: 接收一个Map
       //selectOne ： 接收只有一个对象的时候
       List<User> users = sqlSession.selectList("org.westos.dao.UserDao.selectUserByRowBounds", null, rowBounds);
   
       for (User user : users) {
           System.out.println(user);
       }
   }
   

limit 和 rowBounds区别

• rowBounds 本质就是封装了limit
• limit 是在SQL层面实现分页
• rowBounds 在代码层面实现分页

四、Mybatis使用注解开发

注解可以替代一些xml文件中的配置，CRUD的注解：

• @insert() 插入数据
• @delete() 删除数据
• @update() 更新数据
• @select() 查询数据

UserMapper接口

package org.westos.dao;

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 org.westos.pojo.User;

import java.util.List;

public interface UserMapper {
    //查询全部用户
    @Select("select * from user")
    List<User> selectUser();

    //通过ID查询用户
    @Select("select * from user where id = #{id}")
    User selectUserById(int id);

    //添加用户
    @Insert("insert into user (id,name,pwd) values (#{id},#{name},#{pwd})")
    int addUser(User user);

    //修改用户信息
    @Update("update user set name = #{name},pwd = #{pwd} where id = #{id}")
    int updateUser(User user);

    //删除用户
    @Delete("delete from user where id=#{id}")
    int deleteUserById(int id);
}

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 resource="database.properties"/>


    <!--配置别名-->
    <typeAliases>
        <typeAlias type="org.westos.pojo.User" alias="User"/>
        <package name="org.westos.pojo"/>
    </typeAliases>

    <!--配置环境，这里可以有多套环境 default代表默认的是那一套-->
    <environments default="development">
        <!--配置一套环境 id .环境的名字-->
        <environment id="development">
            <!--transactionManager:事务管理，type:jdbc-->
            <transactionManager type="JDBC"/>
            <!--dataSource 数据源-->
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <!--xml中不允许&符号直接出现，我们需要使用 &amp; 代替-->
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <!--关联映射文件-->

    <mappers>
        <!--class对应的是一个接口类-->
        <!--resource对应的是一个接口类的映射文件-->
        <!--<mapper resource="org/westos/dao/userMapper.xml"/>-->
        <mapper class="org.westos.dao.UserMapper"/>
    </mappers>

</configuration>

测试类

package org.westos.dao;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.junit.Test;
import org.westos.pojo.User;
import org.westos.utils.MybatisUtils;

import java.sql.ParameterMetaData;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

public class UserMapperTest {
    @Test
    public void selectUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

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


    @Test
    public void selectUserById(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.selectUserById(3);
        System.out.println(user);
    }

    @Test
    public void insertUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int i = mapper.addUser(new User(8, "小明", "236546"));
        System.out.println(i);
    }

    @Test
    public void updateUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int i = mapper.updateUser(new User(3, "橘右京", "12344"));
        System.out.println(i);
    }

    @Test
    public void delete(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.deleteUserById(8);
    }
}

注意：

1. 需开启事务自动提交
2. @param参数尽量都写上，如果有多个参数，就必须填写