001: import java.text.DateFormat;
002: import java.util.Date;
003: import java.util.TimeZone;
004: import java.sql.Connection;
005: import java.sql.Statement;
006: import java.sql.ResultSet;
007: import java.sql.SQLException;
008: 
009: /**
010:    This bean formats the local time of day for a given date
011:    and city, consulting a database in addition to the standard
012:    time zone ID list.
013: */
014: public class ZoneDBBean
015: { 
016:    /**
017:       Initializes the formatter.
018:    */
019:    public ZoneDBBean()
020:    {
021:       timeFormatter = DateFormat.getTimeInstance();
022:    }
023: 
024:    /**
025:       The city property. 
026:       @return the city for which to report the local time
027:    */
028:    public String getCity()
029:    {
030:       return city;
031:    }
032: 
033:    /**
034:       The city property. 
035:       @param aCity the city for which to report the local time
036:    */
037:    public void setCity(String aCity)
038:    {
039:       city = aCity;
040:    }
041: 
042:    /**
043:       Write-only date property. 
044:       @param aDate the date to be formatted.
045:    */
046:    public void setDate(Date aDate)
047:    {
048:       theDate = aDate;
049:    }
050: 
051:    /**
052:       Read-only time property.
053:       @return the formatted time
054:    */
055:    public String getTime() throws SQLException
056:    {
057:       TimeZone zone = null;
058:       zone = getTimeZoneDB();
059:       if (zone == null) // not found in database
060:          zone = getTimeZone(city); 
061:       if (zone == null) return "not available";
062:       timeFormatter.setTimeZone(zone);
063:       return timeFormatter.format(theDate);     
064:    }
065: 
066:    /**
067:       Looks up the time zone for a city in the database and
068:       the list of time zone IDs.
069:       @param aCity the city for which to find the time zone
070:       @return the time zone or null if no match is found
071:    */
072:    private TimeZone getTimeZoneDB() throws SQLException
073:    {
074:       Connection conn = null;
075:       try
076:       {
077:          conn = DataSourceBean.getConnection();
078:          Statement stat = conn.createStatement();
079:          String query = "SELECT Zone FROM CityZone" 
080:             + " WHERE City = '" + city + "'";
081:          ResultSet result = stat.executeQuery(query);
082:          if (result.next())
083:             return TimeZone.getTimeZone(result.getString(1));
084:          else
085:             return null;
086:       }
087:       finally
088:       {
089:          if (conn != null) conn.close();
090:       }
091:    }
092: 
093:    /**
094:       Looks up the time zone for a city in the list of time
095:       zone IDs
096:       @param aCity the city for which to find the time zone
097:       @return the time zone or null if no match is found
098:    */
099:    private static TimeZone getTimeZone(String city)
100:    {
101:       String[] ids = TimeZone.getAvailableIDs();
102:       for (int i = 0; i < ids.length; i++)
103:          if (timeZoneIDmatch(ids[i], city))
104:             return TimeZone.getTimeZone(ids[i]);
105:       return null;
106:    }
107: 
108:    /**
109:       Checks whether a time zone ID matches a city
110:       @param id the time zone ID (e.g. "America/Los_Angeles")
111:       @param aCity the city to match (e.g. "Los Angeles")
112:       @return true if the ID and city match
113:    */
114:    private static boolean timeZoneIDmatch(String id, String city)
115:    {
116:       String idCity = id.substring(id.indexOf('/') + 1);
117:       return idCity.replace('_', ' ').equals(city);
118:    }
119: 
120:    private Date theDate;
121:    private DateFormat timeFormatter;
122:    private String city;
123: }
124: