Servlet连接MySQL数据库步骤

MySQL驱动下载:

首先得下载对应数据库的连接驱动包
我用的MySQL数据库所用的驱动是mysql-connector-java-8.0.18.jar
MySQL驱动下载官网: MySQL驱动下载
然后选择下载:

然后选择一个下载即可.(windows选择第二个)

Java操作数据库基本流程

1.加载驱动程序

Class.forName("com.mysql.cj.jdbc.Driver");

2.建立连接对象

String dburl = "jdbc:mysql://127.0.0.1:3306/数据库名?useSSL=false&serverTimezone=UTC"
Connection conn = DriverManager.getConnection(dburl,"用户名","密码");

3.创建语句对象

几种对象的创建方式不同:

Statement对象->conn.createStatement()
PreparedStatement对象->conn.prepareStatement()

比如创建Statement对象Statement stmt = conn.createStatement();

4.执行SQL语句并处理结果

ResultSet result = stmt.executeQuery(sql);

while(result.next()){
	out.println(result.getString(1)+"\t");
}

5.关闭建立的对象

dbconn.close();

示例:

使用PreparedStatement对象来进行SQL语句操作

数据库表结构

javaBean结构:

package com.demol;
import java.io.Serializable;

public class Product implements Serializable {
    private int id;
    private String pname;
    private String brand;
    private float price;
    private int stock;
    public Product(){}

    public Product(int id, String pname, String brand, float price, int stock) {
        this.id = id;
        this.pname = pname;
        this.brand = brand;
        this.price = price;
        this.stock = stock;
    }
}

逻辑处理的Servlet

package com.demol;

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

@WebServlet(name = "ProductQueryServlet",urlPatterns={"/query-product"})
public class ProductQueryServlet extends HttpServlet {
    Connection dbconn = null;
    public void init() {
        String dburl  = "jdbc:mysql://127.0.0.1:3306/new_schema?useSSL=false&serverTimezone=UTC";
        String username ="用户名";
        String password = "密码";
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            dbconn = DriverManager.getConnection(dburl,username,password);
            System.out.println("数据库连接成功");
        }catch (ClassNotFoundException e1){
            System.out.println(e1+"驱动程序找不到");
        }catch(SQLException e2){
            System.out.println(e2);
        }
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //返回所有商品
        ArrayList<Product> productList = null;
        productList = new ArrayList<Product>();
        try{
            String sql = "select * from products";
            PreparedStatement pstmt = dbconn.prepareStatement(sql);
            ResultSet result = pstmt.executeQuery();
            while(result.next()){
                Product product = new Product();
                product.setId(result.getInt("id"));
                product.setPname(result.getString("pname"));
                product.setBrand(result.getString("brand"));
                product.setPrice(result.getFloat("price"));
                product.setStock(result.getInt("stock"));
                productList.add(product);
            }
            if(!productList.isEmpty()){
                request.getSession().setAttribute("productList",productList);
                response.sendRedirect("/displayAllProduct.jsp");
            }else{
                response.sendRedirect("error.jsp");
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
    }
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            String productid = request.getParameter("productid");
            try{
                String sql = "select * from products where id= ?";
                PreparedStatement pstmt = dbconn.prepareStatement(sql);
                pstmt.setString(1,productid);
                ResultSet result = pstmt.executeQuery();
                if(result.next()){
                    Product product = new Product();
                    product.setId(result.getInt("id"));
                    product.setPname(result.getString("pname"));
                    product.setBrand(result.getString("brand"));
                    product.setPrice(result.getFloat("price"));
                    product.setStock(result.getInt("stock"));
                    request.getSession().setAttribute("product",product);
                    response.sendRedirect("displayProduct.jsp");
                }else{
                    response.sendRedirect("error.jsp");
                }
            }catch(SQLException e){
                e.printStackTrace();
            }
    }
    public void destroy(){
        try{
            dbconn.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
}