达内2011当当网源码（数据库为oracle）

package com.tarena.dang.dao.impl; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.tarena.dang.dao.BookDAO; import com.tarena.dang.pojo.Book; import com.tarena.dang.pojo.Item; import com.tarena.dang.pojo.Product; import com.tarena.dang.util.DbUtil; public class JdbcBookDAO implements BookDAO { private static final String findByCatId = " select d_product.id,d_product.product_name,d_product.description,d_product.add_time,d_product.fixed_price,d_product.dang_price,d_product.keywords,d_product.has_deleted,d_product.product_pic, d_book.id,d_book.author,d_book.publishing,d_book.publish_time,d_book.word_number,d_book.which_edtion,d_book.total_page,d_book.print_time,d_book.print_number,d_book.isbn,d_book.author_summary,d_book.catalogue from d_category_product join d_product on d_product.id=d_category_product.product_id join d_book on d_book.id= d_product.id where d_category_product.cat_id=? and rownum between ? and ?"; // // "select dp.*,db.* " // + "from d_category_product dcp " // + "join d_product dp on(dcp.product_id=dp.id) " // + "join d_book db on(dp.id=db.id) " + "where dcp.cat_id=? " // + "limit ?,?"; private static final String findAllBook = "select * from d_product dp join d_book db on(dp.id=db.id) where rownum<=2"; //" select * from d_product dp join d_book db on(dp.id=db.id) order by rand() limit 2"; // 热销 private static final String findHotBook =" select d_product.id,d_product.product_name,d_product.description,d_product.add_time,d_product.fixed_price,d_product.dang_price,d_product.keywords,d_product.has_deleted,d_product.product_pic from d_product left join d_item on d_item.product_id=d_product.id where rownum<=? order by d_item.product_num desc"; // "select dp.* from d_item di join " // + "d_product dp on(di.product_id=dp.id) order by di.product_num desc limit ?"; // 新书热销 public static String HOT_SALE = "select * from (select a.*,rownum row_num from (select d_product.id,order_id ,product_id, d_product.product_name,d_product.dang_price,d_item.product_num, amount ,add_time from d_product left join d_item on d_item.product_id=d_product.id order by d_product.id desc) a) b where add_time <= ? and b.row_num between 1 and ?"; /* * oracle语法： select * from * * (select a.*,rownum row_num from * * (select * from d_product left join d_item on d_item.id=d_product.id order * by d_product.id desc) a * ) b where b.row_num between 1 and 10 * */ public List<Product> findByCatId(int catId, int page, int size) throws Exception { PreparedStatement pst = DbUtil.getConnection().prepareStatement( findByCatId); pst.setInt(1, catId); // 根据要显示的页数，计算出抓取记录的起始点 int begin = (page - 1) * size; pst.setInt(2, begin);// 设置抓取的起始点 pst.setInt(3, size);// 设置最多抓取多少条记录 ResultSet rs = pst.executeQuery(); List<Product> list = new ArrayList<Product>(); while (rs.next()) { System.out.println("-----------------------"); // 将查询的数据分装成Book对象 Book pro = new Book(); // 设置dp.*的信息-->d_product表字段 pro.setId(rs.getInt("id")); pro.setProductName(rs.getString("product_name")); pro.setDescription(rs.getString("description")); pro.setAddTime(rs.getLong("add_time")); pro.setFixedPrice(rs.getDouble("fixed_price")); pro.setDangPrice(rs.getDouble("dang_price")); pro.setKeywords(rs.getString("keywords")); pro.setProductPic(rs.getString("product_pic")); pro.setHasDeleted(rs.getInt("has_deleted")); // 设置db.*的数据-->d_book表字段 System.out.println("---------||--------------"); pro.setAuthor(rs.getString("author")); pro.setPublishing(rs.getString("publishing")); pro.setPublishTime(rs.getLong("publish_time")); pro.setWordNumber(rs.getString("word_number")); pro.setWhichEditon(rs.getInt("which_edtion")); pro.setTotalPage(rs.getInt("total_page")); pro.setPrintNumner(rs.getString("print_number")); pro.setIsbn(rs.getString("isbn")); pro.setAuthorSummary(rs.getString("author_summary")); pro.setCatalogue(rs.getString("catalogue")); // TODO 设置剩余的book字段值 list.add(pro); } return list; } // 提取编辑推荐的书的信息 public List<Book> findAll() throws Exception { PreparedStatement pst = DbUtil.getConnection().prepareStatement( findAllBook); ResultSet rs = pst.executeQuery(); List<Book> list = new ArrayList<Book>(); while (rs.next()) { Book book = new Book(); book.setId(rs.getInt("id")); book.setProductName(rs.getString("product_name")); book.setAuthor(rs.getString("author")); book.setPublishing(rs.getString("publishing")); book.setPublishTime(rs.getLong("publish_time")); book.setDescription(rs.getString("description")); book.setFixedPrice(rs.getDouble("fixed_price")); book.setDangPrice(rs.getDouble("dang_price")); book.setProductPic(rs.getString("product_pic")); list.add(book); } return list; } // 热销 public List<Product> findHotBook(int number) throws SQLException { PreparedStatement pst = DbUtil.getConnection().prepareStatement( findHotBook); pst.setInt(1, number); ResultSet rs = pst.executeQuery(); List<Product> list = new ArrayList<Product>(); while (rs.next()) { // 将查询的数据封装成Book对象 Product pro = new Product(); // 设置dp.*的信息-->d_product表字段 pro.setId(rs.getInt("id")); pro.setProductName(rs.getString("product_name")); pro.setDescription(rs.getString("description")); pro.setAddTime(rs.getLong("add_time")); pro.setFixedPrice(rs.getDouble("fixed_price")); pro.setDangPrice(rs.getDouble("dang_price")); pro.setKeywords(rs.getString("keywords")); pro.setProductPic(rs.getString("product_pic")); pro.setHasDeleted(rs.getInt("has_deleted")); list.add(pro); } return list; } // 新书热销 public List<Item> findHotNewBook(long monthTime, int number) throws Exception { // System.out.println("杩涘叆鏂规硶浣撲簡"); PreparedStatement pstm = DbUtil.getConnection().prepareStatement( HOT_SALE); // System.out.println(Constant.HOT_SALE); pstm.setLong(1, monthTime); pstm.setInt(2, number); ResultSet rs = pstm.executeQuery(); List<Item> items = new ArrayList<Item>(); while (rs.next()) { Item item = new Item(); item.setId(rs.getInt("id")); item.setOrder_id(rs.getInt("order_id")); item.setProduct_id(rs.getInt("product_id")); item.setPriduct_name(rs.getString("product_name")); item.setDang_price(rs.getDouble("dang_price")); item.setProduct_num(rs.getInt("product_num")); item.setAmout(rs.getDouble("amount")); items.add(item); } return items; } public static void main(String[] args) throws Exception { JdbcBookDAO dao = new JdbcBookDAO(); // List<Book> books = dao.findAll(); // System.out.println(books.toString()); // System.out.println( dao.findHotBook(8).toString()); long now = System.currentTimeMillis(); long monthTime = 30 * 24 * 60 * 1000L; Long lastTime = now - monthTime; System.out.println(now); System.out.println(monthTime); System.out.println(lastTime); List<Item> bookNames = dao.findHotNewBook(lastTime, 9); System.out.println(bookNames.toString()); } }