package dao;

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

import entity.Article;
import entity.Commodity;



/**
 * 商品的数据库处理类
 * 对应数据库COMMODITY表
 * @author Administrator
 *
 */
public class CommodityDao {
	DBconn conn = new DBconn();
	//在数据库中添加一个商品
	public boolean add(Commodity cmd){
		return conn.update("insert into COMMODITY(COMMODITYNAME,TYPEID,COMMODITYMATERIAL,COMMODITYPCAK,COMMODITYLANGUAGE,COMMODITYDESCRIPTION,COMMODITYMARKETPRICE,COMMODITYPRESENTPRICE,COMMODITYPICTURE,COMMODITYDETAIL,COMMODITYKEYWORD,COMMODITYADDEDTIME,ISTOP) values(?,?,?,?,?,?,?,?,?,?,?,sysdate,?)", 
				new String[]{
					cmd.getCommodityName(),
					String.valueOf(cmd.getTypeId()),
					cmd.getCommodityMaterial(),
					cmd.getCommodityPcak(),
					cmd.getCommodityLanguage(),
					cmd.getCommodityDescription(),
					String.valueOf(cmd.getCommodityMarketPrice()),
					String.valueOf(cmd.getCommodityPresentPrice()),
					cmd.getCommodityPicture(),cmd.getCommodityDetail(),
					cmd.getCommodityKeyword(),
					//cmd.getCommodityAddedtime(),
					cmd.getIsTop()
				})>0;
	}
	//根据商品id来删除商品
	public boolean deleteById(int cmdId){
		return conn.update("delete from COMMODITY where COMMODITYID=?", 
				new String[]{String.valueOf(cmdId)})>0;
	}
	//根据商品对象来更新商品
	public boolean updateByCommodity(Commodity cmd){
		return conn.update("update COMMODITY set COMMODITYNAME=?,TYPEID=?,COMMODITYMATERIAL=?, COMMODITYPCAK=?,COMMODITYLANGUAGE=?,COMMODITYDESCRIPTION=?,COMMODITYMARKETPRICE=?,COMMODITYPRESENTPRICE=?,COMMODITYPICTURE=?,COMMODITYDETAIL=?,COMMODITYKEYWORD=?,COMMODITYADDEDTIME=to_date(?,'yyyy-mm-dd hh24:mi:ss'),ISTOP=?,COMMODITYCLICKNUM=? where COMMODITYID=?", 
				new String[]{
					cmd.getCommodityName(),
					String.valueOf(cmd.getTypeId()),
					cmd.getCommodityMaterial(),
					cmd.getCommodityPcak(),
					cmd.getCommodityLanguage(),
					cmd.getCommodityDescription(),
					String.valueOf(cmd.getCommodityMarketPrice()),
					String.valueOf(cmd.getCommodityPresentPrice()),
					cmd.getCommodityPicture(),
					cmd.getCommodityDetail(),
					cmd.getCommodityKeyword(),
					cmd.getCommodityAddedtime(),
					cmd.getIsTop(),
					String.valueOf(cmd.getCommodityClickNum()),
					String.valueOf(cmd.getCommodityId())
				})>0;
	}
	
	/**
	 * @param price
	 * @return 根据价格查询数据
	 */
	public ArrayList<Commodity> queryAllByPrice(String price){
		ArrayList<Commodity> list = new  ArrayList<Commodity>();
		String str="select * from COMMODITY where 1=1 ";
		str=str+price;
		
		ResultSet rs=conn.query(str,null);
		try {
			while(rs.next()){
				Commodity cmd = new Commodity();
				cmd.setCommodityId(rs.getInt("COMMODITYID"));
				cmd.setCommodityName(rs.getString("COMMODITYNAME"));
				cmd.setTypeId(rs.getInt("TYPEID"));
				cmd.setCommodityMaterial(rs.getString("COMMODITYMATERIAL"));
				cmd.setCommodityPcak(rs.getString("COMMODITYPCAK"));
				cmd.setCommodityLanguage(rs.getString("COMMODITYLANGUAGE"));
				cmd.setCommodityDescription(rs.getString("COMMODITYDESCRIPTION"));
				cmd.setCommodityMarketPrice(rs.getDouble("COMMODITYMARKETPRICE"));
				cmd.setCommodityPresentPrice(rs.getDouble("COMMODITYPRESENTPRICE"));
				cmd.setCommodityPicture(rs.getString("COMMODITYPICTURE"));
				cmd.setCommodityDetail(rs.getString("COMMODITYDETAIL"));
				cmd.setCommoditySale(rs.getInt("COMMODITYSALE"));
				cmd.setCommodityKeyword(rs.getString("COMMODITYKEYWORD"));
				cmd.setCommodityAddedtime(rs.getString("COMMODITYADDEDTIME"));
				cmd.setIsTop(rs.getString("ISTOP"));
				cmd.setCommodityClickNum(rs.getInt("COMMODITYCLICKNUM"));
				list.add(cmd);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();
		}
		return list;
	}
	
	
	
	/**
	 * @param keyword
	 * @return 根据商品的关键字来从商品名字中查询商品信息
	 */
	public ArrayList<Commodity> queryByKeywordFromName(String keyword){
		ArrayList<Commodity> list = new  ArrayList<Commodity>();
		ResultSet rs=conn.query("select a.*,b.TYPENAME from COMMODITY a inner join COMMODITYTYPE b on(a.TYPEID=b.TYPEID) where a.COMMODITYKEYWORD like '%"+keyword+"%'",null);
		try {
			while(rs.next()){
				Commodity cmd = new Commodity();
				cmd.setCommodityId(rs.getInt("COMMODITYID"));
				cmd.setCommodityName(rs.getString("COMMODITYNAME"));
				cmd.setTypeId(rs.getInt("TYPEID"));
				cmd.setCommodityMaterial(rs.getString("COMMODITYMATERIAL"));
				cmd.setCommodityPcak(rs.getString("COMMODITYPCAK"));
				cmd.setCommodityLanguage(rs.getString("COMMODITYLANGUAGE"));
				cmd.setCommodityDescription(rs.getString("COMMODITYDESCRIPTION"));
				cmd.setCommodityMarketPrice(rs.getDouble("COMMODITYMARKETPRICE"));
				cmd.setCommodityPresentPrice(rs.getDouble("COMMODITYPRESENTPRICE"));
				cmd.setCommodityPicture(rs.getString("COMMODITYPICTURE"));
				cmd.setCommodityDetail(rs.getString("COMMODITYDETAIL"));
				cmd.setCommoditySale(rs.getInt("COMMODITYSALE"));
				cmd.setCommodityKeyword(rs.getString("COMMODITYKEYWORD"));
				cmd.setCommodityAddedtime(rs.getString("COMMODITYADDEDTIME"));
				cmd.setIsTop(rs.getString("ISTOP"));
				cmd.setCommodityClickNum(rs.getInt("COMMODITYCLICKNUM"));
				cmd.setTypeName(rs.getString("TYPENAME"));
				list.add(cmd);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();
		}
		return list;
	}
	
	/**
	 * @param keyword
	 * @return 根据商品的关键字来模糊查询完整关键字
	 */
	public ArrayList<String> queryByKeyword2(String keyword){
		ArrayList<String> list = new  ArrayList<String>();
		ResultSet rs=conn.query("select COMMODITYKEYWORD from COMMODITY where COMMODITYKEYWORD like '%"+keyword+"%'",null);
		
		try {
			while(rs.next()){
				list.add(rs.getString("COMMODITYKEYWORD"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();
		}
		return list;
	}
	/**
	 * @param keyword
	 * @return 根据商品的关键字来模糊查询完整关键字
	 */
	public ArrayList<String> queryNameByKeyword(String keyword){
		ArrayList<String> list = new  ArrayList<String>();
		ResultSet rs=conn.query("select COMMODITYNAME from COMMODITY where COMMODITYNAME like '%"+keyword+"%'",null);
		
		try {
			while(rs.next()){
				list.add(rs.getString("COMMODITYNAME"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();
		}
		return list;
	}
	
	//根据商品的类型ID来查询商品信息8个
	/**
	 * @param parentid
	 * @return 根据父ID查询销量前8的鲜花产品
	 */
	public ArrayList<Commodity> queryAllByTypeId(String parentid){
		ArrayList<Commodity> list = new  ArrayList<Commodity>();
		ResultSet rs=conn.query("select a.*,rownum,b.TYPENAME from (select * from commodity where typeid in(select typeid from COMMODITYtype where parentTYPEID=?) order by commoditySale desc)a inner join COMMODITYTYPE b on(a.TYPEID=b.TYPEID) where rownum<=8 order by rownum", new String[]{parentid});
		try {
			while(rs.next()){
				Commodity cmd = new Commodity();
				cmd.setCommodityId(rs.getInt("COMMODITYID"));
				cmd.setCommodityName(rs.getString("COMMODITYNAME"));
				cmd.setTypeId(rs.getInt("TYPEID"));
				cmd.setCommodityMaterial(rs.getString("COMMODITYMATERIAL"));
				cmd.setCommodityPcak(rs.getString("COMMODITYPCAK"));
				cmd.setCommodityLanguage(rs.getString("COMMODITYLANGUAGE"));
				cmd.setCommodityDescription(rs.getString("COMMODITYDESCRIPTION"));
				cmd.setCommodityMarketPrice(rs.getDouble("COMMODITYMARKETPRICE"));
				cmd.setCommodityPresentPrice(rs.getDouble("COMMODITYPRESENTPRICE"));
				cmd.setCommodityPicture(rs.getString("COMMODITYPICTURE"));
				cmd.setCommodityDetail(rs.getString("COMMODITYDETAIL"));
				cmd.setCommoditySale(rs.getInt("COMMODITYSALE"));
				cmd.setCommodityKeyword(rs.getString("COMMODITYKEYWORD"));
				cmd.setCommodityAddedtime(rs.getString("COMMODITYADDEDTIME"));
				cmd.setIsTop(rs.getString("ISTOP"));
				cmd.setCommodityClickNum(rs.getInt("COMMODITYCLICKNUM"));
				cmd.setTypeName(rs.getString("TYPENAME"));
				list.add(cmd);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();
		}
		return list;
	}
	/**
	 * @param parentid
	 * @return根据父ID按销量查询4个商品
	 */
	public ArrayList<Commodity> queryAllByTypeId1(String parentid){
		ArrayList<Commodity> list = new  ArrayList<Commodity>();
		ResultSet rs=conn.query("select a.*,rownum,b.TYPENAME from (select * from commodity where typeid in(select typeid from COMMODITYtype where parentTYPEID=?) order by commoditySale desc)a inner join COMMODITYTYPE b on(a.TYPEID=b.TYPEID) where rownum<=4 order by rownum", new String[]{parentid});
		try {
			while(rs.next()){
				Commodity cmd = new Commodity();
				cmd.setCommodityId(rs.getInt("COMMODITYID"));
				cmd.setCommodityName(rs.getString("COMMODITYNAME"));
				cmd.setTypeId(rs.getInt("TYPEID"));
				cmd.setCommodityMaterial(rs.getString("COMMODITYMATERIAL"));
				cmd.setCommodityPcak(rs.getString("COMMODITYPCAK"));
				cmd.setCommodityLanguage(rs.getString("COMMODITYLANGUAGE"));
				cmd.setCommodityDescription(rs.getString("COMMODITYDESCRIPTION"));
				cmd.setCommodityMarketPrice(rs.getDouble("COMMODITYMARKETPRICE"));
				cmd.setCommodityPresentPrice(rs.getDouble("COMMODITYPRESENTPRICE"));
				cmd.setCommodityPicture(rs.getString("COMMODITYPICTURE"));
				cmd.setCommodityDetail(rs.getString("COMMODITYDETAIL"));
				cmd.setCommoditySale(rs.getInt("COMMODITYSALE"));
				cmd.setCommodityKeyword(rs.getString("COMMODITYKEYWORD"));
				cmd.setCommodityAddedtime(rs.getString("COMMODITYADDEDTIME"));
				cmd.setIsTop(rs.getString("ISTOP"));
				cmd.setCommodityClickNum(rs.getInt("COMMODITYCLICKNUM"));
				cmd.setTypeName(rs.getString("TYPENAME"));
				list.add(cmd);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();
		}
		return list;
	}
	
	/**
	 * @return 返回所有商品中销量最高的4件商品
	 */
	public ArrayList<Commodity> queryAllHotCommoditys(){
		ArrayList<Commodity> list = new  ArrayList<Commodity>();
		ResultSet rs=conn.query("select a.*,rownum from (select * from commodity where typeid in(select typeid from COMMODITYtype) order by commoditySale desc)a where rownum<=4 order by rownum",null);
		try {
			while(rs.next()){
				Commodity cmd = new Commodity();
				cmd.setCommodityId(rs.getInt("COMMODITYID"));
				cmd.setCommodityName(rs.getString("COMMODITYNAME"));
				cmd.setTypeId(rs.getInt("TYPEID"));
				cmd.setCommodityMaterial(rs.getString("COMMODITYMATERIAL"));
				cmd.setCommodityPcak(rs.getString("COMMODITYPCAK"));
				cmd.setCommodityLanguage(rs.getString("COMMODITYLANGUAGE"));
				cmd.setCommodityDescription(rs.getString("COMMODITYDESCRIPTION"));
				cmd.setCommodityMarketPrice(rs.getDouble("COMMODITYMARKETPRICE"));
				cmd.setCommodityPresentPrice(rs.getDouble("COMMODITYPRESENTPRICE"));
				cmd.setCommodityPicture(rs.getString("COMMODITYPICTURE"));
				cmd.setCommodityDetail(rs.getString("COMMODITYDETAIL"));
				cmd.setCommoditySale(rs.getInt("COMMODITYSALE"));
				cmd.setCommodityKeyword(rs.getString("COMMODITYKEYWORD"));
				cmd.setCommodityAddedtime(rs.getString("COMMODITYADDEDTIME"));
				cmd.setIsTop(rs.getString("ISTOP"));
				cmd.setCommodityClickNum(rs.getInt("COMMODITYCLICKNUM"));
				list.add(cmd);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();
		}
		return list;
	}
	
	//
	/**根据父ID查询商品的价格最低的3个
	 * @return
	 */
	public ArrayList<Commodity> queryByPrice(){
		
		ArrayList<Commodity> list = new  ArrayList<Commodity>();
		ResultSet rs=conn.query("select a.*,rownum,b.TYPENAME from (select * from commodity where typeid in(select typeid from COMMODITYtype where parentTYPEID=1) order by commodityPresentPrice ASC)a inner join COMMODITYTYPE b on(a.TYPEID=b.TYPEID) where rownum<=3 order by rownum",null);
		try {
			while(rs.next()){
				Commodity cmd = new Commodity();
				cmd.setCommodityId(rs.getInt("COMMODITYID"));
				cmd.setCommodityName(rs.getString("COMMODITYNAME"));
				cmd.setTypeId(rs.getInt("TYPEID"));
				cmd.setCommodityMaterial(rs.getString("COMMODITYMATERIAL"));
				cmd.setCommodityPcak(rs.getString("COMMODITYPCAK"));
				cmd.setCommodityLanguage(rs.getString("COMMODITYLANGUAGE"));
				cmd.setCommodityDescription(rs.getString("COMMODITYDESCRIPTION"));
				cmd.setCommodityMarketPrice(rs.getDouble("COMMODITYMARKETPRICE"));
				cmd.setCommodityPresentPrice(rs.getDouble("COMMODITYPRESENTPRICE"));
				cmd.setCommodityPicture(rs.getString("COMMODITYPICTURE"));
				cmd.setCommodityDetail(rs.getString("COMMODITYDETAIL"));
				cmd.setCommoditySale(rs.getInt("COMMODITYSALE"));
				cmd.setCommodityKeyword(rs.getString("COMMODITYKEYWORD"));
				cmd.setCommodityAddedtime(rs.getString("COMMODITYADDEDTIME"));
				cmd.setIsTop(rs.getString("ISTOP"));
				cmd.setCommodityClickNum(rs.getInt("COMMODITYCLICKNUM"));
				cmd.setTypeName(rs.getString("TYPENAME"));
				list.add(cmd);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();
		}
		return list;
	}
	
	
	//
	/**
	 * @param PARENTID
	 * @return根据商品的类型ID来查询前三个商品信息
	 */
	public ArrayList<Commodity> queryThreeByTypeId(String PARENTID){
		ArrayList<Commodity> list = new  ArrayList<Commodity>();
		ResultSet rs=conn.query("select * from COMMODITY where TYPEID=? and rownum<=3 order by commoditySale ASC ", new String[]{PARENTID});
		try {
			while(rs.next()){
				Commodity cmd = new Commodity();
				cmd.setCommodityId(rs.getInt("COMMODITYID"));
				cmd.setCommodityName(rs.getString("COMMODITYNAME"));
				cmd.setTypeId(rs.getInt("TYPEID"));
				cmd.setCommodityMaterial(rs.getString("COMMODITYMATERIAL"));
				cmd.setCommodityPcak(rs.getString("COMMODITYPCAK"));
				cmd.setCommodityLanguage(rs.getString("COMMODITYLANGUAGE"));
				cmd.setCommodityDescription(rs.getString("COMMODITYDESCRIPTION"));
				cmd.setCommodityMarketPrice(rs.getDouble("COMMODITYMARKETPRICE"));
				cmd.setCommodityPresentPrice(rs.getDouble("COMMODITYPRESENTPRICE"));
				cmd.setCommodityPicture(rs.getString("COMMODITYPICTURE"));
				cmd.setCommodityDetail(rs.getString("COMMODITYDETAIL"));
				cmd.setCommoditySale(rs.getInt("COMMODITYSALE"));
				cmd.setCommodityKeyword(rs.getString("COMMODITYKEYWORD"));
				cmd.setCommodityAddedtime(rs.getString("COMMODITYADDEDTIME"));
				cmd.setIsTop(rs.getString("ISTOP"));
				cmd.setCommodityClickNum(rs.getInt("COMMODITYCLICKNUM"));
				list.add(cmd);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();
		}
		return list;
	}
	
	
	/**
	 * @param PARENTID
	 * @return根据父类Id查询前三个鲜花
	 */
	public ArrayList<Commodity> queryAll(String PARENTID){
		ArrayList<Commodity> list = new  ArrayList<Commodity>();
		ResultSet rs=conn.query("select a.*,rownum from (select * from commodity where typeid in(select typeid from COMMODITYtype where parentTYPEID="+PARENTID+")  order by COMMODITYID ASC)a where rownum<=3 ", null);
		try {
			while(rs.next()){
				Commodity cmd = new Commodity();
				cmd.setCommodityId(rs.getInt("COMMODITYID"));
				cmd.setCommodityName(rs.getString("COMMODITYNAME"));
				cmd.setTypeId(rs.getInt("TYPEID"));
				cmd.setCommodityMaterial(rs.getString("COMMODITYMATERIAL"));
				cmd.setCommodityPcak(rs.getString("COMMODITYPCAK"));
				cmd.setCommodityLanguage(rs.getString("COMMODITYLANGUAGE"));
				cmd.setCommodityDescription(rs.getString("COMMODITYDESCRIPTION"));
				cmd.setCommodityMarketPrice(rs.getDouble("COMMODITYMARKETPRICE"));
				cmd.setCommodityPresentPrice(rs.getDouble("COMMODITYPRESENTPRICE"));
				cmd.setCommodityPicture(rs.getString("COMMODITYPICTURE"));
				cmd.setCommodityDetail(rs.getString("COMMODITYDETAIL"));
				cmd.setCommoditySale(rs.getInt("COMMODITYSALE"));
				cmd.setCommodityKeyword(rs.getString("COMMODITYKEYWORD"));
				cmd.setCommodityAddedtime(rs.getString("COMMODITYADDEDTIME"));
				cmd.setIsTop(rs.getString("ISTOP"));
				cmd.setCommodityClickNum(rs.getInt("COMMODITYCLICKNUM"));
				list.add(cmd);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();
		}
		return list;
	}
	
	
	
	
	/**
	 * @param PARENTID
	 * @return根据商品Id查询全部信息
	 */
	public ArrayList<Commodity> queryByTypeId(String PARENTID){
		ArrayList<Commodity> list = new  ArrayList<Commodity>();
		ResultSet rs=conn.query("select * from COMMODITY where TYPEID=? ", new String[]{PARENTID});
		try {
			while(rs.next()){
				Commodity cmd = new Commodity();
				cmd.setCommodityId(rs.getInt("COMMODITYID"));
				cmd.setCommodityName(rs.getString("COMMODITYNAME"));
				cmd.setTypeId(rs.getInt("TYPEID"));
				cmd.setCommodityMaterial(rs.getString("COMMODITYMATERIAL"));
				cmd.setCommodityPcak(rs.getString("COMMODITYPCAK"));
				cmd.setCommodityLanguage(rs.getString("COMMODITYLANGUAGE"));
				cmd.setCommodityDescription(rs.getString("COMMODITYDESCRIPTION"));
				cmd.setCommodityMarketPrice(rs.getDouble("COMMODITYMARKETPRICE"));
				cmd.setCommodityPresentPrice(rs.getDouble("COMMODITYPRESENTPRICE"));
				cmd.setCommodityPicture(rs.getString("COMMODITYPICTURE"));
				cmd.setCommodityDetail(rs.getString("COMMODITYDETAIL"));
				cmd.setCommoditySale(rs.getInt("COMMODITYSALE"));
				cmd.setCommodityKeyword(rs.getString("COMMODITYKEYWORD"));
				cmd.setCommodityAddedtime(rs.getString("COMMODITYADDEDTIME"));
				cmd.setIsTop(rs.getString("ISTOP"));
				cmd.setCommodityClickNum(rs.getInt("COMMODITYCLICKNUM"));
				list.add(cmd);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();
		}
		return list;
	}
	
	/**根据商品父类型id查询销量最高的5件商品
	 * @param PARENTID 父类型id
	 * @return商品集合
	 */
	public ArrayList<Commodity> querySalesRanking(String PARENTID){
		ArrayList<Commodity> list = new  ArrayList<Commodity>();
		ResultSet rs=conn.query("select * from(select a.*,rownum from commodity a where typeid in( select typeId from commoditytype where parenttypeid=?) order by commoditysale desc) where rownum<=4", new String[]{PARENTID});
		try {
			while(rs.next()){
				Commodity cmd = new Commodity();
				cmd.setCommodityId(rs.getInt("COMMODITYID"));
				cmd.setCommodityName(rs.getString("COMMODITYNAME"));
				cmd.setTypeId(rs.getInt("TYPEID"));
				cmd.setCommodityMaterial(rs.getString("COMMODITYMATERIAL"));
				cmd.setCommodityPcak(rs.getString("COMMODITYPCAK"));
				cmd.setCommodityLanguage(rs.getString("COMMODITYLANGUAGE"));
				cmd.setCommodityDescription(rs.getString("COMMODITYDESCRIPTION"));
				cmd.setCommodityMarketPrice(rs.getDouble("COMMODITYMARKETPRICE"));
				cmd.setCommodityPresentPrice(rs.getDouble("COMMODITYPRESENTPRICE"));
				cmd.setCommodityPicture(rs.getString("COMMODITYPICTURE"));
				cmd.setCommodityDetail(rs.getString("COMMODITYDETAIL"));
				cmd.setCommoditySale(rs.getInt("COMMODITYSALE"));
				cmd.setCommodityKeyword(rs.getString("COMMODITYKEYWORD"));
				cmd.setCommodityAddedtime(rs.getString("COMMODITYADDEDTIME"));
				cmd.setIsTop(rs.getString("ISTOP"));
				cmd.setCommodityClickNum(rs.getInt("COMMODITYCLICKNUM"));
				list.add(cmd);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();
		}
		return list;
	}
	
	/**
	 * @param str
	 * @param page
	 * @param size查询分类的分页
	 * @return
	 */
	public ArrayList<Commodity> queryByTypeIdSql(String str,int page,int size) {
		ArrayList<Commodity> list = new ArrayList<Commodity>();
		Commodity com=null;
		String sql = "select b.* from (select a.*,rownum rn from(select * from commodity where 1=1 "+str+"  order by commodityid desc)a  where rownum<="+page*size+" )b where b.rn>"+(page-1)*size;
		ResultSet rs = conn.query(sql,null);
		try {
			while (rs.next()) {
				com = new Commodity();
				com.setCommodityId(rs.getInt(1));
				com.setCommodityName(rs.getString(2));
				com.setTypeId(rs.getInt(3));
				com.setCommodityMaterial(rs.getString(4));
				com.setCommodityPcak(rs.getString(5));
				com.setCommodityLanguage(rs.getString(6));
				com.setCommodityDescription(rs.getString(7));
				com.setCommodityMarketPrice(rs.getInt(8));
				com.setCommodityPresentPrice(rs.getInt(9));
				com.setCommodityPicture(rs.getString(10));
				com.setCommodityDetail(rs.getString(11));
				com.setCommoditySale(rs.getInt(12));
				com.setCommodityKeyword(rs.getString(13));
				com.setCommodityAddedtime(rs.getString(14).substring(0,rs.getString("COMMODITYADDEDTIME").length()-2));
				com.setIsTop(rs.getString(15));
				com.setCommodityClickNum(rs.getInt(16));
				list.add(com);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			conn.closeAll();
		}
		return list;
	}
	
	/**
	 * @param str
	 * @param page
	 * @param size查询全部鲜花分页
	 * @return
	 */
	public ArrayList<Commodity> queryAllByTypeIdSql(String PARENTID,int page,int size) {
		ArrayList<Commodity> list = new ArrayList<Commodity>();
		Commodity com=null;
		String sql = "select b.* from (select a.*,rownum rn from(select * from commodity where typeid in(select typeid from COMMODITYtype where parentTYPEID="+PARENTID+") order by COMMODITYID ASC)a where rownum<="+page*size+" )b where b.rn>"+(page-1)*size;
		ResultSet rs = conn.query(sql,null);
		try {
			while (rs.next()) {
				com = new Commodity();
				com.setCommodityId(rs.getInt(1));
				com.setCommodityName(rs.getString(2));
				com.setTypeId(rs.getInt(3));
				com.setCommodityMaterial(rs.getString(4));
				com.setCommodityPcak(rs.getString(5));
				com.setCommodityLanguage(rs.getString(6));
				com.setCommodityDescription(rs.getString(7));
				com.setCommodityMarketPrice(rs.getInt(8));
				com.setCommodityPresentPrice(rs.getInt(9));
				com.setCommodityPicture(rs.getString(10));
				com.setCommodityDetail(rs.getString(11));
				com.setCommoditySale(rs.getInt(12));
				com.setCommodityKeyword(rs.getString(13));
				com.setCommodityAddedtime(rs.getString(14).substring(0,rs.getString("COMMODITYADDEDTIME").length()-2));
				com.setIsTop(rs.getString(15));
				com.setCommodityClickNum(rs.getInt(16));
				list.add(com);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			conn.closeAll();
		}
		return list;
	}
	
	/**
	 * @param str
	 * @param page
	 * @param size查询特价商品的分类查询
	 * @return
	 */
	public ArrayList<Commodity> queryByTJSql(String str,int page,int size) {
		ArrayList<Commodity> list = new ArrayList<Commodity>();
		Commodity com=null;
		String sql = "select b.* from (select a.*,rownum rn from(select * from commodity where 1=1 "+str+" )a  where rownum<="+page*size+" )b where b.rn>"+(page-1)*size;
		ResultSet rs = conn.query(sql,null);
		try {
			while (rs.next()) {
				com = new Commodity();
				com.setCommodityId(rs.getInt(1));
				com.setCommodityName(rs.getString(2));
				com.setTypeId(rs.getInt(3));
				com.setCommodityMaterial(rs.getString(4));
				com.setCommodityPcak(rs.getString(5));
				com.setCommodityLanguage(rs.getString(6));
				com.setCommodityDescription(rs.getString(7));
				com.setCommodityMarketPrice(rs.getInt(8));
				com.setCommodityPresentPrice(rs.getInt(9));
				com.setCommodityPicture(rs.getString(10));
				com.setCommodityDetail(rs.getString(11));
				com.setCommoditySale(rs.getInt(12));
				com.setCommodityKeyword(rs.getString(13));
				com.setCommodityAddedtime(rs.getString(14).substring(0,rs.getString("COMMODITYADDEDTIME").length()-2));
				com.setIsTop(rs.getString(15));
				com.setCommodityClickNum(rs.getInt(16));
				list.add(com);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			conn.closeAll();
		}
		return list;
	}
	
	
	
	/**
	 * @param floral
	 * @return根据花材名称来查询商品信息
	 */
	public ArrayList<Commodity> queryAllByFloral(String floral){
		ArrayList<Commodity> list = new  ArrayList<Commodity>();
		ResultSet rs=conn.query("select * from COMMODITY where COMMODITYMATERIAL like '%"+floral+"%'",null);
		
		try {
			while(rs.next()){
				Commodity cmd = new Commodity();
				cmd.setCommodityId(rs.getInt("COMMODITYID"));
				cmd.setCommodityName(rs.getString("COMMODITYNAME"));
				cmd.setTypeId(rs.getInt("TYPEID"));
				cmd.setCommodityMaterial(rs.getString("COMMODITYMATERIAL"));
				cmd.setCommodityPcak(rs.getString("COMMODITYPCAK"));
				cmd.setCommodityLanguage(rs.getString("COMMODITYLANGUAGE"));
				cmd.setCommodityDescription(rs.getString("COMMODITYDESCRIPTION"));
				cmd.setCommodityMarketPrice(rs.getDouble("COMMODITYMARKETPRICE"));
				cmd.setCommodityPresentPrice(rs.getDouble("COMMODITYPRESENTPRICE"));
				cmd.setCommodityPicture(rs.getString("COMMODITYPICTURE"));
				cmd.setCommodityDetail(rs.getString("COMMODITYDETAIL"));
				cmd.setCommoditySale(rs.getInt("COMMODITYSALE"));
				cmd.setCommodityKeyword(rs.getString("COMMODITYKEYWORD"));
				cmd.setCommodityAddedtime(rs.getString("COMMODITYADDEDTIME"));
				cmd.setIsTop(rs.getString("ISTOP"));
				cmd.setCommodityClickNum(rs.getInt("COMMODITYCLICKNUM"));
				list.add(cmd);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();
		}
		return list;
	}
	
	/**
	 * @param keyword
	 * @return 根据商品的关键字来查询商品信息
	 */
	public ArrayList<Commodity> queryByKeyword(String keyword){
		ArrayList<Commodity> list = new  ArrayList<Commodity>();
		ResultSet rs=conn.query("select a.*,b.TYPENAME from COMMODITY a inner join COMMODITYTYPE b on(a.TYPEID=b.TYPEID) where a.COMMODITYNAME like '%"+keyword+"%'",null);
		try {
			while(rs.next()){
				Commodity cmd = new Commodity();
				cmd.setCommodityId(rs.getInt("COMMODITYID"));
				cmd.setCommodityName(rs.getString("COMMODITYNAME"));
				cmd.setTypeId(rs.getInt("TYPEID"));
				cmd.setCommodityMaterial(rs.getString("COMMODITYMATERIAL"));
				cmd.setCommodityPcak(rs.getString("COMMODITYPCAK"));
				cmd.setCommodityLanguage(rs.getString("COMMODITYLANGUAGE"));
				cmd.setCommodityDescription(rs.getString("COMMODITYDESCRIPTION"));
				cmd.setCommodityMarketPrice(rs.getDouble("COMMODITYMARKETPRICE"));
				cmd.setCommodityPresentPrice(rs.getDouble("COMMODITYPRESENTPRICE"));
				cmd.setCommodityPicture(rs.getString("COMMODITYPICTURE"));
				cmd.setCommodityDetail(rs.getString("COMMODITYDETAIL"));
				cmd.setCommoditySale(rs.getInt("COMMODITYSALE"));
				cmd.setCommodityKeyword(rs.getString("COMMODITYKEYWORD"));
				cmd.setCommodityAddedtime(rs.getString("COMMODITYADDEDTIME"));
				cmd.setIsTop(rs.getString("ISTOP"));
				cmd.setCommodityClickNum(rs.getInt("COMMODITYCLICKNUM"));
				cmd.setTypeName(rs.getString("TYPENAME"));
				list.add(cmd);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();
		}
		return list;
	}
	
	
	/**
	 * @param cmdId
	 * @return根据商品的id来在查找出这个商品对象
	 */
	public Commodity queryByCommodityId(String cmdId){
		Commodity cmd = new Commodity();
		
		ResultSet rs=conn.query("select * from COMMODITY where COMMODITYID=?", new String[]{cmdId});
		try {
			while(rs.next()){
				cmd.setCommodityId(rs.getInt("COMMODITYID"));
				cmd.setCommodityName(rs.getString("COMMODITYNAME"));
				cmd.setTypeId(rs.getInt("TYPEID"));
				cmd.setCommodityMaterial(rs.getString("COMMODITYMATERIAL"));
				cmd.setCommodityPcak(rs.getString("COMMODITYPCAK"));
				cmd.setCommodityLanguage(rs.getString("COMMODITYLANGUAGE"));
				cmd.setCommodityDescription(rs.getString("COMMODITYDESCRIPTION"));
				cmd.setCommodityMarketPrice(rs.getDouble("COMMODITYMARKETPRICE"));
				cmd.setCommodityPresentPrice(rs.getDouble("COMMODITYPRESENTPRICE"));
				cmd.setCommodityPicture(rs.getString("COMMODITYPICTURE"));
				
				cmd.setCommodityDetail(rs.getString("COMMODITYDETAIL"));
				cmd.setCommoditySale(rs.getInt("COMMODITYSALE"));
				cmd.setCommodityKeyword(rs.getString("COMMODITYKEYWORD"));
				cmd.setCommodityAddedtime(rs.getString("COMMODITYADDEDTIME").substring(0,rs.getString("COMMODITYADDEDTIME").length()-2));
				cmd.setIsTop(rs.getString("ISTOP"));
				cmd.setCommodityClickNum(rs.getInt("COMMODITYCLICKNUM"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();
		}
		return cmd;
	}
	
	
	/**
	 * @param mate
	 * @return 根据商品的花类来查询商品信息
	 */
	public ArrayList<Commodity> queryByMate(String mate){
		ArrayList<Commodity> list = new  ArrayList<Commodity>();
		ResultSet rs=conn.query("select * from COMMODITY where COMMODITYMATERIAL like '%"+mate+"%'",null);
		try {
			while(rs.next()){
				Commodity cmd = new Commodity();
				cmd.setCommodityId(rs.getInt("COMMODITYID"));
				cmd.setCommodityName(rs.getString("COMMODITYNAME"));
				cmd.setTypeId(rs.getInt("TYPEID"));
				cmd.setCommodityMaterial(rs.getString("COMMODITYMATERIAL"));
				cmd.setCommodityPcak(rs.getString("COMMODITYPCAK"));
				cmd.setCommodityLanguage(rs.getString("COMMODITYLANGUAGE"));
				cmd.setCommodityDescription(rs.getString("COMMODITYDESCRIPTION"));
				cmd.setCommodityMarketPrice(rs.getDouble("COMMODITYMARKETPRICE"));
				cmd.setCommodityPresentPrice(rs.getDouble("COMMODITYPRESENTPRICE"));
				cmd.setCommodityPicture(rs.getString("COMMODITYPICTURE"));
				cmd.setCommodityDetail(rs.getString("COMMODITYDETAIL"));
				cmd.setCommoditySale(rs.getInt("COMMODITYSALE"));
				cmd.setCommodityKeyword(rs.getString("COMMODITYKEYWORD"));
				cmd.setCommodityAddedtime(rs.getString("COMMODITYADDEDTIME"));
				cmd.setIsTop(rs.getString("ISTOP"));
				cmd.setCommodityClickNum(rs.getInt("COMMODITYCLICKNUM"));
				list.add(cmd);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();
		}
		return list;
	}
	
	/**
	 * @param page页数
	 * @param size显示记录数
	 * @return根据分页获取数据记录
	 */
	public ArrayList<Commodity> queryByPage(int typeId,int page,int size) {
		ArrayList<Commodity> list = new ArrayList<Commodity>();
		Commodity com=null;
		String sql="select b.* from (select a.*,rownum rn from(select * from commodity where typeid=?  order by commodityid desc)a  where rownum<="+page*size+" )b where b.rn>"+(page-1)*size;
		String[] paras=new String[]{String.valueOf(typeId)};
		ResultSet rs = conn.query(sql,paras);
		try {
			while (rs.next()) {
				com = new Commodity();
				com.setCommodityId(rs.getInt(1));
				com.setCommodityName(rs.getString(2));
				com.setTypeId(rs.getInt(3));
				com.setCommodityMaterial(rs.getString(4));
				com.setCommodityPcak(rs.getString(5));
				com.setCommodityLanguage(rs.getString(6));
				com.setCommodityDescription(rs.getString(7));
				com.setCommodityMarketPrice(rs.getInt(8));
				com.setCommodityPresentPrice(rs.getInt(9));
				com.setCommodityPicture(rs.getString(10));
				com.setCommodityDetail(rs.getString(11));
				com.setCommoditySale(rs.getInt(12));
				com.setCommodityKeyword(rs.getString(13));
				com.setCommodityAddedtime(rs.getString(14).substring(0,rs.getString(14).length()-2));
				com.setIsTop(rs.getString(15));
				com.setCommodityClickNum(rs.getInt(16));
				list.add(com);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			conn.closeAll();
		}
		return list;
	}
	
	/**
	 * @param typeId
	 * @return 获取总的数据记录个数
	 */
	public int getRecordCount(int typeId){
		int count=0;
		ResultSet rs=conn.query("select count(*) as rcount from commodity where typeId=?",new String[]{String.valueOf(typeId)});
		try {
			while(rs.next()){
				count=rs.getInt("rcount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();
		}
		return count;
	}
	
	/**
	 * @param str
	 * @param page
	 * @param size
	 * @return 根据语句分页查询数据
	 */
	public ArrayList<Commodity> queryByPageSql(String str,int page,int size) {
		ArrayList<Commodity> list = new ArrayList<Commodity>();
		Commodity com=null;
		String sql = "select b.* from (select a.*,rownum rn from(select * from commodity where 1=1 "+str+"  order by commodityid desc)a  where rownum<="+page*size+" )b where b.rn>"+(page-1)*size;
		ResultSet rs = conn.query(sql,null);
		try {
			while (rs.next()) {
				com = new Commodity();
				com.setCommodityId(rs.getInt(1));
				com.setCommodityName(rs.getString(2));
				com.setTypeId(rs.getInt(3));
				com.setCommodityMaterial(rs.getString(4));
				com.setCommodityPcak(rs.getString(5));
				com.setCommodityLanguage(rs.getString(6));
				com.setCommodityDescription(rs.getString(7));
				com.setCommodityMarketPrice(rs.getInt(8));
				com.setCommodityPresentPrice(rs.getInt(9));
				com.setCommodityPicture(rs.getString(10));
				com.setCommodityDetail(rs.getString(11));
				com.setCommoditySale(rs.getInt(12));
				com.setCommodityKeyword(rs.getString(13));
				com.setCommodityAddedtime(rs.getString(14).substring(0,rs.getString("COMMODITYADDEDTIME").length()-2));
				com.setIsTop(rs.getString(15));
				com.setCommodityClickNum(rs.getInt(16));
				list.add(com);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			conn.closeAll();
		}
		return list;
	}
	
	/**
	 * @param str
	 * @return 获取总的记录数
	 */
	public int getRecordCountSql(String str){
		int count=0;
		ResultSet rs=conn.query("select count(*) as rcount from commodity where 1=1 "+str+"",null);
		try {
			while(rs.next()){
				count=rs.getInt("rcount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();
		}
		return count;
	}
	
	/**
	 * @param str
	 * @return 根据父类ID获取总的记录数
	 */
	public int getRecordCountSqlByID(String PARENTID){
		int count=0;
		ResultSet rs=conn.query("select COUNT(*) as rcount from commodity where typeid in (select typeid FROM commoditytype where parenttypeid="+PARENTID+") ",null);
		try {
			while(rs.next()){
				count=rs.getInt("rcount");
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();
		}
		return count;
	}
	
	
	public ArrayList<Commodity> queryBySql(String sql){
		ArrayList<Commodity> list = new ArrayList<Commodity>();
		ResultSet rs = conn.query(sql, null);
		try {
			while(rs.next()){
				Commodity com = new Commodity();
				com = new Commodity();
				com.setCommodityId(rs.getInt(1));
				com.setCommodityName(rs.getString(2));
				com.setTypeId(rs.getInt(3));
				com.setCommodityMaterial(rs.getString(4));
				com.setCommodityPcak(rs.getString(5));
				com.setCommodityLanguage(rs.getString(6));
				com.setCommodityDescription(rs.getString(7));
				com.setCommodityMarketPrice(rs.getInt(8));
				com.setCommodityPresentPrice(rs.getInt(9));
				com.setCommodityPicture(rs.getString(10));
				com.setCommodityDetail(rs.getString(11));
				com.setCommoditySale(rs.getInt(12));
				com.setCommodityKeyword(rs.getString(13));
				com.setCommodityAddedtime(rs.getString(14).substring(0,rs.getString("COMMODITYADDEDTIME").length()-2));
				com.setIsTop(rs.getString(15));
				com.setCommodityClickNum(rs.getInt(16));
				list.add(com);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();
		}
		return list;
	}
	public boolean updatesale(int commodityid){
		return conn.update("update commodity set commoditySale=commoditySale+1  where commodityid=? ", new String[]{String.valueOf(commodityid)})>0;	
	}
}
最近下载更多
wanglinddad  LV51 4月15日
lishangchen  LV3 1月27日
sovy030201  LV5 1月11日
765105637  LV9 1月11日
八七  LV8 1月3日
北有深秋  LV3 2021年12月21日
adminsan  LV2 2021年12月15日
chenchen479  LV6 2021年11月28日
xiaohe229  LV8 2021年8月9日
最近浏览更多
chj1234  LV4 9月19日
lsglsg9  LV20 9月11日
49202046  LV7 8月1日
liu2022  LV14 7月31日
豆崽啾啾 6月27日
暂无贡献等级
暂无贡献等级
dasdascccf  LV10 6月16日
邬松桥  LV8 6月14日
gaohuajian 6月10日
暂无贡献等级
顶部 客服 微信二维码 底部
>扫描二维码关注最代码为好友扫描二维码关注最代码为好友