ssm实现crud_mybatis分页查询（2）

1、多表查询

数据库模型图如下：

当查询用户信息的时候，一般需要同时得到他的团队信息，这里对mybatis自动生成的类和mapper进行修改。
在UserMapper接口中新增两个可以得到用户团队信息的方法：

    List<User> selectByExampleWithTeam(UserExample example);

    User selectByPrimaryKeyWithTeam(Integer userId);

对应的，在UserMapper配置文件中添加如下内容：

    <resultMap id="WithTeamResultMap" type="cn.mk95.www.ssm_crud.bean.User">
        <id column="user_id" jdbcType="INTEGER" property="userId"/>
        <result column="user_name" jdbcType="VARCHAR" property="userName"/>
        <result column="email" jdbcType="VARCHAR" property="email"/>
        <result column="sex" jdbcType="CHAR" property="sex"/>
        <result column="team_id" jdbcType="INTEGER" property="teamId"/>
        <association property="team" javaType="cn.mk95.www.ssm_crud.bean.Team">
            <result property="teamId" column="team_id"/>
            <result property="teamName" column="team_name"/>
        </association>
    </resultMap>
    <sql id="withTeam_Column_List">
    u.user_id, u.user_name, u.email, u.sex, u.team_id, t.team_id, t.team_name
    </sql>
    <!--查询用户带团队信息-->
    <!--List<User> selectByExampleWithTeam(UserExample example);
      User selectByPrimaryKeyWithTeam(Integer userId);-->
    <select id="selectByExampleWithTeam" resultMap="WithTeamResultMap">
        select
        <if test="distinct">
            distinct
        </if>
        <include refid="withTeam_Column_List"/>
        FROM `user` u LEFT JOIN team t ON u.team_id=t.team_id
        <if test="_parameter != null">
            <include refid="Example_Where_Clause"/>
        </if>
        <if test="orderByClause != null">
            order by ${orderByClause}
        </if>
    </select>
    <select id="selectByPrimaryKeyWithTeam" parameterType="java.lang.Integer" resultMap="WithTeamResultMap">
        select
        <include refid="withTeam_Column_List"/>
        FROM `user` u LEFT JOIN team t ON u.team_id=t.team_id
        where user_id = #{userId,jdbcType=INTEGER}
    </select>

其中，使用了左连接来查询得到用户团队信息，在配置结果集的时候，使用<association> 标签设置对象及其属性。

2、通过pagehelper来实现分页及测试

在maven配置文件中导入pagehelper：

    <!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper -->
    <dependency>
      <groupId>com.github.pagehelper</groupId>
      <artifactId>pagehelper</artifactId>
      <version>5.0.0</version>
    </dependency>

然后，在mybatis的配置文件中，添加分页功能的支持：

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

之后，创建UserController类来测试分页功能，完整代码如下：

package cn.mk95.www.ssm_crud.controller;

import cn.mk95.www.ssm_crud.bean.User;
import cn.mk95.www.ssm_crud.service.UserService;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;

import java.util.List;

/**
 * Created by yyh on 2017/7/14.
 */
@Controller
public class UserController {

    @Autowired
    UserService userService;

    @RequestMapping("/list")
    public String getUsers(@RequestParam(value = "pn",defaultValue = "0")int pn, Model model){
        PageHelper.startPage(pn,5);//在要分页查询的方法前指定页数和每页的数据数量
        List<User> users=userService.findAll();
        //使用pageoinfo包装查询后的结果，封装了详细的分页信息，包括数据，传入连续显示的页数
        PageInfo pageInfo=new PageInfo(users,5);
        model.addAttribute("pageinfo",pageInfo);
        return "list";
    }
}

UserService的完整代码如下：

package cn.mk95.www.ssm_crud.service;

import cn.mk95.www.ssm_crud.bean.User;
import cn.mk95.www.ssm_crud.dao.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * Created by yyh on 2017/7/14.
 */
@Service
public class UserService {

    @Autowired
    UserMapper userMapper;

    public List<User> findAll() {
        return  userMapper.selectByExample(null);
    }
}

使用spring提供的单元测试来测试分页功能的实现：

package cn.mk95.www.ssm_crud.test;

import cn.mk95.www.ssm_crud.bean.User;
import com.github.pagehelper.PageInfo;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.mock.web.MockHttpServletRequest;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.web.WebAppConfiguration;
import org.springframework.test.web.servlet.MockMvc;
import org.springframework.test.web.servlet.MvcResult;
import org.springframework.test.web.servlet.request.MockMvcRequestBuilders;
import org.springframework.test.web.servlet.setup.MockMvcBuilders;
import org.springframework.web.context.WebApplicationContext;

import java.util.List;

/**
 * Created by yyh on 2017/7/14.
 */
@RunWith(SpringJUnit4ClassRunner.class)
@WebAppConfiguration  //注入spring ioc容器需要用到
@ContextConfiguration(locations = {"file:src/main/webapp/WEB-INF/springContext.xml", "file:src/main/webapp/WEB-INF/springDispatcherServlet.xml"})
public class MvcTest {//ContextConfiguration注解后面的分别是spring配置文件和spring MVC配置文件

    @Autowired
    WebApplicationContext context;

    MockMvc mockMvc;//用于模拟请求

    @Before
    public void initMockMvc() {
        mockMvc = MockMvcBuilders.webAppContextSetup(context).build();
    }

    @Test
    public void findAlluserTest() throws Exception {
        MvcResult result = mockMvc.perform(MockMvcRequestBuilders.get("/list").param("pn", "1")).andReturn();
        MockHttpServletRequest request = result.getRequest();
        PageInfo pageInfo = (PageInfo) request.getAttribute("pageinfo");
        List<User> users = pageInfo.getList();
        System.out.println("用户信息：");
        for (User user : users) {
            System.out.println(user.toString());
        }
        System.out.println("总记录数："+pageInfo.getTotal());
        System.out.println("当前页："+pageInfo.getPageNum());
        System.out.println("连续显示的页数：");
        for (int i=0;i<pageInfo.getNavigatepageNums().length;i++){
            System.out.print(pageInfo.getNavigatepageNums()[i]+" ");
        }
    }
}

运行结果如下：