Mybatis---动态SQL---分页

动态sql

第一部分------常用动态sql语句（分页，模糊查询 用框架）
1.UserMapper.xml (映射文件)

  <!--用户列表   分页查询-->
  <select id="findUserPage" resultType="cn.kgc.pojo.User">
    SELECT * FROM smbms_user
    <trim prefix="where" prefixOverrides="and">
      <if test="userName!=null and userName!=''">
        and userName like concat('%',#{userName},'%')
      </if>
      <if test="userRole!=null and userRole!=''">
        and userRole=#{userRole}
      </if>
    </trim>
    LIMIT #{pageNo},#{pageSize}
  </select>
    <!--总数-->
  <select id="count" resultType="java.lang.Integer">
      SELECT COUNT(*)FROM smbms_user
    <trim prefix="where" prefixOverrides="and">
      <if test="userName!=null and userName!=''">
        and userName like concat('%',#{userName},'%')
      </if>
      <if test="userRole!=null and userRole!=''">
        and userRole=#{userRole}
      </if>
    </trim>
  </select>

2.UserServiceImpl.java (实现类)
动态sql语句的分页，service实现方法

//查询  分页   用户列表
    public Map findUserPage(int pageNo,String userName,Long userRole) {
        int count = userMapper.count(userName,userRole);//总数
        List<User> userPageList = userMapper.findUserPage((pageNo - 1) * 3, 3,userName,userRole);//分页查询
        int countPage=count%3==0 ? count/3 : (count/3)+1;    //三目运算  计算总页数
        //放进Map
        Map<String,Object> userMap =new HashMap<>();
        userMap.put("count",count);
        userMap.put("userPageList",userPageList);
        userMap.put("countPage",countPage);
        userMap.put("pageNo",pageNo);
        return userMap;
    }

第二部分------三层架构 分页 (未用框架)
1.ServiceImpl.java(实现类)

    //根据title  模糊查询   分页展示
    public Page findNewsDetaiBytitle(String title, int pageNo) throws SQLException {
        Page page=new Page();
        page.setPageNo(pageNo);//当前页
        page.setCountNo(newsDetailDao.NewsDetaiCount(title));//信息总数
        page.setListNewsDetail(newsDetailDao.findNewsDetaiBytitle(title,pageNo));//信息集合
        //总页数在page页面计算的
        return page;
    }

2.DaoImpl（实现类）

//根据title  模糊查询   分页展示
    public List<NewsDetail> findNewsDetaiBytitle(String title, int pageNo) throws SQLException {
        List<NewsDetail>list=new ArrayList<>();
        Page page=new Page();
        int pageSize = page.getPageSize();
        String sql;
        ResultSet rs;
        if(title.equals("")){
            sql="SELECT id,title,summary,author,createdate FROM news_detail  LIMIT ?,?";
            Object[]params={(pageNo-1)*pageSize,pageSize};
            rs = executeQuery(sql, params);
        }else {
            sql="SELECT id,title,summary,author,createdate FROM news_detail WHERE title LIKE ? LIMIT ?,?";
            Object[]params={"%"+title+"%",(pageNo-1)*pageSize,pageSize};
            rs = executeQuery(sql, params);
        }
        while(rs.next()){
            String id = rs.getString("id");
            String title2 = rs.getString("title");
            String summary = rs.getString("summary");
            String author = rs.getString("author");
            String createdate = rs.getString("createdate");

            NewsDetail newsDetail=new NewsDetail();

            newsDetail.setId(Integer.parseInt(id));
            newsDetail.setTitle(title2);
            newsDetail.setSummary(summary);
            newsDetail.setAuthor(author);
            newsDetail.setCreatedate(Timestamp.valueOf(createdate));

            list.add(newsDetail);
        }
        return list;
    }



//总数
    public int NewsDetaiCount(String title) throws SQLException {
        String sql;
        ResultSet rs;
        if(title.equals("")){
            sql="SELECT COUNT(1) FROM news_detail";
            Object[]params={};
            rs = executeQuery(sql, params);
        }else {
            sql="SELECT COUNT(1) FROM news_detail WHERE title LIKE ?";
            Object[]params={"%"+title+"%"};
            rs = executeQuery(sql, params);
        }
        int totle=0;
        while(rs.next()){
            totle = rs.getInt(1);
        }
        return totle;
    }

第三部分 多字段相等，联表查询

SELECT *,
(SELECT `cityName` FROM city WHERE id = #{departureCity})as cityName,
(SELECT `cityName` FROM city WHERE id = #{arrivalCity}) as cityName2
FROM flight 
WHERE  departureCity =#{departureCity} AND arrivalCity =#{arrivalCity}
LIMIT #{pageNo},#{pageSize}