java+jsp+struts2电子商务小项目

package com.xmg.dang.dao.impl; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import com.xmg.dang.dao.ProductDAO; import com.xmg.dang.entity.Book; import com.xmg.dang.entity.Product; import com.xmg.dang.util.DBUtil; public class JdbcProductDAO implements ProductDAO { public List<Product> findNew() throws Exception { String sql = "select *from d_product where " + "has_deleted=0 order by " + "add_time desc limit 0,8"; PreparedStatement stmt = DBUtil.getConnection().prepareStatement(sql); ResultSet rs = stmt.executeQuery(); List<Product> list = new ArrayList<Product>(); while (rs.next()) { Product pro = new 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.setHasDeleted(rs.getInt("has_deleted")); pro.setProductPic(rs.getString("product_pic")); list.add(pro); } return list; } public List<Book> findRecommend() throws Exception { List<Book> list = new ArrayList<Book>(); String sql = "select * from d_product dp join d_book db on(dp.id=db.id) " + "order by rand() limit 0,2"; PreparedStatement stmt = DBUtil.getConnection().prepareStatement(sql); ResultSet rs = stmt.executeQuery(); 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.setPublish_time(rs.getLong("publish_time")); book.setCatalogue(rs.getString("catalogue")); book.setProductPic(rs.getString("product_pic")); book.setFixedPrice(rs.getDouble("fixed_price")); book.setDangPrice(rs.getDouble("dang_price")); list.add(book); } return list; } public List<Product> findHot() throws Exception { StringBuilder sb = new StringBuilder(); sb.append("select d_product.* "); sb.append("from d_item join d_product "); sb.append("on(d_item.product_id=d_product.id) "); sb.append(" group by product_id "); sb.append("order by sum(product_num) desc "); sb.append("limit 0,8;"); PreparedStatement stmt = DBUtil.getConnection().prepareStatement( sb.toString()); ResultSet rs = stmt.executeQuery(); List<Product> list = new ArrayList<Product>(); while (rs.next()) { Product pro = new 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.setHasDeleted(rs.getInt("has_deleted")); pro.setProductPic(rs.getString("product_pic")); list.add(pro); } return list; } public List<Product> productList(int cid, int size, int page) throws Exception { StringBuffer sql = new StringBuffer(); sql.append("select dp.*,db.* "); sql.append(" from d_category_product dcp "); sql.append(" join d_product dp on(dcp.product_id=dp.id) "); sql.append(" join d_book db on(dp.id=db.id) "); sql.append(" where dcp.cat_id=? "); sql.append(" limit ?,? "); PreparedStatement stmt = DBUtil.getConnection(). prepareStatement(sql.toString()); stmt.setInt(1, cid); stmt.setInt(2, (page-1)*size); stmt.setInt(3, size); ResultSet rs = stmt.executeQuery(); List<Product> list = new ArrayList<Product>(); while(rs.next()){ Book book = new Book(); //设置取出的d_prodcut字段值 book.setId(rs.getInt("id")); book.setProductName(rs.getString("product_name")); book.setFixedPrice(rs.getDouble("fixed_price")); book.setDangPrice(rs.getDouble("dang_price")); book.setProductPic(rs.getString("product_pic")); //设置取出的d_book字段值 book.setAuthor(rs.getString("author")); book.setPublishing(rs.getString("publishing")); book.setPublish_time(rs.getLong("publish_time")); list.add(book); } return list; } public int getPages(int cid,int size) throws Exception { StringBuffer sql = new StringBuffer(); sql.append("select count(*) "); sql.append(" from d_category_product dcp "); sql.append(" join d_product dp on(dcp.product_id=dp.id) "); sql.append(" join d_book db on(dp.id=db.id) "); sql.append(" where dcp.cat_id=? "); PreparedStatement stmt = DBUtil.getConnection(). prepareStatement(sql.toString()); stmt.setInt(1, cid); ResultSet rs = stmt.executeQuery(); int pages = 0; if(rs.next()){ int len = rs.getInt(1); pages = len%size==0?len/size:len/size+1; } return pages; } public Product findById(int id) throws Exception { String sql = "select * from d_product where id=?"; PreparedStatement stmt = DBUtil.getConnection(). prepareStatement(sql.toString()); stmt.setInt(1, id); ResultSet rs = stmt.executeQuery(); Product pro = new Product(); if (rs.next()) { 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.setHasDeleted(rs.getInt("has_deleted")); pro.setProductPic(rs.getString("product_pic")); } return pro; } }