- /**
- * 需求说明: 为用户管理之查询用户列表功能增加分页实现 列表结果按照创建时间降序排列
- * @param roleids
- * @return
- */
- public List<User> getUserListByPage(@Param("usercode")String usercode,@Param("userName")String userName,@Param("userRole")Integer userRole,@Param("pageSize")Integer pageSize,@Param("currentPage")Integer currentPage);
- <!-- 分页查询 -->
- <select id="getUserListByPage" resultMap="userList">
- select * from smbms_user a,smbms_role r where 1=1 and a.userrole=r.id
- <if test="userName!=null and userName!='' "> and a.userName like concat('%',#{userName},'%') </if>
- <if test="userRole!=null and userRole!='' "> and a.userrole=#{userRole} </if>
- order by a.creationdate desc limit #{currentPage},#{pageSize}
- </select>
- <!-- 当数据库中的字段信息与对象的属性不一致时需要通过 resultMap 来映射 -->
- <resultMap type="User" id="userList">
- <result property="id" column="id"/>
- <result property="userCode" column="userCode"/>
- <result property="userName" column="userName"/>
- <result property="phone" column="phone"/>
- <result property="birthday" column="birthday"/>
- <result property="gender" column="gender"/>
- <result property="userRole" column="userRole"/>
- <!-- <result property="userRoleName" column="roleName"/> -->
- </resultMap>
- //mybatis 分页
- @Test
- public void testGetUserListByPage(){
- SqlSession sqlSession = null;
- String usercode="";
- String userName="";
- Integer userRole=3;
- Integer pageSize=5;
- Integer currentPage=0;//MySQL 数据库默认起步是从 0 开始
- List<User> userListShow=new ArrayList<User>();
- try {
- sqlSession = MyBatisUtil.createSqlSession();
- userListShow = sqlSession.getMapper(UserMapper.class).getUserListByPage(usercode,userName,userRole,pageSize,currentPage);
- } catch (Exception e) {
- // TODO: handle exception
- e.printStackTrace();
- }finally{
- MyBatisUtil.closeSqlSession(sqlSession);
- }
- for(User user: userListShow){
- logger.debug("testGetUserListByPage UserCode:" + user.getUserCode() + "and UserName:" + user.getUserName()+"and userRole:"+user.getUserRole());
- }
- }
运行结果:
- [DEBUG] 2019-12-22 17:53:33,894 cn.smbms.dao.user.UserMapper.getUserListByPage - ==> Preparing: select * from smbms_user a,smbms_role r where 1=1 and a.userrole=r.id and a.userrole=? order by a.creationdate desc limit ?,?
- [DEBUG] 2019-12-22 17:53:33,911 cn.smbms.dao.user.UserMapper.getUserListByPage - ==> Parameters: 3(Integer), 0(Integer), 5(Integer)
- [DEBUG] 2019-12-22 17:53:33,925 org.apache.ibatis.transaction.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection [[email protected]]
- [DEBUG] 2019-12-22 17:53:33,925 org.apache.ibatis.transaction.jdbc.JdbcTransaction - Closing JDBC Connection [[email protected]]
- [DEBUG] 2019-12-22 17:53:33,925 org.apache.ibatis.datasource.pooled.PooledDataSource - Returned connection 1249086728 to pool.
[DEBUG] 2019-12-22 17:53:33,926 cn.smbms.dao.user.UserMapperTest - testGetUserListByPage UserCode: sunxing and UserName: 孙兴 and userRole:3
[DEBUG] 2019-12-22 17:53:33,926 cn.smbms.dao.user.UserMapperTest - testGetUserListByPage UserCode: zhangchen and UserName: 张晨 and userRole:3
[DEBUG] 2019-12-22 17:53:33,926 cn.smbms.dao.user.UserMapperTest - testGetUserListByPage UserCode: dengchao and UserName: 邓超 and userRole:3
[DEBUG] 2019-12-22 17:53:33,926 cn.smbms.dao.user.UserMapperTest - testGetUserListByPage UserCode: zhaoyan and UserName: 赵燕 and userRole:3
[DEBUG] 2019-12-22 17:53:33,926 cn.smbms.dao.user.UserMapperTest - testGetUserListByPage UserCode: sunlei and UserName: 孙磊 and userRole:3
来源: http://www.bubuko.com/infodetail-3344953.html