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

最新推荐文章于 2022-12-28 11:31:34 发布

勤读意现

最新推荐文章于 2022-12-28 11:31:34 发布

阅读量2.3k

点赞数 2

分类专栏： servlet jdbc jsp 文章标签： servlet mysql jdbc jsp

本文链接：https://blog.csdn.net/u013154103/article/details/77823139

版权

jdbc 同时被 3 个专栏收录

4 篇文章 0 订阅

订阅专栏

servlet

1 篇文章 0 订阅

订阅专栏

jsp

1 篇文章 0 订阅

订阅专栏

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

目录结构：
这里写图片描述

用到的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;
    }
}