【JavaWeb】练习一

1>结合jdbc的知识实现登录
2>登录成功后转入数据展示页面
3>在展示列表中实现删除功能

bean下的Employees和User

public class Employees {
    private  int eno;
    private String ename;
    private String egender;
    private int deptno;
    private String ejob;
    private int esalary;
    private Date hireDate;

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

    public String getEname() {
        return ename;
    }

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

    public String getEgender() {
        return egender;
    }

    public void setEgender(String egender) {
        this.egender = egender;
    }

    public int getDeptno() {
        return deptno;
    }

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

    public String getEjob() {
        return ejob;
    }

    public void setEjob(String ejob) {
        this.ejob = ejob;
    }

    public int getEsalary() {
        return esalary;
    }

    public void setEsalary(int esalary) {
        this.esalary = esalary;
    }

    public Date getHireDate() {
        return hireDate;
    }

    public void setHireDate(Date hireDate) {
        this.hireDate = hireDate;
    }
}

public class User {
    private Integer uid;
    private String uname;
    private String upwd;

    public Integer getUid() {
        return uid;
    }

    public void setUid(Integer uid) {
        this.uid = uid;
    }

    public String getUname() {
        return uname;
    }

    public void setUname(String uname) {
        this.uname = uname;
    }

    public String getUpwd() {
        return upwd;
    }

    public void setUpwd(String upwd) {
        this.upwd = upwd;
    }
}

Dao下的Employess和User接口

public interface EmployeesDao {
    public List<Employees> findAll();

    public Employees findById(String  id);

    public Integer delect(String id);
    
}

public interface UserDao {
    public User find(String name, String pwd);
}

实现类

public class EmployeesDaoImpl implements EmployeesDao {
    JDBCUntils jdbcUntils = new JDBCUntils();

    @Override
    public List<Employees> findAll() {
        return  jdbcUntils.executeQuery(Employees.class,"select * from Emploeey");
    }

    public static void main(String[] args) {
        EmployeesDao employeesDao =new EmployeesDaoImpl();
        List<Employees> employees = employeesDao.findAll();
        System.out.println(employees.toString());
    }
    @Override
    public Employees findById(String  id) {
        return jdbcUntils.findOneInstance(Employees.class,"select * from Emploeey where eno = ?",id);
    }

    @Override
    public Integer delect(String id) {

        return jdbcUntils.executeUpdate("DELETE FROM emploeey WHERE eno=?", id);
    }
}

public class UserDaoImpl implements UserDao {
    JDBCUntils jdbcUtils = new JDBCUntils();

    @Override
    public User find(String username, String password) {
        return jdbcUtils. findOneInstance(User.class,"select * from users where uname = ? and upwd = ?",username,password);
    }

    public static void main(String[] args) {
        UserDao usersDao = new UserDaoImpl();
        User user = usersDao.find("张三","123456");
        System.out.println(user);
    }
}

Untils下的JDBCUntils类

public class JDBCUntils {
    // 1. 定义一个成员变量DataSource
    public Connection getConnection()throws  Exception{
        Properties properties = new Properties();
        InputStream inputStream = JDBCUntils.class.getClassLoader().getResourceAsStream("Druid.properties");
        properties.load(inputStream);
        DataSource dataSource =  DruidDataSourceFactory.createDataSource(properties);
        Connection connection =  dataSource.getConnection();

        return  connection;
    }

    public int executeUpdate(String sql,Object...params){
        Connection connection = null;
        PreparedStatement pstmt = null;
        try {
            connection =getConnection();
            pstmt =   connection.prepareStatement(sql);
            setParams(pstmt,params);
            int rows =    pstmt.executeUpdate();
            return rows;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(connection,pstmt,null);
        }
        return -1;
    }

    public  Object findOneVal( String sql, Object...params){
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs  = null;
        Object val = null;
        try {
            connection = getConnection();
            pstmt =  connection.prepareStatement(sql);
            setParams(pstmt,params);
            rs =  pstmt.executeQuery();
            if(rs.next()){
                val =   rs.getObject(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(connection,pstmt,rs);
        }
        return  val;
    }
    private  void setParams(PreparedStatement pstmt ,Object...params){
        try {
            if(params!=null&&params.length>0){
                for(int i=0;i<params.length;i++){
                    pstmt.setObject(i+1,params[i]);
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public <T> T findOneInstance(Class<T> clazz, String sql, Object...params){
        List<T> list =   executeQuery(clazz,sql,params);
        if(list!=null&&list.size()>0){
            return  list.get(0);
        }else{
            return  null;
        }
    }
    public  <T> List<T> executeQuery(Class<T> clazz, String sql, Object...params){
        List<T> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs =null;
        JDBCUntils  jdbcUtils = new JDBCUntils();
        try {
            connection = jdbcUtils.getConnection();
            pstmt = connection.prepareStatement(sql);
            setParams(pstmt,params);
            //  pstmt.setString(1,"zs");
            rs =   pstmt.executeQuery();
            while (rs.next()){
                T obj =   clazz.newInstance();
                list.add(obj);
                ResultSetMetaData rsmd =  rs.getMetaData();
                int colcount =  rsmd.getColumnCount();
                for(int i=1;i<=colcount ;i++){
                    String label =  rsmd.getColumnLabel(i);//表中的查询结构有_ 按照驼峰命名规则处理
                    Object val =   rs.getObject(label);
                    String type =  rsmd.getColumnClassName(i);
                    label = change(label);
                    Field field = clazz.getDeclaredField(label);//获取声明的属性
                    field.setAccessible(true);
                    if(type.contains("Date")){
                        Class type2 =   field.getType();
                        if(type2.toString().contains("Date")){
                            field.set(obj,val);
                        }else{
                            //日期类型转换城字符串
                            if(null!=val){
                                Date date = (Date)val;
                                field.set(obj,date.toString());
                            }
                        }
                    }else{
                        field.set(obj,val);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtils.close(connection,pstmt,rs);
        }
        return  list;
    }
    private   static  String change(String label){
        String newlabel = label;
        int index =label.indexOf("_") ;
        if(index!=-1){//说明检测到_
            newlabel = label.substring(0,index)+ label.substring(index+1,index+2).toUpperCase()+ label.substring(index+2).toLowerCase();
        }

        return newlabel;
    }


    public int save(String sql,Object...params){
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int id = -1;
        try {
            connection =getConnection();
            pstmt =   connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            setParams(pstmt,params);
            pstmt.executeUpdate();
            rs =   pstmt.getGeneratedKeys();
            if(rs.next()){
                Object obj =   rs.getObject(1);
                id = Integer.parseInt(obj.toString());
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(connection,pstmt,rs);
        }
        return id;
    }


    public  void close(Connection connection , Statement stmt, ResultSet rs){
        try {
            if(rs!=null){
                rs.close();
            }
            if(stmt!=null){
                stmt.close();
            }
            if(connection!=null){
                connection.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

}```

src下的Druid.properties

```java
driverClassName = com.mysql.cj.jdbc.Driver
url = jdbc:mysql:///db0517?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true&allowPublicKeyRetrieval=true
username = root
password = 200010191992+
initialSize = 5
maxActive = 10
maxWait = 3000

web 下的jsp

login.jsp

    <form action="dologin.jsp" method="post">
        <P>
            用户名:<input type="text" name="username"/>
        </P>
        <P>
            密码: <input type="password" name="pwd"/>
        </P>
        <P>
            <input type="submit" value="登录"/>
        </P>
    </form>

dologin.jsp

 <%
        request.setCharacterEncoding("UTF-8");
        String name = request.getParameter("username");
        String pwd  = request.getParameter("pwd");
        UserDao userDao = new UserDaoImpl();
        User u = userDao.find(name,pwd);
        if (u != null) {
            response.sendRedirect("form.jsp");
        }else {
            response.sendRedirect("login.jsp");
        }
    %>

 <%
        EmployeesDao employeesDao = new EmployeesDaoImpl();
        List<Employees> list = employeesDao.findAll();
    %>
    <table width="780" border="1" class="table">
        <tr>
            <td>员工编号</td>
            <td>员工姓名</td>
            <td>员工性别</td>
            <td>部门编号</td>
            <td>员工职位</td>
            <td>员工工资</td>
            <td>入职日期</td>
        </tr>
        <%
            for(Employees e:list) {
        %>
        <tr>
            <td><%=e.getEno()%></td>
            <td><%=e.getEname()%></td>
            <<td><%=e.getEgender()%></td>
            <td><%=e.getDeptno()%></td>
            <td><%=e.getEjob()%></td>
            <td><%=e.getEsalary()%></td>
            <td><%=e.getHireDate()%></td>
            <td><a href="delect.jsp?id=<%=e.getEno()%>">删除</a> </td>
        </tr>
        <%
            }
        %>
    </table>

<%
    EmployeesDao employeesDao=new EmployeesDaoImpl();
    String id = request.getParameter("id");
    employeesDao.delect(id);
    response.sendRedirect("form.jsp");
%>

效果展示

删除效果