dao.java.svn-base

监控整个SP系统的SMS信息通道的发送情况并且生成日志。 支持权限判断。

package com.infobank.superchannel.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Hashtable;
import java.util.LinkedList;
import java.util.List;
import java.util.Queue;

import com.infobank.superchannel.daemon.databasecache.DatabaseCacheDaemon;
import com.infobank.superchannel.pojo.BlackList;
import com.infobank.superchannel.pojo.TextMessage;
import com.infobank.superchannel.pojo.TextMessageResultSetting;
import com.infobank.superchannel.pojo.TextMessageStatusSetting;
import com.infobank.superchannel.util.DatabaseConnectionPool;
import com.infobank.superchannel.daemon.databasecache.SaveIncomingMessageTask.IncomingMessageTableSetting;

/**
 * @author LiW
 * @version 创建时间：2007-10-12 类说明: 建立连接取数据并进行相关操作
 * 孙晓光修订
 */
public class DAO {
  // 数据库连接池
  private DatabaseConnectionPool dbcp = DatabaseConnectionPool.getInstance();
  
  // 上次读取信息的主键值映射表
  private Hashtable<String,Integer> lastPKMap=new Hashtable<String,Integer>();
  
  /**
   * 私有构造函数确保单件
   */
  private DAO(){
  }

  // 单件实例
  private static DAO instance=new DAO();

  /**
   * 取得DAO的单件实例
   * @return DAO的单件实例
   */
  public static DAO getInstance(){
    return instance;
  }

  /**
   * 取得待发的新信息
   * @Param dataSource name of datasource
   * @param table 扫描的数据库表格名称
   * @param callbackPattern 特服号码模式
   * @param maxNumber 最多一次取出的信息条数
   * @param statusSetting status setting
   * @param resultSetting result setting
   * @param tag message tag
   * @param listener DAO event listener
   */
  public void getPendingMessage(String dataSource, String table, String callbackPattern, 
      int maxNumber,  TextMessageStatusSetting statusSetting, 
      TextMessageResultSetting resultSetting, String tag, DAOListener listener){
    Statement stmt = null;
    ResultSet rs = null;
    Queue<TextMessage> result=null;
    TextMessage message=null;
    Integer lastPK=lastPKMap.get(table);
    if(lastPK==null)
      lastPK=0;
    Connection conn=dbcp.getConnection(dataSource);
    if(conn==null)
      return;

    try{
      stmt = conn.createStatement();
      StringBuilder sqlBuilder=new StringBuilder();
      sqlBuilder.append("SELECT tran_pr,tran_callback,tran_msg,tran_phone,tran_msgkey FROM ");
      sqlBuilder.append(table);
      sqlBuilder.append(" WHERE tran_status=");
      sqlBuilder.append(statusSetting.getStatus(TextMessage.STATUS.WAITING));
      sqlBuilder.append(" AND tran_pr >");
      sqlBuilder.append(lastPK);
      sqlBuilder.append(" AND tran_callback like '");
      sqlBuilder.append(callbackPattern);
      sqlBuilder.append("' ORDER BY tran_msg, tran_pr DESC");
      String sql=sqlBuilder.toString();

      rs = stmt.executeQuery(sql);

      if(!rs.next()){
        return;
      }

      result=new LinkedList<TextMessage>();
      lastPK = rs.getInt("tran_pr");
      int count=0;
      do{
        message = new TextMessage();
        message.setStatusSetting(statusSetting);
        message.setResultSetting(resultSetting);
        message.setTran_pr(rs.getString("tran_pr"));
        message.setTran_callback(rs.getString("tran_callback"));
        message.setTran_msg(rs.getString("tran_msg"));
        message.setTran_phone(rs.getString("tran_phone"));
        message.setTran_msgkey(rs.getString("tran_msgkey"));
        message.setStatus(TextMessage.STATUS.SENDING);
        message.setResult(TextMessage.RESULT.FAILED);
        message.setTableName(table);
        message.setDataSource(dataSource);
        message.setTag(tag);
        int pk=message.getMessageID();
        if(lastPK<pk)
          lastPK=pk;
        result.add(message);

        if(++count>=maxNumber){
          listener.onMessages(result);

          count=0;
          result.clear();
        }
      }while (rs.next());
      lastPKMap.put(table, lastPK);
      
      if(!result.isEmpty()){
        listener.onMessages(result);
        result.clear();
      }
      
      return ;
    }
    catch(Exception ex){
      ex.printStackTrace();
      return;
    }
    finally{
      if(rs!=null){
        try{
          rs.close();
        }
        catch(SQLException ex){
        }
        rs=null;
      }
      if(stmt!=null){
        try{
          stmt.close();
        }
        catch(SQLException ex){
        }
        stmt=null;
      }
      if(conn!=null){
        try{
          conn.close();
        }
        catch(SQLException ex){

        }
        conn=null;
      }
    }
  } 

  /**
   * 取得黑名单
   * @param dataSource datasource name
   * @param table name of the table where has blacklist
   * @param entCode entprise code pattern
   * @return 黑名单
   */
  public BlackList getBlackList(String dataSource,String table, String entCode){
    Statement stmt = null;
    ResultSet rs = null;
    List<String> result=null;
    Connection conn=dbcp.getConnection(dataSource);

    try{
      if(conn==null)
        return null;
      stmt = conn.createStatement();
      StringBuilder sqlBuilder=new StringBuilder();
      sqlBuilder.append("SELECT bn_phone FROM ");
      sqlBuilder.append(table);
      sqlBuilder.append(" WHERE bn_status='1' AND bn_entcode like '");
      sqlBuilder.append(entCode);
      sqlBuilder.append("'");
      String sql=sqlBuilder.toString();

      rs = stmt.executeQuery(sql);

      if(!rs.next()){
        return null;
      }

      result=new LinkedList<String>();
      do{
        result.add(rs.getString("bn_phone"));
      }while (rs.next());
      return new BlackList(result);
    }
    catch(Exception ex){
      ex.printStackTrace();
      return null;
    }
    finally{
      if(rs!=null){
        try{
          rs.close();
        }
        catch(SQLException ex){
        }
        rs=null;
      }
      if(stmt!=null){
        try{
          stmt.close();
        }
        catch(SQLException ex){
        }
        stmt=null;
      }
      if(conn!=null){
        try{
          conn.close();
        }
        catch(SQLException ex){

        }
        conn=null;
      }
    }
  }

  /**
   * 将信息的状态更新回写到数据库
   * @param statusQueue 状态更新待回写的信息
   */
  public void updateMessageStatus(Queue<TextMessage> statusQueue){
    if(statusQueue==null)
      return;
    TextMessage message = statusQueue.poll();
    if(message==null)
      return;

    Statement stmt = null;
    Connection conn=null;
    String lastDataSource="";
    try{
      while(message!=null){
        if(!lastDataSource.equals(message.getDataSource())){
          lastDataSource=message.getDataSource();
          if(stmt!=null){
            stmt.close();
          }
          if(conn!=null){
            conn.close();
          }
          conn=dbcp.getConnection(lastDataSource);
          if(conn==null){
            message=statusQueue.poll();
            continue;
          }
          stmt = conn.createStatement();
        }
        StringBuilder sqlBuilder=new StringBuilder();
        sqlBuilder.append("UPDATE ");
        sqlBuilder.append(message.getTableName());
        sqlBuilder.append(" SET tran_status=");
        sqlBuilder.append(message.getStatus());
        sqlBuilder.append(", tran_rslt=");
        sqlBuilder.append(message.getResult());
        sqlBuilder.append(" WHERE tran_pr=");
        sqlBuilder.append(message.getMessageID());
        String sql=sqlBuilder.toString();
        stmt.execute(sql);
        sqlBuilder=null;
        message=statusQueue.poll();
      }
    }
    catch(SQLException ex){
      ex.printStackTrace();
    }
    finally{
      if(stmt!=null){
        try{
          stmt.close();
        }
        catch(SQLException ex){

        }
        stmt=null;
      }

      if(conn!=null){
        try{
          conn.close();
        }
        catch(SQLException ex){

        }
        conn=null;
      }
    }
  }

  /**
   * save new incoming messages
   * @param settings incoming text message setting 
   * @param messages new incoming messages
   */
  public void saveIncomingMessage(IncomingMessageTableSetting[] settings, Queue<TextMessage> messages){
    if(messages==null||messages.isEmpty())
      return;

    PreparedStatement stmt = null;
    Connection conn=null;

    try{
      for(IncomingMessageTableSetting setting:settings){
        conn=dbcp.getConnection(setting.dataSource);
        if(conn==null){
          continue;
        }
        
        StringBuilder sqlBuilder=new StringBuilder();
        sqlBuilder.append("INSERT INTO ");
        sqlBuilder.append(setting.tableName);
        sqlBuilder.append(" (tran_phone,tran_callback,tran_status,tran_date,tran_deliverdate,tran_msg) VALUES(?,?,1,now(),now(),?)");
        stmt = conn.prepareStatement(sqlBuilder.toString());
        sqlBuilder=null;

        for(TextMessage message : messages){
          stmt.setString(1, message.getPhone());
          stmt.setString(2, message.getCallback());
          stmt.setString(3, message.getMessage());
          stmt.executeUpdate();
        }
        stmt.close();
        conn.close();
      }
    }
    catch(SQLException ex){
      ex.printStackTrace();
    }
    finally{
      if(stmt!=null){
        try{
          stmt.close();
        }
        catch(SQLException ex){

        }
        stmt=null;
      }

      if(conn!=null){
        try{
          conn.close();
        }
        catch(SQLException ex){

        }
        conn=null;
      }
    }
  }
}