staticdbdata.java

我在加拿大学习的一个比较复杂的在线银行程序.

package com.ebusiness.ebank.util;

/**
 * <p>Title: </p>
 * <p>Description: This class load the static read-only data from database and store
 *                 them in memory for fast access. The class is a singlton and the data
 *                 will be fetched on the first call to one of its public/protected methods </p>
 * <p>Copyright: Copyright (c) 2005</p>
 * <p>Company: eBusiness Inc., All right reserved</p>
 * @author not attributable
 * @version 1.0
 */

import java.util.Map;
import java.util.HashMap;
import java.util.List;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Collection;
import java.util.Collections;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.apache.log4j.MDC;

import com.ebusiness.ebank.servicedelegate.ServiceLocator;
import com.ebusiness.ebank.servicedelegate.ServiceLocatorException;
import com.ebusiness.ebank.exception.ErrorMessages;

public class StaticDBData {

  //Hold country data. The key is country_languageCode
  //e.g. country_en, country_fr,  and the value is a list of OptionLabelValue objects
   private Map     countryMap;

   //Hold state/province data. The key is countryCode.state_province_languageCode,
   //e.g. CA.state_province_en, and the value is a list of OptionLabelValue for
   //the country_language.
   private Map     stateProvMap;

   //Hold state/province code. The key is country code, and the value is a list of
   //state/province codes for that country
   private Map     stateProvCodeMap;

   //Hold the data from Flag_Description tabel. The key is TableName.ColumnName_language,
   //e.g. cardholder.status_en, and the value is a list of OptionLableValue objects
   private Map     flagMap;

   //Hold codes only. It's for fast validation of country
   private List    countryCodes;

   private DataSource ds;

   private Logger log;

   private static StaticDBData instance;

   static
   {
       instance = new StaticDBData();
   }

   private StaticDBData()
   {
       //initialize the HashMaps and List
       countryMap = new HashMap();
       stateProvMap = new HashMap();
       stateProvCodeMap = new HashMap();
       flagMap = new HashMap();
       countryCodes = new ArrayList();

       log = Logger.getLogger(this.getClass());

       //load country and state/province data to the above HashMaps
       retrieveData();

       //sort all state/province list for fast search purpose
       Collection values = stateProvCodeMap.values();
       for (Iterator i = values.iterator(); i.hasNext(); )
       {
           Collections.sort((List)i.next());
       }

       //sort country code for fast search purpose
       Collections.sort(countryCodes);
   }

   public static StaticDBData getInstance()
   {
       return instance;
   }

   public Map getCountryMap()
   {
      return this.countryMap;
   }

   public Map getStateProvMap()
   {
      return this.stateProvMap;
   }

   //Return flag_description values
   public Map getFlagMap()
   {
      return this.flagMap;
   }

   //The following two methods are intended for ValidationHelper class
   public List getCountryCodes()
   {
      return this.countryCodes;
   }

   public Map getStateProvCodeMap()
   {
      return this.stateProvCodeMap;
   }

  /**
  *  This method retrieves Country, State/Province and other static data from Country, State_Province
  *  and Flag_description tables in eBank database schema, and stores them to three HashMaps for future use.
  *  It is executed only once when the class is being instantiated.
  */
  private void retrieveData()
  {
     Connection con = null;
     PreparedStatement ps = null;
     ResultSet rs = null;
     List list = null;
     MDC.put(Constants.USER_ID, "WebInitial");

     try
     {
         //get eBank datasource
         DataSource ds = ServiceLocator.getInstance().getDataSource(Constants.DATASOURCE_EBANK);
         con = ds.getConnection();

         //load country data
         ps = con.prepareStatement("SELECT * FROM " + Constants.COUNTRY + " WHERE language is not NULL ORDER BY language, name" );
         log.info(" Start loading " + Constants.COUNTRY + " information from database...");
         rs = ps.executeQuery();

         String language = "";
         String currLanguage = "";
         String code = "";
         list = new ArrayList();
         while (rs.next())
         {
             currLanguage = rs.getString("language");
             if (!language.equals(currLanguage))
             {
                 if(!language.equals(""))
                 {
                     countryMap.put(Constants.COUNTRY + "_" + language, list);
                     list = new ArrayList();
                 }
                 language = currLanguage;
             }
             code = rs.getString("code");
             if (currLanguage.equals("en"))
                countryCodes.add(code);
             list.add(new OptionLabelValue(rs.getString("name"), code));
         }
         countryMap.put(Constants.COUNTRY + "_" + language, list);

         log.info("Country information loaded successfully");
         ps.close();

         //load state_province data
         ps = con.prepareStatement("SELECT * FROM " + Constants.STATE_PROVINCE + " WHERE language is not NULL ORDER BY language, country, name");
         log.info("Start loading " + Constants.STATE_PROVINCE + " information");
         rs = ps.executeQuery();

         language = "";
         currLanguage = "";
         String country = "";
         String currCountry = "";
         list = new ArrayList();
         List codeList = new ArrayList();
         while (rs.next())
         {
             currLanguage = rs.getString("language");
             currCountry = rs.getString("country");
             if (!language.equals(currLanguage) || !country.equals(currCountry))
             {
                 if(!country.equals(""))
                 {
                     stateProvMap.put(country + "." + Constants.STATE_PROVINCE + "_" + language, list);
                     list = new ArrayList();
                     if (language.equals("en"))
                     {
                        stateProvCodeMap.put(country, codeList);
                        codeList = new ArrayList();
                     }
                 }
                 language = currLanguage;
                 country = currCountry;
             }
             code = rs.getString("code");
             list.add(new OptionLabelValue(rs.getString("name"), code));
             if (currLanguage.equals("en"))
                  codeList.add(code);
         }
         stateProvMap.put(country + "." + Constants.STATE_PROVINCE + "_" + language, list);
         if (currLanguage.equals("en"))
            stateProvCodeMap.put(country, codeList);
         log.info("State/Province information loaded successfully");
         ps.close();

         //Load Flag_description
         ps = con.prepareStatement("SELECT * FROM " + Constants.FLAG_DESCRIPTION + " WHERE language is not NULL ORDER BY language, table_name, column_name, display_value" );
         log.info(" Start loading " + Constants.FLAG_DESCRIPTION + " information from database...");
         rs = ps.executeQuery();

         language = "";
         currLanguage = "";
         String tName = "";
         String currTName = "";
         String cName = "";
         String currCName = "";
         list = new ArrayList();
         while (rs.next())
         {
             currLanguage = rs.getString("language");
             currTName = rs.getString("table_name");
             currCName = rs.getString("column_name");
             if (!language.equals(currLanguage) || !tName.equals(currTName) || !cName.equals(currCName))
             {
                 if(!language.equals(""))
                 {
                     flagMap.put(tName + "." + cName + "_" + language, list);
                     list = new ArrayList();
                 }
                 language = currLanguage;
                 tName = currTName;
                 cName = currCName;
             }
             list.add(new OptionLabelValue(rs.getString("display_value"), rs.getString("flag_value")));
         }
         flagMap.put(tName + "." + cName + "_" + language, list);

         log.info(Constants.FLAG_DESCRIPTION + " information loaded successfully");
     }
     catch (SQLException se)
     {
         log.fatal(ErrorMessages.OTHER_DATABASE_ERROR, se);
     }
     catch (ServiceLocatorException se)
     {
         log.fatal(ErrorMessages.FAIL_TO_LOOKUP_DATASOURCE + ": " + Constants.DATASOURCE_EBANK);
     }
     catch (Exception e)
     {
         log.fatal(e.getMessage(), e);
     }
     finally
     {
         close(ps, con);
     }
  }

  private void closeStatement(Statement s)
  {
     try
     {
         if (s != null)
             s.close();
     }
     catch (SQLException se)
     {
         log.error(ErrorMessages.FAIL_TO_CLOSE_DATABASE_STATEMENT, se);
     }
  }

  private void closeConnection(Connection con)
  {
     try
     {
         if (con != null)
             con.close();
     }
     catch (SQLException se)
     {
         log.error(ErrorMessages.FAIL_TO_CLOSE_DATABASE_CONNECTION, se);
     }
  }

  private void close(Statement s, Connection con)
  {
     this.closeStatement(s);
     this.closeConnection(con);
  }
}