使用LayUi+Servlet+JDBC开发员工管理

---

前言

本篇主要讲述，一个员工管理模块的开发

---

一、准备工作

首先需要准备数据库文件，我们需要两张表，一张员工表employee，一张部门表department。员工和部门是多对一的关系，即，多个员工对应一个部门。一个员工只有一个部门。
字段设计如下：
employee
apartment
两个表之间，通过部门编号dept_no实现外键关联。
然后创建web项目，结构如下：

导入相关jar包和静态资源。

导入layui包

二、编写代码

1.登陆实现

此处我直接在网上找了一个登陆页面。
大概是下图这样：

首先编写页面的验证处理

//登陆
var JsonData = {
		username: uname,
		password: pwd
	};
	$.post("login?method=loginVerify",JsonData,function(res) {
		$d = JSON.parse(res);
		if($d.code == 0){
			layer.msg($d.msg, {
				icon: 1,
				time: 1000 //1秒关闭（如果不配置，默认是3秒）
			}, function(){
				window.sessionStorage.setItem('username',uname);
				window.location.href='managePage/index.html';
			});
		}else{
			layer.msg($d.msg,{
				icon: 2,
				time: 1000 //2秒关闭（如果不配置，默认是3秒）
			});
		}
	});

编写后端Servlet方法，接收Ajax的异步请求

@WebServlet("/login")
public class LoginServlet extends BaseServlet {
    private LoginServiceImpl service =  new LoginServiceImpl();


    public void loginVerify(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //接收数据，进行判断是否登陆成功！
        HttpSession session = req.getSession();
        String username =  req.getParameter("username");
        String password = req.getParameter("password");
        int key = service.verifyLogin(username,password);
        //设置输出流
        PrintWriter out = resp.getWriter();
        if (key > 0){//登陆成功
            //设置session值
            session.setAttribute("loginFlag",true);
            out.write(Result.toClient(0,"登陆成功！"));
        }else {
            out.write(Result.toClient(1,"登陆失败，用户名或密码错误！"));
        }
        //关流
        out.flush();
        out.close();
    }
    public void loginOut(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

        HttpSession session = req.getSession();
        //销毁登陆状态
        session.invalidate();
    }
}

编写service业务层接口，编写处理servlet调用的方法

package com.lzl.service;

/**
 * --效率，是成功的核心关键--
 * --自负，会让每个人都屈膝下跪--
 *
 * @Author lzl
 * @Date 2022/9/12 09:59
 */

public interface LoginService {
    /**
     *@Description 业务层登陆验证
     *@Param username,password
     *@Return 受影响行数
     */
    Integer verifyLogin(String username,String password);
}

编写业务实现类

/**
 * --效率，是成功的核心关键--
 * --自负，会让每个人都屈膝下跪--
 *
 * @Author lzl
 * @Date 2022/9/12 10:01
 */

public class LoginServiceImpl implements LoginService {
    private UserDao impl = new UserDaoImpl();
    /**
     * @param username
     * @param password
     * @Description 业务层登陆验证
     * @Param username, password
     * @Return 受影响行数
     */
    @Override
    public Integer verifyLogin(String username, String password) {
        return impl.getLogin(username,password);
    }
}

编写Dao接口，及其实现方法

package com.lzl.dao;

public interface UserDao {
    int getLogin(String username, String password);
}

public class UserDaoImpl implements UserDao {
    //获得连接
    private Connection conn = null;
    //预编译处理对象
    private PreparedStatement pst = null;
    //结果集
    private ResultSet rts = null;
    @Override
    public int getLogin(String username, String password) {
        int key = 0;
        try {
            conn = DBUtil.getConnection();
            String sql  = "select * from user where username = ?";
            pst = conn.prepareStatement(sql);
            pst.setString(1,username);
            rts = pst.executeQuery();
            User user = null;
            if(rts.next()){
                user = new User();
                user.setUsername(rts.getString("username"));
                user.setPassword(rts.getString("password"));
                if (user.getPassword().equals(password)){
                    key = 1;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(rts,pst,conn);
        }
        return key;
    }
  
}

至此，登陆功能实现完毕！

接下来查询所有员工，首先先编写Daoimpl

 //获得连接
    private Connection conn = null;
    //预编译处理对象
    private PreparedStatement pst = null;
    //结果集
    private ResultSet rts = null;
    // 参数集合params
    private ArrayList<Object> params = null;
    /**
     * 获得所有的员工数据
     * @return list员工集合
     */
    @Override
    public List<Employee> getAll(Map<String, Object> keys) {
        List<Employee> list = new ArrayList<>();
        // 查询所需参数的集合
        params = new ArrayList<>();
        //设置sql where 1 = 1 确保sql一定可以执行
        String sql = "select e.*,d.dept_name from employee e " +
                "inner join department d on e.emp_dept_no = d.dept_no where 1 = 1";
        //拼接字符串
        if(!StringUtils.isNull(keys.get("emp_name"))){
            sql += " and e.emp_name like ?";
            params.add("%" + keys.get("emp_name") + "%");
        }
        if(!StringUtils.isNull(keys.get("dept_no"))){
            sql += " and e.emp_dept_no = ?";
            params.add(keys.get("dept_no"));
        }
        if(!StringUtils.isNull(keys.get("emp_status"))){
            sql += " and e.status = ?";
            params.add(keys.get("emp_status"));
        }
        if(!StringUtils.isNull(keys.get("beginTime"))){
            sql += " and e.emp_hire_time > ?";
            params.add(keys.get("beginTime"));
        }
        if(!StringUtils.isNull(keys.get("endTime"))){
            sql += " and e.emp_hire_time  < ?";
            params.add(keys.get("endTime"));
        }
        sql += " order by emp_no limit ?,?";
        Integer page = (Integer) keys.get("page");
        Integer limit = (Integer) keys.get("limit");
        params.add((page - 1) * limit);
        params.add(limit);
        try {
            conn = DBUtil.getConnection();
            pst = conn.prepareStatement(sql);
            // 设置参数
            for (int i = 0; i < params.size(); i++) {
                pst.setObject(i + 1, params.get(i));
            }
            rts = pst.executeQuery();
            while (rts.next()){
                Employee employee = new Employee();
                employee.setEmp_no(rts.getInt("emp_no"));
                employee.setEmp_name(rts.getString("emp_name"));
                employee.setEmp_gender(rts.getString("emp_gender"));
                employee.setEmp_age(rts.getInt("emp_age"));
                int emp_dept_no = rts.getInt("emp_dept_no");
                employee.setEmp_dept_no(emp_dept_no);
                employee.setEmp_salary(rts.getDouble("emp_salary"));
                employee.setEmp_hire_time(rts.getString("emp_hire_time"));
                employee.setEmp_position(rts.getString("emp_position"));
                employee.setEmp_status(rts.getInt("emp_status"));
                String dept_name = rts.getString("dept_name");
                Department department = new Department(emp_dept_no,dept_name);
                employee.setDepartment(department);
                list.add(employee);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(rts,pst,conn);
        }
        return list;
    }

编写service层方法

 private EmployeeDao dao = new EmployeeDaoImpl();
    /**
     * 获得所有的员工
     *
     * @return 符合前端解析的json数据 code，msg，count，data
     */
    @Override
    public ResultLayUi<Employee> getAllEmployee(Map<String, Object> keys) {
        List<Employee> list = dao.getAll(keys);
        int count = dao.getCount(keys);
        ResultLayUi<Employee> resultLayUi = new ResultLayUi<>();
        if (list.size() > 0){
           resultLayUi.setCode(0);
           resultLayUi.setMsg("查询成功！");
           resultLayUi.setCount(count);
           resultLayUi.setData(list);
        }else {
            resultLayUi.setCode(1);
            resultLayUi.setMsg("没有数据！");
        }
        return resultLayUi;
    }

编写servlet层

 private EmployeeService service = new EmployeeServiceImpl();

    /**
     * 获得所有数据
     * @param req
     * @param resp
     * @throws ServletException
     * @throws IOException
     */
    public void getAll(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //多条件查询要传参
        Map<String, Object> keys = new HashMap<>();
        //设置分页
        Integer page = req.getParameter("page") == null ? 1 : Integer.parseInt(req.getParameter("page"));
        Integer limit = req.getParameter("limit") == null ? 10 : Integer.parseInt(req.getParameter("limit"));
        keys.put("page", page);
        keys.put("limit", limit);
        String emp_name = req.getParameter("emp_name");
        String dept_no = req.getParameter("dept_no");
        String emp_position = req.getParameter("emp_position");
        String emp_status = req.getParameter("emp_status");
        String beginTime = req.getParameter("begin");
        String endTime = req.getParameter("end");
        //装进容器
        keys.put("emp_name",emp_name);
        keys.put("dept_no",dept_no);
        keys.put("emp_position",emp_position);
        keys.put("emp_status",emp_status);
        keys.put("beginTime",beginTime);
        keys.put("endTime",endTime);
        //设置输出流
        PrintWriter out = resp.getWriter();
        //获得数据
        ResultLayUi<Employee> layUi = service.getAllEmployee(keys);
        //向页面输出
        out.write(Result.toClient(
                layUi.getCode(),layUi.getMsg(),
                layUi.getCount(),layUi.getData()));
        out.flush();
        out.close();
    }

编写前端

<div class="layui-card" style="margin-left: 10px; margin-top: 3px; padding-top: 10px; margin-right: 10px; margin-bottom: 0px; ">
  <!-- 头部-->
  <div class="layui-card-header">
<!-- 顶部工具栏 -->
      <div class="layui-form-item"  id="topBar">
        <form class="layui-form" onsubmit="return false;">
          <label class="layui-form-label">姓名：</label>
          <input type="text" id="emp_name" name="emp_name" style="width:150px;" class="layui-input layui-input-inline"/>
          <label class="layui-form-label">部门：</label>
          <div class="layui-input-inline" style="width:100px;">
            <select id="dept_no" class="option">
              <option value=""></option>
            </select>
          </div>
          <label class="layui-form-label">状态：</label>
          <div class="layui-input-inline" style="width:100px;">
            <select id="emp_status" >
              <option value="">--全部--</option>
              <option value="0">在职</option>
              <option value="1">离职</option>
            </select>
          </div>
          <label class="layui-form-label">入职时间：</label>
          <input type="text" id="begin" name="begin" style="width:100px;" class="layui-input layui-input-inline"/>
          <label class="layui-form-label" style="width: 20px; text-align: center">至</label>
          <input type="text" id="end" name="end" style="width:100px;" class="layui-input layui-input-inline"/>

          <!--操作按钮 -->
          <label class="layui-form-label" style="width: 20px;"></label>
          <button class="layui-btn layui-btn-sm " id="search"><i class="layui-icon">&#xe615;</i></button>
        </form>
      </div>
  </div>
  <!-- 中部-->
  <div class="layui-card-body" style="margin-top: 0px;padding-top: 0px; height: 528px">
    <!-- 表格工具栏 -->
    <script type="text/html" id="topTool">
      <button type="button" class="layui-btn layui-btn-xs" lay-event="add" >
        <i class="layui-icon" >&#xe61f;</i>添加
      </button>
      <button class="layui-btn layui-btn-xs layui-btn-danger"  lay-event="delAll">
        <i class="layui-icon">&#xe640;</i>批量删除
      </button>
      <button class="layui-btn layui-btn-xs layui-btn-normal"  lay-event="put">
        <i class="layui-icon">&#xe609;</i>入职办理
      </button>
    </script>
    <!-- 行内工具栏 -->
    <script type="text/html" id="barDemo">
      <button class="layui-btn layui-btn-xs layui-bg-orange" lay-event="edit" id="upbtn"><i class="layui-icon">&#xe642;</i></button>
      <button class="layui-btn layui-btn-xs layui-btn-danger" lay-event="delete" id="dlbtn"><i class="layui-icon">&#xe640;</i></button>
    </script>
    <!-- 数据表格 -->
    <table  class="layui-table" style="margin-top: 0px;" id="tableDemo" lay-filter="table"></table>
  </div>

<script type="text/javascript" src="../lib/layui-v2.6.3/layui.js" ></script>
<script src="/static/js/jquery-1.12.4.min.js"></script>
<script type="text/javascript">
  layui.use(['table','layer','form','laydate','transfer'],function(){
    var table = layui.table;
    var layer = layui.layer;
    var form = layui.form;
    var laydate = layui.laydate;
    var transfer = layui.transfer;
    var $ = layui.$;
    //渲染时间
    laydate.render({
      elem:'#begin',
    });
    laydate.render({
      elem:'#end',
    });
    laydate.render({
      elem:'#year2',
    });
    //渲染下拉框
    $.post("/employee?method=getDept",{},function(res){
      //遍历集合
      $.each(res.data,function(index,item){
        //<option value="item.cid">item.cName</option>
        $(".option").append(new Option(item.dept_name,item.dept_no));
      });
      //渲染layui下拉单
      layui.form.render();
    },"json");
    //表格渲染
    table.render({
      elem: '#tableDemo',//指定原始表格元素选择器（推荐id选择器）
      even: true,
      page: true,
      method:'post',
      width: 1250,
      toolbar: '#topTool',
      url:'/employee?method=getAll',
      cols:[[
        {type: 'checkbox', fixed: 'left'},
        {fixed:'left',type:'numbers', title:'编号'},
        {field:'emp_name', title:'姓名',width:100,align:'center'},
        {field:'emp_gender', title:'性别',width:80,align:'center'},
        {field:'emp_age', title:'年龄',width:80,align:'center'},
        {templet : '<span>{{d.department.dept_name}}</span>', title:'部门',align:'center'},
        {field:'emp_salary', title:'薪资',align:'center'},
        {field:'emp_hire_time', title:'入职时间',align:'center'},
        {field:'emp_position', title:'职位',align:'center'},
        {templet : '#status',title:'状态',align:'center'},
        {fixed: 'right', title:'操作', width:100, align:'center', toolbar: '#barDemo'}
      ]],
      id:"retable",
    });

    //重载函数
    active = {
          reload : function(){
              table.reload('retable',{
                  where:{
                      "emp_name" : $("#emp_name").val(), //名字
                      "dept_no" : $("#dept_no").val(), //部门
                      "emp_status" : $("#emp_status").val(), //状态
                      "begin" : $("#begin").val(), //开始年份
                      "end" : $("#end").val(), //结束年份
                  }
                  ,page: {
                      curr: $(".layui-laypage-em").next().html() //重新从第 1 页开始
                  }
              });
          }
      }

    //多条件查询
    $("#search").click(function(){
      active.reload();
    });

至此查询所有和多条件查询已经完成
最后贴一张完成的效果图