7.20学习日志

前期准备

1.IDEA
2.Mysql5.7版本（每次开机需在服务关闭8.0版本mysql）、Navicat
3.安装Tomcat8.0.5

代码部分

目录

1.实体类User

package com.wl.bean;

public class User {
    public User() {
    }

    public User(int id, String username, String password) {
        this.id = id;
        this.username = username;
        this.password = password;
    }
    
    private  int id;
    private  String username;
    private  String password;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }


}

分为属性、get set、tostring、构造方法四块。
2.数据库方法UserDao

package com.wl.Dao;

import com.wl.bean.User;
import com.wl.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDao {
    public List<User> findAll() throws ClassNotFoundException, SQLException {

        List<User> userList = new ArrayList<>();
        Connection connection = DBUtil.getConnection();
        System.out.println("获得连接成功");
        String sql = "select * from tb_user";
        PreparedStatement statement = connection.prepareStatement(sql);
        ResultSet resultSet = statement.executeQuery();
        while(resultSet.next()){
            User user = new User();
            int id = resultSet.getInt(1);
            String username = resultSet.getString(2);
            String password = resultSet.getString(3);
            user.setId(id);
            user.setUsername(username);
            user.setPassword(password);
            userList.add(user);
        }
        DBUtil.close(resultSet,statement,connection);
        return userList;
    }
//    public boolean login(String username,String password) throws SQLException, ClassNotFoundException {
//        Connection connection=DBUtil.getConnection();
//        System.out.println("获得连接成功");
//        String sql = "select password from tb_user where username = ?";
//        PreparedStatement statement = connection.prepareStatement(sql);
//        statement.setString(2,username);
//        ResultSet resultSet = statement.executeQuery();
//        if(resultSet.getRow()!=0){
//            if()
//        }
//        return false;
//    }
    public void deleteById(int id) throws SQLException, ClassNotFoundException {
        Connection connection=DBUtil.getConnection();
        System.out.println("获得连接成功");
        String sql = "delete from tb_user where id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,id);
        statement.executeUpdate();
        DBUtil.close(null,statement,connection);
    }
}

用于存放数据库方法
3.UserServlet

package com.wl.servlet;
import com.wl.Dao.UserDao;
import com.wl.bean.User;

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.sql.SQLException;
import java.util.List;

public class UserServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//        resp.getWriter().write("chenggong");
        UserDao userDao = new UserDao();
        List<User>all = null;
        try {
            all = userDao.findAll();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        resp.getWriter().write(all.toString());
    }

}

4.DBUtil数据库连接关闭

package com.wl.util;

import java.sql.*;

public class DBUtil {
    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/whlg","root","123456");
        System.out.println("DBUtil获得连接成功");
        return connection;
    }
    public static void close(ResultSet resultSet,Statement statement,Connection connection) throws SQLException {
        if(resultSet!=null) {
            resultSet.close();
        }
        if(statement!=null) {
            statement.close();
        }
        if(connection!=null){
            connection.close();
        }
    }

}

控制数据库连接和关闭
5.测试类Test

package com.wl;

import com.wl.Dao.UserDao;
import com.wl.bean.User;

import java.sql.SQLException;
import java.util.List;

public class Test {
    public static  void main(String[] args) throws SQLException, ClassNotFoundException {
        UserDao userDao=new UserDao();
        List<User> userList = userDao.findAll();
        System.out.println(userList);
    }
}

测试代码能否运行
6.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>hello</servlet-name>
        <servlet-class>com.wl.servlet.UserServlet</servlet-class>
    </servlet>
    <servlet>
        <servlet-name>login</servlet-name>
        <servlet-class>com.wl.servlet.UserServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>hello</servlet-name>
        <url-pattern>/hello</url-pattern>
    </servlet-mapping>
    <servlet-mapping>
    <servlet-name>login</servlet-name>
    <url-pattern>/login</url-pattern>
    </servlet-mapping>
</web-app>

7.主页面index.jsp

<%--
  Created by IntelliJ IDEA.
  User: 98154
  Date: 2020/7/20
  Time: 9:18
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
  <head>
    <title>$Title$</title>
  </head>
  <body>
  <a href="/hello">查询所有用户</a>
  <form action="/login">
    username:<br>
    <input type="text" name="username" >
    <br>
    password:<br>
    <input type="text" name="password" >
    <br><br>
    <input type="submit" value="Login">
  </form>
  </body>
</html>

错误及解决

错误：
测试类正常运行代码，可是网页无法连接数据库。

解决方法：

将项目libs中mysql-connector-java转移至tomcat的lib中，最终在网页成功查询所有用户。

工具分享

网页设计网站