五,代码解析
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 block e.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 block e.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 block e.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 block e.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 block e.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 block e.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 block e.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 block e.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 block e.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 block e.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 block e.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 block e.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(); } } }