package com.publish.shop.bulletin.dao;import java.sql.*;import java.util.ArrayList;//import com.publish.shop.util.dao.SerialNoDAO;//import com.publish.shop.util.db.DbManager;import com.publish.shop.util.db.DbPool;//import com.publish.shop.util.javabeans.Pager;import com.publish.shop.util.javabeans.Debug;import com.publish.shop.util.javabeans.DateTimeUtility;//import com.publish.shop.util.javabeans.DateUtility;import com.publish.shop.bulletin.javabeans.BulletinModel;//import org.apache.struts.util.LabelValueBean;import com.publish.shop.security.javabeans.UserModel;import com.publish.shop.security.javabeans.UserProxy;public class BulletinDAO {  public BulletinDAO() {  }  public String insert(BulletinModel model) throws Exception{    String sql = "insert into BulletinInfo_table(bulletinTitle,bulletinBody,inputUserId)"                 +" values(?,?,?)";    Connection con = null;    PreparedStatement stmt = null;    String bulletinId = "0";    try{      con = DbPool.getConnection();      stmt = con.prepareStatement(sql);      stmt.setString(1,model.getBulletinTitle());      stmt.setString(2,model.getBulletinBody());      stmt.setInt(3,model.getInputUserId());      stmt.executeUpdate();      con.commit();    }catch(Exception e){      con.rollback();      throw e;    }    finally{      DbPool.closeStatement(stmt);      DbPool.closeConnection(con);    }    return bulletinId;  }    public void update(BulletinModel model) throws Exception{      String sql = "update BulletinInfo_table set bulletinTitle='"+model.getBulletinTitle()+"',bulletinBody='"+model.getBulletinBody()+"',updateDate='"+DateTimeUtility.getCurTimeStamp()+"',isValid='"+model.getIsValid()          +" where bulletinId='"+model.getBulletinId()+"'";      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 bulletinId) throws Exception{        String sql = "delete from BulletinInfo_table where bulletinId='" + bulletinId +"'";        Connection con = null;        PreparedStatement stmt = null;        try        {          con = DbPool.getConnection();          stmt = con.prepareStatement(sql);          stmt.executeUpdate();          con.commit();        }        catch(Exception e)        {          throw e;        }        finally        {          DbPool.closeStatement(stmt);          DbPool.closeConnection(con);        }    }    public ArrayList query(BulletinModel model) throws Exception{        ArrayList list = new ArrayList();        String sql = "select bulletinId,bulletinTitle,bulletinBody,inputDate,updateDate,inputUserId,bulletinPoint,bulletinSort from BulletinInfo_table where isValid='Y'";        if(model.getBulletinId()!=0){          sql += " and bulletinId like '%"+model.getBulletinId()+"%'";        }        if(model.getBulletinTitle()!=null && !model.getBulletinTitle().equals("")){          sql += " and bulletinTitle like '%"+model.getBulletinTitle()+"%'";        }        if(model.getBulletinBody()!=null && !model.getBulletinBody().equals("")){          sql += " and bulletinBody like '%"+model.getBulletinBody()+"%'";        }        if(model.getInputDate()!=null && !model.getInputDate().equals("")){          sql += " and inputDate='"+model.getInputDate()+"'";        }        if(model.getUpdateDate()!=null && !model.getUpdateDate().equals("")){          sql += " and updateDate='"+model.getUpdateDate()+"'";        }        if(model.getInputUserId()!=0){          sql += " and inputUserId='"+model.getInputUserId()+"'";        }        Debug.println(sql);        ResultSet rs = null;         Connection con = null;         Statement statement = null;         try{            con = DbPool.getConnection();            statement = con.createStatement();            rs = statement.executeQuery(sql);            while(rs.next()){              BulletinModel bulletinmodel = new BulletinModel();              bulletinmodel.setBulletinId(rs.getInt(1));              bulletinmodel.setBulletinTitle(rs.getString(2));              bulletinmodel.setBulletinBody(rs.getString(3));              bulletinmodel.setInputDate(rs.getString(4));              bulletinmodel.setUpdateDate(rs.getString(5));              bulletinmodel.setInputUserId(rs.getInt(6));              bulletinmodel.setBulletinPoint(rs.getString(7));              bulletinmodel.setBulletinSort(rs.getString(8));              UserProxy lpProxy = new UserProxy();              UserModel model0 = lpProxy.queryUserById(bulletinmodel.getInputUserId());              if (model0 != null)                bulletinmodel.setInputUserName(model0.getUserName());              list.add(bulletinmodel);            }        }catch(Exception e){            throw e;        }        finally{          DbPool.closeResultSet(rs);          DbPool.closeStatement(statement);          DbPool.closeConnection(con);        }        return list;    }    public BulletinModel queryBulletin(int bulletinId) throws Exception{      BulletinModel model = null;      String sql = "select bulletinId,bulletinTitle,bulletinBody,inputDate,updateDate,inputUserId,bulletinPoint,bulletinSort from BulletinInfo_table";      sql += " where bulletinId = '"+bulletinId+"'";      ResultSet rs = null;      Connection con = null;      Statement statement = null;      try{        con = DbPool.getConnection();        statement = con.createStatement();        rs = statement.executeQuery(sql);        while(rs.next()){              model = new BulletinModel();              model.setBulletinId(rs.getInt(1));              model.setBulletinTitle(rs.getString(2));              model.setBulletinBody(rs.getString(3));              model.setInputDate(rs.getString(4));              model.setUpdateDate(rs.getString(5));              model.setInputUserId(rs.getInt(6));              model.setBulletinPoint(rs.getString(7));              model.setBulletinSort(rs.getString(8));              UserProxy lpProxy = new UserProxy();              UserModel model0 = lpProxy.queryUserById(model.getInputUserId());              if (model0 != null)                model.setInputUserName(model0.getUserName());        }      }catch(Exception e){          throw e;      }      finally{        DbPool.closeResultSet(rs);        DbPool.closeStatement(statement);        DbPool.closeConnection(con);      }      return model;    }}