catalogdao.java~142~

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

package xian.bin.dao;

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

public class CatalogDAO {
  private ConnectionPool pcon;
  private String[] cataName;
  //ConnectionDataSource dataSource;
  //DataSource ds;
  public CatalogDAO(){
    pcon=ConnectionPool.getConnectionPool();
    //dataSource=ConnectionDataSource.getConnectionDataSource();
    //ds=dataSource.getDataSource();
  }

 /* public void executeSQL(String sql)throws Exception{
    try{
      Connection con = pcon.getConnection();
      pstmt = con.prepareStatement(sql);
    }
    catch(Exception e){
      e.printStackTrace();
    }
  }*/

  /*public Collection getBigResultSet()throws Exception{
    Collection bigCatalogs=new ArrayList();
    try{
      ResultSet rs=pstmt.executeQuery();
      while(rs.next()){
        CatalogDTO dto=new CatalogDTO();
        dto.setOrder(rs.getInt("order"));
        dto.setCatalog(rs.getString("catalog"));
        bigCatalogs.add(dto);
      }
    }
    catch(Exception e){
      e.printStackTrace();
    }
    return bigCatalogs;
  }*/

  //添加一个大类别
  public void addBigCatalog(String catalog)throws Exception{
    Connection con=pcon.getConnection();
    PreparedStatement pstmt=null;
    try{
      pstmt=con.prepareStatement("insert into bigcatalog values(?)");
      pstmt.setString(1,catalog);
      pstmt.executeUpdate();
    }
    catch(Exception e){
      e.printStackTrace();
    }

  }

  //获得得所有大类别
  public Collection getAllBigCatalog()throws Exception{
    Collection catalogs=new ArrayList();
    Connection con=pcon.getConnection();
    java.sql.Statement stmt=null;
    try{
      stmt=con.createStatement();
      ResultSet rst=stmt.executeQuery("select * from bigcatalog");
      while(rst.next()){
        CatalogDTO dto=new CatalogDTO();
        //System.out.println("Catalog="+rs.getString("catalog"));
        //System.out.println("Catalog="+rs.getString("catalog"));
        //System.out.println("Catalog="+rs.getString("catalog"))
        //String catalog=rs.getString("catalog");
        dto.setOrder(rst.getInt("cataid"));
        dto.setCatalog(rst.getString("cataName"));
        catalogs.add(dto);
      }
    }
    catch(Exception e){
      e.printStackTrace();
    }
    return catalogs;
  }

  public String[] getAllBigCataName()throws Exception{
    String[] cataName=null;int i=0;
    Connection con=pcon.getConnection();
    try{
      PreparedStatement pstmt=con.prepareStatement("select * from bigcatalog");
      ResultSet rs=pstmt.executeQuery();
      while(rs.next()){
        CatalogDTO dto=new CatalogDTO();
       System.out.println(rs.getString("cataName"));
        //cataName[i]=rs.getString(rs.getString("cataName"));
        i=i+1;
      }
    }
    catch(Exception e){
      e.printStackTrace();
    }
    return cataName;
  }


  //获得一个大类别
  public CatalogDTO getBigCatalog(int order)throws Exception{
    CatalogDTO dto=new CatalogDTO();
    Connection con=pcon.getConnection();
    PreparedStatement pstmt=null;
    try{
      pstmt=con.prepareStatement("select * from bigcatalog where cataid=?");
      pstmt.setInt(1,order);
      ResultSet rs=pstmt.executeQuery();

      while(rs.next()){

        dto.setOrder(rs.getInt("cataid"));
        dto.setCatalog(rs.getString("cataName"));

      }
    }
    catch(Exception e){
      e.printStackTrace();
    }
    return dto;
  }

//删除一个大类别
  public void delBigCatalog(int order)throws Exception{
    Connection con=pcon.getConnection();
    java.sql.PreparedStatement pstmt=null;
    try{
      System.out.println("order="+order);
      pstmt=con.prepareStatement("delete from bigcatalog where cataid=?");
      pstmt.setInt(1,order);
      pstmt.executeUpdate();
    }
    catch(Exception e){
      e.printStackTrace();
    }
  }
 //修改一个大类别
  public void updataBigCatalog(int order,String catalog)throws Exception{
    Connection con=pcon.getConnection();
    PreparedStatement pstmt=null;
    try{
      pstmt=con.prepareStatement("update bigcatalog set cataName=? where cataid=?");
      pstmt.setString(1,catalog);
      pstmt.setInt(2,order);
      pstmt.executeUpdate();
    }
    catch(Exception e){
      e.printStackTrace();
    }
  }

  //--------------------------小类别操作--------------------------
  //添加一个小类别
  public void addSmaillCatalog(String bclass,String catalog)throws Exception{
    Connection con=pcon.getConnection();
    PreparedStatement pstmt=null;
    try{
      pstmt=con.prepareStatement("insert into smallcatalog values(?,?)");
      pstmt.setString(1,bclass);
      pstmt.setString(2,catalog);
      pstmt.executeUpdate();
    }
    catch(Exception e){
      e.printStackTrace();
    }

  }

  //获得得所有小类别
  public Collection getAllSmallCatalog()throws Exception{
    Collection smallcatalogs=new ArrayList();
    Connection con=pcon.getConnection();
    PreparedStatement pstmt=null;
    try{
      pstmt=con.prepareStatement("select * from smallcatalog");
      ResultSet rs=pstmt.executeQuery();
      while(rs.next()){
        SmallcatalogDTO dto=new SmallcatalogDTO();

        dto.setCataid(rs.getInt("cataid"));
        dto.setCatalog(rs.getString("cataName"));
        dto.setBclass(rs.getString("bclass"));
        smallcatalogs.add(dto);
      }
    }
    catch(Exception e){
      e.printStackTrace();
    }
    return smallcatalogs;
  }
  //获得一个小类别
  public SmallcatalogDTO getSmallCatalog(int cataid)throws Exception{
    SmallcatalogDTO dto=new SmallcatalogDTO();
    Connection con=pcon.getConnection();
    PreparedStatement pstmt=null;
    try{
      pstmt=con.prepareStatement("select * from smallcatalog where cataid=?");
      pstmt.setInt(1,cataid);
      ResultSet rs=pstmt.executeQuery();

      while(rs.next()){

        dto.setCataid(rs.getInt("cataid"));
        dto.setCatalog(rs.getString("cataName"));
        dto.setBclass(rs.getString("aclass"));
      }
      rs.close();
      pstmt.close();
      con.close();
    }
    catch(Exception e){
      e.printStackTrace();
    }
    return dto;
  }

//删除一个小类别
  public void delSmallCatalog(int cataid)throws Exception{
    Connection con=pcon.getConnection();
    java.sql.PreparedStatement pstmt=null;
    try{
      System.out.println("order="+cataid);
      pstmt=con.prepareStatement("delete from smallcatalog where cataid=?");
      pstmt.setInt(1,cataid);
      pstmt.executeUpdate();
    }
    catch(Exception e){
      e.printStackTrace();
    }
  }
 //修改一个小类别
  public void updataSmallCatalog(int cataid,String catalog,String bclass)throws Exception{
    Connection con=pcon.getConnection();
    PreparedStatement pstmt=null;
    try{
      pstmt=con.prepareStatement("update bigcatalog set cataName=?,bcalss=? where cataid=?");
      pstmt.setString(1,catalog);
      pstmt.setString(2,bclass);
      pstmt.setInt(3,cataid);
      pstmt.executeUpdate();
    }
    catch(Exception e){
      e.printStackTrace();
    }
  }

  //根据一个大类别获得所有类别
  public Collection getbsCatalog(String bclass)throws Exception{
    Collection smallCatalogs=new ArrayList();

    Connection con=pcon.getConnection();
    PreparedStatement pstmt=null;
    try{
      pstmt=con.prepareStatement("select * from smallcatalog where bclass=?");
      pstmt.setString(1,bclass);
      ResultSet rs=pstmt.executeQuery();

      while(rs.next()){
        SmallcatalogDTO dto=new SmallcatalogDTO();
        dto.setCataid(rs.getInt("cataid"));
        dto.setCatalog(rs.getString("cataName"));
        dto.setBclass(rs.getString("aclass"));
        smallCatalogs.add(dto);
      }
    }
    catch(Exception e){
      e.printStackTrace();
    }
    return smallCatalogs;
  }

}