shopinfodao.java

一个仿造淘宝的jsp网站。功能比较完善

package com.jc.taobao.gjj.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.jc.taobao.gjj.db.DBManager;
import com.jc.taobao.gjj.entity.ShopInfo;

public class ShopInfoDAO {
	DBManager dm = new DBManager();

	public int delete(Integer id) {
		String sql = "delete from shopinfo where sid=" + id;
		return dm.updb(sql);
	}

	public int merge(ShopInfo shop) {
		String sql = "update shopinfo set tid=" + shop.getTid() + ",stateid="
				+ shop.getStateid() + ",userid=" + shop.getUserid()
				+ ",shopname='" + shop.getShopname() + "',price="
				+ shop.getPrice() + ",shopmake='" + shop.getShopmake()
				+ "',shopphoto='" + shop.getShopphoto() + "',shopdesc="
				+ shop.getShopdesc() + "' where sid=" + shop.getSid();
		return dm.updb(sql);
	}

	public int save(ShopInfo shop) {
		String sql = "insert into shopinfo values("
				+ shop.getTid() + "," + shop.getStateid() + ","
				+ shop.getUserid() + ",'" + shop.getShopname() + "',"
				+ shop.getPrice() + ",'" + shop.getShopmake() + "','"
				+ shop.getShopphoto() + "','" + shop.getShopdesc() + "') "+"select @@identity as shopid";
		return dm.updb(sql);
	}
	public int save2(ShopInfo shop) {
		int getshopid=0;
		String sql = "insert into shopinfo values("
				+ shop.getTid() + "," + shop.getStateid() + ","
				+ shop.getUserid() + ",'" + shop.getShopname() + "',"
				+ shop.getPrice() + ",'" + shop.getShopmake() + "','"
				+ shop.getShopphoto() + "','" + shop.getShopdesc() + "') "+"select @@identity as shopidxx";
		 try {
			ResultSet rs=dm.getRs(sql);
			 while(rs.next())
			 {
				 getshopid=rs.getInt("shopidxx");
			 }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return getshopid;
	}
	private List<ShopInfo> query(String sql) {//查询
		List<ShopInfo> list = new ArrayList<ShopInfo>();
		ResultSet rs = dm.getRs(sql);
		try {
			while (rs.next()) {
				ShopInfo shop = new ShopInfo();
				shop.setSid(rs.getInt("sid"));
				shop.setTid(rs.getInt("tid"));
				shop.setStateid(rs.getInt("stateid"));
				shop.setUserid(rs.getInt("userid"));
				shop.setShopname(rs.getString("shopname"));
				shop.setPrice(rs.getFloat("price"));
				shop.setShopmake(rs.getString("shopmake"));
				shop.setShopphoto(rs.getString("shopphoto"));
				shop.setShopdesc(rs.getString("shopdesc"));
				list.add(shop);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	public List<ShopInfo> querybySuiJITwenty()
	{
		String sql="select top 20 * from shopinfo order by newid()";
		return query(sql);
	}
	
	public List<ShopInfo> querybyALL()//查询所有商品信息
	{
		String sql="select * from shopinfo order by sid asc";
		return query(sql);
	}
	
	public List<ShopInfo> querybysid(Integer shopid)//根据商品编号查出商品信息
	{
		String sql="select * from shopinfo where sid="+shopid;
		return query(sql);
	}
	public List<ShopInfo> querybyQiTa(ShopInfo shop)
	{
		String sql="select * from shopinfo where tid="+shop.getTid()+" and userid="+shop.getUserid()+" and shopname='"+shop.getShopname()+"' and price="+shop.getPrice()+" and shopmake='"+shop.getShopmake()+"' and shopphoto='"+shop.getShopphoto()+"'";
		return query(sql);
	}
	
	public List<ShopInfo> querybyshopname(ShopInfo shop)//根据商品名称查出商品信息
	{
		String sql="select * from shopinfo where shopname like '"+"%"+shop.getShopname()+"%"+"'";
		return query(sql);
	}
	
	public List<ShopInfo> querybytypeidandhot(Integer typeid)
	{
		String sql="select top 10 * from shopinfo  where tid="+typeid+" order by newid()";
		return query(sql);
	}
	public List<ShopInfo> querybyTopTen()
	{
		String sql="select top 10 * from shopinfo";
		return query(sql);
	}
	public List<ShopInfo> querybyshopdesc(ShopInfo shop)//根据商品描述查询商品信息
	{
		String sql="select * from shopinfo where shopdesc like '"+"%"+shop.getShopdesc()+"%"+"'";
		return query(sql);
	}
	
	public List<ShopInfo> querybystateid(ShopInfo shop)//根据商品状态查询商品信息
	{
		String sql="select * from shopinfo where stateid="+shop.getStateid();
		return query(sql);
	}
	
	public List<ShopInfo> querybytid(ShopInfo shop)//根据商品类别查询商品信息
	{
		String sql="select * from shopinfo where tid="+shop.getTid();
		return query(sql);
	}
	public List<ShopInfo> finToptenShopInfoByTypeId(Integer typeid)
	{
		String sql="select top 8 * from shopinfo where tid="+typeid;
		return query(sql);
	}
	public List<ShopInfo> finTopSIXShopInfoByTypeId(Integer typeid)
	{
		String sql="select top 6 * from shopinfo where tid="+typeid;
		return query(sql);
	}
	public List<ShopInfo> queryShopByCurrentSIXPageByTypeid(Integer page,Integer typeid)
	  {
		  String sql="select top 6 * from shopinfo where sid not in " +
		  		"(select top "+(page-1)*6+" sid from shopinfo where tid="+typeid+") and tid="+typeid;
		  return query(sql);
	  }
	
	public List<ShopInfo> queryShopByTypeid(Integer typeid)
	{
		String sql="select * from shopinfo where tid="+typeid;
		return query(sql);
	}
	public List<ShopInfo> queryShopByCurrentPageByTypeid(Integer page,Integer typeid)
	  {
		  String sql="select top 12 * from shopinfo where sid not in " +
		  		"(select top "+(page-1)*12+" sid from shopinfo where tid="+typeid+") and tid="+typeid;
		  return query(sql);
	  }
	public List<ShopInfo> querybyshopmake(String shopmake)//根据商品的产地查询商品信息
	{
		String sql="select * from shopinfo where shopmake like '"+"%"+shopmake+"%"+"'";
		return query(sql);
	}
	
	public List<ShopInfo> querybyshopprice(float price1,float price2)//根据价格查询 
	{
		String sql="select * from  shopinfo where price>"+price1+" and price <"+price2;
		return query(sql);
	}

}