第四天——jsp练习：servlet连接数据库，浏览器显示数据

创建用户信息user，连接数据库，把mysql中数据存入user集合，使用request把user集合放入request域中，在jsp页面把request域中对象拿出来进行遍历，放入表格中。

根据sal工资高低进行职级划分

0-500     A

501-1000  B

1001-1500  C

1501-2000  D

2001-3000  E

3001-4000  F

4000+     G

public class user implements Serializable {
    private int empno;
    private String ename;
    private String job;
    private int mgr;
    private Date hiredate;
    private Double sal;
    private Double comm;
    private int deptno;

    public user() {
    }

    @Override
    public String toString() {
        return "user{" +
                "empno=" + empno +
                ", ename='" + ename + ''' +
                ", job='" + job + ''' +
                ", mgr=" + mgr +
                ", hiredate=" + hiredate +
                ", sal=" + sal +
                ", comm=" + comm +
                ", deptno=" + deptno +
                '}';
    }

    public user(int empno, String ename, String job, int mgr, Date hiredate, Double sal, Double comm, int deptno) {
        this.empno = empno;
        this.ename = ename;
        this.job = job;
        this.mgr = mgr;
        this.hiredate = hiredate;
        this.sal = sal;
        this.comm = comm;
        this.deptno = deptno;
    }

    public int getEmpno() {
        return empno;
    }

    public void setEmpno(int empno) {
        this.empno = empno;
    }

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public int getMgr() {
        return mgr;
    }

    public void setMgr(int mgr) {
        this.mgr = mgr;
    }

    public Date getHiredate() {
        return hiredate;
    }

    public void setHiredate(Date hiredate) {
        this.hiredate = hiredate;
    }

    public Double getSal() {
        return sal;
    }

    public void setSal(Double sal) {
        this.sal = sal;
    }

    public Double getComm() {
        return comm;
    }

    public void setComm(Double comm) {
        this.comm = comm;
    }

    public int getDeptno() {
        return deptno;
    }

    public void setDeptno(int deptno) {
        this.deptno = deptno;
    }
}

 private String url = "jdbc:mysql://localhost:3306/sqlwe?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
    private String user = "root";
    private String pwd = "root";
    @Override
    public List findall() {
        ResultSet resultSet=null;
        PreparedStatement psm=null;
        Connection connection=null;
        ArrayList list= new ArrayList<>();
        try {
            //加载、注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //获得连接
            connection= DriverManager.getConnection(url,user,pwd);
            psm = connection.prepareStatement("SELECt *from emp");
            resultSet = psm.executeQuery();
            while (resultSet.next()){
                Integer empno=resultSet.getInt("empno");
                Integer deptno=resultSet.getInt("deptno");
                Integer mgr=resultSet.getInt("mgr");
                String ename=resultSet.getString("ename");
                String job=resultSet.getString("job");
                Double sal=resultSet.getDouble("sal");
                Double comm=resultSet.getDouble("comm");
                Date hiredate=resultSet.getDate("hiredate");
                pojo.user user1 = new user(empno, ename, job, mgr, hiredate, sal, comm, deptno);
                list.add(user1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (null!=resultSet){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(null!=psm){
                try {
                    psm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (null!=connection){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
        return list;
    }

 protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        EmdaoImpl emdao = new EmdaoImpl();
        List users = emdao.findall();
        //把数据放入request对象中
        req.setAttribute("user",users);
        //forward转发
        req.getRequestDispatcher("show.jsp").forward(req,resp);
    }
}

%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ page import="pojo.user" %>
<%@ page import="java.util.List" %>
>


    
    


    
        <%
            List users = (List) request.getAttribute("user");
            for (user user1:users){%>
        
        <%}%>
    

        

            
编号
            
姓名
            
职务
            
上司
            
入职日期
            
工资
            
职级
            
奖金
            
部门
        

            
<%=user1.getEmpno()%>
            
<%=user1.getEname()%>
            
<%=user1.getJob()%>
            
<%=user1.getMgr()%>
            
<%=user1.getHiredate()%>
            
<%=user1.getSal()%>
            

            <%
                double sal=user1.getSal();
                if (sal>=4000){
                out.print("G");
            }else if (sal>=3001&sal<4000){
                out.print("F");
            }else if (sal>=2001&sal<3000){
                out.print("E");
            }else if (sal>=1501&sal<2000){
                out.print("D");
            }else if (sal>=1001&sal<1500){
                out.print("C");
            }else if (sal>=501&sal<1000){
                out.print("B");
            }else{
                    out.print("A");
            }

            %>
            
            
<%=user1.getComm()%>
            
<%=user1.getDeptno()%>