一、需求分析

用JSP/Servlet技術(shù)開發(fā)一個網(wǎng)上書店,主要完成以下功能：

用戶：注冊與登錄，用戶信息的修改

圖書：圖書顯示，圖書的購買，購物車的管理。圖書的添加，刪除，查詢

二、系統(tǒng)設(shè)計

1)創(chuàng)建相關(guān)的數(shù)據(jù)庫表；

2)使用MVC架構(gòu)或者JSP+JavaBean,至少一個功能模塊使用MVC;

3)在JSP中綜合使用EL 表達式，JSTL標記庫，JavaBean ；

4)使用過濾器完成請求參數(shù)編碼的設(shè)置或登錄權(quán)限的控制；

5)正確完成Servlet和過濾器的配置

6)使用數(shù)據(jù)庫連接池進行數(shù)據(jù)庫連

三、程序流程圖

四、各功能實現(xiàn)代碼

4.1管理員功能實現(xiàn)

4.1.1管理員類

4.1.2管理員類dao接口

4.1.3管理員類dao接口的實現(xiàn)方法（操作數(shù)據(jù)庫）

package bean;import utils.JDBCUtil;import java.sql.*;public class adminDaoImpl implements adminDao{public void addadmin(admin a) {//連接數(shù)據(jù)庫Connection conn = null;PreparedStatement psql=null;ResultSet rs=null;try {conn = JDBCUtil.getConnection();String sql = "insert into admin value (?,?)";conn = JDBCUtil.getConnection();psql=conn.prepareStatement(sql);psql.setString(1,a.getName());psql.setString(2,a.getPassword());int i = psql.executeUpdate();if(i>0){System.out.println("插入成功！");}else {System.out.println("插入失敗！");}conn.close();} catch (SQLException e) {e.printStackTrace();}finally {JDBCUtil.close(psql, conn);}}public admin queryadmin(String username) {//連接數(shù)據(jù)庫Connection conn = null;Statement statement=null;ResultSet rs=null;admin a=new admin();try {conn = JDBCUtil.getConnection();statement = conn.createStatement();String sql = "select * from admin where name ='" + username + "'";rs=statement.executeQuery(sql);while(rs.next()){a.setName(rs.getString("name"));a.setPassword(rs.getString("password"));}rs.close();conn.close();return a;} catch (SQLException e) {e.printStackTrace();}finally {JDBCUtil.close(statement, conn);}return a;} }

4.1.4管理員登錄頁面

4.1.5管理員注冊頁面

4.1.5管理員登錄的service方法

import bean.UserDaoImpl; import bean.adminDao; import bean.adminDaoImpl;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 javax.servlet.http.HttpSession; import java.io.IOException; import java.io.PrintWriter;@WebServlet(name = "adminLoginServlet",urlPatterns = "/adminLoginServlet") public class adminLoginServlet extends HttpServlet {protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {//設(shè)置編碼和響應(yīng)類型response.setContentType("text/html");response.setCharacterEncoding("utf-8");request.setCharacterEncoding("utf-8");HttpSession session =request.getSession(true);// 1.獲得用戶名和密碼// 2.驗證用戶名和密碼是否正確 "zhou" "123456"String adminname=request.getParameter("adminName");String password=request.getParameter("adminPasswd");//獲得響應(yīng)的輸出流PrintWriter pw=response.getWriter();adminDao dao = new adminDaoImpl();String name=dao.queryadmin(adminname).getName();String passwd=dao.queryadmin(adminname).getPassword();if(adminname.equals(name)&&password.equals(passwd)){session.setAttribute("admin",name);request.getRequestDispatcher("/adminnavcation.jsp").forward(request,response);}else {//驗證失敗pw.println("<font color='green'><h2>登錄失敗</h2></font>");}}protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {this.doPost(request,response);} }

管理員注冊的方法

import utils.JDBCUtil;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.Connection; import java.sql.PreparedStatement; import java.sql.SQLException;@WebServlet(name = "adminRegistServlet",urlPatterns = "/adminRegistServlet") public class adminRegistServlet extends HttpServlet {protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {request.setCharacterEncoding("utf-8");response.setContentType("html/text;charset=utf-8");//1.獲取數(shù)據(jù)String name = request.getParameter("name");String password = request.getParameter("password");//2.判斷用戶名不能為空if (name==null||"".equals(name)){System.out.println("用戶名不能為空!");String s = "用戶名不能為空";response.getWriter().write(s);}//連接數(shù)據(jù)庫，插入數(shù)據(jù)Connection conn = null;PreparedStatement psta = null;String sql = "insert into admin value (?,?)";try {conn = JDBCUtil.getConnection();psta = conn.prepareStatement(sql);psta.setString(1,name);psta.setString(2,password);psta.execute();} catch (SQLException e) {e.printStackTrace();}finally {JDBCUtil.close(psta,conn);}response.sendRedirect("adminlogin.html");}protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {this.doPost(request,response);} }

4.1.6管理員登陸后的顯示的主界面

4.1.7管理員添加商品的方法

import utils.JDBCUtil;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.Connection; import java.sql.PreparedStatement; import java.sql.SQLException;@WebServlet(name = "adminaddbooksServlet",urlPatterns = "/adminaddbooksServlet") public class adminaddbooksServlet extends HttpServlet {protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {request.setCharacterEncoding("utf-8");response.setContentType("html/text;charset=utf-8");//1.獲取數(shù)據(jù)String name = request.getParameter("name");String writer = request.getParameter("writer");String type=request.getParameter("type");String id=request.getParameter("id");int number= Integer.parseInt(request.getParameter("number"));int price= Integer.parseInt(request.getParameter("price"));//2.判斷用戶名不能為空/*if (name==null||"".equals(name)&&writer==null||"".equals(writer)&&type==null||"".equals(type)&&id==null||"".equals(id)&&number==0||"".equals(price)&&writer==null||"".equals(price)){String s = "書籍信息要寫完整";response.getWriter().write(s);response.sendRedirect("addbooks.html");}*///連接數(shù)據(jù)庫，插入數(shù)據(jù)Connection conn = null;PreparedStatement psta = null;String sql = "insert into book value (?,?,?,?,?,?)";try {conn = JDBCUtil.getConnection();psta = conn.prepareStatement(sql);psta.setString(1,name);psta.setString(2,writer);psta.setString(3,type);psta.setString(4,id);psta.setInt(5,number);psta.setInt(6,price);psta.execute();/*int i = psta.executeUpdate(sql);if(i>0){//request.getRequestDispatcher("adminlogin.html").forward(request,response);response.sendRedirect("adminnavcation.jsp");}else {System.out.println("插入失敗！");}*/} catch (SQLException e) {e.printStackTrace();}finally {JDBCUtil.close(psta,conn);}response.sendRedirect("adminnavcation.jsp");}protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {this.doPost(request,response);} }

4.1.7 管理員刪除商品的方法

<%@ page contentType="text/html;charset=UTF-8" language="java" %> <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <%@page isELIgnored="false" %> <%@ page import="java.util.List" %> <%@ page import="bean.*" %> <%@ page import="java.io.PrintWriter" %> <html> <head><title>管理員刪除圖書頁面</title> </head> <body> <%//獲取得到傳遞過來的idString id = request.getParameter("bid");BookDao dao=new BookDao();dao.deletebook(id);response.sendRedirect("adminnavcation.jsp"); %> </body> </html>

4.1.8管理員修改圖書單價的方法

<%@ page contentType="text/html;charset=UTF-8" language="java" %> <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <%@page isELIgnored="false" %> <%@ page import="java.util.List" %> <%@ page import="bean.*" %> <%@ page import="java.io.PrintWriter" %> <html> <head><title>修改圖書單價頁面</title></head> <body> <%request.setCharacterEncoding("utf-8");response.setCharacterEncoding("utf-8");//獲取得到傳遞過來的idString bookname=request.getParameter("name");int price= Integer.parseInt(request.getParameter("price"));BookDao dao=new BookDao();dao.updateprice(bookname,price);response.sendRedirect("adminnavcation.jsp"); %> </body> </html>

4.1.9管理員修改圖書庫存的方法

<%@ page contentType="text/html;charset=UTF-8" language="java" %> <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <%@page isELIgnored="false" %> <%@ page import="java.util.List" %> <%@ page import="bean.*" %> <%@ page import="java.io.PrintWriter" %> <html> <head><title>修改圖書數(shù)量頁面</title> </head> <body> <%request.setCharacterEncoding("utf-8");response.setCharacterEncoding("utf-8");//獲取得到傳遞過來的idString bookname=request.getParameter("name");int number= Integer.parseInt(request.getParameter("number"));BookDao dao=new BookDao();dao.updatenumber(bookname,number);response.sendRedirect("adminnavcation.jsp"); %> </body> </html>

4.1.10管理員查看所有用戶訂單的方法

<%@ page import="java.util.List" %> <%@ page import="bean.*" %> <%@ page import="bean.ShoppingCar" %> <%@ page import="java.util.ArrayList" %><%--Created by IntelliJ IDEA.User: LenovoDate: 2019/11/17Time: 16:39To change this template use File | Settings | File Templates. --%> <%@ page contentType="text/html;charset=UTF-8" language="java" %> <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <html> <head><title>管理員查看訂單</title> </head> <link rel="stylesheet" type="text/css" href="showallbooks.css"> <body style="margin: 0 auto; padding: 0; background-repeat: no-repeat"> <%ShoppingcarDao dao=new ShoppingcarDaoImpl();List<ShoppingCar> clist=dao.Queryallorderlist();pageContext.setAttribute("clist",clist);%> <c:if test="${empty(clist)}"><h1>暫無訂單！</h1> </c:if><%--如果購物車有購物項，就應(yīng)該把購物項的信息顯示給用戶--%> <c:if test="${!empty(clist)}"> <table id="tb"><tr><td>買家</td><td>書籍編號</td><td>書名</td><td>單價</td><td>數(shù)量</td><td>小計</td></tr><c:forEach var="clist" items="${clist}" varStatus="status"><tr><td>${clist.username}</td><td>${clist.bookid}</td><td>${clist.bookname}</td><td>${clist.bookprice}</td><td>${clist.booknumber}</td><td>${clist.littleprice}</td></tr></c:forEach></c:if> </table> </body> </html>

4.2用戶功能實現(xiàn)

4.2.1用戶類

4.2.2用戶類dao接口

4.2.3用戶類dao接口的實現(xiàn)方法（操作數(shù)據(jù)庫）

package bean;import utils.JDBCUtil;import java.sql.*;public class UserDaoImpl implements UserDao {@Overridepublic void adduser(User u) {//連接數(shù)據(jù)庫Connection conn = null;PreparedStatement psql=null;ResultSet rs=null;try {conn = JDBCUtil.getConnection();String sql = "insert into user value (?,?,?)";conn = JDBCUtil.getConnection();psql=conn.prepareStatement(sql);psql.setString(1,u.getName());psql.setString(2,u.getPassword());psql.setString(3,u.getAddr());int i = psql.executeUpdate();if(i>0){System.out.println("插入成功！");}else {System.out.println("插入失敗！");}conn.close();} catch (SQLException e) {e.printStackTrace();}finally {JDBCUtil.close(psql, conn);}}@Overridepublic User queryuser(String username) {//連接數(shù)據(jù)庫Connection conn = null;Statement statement=null;ResultSet rs=null;User u=new User();try {conn = JDBCUtil.getConnection();statement = conn.createStatement();String sql = "select * from user where name ='" + username + "'";rs=statement.executeQuery(sql);while(rs.next()){u.setName(rs.getString("name"));u.setPassword(rs.getString("password"));}rs.close();conn.close();return u;} catch (SQLException e) {e.printStackTrace();}finally {JDBCUtil.close(statement, conn);}return u;}public void updatepassword(String name,String password){Connection conn = null;PreparedStatement psql=null;ResultSet rs=null;try {conn = JDBCUtil.getConnection();psql=conn.prepareStatement("update user set password=? where name='" + name + "'");psql.setString(1,password);psql.execute();conn.close();} catch (SQLException e) {e.printStackTrace();}finally {JDBCUtil.close(psql, conn);}}public void updateaddr(String name,String addr){Connection conn = null;PreparedStatement psql=null;ResultSet rs=null;try {conn = JDBCUtil.getConnection();psql=conn.prepareStatement("update user set atm.user.address=? where name='" + name + "'");psql.setString(1,addr);psql.execute();conn.close();} catch (SQLException e) {e.printStackTrace();}finally {JDBCUtil.close(psql, conn);}} }

4.2.4用戶登錄頁面

4.2.5用戶登錄的service方法

import bean.User; import bean.UserDaoImpl;import javax.servlet.http.HttpSession; import java.io.IOException; import java.io.PrintWriter;@javax.servlet.annotation.WebServlet(name = "loginServlet",urlPatterns="/loginServlet") public class loginServlet extends javax.servlet.http.HttpServlet {protected void doPost(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws javax.servlet.ServletException, IOException {//設(shè)置編碼和響應(yīng)類型response.setContentType("text/html");response.setCharacterEncoding("utf-8");request.setCharacterEncoding("utf-8");HttpSession session =request.getSession(true);// 1.獲得用戶名和密碼// 2.驗證用戶名和密碼是否正確 "zhou" "123456"String username=request.getParameter("userName");String password=request.getParameter("userPasswd");//獲得響應(yīng)的輸出流PrintWriter pw=response.getWriter();UserDaoImpl dao =new UserDaoImpl();String name=dao.queryuser(username).getName();String passwd=dao.queryuser(username).getPassword();if(username.equals(name)&&password.equals(passwd)){session.setAttribute("user",username);request.getRequestDispatcher("/books.jsp").forward(request,response);}else {//驗證失敗pw.println("<font color='green'><h2>登錄失敗</h2></font>");}}protected void doGet(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws javax.servlet.ServletException, IOException {this.doPost(request,response);} }

4.2.5 用戶注冊實現(xiàn)的service方法

import utils.JDBCUtil; import java.sql.PreparedStatement; 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.Connection; import java.sql.SQLException; @WebServlet(name = "RegistServlet",urlPatterns = "/RegistServlet") public class RegistServlet extends HttpServlet {protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {request.setCharacterEncoding("utf-8");response.setContentType("html/text;charset=utf-8");//1.獲取數(shù)據(jù)String name = request.getParameter("name");String password = request.getParameter("password");String addr = request.getParameter("addr");//2.判斷用戶名不能為空if (name==null||"".equals(name)){System.out.println("用戶名不能為空!");String s = "用戶名不能為空";response.getWriter().write(s);}//連接數(shù)據(jù)庫，插入數(shù)據(jù)Connection conn = null;PreparedStatement psta = null;String sql = "insert into user value (?,?,?)";try {conn = JDBCUtil.getConnection();psta = conn.prepareStatement(sql);psta.setString(1,name);psta.setString(2,password);psta.setString(3,addr);int i = psta.executeUpdate();if(i>0){response.sendRedirect("userlogin.html");}else {System.out.println("插入失敗！");}} catch (SQLException e) {e.printStackTrace();}finally {JDBCUtil.close(psta,conn);}}protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {this.doPost(request,response);} }

4.2.6用戶登陸后的顯示的主界面

4.2.7用戶修改密碼的方法

4.2.8用戶修改收貨地址的方法

4.2.9用戶把圖書加入購物車的方法

4.2.10用戶把圖書從購物車中刪除的方法

4.2.11用戶購物車結(jié)算的方法

4.2.12用戶查看本用戶訂單的方法

4.3購物車類

4.3.1購物車類

4.3.2購物車類dao接口

4.3.3購物車類dao接口的實現(xiàn)方法（操作數(shù)據(jù)庫）

package bean;import utils.JDBCUtil; import java.sql.*; import java.util.ArrayList; import java.util.List;public class ShoppingcarDaoImpl implements ShoppingcarDao{public void DeleteBook(String id){//連接數(shù)據(jù)庫Connection conn = null;Statement statement=null;ResultSet rs=null;try {conn = JDBCUtil.getConnection();statement = conn.createStatement();String sql = "delete from shoppingcar where bookid="+id;statement.executeUpdate(sql);} catch (SQLException e) {e.printStackTrace();}finally {JDBCUtil.close(statement, conn);}}public Book querybook(String id){//連接數(shù)據(jù)庫Connection conn = null;Statement statement=null;ResultSet rs=null;Book b=new Book();try {conn = JDBCUtil.getConnection();statement = conn.createStatement();String sql = "select * from atm.book where bID="+id;rs=statement.executeQuery(sql);while(rs.next()){ShoppingCar s=new ShoppingCar();b.setName(rs.getString(1));b.setAuthor(rs.getString(2));b.setType(rs.getString(3));b.setId(rs.getString(4));b.setNumber(rs.getInt(5));b.setPrice(rs.getInt(6));}rs.close();conn.close();return b;} catch (SQLException e) {e.printStackTrace();}finally {JDBCUtil.close(statement, conn);}return b;}public void inserts(Book b,String username,int number){//連接數(shù)據(jù)庫Connection conn = null;PreparedStatement psql=null;ResultSet rs=null;try {conn = JDBCUtil.getConnection();psql= conn.prepareStatement("insert shoppingcar(username, bookid, bookname, bookprice, booknumber)"+"values(?,?,?,?,?)");psql.setString(1,username);psql.setString(2, b.getId());psql.setString(3, b.getName());psql.setInt(4,b.getPrice());psql.setInt(5,number);psql.execute();conn.close();}catch (SQLException e){e.printStackTrace();}finally {JDBCUtil.close(psql, conn);}}public void update(Book b,String username,int number){//連接數(shù)據(jù)庫Connection conn = null;PreparedStatement psql=null;ResultSet rs=null;List<ShoppingCar> list=new ArrayList<ShoppingCar>();try {conn = JDBCUtil.getConnection();psql= conn.prepareStatement("update shoppingcar set booknumber = ? where bookid=? and username in (select username from user where username='" + username + "')");//psql= conn.prepareStatement("update shoppingcar set booknumber = ? where bookid=?");//psql= conn.prepareStatement("update shoppingcar set booknumber = ? where (bookid=? and username='\" + username + \"')");//psql= conn.prepareStatement("update shoppingcar set booknumber = ? where bookid=? and username='\" + username + \"'");//psql= conn.prepareStatement("update shoppingcar set username,bookid,bookname,bookprice,booknumber=? where username=?");psql.setInt(1,number);psql.setString(2,b.getId());psql.execute();conn.close();} catch (SQLException e){e.printStackTrace();}finally {JDBCUtil.close(psql, conn);}}public ShoppingCar Queryone1(String username, String id) {//連接數(shù)據(jù)庫Connection conn = null;Statement statement=null;ResultSet rs=null;ShoppingCar s=new ShoppingCar();try {conn = JDBCUtil.getConnection();statement = conn.createStatement();String sql = "select * from shoppingcar where bookid="+id+" and username in (select username from shoppingcar where username='" + username + "')";rs=statement.executeQuery(sql);{while(rs.next()){s.setUsername(rs.getString(1));s.setBookid(rs.getString(2));s.setBookname(rs.getString(3));s.setBookprice(rs.getInt(4));s.setBooknumber(rs.getInt(5));}}rs.close();conn.close();return s;} catch (SQLException e){e.printStackTrace();}finally {JDBCUtil.close(statement, conn);}return s;}public int Queryone(String username, String id){//連接數(shù)據(jù)庫Connection conn = null;Statement statement=null;ResultSet rs=null;int number=1;try {conn = JDBCUtil.getConnection();statement = conn.createStatement();String sql = "select * from shoppingcar where username='" + username + "'" ;rs=statement.executeQuery(sql);{while(rs.next()){if(rs.getString(2).equals(id))number+=(rs.getInt(5));}}rs.close();conn.close();} catch (SQLException e){e.printStackTrace();}finally {JDBCUtil.close(statement, conn);}return number;}public int Queryone2(String username, String id){//連接數(shù)據(jù)庫Connection conn = null;Statement statement=null;ResultSet rs=null;int number=0;try {conn = JDBCUtil.getConnection();statement = conn.createStatement();String sql = "select * from shoppingcar where username='" + username + "'" ;rs=statement.executeQuery(sql);{while(rs.next()){if(rs.getString(2).equals(id))number=(rs.getInt(5));}}rs.close();conn.close();} catch (SQLException e){e.printStackTrace();}finally {JDBCUtil.close(statement, conn);}return number;}public List<ShoppingCar> Queryall(String username){//連接數(shù)據(jù)庫Connection conn = null;Statement statement=null;ResultSet rs=null;List<ShoppingCar> list=new ArrayList<ShoppingCar>();try {conn = JDBCUtil.getConnection();statement = conn.createStatement();String sql = "select * from shoppingcar where username='" + username + "'";rs=statement.executeQuery(sql);while(rs.next()){ShoppingCar s=new ShoppingCar();s.setUsername(rs.getString(1));s.setBookid(rs.getString(2));s.setBookname(rs.getString(3));s.setBookprice(rs.getInt(4));s.setBooknumber(rs.getInt(5));list.add(s);}rs.close();conn.close();return list;} catch (SQLException e) {e.printStackTrace();}finally {JDBCUtil.close(statement, conn);}return list;}public List<ShoppingCar> Queryallorderlist(){//連接數(shù)據(jù)庫Connection conn = null;Statement statement=null;ResultSet rs=null;List<ShoppingCar> list=new ArrayList<ShoppingCar>();try {conn = JDBCUtil.getConnection();statement = conn.createStatement();String sql = "select * from orderlist";rs=statement.executeQuery(sql);while(rs.next()){ShoppingCar s=new ShoppingCar();s.setUsername(rs.getString(1));System.out.println(s.getUsername());//調(diào)試s.setBookid(rs.getString(2));s.setBookname(rs.getString(3));s.setBookprice(rs.getDouble(4));s.setBooknumber(rs.getInt(5));list.add(s);}rs.close();conn.close();return list;} catch (SQLException e) {e.printStackTrace();}finally {JDBCUtil.close(statement, conn);}return list;}public void updatenumber(String id,int number){//連接數(shù)據(jù)庫Connection conn = null;PreparedStatement psql=null;ResultSet rs=null;try {conn = JDBCUtil.getConnection();psql= conn.prepareStatement("update book set bNumber = ? where bID=?");psql.setInt(1,number);psql.setString(2,id);psql.execute();conn.close();} catch (SQLException e){e.printStackTrace();}finally {JDBCUtil.close(psql, conn);}}public void drop(String username){//連接數(shù)據(jù)庫Connection conn = null;PreparedStatement psql=null;ResultSet rs=null;try {conn = JDBCUtil.getConnection();psql= conn.prepareStatement("delete from shoppingcar where username=?");psql.setString(1,username);psql.execute();conn.close();} catch (SQLException e){e.printStackTrace();} finally {JDBCUtil.close(psql, conn);}}//單個結(jié)算后把記錄寫入訂單表public void orderlist1(ShoppingCar s){//連接數(shù)據(jù)庫Connection conn = null;PreparedStatement psql=null;ResultSet rs=null;try {conn = JDBCUtil.getConnection();psql= conn.prepareStatement("insert orderlist(customer, bookId, bookName, bookPrice, bookNumber)"+"values(?,?,?,?,?)");psql.setString(1,s.getUsername());psql.setString(2, s.getBookid());psql.setString(3, s.getBookname());psql.setDouble(4,s.getBookprice());psql.setInt(5,s.getBooknumber());psql.execute();conn.close();} catch (SQLException e){e.printStackTrace();}finally {JDBCUtil.close(psql, conn);}}//結(jié)算后把購物車記錄寫入訂單表public void orderlist(List<ShoppingCar> list){//連接數(shù)據(jù)庫Connection conn = null;PreparedStatement psql=null;ResultSet rs=null;try {conn = JDBCUtil.getConnection();psql= conn.prepareStatement("insert orderlist(customer, bookId, bookName, bookPrice, bookNumber)"+"values(?,?,?,?,?)");for(ShoppingCar s:list){psql.setString(1,s.getUsername());psql.setString(2, s.getBookid());psql.setString(3, s.getBookname());psql.setDouble(4,s.getBookprice());psql.setInt(5,s.getBooknumber());psql.execute();}conn.close();} catch (SQLException e){e.printStackTrace();}finally {JDBCUtil.close(psql, conn);}}public List<ShoppingCar> showuserorderlist(String username){//連接數(shù)據(jù)庫Connection conn = null;Statement statement=null;ResultSet rs=null;List<ShoppingCar> list=new ArrayList<ShoppingCar>();try {conn = JDBCUtil.getConnection();statement = conn.createStatement();String sql = "select * from orderlist where customer='" + username + "'";rs=statement.executeQuery(sql);while(rs.next()){ShoppingCar s=new ShoppingCar();s.setBookid(rs.getString(2));s.setBookname(rs.getString(3));s.setBookprice(rs.getInt(4));s.setBooknumber(rs.getInt(5));list.add(s);}rs.close();conn.close();return list;} catch (SQLException e) {e.printStackTrace();}finally {JDBCUtil.close(statement, conn);}return list;}public void deleteonebook(String username,String id){//連接數(shù)據(jù)庫Connection conn = null;PreparedStatement psql=null;ResultSet rs=null;try {conn = JDBCUtil.getConnection();psql= conn.prepareStatement("delete from shoppingcar where username=? and bookid in (select bookid from shoppingcar where bookid=?)");psql.setString(1,username);psql.setString(2,id);psql.execute();conn.close();} catch (SQLException e){e.printStackTrace();} finally {JDBCUtil.close(psql, conn);}} }

4.3.4購物車顯示頁面

5.3圖書類及方法

5.3.1圖書類

5.3.2圖書類dao實現(xiàn)方法

5.3.3顯示所有圖書的方法

JDBCUtil工具類

package utils; import javax.sql.DataSource; import java.sql.*; import bean.User; import com.alibaba.druid.pool.DruidDataSourceFactory;import java.util.Properties; public class JDBCUtil {private static DataSource ds ;//加載配置文件，創(chuàng)建數(shù)據(jù)庫連接池static{try {Properties pro = new Properties();pro.load(JDBCUtil.class.getClassLoader().getResourceAsStream("druid.properties"));ds = DruidDataSourceFactory.createDataSource(pro);} catch (Exception e) {e.printStackTrace();}}//獲取連接池對象public static Connection getConnection() throws SQLException {return ds.getConnection();}//釋放資源public static void close(Statement sttm, Connection conn, ResultSet rs){if(sttm!=null){try {sttm.close();} catch (SQLException e) {e.printStackTrace();}}if(conn!=null){try {conn.close();} catch (SQLException e) {e.printStackTrace();}}if(rs!=null){try {rs.close();} catch (SQLException e) {e.printStackTrace();}}}public static void close(Statement sttm, Connection conn){close(sttm,conn,null);}public static DataSource getDs(){return ds;}}

sql文件

/* Navicat MySQL Data TransferSource Server : DESKTOP-7UO4BFR Source Server Version : 50725 Source Host : localhost:3306 Source Database : atmTarget Server Type : MYSQL Target Server Version : 50725 File Encoding : 65001Date: 2020-12-09 13:34:02 */SET FOREIGN_KEY_CHECKS=0;-- ---------------------------- -- Table structure for `account` -- ---------------------------- DROP TABLE IF EXISTS `account`; CREATE TABLE `account` (`UserID` varchar(20) NOT NULL,`UserName` varchar(15) DEFAULT NULL,`money` int(11) DEFAULT NULL,PRIMARY KEY (`UserID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- ---------------------------- -- Records of account -- ---------------------------- INSERT INTO `account` VALUES ('1815925066', '周文豪', '1400'); INSERT INTO `account` VALUES ('1815925067', '閆守建', '1400');-- ---------------------------- -- Table structure for `admin` -- ---------------------------- DROP TABLE IF EXISTS `admin`; CREATE TABLE `admin` (`name` varchar(20) NOT NULL,`password` varchar(20) DEFAULT NULL,PRIMARY KEY (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- ---------------------------- -- Records of admin -- ---------------------------- INSERT INTO `admin` VALUES ('俞敏洪', '123456'); INSERT INTO `admin` VALUES ('李嘉誠', '123456'); INSERT INTO `admin` VALUES ('王健林', '123456'); INSERT INTO `admin` VALUES ('馬云', '123456');-- ---------------------------- -- Table structure for `book` -- ---------------------------- DROP TABLE IF EXISTS `book`; CREATE TABLE `book` (`bName` varchar(20) NOT NULL,`bWtiter` varchar(15) DEFAULT NULL,`bType` varchar(15) DEFAULT NULL,`bID` varchar(15) DEFAULT NULL,`bNumber` int(11) DEFAULT NULL,`bPrice` int(11) DEFAULT NULL,PRIMARY KEY (`bName`),KEY `bID` (`bID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- ---------------------------- -- Records of book -- ---------------------------- INSERT INTO `book` VALUES ('三體', '劉慈欣', '科幻小說', '004', '29', '55'); INSERT INTO `book` VALUES ('三國演義', '羅貫中', '四大名著', '001', '21', '35'); INSERT INTO `book` VALUES ('假面山莊', '東野圭吾', '外國小說', '005', '25', '88'); INSERT INTO `book` VALUES ('昆蟲記', '法布爾', '文學(xué)', '033', '40', '40'); INSERT INTO `book` VALUES ('比索寓言', '比索', '寓言故事', '008', '2', '20'); INSERT INTO `book` VALUES ('活著', '余華', '現(xiàn)實主義小說', '006', '6', '36'); INSERT INTO `book` VALUES ('西游記', '吳承恩', '四大名著', '002', '23', '30'); INSERT INTO `book` VALUES ('鋼鐵是怎樣煉成的', '尼古拉·奧斯特洛夫斯基', '外國名著', '003', '34', '56');-- ---------------------------- -- Table structure for `orderlist` -- ---------------------------- DROP TABLE IF EXISTS `orderlist`; CREATE TABLE `orderlist` (`customer` varchar(20) DEFAULT NULL,`bookID` varchar(20) DEFAULT NULL,`bookName` varchar(20) DEFAULT NULL,`bookPrice` double(20,0) DEFAULT NULL,`bookNumber` int(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- ---------------------------- -- Records of orderlist -- ---------------------------- INSERT INTO `orderlist` VALUES ('Tom', '003', '鋼鐵是怎樣煉成的', '56', '1'); INSERT INTO `orderlist` VALUES ('Tom', '004', '三體', '55', '1'); INSERT INTO `orderlist` VALUES ('Tom', '005', '假面山莊', '88', '1'); INSERT INTO `orderlist` VALUES ('周文豪', '005', '假面山莊', '88', '1'); INSERT INTO `orderlist` VALUES ('周文豪', '005', '假面山莊', '88', '3'); INSERT INTO `orderlist` VALUES ('周文豪', '006', '活著', '36', '1'); INSERT INTO `orderlist` VALUES ('周文豪', '003', '鋼鐵是怎樣煉成的', '56', '2'); INSERT INTO `orderlist` VALUES ('周文豪', '001', '三國演義', '35', '1'); INSERT INTO `orderlist` VALUES ('周文豪', '004', '三體', '55', '1'); INSERT INTO `orderlist` VALUES ('周文豪', '004', '三體', '55', '1'); INSERT INTO `orderlist` VALUES ('張世強', '004', '三體', '55', '1'); INSERT INTO `orderlist` VALUES ('周文豪', '004', '三體', '55', '1'); INSERT INTO `orderlist` VALUES ('張世強', '001', '三國演義', '35', '1'); INSERT INTO `orderlist` VALUES ('周文豪', '004', '三體', '55', '1'); INSERT INTO `orderlist` VALUES ('周文豪', '001', '三國演義', '35', '1'); INSERT INTO `orderlist` VALUES ('周文豪', '006', '活著', '36', '1'); INSERT INTO `orderlist` VALUES ('周文豪', '004', '三體', '55', '1'); INSERT INTO `orderlist` VALUES ('周文豪', '006', '活著', '36', '1'); INSERT INTO `orderlist` VALUES ('周文豪', '002', '西游記', '30', '2'); INSERT INTO `orderlist` VALUES ('閆守建', '007', '安徒生童話', '15', '1'); INSERT INTO `orderlist` VALUES ('閆守建', '004', '三體', '55', '1'); INSERT INTO `orderlist` VALUES ('閆守建', '003', '鋼鐵是怎樣煉成的', '56', '1');-- ---------------------------- -- Table structure for `shoppingcar` -- ---------------------------- DROP TABLE IF EXISTS `shoppingcar`; CREATE TABLE `shoppingcar` (`username` varchar(20) DEFAULT NULL,`bookid` varchar(20) DEFAULT NULL,`bookname` varchar(20) DEFAULT NULL,`bookprice` int(20) DEFAULT NULL,`booknumber` int(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- ---------------------------- -- Records of shoppingcar -- ------------------------------ ---------------------------- -- Table structure for `user` -- ---------------------------- DROP TABLE IF EXISTS `user`; CREATE TABLE `user` (`name` varchar(15) NOT NULL,`password` varchar(20) DEFAULT NULL,`address` varchar(30) DEFAULT NULL,PRIMARY KEY (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- ---------------------------- -- Records of user -- ---------------------------- INSERT INTO `user` VALUES ('CATs', '123456', '奧術(shù)大師大奧所'); INSERT INTO `user` VALUES ('James', '123456', '奧術(shù)大師大奧所'); INSERT INTO `user` VALUES ('Tom', '123456', '南洋理工學(xué)院12號公寓205'); INSERT INTO `user` VALUES ('zhou', '123456', '南陽理工學(xué)院12#201'); INSERT INTO `user` VALUES ('侯明明', '123456', '南陽理工學(xué)院12#201'); INSERT INTO `user` VALUES ('周五', '123456', '是的撒多所多所多所多所多、'); INSERT INTO `user` VALUES ('周文豪', '123456', '南陽理工學(xué)院'); INSERT INTO `user` VALUES ('張世強', '123456', '南洋理工學(xué)院12號公寓201'); INSERT INTO `user` VALUES ('曹瑾', '123456', '南陽理工學(xué)院12#215'); INSERT INTO `user` VALUES ('朱命豪', '666666', '南陽理工學(xué)院'); INSERT INTO `user` VALUES ('王康', '123456', '河南大學(xué)'); INSERT INTO `user` VALUES ('米軍臣', '123456', '南陽理工學(xué)院12#212'); INSERT INTO `user` VALUES ('胡廣濤', '123456', '南陽理工學(xué)院12#218'); INSERT INTO `user` VALUES ('薛之謙', '123456', '南陽理工學(xué)院12#215'); INSERT INTO `user` VALUES ('鄺凱興', '123456', '南陽理工學(xué)院12#331'); INSERT INTO `user` VALUES ('閆守建', '123456', '南洋理工學(xué)院12號公寓201'); INSERT INTO `user` VALUES ('靳凌霄', '123456', '南陽理工學(xué)院12#201');-- ---------------------------- -- Table structure for `user2` -- ---------------------------- DROP TABLE IF EXISTS `user2`; CREATE TABLE `user2` (`id` varchar(11) NOT NULL,`name` varchar(20) DEFAULT NULL,`password` varchar(20) DEFAULT NULL,`address` varchar(20) DEFAULT NULL,PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- ---------------------------- -- Records of user2 -- ---------------------------- INSERT INTO `user2` VALUES ('1815925061', 'sdsdssdsd', '123456', '南陽理工'); INSERT INTO `user2` VALUES ('1815925066', '周文豪', '123456', '南陽理工'); INSERT INTO `user2` VALUES ('1815925110', '閆守建', '123456', '南陽理工'); INSERT INTO `user2` VALUES ('1815925116', '周六', '123456', '南陽理工'); INSERT INTO `user2` VALUES ('1815925118', '周五', '123456', '南陽理工'); INSERT INTO `user2` VALUES ('1815925167', '侯明明', '123456', '南陽理工');-- ---------------------------- -- Table structure for `users` -- ---------------------------- DROP TABLE IF EXISTS `users`; CREATE TABLE `users` (`UserID` varchar(20) NOT NULL,`Username` varchar(15) DEFAULT NULL,`UserPassword` varchar(20) DEFAULT NULL,PRIMARY KEY (`UserID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- ---------------------------- -- Records of users -- ---------------------------- INSERT INTO `users` VALUES ('1815925066', '周文豪', '123456'); INSERT INTO `users` VALUES ('1815925067', '閆守建', '123456');

數(shù)據(jù)庫連接池

Druid.properties

driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql:///atm?characterEncoding=utf8 username=root password=zwh19991219 initialSize=5 maxActive=10 maxWait=3000 maxIdle=8 minIdle=3

需要用到的jar包

需要項目源碼的可以點擊資源下載，也可以加我個人QQ:903985300私發(fā)給你

開發(fā)軟件IDEA，如果是ecplise的讀者，需要上網(wǎng)查找IDEA轉(zhuǎn)ecplise的方法

MYSQL5.6 和 MYSQL 8.0版本的都有

總結(jié)

以上是生活随笔為你收集整理的JavaWeb网上书城项目的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。