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

五，代码解析

5.1数据表JavaBean

Book.Java

[java]  view plain  copy

 print ?

1. package com.beans;  
2.   
3. public class Book {  
4.     public static final int PAGE_SIZE=6;  
5.     private int bookId;  
6.     private String name;  
7.     private String author;  
8.     private String publisher;  
9.     private String price;  
10.     public Book(){  
11.           
12.     }  
13.    public Book(int bookId, String name,String author,String publisher,String price){  
14.         this.bookId=bookId;  
15.         this.name=name;  
16.         this.author=author;  
17.         this.publisher=publisher;  
18.         this.price=price;     
19.     }  
20.     public int getBookId() {  
21.         return bookId;  
22.     }  
23.     public void setBookId(int bookId) {  
24.         this.bookId = bookId;  
25.     }  
26.     public String getName() {  
27.         return name;  
28.     }     
29.     public void setName(String name) {  
30.         this.name = name;  
31.     }  
32.     public String getAuthor() {  
33.         return author;  
34.     }  
35.     public void setAuthor(String author) {  
36.         this.author = author;  
37.     }  
38.     public String getPublisher() {  
39.         return publisher;  
40.     }  
41.     public void setPublisher(String publisher) {  
42.         this.publisher = publisher;  
43.     }  
44.     public String getPrice() {  
45.         return price;  
46.     }  
47.     public void setPrice(String price) {  
48.         this.price = price;  
49.     }    
50. }  

User.java

[java]  view plain  copy

 print ?

1. package com.beans;  
2.   
3. public class User {  
4.       private String ID;  
5.       private String Password;  
6.       private String Sex;  
7.       private String Phone;  
8.       private String Home;  
9.       private String Email;  
10.       private String Header;  
11.       public User(){  
12.             
13.       }  
14.       public User(String ID,String Password, String Sex,String Phone,String Home,String Email,String Header){  
15.           this.ID=ID;  
16.           this.Password=Password;  
17.           this.Sex=Sex;  
18.           this.Phone=Phone;  
19.           this.Home=Home;  
20.           this.Email=Email;   
21.           this.Header=Header;  
22.       }  
23.     public String getID() {  
24.         return ID;  
25.     }  
26.     public void setID(String iD) {  
27.         ID = iD;  
28.     }  
29.     public String getPassword() {  
30.         return Password;  
31.     }  
32.     public void setPassword(String password) {  
33.         Password = password;  
34.     }  
35.     public String getSex() {  
36.         return Sex;  
37.     }  
38.     public void setSex(String sex) {  
39.         Sex = sex;  
40.     }  
41.     public String getPhone() {  
42.         return Phone;  
43.     }  
44.     public void setPhone(String phone) {  
45.         Phone = phone;  
46.     }  
47.     public String getHome() {  
48.         return Home;  
49.     }  
50.     public void setHome(String home) {  
51.         Home = home;  
52.     }  
53.     public String getEmail() {  
54.         return Email;  
55.     }  
56.     public void setEmail(String email) {  
57.         Email = email;  
58.     }  
59.     public String getHeader() {  
60.         return Header;  
61.     }  
62.     public void setHeader(String header) {  
63.         Header = header;  
64.     }  
65.         
66. }  

CartBook.java是用户的购物车表

[java]  view plain  copy

 print ?

1. package com.beans;  
2.   
3. public class CartBook {  
4.     private int Id;  
5.     private String name;  
6.     private String price;  
7.     private int num;  
8.     private int total;  
9.     public CartBook(){  
10.           
11.     }  
12.    public CartBook(int Id, String name,String price,int num,int total){  
13.         this.Id=Id;  
14.         this.name=name;  
15.         this.price=price;   
16.         this.num=num;  
17.         this.total=total;  
18.     }  
19.     public int getBookId() {  
20.         return Id;  
21.     }  
22.     public void setBookId(int Id) {  
23.         this.Id = Id;  
24.     }  
25.     public String getName() {  
26.         return name;  
27.     }     
28.     public void setName(String name) {  
29.         this.name = name;  
30.     }  
31.   
32.     public String getPrice() {  
33.         return price;  
34.     }  
35.     public void setPrice(String price) {  
36.         this.price = price;  
37.     }  
38.     public int getNum() {  
39.         return num;  
40.     }  
41.     public void setNum(int num) {  
42.         this.num = num;  
43.     }  
44.     public int getTotal() {  
45.         return total;  
46.     }  
47.     public void setTotal(int total) {  
48.         this.total = total;  
49.     }   
50.       
51. }  

5.2操作数据库DAO

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

 

[java]  view plain  copy

 print ?

1. package com.Dao;  
2.   
3. import java.sql.Connection;  
4. import java.sql.PreparedStatement;  
5. import java.sql.ResultSet;  
6. import java.sql.SQLException;  
7. import java.util.ArrayList;  
8. import java.util.List;  
9.   
10. import com.beans.Book;  
11. import com.tools.DBConnection;  
12.   
13. public class BookDao {  
14.      DBConnection DB=new DBConnection();  
15.      Connection conn=null;  
16.      //返回所有图书列表  
17.      public List<Book> getBookList(){  
18.          List<Book> list=new ArrayList<Book>();  
19.          try {  
20.               conn=DB.getCon();  
21.               String sql="select * from books";  
22.               PreparedStatement pstm=conn.prepareStatement(sql);  
23.               ResultSet rs=pstm.executeQuery();  
24.               while(rs.next()){  
25.                   Book book=new Book();  
26.                   book.setBookId(rs.getInt(1));  
27.                   book.setName(rs.getString(2));  
28.                   book.setAuthor(rs.getString(3));  
29.                   book.setPublisher(rs.getString(4));  
30.                   book.setPrice(rs.getString(5));  
31.                   list.add(book);  
32.               }  
33.               return list;  
34.         } catch (SQLException e) {  
35.             // TODO Auto-generated catch block  
36.             e.printStackTrace();  
37.         }  
38.          return null;  
39.      }  
40.      //根据图书ID返回这本书的信息  
41.      public Book getBookById(int bookid){  
42.          Book book=new Book();  
43.          try {  
44.               conn=DB.getCon();  
45.               String sql="select * from books where BookID=?";  
46.               PreparedStatement pstm=conn.prepareStatement(sql);  
47.               pstm.setInt(1, bookid);  
48.               ResultSet rs=pstm.executeQuery();  
49.               while(rs.next())  
50.               {  
51.                   book.setBookId(rs.getInt(1));  
52.                   book.setName(rs.getString(2));  
53.                   book.setAuthor(rs.getString(3));  
54.                   book.setPublisher(rs.getString(4));  
55.                   book.setPrice(rs.getString(5));                 
56.               }  
57.               return book;  
58.         } catch (SQLException e) {  
59.             // TODO Auto-generated catch block  
60.             e.printStackTrace();  
61.         }             
62.      return null;  
63.      }  
64.        
65. }  

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

[java]  view plain  copy

 print ?

1. package com.Dao;  
2.   
3. import java.sql.Connection;  
4. import java.sql.PreparedStatement;  
5. import java.sql.ResultSet;  
6. import java.sql.SQLException;  
7. import java.util.ArrayList;  
8. import java.util.List;  
9.   
10. import com.beans.Book;  
11. import com.beans.CartBook;  
12. import com.tools.DBConnection;  
13.   
14. public class CartDao {  
15.      DBConnection DB=new DBConnection();  
16.      Connection conn=null;  
17.      //获得所有已买书籍  
18.     public List<CartBook> getAllCartBooks(String userid){  
19.          conn = DB.getCon();    //获取数据库连接    
20.          List<CartBook> list=new ArrayList<CartBook>();  
21.          System.out.println("已经进入函数");  
22.          if(conn!= null){             
23.              try {                
24.                 System.out.println(userid);  
25.                 String sql="select * from "+userid;  
26.                 System.out.println(sql);  
27.                 PreparedStatement pstm=conn.prepareStatement(sql);  
28.                 ResultSet rs=pstm.executeQuery();  
29.                 while(rs.next()){  
30.                     CartBook cb=new CartBook();  
31.                     cb.setBookId(rs.getInt(1));  
32.                     cb.setName(rs.getString(2));  
33.                     cb.setPrice(rs.getString(3));  
34.                     cb.setNum(rs.getInt(4));  
35.                     cb.setTotal(rs.getInt(5));  
36.                     list.add(cb);  
37.                 }                 
38.                   
39.             } catch (SQLException e) {  
40.                 // TODO Auto-generated catch block  
41.                 e.printStackTrace();  
42.             }  
43.          }  
44.          return list;  
45.      }  
46.      //插入书籍  
47.     public boolean InsertBook(String userid,Book b){    
48.          conn = DB.getCon();            //获取数据库连接         
49.          //System.out.println(userid);  
50.       if(conn!=null){  
51.           try {   
52.                
53.             String sql="insert into "+userid+" values(?,?,?,?,?)";  
54.             System.out.println(sql);  
55.             PreparedStatement pstm=conn.prepareStatement(sql);            
56.             pstm.setInt(1, b.getBookId());  
57.             //System.out.println(b.getName());  
58.             pstm.setString(2, b.getName());           
59.             pstm.setString(3, b.getPrice());  
60.             pstm.setInt(4, 1);  
61.             pstm.setInt(5, Integer.parseInt(b.getPrice()));  
62.             System.out.println("语句没错");  
63.             pstm.executeUpdate();  
64.             return true;              
65.         } catch (SQLException e) {  
66.             // TODO Auto-generated catch block  
67.             e.printStackTrace();  
68.         }         
69.       }  
70.       return false;       
71.      }  
72.      //删除已买书籍  
73.     public boolean DeleteBook(String userid,int bookid){           
74.          conn = DB.getCon();            //获取数据库连接    
75.          if(conn!=null){          
76.             try {  
77.                   
78.                 String sql="delete from "+userid+" where ID='"+bookid+"'";  
79.                 PreparedStatement pstm=conn.prepareStatement(sql);  
80.                 pstm.executeUpdate();  
81.                 return true;  
82.                   
83.             } catch (SQLException e) {  
84.                 // TODO Auto-generated catch block  
85.                 e.printStackTrace();  
86.             }  
87.          }  
88.          return false;  
89.      }  
90.      //修改数量  
91.    public  boolean ModifyNum(String userid,int bookid,int num){  
92.          conn = DB.getCon();            //获取数据库连接    
93.          int total=0;  
94.          int oldnum=0;  
95.          if(conn!=null){  
96.             try{  
97.                 //获得原来的数量  
98.                  String sql3="select Num from "+userid+" where ID='"+bookid+"'";  
99.                  PreparedStatement pstm3 = conn.prepareStatement(sql3);  
100.                  ResultSet rs=pstm3.executeQuery();  
101.                  if(rs.next()){  
102.                      oldnum=rs.getInt(1);  
103.                  }  
104.                  if(oldnum==1){  
105.                      if(num==-1){  
106.                          return true;  
107.                      }  
108.                  }  
109.                  //更新数量  
110.                  String sql="update "+userid+" set Num='"+(num+oldnum)+"' where ID='"+bookid+"'";  
111.                  PreparedStatement pstm = conn.prepareStatement(sql);  
112.                  pstm.executeUpdate();  
113.                   //计算总价  
114.                  String sql1="select Price from "+userid+" where ID='"+bookid+"'";  
115.                  PreparedStatement pstm1 = conn.prepareStatement(sql1);  
116.                  ResultSet rs1=pstm1.executeQuery();  
117.                  if(rs1.next()){  
118.                      total=Integer.parseInt(rs1.getString("Price"))*(num+oldnum);  
119.                  }  
120.                  //修改总价  
121.                  String sql2="update "+userid+" set Total='"+total+"' where ID='"+bookid+"'";  
122.                  PreparedStatement pstm2 = conn.prepareStatement(sql2);  
123.                  pstm2.executeUpdate();   
124.                    
125.             } catch (SQLException e) {  
126.                 // TODO Auto-generated catch block  
127.                 e.printStackTrace();  
128.             }                        
129.          }  
130.          return false;  
131.      }  
132.    //书籍是否存在  
133.      public boolean isContainBook(String userid,String bookid){  
134.          conn = DB.getCon();            //获取数据库连接    
135.          if(conn!=null){           
136.              try {                 
137.                  String sql="select * from "+userid+" where ID='"+bookid+"'";  
138.                  PreparedStatement pstm = conn.prepareStatement(sql);  
139.                  ResultSet rs= pstm.executeQuery();  
140.                  if(rs.next())//如果存在这本书  
141.                      return true;  
142.                    
143.             } catch (SQLException e) {  
144.                 // TODO Auto-generated catch block  
145.                 e.printStackTrace();  
146.             }  
147.                
148.          }else{  
149.              System.out.println("创建连接失败");  
150.          }         
151.          return false;  
152.      }  
153.      //返回书的总价格  
154.      public int getTotalPrice(String userid){  
155.          conn = DB.getCon();            //获取数据库连接    
156.          if(conn!=null){      
157.               
158.              try {  
159.                   
160.                  String sql="select sum(Total) as total from "+userid;  
161.                  PreparedStatement pstm = conn.prepareStatement(sql);  
162.                  ResultSet rs= pstm.executeQuery();  
163.                  if(rs.next()){  
164.                      return rs.getInt(1);  
165.                  }  
166.                    
167.             } catch (SQLException e) {  
168.                 // TODO Auto-generated catch block  
169.                 e.printStackTrace();  
170.             }  
171.          }  
172.          return 0;  
173.      }  
174.      //清空所有书籍  
175.      public boolean ClearCartBook(String userid){  
176.          conn = DB.getCon();            //获取数据库连接    
177.          if(conn!=null){          
178.             try {  
179.                   
180.                 String sql="delete from "+userid;  
181.                 PreparedStatement pstm=conn.prepareStatement(sql);  
182.                 pstm.executeUpdate();  
183.                 return true;  
184.                   
185.             } catch (SQLException e) {  
186.                 // TODO Auto-generated catch block  
187.                 e.printStackTrace();  
188.             }  
189.          }  
190.          return false;  
191.           
192.      }  
193. }  

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

[java]  view plain  copy

 print ?

1. package com.Dao;  
2.   
3. import java.sql.Connection;  
4. import java.sql.PreparedStatement;  
5. import java.sql.ResultSet;  
6. import java.sql.SQLException;  
7. import java.util.ArrayList;  
8. import java.util.List;  
9.   
10. import com.beans.Book;  
11. import com.tools.DBConnection;  
12.   
13. public class PageQueryDao {  
14.         //分页查询所有书目信息  
15.         public List<Book> getPageList(int page){  
16.             List<Book> list=new ArrayList<Book>();  
17.             DBConnection DB=new DBConnection();  
18.             Connection conn=DB.getCon();  
19.             String sql="select * from books order by BookID asc limit ?,?";//limit关键字  
20.             try {  
21.                   
22.                 PreparedStatement pstm=conn.prepareStatement(sql);  
23.                 pstm.setInt(1, (page-1)*Book.PAGE_SIZE); //设置查询记录的开始位置  
24.                 pstm.setInt(2, Book.PAGE_SIZE);          //设置查询数据所返回的记录数  
25.                 ResultSet rs=pstm.executeQuery();  
26.                  while(rs.next()){  
27.                       Book book=new Book();  
28.                       book.setBookId(rs.getInt(1));  
29.                       book.setName(rs.getString(2));  
30.                       book.setAuthor(rs.getString(3));  
31.                       book.setPublisher(rs.getString(4));  
32.                       book.setPrice(rs.getString(5));  
33.                       list.add(book);  
34.                   }  
35.                  rs.close();  
36.                  pstm.close();  
37.                  conn.close();  
38.                    
39.             } catch (SQLException e) {  
40.                 // TODO Auto-generated catch block  
41.                 e.printStackTrace();  
42.             }  
43.             return list;  
44.         }  
45.           
46.         //查询总记录数  
47.         public int FindCounts(){  
48.             int count=0;  
49.             DBConnection DB=new DBConnection();  
50.             Connection conn=DB.getCon();  
51.             String sql="select count(*) from books";  
52.             try {  
53.                   
54.                 PreparedStatement pstm=conn.prepareStatement(sql);  
55.                 ResultSet rs=pstm.executeQuery();  
56.                 if(rs.next()){//rs里就一个值  
57.                     count=rs.getInt(1);                   
58.                 }  
59.                 rs.close();  
60.                 conn.close();  
61.             } catch (SQLException e) {  
62.                 // TODO Auto-generated catch block  
63.                 e.printStackTrace();  
64.             }  
65.             return count;  
66.               
67.         }  
68.       
69. }  

UserDao.java

[java]  view plain  copy

 print ?

1. package com.Dao;  
2.   
3. import java.sql.Connection;  
4. import java.sql.PreparedStatement;  
5. import java.sql.ResultSet;  
6. import java.sql.SQLException;  
7.   
8. import com.beans.User;  
9. import com.tools.DBConnection;  
10.   
11. public class UserDao {  
12.      DBConnection DB=new DBConnection();  
13.      Connection conn=null;  
14.    //编写按用户名密码查询用户方法  
15.      public User getUser(String userID,String Password){  
16.         User user = new User();             //创建JavaBean对象  
17.         conn = DB.getCon();         //获取数据库连接  
18.         try {  
19.             String sql = "select * from users where ID = ? and Password = ?";   //定义查询预处理语句  
20.             PreparedStatement statement = conn.prepareStatement(sql);       //实例化PreparedStatement对象  
21.             statement.setString(1, userID);         //设置预处理语句参数  
22.             statement.setString(2, Password);  
23.             ResultSet rest = statement.executeQuery();  //执行预处理语句  
24.             while(rest.next()){  
25.                 user.setID(rest.getString(1));              //应用查询结果设置对象属性                
26.                 user.setPassword(rest.getString(2));  
27.                 user.setSex(rest.getString(3));  
28.                 user.setPhone(rest.getString(4));  
29.                 user.setHome(rest.getString(5));  
30.                 user.setEmail(rest.getString(6));  
31.                 user.setHeader(rest.getString(7));  
32.             }  
33.         } catch (SQLException e) {            
34.             e.printStackTrace();  
35.         }         
36.         return user;                        //返回查询结果  
37.      }    
38.      //根据用户id返回用户对象  
39.      public User getUser(String userid){  
40.          User user = new User();                //创建JavaBean对象  
41.          conn = DB.getCon();            //获取数据库连接  
42.         try {  
43.              String sql="select * from users where ID='"+userid+"'";  
44.               PreparedStatement pstm = conn.prepareStatement(sql);  
45.               ResultSet rest = pstm.executeQuery(); //执行预处理语句  
46.                 while(rest.next()){  
47.                     user.setID(rest.getString(1));              //应用查询结果设置对象属性                
48.                     user.setPassword(rest.getString(2));  
49.                     user.setSex(rest.getString(3));  
50.                     user.setPhone(rest.getString(4));  
51.                     user.setHome(rest.getString(5));  
52.                     user.setEmail(rest.getString(6));  
53.                     user.setHeader(rest.getString(7));  
54.                 }  
55.         } catch (SQLException e) {  
56.             // TODO Auto-generated catch block  
57.             e.printStackTrace();  
58.         }     
59.          return user;  
60.      }  
61.      public boolean isContainUser(String id){  
62.           
63.         try {  
64.              conn = DB.getCon();            //获取数据库连接  
65.              String sql="select * from users where ID=?";  
66.              PreparedStatement pstm;  
67.              pstm = conn.prepareStatement(sql);  
68.              pstm.setString(1, id);  
69.              ResultSet rs=pstm.executeQuery();  
70.              if(rs.next()){  
71.                  return true;  
72.              }  
73.                
74.         } catch (SQLException e) {  
75.             // TODO Auto-generated catch block  
76.             e.printStackTrace();  
77.         }  
78.          return false;         
79.      }  
80.        
81. }  

5.3连接数据库代码

[java]  view plain  copy

 print ?

1. package com.tools;  
2. import java.sql.*;  
3. public class DBConnection {   
4. private Connection con;         //定义数据库连接类对象  
5. private PreparedStatement pstm;   
6. private String user="root";     //连接数据库用户名  
7. private String password="123456";       //连接数据库密码  
8. private String driverName="com.mysql.jdbc.Driver";  //数据库驱动  
9. private String url="jdbc:mysql://localhost:3306/shoppingcart";        
10. //连接数据库的URL,后面的是为了防止插入数据 库出现乱码，?useUnicode=true&characterEncoding=UTF-8  
11. //构造函数  
12. public DBConnection(){  
13.       
14. }  
15. /**创建数据库连接*/  
16. public Connection getCon(){  
17.     try{  
18.         Class.forName("com.mysql.jdbc.Driver");  
19.     }catch(ClassNotFoundException e){  
20.         System.out.println("加载数据库驱动失败！");  
21.         e.printStackTrace();  
22.     }  
23.     try {  
24.         con=DriverManager.getConnection(url,user,password);     //获取数据库连接  
25.     } catch (SQLException e) {  
26.         System.out.println("创建数据库连接失败！");  
27.         con=null;  
28.         e.printStackTrace();  
29.     }  
30.     return con;                 //返回数据库连接对象  
31. }     
32. /** 
33.  *@功能：对数据库进行增、删、改、查操作 
34.  *@参数：sql为SQL语句；params为Object数组，里面存储的是为sql表示的SQL语句中"?"占位符赋值的数据  
35.  */  
36.     public void doPstm(String sql,Object[] params){  
37.         if(sql!=null&&!sql.equals("")){  
38.             if(params==null)  
39.                 params=new Object[0];             
40.             getCon();  
41.             if(con!=null){  
42.                 try{          
43.                     System.out.println(sql);  
44.                     pstm=con.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);  
45.                     for(int i=0;i<params.length;i++){  
46.                         pstm.setObject(i+1,params[i]);  
47.                     }  
48.                     pstm.execute();  
49.                 }catch(SQLException e){  
50.                     System.out.println("doPstm()方法出错！");  
51.                     e.printStackTrace();  
52.                 }                 
53.             }             
54.         }  
55.     }     
56.     public ResultSet getRs() throws SQLException{  
57.         return pstm.getResultSet();       
58.     }  
59.     public int getCount() throws SQLException{  
60.         return pstm.getUpdateCount();         
61.     }  
62.     public void closed(){  
63.         try{  
64.             if(pstm!=null)  
65.                 pstm.close();             
66.         }catch(SQLException e){  
67.             System.out.println("关闭pstm对象失败！");  
68.             e.printStackTrace();  
69.         }  
70.         try{  
71.             if(con!=null){  
72.                 con.close();  
73.             }  
74.         }catch(SQLException e){  
75.             System.out.println("关闭con对象失败！");  
76.             e.printStackTrace();  
77.         }  
78.     }  
79. }  

五，代码解析

5.1数据表JavaBean

Book.Java

[java]  view plain  copy

 print ?

1. package com.beans;  
2.   
3. public class Book {  
4.     public static final int PAGE_SIZE=6;  
5.     private int bookId;  
6.     private String name;  
7.     private String author;  
8.     private String publisher;  
9.     private String price;  
10.     public Book(){  
11.           
12.     }  
13.    public Book(int bookId, String name,String author,String publisher,String price){  
14.         this.bookId=bookId;  
15.         this.name=name;  
16.         this.author=author;  
17.         this.publisher=publisher;  
18.         this.price=price;     
19.     }  
20.     public int getBookId() {  
21.         return bookId;  
22.     }  
23.     public void setBookId(int bookId) {  
24.         this.bookId = bookId;  
25.     }  
26.     public String getName() {  
27.         return name;  
28.     }     
29.     public void setName(String name) {  
30.         this.name = name;  
31.     }  
32.     public String getAuthor() {  
33.         return author;  
34.     }  
35.     public void setAuthor(String author) {  
36.         this.author = author;  
37.     }  
38.     public String getPublisher() {  
39.         return publisher;  
40.     }  
41.     public void setPublisher(String publisher) {  
42.         this.publisher = publisher;  
43.     }  
44.     public String getPrice() {  
45.         return price;  
46.     }  
47.     public void setPrice(String price) {  
48.         this.price = price;  
49.     }    
50. }  

User.java

[java]  view plain  copy

 print ?

1. package com.beans;  
2.   
3. public class User {  
4.       private String ID;  
5.       private String Password;  
6.       private String Sex;  
7.       private String Phone;  
8.       private String Home;  
9.       private String Email;  
10.       private String Header;  
11.       public User(){  
12.             
13.       }  
14.       public User(String ID,String Password, String Sex,String Phone,String Home,String Email,String Header){  
15.           this.ID=ID;  
16.           this.Password=Password;  
17.           this.Sex=Sex;  
18.           this.Phone=Phone;  
19.           this.Home=Home;  
20.           this.Email=Email;   
21.           this.Header=Header;  
22.       }  
23.     public String getID() {  
24.         return ID;  
25.     }  
26.     public void setID(String iD) {  
27.         ID = iD;  
28.     }  
29.     public String getPassword() {  
30.         return Password;  
31.     }  
32.     public void setPassword(String password) {  
33.         Password = password;  
34.     }  
35.     public String getSex() {  
36.         return Sex;  
37.     }  
38.     public void setSex(String sex) {  
39.         Sex = sex;  
40.     }  
41.     public String getPhone() {  
42.         return Phone;  
43.     }  
44.     public void setPhone(String phone) {  
45.         Phone = phone;  
46.     }  
47.     public String getHome() {  
48.         return Home;  
49.     }  
50.     public void setHome(String home) {  
51.         Home = home;  
52.     }  
53.     public String getEmail() {  
54.         return Email;  
55.     }  
56.     public void setEmail(String email) {  
57.         Email = email;  
58.     }  
59.     public String getHeader() {  
60.         return Header;  
61.     }  
62.     public void setHeader(String header) {  
63.         Header = header;  
64.     }  
65.         
66. }  

CartBook.java是用户的购物车表

[java]  view plain  copy

 print ?

1. package com.beans;  
2.   
3. public class CartBook {  
4.     private int Id;  
5.     private String name;  
6.     private String price;  
7.     private int num;  
8.     private int total;  
9.     public CartBook(){  
10.           
11.     }  
12.    public CartBook(int Id, String name,String price,int num,int total){  
13.         this.Id=Id;  
14.         this.name=name;  
15.         this.price=price;   
16.         this.num=num;  
17.         this.total=total;  
18.     }  
19.     public int getBookId() {  
20.         return Id;  
21.     }  
22.     public void setBookId(int Id) {  
23.         this.Id = Id;  
24.     }  
25.     public String getName() {  
26.         return name;  
27.     }     
28.     public void setName(String name) {  
29.         this.name = name;  
30.     }  
31.   
32.     public String getPrice() {  
33.         return price;  
34.     }  
35.     public void setPrice(String price) {  
36.         this.price = price;  
37.     }  
38.     public int getNum() {  
39.         return num;  
40.     }  
41.     public void setNum(int num) {  
42.         this.num = num;  
43.     }  
44.     public int getTotal() {  
45.         return total;  
46.     }  
47.     public void setTotal(int total) {  
48.         this.total = total;  
49.     }   
50.       
51. }  

5.2操作数据库DAO

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

 

[java]  view plain  copy

 print ?

1. package com.Dao;  
2.   
3. import java.sql.Connection;  
4. import java.sql.PreparedStatement;  
5. import java.sql.ResultSet;  
6. import java.sql.SQLException;  
7. import java.util.ArrayList;  
8. import java.util.List;  
9.   
10. import com.beans.Book;  
11. import com.tools.DBConnection;  
12.   
13. public class BookDao {  
14.      DBConnection DB=new DBConnection();  
15.      Connection conn=null;  
16.      //返回所有图书列表  
17.      public List<Book> getBookList(){  
18.          List<Book> list=new ArrayList<Book>();  
19.          try {  
20.               conn=DB.getCon();  
21.               String sql="select * from books";  
22.               PreparedStatement pstm=conn.prepareStatement(sql);  
23.               ResultSet rs=pstm.executeQuery();  
24.               while(rs.next()){  
25.                   Book book=new Book();  
26.                   book.setBookId(rs.getInt(1));  
27.                   book.setName(rs.getString(2));  
28.                   book.setAuthor(rs.getString(3));  
29.                   book.setPublisher(rs.getString(4));  
30.                   book.setPrice(rs.getString(5));  
31.                   list.add(book);  
32.               }  
33.               return list;  
34.         } catch (SQLException e) {  
35.             // TODO Auto-generated catch block  
36.             e.printStackTrace();  
37.         }  
38.          return null;  
39.      }  
40.      //根据图书ID返回这本书的信息  
41.      public Book getBookById(int bookid){  
42.          Book book=new Book();  
43.          try {  
44.               conn=DB.getCon();  
45.               String sql="select * from books where BookID=?";  
46.               PreparedStatement pstm=conn.prepareStatement(sql);  
47.               pstm.setInt(1, bookid);  
48.               ResultSet rs=pstm.executeQuery();  
49.               while(rs.next())  
50.               {  
51.                   book.setBookId(rs.getInt(1));  
52.                   book.setName(rs.getString(2));  
53.                   book.setAuthor(rs.getString(3));  
54.                   book.setPublisher(rs.getString(4));  
55.                   book.setPrice(rs.getString(5));                 
56.               }  
57.               return book;  
58.         } catch (SQLException e) {  
59.             // TODO Auto-generated catch block  
60.             e.printStackTrace();  
61.         }             
62.      return null;  
63.      }  
64.        
65. }  

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

[java]  view plain  copy

 print ?

1. package com.Dao;  
2.   
3. import java.sql.Connection;  
4. import java.sql.PreparedStatement;  
5. import java.sql.ResultSet;  
6. import java.sql.SQLException;  
7. import java.util.ArrayList;  
8. import java.util.List;  
9.   
10. import com.beans.Book;  
11. import com.beans.CartBook;  
12. import com.tools.DBConnection;  
13.   
14. public class CartDao {  
15.      DBConnection DB=new DBConnection();  
16.      Connection conn=null;  
17.      //获得所有已买书籍  
18.     public List<CartBook> getAllCartBooks(String userid){  
19.          conn = DB.getCon();    //获取数据库连接    
20.          List<CartBook> list=new ArrayList<CartBook>();  
21.          System.out.println("已经进入函数");  
22.          if(conn!= null){             
23.              try {                
24.                 System.out.println(userid);  
25.                 String sql="select * from "+userid;  
26.                 System.out.println(sql);  
27.                 PreparedStatement pstm=conn.prepareStatement(sql);  
28.                 ResultSet rs=pstm.executeQuery();  
29.                 while(rs.next()){  
30.                     CartBook cb=new CartBook();  
31.                     cb.setBookId(rs.getInt(1));  
32.                     cb.setName(rs.getString(2));  
33.                     cb.setPrice(rs.getString(3));  
34.                     cb.setNum(rs.getInt(4));  
35.                     cb.setTotal(rs.getInt(5));  
36.                     list.add(cb);  
37.                 }                 
38.                   
39.             } catch (SQLException e) {  
40.                 // TODO Auto-generated catch block  
41.                 e.printStackTrace();  
42.             }  
43.          }  
44.          return list;  
45.      }  
46.      //插入书籍  
47.     public boolean InsertBook(String userid,Book b){    
48.          conn = DB.getCon();            //获取数据库连接         
49.          //System.out.println(userid);  
50.       if(conn!=null){  
51.           try {   
52.                
53.             String sql="insert into "+userid+" values(?,?,?,?,?)";  
54.             System.out.println(sql);  
55.             PreparedStatement pstm=conn.prepareStatement(sql);            
56.             pstm.setInt(1, b.getBookId());  
57.             //System.out.println(b.getName());  
58.             pstm.setString(2, b.getName());           
59.             pstm.setString(3, b.getPrice());  
60.             pstm.setInt(4, 1);  
61.             pstm.setInt(5, Integer.parseInt(b.getPrice()));  
62.             System.out.println("语句没错");  
63.             pstm.executeUpdate();  
64.             return true;              
65.         } catch (SQLException e) {  
66.             // TODO Auto-generated catch block  
67.             e.printStackTrace();  
68.         }         
69.       }  
70.       return false;       
71.      }  
72.      //删除已买书籍  
73.     public boolean DeleteBook(String userid,int bookid){           
74.          conn = DB.getCon();            //获取数据库连接    
75.          if(conn!=null){          
76.             try {  
77.                   
78.                 String sql="delete from "+userid+" where ID='"+bookid+"'";  
79.                 PreparedStatement pstm=conn.prepareStatement(sql);  
80.                 pstm.executeUpdate();  
81.                 return true;  
82.                   
83.             } catch (SQLException e) {  
84.                 // TODO Auto-generated catch block  
85.                 e.printStackTrace();  
86.             }  
87.          }  
88.          return false;  
89.      }  
90.      //修改数量  
91.    public  boolean ModifyNum(String userid,int bookid,int num){  
92.          conn = DB.getCon();            //获取数据库连接    
93.          int total=0;  
94.          int oldnum=0;  
95.          if(conn!=null){  
96.             try{  
97.                 //获得原来的数量  
98.                  String sql3="select Num from "+userid+" where ID='"+bookid+"'";  
99.                  PreparedStatement pstm3 = conn.prepareStatement(sql3);  
100.                  ResultSet rs=pstm3.executeQuery();  
101.                  if(rs.next()){  
102.                      oldnum=rs.getInt(1);  
103.                  }  
104.                  if(oldnum==1){  
105.                      if(num==-1){  
106.                          return true;  
107.                      }  
108.                  }  
109.                  //更新数量  
110.                  String sql="update "+userid+" set Num='"+(num+oldnum)+"' where ID='"+bookid+"'";  
111.                  PreparedStatement pstm = conn.prepareStatement(sql);  
112.                  pstm.executeUpdate();  
113.                   //计算总价  
114.                  String sql1="select Price from "+userid+" where ID='"+bookid+"'";  
115.                  PreparedStatement pstm1 = conn.prepareStatement(sql1);  
116.                  ResultSet rs1=pstm1.executeQuery();  
117.                  if(rs1.next()){  
118.                      total=Integer.parseInt(rs1.getString("Price"))*(num+oldnum);  
119.                  }  
120.                  //修改总价  
121.                  String sql2="update "+userid+" set Total='"+total+"' where ID='"+bookid+"'";  
122.                  PreparedStatement pstm2 = conn.prepareStatement(sql2);  
123.                  pstm2.executeUpdate();   
124.                    
125.             } catch (SQLException e) {  
126.                 // TODO Auto-generated catch block  
127.                 e.printStackTrace();  
128.             }                        
129.          }  
130.          return false;  
131.      }  
132.    //书籍是否存在  
133.      public boolean isContainBook(String userid,String bookid){  
134.          conn = DB.getCon();            //获取数据库连接    
135.          if(conn!=null){           
136.              try {                 
137.                  String sql="select * from "+userid+" where ID='"+bookid+"'";  
138.                  PreparedStatement pstm = conn.prepareStatement(sql);  
139.                  ResultSet rs= pstm.executeQuery();  
140.                  if(rs.next())//如果存在这本书  
141.                      return true;  
142.                    
143.             } catch (SQLException e) {  
144.                 // TODO Auto-generated catch block  
145.                 e.printStackTrace();  
146.             }  
147.                
148.          }else{  
149.              System.out.println("创建连接失败");  
150.          }         
151.          return false;  
152.      }  
153.      //返回书的总价格  
154.      public int getTotalPrice(String userid){  
155.          conn = DB.getCon();            //获取数据库连接    
156.          if(conn!=null){      
157.               
158.              try {  
159.                   
160.                  String sql="select sum(Total) as total from "+userid;  
161.                  PreparedStatement pstm = conn.prepareStatement(sql);  
162.                  ResultSet rs= pstm.executeQuery();  
163.                  if(rs.next()){  
164.                      return rs.getInt(1);  
165.                  }  
166.                    
167.             } catch (SQLException e) {  
168.                 // TODO Auto-generated catch block  
169.                 e.printStackTrace();  
170.             }  
171.          }  
172.          return 0;  
173.      }  
174.      //清空所有书籍  
175.      public boolean ClearCartBook(String userid){  
176.          conn = DB.getCon();            //获取数据库连接    
177.          if(conn!=null){          
178.             try {  
179.                   
180.                 String sql="delete from "+userid;  
181.                 PreparedStatement pstm=conn.prepareStatement(sql);  
182.                 pstm.executeUpdate();  
183.                 return true;  
184.                   
185.             } catch (SQLException e) {  
186.                 // TODO Auto-generated catch block  
187.                 e.printStackTrace();  
188.             }  
189.          }  
190.          return false;  
191.           
192.      }  
193. }  

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

[java]  view plain  copy

 print ?

1. package com.Dao;  
2.   
3. import java.sql.Connection;  
4. import java.sql.PreparedStatement;  
5. import java.sql.ResultSet;  
6. import java.sql.SQLException;  
7. import java.util.ArrayList;  
8. import java.util.List;  
9.   
10. import com.beans.Book;  
11. import com.tools.DBConnection;  
12.   
13. public class PageQueryDao {  
14.         //分页查询所有书目信息  
15.         public List<Book> getPageList(int page){  
16.             List<Book> list=new ArrayList<Book>();  
17.             DBConnection DB=new DBConnection();  
18.             Connection conn=DB.getCon();  
19.             String sql="select * from books order by BookID asc limit ?,?";//limit关键字  
20.             try {  
21.                   
22.                 PreparedStatement pstm=conn.prepareStatement(sql);  
23.                 pstm.setInt(1, (page-1)*Book.PAGE_SIZE); //设置查询记录的开始位置  
24.                 pstm.setInt(2, Book.PAGE_SIZE);          //设置查询数据所返回的记录数  
25.                 ResultSet rs=pstm.executeQuery();  
26.                  while(rs.next()){  
27.                       Book book=new Book();  
28.                       book.setBookId(rs.getInt(1));  
29.                       book.setName(rs.getString(2));  
30.                       book.setAuthor(rs.getString(3));  
31.                       book.setPublisher(rs.getString(4));  
32.                       book.setPrice(rs.getString(5));  
33.                       list.add(book);  
34.                   }  
35.                  rs.close();  
36.                  pstm.close();  
37.                  conn.close();  
38.                    
39.             } catch (SQLException e) {  
40.                 // TODO Auto-generated catch block  
41.                 e.printStackTrace();  
42.             }  
43.             return list;  
44.         }  
45.           
46.         //查询总记录数  
47.         public int FindCounts(){  
48.             int count=0;  
49.             DBConnection DB=new DBConnection();  
50.             Connection conn=DB.getCon();  
51.             String sql="select count(*) from books";  
52.             try {  
53.                   
54.                 PreparedStatement pstm=conn.prepareStatement(sql);  
55.                 ResultSet rs=pstm.executeQuery();  
56.                 if(rs.next()){//rs里就一个值  
57.                     count=rs.getInt(1);                   
58.                 }  
59.                 rs.close();  
60.                 conn.close();  
61.             } catch (SQLException e) {  
62.                 // TODO Auto-generated catch block  
63.                 e.printStackTrace();  
64.             }  
65.             return count;  
66.               
67.         }  
68.       
69. }  

UserDao.java

[java]  view plain  copy

 print ?

1. package com.Dao;  
2.   
3. import java.sql.Connection;  
4. import java.sql.PreparedStatement;  
5. import java.sql.ResultSet;  
6. import java.sql.SQLException;  
7.   
8. import com.beans.User;  
9. import com.tools.DBConnection;  
10.   
11. public class UserDao {  
12.      DBConnection DB=new DBConnection();  
13.      Connection conn=null;  
14.    //编写按用户名密码查询用户方法  
15.      public User getUser(String userID,String Password){  
16.         User user = new User();             //创建JavaBean对象  
17.         conn = DB.getCon();         //获取数据库连接  
18.         try {  
19.             String sql = "select * from users where ID = ? and Password = ?";   //定义查询预处理语句  
20.             PreparedStatement statement = conn.prepareStatement(sql);       //实例化PreparedStatement对象  
21.             statement.setString(1, userID);         //设置预处理语句参数  
22.             statement.setString(2, Password);  
23.             ResultSet rest = statement.executeQuery();  //执行预处理语句  
24.             while(rest.next()){  
25.                 user.setID(rest.getString(1));              //应用查询结果设置对象属性                
26.                 user.setPassword(rest.getString(2));  
27.                 user.setSex(rest.getString(3));  
28.                 user.setPhone(rest.getString(4));  
29.                 user.setHome(rest.getString(5));  
30.                 user.setEmail(rest.getString(6));  
31.                 user.setHeader(rest.getString(7));  
32.             }  
33.         } catch (SQLException e) {            
34.             e.printStackTrace();  
35.         }         
36.         return user;                        //返回查询结果  
37.      }    
38.      //根据用户id返回用户对象  
39.      public User getUser(String userid){  
40.          User user = new User();                //创建JavaBean对象  
41.          conn = DB.getCon();            //获取数据库连接  
42.         try {  
43.              String sql="select * from users where ID='"+userid+"'";  
44.               PreparedStatement pstm = conn.prepareStatement(sql);  
45.               ResultSet rest = pstm.executeQuery(); //执行预处理语句  
46.                 while(rest.next()){  
47.                     user.setID(rest.getString(1));              //应用查询结果设置对象属性                
48.                     user.setPassword(rest.getString(2));  
49.                     user.setSex(rest.getString(3));  
50.                     user.setPhone(rest.getString(4));  
51.                     user.setHome(rest.getString(5));  
52.                     user.setEmail(rest.getString(6));  
53.                     user.setHeader(rest.getString(7));  
54.                 }  
55.         } catch (SQLException e) {  
56.             // TODO Auto-generated catch block  
57.             e.printStackTrace();  
58.         }     
59.          return user;  
60.      }  
61.      public boolean isContainUser(String id){  
62.           
63.         try {  
64.              conn = DB.getCon();            //获取数据库连接  
65.              String sql="select * from users where ID=?";  
66.              PreparedStatement pstm;  
67.              pstm = conn.prepareStatement(sql);  
68.              pstm.setString(1, id);  
69.              ResultSet rs=pstm.executeQuery();  
70.              if(rs.next()){  
71.                  return true;  
72.              }  
73.                
74.         } catch (SQLException e) {  
75.             // TODO Auto-generated catch block  
76.             e.printStackTrace();  
77.         }  
78.          return false;         
79.      }  
80.        
81. }  

5.3连接数据库代码

[java]  view plain  copy

 print ?

1. package com.tools;  
2. import java.sql.*;  
3. public class DBConnection {   
4. private Connection con;         //定义数据库连接类对象  
5. private PreparedStatement pstm;   
6. private String user="root";     //连接数据库用户名  
7. private String password="123456";       //连接数据库密码  
8. private String driverName="com.mysql.jdbc.Driver";  //数据库驱动  
9. private String url="jdbc:mysql://localhost:3306/shoppingcart";        
10. //连接数据库的URL,后面的是为了防止插入数据 库出现乱码，?useUnicode=true&characterEncoding=UTF-8  
11. //构造函数  
12. public DBConnection(){  
13.       
14. }  
15. /**创建数据库连接*/  
16. public Connection getCon(){  
17.     try{  
18.         Class.forName("com.mysql.jdbc.Driver");  
19.     }catch(ClassNotFoundException e){  
20.         System.out.println("加载数据库驱动失败！");  
21.         e.printStackTrace();  
22.     }  
23.     try {  
24.         con=DriverManager.getConnection(url,user,password);     //获取数据库连接  
25.     } catch (SQLException e) {  
26.         System.out.println("创建数据库连接失败！");  
27.         con=null;  
28.         e.printStackTrace();  
29.     }  
30.     return con;                 //返回数据库连接对象  
31. }     
32. /** 
33.  *@功能：对数据库进行增、删、改、查操作 
34.  *@参数：sql为SQL语句；params为Object数组，里面存储的是为sql表示的SQL语句中"?"占位符赋值的数据  
35.  */  
36.     public void doPstm(String sql,Object[] params){  
37.         if(sql!=null&&!sql.equals("")){  
38.             if(params==null)  
39.                 params=new Object[0];             
40.             getCon();  
41.             if(con!=null){  
42.                 try{          
43.                     System.out.println(sql);  
44.                     pstm=con.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);  
45.                     for(int i=0;i<params.length;i++){  
46.                         pstm.setObject(i+1,params[i]);  
47.                     }  
48.                     pstm.execute();  
49.                 }catch(SQLException e){  
50.                     System.out.println("doPstm()方法出错！");  
51.                     e.printStackTrace();  
52.                 }                 
53.             }             
54.         }  
55.     }     
56.     public ResultSet getRs() throws SQLException{  
57.         return pstm.getResultSet();       
58.     }  
59.     public int getCount() throws SQLException{  
60.         return pstm.getUpdateCount();         
61.     }  
62.     public void closed(){  
63.         try{  
64.             if(pstm!=null)  
65.                 pstm.close();             
66.         }catch(SQLException e){  
67.             System.out.println("关闭pstm对象失败！");  
68.             e.printStackTrace();  
69.         }  
70.         try{  
71.             if(con!=null){  
72.                 con.close();  
73.             }  
74.         }catch(SQLException e){  
75.             System.out.println("关闭con对象失败！");  
76.             e.printStackTrace();  
77.         }  
78.     }  
79. }