categorydao.java

jsp网站开发四“酷”全书原码

package com.publish.shop.product.dao;

import java.sql.*;
import java.util.ArrayList;
import com.publish.shop.util.db.DbPool;
import com.publish.shop.product.javabeans.CategoryModel;
import org.apache.struts.util.LabelValueBean;

public class CategoryDAO {
  public CategoryDAO()
  {
  }

  public ArrayList queryCategory(CategoryModel model) throws Exception{
    ArrayList list = new ArrayList();
    String sql = "select catId,catName from ProductCategory_table";
    int sqlLength = sql.length();
    if(model.getCatId()!=null && !model.getCatId().trim().equals("")){
      sql = sql+" where catId like '%"+model.getCatId().trim()+"%'";
    }
    if(model.getCatName()!=null && !model.getCatName().trim().equals("")){
      if(sql.length()>sqlLength)
        sql = sql+" and catName like '%"+model.getCatName().trim()+"%'";
      else
        sql = sql+" where catName like '%"+model.getCatName().trim()+"%'";
    }
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;
    CategoryModel lModel = null;
    try{
      con = DbPool.getConnection();
      stmt = con.createStatement();
      rs = stmt.executeQuery(sql);
      while(rs.next()){
        lModel = new CategoryModel();
        lModel.setCatId(rs.getString(1));
        lModel.setCatName(rs.getString(2));
        list.add(lModel);
      }
    }catch(Exception e){
      throw e;
    }
    finally{
      DbPool.closeResultSet(rs);
      DbPool.closeStatement(stmt);
      DbPool.closeConnection(con);
    }
    return list;
  }

  public CategoryModel queryCategory(String catId) throws Exception{
    CategoryModel lModel = null;
    String sql = "select catId,catName from ProductCategory_table"
                 +" where catId = '"+catId+"'";
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;
    try{
      con = DbPool.getConnection();
      stmt = con.createStatement();
      rs = stmt.executeQuery(sql);
      while(rs.next()){
        lModel = new CategoryModel();
        lModel.setCatId(rs.getString(1));
        lModel.setCatName(rs.getString(2));
      }
    }catch(Exception e){
      throw e;
    }
    finally{
      DbPool.closeResultSet(rs);
      DbPool.closeStatement(stmt);
      DbPool.closeConnection(con);
    }
    return lModel;
  }

  public CategoryModel queryCatByName(String catName) throws Exception{
      CategoryModel lModel = null;
      String sql = "select catId,catName from ProductCategory_table"
                   +" where catName = '"+catName+"'";
      Connection con = null;
      Statement stmt = null;
      ResultSet rs = null;
      try{
        con = DbPool.getConnection();
        stmt = con.createStatement();
        rs = stmt.executeQuery(sql);
        while(rs.next()){
          lModel = new CategoryModel();
          lModel.setCatId(rs.getString(1));
          lModel.setCatName(rs.getString(2));
        }
      }catch(Exception e){
        throw e;
      }
      finally{
        DbPool.closeResultSet(rs);
        DbPool.closeStatement(stmt);
        DbPool.closeConnection(con);
      }
      return lModel;
    }

    public void insert(CategoryModel model) throws Exception{
      String sql = "insert into ProductCategory_table(catId,catName)"
                   +" values('"+model.getCatId()+"','"+model.getCatName()+"')";
      Connection con = null;
      Statement stmt = null;
      int rs = 0;
      try{
        con = DbPool.getConnection();
        stmt = con.createStatement();
        rs = stmt.executeUpdate(sql);
        con.commit();
      }catch(Exception e){
        throw e;
      }
      finally{
        DbPool.closeStatement(stmt);
        DbPool.closeConnection(con);
      }
    }

    public void update(CategoryModel model) throws Exception{
      String sql = "update ProductCategory_table set catName='"+model.getCatName()+"'"
                   +" where groupId='"+model.getCatId()+"'";
      Connection con = null;
      Statement stmt = null;
      try{
        con = DbPool.getConnection();
        stmt = con.createStatement();
        stmt.executeUpdate(sql);
        con.commit();
      }catch(Exception e){
        throw e;
      }
      finally{
        DbPool.closeStatement(stmt);
        DbPool.closeConnection(con);
      }
    }

    public void delete(String catId) throws Exception{
      String sql = "delete from ProductCategory_table where catId=?";
      Connection con = null;
      PreparedStatement stmt = null;
      try{
        con = DbPool.getConnection();
        stmt = con.prepareStatement(sql);
        stmt.setString(1,catId);
        stmt.executeUpdate();
        con.commit();
      }catch(Exception e){
        throw e;
      }
      finally{
        DbPool.closeStatement(stmt);
        DbPool.closeConnection(con);
      }
    }

    public ArrayList getAllCatLabel() throws Exception{
      ArrayList list = new ArrayList();
      String sql01 = "select catId, catName from ProductCategory_table";
      return list;
    }

    public ArrayList getCatLabel() throws Exception{
        ArrayList list = new ArrayList();
        String sql = "select catId,catName from ProductCategory_table";
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try{
          con = DbPool.getConnection();
          stmt = con.createStatement();
          rs = stmt.executeQuery(sql);
          CategoryModel lModel;
          while(rs.next()){
            String catId = rs.getString("catId");
            String catName = rs.getString("catName");
            LabelValueBean label = new LabelValueBean(catName,catId);
            list.add(label);
          }
        }catch(Exception e){
          throw e;
        }
        finally{
          DbPool.closeResultSet(rs);
          DbPool.closeStatement(stmt);
          DbPool.closeConnection(con);
        }
        return list;
      }

}