package com.sxt.dao.impl;

import java.sql.ResultSet;

import java.util.List;

import org.apache.commons.dbutils.handlers.BeanHandler;

import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.sxt.bean.Goods;

import com.sxt.dao.GoodsDao; import com.sxt.util.JDBCUtil; import com.sxt.util.PageBean;

public class GoodsDaoImpl implements GoodsDao {

@Overridepublic PageBean findGoods(String gname, String pStart, String pEnd,        String dStart, String dEnd, String gstatus,int page,int rows) throws Exception {    String sql = "select * from goods where 1=1";    if (null != gname && !"".equals(gname)) {        sql += " and gname like '%" + gname + "%'";    }    if (null != pStart && !"".equals(pStart)) {        sql += " and gprice >= " + pStart + "";    }    if (null != pEnd && !"".equals(pEnd)) {        sql += " and gprice <= " + pEnd + "";    }    //注意日期格式化    if (null != dStart && !"".equals(dStart)) {        sql += " and DATE_FORMAT(prodate,'%Y-%m-%d') >= '" + dStart + "'";    }    if (null != dEnd && !"".equals(dEnd)) {        sql += " and DATE_FORMAT(prodate,'%Y-%m-%d') <= '" + dEnd + "'";    }    if (null != gstatus && !"".equals(gstatus)) {        sql += " and gstatus = " + gstatus + "";    }    String fysql="select count(1) from("+sql+") a";    sql+=" order by gid desc limit "+(page-1)*rows+","+rows;    //查询一共多条记录    ResultSet rs = JDBCUtil.query(fysql);    int total=0;    if(rs.next()){        total=rs.getInt(1);    }    List
     
       gs = new BeanListHandler
      
       (Goods.class).handle(JDBCUtil.query(sql));    PageBean pb = new PageBean();    pb.setTotal(total);    pb.setRows(gs);    JDBCUtil.close();    return pb;}@Overridepublic boolean delGoods(String gid) throws Exception {    String sql = "delete from goods where gid = " + gid;    int i = JDBCUtil.update(sql);    if (i > 0) {        return true;    }    return false;}@Overridepublic boolean saveGoods(Goods g) throws Exception {    int i = 0;    if (g != null) {        String sql = "";        if (g.getGid() != null) {            // 修改            sql += "update goods set gname=?,gprice=?,gnum=?,prodate=?,gaddr=?,gtel=?,gstatus=?,gurl=? where gid=?";            i = JDBCUtil.update(sql, g.getGname(), g.getGprice(),                    g.getGnum(), g.getProdate(),                     g.getGaddr(), g.getGtel(), g.getGstatus(), g.getGurl(),                    g.getGid());        } else {            // 新增            sql += "insert into goods values(null,?,?,?,?,?,?,?,?)";            i = JDBCUtil.update(sql, g.getGname(), g.getGprice(),                    g.getGnum(), g.getProdate(),                    g.getGaddr(), g.getGtel(), g.getGstatus(), g.getGurl());        }    }    if (i > 0) {        return true;    }    return false;}@Overridepublic Goods findGoodsById(String gid) throws Exception {    String sql = "select * from goods where gid = " + gid;    ResultSet rs = JDBCUtil.query(sql);    BeanHandler
       
         bh = new BeanHandler
        
         (Goods.class);    Goods g = bh.handle(rs);    JDBCUtil.close();    return g;}
        
       
      
     

}