方法一：使用索引的方式  不推荐

              按照参数的顺序 从0开始传递的参数 用 0 1 2 代替 缺点是 如果需要在已有的查询条件之前再加条件 需要 修改其他参数的索引

              因为是多个参数 parameterType 不确定 所以 在定义 select 标签时 parameterType 不用写

Mapper：

    <select id = "selectBuMenByPage" resultType = "BuMen">
        With deptInfo AS
        (
           select ROW_NUMBER() OVER(order by id ASC) AS RowNumber,* from tbInfoDept 
        )
        Select * FROM deptInfo Where RowNumber BETWEEN (#{1}*#{0} - #{0}+1) AND (#{1}*#{0})
    </select>

   注意：的是 select 查询中 如果用 Top 的话 是不支持 # 占位符传参的 Top后面是不允许使用问号占位符的

 

接口：参数名任意

List<BuMen> selectBuMenByPage(int offset,int pagesize);

测试调用：

    public void selectBuMenByPage() {
        
        SqlSession session = BuMenUtil.getSqlSession();
        BuMenMapper buMenMapper = session.getMapper(BuMenMapper.class);
        List<BuMen> buMenlisList = buMenMapper.selectBuMenByPage(3,2);                                                                                                                                                                                                        
        session.close();
        System.out.println(buMenlisList);
    }

 

 

 方法二：使用注解

 Mapper：

    <select id = "selectBuMenByPage" resultType = "BuMen">
        With deptInfo AS
        (
           select ROW_NUMBER() OVER(order by id ASC) AS RowNumber,* from tbInfoDept 
        )
        Select * FROM deptInfo Where RowNumber BETWEEN (#{pagesize}*#{offset} - #{offset}+1) AND (#{pagesize}*#{offset})
    </select>

 

接口：注解的value值要和Mapper中的#占位参数一致

List<BuMen> selectBuMenByPage(@Param(value = "offset")int offset,@Param(value = "pagesize")int pagesize);

 

方法三：使用Map参数绑定

Mapper：Mapper中的参数占位符要和调用接口时map中的Key一一对应

    <select id = "selectBuMenByPage" resultType = "BuMen">
        With deptInfo AS
        (
           select ROW_NUMBER() OVER(order by id ASC) AS RowNumber,* from tbInfoDept 
        )
        Select * FROM deptInfo Where RowNumber BETWEEN (#{pagesize}*#{offset} - #{offset}+1) AND (#{pagesize}*#{offset})
    </select>

 

接口：

List<BuMen> selectBuMenByPage(Map<String,Object> map);

测试：

    @Test
    public void selectBuMenByPage() {
        
        SqlSession session = BuMenUtil.getSqlSession();
        BuMenMapper buMenMapper = session.getMapper(BuMenMapper.class);
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("offset", 3);
        map.put("pagesize", 2);
        List<BuMen> buMenlisList = buMenMapper.selectBuMenByPage(map);
        session.close();
        System.out.println(buMenlisList);
    }