goodsaction.java

JavaBean+Jsp 开发的《手机商城系统》

package com.test.ps.goods;


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

import com.test.ps.util.DBUtil;

public class GoodsAction
{
	int rowsPerpage=6;
	//返回goods表中的记录数
    private int getGoodsCount(String sql)
    {
    	int row=0; 	
    	ResultSet rs=DBUtil.getResult(sql);
    	try {
			rs.last();
			row=rs.getRow();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	return row;
    }
    //根据总行数计算总页数
    private int countTotalPage(int totalRows)
    {
    	if(totalRows%rowsPerpage==0)
    		return totalRows/rowsPerpage;
    	else
    		return totalRows/rowsPerpage+1;
    }
    public PageData getPageData(String page)
    {
    	String sql="select * from goods";
    	int totalRows=getGoodsCount(sql);
    	
    	int totalPage=countTotalPage(totalRows);
    	
    	int pageNum=Integer.parseInt(page);
    	if(pageNum<1)pageNum=1;
    	if(pageNum>totalPage)pageNum=totalPage;
    	PageData pagedata=new PageData();
    	ArrayList<Goods>  data=new ArrayList<Goods> ();
    	int num=(pageNum-1)*rowsPerpage;
    	String sql1="select * from goods limit "+num+","+rowsPerpage;
    	ResultSet rs=DBUtil.getResult(sql1);
     	try {
				while(rs.next())
				{
					Goods good=new Goods();
					good.setGId((rs.getInt("gId")));
					good.setGoodsImage(rs.getString("goodsImage"));
					good.setGoodsName(rs.getString("goodsName"));
					good.setGoodsPrice(rs.getFloat("goodsPrice"));
					data.add(good);
				}
				pagedata.setCurPage(pageNum);
				pagedata.setData(data);
				pagedata.setTotalPage(totalPage);
		  } 
     	catch (SQLException e)
		 {
			e.printStackTrace();
		 }
     	return pagedata;
    }
    public PageData getParentPageData(String page,int categoryId,String keyWord)
    {   
    	PageData pagedata=new PageData();
    	String sql="";
    	if(keyWord==null||keyWord.equals(""))
    	 sql="select * from goods where categoryId in(select categoryId from GoodsCategory where parentId="+categoryId+")";
    	else
    		sql="select * from goods where categoryId in(select categoryId from GoodsCategory where parentId="+categoryId+")"+"and goodsName like '%"+keyWord+"%'";
    	int totalRows=getGoodsCount(sql);
    	int totalPage=countTotalPage(totalRows);
    	int pageNum=Integer.parseInt(page);
    	if(pageNum<1)pageNum=1;
    	if(pageNum>totalPage)pageNum=totalPage;
    	if(totalPage==0){pagedata=null;return pagedata;}
    	ArrayList<Goods>  data=new ArrayList<Goods> ();
    	int num=(pageNum-1)*rowsPerpage;
    	String sql1=sql+" "+"limit "+num+","+rowsPerpage;
    	ResultSet rs=DBUtil.getResult(sql1);
    	if(rs.equals(null)){pagedata=null;return pagedata;}
     	try {
				while(rs.next())
				{
					Goods good=new Goods();
					good.setCategoryId(rs.getInt("categoryId"));
					good.setGoodsCode(rs.getString("goodsCode"));
					good.setDescr(rs.getString("descr"));
					good.setHotgoods(rs.getString("hotgoods"));
					
					good.setGId((rs.getInt("gId")));
					good.setGoodsImage(rs.getString("goodsImage"));
					good.setGoodsName(rs.getString("goodsName"));
					good.setGoodsPrice(rs.getFloat("goodsPrice"));
					data.add(good);
				}
				pagedata.setCurPage(pageNum);
				pagedata.setData(data);
				pagedata.setTotalPage(totalPage);
		  } 
     	catch (SQLException e)
		 {System.out.println("6");
			e.printStackTrace();
		 }
     	return pagedata;
    }


    public PageData getPageData(String page,int categoryId)
    {   
    	PageData pagedata=new PageData();
    	String sql="select * from goods where categoryId='"+categoryId+"'";
    	int totalRows=getGoodsCount(sql);
    	int totalPage=countTotalPage(totalRows);
    	int pageNum=Integer.parseInt(page);
    	if(pageNum<1)pageNum=1;
    	if(pageNum>totalPage)pageNum=totalPage;
    	if(totalPage==0){pagedata=null;return pagedata;}
    	ArrayList<Goods>  data=new ArrayList<Goods> ();
    	int num=(pageNum-1)*rowsPerpage;
    	if(num<0)
    		num=0;
    	String sql1="select * from goods where categoryId='"+categoryId+"'limit "+num+","+rowsPerpage;
    	
    	ResultSet rs=DBUtil.getResult(sql1);
    	if(rs.equals(null)){pagedata=null;return pagedata;}

     	try {
				while(rs.next())
				{
					Goods good=new Goods();
					good.setGId((rs.getInt("gId")));
					good.setGoodsImage(rs.getString("goodsImage"));
					good.setGoodsName(rs.getString("goodsName"));
					//System.out.println(rs.getString("goodsName"));
					good.setGoodsPrice(rs.getFloat("goodsPrice"));
					//+++++++
					good.setCategoryId(rs.getInt("categoryId"));
					good.setGoodsCode(rs.getString("goodsCode"));
					good.setDescr(rs.getString("descr"));
					good.setHotgoods(rs.getString("hotgoods"));
					//+++++++
					data.add(good);
				}
				pagedata.setCurPage(pageNum);
				pagedata.setData(data);
				pagedata.setTotalPage(totalPage);

		  } 
     	catch (SQLException e)
		 {
			e.printStackTrace();
		 }
     	return pagedata;
    }

    //得到搜索结果
    public PageData getPageData(String page,int categoryId,String keyWord)
    {   
    	String sql;
    	//System.out.println("the first"+sql);
    	PageData pagedata=new PageData();
    	if(categoryId!=0)
    	{
    		if(keyWord==null||keyWord.equals(""))
    			sql="select * from goods where categoryId="+categoryId;
    		else
    			sql="select * from goods where categoryId="+categoryId+" "+"and goodsName like '%"+keyWord+"%'";
    	}
    	else
    	{
    		if(keyWord==null||keyWord.equals(""))
    			sql="select * from goods ";
    		else
        		sql="select * from goods where goodsName like '%"+keyWord+"%'";
    	}
        int totalRows=getGoodsCount(sql);
    	int totalPage=countTotalPage(totalRows);
    	int pageNum=Integer.parseInt(page);
    	if(pageNum<1)pageNum=1;
    	if(pageNum>totalPage)pageNum=totalPage;
    	if(totalPage==0){pagedata=null;return pagedata;}
        ArrayList<Goods>  data=new ArrayList<Goods> ();
    	int num=(pageNum-1)*rowsPerpage;
    	String sql2=sql+" "+"limit "+num+","+rowsPerpage;
    	ResultSet rs=DBUtil.getResult(sql2);
    	if(rs.equals(null)){pagedata=null;return pagedata;}
     	try {
				while(rs.next())
				{
					Goods good=new Goods();
					good.setGId((rs.getInt("gId")));
					//+++++++
					good.setCategoryId(rs.getInt("categoryId"));
					good.setGoodsCode(rs.getString("goodsCode"));
					good.setDescr(rs.getString("descr"));
					good.setHotgoods(rs.getString("hotgoods"));
					
					good.setGoodsImage(rs.getString("goodsImage"));
					good.setGoodsName(rs.getString("goodsName"));
					good.setGoodsPrice(rs.getFloat("goodsPrice"));
					data.add(good);
				}
				pagedata.setCurPage(pageNum);
				pagedata.setData(data);
				pagedata.setTotalPage(totalPage);
		  } 
     	catch (SQLException e)
		 {
			e.printStackTrace();
		 }
    	
     	return pagedata;
    	
    }
 
  //获得商品信息
    public static Goods getGoodsInf(String goodsCode){
    	Goods goods=new Goods();
    	ResultSet rs=DBUtil.getResult("select * from goods where goodsCode='"+goodsCode+"'");
    	try{
    		rs.next();
    		goods.setGoodsCode(goodsCode);
    		goods.setGoodsName(rs.getString("goodsName"));
        	goods.setGoodsImage(rs.getString("goodsImage"));
        	goods.setGoodsPrice(rs.getInt("goodsPrice"));
        	goods.setDescr(rs.getString("descr"));
        	goods.setHotgoods(rs.getString("hotgoods"));
    	}catch(Exception e){
    		e.printStackTrace();
    	}
    	return goods;
    }
    

  //商品名检查
    public static int checkName(Goods goods){
    	int result=0;
    	String sql="select * from goods where goodsName='"+goods.getGoodsName()+"'";
    	ResultSet rs=DBUtil.getResult(sql);
    	try{
    		if(rs.next())
    			result=1;
    		else
    			result=0;
    	}catch(Exception e){
    		e.printStackTrace();
    	}
    	return result;
    }
  //商品编号检查
    public static int checkCode(Goods goods){
    	int result=0;
    	String sql="select * from goods where goodsCode='"+goods.getGoodsCode()+"'";
    	ResultSet rs=DBUtil.getResult(sql);
    	try{
    		if(rs.next())
    			result=1;
    		else
    			result=0;
    	}catch(Exception e){
    		e.printStackTrace();
    	}
    	return result;
    }

    //获得所有商品个数
    public int getAllGoodsNum(){
    	int n=getGoodsCount("select * from goods");
    	return n;
    }
  //增加商品
    public static void addGoods(Goods goods){
    	try{
    		String gDate=new SimpleDateFormat("yyyy-MM-dd").format(goods.getGDate());
    		String sql="insert into goods(goodsCode,goodsName,goodsPrice,goodsImage,descr,gDate,hotgoods,categoryId) values('"
					+goods.getGoodsCode()+"','"+goods.getGoodsName()+"',"+goods.getGoodsPrice()+",'"+goods.getGoodsImage()+"','"
					+goods.getDescr()+"','"+gDate+"','"+goods.getHotgoods()+"',"+goods.getCategoryId()+")";
			//DBUtil.executeUpdate(sql);
			DBUtil.executeUpdate(sql);
		}catch(Exception ex){
			ex.printStackTrace();
		}
    }
    
    //修改商品
    public static void changeGoods(Goods newGood,String oGoodCode){
    	String gDate=new SimpleDateFormat("yyyy-MM-dd").format(newGood.getGDate());
    	try{
			String sql="update goods " +"set goodsName='"+newGood.getGoodsName()+"',goodsPrice="+newGood.getGoodsPrice()
					+",goodsImage='"+newGood.getGoodsImage()+"',descr='"+newGood.getDescr()+"',gDate='"+gDate+"',hotgoods='"
					+newGood.getHotgoods()+"',categoryId="+newGood.getCategoryId()+" where goodsCode='"+oGoodCode+"'";
			DBUtil.executeUpdate(sql);
		}catch(Exception ex){
			ex.printStackTrace();
		}
    }
    
    //删除商品
    public static void deleteGoods(String goodsCode){
	    try{
			String sql="delete from goods where goodsCode='"+goodsCode+"'";
			DBUtil.executeUpdate(sql);
		}catch(Exception ex){
			ex.printStackTrace();
		}
    }
    public static Goods getGoods(int gId)
	{
   	     Goods good=new Goods();
		//String sql="select * from goods where categoryId in(select categoryId form goodscategory where parentId='"+categoryId+"' )";
		 String sql="select * from goods where gId='"+gId+"'";
		 ResultSet rs=DBUtil.getResult(sql);
		 try {
			while(rs.next())
			 {
			
				 good.setGId(gId);
				 good.setGoodsImage(rs.getString("goodsImage"));
				 good.setGoodsName(rs.getString("goodsName"));
				// System.out.println(rs.getString("goodsName"));
				 good.setGoodsPrice(rs.getFloat("goodsPrice"));
				 good.setDescr(rs.getString("descr"));
				
			 }
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		 return  good;
	}
    

}