|
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 productList = null;
productList = new ArrayList();
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();
}
}
}
|