IDEA MySql之增删改查

用IDEA开发工具和MySql实现登录和增删改查的功能。

功能分析：
1.登录
2.增加
3.删除
4.修改
5.查询

效果演示

登陆页面
在这里插入图片描述
信息显示页面
在这里插入图片描述

一 ：数据库设计

MySql数据库名为 school
登录表名为 login
信息表名为 student
在这里插入图片描述
登录表插入合适的数据
在这里插入图片描述
信息表自己插入

二 ： 代码展示

目录结构
在这里插入图片描述
注意：我们需要引入相关架包

完整代码

信息类
Student

package com.zsh.bean;

public class Student {
    private int id;
    private String name;
    private String sex;
    private String address;

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public Student() {
        super();
        // TODO Auto-generated constructor stub
    }

    public Student(int id, String name, String sex, String address) {
        super();
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.address = address;
    }
}

增删改查登录
AddServlet

package com.zsh.servlet;

import com.zsh.util.DBUtil;

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.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

@WebServlet(name = "AddServlet")
public class AddServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setCharacterEncoding("utf-8");

        String name = request.getParameter("name");
        String sex = request.getParameter("sex");
        String address = request.getParameter("address");

        Connection conn = DBUtil.getConn();
        PreparedStatement ps = null;
        String sql = "insert into student(name,sex,address) values(?,?,?)";

        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1,name);
            ps.setString(2,sex);
            ps.setString(3,address);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeConn(conn,ps,null);
        }
        response.sendRedirect("select");

    }

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

DeleteServlet

package com.zsh.servlet;

import com.zsh.util.DBUtil;

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.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

@WebServlet(name = "DeleteServlet")
public class DeleteServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        request.setCharacterEncoding("utf-8");
        response.setCharacterEncoding("utf-8");

        String id = request.getParameter("id");

        Connection conn = DBUtil.getConn();
        PreparedStatement ps = null;
        String sql = "delete from student where id = ?";

        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,Integer.parseInt(id));
            ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeConn(conn,ps,null);
        }
        response.sendRedirect("select");

    }

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

LoginServlet

package com.zsh.servlet;

import com.zsh.util.DBUtil;

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

@javax.servlet.annotation.WebServlet(name = "LoginServlet")
public class LoginServlet extends javax.servlet.http.HttpServlet {
    protected void doPost(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws javax.servlet.ServletException, IOException {

        request.setCharacterEncoding("utf-8");
        response.setCharacterEncoding("utf-8");

        String name = request.getParameter("name");
        String pwd = request.getParameter("pwd");

        boolean flag = false;
        Connection conn = DBUtil.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select * from login where name = ? and pwd = ?";

        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1,name);
            ps.setString(2,pwd);
            rs = ps.executeQuery();

            if(rs.next()){
                flag = true;
            }else{
                flag = false;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeConn(conn,ps,rs);
        }

        if(flag){
            response.sendRedirect("select");
        }else {
            response.sendRedirect("index.jsp");
        }

    }

    protected void doGet(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws javax.servlet.ServletException, IOException {

    }
}

SelectServlet

package com.zsh.servlet;

import com.zsh.bean.Student;
import com.zsh.util.DBUtil;

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 javax.servlet.http.HttpSession;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@WebServlet(name = "SelectServlet")
public class SelectServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        request.setCharacterEncoding("utf-8");
        response.setCharacterEncoding("utf-8");

        Connection conn = DBUtil.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select * from student";
        List<Student> students = new ArrayList<Student>();

        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                Student student = new Student(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4));
                students.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeConn(conn,ps,rs);
        }
        HttpSession session = request.getSession();
        session.setAttribute("stu",students);
        response.sendRedirect("success.jsp");

    }

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

    }
}

UpdateServlet

package com.zsh.servlet;

import com.zsh.util.DBUtil;

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.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

@WebServlet(name = "UpdateServlet")
public class UpdateServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        request.setCharacterEncoding("utf-8");
        response.setCharacterEncoding("utf-8");

        String id = request.getParameter("id");
        String name = request.getParameter("name");
        String sex = request.getParameter("sex");
        String address = request.getParameter("address");

        Connection conn = DBUtil.getConn();
        PreparedStatement ps = null;
        String sql = "update student set name=?,sex=?,address=? where id = ?";

        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1,name);
            ps.setString(2,sex);
            ps.setString(3,address);
            ps.setInt(4,Integer.parseInt(id));
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeConn(conn,ps,null);
        }

        response.sendRedirect("select");

    }

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

链接驱动类
DBUtil

package com.zsh.util;

import java.sql.*;

public class DBUtil {
    public static Connection getConn(){
        Connection conn = null;
        String url = "jdbc:mysql://localhost:3306/school";
        String user = "root";
        String pwd = "111";


        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, pwd);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return conn;
    }

    public static void closeConn(Connection conn, PreparedStatement ps, ResultSet rs){
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(ps!=null){
            try {
                ps.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}

路径文件
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>LoginServlet</servlet-name>
        <servlet-class>com.zsh.servlet.LoginServlet</servlet-class>
    </servlet>
    <servlet>
        <servlet-name>SelectServlet</servlet-name>
        <servlet-class>com.zsh.servlet.SelectServlet</servlet-class>
    </servlet>
    <servlet>
        <servlet-name>DeleteServlet</servlet-name>
        <servlet-class>com.zsh.servlet.DeleteServlet</servlet-class>
    </servlet>
    <servlet>
        <servlet-name>AddServlet</servlet-name>
        <servlet-class>com.zsh.servlet.AddServlet</servlet-class>
    </servlet>
    <servlet>
        <servlet-name>UpdateServlet</servlet-name>
        <servlet-class>com.zsh.servlet.UpdateServlet</servlet-class>
    </servlet>
    
    <servlet-mapping>
        <servlet-name>LoginServlet</servlet-name>
        <url-pattern>/login</url-pattern>
    </servlet-mapping>
    <servlet-mapping>
        <servlet-name>SelectServlet</servlet-name>
        <url-pattern>/select</url-pattern>
    </servlet-mapping>
    <servlet-mapping>
        <servlet-name>DeleteServlet</servlet-name>
        <url-pattern>/del</url-pattern>
    </servlet-mapping>
    <servlet-mapping>
        <servlet-name>AddServlet</servlet-name>
        <url-pattern>/add</url-pattern>
    </servlet-mapping>
    <servlet-mapping>
        <servlet-name>UpdateServlet</servlet-name>
        <url-pattern>/update</url-pattern>
    </servlet-mapping>

</web-app>

JSP页面
add.jsp

<%--
  Created by IntelliJ IDEA.
  User: 张叔行
  Date: 2019/12/3
  Time: 16:22
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>添加</title>
</head>
<body>
<form action="add" method="post">
    姓名：<input type="text" name="name"><br>
    性别：<input type="text" name="sex"><br>
    住址：<input type="text" name="address"><br>
    <input type="submit" value="添加">
</form>
</body>
</html>

index.jsp

<%--
  Created by IntelliJ IDEA.
  User: 张叔行
  Date: 2019/12/3
  Time: 14:28
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
  <head>
    <title>登录</title>
    <script src="jquery.js"></script>
  </head>
  <body>
  <form action="login" method="post">
    账号：<input type="text" name="name" id="name"><br>
    密码：<input type="password" name="pwd" id="pwd"><br>
    <input type="submit" value="登录">
  </form>
  </body>
  <script>
    $("form").submit(function(){
      var name = $("#name").val();
      var pwd = $("#pwd").val();

      var nameFlag = false;
      var pwdFlag = false;

      if(name == "" || name == "undefined"){
        nameFlag = false;
        alert("账户不能为空！");
      }else{
        nameFlag = true;
      }

      if(pwd == "" || pwd == "undefined"){
        pwdFlag = false;
        alert("密码不能为空！");
      }else{
        pwdFlag = true;
      }

      if(nameFlag == false || pwdFlag == false){
        return false;
      }else{
        return true;
      }

    })
  </script>
</html>

success.jsp

<%@ page import="java.util.List" %>
<%@ page import="com.zsh.bean.Student" %>
<%@ page import="java.util.ArrayList" %><%--
  Created by IntelliJ IDEA.
  User: 张叔行
  Date: 2019/12/3
  Time: 14:40
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>信息显示</title>
</head>
<body>
<table>
    <thead>
    <tr>
        <td>
            序号
        </td>
        <td>
            姓名
        </td>
        <td>
            性别
        </td>
        <td>
            住址
        </td>
        <td colspan="2">
            操作
        </td>
        <td>

        </td>
        <td>
            <a href="add.jsp">添加</a>
        </td>
    </tr>
    </thead>
    <tbody>
    <%
        List<Student> students = (ArrayList)session.getAttribute("stu");
        for (Student student : students){
            %>
    <tr>
        <td>
            <%=student.getId() %>
        </td>
        <td>
            <%=student.getName() %>
        </td>
        <td>
            <%=student.getSex() %>
        </td>
        <td>
            <%=student.getAddress() %>
        </td>
        <td>
            <a href="del?id=<%=student.getId() %>">删除</a>
        </td>
        <td>
            <a href="update.jsp?id=<%=student.getId() %>">更新</a>
        </td>
    </tr>
    <%
        }
    %>
    </tbody>
</table>

</body>
</html>

update.jsp

<%@ page import="java.sql.Connection" %>
<%@ page import="com.zsh.util.DBUtil" %>
<%@ page import="java.sql.PreparedStatement" %>
<%@ page import="java.sql.ResultSet" %><%--
  Created by IntelliJ IDEA.
  User: 张叔行
  Date: 2019/12/3
  Time: 16:38
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>更新</title>
</head>
<body>

    <%
        request.setCharacterEncoding("utf-8");
        response.setCharacterEncoding("utf-8");

        String id = request.getParameter("id");

        Connection conn = DBUtil.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select * from student where id = ?";
        try{
            ps = conn.prepareStatement(sql);
            ps.setInt(1,Integer.parseInt(id));
            rs = ps.executeQuery();

            while (rs.next()){

                String name = rs.getString("name");
                String sex = rs.getString("sex");
                String address = rs.getString("address");
                %>
                <form action="update" method="post">
                    ID:<input type="text" name="id" value="<%=id %>" readonly="readonly"><br>
                    姓名：<input type="text" name="name" value="<%=name %>"><br>
                    性别：<input type="text" name="sex" value="<%=sex %>"><br>
                    住址：<input type="text" name="address" value="<%=address %>"><br>
                    <input type="submit" value="添加">
                </form>
                <%
            }

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtil.closeConn(conn,ps,rs);
        }


    %>

</body>
</html>

希望上述例子能对你有所帮助。

了解更多关注我哟！！！

版权声明：本文为weixin_45743799原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。