JDBC访问数据库
(参考自书籍JavaWeb编程技术 沈泽刚 清华大学出版社 第3版 第五章)
MySQL版本:
mysql-installer-community-8.0.11.0.msi
mysql-connector-java-8.0.11.zip
1、创建数据库、数据表
Create database webstore;
Use webstore;
Create table products(
id integer not null primary key,
pname varchar(20) not null,
brand varchar(20) not null,
price float,
stock smallint
);
查看表的结构:
Describe products;
插入记录:
Insert into products values(102,"平板电脑","苹果",1990,5);
Insert into products values(101,"数码相机","奥林巴斯",1330,3);
Insert into products values(103,"笔记本电脑","lenvov",4900,8);
Insert into products values(104,"华为P9手机","华为",5000,5);
Select * from products;
实体类:Product.java——程序5.1
查询记录的Servlet:ProductQueryServlet.java
视图页面:
queryProduct.jsp——起始页面,程序5.2
displayProduct.jsp——查询一件商品,程序5.4
displayAllProduct.jsp——查询所有商品,程序5.5
error.jsp——程序5.6
(项目结构如图)
ProductQueryServlet.java
1 package com.demo; 2 3 import java.io.IOException; 4 import java.sql.Connection; 5 import java.sql.DriverManager; 6 import java.sql.ResultSet; 7 import java.sql.SQLException; 8 import java.util.ArrayList; 9 10 import javax.servlet.ServletException; 11 import javax.servlet.annotation.WebServlet; 12 import javax.servlet.http.HttpServlet; 13 import javax.servlet.http.HttpServletRequest; 14 import javax.servlet.http.HttpServletResponse; 15 import com.model.Product; 16 17 18 /** 19 * Servlet implementation class ProductQueryServlet 20 */ 21 @WebServlet("/product-query")//此处对应的是queryProduct中的表单 22 public class ProductQueryServlet extends HttpServlet { 23 private static final long serialVersionUID = 1L; 24 Connection dbconn = null; 25 public void init() { 26 String driver = "com.mysql.cj.jdbc.Driver"; 27 String dburl = 28 "jdbc:mysql://localhost/webstore?useSSL=false&serverTimezone=Asia/Shanghai"; 29 String username = "";//数据库用户 30 String password = "";//数据库密码 31 try { 32 Class.forName(driver); 33 dbconn = DriverManager.getConnection( 34 dburl,username,password); 35 } catch (ClassNotFoundException e1) { 36 System.out.println(e1); 37 getServletContext().log("驱动程序类找不到!"); 38 } catch(SQLException e2){ 39 System.out.println(e2); 40 } 41 } 42 /** 43 * @see HttpServlet#HttpServlet() 44 */ 45 public ProductQueryServlet() { 46 super(); 47 // TODO Auto-generated constructor stub 48 } 49 50 /** 51 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) 52 */ 53 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { //超链接所以是doGet 56 try{ 57 //创建statement类对象,用来执行SQL语句! 58 java.sql.Statement stmt = dbconn.createStatement(); 59 //要执行的SQL语句 60 String sql="select * from products" ; 61 //ResultSet类,用来存放获取的结果集! 62 ResultSet rst = stmt.executeQuery(sql); 63 64 ArrayList<Product> productList = null; 65 productList = new ArrayList<Product>(); 66 while(rst.next()){ 67 Product product = new Product(); 68 product.setId(rst.getInt("id")); 69 product.setPname(rst.getString("pname")); 70 product.setBrand(rst.getString("brand")); 71 product.setPrice(rst.getFloat("price")); 72 product.setStock(rst.getInt("stock")); 73 productList.add(product); 74 } 75 if(!productList.isEmpty()){ 76 request.getSession().setAttribute("productList",productList); 77 response.sendRedirect("/teacherWebCode/displayAllProduct.jsp"); 78 }else{ 79 response.sendRedirect("/teacherWebCode/error.jsp"); 80 } 81 82 rst.close(); 83 ((Connection) stmt).close(); 84 85 }catch(SQLException e2){ 86 System.out.println(e2); 87 }catch(Exception e){ 88 e.printStackTrace(); 89 } 90 91 } 92 93 /** 94 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) 95 */ 96 protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { //表单form需要doPost请求 99 try{ 100 String id = request.getParameter("productid"); 101 if(id==""){ 102 response.sendRedirect("/teacherWebCode/error.jsp"); 103 } 104 else{ 105 //创建statement类对象,用来执行SQL语句! 106 java.sql.Statement stmt= dbconn.createStatement(); 107 //要执行的SQL语句 108 String sql="select * from products WHERE id ="+id; 109 //ResultSet类,用来存放获取的结果集! 110 ResultSet rst= stmt.executeQuery(sql);111 if(rst.next()){ 112 Product product = new Product(); 113 product.setId(rst.getInt("id")); 114 product.setPname(rst.getString("pname")); 115 product.setBrand(rst.getString("brand")); 116 product.setPrice(rst.getFloat("price")); 117 product.setStock(rst.getInt("stock")); 118 request.getSession().setAttribute("product", product); 119 response.sendRedirect("/teacherWebCode/displayProduct.jsp"); 120 }else{ 121 response.sendRedirect("/teacherWebCode/error.jsp"); 122 } 123 124 rst.close(); 125 ((Connection) stmt).close(); 126 } 127 128 }catch(SQLException e2){ 129 System.out.println(e2); 130 }catch(Exception e){ 131 e.printStackTrace(); 132 } 133 } 134 135 public void destroy(){ 136 try { 137 dbconn.close(); 138 }catch(Exception e){ 139 e.printStackTrace(); 140 } 141 } 142 143 }
Product.java
1 package com.model; 2 3 import java.io.Serializable; 4 5 6 @SuppressWarnings("serial") 7 public class Product implements Serializable{ 8 private int id; 9 private String pname; 10 private String brand; 11 private float price; 12 private int stock; 13 public Product() { } 14 public Product(int id, String pname, String brand, float price, int stock) { 15 super(); 16 this.id = id; 17 this.pname = pname; 18 this.brand = brand; 19 this.price = price; 20 this.stock = stock; 21 } 22 public int getId() { 23 return id; 24 } 25 public void setId(int id) { 26 this.id = id; 27 } 28 public String getPname() { 29 return pname; 30 } 31 public void setPname(String pname) { 32 this.pname = pname; 33 } 34 public String getBrand() { 35 return brand; 36 } 37 public void setBrand(String brand) { 38 this.brand = brand; 39 } 40 public float getPrice() { 41 return price; 42 } 43 public void setPrice(float price) { 44 this.price = price; 45 } 46 public int getStock() { 47 return stock; 48 } 49 public void setStock(int stock) { 50 this.stock = stock; 51 } 52 53 }
displayAllProduct.jsp
1 <%@ page contentType="text/html; charset=UTF-8" 2 pageEncoding="UTF-8" %> 3 <%@ page import="java.util.*,com.model.Product" %> 4 <html> 5 <head><title>显示所有商品</title></head> 6 <body> 7 <table border="1"> 8 <tr><td>商品号</td><td>商品名</td><td>品牌</td> 9 <td>价格</td><td>数量</td><td></tr> 10 <% ArrayList <Product> productList = 11 (ArrayList<Product>)session.getAttribute("productList"); 12 for(Product product:productList){ 13 %> 14 <tr><td><%=product.getId()%></td> 15 <td><%=product.getPname()%></td> 16 <td><%=product.getBrand()%></td> 17 <td><%=product.getPrice()%></td> 18 <td><%=product.getStock()%></td> 19 </tr> 20 <% 21 } 22 %> 23 </table> 24 </body></html>
displayProduct.jsp
1 <%@ page contentType="text/html; charset=utf-8" %> 2 <jsp:useBean id="product" type="com.model.Product" 3 scope="session"></jsp:useBean> 4 <html> 5 <head><title>商品信息</title></head> 6 <body> 7 <table border="0"> 8 <tr><td>商品号:</td><td>${product.id}</td></tr> 9 <tr><td>商品名:</td><td>${product.pname}</td></tr> 10 <tr><td>品牌: </td><td>${product.brand}</td></tr> 11 <tr><td>价格: </td><td>${product.price}</td></tr> 12 <tr><td>库存量:</td><td>${product.stock}</td></tr> 13 </table> 14 </body></html>
error.jsp
1 <%@ page contentType="text/html; charset=UTF-8" %> 2 <html><body> 3 该商品不存在。<a href="/teacherWebCode/queryProduct.jsp">返回</a> 4 </body></html>
querryProduct.jsp
1 <%@ page language="java" contentType="text/html; charset=UTF-8" 2 pageEncoding="UTF-8"%> 3 <!DOCTYPE html> 4 <html> 5 <head> 6 <meta charset="UTF-8"> 7 <title>商品查询</title> 8 </head> 9 <body> 10 <p><a href="product-query">查询所有商品</a></p> 11 <form action = "product-query" method="post"> 12 请输入商品号: 13 <input type = "text" name="productid" size="15"> 14 <input type = "submit" value = "确定"> 15 </form> 16 17 </body> 18 </html>
jar包mysql-connector-java-8.0.22.jar已经放入C:\Apache Software Foundation\Tomcat 9.0\lib中