练习javaweb+mysql+jsp

只是简单的使用mysql、简单的练习。
有很多待完善的地方，比如list的servlet页面，应该判断有没有用户的。
比如list.jsp 应该循环list而不是写死
index.jsp 样式可以再优化一下的。比如按钮就特丑。

本文展示了一个简单的MySQL数据库操作练习项目，主要包含以下内容：

数据库连接工具类(BaseDao)：
提供连接MySQL数据库的基本功能
实现SQL执行(增删改查)和资源关闭方法
使用JDBC驱动和预处理语句
用户实体类(User)：
包含id、用户名、密码等基本属性
DAO层接口及实现：
定义用户登录、查询、增删改等操作
实现具体SQL执行和结果集处理
包含UserList(登录验证)、ALLUser(查询所有)、addUser(添加用户)等方法
项目目前存在以下待改进点：

前端页面需要优化样式
列表页面应实现动态循环而非硬编码
需要增加用户存在性判断等逻辑验证
代码结构有待完善
这是一个基础的JDBC实践项目，展示了数据库连接、CRUD操作的基本实现方式。

连接后端

package com.zhang.dao;



import java.sql.*;

//连接数据库工具
public class BaseDao {

    private String url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8";
    private String username = "root";
    private String password = "1234";
    private static String driver = "com.mysql.jdbc.Driver";

    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;

    static {
        try {
            //加载驱动
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //创建链接
    private void getConnection() {
        try {
            conn = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //执行sql
    //增删改
    public int executeUpdate(String sql, Object... objs) {
        //获得连接
        getConnection();
        try {
//select * from user where username=?and password=?
            ps = conn.prepareStatement(sql);
            if (objs != null) {
                for (int i = 0; i < objs.length; i++) {
                    ps.setObject(i + 1, objs[i]);
                }
            }
            int i = ps.executeUpdate();
            close();
            //关闭资源
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }

    //查询
    public ResultSet executeQuery(String sql,Object... objs) {
        getConnection();
        try {
            ps = conn.prepareStatement(sql);
            if (objs != null) {
                for (int i = 0; i < objs.length; i++) {
                    ps.setObject(i + 1, objs[i]);
                }
            }
            rs = ps.executeQuery();
            return rs;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    //关闭
    public void close() {

        try {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            System.out.println("关闭出错");
        }
    }
    public void test() {
        try {
            Class.forName(driver);
            Connection connection = DriverManager.getConnection(url, username, password);
            System.out.println("连接成功");

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        BaseDao BaseDao = new BaseDao();
        BaseDao.test();

    }
}

实体类

public class User {
    private Integer id;
    private String username;
    private String password;
    private String name;
    private String addr;
    private String tel;
}

dao层

import com.zhang.entity.User;

import java.util.List;

public interface UserDao {
    //用户登录，新增用户，删除用户，修改用户。
    //用户登录
    User UserList(User user);

    //查询所有
    List<User> ALLUser();

    //增加用户信息
    int addUser(User user);

    //删除用户
    int delUserByID(Integer id);

    //修改用户
    int updUserByID(Integer id);
}


import com.zhang.dao.BaseDao;
import com.zhang.dao.UserDao;
import com.zhang.entity.User;

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


public class UserDaoImpl extends BaseDao implements UserDao {
    @Override
    public User UserList(User user) {
        String sql = "select * from user where username= ? and password=?";
        ResultSet rs = executeQuery(sql,user.getUsername(),user.getPassword());
        try {
            User user1=null;
            if (rs.next()){
                user1= new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6));
            }
            close();
            return user1;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<User> ALLUser() {
        String sql = "select * from user";
        ResultSet rs = executeQuery(sql);
        List list=new ArrayList();
        try {
            while (rs.next()){
                list.add(new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6)));
            }
            close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int addUser(User user) {
        String sql = " insert into user(id,username,password,name,addr,tel) values(?,?,?,?,?,?);";
        int i = this.executeUpdate(sql,user.getId(),user.getUsername(),user.getPassword(),user.getName(),user.getAddr(),user.getTel());
        return i ;
    }

    @Override
    public int delUserByID(Integer id) {
        return 0;
    }

    @Override
    public int updUserByID(Integer id) {
        return 0;
    }

    public static void main(String[] args) {
//        UserDaoImpl UserDaoImpl=new UserDaoImpl();
////        System.out.println(UserDaoImpl.ALLUser());
//        List<User> users = UserDaoImpl.ALLUser();
    }

}

service层

import com.zhang.entity.User;

import java.util.List;

public interface UserService {
    User UserList(User user);

    //增加用户信息
    boolean addUser(User user);

    //删除用户
    boolean delUserByID(Integer id);

    //修改用户
    boolean updUserByID(Integer id);
    //查询所有
    List<User> ALLUser();
}



import com.zhang.dao.UserDao;
import com.zhang.dao.impl.UserDaoImpl;
import com.zhang.entity.User;
import com.zhang.service.UserService;

import java.util.List;

public class UserServceImpl implements UserService {
    UserDao userDao=new UserDaoImpl();
    @Override
    public User UserList(User user) {
        return userDao.UserList(user);
    }

    @Override
    public boolean addUser(User user) {
        return userDao.addUser(user)>0?true:false;
    }

    @Override
    public boolean delUserByID(Integer id) {
        return false;
    }

    @Override
    public boolean updUserByID(Integer id) {
        return false;
    }

    @Override
    public List<User> ALLUser() {
        return userDao.ALLUser();
    }
}

servlet层

import com.zhang.entity.User;
import com.zhang.service.UserService;
import com.zhang.service.impl.UserServceImpl;

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("/addServlet")
public class AddServlet extends HttpServlet {
    UserService userService=new UserServceImpl();
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String username = request.getParameter("username");
        String password = request.getParameter("password");
        String name = request.getParameter("name");
        String addr = request.getParameter("addr");
        String tel = request.getParameter("tel");
        boolean b = userService.addUser(new User(null, username, password, name, addr, tel));
        response.sendRedirect("./list.jsp");
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    }
}



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("/listServlet")
public class ListServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        System.out.println(123);
        response.sendRedirect("./list.jsp");
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    }
}


import com.zhang.entity.User;
import com.zhang.service.UserService;
import com.zhang.service.impl.UserServceImpl;

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

@WebServlet("/loginServlet")
public class LoginServlet extends HttpServlet {
    UserService userService=new UserServceImpl();
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String username = request.getParameter("username");
        String password = request.getParameter("password");
        System.out.println(username+password);
        User user = userService.UserList(new User(username,password));
        List<User> list = userService.ALLUser();
        if(user!=null){
            //登陆成功
            request.getSession().setAttribute("user",user);
            request.getSession().setAttribute("list",list);
            response.sendRedirect("./list.jsp");
        }else {
            response.sendRedirect("./err.jsp");
        }
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    }
}

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">
</web-app>

前端页面

<!-- add.jsp -->

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>添加</title>
</head>
<body>
<form action="./addServlet" method="post">
    <input type="text" name="id" hidden><br>
    username:<input type="text" name="username"><br>
    password:<input type="text" name="password"><br>
    name:<input type="text" name="name"><br>
    addr:<input type="text" name="addr"><br>
    tel:<input type="text" name="tel"><br>
    <input type="submit" value="提交">
</form>
</body>
</html>

<!-- err.jsp -->
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
<h1>错误</h1>
</body>
</html>


<!-- index.jsp -->
<%@ page contentType="text/html;charset=UTF-8" language="java" %>

<html>
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  <title>Brook系统</title>

  <script type="text/javascript" src="js/jquery-3.4.1.min.js"></script>


  <style type="text/css">
    body {
      background-color:#00b38a;
      text-align:center;
    }

    .lp-login {
      position:absolute;
      width:500px;
      height:300px;
      top:50%;
      left:50%;
      margin-top:-250px;
      margin-left:-250px;
      background: #fff;
      border-radius: 4px;
      box-shadow: 0 0 10px #12a591;
      padding: 57px 50px 35px;
      box-sizing: border-box
    }


    .lp-login .loginBtn {
      display:block;
      text-decoration:none;
      height: 48px;
      width: 150px;
      line-height: 48px;
      font-size: 16px;
      color: #fff;
      text-align: center;
      background-image: -webkit-gradient(linear, left top, right top, from(#09cb9d), to(#02b389));
      background-image: linear-gradient(90deg, #09cb9d, #02b389);
      border-radius: 3px
    }


    input[type='text'] {
      height:30px;
      width:250px;
    }

    span {
      font-style: normal;
      font-variant-ligatures: normal;
      font-variant-caps: normal;
      font-variant-numeric: normal;
      font-variant-east-asian: normal;
      font-weight: normal;
      font-stretch: normal;
      font-size: 14px;
      line-height: 22px;
      font-family: "Hiragino Sans GB", "Microsoft Yahei", SimSun, Arial, "Helvetica Neue", Helvetica;
    }

  </style>
  <script type="text/javascript">
    $(function(){
      $(".loginBtn").bind("click",function(){
        let username = $("#username").val();
        let password = $("#password").val();

        if(username == null){
          alert("请输入用户名");
          return;
        }

        $.ajax({
          url:'resume/login',
          type:'POST',    //GET
          async:false,    //或false,是否异步
          data:{
            userid:username,
            password:password
          },
          timeout:5000,    //超时时间
          dataType:'json', //返回的数据格式：json/xml/html/script/jsonp/text
          success:function(data){
            alert(data.message);
            if (data.code == '0') {
              window.location.href = 'list.jsp';
            }
          },
          failure:function (data) {

          }
        })
      })
    })
  </script>
</head>
<body>


<form action="./loginServlet" method="post">
  <table class="lp-login">
    <tr>
      <td align="right"><span>用户名</span></td>
      <td align="center">
        <input type="text" id="username" name="username" placeholder="username"></input>
      </td>
    </tr>
    <tr>
      <td align="right"><span>密码</span></td>
      <td align="center">
        <input type="text" id="password" name="password" placeholder="password"></input>
      </td>
    </tr>
    <tr align="center">
      <td colspan="2">
        <a class="loginBtn"><input type="submit" value="登陆"></a>
      </td>
    </tr>
  </table>
</form>

</body>
</html>



<!-- list.jsp -->
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page import="com.zhang.entity.User" %>
<%@ page import="java.util.List" %>
<%@ page import="com.zhang.service.UserService" %>
<%@ page import="com.zhang.service.impl.UserServceImpl" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html lang="en">

<head><title>简历列表</title></head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<style type="text/css">
    table, td{
        font:100% '微软雅黑';
    }
    table{width:80%;border-collapse:collapse; margin:0 0 0 100px}
    th, td{text-align:center;border:1px solid #fff;}
    th{background:#328aa4}
    td{background:#e5f1f4;}
</style>
<script type="text/javascript" src="js/jquery-3.4.1.min.js"></script>

<script type="text/javascript">
    $(function(){
        loadResumeListData();

        let tds = $("td");
        tds.click(tdClick);
    });

    //添加点击事件
    function addClickEvent() {
        //第一行绑定添加事件
        $("#add_btn").bind("click", function() {
            addNewLine(this);
        })
        //第一行绑定编辑事件
        $("#edit_btn").bind("click", function() {
            saveData(this);
        })
        //第一行绑定删除事件
        $("#delete_btn").bind("click", function() {
            deleteData();
        })
    }

    //删除数据
    function deleteData() {
        if(window.confirm("您确定要删除数据吗?")) {
            let id = $('table tr').eq(1).find("td").eq(0).text();
            $.ajax({
                url:'resume/delete',
                type:'POST',
                async:false,
                data:{
                    id: id
                },
                timeout:5000,    //超时时间
                dataType:'json', //返回的数据格式：json/xml/html/script/jsonp/text
                success:function(data){
                    loadResumeListData();
                },
                failure:function (data) {

                }
            })
        }
    }

    function saveData(saveBtn) {
        let currLine = $(saveBtn).parent().parent().prevAll().length + 1;
        let id = $('table tr').eq(currLine).find("td").eq(0).text();
        let name = $('table tr').eq(currLine).find("td").eq(1).text();
        let address = $('table tr').eq(currLine).find("td").eq(2).text();
        let phone = $('table tr').eq(currLine).find("td").eq(3).text();

        $.ajax({
            url:'resume/update',
            type:'POST',
            async:false,
            data:{
                id: id,
                name: name,
                address: address,
                phone: phone
            },
            timeout:5000,    //超时时间
            dataType:'json', //返回的数据格式：json/xml/html/script/jsonp/text
            success:function(data){
                alert("保存成功!");
                loadResumeListData();
            },
            failure:function (data) {

            }
        })
    }

    //给表格添加点击事件，使表格可编辑
    function tdClick(){
        let tdnode = $(this);
        let tdtext = tdnode.text();
        if (tdtext == '修改   删除' || tdtext == '新建') {
            return;
        }
        tdnode.html("");
        let input = $("<input>");
        input.val(tdtext);
        input.keyup(function(event){
            let myEvent = event || window.event;
            let keyCode = myEvent.keyCode;
            //判断是否按下Entry键
            if(keyCode == 13) {
                let inputnode = $(this);
                let inputtext = inputnode.val();
                let td = inputnode.parent();
                td.html(inputtext);
                td.click(tdClick);
            }
            //判断是否按下ESC键
            if(keyCode == 27) {
                $(this).parent().html(tdtext);
                $(this).parent().click(tdClick);
            }
        });

        tdnode.append(input);
        tdnode.children("input").trigger("select");
        //输入框失去焦点，所执行的方法
        input.blur(function() {
            tdnode.html($(this).val());
            tdnode.click(tdClick);
        });
        tdnode.unbind("click");
    }

    <!--请求列表数据-->
    function loadResumeListData() {
        $.ajax({
            url:'resume/findList',
            type:'POST',
            async:false,
            data:{
            },
            timeout:5000,    //超时时间
            dataType:'json', //返回的数据格式：json/xml/html/script/jsonp/text
            success:function(data){
                refreshList(data);
                addClickEvent();
            },
            failure:function (data) {

            }
        })
    }

    <!--刷新列表-->
    function refreshList(data) {
        let str1 = "";
        $("#resumeBody").html("");
        for(let i = 0; i<data.length; i++) {
            str1 = "<tr>" +
                "<td id=\"id\">" + data[i].id + "</td>" +
                "<td id=\"name\">" + data[i].name + "</td>" +
                "<td id=\"address\">" + data[i].address + "</td>" +
                "<td id=\"phone\">" + data[i].phone + "</td>" +
                "<td>" + "<a href=\"#\" id=\"edit_btn\">修改</a>" + "   " +
                "<a href=\"#\" id=\"delete_btn\">删除</a>" +
                "</td>" +

                "</tr>";
            $("#resumeBody").append(str1);
        }
    }

    <!--添加一行-->
    function addNewLine() {
        let str1 = "";
        str1 = "<tr>" +
            "<td id=\"id\">" + "</td>" +
            "<td id=\"address\">" + "</td>" +
            "<td id=\"name\">" + "</td>" +
            "<td id=\"phone\">" + "</td>" +
            "<td>" + "<a href=\"#\" id=\"addNew_btn\">新建</a>" +
            "</td>" +

            "</tr>";
        $("#resumeBody").append(str1);

        let tds = $("td");
        tds.click(tdClick);

        $("#addNew_btn").bind("click", function() {
            saveData(this);
        })
    }
</script>

<!--绘制表格-->
<body>
<a href="./add.jsp" style ="margin:100px" id="add_btn">新增</a>
<form action="">

</form>
<table id="tb">
    <c:forEach items="list">
    <tr id="listTable">

        <th style="width:100px" >ID</th>
        <th style="width:100px" >姓名</th>
        <th style="width:100px" >地址</th>
        <th style="width:100px" >电话</th>
<%--        <th style="width:100px" >功能</th>--%>
    </tr>
        <th style="width:100px" > ${list.get(0).id}</th>
        <th style="width:100px" > ${list.get(0).username}</th>
        <th style="width:100px" > ${list.get(0).addr}</th>
        <th style="width:100px" > ${list.get(0).tel}</th>
        <br>
    <th style="width:100px" > ${list.get(1).id}</th>
    <th style="width:100px" > ${list.get(1).username}</th>
    <th style="width:100px" > ${list.get(1).addr}</th>
    <th style="width:100px" > ${list.get(1).tel}</th>
    <br>
    <tbody id="resumeBody">
    </tbody>
</table>

</c:forEach>

<%
//    UserService userService=new UserServceImpl();
//    List<User> list = userService.ALLUser();
//    out.println(list.get(0));
//    for (User user: list){
//     out.println(user);
//        out.println();
//    }
%>
</body>
</html>