仿淘宝及SQL数据库

package com.jc.taobao.cj.dao; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.jc.taobao.cj.db.DbManager; import com.jc.taobao.cj.entity.ShopInfo; public class SelectShopInfoDAO { // 查寻所有商品 public List<ShopInfo> getAll() { String sql = "select * from shopinfo order by sid"; return Query(sql); } // 按商品的编号查寻 public ShopInfo getByid(int id) { String sql = "select * from shopinfo where sid=" + id+" order by sid"; return Query(sql).get(0); } // 按商品类型编号查寻 public List<ShopInfo> getByctid(int id) { String sql = "select * from shopinfo where ctid=" + id+" order by sid"; return Query(sql); } // 按用户编号查寻 public List<ShopInfo> getByuserid(int id) { String sql = "select * from shopinfo where userid=" + id+" order by sid"; return Query(sql); } // 按商品名称查寻 public List<ShopInfo> getByshopname(String shopname) { String sql = "select * from shopinfo where shopname like '%" + shopname + "%' order by sid"; return Query(sql); } public List<ShopInfo> getByShopNamePage(String shopname,int page) { page=(page-1)*5; String sql = "select top 5 * from shopinfo where shopname like '%"+shopname+"%' and sid not in(select top "+page+" sid from shopinfo where shopname like '%"+shopname+"%')"; return Query(sql); } //根据价格查寻 public List<ShopInfo> getByshopprice(int price1,int price2,String shopname) { String sql="select * from shopinfo where shopname='"+shopname+"' and between shopprice>="+price1+" and shopprice>="+price2+" order by sid"; return Query(sql); } //查寻随机四个商品 public List<ShopInfo> getByFour(int ctid) { String sql="select top 3 * from shopinfo where ctid="+ctid+" order by newid()"; return Query(sql); } //查寻随机十个商品 public List<ShopInfo> getByTen(int tid) { String sql="select top 15 * from shopinfo where ctid in (select ctid from childtypeInfo where tid="+tid+")"; return Query(sql); } public List<ShopInfo> getByFive() { String sql="select top 15 * from shopinfo order by newid()"; return Query(sql); } public List<ShopInfo> getPage(int page) { page=(page-1)*5; String sql = "select top 5 * from shopinfo where sid not in (select top " + page + " sid from shopinfo)"; return Query(sql); } public List<ShopInfo> getPage(int page,int ctid) { page=(page-1)*5; String sql = "select top 5 * from shopinfo where ctid="+ctid+" and sid not in (select top " + page + " sid from shopinfo where ctid="+ctid+")"; return Query(sql); } public List<ShopInfo> getByTid(int tid) { String sql="select * from shopinfo where ctid in (select ctid from childtypeinfo where tid=(select tid from typeinfo where tid="+tid+"))"; return Query(sql); } public List<ShopInfo> getPageTid(int page,int tid) { page=(page-1)*5; String sql="select top 5 * from shopinfo where ctid in (select ctid from childtypeinfo where tid=(select tid from typeinfo where tid="+tid+")) and sid not in (select top " +page+" sid from shopinfo where ctid in (select ctid from childtypeinfo where tid=(select tid from typeinfo where tid=" +tid+")))"; return Query(sql); } public List<ShopInfo> getPageBlack(int page) { page=(page-1)*9; String sql="select top 9 * from shopinfo where sid not in (select top " + page + " sid from shopinfo order by sid desc) order by sid desc"; return Query(sql); } //由此方法调入数据访问层并返回结果集 private List<ShopInfo> Query(String sql) { List<ShopInfo> list = new ArrayList<ShopInfo>(); DbManager db = new DbManager(); ResultSet rs = db.getRs(sql); try { while (rs.next()) { ShopInfo shop = new ShopInfo(); shop.setSid(rs.getInt("sid")); shop.setCtid(rs.getInt("ctid")); shop.setUtid(rs.getInt("utid")); shop.setUserid(rs.getInt("userid")); shop.setShopname(rs.getString("shopname")); shop.setShopprice(rs.getFloat("shopprice")); shop.setShopmake(rs.getString("shopmake")); shop.setShopphoto(rs.getString("shopphoto")); shop.setShopsay(rs.getString("shopsay")); shop.setUnmber(rs.getInt("number")); list.add(shop); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return list; } }