productdao.java

一个很不错的电子商务后台管理系统 这是一个电子商务网站的后台管理系统 要运行此系统必须具备以下条件 1.首先要把SNSQL文本中的SQL脚本确保在SQL Server中执行 2.包Se

package xian.bin.dao;

import java.util.*;
//import xian.bin.product.*;
import xian.bin.dto.*;
import xian.bin.db.*;
import java.sql.*;
import javax.sql.*;

public class ProductDAO {
  //private ConnectionPool pcon;
  private ConnectionDataSource dataSource;
  private DataSource ds;
  public ProductDAO() {
    //pcon=ConnectionPool.getConnectionPool();
    try{
      dataSource = ConnectionDataSource.getConnectionDataSource();
      ds = dataSource.getDataSource();
    }
    catch(Exception e){
      e.printStackTrace();
    }
  }

  public void addProduct(String pid,String pname, String brand, String spec,
                         String unit, float price, float assprice, String pictrue,
                         String explain, String bigcatalog, String smaillcatalog) throws Exception {
    Connection con=ds.getConnection();
    java.sql.PreparedStatement pstmt=null;
    try{
      pstmt=con.prepareStatement("insert into product values(?,?,?,?,?,?,?,?,?,?,?)");
      pstmt.setString(1,pid);
      pstmt.setString(2,pname);
      pstmt.setString(3,brand);
      pstmt.setString(4,spec);
      pstmt.setString(5,unit);
      pstmt.setFloat(6,price);
      pstmt.setFloat(7,assprice);
      pstmt.setString(8,pictrue);
      pstmt.setString(9,explain);
      pstmt.setString(10,bigcatalog);
      pstmt.setString(11,smaillcatalog);
      pstmt.executeUpdate();
    }
    catch(Exception e){
      e.printStackTrace();
    }
  }
  //获得所有商品信息
  public Collection getAllProduct() throws Exception {
    Collection products=new ArrayList();
    Connection con=ds.getConnection();
    PreparedStatement pstmt=null;
    try{
      pstmt=con.prepareStatement("select * from product");
      ResultSet rs=pstmt.executeQuery();
      while(rs.next()){
        ProductDTO dto=new ProductDTO();
        dto.setPid(rs.getString("pID"));
        dto.setPname(rs.getString("pname"));
        dto.setBrand(rs.getString("brand"));
        dto.setSpec(rs.getString("spec"));
        dto.setUnit(rs.getString("unit"));
        dto.setPrice(rs.getFloat("price"));
        dto.setAssprice(rs.getFloat("assprice"));
        dto.setPictrue(rs.getString("pictrue"));
        dto.setExplain(rs.getString("explain"));
        dto.setBigcatalog(rs.getString("bigcatalog"));
        dto.setSmaillcatalog(rs.getString("smaillcatalog"));
        products.add(dto);
      }
    }
    catch(Exception e){
      e.printStackTrace();
    }
    return products;
  }
  //删除商品信息
  public void delProduct(String pid) throws Exception {
    Connection con=ds.getConnection();
    java.sql.PreparedStatement pstmt=null;
    try{
      pstmt=con.prepareStatement("delete from product where pid=?");
      pstmt.setString(1,pid);
      pstmt.executeUpdate();
    }
    catch(Exception e){
      e.printStackTrace();
    }
  }
  //获得一个商品信息name
  public Collection getProduct(String pname) throws Exception {
    Collection products=new ArrayList();
    Connection con=ds.getConnection();
    java.sql.PreparedStatement pstmt=null;
    try{
      pstmt=con.prepareStatement("select * from product where pname=?");
      pstmt.setString(1,pname);
      ResultSet rs=pstmt.executeQuery();
      while(rs.next()){
        ProductDTO dto=new ProductDTO();
        dto.setPid(rs.getString("pID"));
        dto.setPname(rs.getString("pname"));
        dto.setBrand(rs.getString("brand"));
        dto.setSpec(rs.getString("spec"));
        dto.setUnit(rs.getString("unit"));
        dto.setPrice(rs.getFloat("price"));
        dto.setAssprice(rs.getFloat("assprice"));
        dto.setPictrue(rs.getString("pictrue"));
        dto.setExplain(rs.getString("explain"));
        dto.setBigcatalog(rs.getString("bigcatalog"));
        dto.setSmaillcatalog(rs.getString("smaillcatalog"));
        products.add(dto);
      }
    }
    catch(Exception e){
      e.printStackTrace();
    }
    return products;
  }

  //获得一个商品信息ID
  public ProductDTO getOneProduct(String pid) throws Exception {
    ProductDTO dto=new ProductDTO();
    Connection con=ds.getConnection();
    java.sql.PreparedStatement pstmt=null;
    try{
      pstmt=con.prepareStatement("select * from product where pid=?");
      pstmt.setString(1,pid);
      ResultSet rs=pstmt.executeQuery();
      while(rs.next()){
        dto.setPid(rs.getString("pID"));
        dto.setPname(rs.getString("pname"));
        dto.setBrand(rs.getString("brand"));
        dto.setSpec(rs.getString("spec"));
        dto.setUnit(rs.getString("unit"));
        dto.setPrice(rs.getFloat("price"));
        dto.setAssprice(rs.getFloat("assprice"));
        dto.setPictrue(rs.getString("pictrue"));
        dto.setExplain(rs.getString("explain"));
        dto.setBigcatalog(rs.getString("bigcatalog"));
        dto.setSmaillcatalog(rs.getString("smaillcatalog"));
      }
    }
    catch(Exception e){
      e.printStackTrace();
    }
    return dto;
  }

  //修改商品信息
  public void updateProduct(String pid, String pname, String brand, String spec,
                            String unit, float price, float assprice, String pictrue, String explain, String bigcatalog, String smaillcatalog) throws Exception {
    Connection con=ds.getConnection();
    java.sql.PreparedStatement pstmt=null;
    try{
      pstmt=con.prepareStatement("update product set pname=?,brand=?,spec=?,unit=?,price=?,assprice=?,pictrue=?,explain=?,bigcatalog=?,smaillcatalog=? where pid=?");

      pstmt.setString(1,pname);
      pstmt.setString(2,brand);
      pstmt.setString(3,spec);
      pstmt.setString(4,unit);
      pstmt.setFloat(5,price);
      pstmt.setFloat(6,assprice);
      pstmt.setString(7,pictrue);
      pstmt.setString(8,explain);
      pstmt.setString(9,bigcatalog);
      pstmt.setString(10,smaillcatalog);
      pstmt.setString(11,pid);
      pstmt.executeUpdate();
    }
    catch(Exception e){
      e.printStackTrace();
    }
  }

  //显示所有大类
  public String[] getBigCatalog()throws Exception{
    String[] bigcatalogs=new String[10];
    int i=0;
    Connection con=ds.getConnection();
    PreparedStatement pstmt=null;
    try{
      pstmt=con.prepareStatement("select * from bigcatalog");
      ResultSet rs=pstmt.executeQuery();
      while(rs.next()){
        System.out.println("BigCatalog="+ bigcatalogs[i]);
        bigcatalogs[i]=rs.getString("catalog");
        System.out.println("BigCatalog="+ bigcatalogs[i]);
        i=i+1;
      }
    }
    catch(Exception e){
      e.printStackTrace();
    }
    return bigcatalogs;
  }

  //显示所有小类
  public String[] getSmaillCatalog()throws Exception{
    String[] smaillcatalogs=new String[10];
    int i=0;
    Connection con=ds.getConnection();
    PreparedStatement pstmt=null;
    try{
      pstmt=con.prepareStatement("select * from smallcatalog");
      ResultSet rs=pstmt.executeQuery();
      while(rs.next()){
        smaillcatalogs[i]=rs.getString("catalog");
        System.out.println("SmallCatalog="+smaillcatalogs[i]);
        i=i+1;
      }
    }
    catch(Exception e){
      e.printStackTrace();
    }
    return smaillcatalogs;
  }

  //显示所有品牌
  public String[] getBrand()throws Exception{
    String[] brands=new String[10];
    int i=0;
    Connection con=ds.getConnection();
    PreparedStatement pstmt=null;
    try{
      pstmt=con.prepareStatement("select * from brand");
      ResultSet rs=pstmt.executeQuery();
      while(rs.next()){
        brands[i]=rs.getString("brandname");
        i=i+1;
      }
    }
    catch(Exception e){
      e.printStackTrace();
    }
    return brands;
  }

}