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中

 
 
 
 
 
 
 
 
 
 

posted on 2020-11-08 21:48  stuMartin  阅读(225)  评论(0)  编辑  收藏  举报

导航