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 sql= "select catId, catName from ProductCategory_table";      Connection con=null;      PreparedStatement pstmt=null;      ResultSet rs=null;      CategoryModel lModel;      try{    	  con=DbPool.getConnection();    	  pstmt=con.prepareStatement(sql);    	  rs=pstmt.executeQuery();    	  while(rs.next())    	  {    		  lModel=new CategoryModel();    		 lModel.setCatId(rs.getString(1));    		 lModel.setCatName(rs.getString(2));    		 list.add(lModel);    	  }      }      catch(Exception e)      {    	        }            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;      }}