package org.tarena.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import org.tarena.dao.ProductDAO;
import org.tarena.domain.Book;
import org.tarena.domain.Product;
public class ProductDAOBookImpl extends DangDAOImpl implements ProductDAO{
/**根据栏目id查询图书*/
public List<Product> findByCatId(int catId, int begin, int size) throws Exception {
StringBuffer sql = new StringBuffer("select db.*,dp.*")
.append(" from d_book db join d_product dp")
.append(" on db.id=dp.id")
.append(" join d_category_product dcp")
.append(" on dp.id=dcp.product_id")
.append(" where dcp.cat_id=?")
.append(" order by dp.add_time desc")
.append(" limit ?,?");
Connection con = getConnection();
PreparedStatement pstmt = con.prepareStatement(sql.toString());
pstmt.setInt(1, catId);
pstmt.setInt(2, begin);
pstmt.setInt(3, size);
ResultSet rs = pstmt.executeQuery();
List<Product> books = new ArrayList<Product>();
/*+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| id | int(12) | NO | PRI | | |
| author | varchar(200) | NO | | | |
| publishing | varchar(200) | NO | | | |
| publish_time | bigint(20) | NO | | | |
| word_number | varchar(15) | YES | | NULL | |
| which_edtion | varchar(15) | YES | | NULL | |
| total_page | varchar(15) | YES | | NULL | |
| print_time | int(20) | YES | | NULL | |
| print_number | varchar(15) | YES | | NULL | |
| isbn | varchar(25) | YES | | NULL | |
| author_summary | text | NO | | | |
| catalogue | text | NO | | | |
+----------------+--------------+------+-----+---------+-------+*/
/*+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| id | int(12) | NO | PRI | NULL | auto_increment |
| product_name | varchar(100) | NO | | | |
| description | varchar(100) | YES | | NULL | |
| add_time | bigint(20) | YES | | NULL | |
| fixed_price | double | NO | | | |
| dang_price | double | NO | | | |
| keywords | varchar(200) | YES | | NULL | |
| has_deleted | int(1) | NO | | 0 | |
| product_pic | varchar(200) | YES | | NULL | |
+--------------+--------------+------+-----+---------+----------------+*/
while(rs.next()){
Book bk = new Book();
bk.setId(rs.getInt("dp.id"));
bk.setAuthor(rs.getString("db.author"));
bk.setPublishing(rs.getString("db.publishing"));
bk.setPublish_time(rs.getLong("db.publish_time"));
bk.setWord_number(rs.getString("db.word_number"));
bk.setWhich_edition(rs.getString("db.which_edtion"));
bk.setTotal_page(rs.getString("db.total_page"));
bk.setPrint_time(rs.getInt("db.print_time"));
bk.setPrint_number(rs.getString("db.print_number"));
bk.setIsbn(rs.getString("db.isbn"));
bk.setAuthor_summary(rs.getString("db.author_summary"));
bk.setCatalogue(rs.getString("db.catalogue"));
bk.setProduct_name(rs.getString("dp.product_name"));
bk.setDescription(rs.getString("dp.description"));
bk.setAdd_time(rs.getLong("dp.add_time"));
bk.setFixed_price(rs.getDouble("dp.fixed_price"));
bk.setDang_price(rs.getDouble("dp.dang_price"));
bk.setKeywords(rs.getString("dp.keywords"));
bk.setHas_deleted(rs.getInt("dp.has_deleted"));
bk.setProduct_pic(rs.getString("dp.product_pic"));
books.add(bk);
}
return books;
}
/**根据id查询*/
public Product findById(int id)throws Exception{
StringBuffer sql = new StringBuffer("select db.*,dp.*")
.append(" from d_book db join d_product dp")
.append(" on db.id=dp.id")
.append(" where db.id=")
.append(id);
ResultSet rs = getConnection().createStatement().executeQuery(sql.toString());
Book bk = null;
if(rs.next()){
bk = new Book();
bk.setId(rs.getInt("dp.id"));
bk.setAuthor(rs.getString("db.author"));
bk.setPublishing(rs.getString("db.publishing"));
bk.setPublish_time(rs.getLong("db.publish_time"));
bk.setWord_number(rs.getString("db.word_number"));
bk.setWhich_edition(rs.getString("db.which_edtion"));
bk.setTotal_page(rs.getString("db.total_page"));
bk.setPrint_time(rs.getInt("db.print_time"));
bk.setPrint_number(rs.getString("db.print_number"));
bk.setIsbn(rs.getString("db.isbn"));
bk.setAuthor_summary(rs.getString("db.author_summary"));
bk.setCatalogue(rs.getString("db.catalogue"));
bk.setProduct_name(rs.getString("dp.product_name"));
bk.setDescription(rs.getString("dp.description"));
bk.setAdd_time(rs.getLong("dp.add_time"));
bk.setFixed_price(rs.getDouble("dp.fixed_price"));
bk.setDang_price(rs.getDouble("dp.dang_price"));
bk.setKeywords(rs.getString("dp.keywords"));
bk.setHas_deleted(rs.getInt("dp.has_deleted"));
bk.setProduct_pic(rs.getString("dp.product_pic"));
}
return bk;
}
public static void main(String[] args) throws Exception {
List<Product> books = new ProductDAOBookImpl().findByCatId(1, 0, 5);
System.out.println(books.get(1).getId());
System.out.println(books.size());
}
}
- 1
- 2
- 3
- 4
前往页