本项目使用 jstl 实现, 执行数据库的单表 Emp的查询全部

要求:  用 封装 继承 多态的方式 ,实现该功能

分层开发:
entity   实体层
service  业务层接口及实现类
util  工具类

本项目采用 maven 方式创建, 在pom.xml 中需要引入

增加依赖

<dependencies>
        <!--  jsp servlet   -->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>servlet-api</artifactId>
            <version>2.5</version>
        </dependency>
        <!-- jsp -->
        <dependency>
            <groupId>jstl</groupId>
            <artifactId>jstl</artifactId>
            <version>1.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.30</version>
        </dependency>

    </dependencies>

在地址栏 输入 http://localhost:9999/list 返回全部数据

实体层

package entity;

import java.util.Date;

public class Emp {

    private int eno;
    private String name;
    private String phone;
    private Date bornDate;
    private int deptId;





    public int getDeptId() {
        return deptId;
    }

    public void setDeptId(int deptId) {
        this.deptId = deptId;
    }

    public Emp() {
    }

    public Emp(int eno, String name, String phone, Date bornDate,int deptId) {
        this.eno = eno;
        this.name = name;
        this.phone = phone;
        this.bornDate = bornDate;
        this.deptId = deptId;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "eno=" + eno +
                ", name='" + name + '\'' +
                ", phone='" + phone + '\'' +
                ", bornDate=" + bornDate +
                '}';
    }

    public int getEno() {
        return eno;
    }

    public void setEno(int eno) {
        this.eno = eno;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public Date getBornDate() {
        return bornDate;
    }

    public void setBornDate(Date bornDate) {
        this.bornDate = bornDate;
    }
}

工具类

package util;

import java.sql.*;

/**
 * 数据库连接的工具类
 */
public class DBUtils {

    // 数据库连接的4个属性
    public static final String USERNAME= "用户名"; //用户名
    public static final String PASSWORD = "密码";// 密码
    public static final String URL="jdbc:mysql://localhost:3306/数据库";// 数据库的连接地址
    public static final String DRIVER = "com.mysql.cj.jdbc.Driver";  //   注意 8-5.5 写法不同

    /**
     * 获得数据库的连接对象Connection
     * @return  Connection
     */
    public Connection getConn(){
        Connection connection = null;

        try{
            if(connection==null || connection.isClosed()){
                Class.forName(DRIVER); // 加载驱动
                connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
            }
        }catch (Exception e){
            System.out.println("-----数据库连接失败----"+e.getMessage());
        }
        return connection;
    }

    /**
     * 关闭所有的 连接对象
     * @param rs
     * @param st
     * @param conn
     */
    public void closeAll(ResultSet rs, Statement st,Connection conn){
        try{
            // 关闭连接
            if(rs !=null){
                rs.close();
            }
            if(st!=null){
                st.close();
            }
            if(conn !=null){
                conn.close(); //关闭连接
            }
        }catch (Exception e){
            System.out.println("--------关闭失败----"+e.getMessage());
        }


    }


    // insert delete update  均返回 受影响的行数

    /**
     * 增加, 删除, 修改 均需要调用本方法,
     * @param sql   执行 sql
     * @param params  sql 需要拼接的参数
     * @return    受影响的行数,  如果 行数>=1 表示 sql执行成功, 否则 就是执行 失败
     */
    public int execute(String sql,Object[] params){
        //获得数据库的连接
        Connection conn = this.getConn();
        PreparedStatement st = null;
        ResultSet rs = null;
        try{
            st =  conn.prepareStatement(sql);
            //组装sql
            for(int i =0;i< params.length;i++){
                  st.setObject(i+1,params[i]);
            }
            //返回受影响的行数
            return st.executeUpdate();

        }catch (Exception e){

            System.out.println("-----执行失败-----"+e.getMessage());
        }finally {
            this.closeAll(rs,st,conn);
        }

        return 0;
    }



}

业务层  接口及实现类

package service;

import entity.Emp;

import java.util.List;

public interface IEmpService {

    /**
     * 查询全部
     * @return
     */
    List<Emp> findAll();


}

package service.impl;

import entity.Emp;
import service.IEmpService;
import util.DBUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class EmpServiceImpl implements IEmpService {

    @Override
    public List<Emp> findAll() {
        List<Emp> list = new ArrayList<>();
        String sql = " select * from emp "; //sql
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        DBUtils db = new DBUtils();
        try{
            conn = db.getConn(); //获得连接
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            while(rs.next()){
                int eno = rs.getInt("eno");
                String name = rs.getString("ename");
                String phone = rs.getString("phone");
                Date date = rs.getDate("borndate");
                int deptId = rs.getInt("deptid");
                list.add(new Emp(eno,name,phone,date,deptId));
            }
            return list;
        }catch (Exception e){
            System.out.println("异常信息为"+ e.getMessage());
        }finally {
            db.closeAll(rs,st,conn);
        }
        return null;
    }
}

控制层-servlet

package control;

import entity.Emp;
import service.IEmpService;
import service.impl.EmpServiceImpl;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

public class QueryAllServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

        // 执行查询全部员工信息
        IEmpService service = new EmpServiceImpl();
        List<Emp> list = service.findAll();

        req.setAttribute("empList",list); // 设置 属性,
        //跳转页面
        req.getRequestDispatcher("list.jsp").forward(req,resp);

    }
}

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
         version="4.0">



    <servlet>
        <servlet-name>QueryAllServlet</servlet-name>
        <servlet-class>control.QueryAllServlet</servlet-class>
    </servlet>

    <servlet-mapping>
        <servlet-name>QueryAllServlet</servlet-name>
        <url-pattern>/list</url-pattern>
    </servlet-mapping>

</web-app>

list.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>

   <h1>员工信息列表</h1>

   <c:forEach items="${empList}" var="emp">
       <div>
           ${emp.eno} ----${emp.name}----${emp.phone}--${emp.deptId}----${emp.bornDate}
       </div>
   </c:forEach>

</body>
</html>