每周总结二

这一周，重写了一下学生管理系统，总算是用HTML+MySQL+servlet以及Java解决了基本的操作。

在这次写程序过程中，还遇到不少问题。

首先，我的数据库连接居然出毛病了，以前的时候，连接数据库是没问题的，后来看视频又学了一个新的方法，前几天写的时候，用新方法，一直在报错，说我的dbutil有问题，但验证时确实是连接上了，找了半天也没找出方法解决这个毛病，我又换回老方法了。

之后，写完插入方法，进行实验的时候，中文插入后一直是乱码，我看了数据库的字段，又看dao方法，找了半天问题，后来发现，把html中的方法改成post就是插入乱码，改成get就能正常插入，哎。

最后一个问题，是修改的时候，一直报sql语句出错了，我盯着sql语句瞅了半天，查资料，总共得花了一小时，最后发现居然是因为我把set后面的修改的内容用括号括起来了，删掉后立马成功运行了，我真是太无语了。

DBUtil：

package util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBUtil {
    private static String url = "jdbc:mysql://localhost:3306/qimo?&useSSL=false&serverTimezone=UTC";//数据库名称qimo
    private static String user = "root";
    private static String password = "wangzeyang";
    private static String jdbcName="com.mysql.cj.jdbc.Driver";
    private Connection con=null;
    public static  Connection getConnection() {
        Connection con=null;
        try {
            Class.forName(jdbcName);
            con=DriverManager.getConnection(url, user, password);
            //System.out.println("数据库连接成功");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            //System.out.println("数据库连接失败");
            e.printStackTrace();
        }
        try {
            con = DriverManager.getConnection(url,user,password);
            System.out.println("连接成功");


        } catch (SQLException e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        return con;
    }
    public static void main(String[] args)throws SQLException {
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql ="select * from test";
        pstmt = conn.prepareStatement(sql);
        rs = pstmt.executeQuery();
        System.out.println(getConnection());
        while(rs.next()){
            System.out.println("成功");
        }

    }

    // return con;


    public static void close(Connection con) {
        if(con!=null)
            try {
                con.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

    }
    public static void close(Statement state, Connection conn) {
        if(state!=null) {
            try {
                state.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(ResultSet rs, Statement state, Connection conn) {
        if(rs!=null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(state!=null) {
            try {
                state.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

Dao:

package dao;

import entity.Student;
import util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import static util.DBUtil.close;


public class Dao {
    public int insert(Student student){
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        String sql="insert into student (xuehao,name,sex,birthday)values (?,?,?,?)";
        try {
            connection=DBUtil.getConnection();
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,student.getXuehao());
            preparedStatement.setString(2,student.getName());
            preparedStatement.setString(3,student.getSex());
            preparedStatement.setString(4, student.getBirthday());
            int result=preparedStatement.executeUpdate();
            return result;

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(preparedStatement,connection);
        }
        return 0;
    }
    public int update(Student student){
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        String sql="update student set name=?,sex=?,birthday=? where xuehao=?";
        try {
            connection=DBUtil.getConnection();
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,student.getName());
            preparedStatement.setString(2,student.getSex());
            preparedStatement.setString(3, student.getBirthday());
            preparedStatement.setString(4, student.getXuehao());
            int result=preparedStatement.executeUpdate();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(preparedStatement,connection);
        }
        return 0;
    }
    public int delete(String xuehao){
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        String sql="delete  from student where xuehao=?";
        try {
            connection=DBUtil.getConnection();
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,xuehao);
            int result = preparedStatement.executeUpdate();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(preparedStatement,connection);
        }
        return 0;
    }
    public Student select(String xuehao){
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        Student student=null;
        String sql="select * from student where xuehao=?";
        try {
            connection=DBUtil.getConnection();
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,xuehao);
            resultSet=preparedStatement.executeQuery();
            if (resultSet.next()){
                String id=resultSet.getString("xuehao");
                String name=resultSet.getString("name");
                String sex=resultSet.getString("sex");
                String birthday=resultSet.getString("birthday");
                student=new Student(id,name,sex,birthday);
            }
            return student;

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(resultSet,preparedStatement,connection);
        }

        return null;
    }
}

servlet(多个):

package servlet;

import dao.Dao;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
@WebServlet("/Delete")
public class DeleteServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        this.doPost(req,resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String xuehao=req.getParameter("xuehao");
        Dao dao=new Dao();
        if (dao.delete(xuehao)==1){
            System.out.println("删除成功");
        }
        else {
            System.out.println("删除失败");
        }
    }
}







package servlet;

import dao.Dao;
import entity.Student;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
@WebServlet("/Insert")
public class InsertServlet extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("utf-8");
        String xuehao=req.getParameter("xuehao");
        String name=req.getParameter("name");
        String sex=req.getParameter("sex");
        String birthday=req.getParameter("birthday");
        Student student=new Student(xuehao,name,sex,birthday);
        Dao dao=new Dao();
        if (dao.insert(student)==1){
            System.out.println("插入成功");
        }
        else{
            System.out.println("插入失败");
        }
    }

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        this.doPost(req,resp);
        req.setCharacterEncoding("utf-8");
    }
}








package servlet;

import dao.Dao;
import entity.Student;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
@WebServlet("/Select")
public class SelectServlet extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String xuehao=req.getParameter("xuehao");
        Dao dao=new Dao();
        Student student=dao.select(xuehao);
        if (student!=null){
            System.out.println("学号："+student.getXuehao()+"姓名："+student.getName()+"性别："+student.getSex()+"生日："+student.getBirthday());
        }
        else {
            System.out.println("未查询到");
        }
    }

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        this.doPost(req,resp);
    }
}








package servlet;

import dao.Dao;
import entity.Student;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
@WebServlet("/update")
public class UpdateServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        this.doPost(req, resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String xuehao=req.getParameter("xuehao");
        String name=req.getParameter("name");
        String sex=req.getParameter("sex");
        String birthday=req.getParameter("birthday");
        Student student=new Student(xuehao,name,sex,birthday);
        Dao dao=new Dao();
        if (dao.update(student)==1){
            System.out.println("修改成功");
        }
        else{
            System.out.println("修改失败");
        }
    }
}

HTML：

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>insert</title>
</head>
<body>
<form action="/xueshengsushe_war_exploded/Insert" method="get">
    <table align="center">
        <tr><td>添加信息</td></tr>
        <tr><td>学号：<input type="text" name="xuehao"></td></tr>
        <tr><td>姓名：<input type="text" name="name"></td></tr>
        <tr><td>性别：<input type="text" name="sex"></td></tr>
        <tr><td>生日：<input type="text" name="birthday"></td></tr>
        <tr><td><input type="reset" value="重置"></td>
        <td><input type="submit" value="添加"></td>
        </tr>
    </table>
</form>
</body>
</html>







<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>delete</title>
</head>
<body>
<form action="/xueshengsushe_war_exploded/Delete" method="post">
    <table align="center">
        <tr><td>删除信息</td></tr>
        <tr><td>要删除学生学号： <input type="text" name="xuehao"></td></tr>
        <tr>
            <td><input type="submit" value="确认"></td>
        </tr>
    </table>
</form>
</body>
</html>







<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>update</title>
</head>
<body>
<form action="/xueshengsushe_war_exploded/update" method="get">
    <table>
        <tr><td>修改信息</td></tr>
        <tr><td>要修改学生的学号：<input type="text" name="xuehao"></td></tr>
        <tr><td>姓名：<input type="text" name="name"></td></tr>
        <tr><td>性别：<input type="text" name="sex"></td></tr>
        <tr><td>生日：<input type="text" name="birthday"></td></tr>
        <tr>
            <td><input type="submit" value="提交"></td>
            <td><input type="reset" value="重置"></td></tr></table>
</form>
</body>
</html>









<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>select</title>
</head>
<body>
<form action="/xueshengsushe_war_exploded/Select" method="post">
    <table>
        <tr><td>要查询学生的学号</td></tr>
        <tr> <td>学号： <input type="text" name="xuehao"></td></tr>
        <tr><input type="submit" value="查询"></tr>
    </table>
</form>
</body>
</html>