jdbc+servlet+jsp+mysql简单的例子--gxy

刺骨的言语ヽ痛彻心扉 2022-06-09 10:17 122阅读 0赞

今天去面试，叫我做一个简单的注册并查询的例子，发现用多了springmvc,struts2尽然把这个最基础的忘记了。今天写一个最简单的稳固一下。其中有些参考了其他博主，再次感谢！

目录结构：  
![这里写图片描述][SouthEast]

用到的jar也在图中。

web.xml

<?xml version="1.0" encoding="UTF-8"?>
    <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">
      <display-name>simple-project</display-name>
      <servlet>
        <servlet-name>StudentsAdd</servlet-name>
        <servlet-class>servlet/StudentsAdd</servlet-class>
      </servlet>
    
      <servlet-mapping>
        <servlet-name>StudentsAdd</servlet-name>
        <url-pattern>/studentsAdd</url-pattern>
      </servlet-mapping>
    
    
      <welcome-file-list>
        <welcome-file>index.jsp</welcome-file>
      </welcome-file-list>
    </web-app>

index.jsp

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
     <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <html>
      <head>
        <title>注册</title>
    
      <body>
    
        <form action="addUser" method="post" >
    
            用户名：<input type="text" value="" name="name"/>
            <br/>
            密码：<input type="password" value="" name="password"/>
            <br/>
            <input type="submit" value="提交"/>
    
        </form>
    
    
        <table border="1">
            <tr>
                <td>编号</td>
                <td>姓名</td>  
            </tr>
    
            <c:forEach items="${list}" var="stu">
                <tr>
                    <td>${stu.id }</td>
                    <td>${stu.name }</td> 
    
                </tr>
            </c:forEach>
        </table>
    
    
      </body>
    </html>

实体类：

package model;
    
    public class Student {
        public long id;
        public String name;
        private String password;
    
        public void setPassword(String password) {
            this.password = password;
        }
    
        public String getPassword() {
            return password;
        }
        public long getId() {
            return id;
        }
        public void setId(long id) {
            this.id = id;
        }
        public String getName() {
            return name;
        }
        public void setName(String name) {
            this.name = name;
        }
    
    
    
    }

servlet类：

1.查询的

package servlet;
    
    import java.io.IOException;
    import java.util.ArrayList;
    
    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 model.Student;
    import util.DBConnection;
    
    /** * Servlet implementation class StudentsAdd */
    @WebServlet("/StudentsAdd")
    public class StudentsAdd extends HttpServlet { 
        private static final long serialVersionUID = 1L;
    
        /** * @see HttpServlet#HttpServlet() */
        public StudentsAdd() {
            super();
        }
    
        /** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */
        protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            doPost(request, response);
        }
    
        /** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */
        protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    
    
            DBConnection db = new DBConnection();  
    
            String sql = "select * from user";  
    
            ArrayList<Student> list = db.getStudentList(sql);  
    
            request.setAttribute("list", list);  
    
            request.getRequestDispatcher("index.jsp").forward(request, response);  
    
    
        }
    
    }

2.增加的：

package servlet;
    
    import java.io.IOException;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.Random;
    
    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 model.Student;
    import util.DBConnection;
    
    /** * 添加用户 * @author Administrator * */
    @WebServlet("/addUser")
    public class AddUser extends HttpServlet{ 
    
        private static final long serialVersionUID = 422040094756504779L;
    
        @Override
        protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
    
            doPost(req, resp);
        }
    
        protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException ,IOException {
    
            String name = req.getParameter("name");
            String password  = req.getParameter("password");
    
            Student stu = new Student();
            stu.setId(new Date().getTime());
            stu.setName(name);
            stu.setPassword(password);
    
            DBConnection dbc = new DBConnection();
    
            String sql = "insert into user values("+new Random().nextInt(100)+",'"+name+"','"+password+"')";
            dbc.ExecuteDel(sql);
    
            sql = "select * from user";  
    
            ArrayList<Student> list = dbc.getStudentList(sql);
    
            req.setAttribute("list", list);  
    
            req.getRequestDispatcher("index.jsp").forward(req, resp);  
    
    
        };
    
    }

数据库连接工具类：

package util;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.ArrayList;
    
    import model.Student;
    
    
    public class DBConnection { 
    
        /** * 驱动类名称 */
        private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
    
        /** * 数据库连接字符串 jdbc:mysql://localhost:3306/test 这种方式只对本地的数据库有用 test为数据库名称 */
        private static final String DATABASE_URL = "jdbc:mysql://localhost:3306/test";
    
        /** * 数据库用户名 */
        private static final String USER_NAME = "root";
    
        /** * 数据库密码 */
        private static final String PASSWORD = "123456";
    
        /** * 数据库连接类 */
        private static Connection conn;
    
        /** * 数据库操作类 */
        private static Statement stmt;
    
    
    
        // 加载驱动
        static{
            try {
                Class.forName(DRIVER_CLASS);
            } catch (Exception e) {
                System.out.println("加载驱动错误");
                System.out.println(e.getMessage());
            }
        }
    
        // 取得连接
        private static Connection getConnection(){
    
            try {
                conn = DriverManager.getConnection(DATABASE_URL, USER_NAME, PASSWORD);
            } catch (Exception e) {
                System.out.println("取得连接错误");
                System.out.println(e.getMessage());
            }
            return conn;
        }
    
        /** * 执行 增、删、改 操作 * @param sql */
        public void ExecuteDel(String sql){
    
            try {
                stmt = getConnection().createStatement();
                int rows = stmt.executeUpdate(sql);
                if(rows >= 1){
                    System.out.println("成功删除.....");
                } else {
                    System.out.println("删除失败.....");
                }
    
            } catch (Exception e) {
                System.out.println("statement取得错误");
                System.out.println(e.getMessage());
            }finally {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
    
    
        }
    
        /** * 读取数据库中的数据（查询操作） * @param sql * @return */
        public ArrayList<Student> getStudentList(String sql){
    
            ArrayList<Student> list = new ArrayList<Student>();
    
            // 取得数据库操作对象
            try {
                stmt = getConnection().createStatement();   
            } catch (Exception e) {
                System.out.println("statement取得错误");
                System.out.println(e.getMessage());
                return null;
            } 
            try {
    
                // 查询数据库对象,返回记录集(结果集)
                ResultSet rs = stmt.executeQuery(sql);
    
                // 循环记录集，查看每一行每一列的记录
                while (rs.next()) {
                    // 第一列 sno
                    int sno = rs.getInt(1);
    
                    // 第2列 sname
                    String sname = rs.getString(2);
    
                    Student stu = new Student();
                    stu.setId(sno);
                    stu.setName(sname);
    
                    list.add(stu);
                }
    
            } catch (Exception e) {
                System.out.println(e.getMessage());
            }
            return list;
        }
    }

完成了。

访问方式：  
[http://localhost:8888/simple-jdbc/StudentsAdd][http_localhost_8888_simple-jdbc_StudentsAdd]

完成，提供下载地址：

[http://download.csdn.net/download/u013154103/9962304][http_download.csdn.net_download_u013154103_9962304]

[SouthEast]: /images/20220609/0f4082e99c6e4b5c965a8e3db87a8747.png
[http_localhost_8888_simple-jdbc_StudentsAdd]: http://localhost:8888/simple-jdbc/StudentsAdd
[http_download.csdn.net_download_u013154103_9962304]: http://download.csdn.net/download/u013154103/9962304