基于Servlet&Jsp的网上书店设计(二)

五，代码解析

5.1数据表JavaBean

Book.java

package com.beans;public class Book {public static final int PAGE_SIZE=6;    private int bookId;    private String name;    private String author;    private String publisher;    private String price;    public Book(){        }   public Book(int bookId, String name,String author,String publisher,String price){    this.bookId=bookId;    this.name=name;    this.author=author;    this.publisher=publisher;    this.price=price;     }   public int getBookId() {   return bookId;   }   public void setBookId(int bookId) {   this.bookId = bookId;   }   public String getName() {   return name;   }   public void setName(String name) {   this.name = name;   }   public String getAuthor() {   return author;   }   public void setAuthor(String author) {   this.author = author;   }   public String getPublisher() {   return publisher;   }   public void setPublisher(String publisher) {   this.publisher = publisher;   }   public String getPrice() {   return price;   }   public void setPrice(String price) {   this.price = price;   }  }

User.java

package com.beans;public class User {      private String ID;      private String Password;      private String Sex;      private String Phone;      private String Home;      private String Email;      private String Header;      public User(){            }      public User(String ID,String Password, String Sex,String Phone,String Home,String Email,String Header){      this.ID=ID;      this.Password=Password;      this.Sex=Sex;      this.Phone=Phone;      this.Home=Home;      this.Email=Email;       this.Header=Header;      }public String getID() {return ID;}public void setID(String iD) {ID = iD;}public String getPassword() {return Password;}public void setPassword(String password) {Password = password;}public String getSex() {return Sex;}public void setSex(String sex) {Sex = sex;}public String getPhone() {return Phone;}public void setPhone(String phone) {Phone = phone;}public String getHome() {return Home;}public void setHome(String home) {Home = home;}public String getEmail() {return Email;}public void setEmail(String email) {Email = email;}public String getHeader() {return Header;}public void setHeader(String header) {Header = header;}      }

CartBook.java是用户的购物车表

package com.beans;public class CartBook {    private int Id;    private String name;    private String price;    private int num;    private int total;    public CartBook(){        }   public CartBook(int Id, String name,String price,int num,int total){    this.Id=Id;    this.name=name;    this.price=price;     this.num=num;        this.total=total;    }   public int getBookId() {   return Id;   }   public void setBookId(int Id) {   this.Id = Id;   }   public String getName() {   return name;   }   public void setName(String name) {   this.name = name;   }   public String getPrice() {   return price;   }   public void setPrice(String price) {   this.price = price;   }public int getNum() {return num;}public void setNum(int num) {this.num = num;}public int getTotal() {return total;}public void setTotal(int total) {this.total = total;}    }

5.2操作数据库DAO

BookDao.java主要有两个功能,：返回图书列表，根据图书Id返回这本书的信息

 

package com.Dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import com.beans.Book;import com.tools.DBConnection;public class BookDao { DBConnection DB=new DBConnection();     Connection conn=null;     //返回所有图书列表     public List<Book> getBookList(){     List<Book> list=new ArrayList<Book>();     try {      conn=DB.getCon();          String sql="select * from books";  PreparedStatement pstm=conn.prepareStatement(sql);  ResultSet rs=pstm.executeQuery();  while(rs.next()){  Book book=new Book();  book.setBookId(rs.getInt(1));  book.setName(rs.getString(2));  book.setAuthor(rs.getString(3));  book.setPublisher(rs.getString(4));  book.setPrice(rs.getString(5));  list.add(book);  }  return list;} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}     return null;     }     //根据图书ID返回这本书的信息     public Book getBookById(int bookid){     Book book=new Book();     try {         conn=DB.getCon();        String sql="select * from books where BookID=?";  PreparedStatement pstm=conn.prepareStatement(sql);  pstm.setInt(1, bookid);  ResultSet rs=pstm.executeQuery();  while(rs.next())  {  book.setBookId(rs.getInt(1));  book.setName(rs.getString(2));  book.setAuthor(rs.getString(3));  book.setPublisher(rs.getString(4));  book.setPrice(rs.getString(5));  }  return book;} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}    return null;     }     }

CartDao.java主要是对购物车中的书籍进行增删查改操作

package com.Dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import com.beans.Book;import com.beans.CartBook;import com.tools.DBConnection;public class CartDao { DBConnection DB=new DBConnection();     Connection conn=null;     //获得所有已买书籍    public List<CartBook> getAllCartBooks(String userid){     conn = DB.getCon();//获取数据库连接       List<CartBook> list=new ArrayList<CartBook>();     System.out.println("已经进入函数");     if(conn!= null){         try {       System.out.println(userid);    String sql="select * from "+userid;    System.out.println(sql);PreparedStatement pstm=conn.prepareStatement(sql);ResultSet rs=pstm.executeQuery();while(rs.next()){CartBook cb=new CartBook();cb.setBookId(rs.getInt(1));cb.setName(rs.getString(2));cb.setPrice(rs.getString(3));cb.setNum(rs.getInt(4));cb.setTotal(rs.getInt(5));list.add(cb);}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}     }     return list;     }     //插入书籍    public boolean InsertBook(String userid,Book b){       conn = DB.getCon();//获取数据库连接           //System.out.println(userid);      if(conn!=null){      try {            String sql="insert into "+userid+" values(?,?,?,?,?)";      System.out.println(sql);  PreparedStatement pstm=conn.prepareStatement(sql);   pstm.setInt(1, b.getBookId());  //System.out.println(b.getName());pstm.setString(2, b.getName());  pstm.setString(3, b.getPrice());  pstm.setInt(4, 1);  pstm.setInt(5, Integer.parseInt(b.getPrice()));  System.out.println("语句没错");  pstm.executeUpdate();  return true;  } catch (SQLException e) {  // TODO Auto-generated catch block  e.printStackTrace();  }           }     return false;         }     //删除已买书籍    public boolean DeleteBook(String userid,int bookid){          conn = DB.getCon();//获取数据库连接       if(conn!=null){       try {        String sql="delete from "+userid+" where ID='"+bookid+"'";PreparedStatement pstm=conn.prepareStatement(sql);pstm.executeUpdate();return true;} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}     }     return false;     }     //修改数量   public  boolean ModifyNum(String userid,int bookid,int num){     conn = DB.getCon();//获取数据库连接       int total=0;     int oldnum=0;     if(conn!=null){    try{    //获得原来的数量     String sql3="select Num from "+userid+" where ID='"+bookid+"'";     PreparedStatement pstm3 = conn.prepareStatement(sql3);     ResultSet rs=pstm3.executeQuery();     if(rs.next()){     oldnum=rs.getInt(1);     }     if(oldnum==1){     if(num==-1){     return true;     }     }     //更新数量     String sql="update "+userid+" set Num='"+(num+oldnum)+"' where ID='"+bookid+"'";         PreparedStatement pstm = conn.prepareStatement(sql);         pstm.executeUpdate();          //计算总价         String sql1="select Price from "+userid+" where ID='"+bookid+"'";         PreparedStatement pstm1 = conn.prepareStatement(sql1);         ResultSet rs1=pstm1.executeQuery();         if(rs1.next()){         total=Integer.parseInt(rs1.getString("Price"))*(num+oldnum);         }         //修改总价         String sql2="update "+userid+" set Total='"+total+"' where ID='"+bookid+"'";         PreparedStatement pstm2 = conn.prepareStatement(sql2);         pstm2.executeUpdate();          } catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}            }     return false;     }   //书籍是否存在     public boolean isContainBook(String userid,String bookid){     conn = DB.getCon();//获取数据库连接       if(conn!=null){         try {         String sql="select * from "+userid+" where ID='"+bookid+"'"; PreparedStatement pstm = conn.prepareStatement(sql); ResultSet rs= pstm.executeQuery(); if(rs.next())//如果存在这本书 return true; } catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}          }else{     System.out.println("创建连接失败");     }          return false;     }     //返回书的总价格     public int getTotalPrice(String userid){     conn = DB.getCon();//获取数据库连接       if(conn!=null){            try {         String sql="select sum(Total) as total from "+userid; PreparedStatement pstm = conn.prepareStatement(sql); ResultSet rs= pstm.executeQuery(); if(rs.next()){ return rs.getInt(1); } } catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}     }     return 0;     }     //清空所有书籍     public boolean ClearCartBook(String userid){     conn = DB.getCon();//获取数据库连接       if(conn!=null){       try {        String sql="delete from "+userid;PreparedStatement pstm=conn.prepareStatement(sql);pstm.executeUpdate();return true;} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}     }     return false;         }}

PageQueryDao.java实现分页查询显示书籍

package com.Dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import com.beans.Book;import com.tools.DBConnection;public class PageQueryDao {    //分页查询所有书目信息        public List<Book> getPageList(int page){        List<Book> list=new ArrayList<Book>();        DBConnection DB=new DBConnection();        Connection conn=DB.getCon();        String sql="select * from books order by BookID asc limit ?,?";//limit关键字        try {        PreparedStatement pstm=conn.prepareStatement(sql);pstm.setInt(1, (page-1)*Book.PAGE_SIZE); //设置查询记录的开始位置pstm.setInt(2, Book.PAGE_SIZE);          //设置查询数据所返回的记录数ResultSet rs=pstm.executeQuery(); while(rs.next()){  Book book=new Book();  book.setBookId(rs.getInt(1));  book.setName(rs.getString(2));  book.setAuthor(rs.getString(3));  book.setPublisher(rs.getString(4));  book.setPrice(rs.getString(5));  list.add(book);  } rs.close(); pstm.close(); conn.close(); } catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}        return list;        }                //查询总记录数        public int FindCounts(){        int count=0;        DBConnection DB=new DBConnection();        Connection conn=DB.getCon();        String sql="select count(*) from books";        try {        PreparedStatement pstm=conn.prepareStatement(sql);ResultSet rs=pstm.executeQuery();if(rs.next()){//rs里就一个值count=rs.getInt(1);}rs.close();conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}        return count;                }}

UserDao.java

package com.Dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import com.beans.User;import com.tools.DBConnection;public class UserDao {     DBConnection DB=new DBConnection();     Connection conn=null;   //编写按用户名密码查询用户方法     public User getUser(String userID,String Password){     User user = new User();//创建JavaBean对象     conn = DB.getCon();//获取数据库连接     try {     String sql = "select * from users where ID = ? and Password = ?";//定义查询预处理语句     PreparedStatement statement = conn.prepareStatement(sql);//实例化PreparedStatement对象     statement.setString(1, userID);//设置预处理语句参数     statement.setString(2, Password);     ResultSet rest = statement.executeQuery();//执行预处理语句     while(rest.next()){     user.setID(rest.getString(1));//应用查询结果设置对象属性        user.setPassword(rest.getString(2));     user.setSex(rest.getString(3));     user.setPhone(rest.getString(4));     user.setHome(rest.getString(5));     user.setEmail(rest.getString(6));     user.setHeader(rest.getString(7));     }     } catch (SQLException e) {     e.printStackTrace();     }     return user;//返回查询结果     }     //根据用户id返回用户对象     public User getUser(String userid){     User user = new User();//创建JavaBean对象       conn = DB.getCon();//获取数据库连接        try {         String sql="select * from users where ID='"+userid+"'";  PreparedStatement pstm = conn.prepareStatement(sql);  ResultSet rest = pstm.executeQuery();//执行预处理语句     while(rest.next()){     user.setID(rest.getString(1));//应用查询结果设置对象属性        user.setPassword(rest.getString(2));     user.setSex(rest.getString(3));     user.setPhone(rest.getString(4));     user.setHome(rest.getString(5));     user.setEmail(rest.getString(6));     user.setHeader(rest.getString(7));     }} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}     return user;     }     public boolean isContainUser(String id){    try { conn = DB.getCon();//获取数据库连接     String sql="select * from users where ID=?";     PreparedStatement pstm; pstm = conn.prepareStatement(sql); pstm.setString(1, id); ResultSet rs=pstm.executeQuery(); if(rs.next()){ return true; } } catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} return false;         }     }

5.3连接数据库代码

package com.tools;import java.sql.*;public class DBConnection {private Connection con;//定义数据库连接类对象private PreparedStatement pstm;private String user="root";//连接数据库用户名private String password="123456";//连接数据库密码private String driverName="com.mysql.jdbc.Driver";//数据库驱动private String url="jdbc:mysql://localhost:3306/shoppingcart";//连接数据库的URL,后面的是为了防止插入数据 库出现乱码，?useUnicode=true&characterEncoding=UTF-8//构造函数public DBConnection(){}/**创建数据库连接*/public Connection getCon(){try{Class.forName("com.mysql.jdbc.Driver");}catch(ClassNotFoundException e){System.out.println("加载数据库驱动失败！");e.printStackTrace();}try {con=DriverManager.getConnection(url,user,password);//获取数据库连接} catch (SQLException e) {System.out.println("创建数据库连接失败！");con=null;e.printStackTrace();}return con;//返回数据库连接对象}/** *@功能：对数据库进行增、删、改、查操作 *@参数：sql为SQL语句；params为Object数组，里面存储的是为sql表示的SQL语句中"?"占位符赋值的数据  */public void doPstm(String sql,Object[] params){if(sql!=null&&!sql.equals("")){if(params==null)params=new Object[0];getCon();if(con!=null){try{System.out.println(sql);pstm=con.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);for(int i=0;i<params.length;i++){pstm.setObject(i+1,params[i]);}pstm.execute();}catch(SQLException e){System.out.println("doPstm()方法出错！");e.printStackTrace();}}}}public ResultSet getRs() throws SQLException{return pstm.getResultSet();}public int getCount() throws SQLException{return pstm.getUpdateCount();}public void closed(){try{if(pstm!=null)pstm.close();}catch(SQLException e){System.out.println("关闭pstm对象失败！");e.printStackTrace();}try{if(con!=null){con.close();}}catch(SQLException e){System.out.println("关闭con对象失败！");e.printStackTrace();}}}