bulletindao.java

一个常用的电子商城的站点源码

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.setString(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()!=null && !model.getBulletinId().equals("")){
          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()!=null && !model.getInputUserId().equals("")){
          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.getString(1));
              bulletinmodel.setBulletinTitle(rs.getString(2));
              bulletinmodel.setBulletinBody(rs.getString(3));
              bulletinmodel.setInputDate(rs.getString(4));
              bulletinmodel.setUpdateDate(rs.getString(5));
              bulletinmodel.setInputUserId(rs.getString(6));
              bulletinmodel.setBulletinPoint(rs.getString(7));
              bulletinmodel.setBulletinSort(rs.getString(8));
              UserProxy lpProxy = new UserProxy();
              UserModel model0 = lpProxy.queryUser(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(String 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.getString(1));
              model.setBulletinTitle(rs.getString(2));
              model.setBulletinBody(rs.getString(3));
              model.setInputDate(rs.getString(4));
              model.setUpdateDate(rs.getString(5));
              model.setInputUserId(rs.getString(6));
              model.setBulletinPoint(rs.getString(7));
              model.setBulletinSort(rs.getString(8));
              UserProxy lpProxy = new UserProxy();
              UserModel model0 = lpProxy.queryUser(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;
    }

}