JSP+Javabean 网上商城书店

package com.bjsxt.shopping.book; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.util.ArrayList; import java.util.List; import com.bjsxt.shopping.category.Category; import com.bjsxt.shopping.util.DB; public class BookService { public static void add(Book b) { Connection conn = null; PreparedStatement pstmt = null; try { conn = DB.getConn(); String sql = "insert into book (ISBN,bookname,author,publisher,descr,normalprice,memberprice,pdate,categoryid,num) values (?,?,?,?, ?, ?, ?, ?, ?,?)"; pstmt = DB.prepare(conn, sql); pstmt.setString(1, b.getISBN()); pstmt.setString(2, b.getBookname()); pstmt.setString(3, b.getAuthor()); pstmt.setString(4, b.getPublisher()); pstmt.setString(5, b.getDescr()); pstmt.setDouble(6, b.getNormalPrice()); pstmt.setDouble(7, b.getMemberPrice()); pstmt.setTimestamp(8, new Timestamp(b.getPdate().getTime())); pstmt.setInt(9, b.getCategoryId()); pstmt.setInt(10, b.getNum()); pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { DB.close(pstmt); DB.close(conn); } } public static void delete(String ISBN) { Connection conn = null; Statement stmt = null; String sql; try { conn = DB.getConn(); sql = "delete from book where ISBN = '" + ISBN+"'"; stmt = DB.getStatement(conn); DB.executeUpdate(stmt, sql); } finally { DB.close(stmt); DB.close(conn); } } public static List<Book> getBooks() { Connection conn = DB.getConn(); Statement stmt = DB.getStatement(conn); String sql = "select * from book order by pdate desc"; ResultSet rs = DB.getResultSet(stmt, sql); List<Book> books = new ArrayList<Book>(); try { while (rs.next()) { Book b = getBookFromRs(rs); books.add(b); } } catch (SQLException e) { e.printStackTrace(); } finally { DB.close(stmt); DB.close(rs); DB.close(conn); } return books; } /** * @param lazy 为true时，只取Product的信息，否则同时取出Product内Category对象的信息 */ public static int getBooks(List<Book> books, int pageNo, int pageSize, boolean lazy) { int totalRecords = -1; Connection conn = DB.getConn(); Statement stmt = DB.getStatement(conn); String sql = ""; if(lazy) { sql = "select * from book order by pdate desc"; } else { sql = "select b.ISBN, b.bookname, b.author,b.publisher,b.descr bdescr, b.normalprice, " + " b.memberprice, b.pdate, b.categoryid ,b.picname,b.num, " + " c.id categoryid, c.name cname, c.descr cdescr, c.pid, c.cno, c.grade " + " from book b join category c on (b.categoryid = c.id) order by b.pdate desc"; } //sql += " limit " + (pageNo - 1) * pageSize + "," + pageSize; ResultSet rs = DB.getResultSet(stmt, sql); Statement stmtCount = DB.getStatement(conn); ResultSet rsCount = DB.getResultSet(stmtCount, "select count(*) from book"); //products = new ArrayList<Product>(); 千万小心这句话不要添加 try { rsCount.next(); totalRecords = rsCount.getInt(1); while (rs.next()) { Book b = null; if(lazy) { b = getBookFromRs(rs); } else { b = new Book(); b.setISBN(rs.getString("ISBN")); b.setBookname(rs.getString("bookname")); b.setAuthor(rs.getString("author")); b.setPublisher(rs.getString("publisher")); b.setDescr(rs.getString("bdescr")); b.setNormalPrice(rs.getDouble("normalprice")); b.setMemberPrice(rs.getDouble("memberprice")); b.setPdate(rs.getTimestamp("pdate")); b.setCategoryId(rs.getInt("categoryid")); b.setPicname(rs.getString("picname")); b.setNum(rs.getInt("num")); Category c = new Category(); c.setId(rs.getInt("categoryid")); c.setName(rs.getString("cname")); c.setDescr(rs.getString("cdescr")); c.setPid(rs.getInt("pid")); c.setCno(rs.getInt("cno")); c.setGrade(rs.getInt("grade")); b.setCategory(c); } books.add(b); } } catch (SQLException e) { e.printStackTrace(); } finally { DB.close(rsCount); DB.close(stmtCount); DB.close(stmt); DB.close(rs); DB.close(conn); } return totalRecords; } public static Book loadByISBN(String ISBN) { Connection conn = null; ResultSet rs = null; Statement stmt = null; Book b = null; try { String sql = "select * from book where ISBN = '" + ISBN+"'"; conn = DB.getConn(); stmt = DB.getStatement(conn); rs = DB.getResultSet(stmt, sql); if (rs.next()) { b = getBookFromRs(rs); } } catch (SQLException e) { e.printStackTrace(); } finally { DB.close(stmt); DB.close(rs); DB.close(conn); } return b; } public static void update(Book b) { Connection conn = null; PreparedStatement pstmt = null; try { conn = DB.getConn(); String sql = "update book set bookname=? , author=? , publisher=? , descr=?, normalprice=?, memberprice=?,pdate=?, categoryid=?,num=? where ISBN=?"; pstmt = DB.prepare(conn, sql); pstmt.setString(1, b.getBookname()); pstmt.setString(2, b.getAuthor()); pstmt.setString(3, b.getPublisher()); pstmt.setString(4, b.getDescr()); pstmt.setDouble(5, b.getNormalPrice()); pstmt.setDouble(6, b.getMemberPrice()); pstmt.setTimestamp(7, new Timestamp(b.getPdate().getTime())); pstmt.setInt(8, b.getCategoryId()); pstmt.setInt(9, b.getNum()); pstmt.setString(10, b.getISBN()); pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { DB.close(pstmt); DB.close(conn); } } public static void updatePicname(Book b) { Connection conn = null; PreparedStatement pstmt = null; try { conn = DB.getConn(); String sql = "update book set picname=? where ISBN=?"; pstmt = DB.prepare(conn, sql); pstmt.setString(1, b.getPicname()); pstmt.setString(2, b.getISBN()); pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { DB.close(pstmt); DB.close(conn); } } private static Book getBookFromRs(ResultSet rs) { Book b = null; try { b = new Book(); b.setISBN(rs.getString("ISBN")); b.setBookname(rs.getString("bookname")); b.setAuthor(rs.getString("author")); b.setPublisher(rs.getString("publisher")); b.setDescr(rs.getString("descr")); b.setNormalPrice(rs.getDouble("normalprice")); b.setMemberPrice(rs.getDouble("memberprice")); b.setPdate(rs.getTimestamp("pdate")); b.setCategoryId(rs.getInt("categoryid")); b.setPicname(rs.getString("picname")); b.setNum(rs.getInt("num")); } catch (SQLException e) { e.printStackTrace(); } return b; } public static void delete(String [] ISBNs) { Connection conn = null; Statement stmt = null; String sql = "delete from book where ISBN in ('-1'"; for(int i=0;i<ISBNs.length;i++){ sql += ",'"+ISBNs[i]+"'"; } sql +=")"; try { conn = DB.getConn(); stmt = DB.getStatement(conn); DB.executeUpdate(stmt, sql); } finally { DB.close(stmt); DB.close(conn); } } public static int find(List<Book> books, int pageNo, int pageSize, int categoryid) { String queryStr = " where b.categoryid="+categoryid; int totalRecords = -1; Connection conn = DB.getConn(); Statement stmt = DB.getStatement(conn); String sql = ""; sql = "select b.ISBN, b.bookname, b.author,b.publisher,b.descr bdescr, b.normalprice, " + " b.memberprice, b.pdate, b.categoryid ,b.picname,b.num, " + " c.id categoryid, c.name cname, c.descr cdescr, c.pid, c.cno, c.grade " + " from book b join category c on (b.categoryid = c.id)" + queryStr + " order by b.pdate desc";