基于ServletJsp的网上书店设计(二)

五，代码解析

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 blocke.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();}} }

总结

以上是生活随笔为你收集整理的基于ServletJsp的网上书店设计(二)的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。