B2C网站+JSP+SQLServer2005

package s2jsp.sg.dao.impl; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import s2jsp.sg.dao.Goods; import s2jsp.sg.dbtool.DB; import s2jsp.sg.project.Goodss; public class GoodsImp extends DB implements Goods { //分页显示所有商品信息 //@Override public List selectGoods(int page,int rows) { //查询出所有的商品信息的行数 int rowsAll=0;//数据库中所有的行数 int pageAll=0;//总共的页数 String sqlC="select count(*) from tb_goods"; ResultSet rsC=this.getResultSet(sqlC,null); try { if(rsC.next()){ rowsAll=rsC.getInt(1); } } catch (SQLException e1) { e1.printStackTrace(); }finally{ this.closeAll(); } if(page<=1){ page=1; } //不显示的行数 int myRows=0; if(page>1){ myRows=(page-1)*rows;//页数减1乘以行数 } //求出总的页数 if(rowsAll%rows==0){//除以要显示的行数 pageAll=rowsAll/rows; }else{ pageAll=rowsAll/rows+1; } //分页显示商品信息 List list=new ArrayList(); String sql="select top "+rows+" * from tb_goods where id not in (select top "+myRows+" id from tb_goods order by creaTime desc) order by creaTime desc"; ResultSet rs=this.getResultSet(sql,null); try { while(rs.next()){ Goodss good=new Goodss(); good.setId(rs.getInt("id")); good.setBigId(rs.getInt("bigId")); good.setSmallId(rs.getInt("smallId")); good.setGoodsName(rs.getString("goodsName")); good.setGoodsFrom(rs.getString("goodsFrom")); good.setIntroduce(rs.getString("introduce")); good.setCreaTime(rs.getString("creaTime")); good.setNowPrice(rs.getDouble("nowPrice")); good.setFreePrice(rs.getDouble("freePrice")); good.setNumber(rs.getInt("number")); good.setPirture(rs.getString("pirture")); good.setMark(rs.getInt("mark")); good.setBuyNum(rs.getInt("buyNum")); list.add(good); } return list; } catch (SQLException e) { e.printStackTrace(); }finally{ this.closeAll(); } return null; } //分页显示大类有商品信息 //@Override public List selectGoodsBigType(int page,int rows,int bigId) { List list=null; //查询出所有的商品信息的行数 int rowsAll=0;//数据库中所有的行数 int pageAll=0;//总共的页数 String sqlC="select count(*) from tb_goods where bigId=?"; Object[] params={bigId}; ResultSet rsC=this.getResultSet(sqlC,params); try { if(rsC.next()){ rowsAll=rsC.getInt(1); } } catch (SQLException e1) { e1.printStackTrace(); }finally{ this.closeAll(); } if(page<=1){ page=1; } //判断是否需要分页 if(rowsAll>3){ //不显示的行数 int myRows=0; if(page>1){ myRows=(page-1)*rows;//页数减1乘以行数 } //求出总的页数 if(rowsAll%rows==0){//除以要显示的行数 pageAll=rowsAll/rows; }else{ pageAll=rowsAll/rows+1; } //分页显示商品信息 list=new ArrayList(); String sql="select top "+rows+" * from tb_goods where id not in (select top "+myRows+" id from tb_goods where bigId="+bigId+" order by creaTime asc) and bigId="+bigId+" order by creaTime asc"; ResultSet rs=this.getResultSet(sql,null); try { while(rs.next()){ Goodss good=new Goodss(); good.setId(rs.getInt("id")); good.setBigId(rs.getInt("bigId")); good.setSmallId(rs.getInt("smallId")); good.setGoodsName(rs.getString("goodsName")); good.setGoodsFrom(rs.getString("goodsFrom")); good.setIntroduce(rs.getString("introduce")); good.setCreaTime(rs.getString("creaTime")); good.setNowPrice(rs.getDouble("nowPrice")); good.setFreePrice(rs.getDouble("freePrice")); good.setNumber(rs.getInt("number")); good.setPirture(rs.getString("pirture")); good.setMark(rs.getInt("mark")); good.setBuyNum(rs.getInt("buyNum")); list.add(good); } return list; } catch (SQLException e) { e.printStackTrace(); }finally{ this.closeAll(); } }else{//不需要分页 //分页显示商品信息 list=new ArrayList(); String sql="select * from tb_goods where bigId="+bigId+""; ResultSet rs=this.getResultSet(sql,null); try { while(rs.next()){ Goodss good=new Goodss(); good.setId(rs.getInt("id")); good.setBigId(rs.getInt("bigId")); good.setSmallId(rs.getInt("smallId")); good.setGoodsName(rs.getString("goodsName")); good.setGoodsFrom(rs.getString("goodsFrom")); good.setIntroduce(rs.getString("introduce")); good.setCreaTime(rs.getString("creaTime")); good.setNowPrice(rs.getDouble("nowPrice")); good.setFreePrice(rs.getDouble("freePrice")); good.setNumber(rs.getInt("number")); good.setPirture(rs.getString("pirture")); good.setMark(rs.getInt("mark")); good.setBuyNum(rs.getInt("buyNum")); list.add(good); } return list; } catch (SQLException e) { e.printStackTrace(); }finally{ this.closeAll(); } } return null; } public static void main(String[] args) { GoodsImp imp=new GoodsImp(); List list=imp.selectGoodsBigType(1,3,7); System.out.println(list.size()); // for(int i=0;i<list.size();i++){ // Goodss good=(Goodss)list.get(i); // System.out.println(good.getMark()+"\n"+good.getCreaTime()+good.getGoodsName()); // } // List list=imp.selectGoods(4,3); // for(int i=0;i<list.size();i++){ // Goodss good=(Goodss)list.get(i); // System.out.println(good.getMark()+"\n"+good.getCreaTime()+good.getGoodsName()); // } } // 以商品编号为条件删除信息 //@Override public int deleteGoodsById(int id) { String sql="delete from tb_goods where id=?"; Object[] params={id}; int result=this.getCount(sql, params); return result; } // 添加操作 //@Override public int insertGoods(Goodss obj) { String sql="insert into tb_goods values(?,?,?,?,?,?,?,?,?,?,?,?)"; Object[] params={obj.getBigId(),obj.getSmallId(),obj.getGoodsName(),obj.getGoodsFrom(),obj.getIntroduce(),obj.getCreaTime(),obj.getNowPrice(),obj.getFreePrice(),obj.getNumber(),obj.getPirture(),obj.getMark(),obj.getBuyNum()}; int result=this.getCount(sql, params); return result; } // 设置特价价格的方法 //@Override public int managerPrice(Goodss obj) { String sql="update tb_goods set freePrice=? where id=?"; Object[] params={obj.getFreePrice(),obj.getId()}; int result=this.getCount(sql, params); return result; } // 以商品的大类别的编号为条件查询信息 //@Override public List selectBig(int big) { List list=new ArrayList(); String sql="select * from tb_goods where bigId=?"; Object[] params={big}; ResultSet rs=this.getResultSet(sql, params); try { while(rs.next()){ Goodss good=new Goodss(); good.setId(rs.getInt("id")); good.setBigId(rs.getInt("bigId")); good.setSmallId(rs.getInt("smallId")); good.setGoodsName(rs.getString("goodsName")); good.setGoodsFrom(rs.getString("goodsFrom")); good.setIntroduce(rs.getString("introduce")); good.setCreaTime(rs.getString("creaTime")); good.setNowPrice(rs.getDouble("nowPrice")); good.setFreePrice(rs.getDouble("freePrice")); good.setNumber(rs.getInt("number")); good.setPirture(rs.getString("pirture")); good.setMark(rs.getInt("mark")); good.setBuyNum(rs.getInt("buyNum")); list.add(good); } return list; } catch (SQLException e) { e.printStackTrace(); }finally{ this.closeAll(); } return null; } // 全部查询 //@Override public List selectGoods() { List list=new ArrayList(); String sql="select * from tb_goods"; ResultSet rs=this.getResultSet(sql, null); try { while(rs.next()){ Goodss good=new Goodss(); good.setId(rs.getInt("id")); good.setBigId(rs.getInt("bigId")); good.setSmallId(rs.getInt("smallId")); good.setGoodsName(rs.getString("goodsName")); good.setGoodsFrom(rs.getString("goodsFrom"));