人员列表条件查询

人员列表条件查询

• controller

//人员模块表现实现类
@WebServlet(
        name = "PersonnelController",
        urlPatterns = "/personnel.do",
        loadOnStartup = 0
)
public class PersonnelController extends HttpServlet {
    //表现层中获取逻辑层实现类
    private PersonnelService personnelService = PersonnelFactory.getPersonnelService();
    //查询数据和添加数据都用到所以写在前面
    private SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");

    //定义Servlet运行方法, 接收客户端请求, 返回服务器响应
    @Override
    protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //当前页面
        int current = 1;

        //list.jsp获得的页面和Java的连接
        String currentStr = request.getParameter("current");
        if (Validator.isNotEmpty(currentStr) && Validator.isInteger(currentStr)) {
            current = Integer.parseInt(currentStr);
        }

        //每页条数
        int size = 10;
        //起始条数
        int start = (current - 1) * size;

        //封装查询参数
        Map<String, Object> params = new HashMap<>();
        params.put("start", start);
        params.put("size", size);
        //查询name
        params.put("name", request.getParameter("name"));
        //查询card
        params.put("card", request.getParameter("card"));
        //查询起薪时间
        String startDate = request.getParameter("start");
        String endDate = request.getParameter("end");
        //验证两个参数是否有值
        if (Validator.isNotEmpty(startDate) && Validator.isNotEmpty(endDate)) {
            //日期必须是yyyy-MM-dd
            if (Validator.isDate(startDate) && Validator.isDate(endDate)) {
                try {
                    params.put("startDate", format.parse(startDate));
                    params.put("endDate", format.parse(endDate));
                } catch (ParseException e) {
                    e.printStackTrace();
                }
            }
        }
        //工作状态
        params.put("state", request.getParameter("state"));
        //车辆补贴
        params.put("vehiclesSubsidies", request.getParameter("vehiclesSubsidies"));
        //供暖补贴
        params.put("heatingSubsidies", request.getParameter("heatingSubsidies"));
        //物业补贴
        params.put("propertySubsidies", request.getParameter("propertySubsidies"));
        //医疗保险
        params.put("healthInsurance", request.getParameter("healthInsurance"));
        //养老保险
        params.put("endowmentInsurance", request.getParameter("endowmentInsurance"));
        //养老失业
        params.put("pensionUnemployment", request.getParameter("pensionUnemployment"));
        //职业年金
        params.put("occupationalPension", request.getParameter("occupationalPension"));
        //工伤生育
        params.put("injuryFertility", request.getParameter("injuryFertility"));

        //查询数据库总条数
        long count = personnelService.queryByCount(params);

        //最大页数
        int max = (int) Math.ceil(count * 1.0 / size);

        //1.查询人员列表 queryByPage:分页查询
        List<Personnel> list = personnelService.queryByPage(params);
        //2.写入请求作用域
        request.setAttribute("list", list);
        request.setAttribute("current", current);
        request.setAttribute("max", max);
        request.setAttribute("count", count);
        request.setAttribute("start", start);
        request.setAttribute("size", size);
        //数据回显
        request.setAttribute("params", params);

        //3.分发器跳转页面
        request.getRequestDispatcher("/view/personnel/list.jsp").forward(request, response);
    }
}

• service

//人员模块逻辑层接口
public interface PersonnelService {
    //分页查询
    List<Personnel> queryByPage(Map<String, Object> params);

    //查询条数
    long queryByCount(Map<String, Object> params);
}

//人员模块逻辑层实现类
public class PersonnelServiceImpl implements PersonnelService {
    //逻辑层中获取数据层实现类
    private PersonnelDao personnelDao = PersonnelFactory.getPersonnelDao();

    //分页查询
    @Override
    public List<Personnel> queryByPage(Map<String, Object> params) {
        return personnelDao.queryByPage(params);
    }

    //查询条数
    @Override
    public long queryByCount(Map<String, Object> params) {
        return personnelDao.queryByCount(params);
    }
}

• dao

//人员数据层接口
public interface PersonnelDao {
    //分页查询
    List<Personnel> queryByPage(Map<String, Object> params);

    //查询条数
    long queryByCount(Map<String, Object> params);
}

//人员模块数据层实现类
public class PersonnelDaoImpl implements PersonnelDao {
    //构造方法
    public PersonnelDaoImpl() {
    }

    //懒汉模式
    private static PersonnelDaoImpl instance = null;

    public static PersonnelDaoImpl getInstance() {
        //双重校验锁:保证不会出现并发
        if (instance == null) {
            synchronized (PersonnelDaoImpl.class) {
                if (instance == null) {
                    instance = new PersonnelDaoImpl();
                }
            }
        }
        return instance;
    }

    //分页查询
    @Override
    public List<Personnel> queryByPage(Map<String, Object> params) {
        //查询数据库
        SqlSession session = MybatisUtils.openSession();
        //分页查询:两个参数:第一个找select语句,第二个找参数
        List<Personnel> list = session.selectList("personnel.queryByPage", params);
        session.close();
        return list;
    }

    @Override
    //查询条数
    public long queryByCount(Map<String, Object> params) {
        //查询数据库
        SqlSession session = MybatisUtils.openSession();
        //selectOne:返回一行
        long count = session.selectOne("personnel.queryByCount", params);
        session.close();
        return count;
    }
}

• xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="personnel">

    <!--条件查询,分页查询-->
    <select id="queryByPage" parameterType="Map" resultType="com.javakc.rms.personnel.entity.Personnel">
        select id,name,unit,card,grade,starting_date,state,contract,vehicles_subsidies,
        heating_subsidies,property_subsidies,health_insurance,endowment_insurance,pension_unemployment,
        occupational_pension,injury_fertility,reason from rms_personnel
        <where>
            <!--名字-->
            <if test="name!=null and name!='' ">
                name like concat('%',#{name},'%')
            </if>
            <!--身份证号-->
            <if test="card!=null and card!='' ">
                and card=#{card}
            </if>
            <!-- 起薪时间-->
            <if test="startDate!=null and endDate!=null ">
                and starting_date between #{startDate} and #{endDate}
            </if>
            <!--工作状态-->
            <if test="state!=null and state!=0 ">
                and state =#{state}
            </if>
            <!--车辆补贴-->
            <if test="vehiclesSubsidies!=null and vehiclesSubsidies==1 ">
                and vehicles_subsidies=1
            </if>
            <!--供暖补贴-->
            <if test="heatingSubsidies!=null and heatingSubsidies==1 ">
                and heating_subsidies=1
            </if>
            <!--医疗保险-->
            <if test="healthInsurance!=null and healthInsurance==1 ">
                and health_insurance=1
            </if>
            <!--养老保险-->
            <if test="endowmentInsurance!=null and endowmentInsurance==1 ">
                and endowment_insurance=1
            </if>
            <!--养老失业-->
            <if test="pensionUnemployment!=null and pensionUnemployment==1 ">
                and pension_unemployment=1
            </if>
            <!--职业年金-->
            <if test="occupationalPension!=null and occupationalPension==1 ">
                and occupational_pension=1
            </if>
            <!--工伤生育-->
            <if test="injuryFertility!=null and injuryFertility==1 ">
                and injury_fertility=1
            </if>
        </where>
        limit #{start},#{size}
    </select>

    <!--条件查询,查询总条数-->
    <select id="queryByCount" parameterType="Map" resultType="Long">
        select count(1) from rms_personnel
        <where>
            <!--名字-->
            <if test="name!=null and name!='' ">
                name like concat('%',#{name},'%')
            </if>
            <!--身份证号-->
            <if test="card!=null and card!='' ">
                and card=#{card}
            </if>
            <!-- 起薪时间-->
            <if test="startDate!=null and endDate!=null ">
                and starting_date between #{startDate} and #{endDate}
            </if>
            <!--工作状态-->
            <if test="state!=null and state!=0 ">
                and state =#{state}
            </if>
            <!--车辆补贴-->
            <if test="vehiclesSubsidies!=null and vehiclesSubsidies==1 ">
                and vehicles_subsidies=1
            </if>
            <!--供暖补贴-->
            <if test="heatingSubsidies!=null and heatingSubsidies==1 ">
                and heating_subsidies=1
            </if>
            <!--物业补贴-->
            <if test="propertySubsidies!=null and propertySubsidies==1 ">
                and property_subsidies=1
            </if>
            <!--医疗保险-->
            <if test="healthInsurance!=null and healthInsurance==1 ">
                and health_insurance=1
            </if>
            <!--养老保险-->
            <if test="endowmentInsurance!=null and endowmentInsurance==1 ">
                and endowment_insurance=1
            </if>
            <!--养老失业-->
            <if test="pensionUnemployment!=null and pensionUnemployment==1 ">
                and pension_unemployment=1
            </if>
            <!--职业年金-->
            <if test="occupationalPension!=null and occupationalPension==1 ">
                and occupational_pension=1
            </if>
            <!--工伤生育-->
            <if test="injuryFertility!=null and injuryFertility==1 ">
                and injury_fertility=1
            </if>
        </where>
    </select>
</mapper>