JDBC_WEB+条件查询

最新推荐文章于 2022-12-27 14:18:54 发布

大背景小人物

最新推荐文章于 2022-12-27 14:18:54 发布

阅读量251

点赞数

分类专栏： JDBC 文章标签： ajax jdbc java

本文链接：https://blog.csdn.net/IT_LD/article/details/107648625

版权

JDBC 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

本文介绍了一个使用JSP、Servlet和MySQL数据库实现的多条件模糊查询的工程。通过一系列的JSP页面、Servlet和DAO实现，用户可以在页面上进行条件输入，后台根据这些条件进行数据库查询，展示查询结果。

摘要由CSDN通过智能技术生成

需求：
JSP+Servlet+mysql实现页面多条件模糊查询
效果图
工程搭建：

index.jsp：

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
  <head>
    <title></title>
  </head>
  <body>
  <%--${pageContext.request.contextPath}当前工程的绝对路径--%>
  <form action="LoginServlet" method="post">
    <table>
      <tr>
        <td>
          账号: <input name="name" value="张三">
        </td>
      </tr>
      <tr>
        <td>
          密码: <input name="password" type="password" value="123">
        </td>
      </tr>
      <tr>
        <td>
          <input type="submit" value="登录">
        </td>
      </tr>
    </table>
  </form>
  </body>
</html>

showAll.jsp：

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
    <title>成功界面</title>
</head>
<body>
<!--method:作用用于区分servlet中 调用哪个方法-->
<form action="${pageContext.request.contextPath}/SearchServlet?method=search" method="post">
    <table>
        <tr>
            <td>
                姓名：<input type="text" name="name">
            </td>
            <td>
                所属部门：
                <select id="dept" name="id">
                    <option value="0">请选择</option>
                </select>
            </td>
            <td>
                搜索：<input type="submit" value="搜索">
            </td>
        </tr>
    </table>
</form>
<table id="tb" width="800px" border="1px" cellpadding="0px" cellspacing="0px">
    <tr>
        <td colspan="6"><a href="AddBeforeServlet">添加</a></td>
    </tr>
    <tr>
        <td align="center">编号</td>
        <td align="center">名称</td>
        <td align="center">性别</td>
        <td align="center">生日</td>
        <td align="center">所属部门</td>
        <td align="center">操作</td>
    </tr>
    <c:forEach items="${emps}" var="e" varStatus="index">
        <tr>
            <td align="center">${index.count}</td>
            <td align="center">${e.name}</td>
            <td align="center">${e.sex}</td>
            <td align="center">${e.birthday}</td>
            <td align="center">${e.dept.name}</td>
            <td align="center"><a href="javascript:del(${e.id})">删除</a>
                <a href="QueryOneServlet?id=${e.id}">编辑</a></td>
        </tr>
    </c:forEach>
</table>
</body>
</html>
<script type="application/javascript" src="${pageContext.request.contextPath}/js/jquery-3.1.0.min.js"></script>
<script type="application/javascript">
    //文件加载时，就执行该函数
    $(function () {
        $.ajax({
            url: 'SearchServlet?method=getDepts',//请求路径
            type: 'get',//请求方式get
            dataType: 'json',//请求后台后，后台返回的数据格式为json
            success: function (data) {//如果请求成功，后台json数据，data只是一个变量名
                //循环
                $(data).each(function (index, item) {//index表示索引值，item表示dept对象
                    //$("#dept")，拿到selected对象，append追加option
                    $("#dept").append("<option value='" + item.id + "'>" + item.name + "</option>")
                })
                //第二种循环方式
                //$(date,function (index,item) {})
            }
        })
    })

    //删除
    function del(id) {
        if (confirm("确认要删除此数据吗？")) {
            //发送请求到后台执行删除,window可忽略不写
            window.location.href = "${pageContext.request.contextPath}/DeleteServlet?id=" + id;
        }
    }
</script>

add.jsp：

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
    <title>添加界面</title>
</head>
<body>

    <form action="AddServlet" method="post">
        <table border="1px solid red" cellpadding="1px" cellspacing="1px" align="cecnter">
        <tr>
            <td>姓名：</td>
            <td><input type="text" name="name"></td>
        </tr>
        <tr>
            <td>密码：</td>
            <td><input type="text" name="password"></td>
        </tr>
        <tr>
            <td>生日：</td>
            <td><input type="text" name="birthday"></td>
        </tr>
        <tr>
            <td>性别：</td>
            <td>
                <input type="radio" name="sex" value="男" checked="checked">男
                <input type="radio" name="sex" value="女">女
            </td>
        </tr>
        <tr>
            <td>所在部门：</td>
            <td>
                <select name="did">
                    <c:forEach items="${requestScope.depts}" var="dept">
                        <option value="${dept.id}">${dept.name}</option>
                    </c:forEach>
                </select>
            </td>
        </tr>
        <tr>
            <td colspan="2" align="center"><input type="submit" value="添加"></td>
        </tr>
        </table>
    </form>

</body>
</html>

showOne.jsp：

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
    <title>修改界面</title>
</head>
<body>
<table border="1px solid red" cellpadding="1px" cellspacing="1px" align="cecnter">
    <form action="UpdateServlet" method="post">
        <tr>
            <td>编号：</td>
            <td><input type="text" name="id" value="${emp.id}" readonly="readonly"></td>
        </tr>
        <tr>
            <td>姓名：</td>
            <td><input type="text" name="name" value="${emp.name}"></td>
        </tr>
        <tr>
            <td>密码：</td>
            <td><input type="text" name="password" value="${emp.password}"></td>
        </tr>
        <tr>
            <td>生日：</td>
            <td><input type="text" name="birthday" value="${emp.birthday}"></td>
        </tr>
        <tr>
            <td>性别：</td>
            <td>
                <c:choose>
                    <c:when test="${emp.sex=='男'}">
                        <input type="radio" name="sex" value="男" checked="checked">男
                        <input type="radio" name="sex" value="女">女
                    </c:when>
                    <c:otherwise>
                        <input type="radio" name="sex" value="男">男
                        <input type="radio" name="sex" value="女" checked="checked">女
                    </c:otherwise>
                </c:choose>
                </td>
        </tr>
        <tr>
            <td>所在部门：</td>
            <td>
                <select name="did">
                    <c:forEach items="${list}" var="dept">
                        <option value="${dept.id}" <c:if test="${dept.name eq emp.dept.name}">selected</c:if>>${dept.name}</option>
                    </c:forEach>
                </select>
            </td>
        </tr>
        <tr>
            <td colspan="2" align="center"><input type="submit" value="修改"></td>
        </tr>
    </form>
</table>
</body>
</html>

LoginServlet：

@WebServlet("/LoginServlet")
public class LoginServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        doGet(request,response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");
        String name=request.getParameter("name");
        String password=request.getParameter("password");
        EmpService empService=new EmpServiceImpl();
        Emp emp = empService.login(name, password);
        //将登录人的信息放入session
        request.getSession().setAttribute("emp",emp);
        request.getRequestDispatcher("/QueryAllServlet").forward(request,response);
    }
}

QueryAllServlet：

@WebServlet("/QueryAllServlet")
public class QueryAllServlet extends HttpServlet {
    EmpService empService = new EmpServiceImpl();
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        doGet(request,response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        List<Emp> emps = empService.findEmps();
        request.setAttribute("emps",emps);
        System.out.println("集合数据::::"+emps);
       request.getRequestDispatcher("WEB-INF/jsp/showAll.jsp").forward(request,response);
    }
}

AddBeforeServlet：

@WebServlet("/AddBeforeServlet")
public class AddBeforeServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    doGet(request,response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");
        DeptService deptService=new DeptServiceImpl();
        List<Dept> depts = deptService.findDepts();
        request.setAttribute("depts",depts);
        request.getRequestDispatcher("WEB-INF/jsp/add.jsp").forward(request,response);
    }
}

AddServlet：

@WebServlet("/AddServlet")
public class AddServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    doGet(request,response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");
        EmpService empService=new EmpServiceImpl();
        String name = request.getParameter("name");
        String password = request.getParameter("password");
        String birthday = request.getParameter("birthday");
        String sex = request.getParameter("sex");
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
        Emp emp=new Emp();
        emp.setName(name);
        emp.setPassword(password);
        try {
            emp.setBirthday(sdf.parse(birthday));
        } catch (ParseException e) {
            e.printStackTrace();
        }
        emp.setSex(sex);
        Dept dept=new Dept();
        int did=Integer.valueOf(request.getParameter("did"));
        dept.setId(did);
        emp.setDept(dept);
        if (empService.add(emp)){
            request.getRequestDispatcher("QueryAllServlet").forward(request,response);
        }else {
            request.getRequestDispatcher("WEB-INF/jsp/error.jsp").forward(request,response);
        }
    }
}

DeleteServlet：

@WebServlet("/DeleteServlet")
public class DeleteServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request,response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        int id=Integer.valueOf(request.getParameter("id"));
        EmpService empService=new EmpServiceImpl();
        if (empService.delete(id)){
            request.getRequestDispatcher("QueryAllServlet").forward(request,response);
        }else {
            request.getRequestDispatcher("WEB-INF/jsp/error.jsp").forward(request,response);
        }
    }
}

UpdateServlet：

@WebServlet("/UpdateServlet")
public class UpdateServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    doGet(request,response);
    }
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");
        EmpService empService=new EmpServiceImpl();
        int id=Integer.valueOf(request.getParameter("id"));
        String name = request.getParameter("name");
        String password = request.getParameter("password");
        String birthday = request.getParameter("birthday");
        String sex = request.getParameter("sex");
        Dept dept=new Dept();
        int did=Integer.valueOf(request.getParameter("did"));
        dept.setId(did);
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");

        Emp emp=new Emp();
        emp.setId(id);
        emp.setName(name);
        emp.setPassword(password);
        try {
            emp.setBirthday(sdf.parse(birthday));
        } catch (ParseException e) {
            e.printStackTrace();
        }
        emp.setSex(sex);
        emp.setDept(dept);
        if (empService.update(emp)){
            request.getRequestDispatcher("QueryAllServlet").forward(request,response);
        }else {
            request.getRequestDispatcher("WEB-INF/jsp/error.jsp").forward(request,response);
        }
    }
}

SearchServlet：

@WebServlet("/SearchServlet")
public class SearchServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");
        String method = request.getParameter("method");
        if (method.equals("search")) {
            search(request, response);
        } else if (method.equals("getDepts")) {
            getDepts(request, response);
        }
    }
    private void search(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //接收两个参数name、id
        String name = request.getParameter("name");
        Integer id = Integer.valueOf(request.getParameter("id"));
        //调用业务层，查询
        EmpService empService = new EmpServiceImpl();
        List<Emp> emps = empService.findListWithCondition(name, id);
        //此处条件查询，返回到页面封装数据的key需要和之前插所有的key保持一致
        request.setAttribute("emps", emps);
        request.getRequestDispatcher("WEB-INF/jsp/showAll.jsp").forward(request, response);
    }
    /**
     * 后台获取部门json数据
     *
     * @param request
     * @param response
     */
    private void getDepts(HttpServletRequest request, HttpServletResponse response) {
        response.setCharacterEncoding("UTF-8");
        //1.调用业务层获取b部门列表
        DeptService deptService = new DeptServiceImpl();
        List<Dept> depts = deptService.findDepts();
        //2.将集合转化为json
        String json = JSON.toJSONString(depts);
        //3.将json写入客户端
        PrintWriter pw = null;
        try {
            pw = response.getWriter();
            pw.print(json);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            pw.close();
        }

    }
}

public interface EmpService {
    //登录
    Emp login(String name, String password);
    //查询所有员工
    List<Emp> findEmps();
    //根据id删除单个
    boolean delete(int id);
    //根据id查询单个对象
    Emp queryOne(int id);
    //修改
    boolean update(Emp emp);
    //添加
    boolean add(Emp emp);
    //条件查询
    List<Emp> findListWithCondition(String name, Integer id);
}

EmpService：

public interface EmpService {
    //登录
    Emp login(String name, String password);
    //查询所有员工
    List<Emp> findEmps();
    //根据id删除单个
    boolean delete(int id);
    //根据id查询单个对象
    Emp queryOne(int id);
    //修改
    boolean update(Emp emp);
    //添加
    boolean add(Emp emp);
    //条件查询
    List<Emp> findListWithCondition(String name, Integer id);
}

EmpServiceImpl：

public class EmpServiceImpl implements EmpService {
    EmpDao empDao=new EmpDapImpl();
    @Override
    public Emp login(String name, String password) {
        return empDao.login(name,password);
    }

    @Override
    public List<Emp> findEmps() {
        return empDao.findEmps();
    }

    @Override
    public boolean delete(int id) {
        if (empDao.delete(id)>0){
            return true;
        }
        return false;
    }

    @Override
    public Emp queryOne(int id) {
        return empDao.queryOne(id);
    }

    @Override
    public boolean update(Emp emp) {
        if(empDao.update(emp)>0){
            return true;
        }
        return false;
    }

    @Override
    public boolean add(Emp emp) {
        if(empDao.add(emp)>0){
            return true;
        }
        return false;
    }

    @Override
    public List<Emp> findListWithCondition(String name, Integer id) {
        return empDao.findListWithCondition(name,id);
    }
}

DeptService ：

public interface DeptService {
    //查询所有部门
    List<Dept> findDepts();
}

DeptServiceImpl ：

public class DeptServiceImpl implements DeptService {

    DeptDao deptDao= new DeptDaoImpl();

    @Override
    public List<Dept> findDepts() {
        return deptDao.findDpets();
    }
}

EmpDao：

public interface EmpDao {
    //登录
    Emp login(String name, String password);
    //查询所有员工
    List<Emp> findEmps();
    //根据id删除单个对象
    int delete(int id);
    //根据id查询单个对象
    Emp queryOne(int id);
    //修改
    int update(Emp emp);
    //添加
    int add(Emp emp);
    //条件查询
    List<Emp> findListWithCondition(String name, Integer id);
}

EmpDapImpl：

public class EmpDapImpl implements EmpDao {
    DbHelper helper = new DbHelper();

    @Override
    public Emp login(String name, String password) {

        Connection conn = helper.getConnection();
        Emp emp=new Emp();
        try {
            PreparedStatement ps = conn.prepareStatement("select * from emp where name=? and password=?");
            //给占位符赋值
            ps.setString(1,name);
            ps.setString(2,password);
            //执行sql
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                emp.setId(rs.getInt("id"));
                emp.setName(rs.getString("name"));
                emp.setSex(rs.getString("sex"));
                emp.setBirthday(rs.getDate("birthday"));
            }
            return emp;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Emp> findEmps() {
        Connection conn = helper.getConnection();
        List<Emp> emps = new ArrayList<>();

        try {
            PreparedStatement ps = conn.prepareStatement("select e.*,d.name dname from emp e , dept d where e.did = d.id");
            //执行sql
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                Emp emp = new Emp();
                emp.setId(rs.getInt("id"));
                emp.setName(rs.getString("name"));
                emp.setSex(rs.getString("sex"));
                emp.setBirthday(rs.getDate("birthday"));

                Dept d = new Dept();
                d.setName(rs.getString("dname"));
                emp.setDept(d);

                emps.add(emp);
            }
            return emps;

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int delete(int id) {
        Connection conn=helper.getConnection();
        try {
            PreparedStatement ps = conn.prepareStatement("delete from emp where id=?");
            ps.setInt(1,id);
            int i = ps.executeUpdate();
            return i;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return 0;
    }

    @Override
    public Emp queryOne(int id) {
        Connection conn=helper.getConnection();
        Emp emp=new Emp();
        try {
            PreparedStatement ps = conn.prepareStatement("select * from emp where id=?");
            ps.setInt(1,id);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                emp.setId(id);
                emp.setName(rs.getString("name"));
                emp.setPassword(rs.getString("password"));
                emp.setSex(rs.getString("sex"));
                emp.setBirthday(rs.getDate("birthday"));
            }
            return emp;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return null;
    }

    @Override
    public int update   (Emp emp) {
        Connection conn=helper.getConnection();
        try {
            PreparedStatement ps = conn.prepareStatement("update emp set name=?,password=?,birthday=?,sex=?,did=? where id=?");
            ps.setString(1,emp.getName());
            ps.setString(2,emp.getPassword());
            long time = emp.getBirthday().getTime();
            ps.setTimestamp(3,new Timestamp(time));
            ps.setString(4,emp.getSex());
            ps.setInt(5,emp.getDept().getId());
            ps.setInt(6,emp.getId());
            int i=ps.executeUpdate();
            return i;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return 0;
    }

    @Override
    public int add(Emp emp) {
        Connection conn=helper.getConnection();
        try {
            PreparedStatement ps = conn.prepareStatement("insert into emp( id,name, password, birthday, sex, did) values(null,?,?,?,?,?)");
            ps.setString(1,emp.getName());
            ps.setString(2,emp.getPassword());
            long time = emp.getBirthday().getTime();
            ps.setTimestamp(3,new Timestamp(time));
            ps.setString(4,emp.getSex());
            ps.setInt(5,emp.getDept().getId());
            int i=ps.executeUpdate();
            return i;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return 0;
    }

    @Override
    public List<Emp> findListWithCondition(String name, Integer id) {
        /**
         * 接收 2个参数  name  和id
         * 1. name为空 ,id是默认值 0 ,代表查询所有
         * sql=select e.*,d.name dname from emp e , dept d where e.did = d.id
         * 2. name不为空 , id=0 ,只按照名称模糊查询
         * sql=select e.*,d.name dname from emp e , dept d where e.did = d.id and e.name like ?
         * 3. name为空 , id>0 ,只按照部门查询
         * sql=select e.*,d.name dname from emp e , dept d where e.did = d.id and d.id=?
         * 4. 都不为空 ,组合查询
         * sql=select e.*,d.name dname from emp e , dept d where e.did = d.id and d.id=? and e.name like ?
         */
        if(StrUtil.isEmpty(name)&&id==0){
            Connection conn = helper.getConnection();
            List<Emp> emps = new ArrayList<>();
            try {
                PreparedStatement ps = conn.prepareStatement("select e.*,d.name dname from emp e , dept d where e.did = d.id");
                ResultSet rs = ps.executeQuery();
                while (rs.next()){
                    Emp emp = new Emp();
                    emp.setId(rs.getInt("id"));
                    emp.setName(rs.getString("name"));
                    emp.setSex(rs.getString("sex"));
                    emp.setBirthday(rs.getDate("birthday"));
                    Dept d = new Dept();
                    d.setName(rs.getString("dname"));
                    emp.setDept(d);
                    emps.add(emp);
                }
                return emps;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }else if(!StrUtil.isEmpty(name)&&id==0){
            Connection conn = helper.getConnection();
            List<Emp> emps = new ArrayList<>();
            try {
                PreparedStatement ps = conn.prepareStatement("select e.*,d.name dname from emp e , dept d where e.did = d.id and e.name like ?");
                ps.setString(1,"%"+name+"%");
                ResultSet rs = ps.executeQuery();
                while (rs.next()){
                    Emp emp = new Emp();
                    emp.setId(rs.getInt("id"));
                    emp.setName(rs.getString("name"));
                    emp.setSex(rs.getString("sex"));
                    emp.setBirthday(rs.getDate("birthday"));
                    Dept d = new Dept();
                    d.setId(rs.getInt("id"));
                    d.setName(rs.getString("dname"));
                    emp.setDept(d);
                    emps.add(emp);
                }
                return emps;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }else if(StrUtil.isEmpty(name)&&id>0){
            Connection conn = helper.getConnection();
            List<Emp> emps = new ArrayList<>();
            try {
                PreparedStatement ps = conn.prepareStatement("select e.*,d.name dname from emp e , dept d where e.did = d.id and d.id=?");
                ps.setInt(1,id);
                ResultSet rs = ps.executeQuery();
                while (rs.next()){
                    Emp emp = new Emp();
                    emp.setId(rs.getInt("id"));
                    emp.setName(rs.getString("name"));
                    emp.setSex(rs.getString("sex"));
                    emp.setBirthday(rs.getDate("birthday"));
                    Dept d = new Dept();
                    d.setId(rs.getInt("id"));
                    d.setName(rs.getString("dname"));
                    emp.setDept(d);
                    emps.add(emp);
                }
                return emps;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }else{
            Connection conn = helper.getConnection();
            List<Emp> emps = new ArrayList<>();
            try {
                PreparedStatement ps = conn.prepareStatement("select e.*,d.name dname from emp e , dept d where e.did = d.id and d.id=? and e.name like ?");
                ps.setInt(1,id);
                ps.setString(2,"%"+name+"%");
                ResultSet rs = ps.executeQuery();
                while (rs.next()){
                    Emp emp = new Emp();
                    emp.setId(rs.getInt("id"));
                    emp.setName(rs.getString("name"));
                    emp.setSex(rs.getString("sex"));
                    emp.setBirthday(rs.getDate("birthday"));
                    Dept d = new Dept();
                    d.setId(rs.getInt("id"));
                    d.setName(rs.getString("dname"));
                    emp.setDept(d);
                    emps.add(emp);
                }
                return emps;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }
}

DeptDao：

public interface DeptDao {
    //查询所有部门
    List<Dept> findDpets();
}

DeptDaoImpl：

public class DeptDaoImpl implements DeptDao {
    DbHelper helper = new DbHelper();
    @Override
    public List<Dept> findDpets() {
        Connection conn = helper.getConnection();
        List<Dept> depts = new ArrayList<>();
        try {
            PreparedStatement ps = conn.prepareStatement("select * from dept");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Dept d = new Dept();
                d.setId(rs.getInt("id"));
                d.setName(rs.getString("name"));
                depts.add(d);
            }
            return depts;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
}