当当图书购物网源码+数据库

package org.tarena.dao.impl; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import org.tarena.dao.ProductDao; import org.tarena.domain.Book; import org.tarena.domain.Product; /** * book 分类dao接口的实现 完成book数据CRUD功能 * * @author soft01 * */ public class JDBCBookDaoImpl extends JDBCBaseDao implements ProductDao { /** * FIND_BY_CATI sql 语句 通过d_category_product的cat_id来查找books集合 */ private static final String FIND_BY_CATID = "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 ?,?"; /** * FIND_BY_RECOMMEND sql 语句 通过开发者的意愿来查找books集合 */ private static final String FIND_BY_RECOMMEND = "select dp.*,db.* from d_product dp join d_book db on (db.id=dp.id) order by db.id limit ?,?"; /** * FIND_BY_NEW sql 语句 通过上架时间查找books集合 */ private static final String FIND_BY_NEW = "select dp.*,db.* from d_product dp join d_book db on (db.id=dp.id) order by dp.add_time desc limit ?,?"; /** * FIND_BY_HOT sql 语句 通过销售量查找books集合 */ private static final String FIND_BY_HOT = "select dp.*,db.* from d_product dp join d_book db on(db.id=dp.id) left join (select product_id,sum(product_num) sale_num from d_item group by product_id ) t1 on (t1.product_id=dp.id) order by t1.sale_num desc limit ?,?"; /** * FIND_BY_HOT sql 语句 查找新书销售榜books集合 */ private static final String FIND_BY_HOTBOARD = "select dp.*,db.* from d_product dp join d_book db on(db.id=dp.id) left join (select product_id,sum(product_num) sale_num from d_item group by product_id ) t1 on (t1.product_id=dp.id) order by t1.sale_num desc,dp.add_time desc limit ?,?"; /** * FIND_BY_id sql 语句 通过商品id来查找books集合 */ private static final String FIND_BY_ID = "select dp.*,db.* from d_product dp join d_book db on (db.id=dp.id and dp.id=?)"; /** * findByCatId(int cid,int begin,int size) * 通过d_category_product的cat_id来查找books集合 */ public List<Product> findByCatId(int cid, int begin, int size) throws Exception { // TODO Auto-generated method stub PreparedStatement ps = openConnection().prepareStatement(FIND_BY_CATID); ps.setInt(1, cid); ps.setInt(2, begin);// 分页起始页 ps.setInt(3, size);// 页面大小 ResultSet rs = ps.executeQuery(); List<Product> pros = new ArrayList<Product>(); while (rs.next()) { Book book = new Book(); bookInit(rs, book); pros.add(book); } closeConnection(); return pros; } /** * bookInit抽取方法 用来构造book对象 * * @param rs * @param book * @throws SQLException */ private void bookInit(ResultSet rs, Book book) throws SQLException { book.setId(rs.getInt("id")); book.setProduct_name(rs.getString("product_name")); book.setFixed_price(rs.getDouble("fixed_price")); book.setDang_price(rs.getDouble("dang_price")); book.setProduct_pic(rs.getString("product_pic")); book.setAdd_time(rs.getLong("add_time")); book.setAuthor(rs.getString("author")); book.setDescription(rs.getString("description")); book.setPublishing(rs.getString("publishing")); book.setPublish_time(rs.getLong("publish_time")); book.setKeywords(rs.getString("keywords")); book.setHas_delete(rs.getInt("has_deleted")); book.setWord_number(rs.getString("word_number")); book.setWhich_edtion(rs.getString("which_edtion")); book.setTotal_page(rs.getString("total_page")); book.setPrint_time(rs.getLong("print_time")); book.setPrint_number(rs.getString("print_number")); book.setIsbn(rs.getString("isbn")); book.setAuthor_summary(rs.getString("author_summary")); book.setCatalogue(rs.getString("catalogue")); } /** * findByRecommend()编辑推荐书籍 按照 d_book id 降序排列 */ public List<Product> findByRecommend(int begin, int size) throws Exception { // TODO Auto-generated method stub PreparedStatement ps = openConnection().prepareStatement( FIND_BY_RECOMMEND); ps.setInt(1, begin);// 分页起始页 ps.setInt(2, size);// 页面大小 ResultSet rs = ps.executeQuery(); List<Product> pros = new ArrayList<Product>(); while (rs.next()) { Book book = new Book(); bookInit(rs, book); pros.add(book); } closeConnection(); return pros; } /** * findByNew()新书上架 按照 add_time 升序排列 */ public List<Product> findByNew(int begin, int size) throws Exception { // TODO Auto-generated method stub PreparedStatement ps = openConnection().prepareStatement(FIND_BY_NEW); ps.setInt(1, begin);// 分页起始页 ps.setInt(2, size);// 页面大小 ResultSet rs = ps.executeQuery(); List<Product> pros = new ArrayList<Product>(); while (rs.next()) { Book book = new Book(); bookInit(rs, book); pros.add(book); } closeConnection(); return pros; } /** * findByHot()热书畅销 按照 d_order找查找销售数量 降序排列 */ public List<Product> findByHot(int begin, int size) throws Exception { // TODO Auto-generated method stub PreparedStatement ps = openConnection().prepareStatement(FIND_BY_HOT); ps.setInt(1, begin);// 分页起始页 ps.setInt(2, size);// 页面大小 ResultSet rs = ps.executeQuery(); List<Product> pros = new ArrayList<Product>(); while (rs.next()) { Book book = new Book(); bookInit(rs, book); // System.out.println("book_id>>"+book.getId()); pros.add(book); } closeConnection(); return pros; } /** * findByHot()热书畅销 按照 d_order找查找销售数量 降序排列 */ public List<Product> findByHotBoard(int begin, int size) throws Exception { // TODO Auto-generated method stub PreparedStatement ps = openConnection().prepareStatement( FIND_BY_HOTBOARD); ps.setInt(1, begin);// 分页起始页 ps.setInt(2, size);// 页面大小 ResultSet rs = ps.executeQuery(); List<Product> pros = new ArrayList<Product>(); while (rs.next()) { Book book = new Book(); bookInit(rs, book); // System.out.println("book_id>>"+book.getId()); pros.add(book); } closeConnection(); return pros; } /** * findById()通过产品id来查找该product信息 */ public Product findById(int id) throws Exception { // TODO Auto-generated method stub PreparedStatement ps = openConnection().prepareStatement(FIND_BY_ID); ps.setInt(1, id); ResultSet rs = ps.executeQuery(); Book book = null; if (rs.next()) { book = new Book(); bookInit(rs, book); // System.out.println("book_id>>"+book.getId()); } closeConnection(); return book; } }