Hibernate and multiple Database connection

Tips:1

Using spring, this becomes MUCH easier

Using annotation mappings as an example: Configuration cfg1 = new AnnotationConfiguration(); cfg1.configure("/hibernate-oracle.cfg.xml"); cfg1.addAnnotatedClass(SomeClass.class); // mapped classes cfg1.addAnnotatedClass(SomeOtherClass.class); SessionFactory sf1 = cfg1.buildSessionFactory();   Configuration cfg2 = new AnnotationConfiguration(); cfg2.configure("/hibernate-mysql.cfg.xml"); cfg2.addAnnotatedClass(SomeClass.class); // could be the same or different than above cfg2.addAnnotatedClass(SomeOtherClass.class); SessionFactory sf2 = cfg2.buildSessionFactory(); Then use sf1 and sf2 to get the sessions for each database. For mapping files, you just use cfg.addClass instead of addAnnotatedClass. Put the cfg.xml files in the root package in this case. Those will have the Oracle or MySQL dialect and connection information.

 

Tips:2

Using Database.properties file

Database.properties file original

database.password=<password>

database.url=jdbc\:mysql\://localhost\:3306/twodb

database.username=<username>

database.driverClassName=com.mysql.jdbc.Driver

Database.properties file modified

database.password1=<password1>

database.url1=jdbc\:mysql\://localhost\:3306/twodbone

database.username1=<username1>

database.password2=<password1>

database.url2=jdbc\:mysql\://localhost\:3306/twodbtwo

database.username2=<username2>

database.driverClassName=com.mysql.jdbc.Driver

Persistence.xml file modified

<........name="persistenceUnit1" transaction-type="RESOURCE_LOCAL">

---------

<........name="persistenceUnit2" transaction-type="RESOURCE_LOCAL">

Note: duplicated the persistence unit code for the 2nd database and define unique ids.

ApplicationContext.xml file modified

<bean..........destroy-method="close" id="dataSource2">

      <property name="driverClassName" value="${database.driverClassName}"/>

      <property name="url" value="${database.url2}"/>

      <property name="username" value="${database.username2}"/>

      <property name="password" value="${database.password2}"/>

Web.xml file modified

<filter>

<filter-name>Spring OpenEntityManagerInViewFilter1</filter-name>

      <filter-class>

.............

</param-value>

      </init-param>

</filter>

 

 

Tips: 3

Configuring Multiple Databases in Hibernate 3.0

hibernate.cfg.xml

·         Databases are configured in an XML file called hibernate.cfg.xml.

·         There are many configuration parameters available that makes the mapping of domain model to relational model easier.

·         The same configurations can be done from your Java class uning org.hibernate.cfg.Configuration class.

The mapping details of the Employee class are available in the Employee.hbm.xml file.

To persist, the Employee object in 2 databases, we need to create 2 configuration files.

 

oracleconfigOne.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN"

"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

<session-factory>

<property name="hibernate.dialect">org.hibernate.dialect.OracleDialect</property>

<property name="hibernate.connection.driver_class">oracle.jdbc.OracleDriver</property>

<property name="hibernate.connection.url"> jdbc:oracle:thin:@10.154.117.76:1521:oracleONE</property>

<property name="hibernate.connection.username">user</property>

<property name="hibernate.connection.password">password</property>

<property name="hibernate.hbm2ddl.auto">create</property>

<property name="hibernate.show_sql">true</property>

<mapping resource="Employee.hbm.xml" />

</session-factory>

</hibernate-configuration>

 

oracleconfigtwo.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN"

"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

<session-factory>

<property name="hibernate.dialect">org.hibernate.dialect.OracleDialect</property>

<property name="hibernate.connection.driver_class">oracle.jdbc.OracleDriver</property>

<property name="hibernate.connection.url"> jdbc:oracle:thin:@10.154.117.76:1521:oracleTWO</property>

<property name="hibernate.connection.username">user</property>

<property name="hibernate.connection.password">password</property>

<property name="hibernate.hbm2ddl.auto">create</property>

<property name="hibernate.show_sql">true</property>

<mapping resource="Employee.hbm.xml" />

</session-factory>

</hibernate-configuration>

 

IEmployeeDAO.java

package hibernatepack.samples;

import java.util.List;

public interface IEmployeeDAO {

public void findAllEmployees();

public void insertEmployee(Employee e);

}

 

EmloyeeDaoImpl.java

public class EmployeeDaoImpl implements IEmployeeDAO {

SessionFactory sessionFactory1 = new  Configuration().configure("oracleconfigONE.cfg.xml").buildSessionFactory();

SessionFactory sessionFactory2 = new Configuration().configure("oracleconfigTWO.cfg.xml").buildSessionFactory();

Session session = null;

Transaction transaction = null;

public void findAllEmployees() {

ArrayList empList = new ArrayList();

try {

session = sessionFactory1.openSession();

transaction = session.beginTransaction();

transaction.begin();

Criteria crit = session.createCriteria(Employee.class);

empList = (ArrayList) crit.list();

System.out.println("Records from Oracle Database");

for (Employee emp : empList) {

System.out.println(emp.getEmpid() + " " + emp.getEmpname() + " " + emp.getSalary());

}

session.close();

session = sessionFactory2.openSession();

Criteria crit1 = session.createCriteria(Employee.class);

empList = (ArrayList) crit1.list();

System.out.println("Records from Derby Database");

for (Employee emp : empList) {

System.out.println(emp.getEmpid() + " " + emp.getEmpname() + " " + emp.getSalary());

}

session.close();

} catch (Exception he) {

he.printStackTrace();

}

}

public void insertEmployee(Employee e) {

try {

session = sessionFactory1.openSession();

transaction = session.beginTransaction();

transaction.begin();

session.save(e);

transaction.commit();

session.close();

session = sessionFactory2.openSession();

transaction = session.beginTransaction();

transaction.begin();

session.save(e);

transaction.commit();

session.close();

} catch (HibernateException he) {

he.printStackTrace();

}

}

}

Tips: 4

Multiple Databases for Different Clients

One session factory per client, created dynamically when users log in

create multiple SessionFactory (one per database)

·         Use some unique Map<SomeKey, SessionFactory>

·         If a SessionFactory hasn't been created yet, build it and store it in the map.

PUZZEL: Create Connection object and use method sessionFactory.openSession(connection); to get hibernate session, it’s correct?

A Hibernate SessionFactory can only handle one DataSource at a time, and generally speaking each DataSource refers to one and only one database. So if you need multiple databases, then the simplest solution is almost certainly multiple SessionFactory instances.

·         There should be different cfg.xml files for different databases

·         Then simply use Configuration object of the Hibernate whenever you want to connect to your second database.

Configuration config = new Configuration().configure("<complete path to your cfg.xml file>");

SessionFactory sessionFactory = config.buildSessionFactory();

Session session = sessionFactory.getCurrentSession();

session.beginTransaction();

 

Use the @Qualifier Annotation on the method that injects the sessionFactory:

 

@Autowired

public ProgramaDAOHibernate(@Qualifier("sessionFactory2") SessionFactory sessionFactory) {

super(sessionFactory);

}

 

 

Tips: 5

Configuring Hibernate to use multiple database schemas

·        An application that manages several clients (or customer if you prefer)

·        Dedicated schema on a database for each clients

·        Application handles only one client at a time, i.e. the user must switch from one client to another in the application (at runtime, no restart of the application) in order to access the data from this new client.

 

Tips: 6

Spring has “AbstractRoutingDataSource”

(Refer:  http://blog.springsource.com/2007/01/23/dynamic-datasource-routing/)

In your case, you need to switch the datasource during one request, so you'll need to have 2 transactions, switching the datasource between them by changing the datasource indicator on the ThreadLocal:

1. For these DAOs, demarcate the wrapping Service-layer either with distinct packages, class names, or method names
2. Indicate to Spring that the Service-layer method calls should run in their own transactional contexts by annotating with @Transactional(propogation=Propogation.REQUIRES_NEW)
3. Create an Aspect (using AspectJ annotation @Aspect) to fire around the service-layer method calls (using @Around) to set the ThreadLocal value before the method call, and to unset it afterwards
4. In the @Controller, simply call the Service-layer methods. The Aspect will take care of setting the values to indicate which datasource to use, and the AbstractRoutingDataSource will use that datasource in the context of each transaction.

Multi-tenancy in Hibernate (supported by Spring 3.1)

 

 

 

J2EE Scheduler

Schedule-based execution in a J2EE Web Application

 

Schedule-based execution in a J2EE Web Application.

 

Scheduled tasks fall into two categories:

• System level task, such as system backup. Usually, it is done by system administrators or operators at data center.
• Application level, such as business report. Usually, this is the responsibilities of business units.

Usually, your application server is in your data center or at a hosting company. You want to control application tasks at your office on a client machine. Different departments want to manage their own tasks. For example, warehouses run inventory checking tasks while sales run sale analysis tasks. If anything goes wrong at the system level (for example database is down), the scheduler should send alert notifications to the administrator on duty. If you run scheduled business patrol tasks and find an abnormal condition at business level, the scheduler should send alarm notifications to the people in charge. All notifications should be handled by the scheduler automatically.

All the above issues require scheduler to support distributed computing environment with the following features:

• The scheduler can run on any machine in the network at any time.
• The scheduler can be run from data center and offices simultaneously.
• The scheduled task should run once and only once at the scheduled time, regardless of how many of the schedulers are running.
• Missed tasks can be detected and alerted. 

 

Vertical Partitioning in DataBase -- Benifits

Vertical partitioning

 

Suppose we have a table with many columns and also millions of rows. Some of the columns in the table are very frequently accessed in some queries, and most of the columns in the table are less frequently accessed in some other queries.

As the table size is huge (in terms of number of columns and rows), any data retrieval query from the table performs slowly. So, this table could be portioned based on the frequency of access of the columns. That is, we can split the table into two or more tables (partitions) where each table would contain a few columns from the original tables.

 In our case, a partition of this table should contain the columns that are frequently accessed by queries, and another partition of this table should contain the columns that are less frequently accessed by other queries. Splitting the columns vertically and putting them in different thinner partitions is called vertical partitioning.

Another good way for applying vertical partitioning could be to partition the indexed columns and non-indexed columns into separate tables. Also, vertical partitioning could be done by splitting LOB or VARCHARMAX columns into separate tables.

Like horizontal partitioning, vertical partitioning also allows to improve query performance (because queries now have to scan less data pages internally, as the other column values from the rows have been moved to another table), but this type of partitioning is to be done carefully, because if there is any query that involves columns from both partitions, then the query processing engine would require joining two partitions of the tables to retrieve data, which in turn would degrade performance.

Working With Time Zones

my server's 9PM is probably not your 9PM

ColdFusion And Java

JRE (Java Runtime Environment) that powers ColdFusion has the latest version of the Olson Timezone Database. This database provides encapsulated logic for each timezone such that we don't have to kill ourselves trying to manage all the rules and applications.

 

This timezone database can be accessed using the java.util.TimeZone class. Each timezone is referenced by an ID that is specific to a location, timezone, and daylight savings time (DST) combination.

 

The java.util.TimeZone class encapsulates the logic surrounding the GMT (Greenwich Mean Time) and DST (Daylight Savings Time) rules while the java.util.GregorianCalendar class allows us to navigate through dates and times in the given timezone.

 

Mapping date/time to a database when using different time zones, requires some thought. It all looks very simple to begin with but I've seen a lot of silly mistakes being made...  

 

There are basically two distinct kind of requirements when working with time zones:

• Normalize to a common time - preferably UTC.
• Keep the original time and time zone information.

The first case can be applied in most situations. As an example consider that we want to create visibility of the whereabouts of a mailed parcel on a global trip. It is sufficient to register each event in UTC and show it to users in their own local time.
  
The second case applies for example when we want to preserve the time and time zone as communicated by someone. E.g. a customer in New York talking to support in London for a Service hosted in Brussels. The support person should communicate with the customer relative to his time zone: "You reported a service error today at 9:30 AM and yesterday at 9:15 AM". We could deduce the customer's time zone from his location - but perhaps he normally works from Los Angeles instead of New York. A conversation a few weeks later reminding him of the problem he reported as "the 6:15 AM incident" would be utterly confusing.

Time in different time zones

Date	Europe/Brussels	America/New York	London
March 13, 2009	15:46	10:46	14:46

  

 Practical Solution

What I propose is to keep all the time data in your database in UTC instead. This has a few advantages:

• The times in the database can be compared, visually and in SQL WHERE clauses.
• Using Java, you don't have to do any conversions when reading or writing to the database.
• When changing DST , values don't jump when transitioning to summer or winter time.

manual interventions (mainly INSERT and UDPDATE) should take into account that UTC must be used, not local time

 Let's illustrate that with a concrete example:

Mapping dates normalized to UTC (using java.util.Date) 

 

A user John in New York enters a date/time for a planned teleconference with his colleague Mark in Brussels. He types 2009-07-06 09:30 , the application parses that to a Date - containing a long value of 1246887000000. This Date represents 14:30 UTC . It is inserted as such in the database.

 

On the other side of the Atlantic ocean, Mark checks the date of the planned meeting, the application reads the date from the database - it still represents 14:30 UTC and contains the same long value of 1246887000000. Mark's session contains a formatter that formats this date to 2009-07-06 15:30

 

 

The basic point is that each user's session contains a SimpleDateFormat , configured to the time zone of that particular user and parses or formats localized string representations of the actual (UTC) date in the database.

 

 Library Classes

The java.util package has a few classes for working with time.

 

Date

java.util.Date is a wrapper for a long value that corresponds with the number of milliseconds since January 1, 1970, 00:00:00 UTC. You can obtain the current time using new Date() or System.out.currentTimeMillis() .  

 

We make one Date, and use SimpleDateFormat to render the date in the context of a particular time zone.

  
long time = 1000*60*60*24*23;
Date date = new Date(time);

TimeZone brusselsTZ = TimeZone.getTimeZone("Europe/Brussels");
TimeZone newYorkTZ = TimeZone.getTimeZone("America/New_York");

SimpleDateFormat formatterNY = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
SimpleDateFormat formatterBR = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");

formatterNY.setTimeZone(newYorkTZ);
formatterBR.setTimeZone(brusselsTZ);

System.out.print("New York: ");
System.out.println(formatterNY.format(date));
//New York: 1970-01-23 19:00:00.000
System.out.print("Brussels: ");
System.out.println(formatterBR.format(date));
//Brussels: 1970-01-24 01:00:00.000

 

TimeZone   

A standard Java install contains all time zone information, it is maintained for all timezones and gets updates - as long as you apply them.

 

Calendar   

GregorianCalendar.

 

Calendar calendar = new GregorianCalendar();
//calendar.setTime(date);
//calendar.setTimeInMillis(time);

calendar.set(2009, Calendar.AUGUST, 21);//Gotcha: months are 0-based, January == 0, December == 11
calendar.add(Calendar.DATE, 28);
System.out.println(formatterBR.format(calendar.getTime()));

//The time part was the time when new GregorianCalendar() was executed
//2009-09-18 13:12:53.341

 Database support for timezones

Databases have some support for working with date/time values taking into account the existence of timezones. Most support is focused on interpreting and showing date/time in the timezone of the 'user' or connection. The fact that in a typical architecture, database connections are shared between application users, changing timezones per connection is not a feasible approach

 

PostgreSQL has one datatype, TIMESTAMP , that contains date and time data. Two datatypes can be combined with timezone data: TIMESTAMP and TIME, but in accordance with the first remark above, this last one is rather problematic.

 

Oracle supports TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE that enable input and output taking time zones into consideration.

TIMESTAMP WITH TIME ZONE includes a time zone offset in its - literal - value. The data is stored as UTC with an offset. Someone in Brussels or New York will see the same date/time/time zone information.

 '2009-07-15 8:27:00 US/Pacific'
 '2009-12-15 15:33:00 -6:00'

TIMESTAMP WITH LOCAL TIME ZONE includes an implicit time zone offset in its value . The data stored in the database is normalized to the database time zone, the offset is not stored

 

MySQL stores date/time data in UTC and interpretes input depending on the current timezone of the host, server or connection depending on configurations.

    

MySQL has two built in datatypes that contain date and time data: DATETIME and TIMESTAMP. Neither contains timezone information.
Conversions between timezones can be done with the function CONVERT_TZ

 SELECT CONVERT_TZ('2009-12-01 10:25','+00:00','-6:00')
 SELECT CONVERT_TZ('2009-12-01 10:25','UTC','Europe/Brussels')

 

MySQL Timezone Create Table SQL Script

MySQL Timezone Create Table SQL Script

 

CREATE TABLE `timezones` (
`id` tinyint(3) unsigned NOT NULL auto_increment,
`timezone_location` varchar(30) NOT NULL default '',
`gmt` varchar(11) NOT NULL default '',
`offset` tinyint(2) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=143 ;

--
-- Dumping data for table `timezones`
--


INSERT INTO `timezones` VALUES (1, 'International Date Line West', '(GMT-12:00)', -12),
(2, 'Midway Island', '(GMT-11:00)', -11),
(3, 'Samoa', '(GMT-11:00)', -11),
(4, 'Hawaii', '(GMT-10:00)', -10),
(5, 'Alaska', '(GMT-09:00)', -9),
(6, 'Pacific Time (US & Canada)', '(GMT-08:00)', -8),
(7, 'Tijuana', '(GMT-08:00)', -8),
(8, 'Arizona', '(GMT-07:00)', -7),
(9, 'Mountain Time (US & Canada)', '(GMT-07:00)', -7),
(10, 'Chihuahua', '(GMT-07:00)', -7),
(11, 'La Paz', '(GMT-07:00)', -7),
(12, 'Mazatlan', '(GMT-07:00)', -7),
(13, 'Central Time (US & Canada)', '(GMT-06:00)', -6),
(14, 'Central America', '(GMT-06:00)', -6),
(15, 'Guadalajara', '(GMT-06:00)', -6),
(16, 'Mexico City', '(GMT-06:00)', -6),
(17, 'Monterrey', '(GMT-06:00)', -6),
(18, 'Saskatchewan', '(GMT-06:00)', -6),
(19, 'Eastern Time (US & Canada)', '(GMT-05:00)', -5),
(20, 'Indiana (East)', '(GMT-05:00)', -5),
(21, 'Bogota', '(GMT-05:00)', -5),
(22, 'Lima', '(GMT-05:00)', -5),
(23, 'Quito', '(GMT-05:00)', -5),
(24, 'Atlantic Time (Canada)', '(GMT-04:00)', -4),
(25, 'Caracas', '(GMT-04:00)', -4),
(26, 'La Paz', '(GMT-04:00)', -4),
(27, 'Santiago', '(GMT-04:00)', -4),
(28, 'Newfoundland', '(GMT-03:30)', -3),
(29, 'Brasilia', '(GMT-03:00)', -3),
(30, 'Buenos Aires', '(GMT-03:00)', -3),
(31, 'Georgetown', '(GMT-03:00)', -3),
(32, 'Greenland', '(GMT-03:00)', -3),
(33, 'Mid-Atlantic', '(GMT-02:00)', -2),
(34, 'Azores', '(GMT-01:00)', -1),
(35, 'Cape Verde Is.', '(GMT-01:00)', -1),
(36, 'Casablanca', '(GMT)', 0),
(37, 'Dublin', '(GMT)', 0),
(38, 'Edinburgh', '(GMT)', 0),
(39, 'Lisbon', '(GMT)', 0),
(40, 'London', '(GMT)', 0),
(41, 'Monrovia', '(GMT)', 0),
(42, 'Amsterdam', '(GMT+01:00)', 1),
(43, 'Belgrade', '(GMT+01:00)', 1),
(44, 'Berlin', '(GMT+01:00)', 1),
(45, 'Bern', '(GMT+01:00)', 1),
(46, 'Bratislava', '(GMT+01:00)', 1),
(47, 'Brussels', '(GMT+01:00)', 1),
(48, 'Budapest', '(GMT+01:00)', 1),
(49, 'Copenhagen', '(GMT+01:00)', 1),
(50, 'Ljubljana', '(GMT+01:00)', 1),
(51, 'Madrid', '(GMT+01:00)', 1),
(52, 'Paris', '(GMT+01:00)', 1),
(53, 'Prague', '(GMT+01:00)', 1),
(54, 'Rome', '(GMT+01:00)', 1),
(55, 'Sarajevo', '(GMT+01:00)', 1),
(56, 'Skopje', '(GMT+01:00)', 1),
(57, 'Stockholm', '(GMT+01:00)', 1),
(58, 'Vienna', '(GMT+01:00)', 1),
(59, 'Warsaw', '(GMT+01:00)', 1),
(60, 'West Central Africa', '(GMT+01:00)', 1),
(61, 'Zagreb', '(GMT+01:00)', 1),
(62, 'Athens', '(GMT+02:00)', 2),
(63, 'Bucharest', '(GMT+02:00)', 2),
(64, 'Cairo', '(GMT+02:00)', 2),
(65, 'Harare', '(GMT+02:00)', 2),
(66, 'Helsinki', '(GMT+02:00)', 2),
(67, 'Istanbul', '(GMT+02:00)', 2),
(68, 'Jerusalem', '(GMT+02:00)', 2),
(69, 'Kyev', '(GMT+02:00)', 2),
(70, 'Minsk', '(GMT+02:00)', 2),
(71, 'Pretoria', '(GMT+02:00)', 2),
(72, 'Riga', '(GMT+02:00)', 2),
(73, 'Sofia', '(GMT+02:00)', 2),
(74, 'Tallinn', '(GMT+02:00)', 2),
(75, 'Vilnius', '(GMT+02:00)', 2),
(76, 'Baghdad', '(GMT+03:00)', 3),
(77, 'Kuwait', '(GMT+03:00)', 3),
(78, 'Moscow', '(GMT+03:00)', 3),
(79, 'Nairobi', '(GMT+03:00)', 3),
(80, 'Riyadh', '(GMT+03:00)', 3),
(81, 'St. Petersburg', '(GMT+03:00)', 3),
(82, 'Volgograd', '(GMT+03:00)', 3),
(83, 'Tehran', '(GMT+03:30)', 3),
(84, 'Abu Dhabi', '(GMT+04:00)', 4),
(85, 'Baku', '(GMT+04:00)', 4),
(86, 'Muscat', '(GMT+04:00)', 4),
(87, 'Tbilisi', '(GMT+04:00)', 4),
(88, 'Yerevan', '(GMT+04:00)', 4),
(89, 'Kabul', '(GMT+04:30)', 4),
(90, 'Ekaterinburg', '(GMT+05:00)', 5),
(91, 'Islamabad', '(GMT+05:00)', 5),
(92, 'Karachi', '(GMT+05:00)', 5),
(93, 'Tashkent', '(GMT+05:00)', 5),
(94, 'Chennai', '(GMT+05:30)', 5),
(95, 'Kolkata', '(GMT+05:30)', 5),
(96, 'Mumbai', '(GMT+05:30)', 5),
(97, 'New Delhi', '(GMT+05:30)', 5),
(98, 'Kathmandu', '(GMT+05:45)', 5),
(99, 'Almaty', '(GMT+06:00)', 6),
(100, 'Astana', '(GMT+06:00)', 6),
(101, 'Dhaka', '(GMT+06:00)', 6),
(102, 'Novosibirsk', '(GMT+06:00)', 6),
(103, 'Sri Jayawardenepura', '(GMT+06:00)', 6),
(104, 'Rangoon', '(GMT+06:30)', 6),
(105, 'Bangkok', '(GMT+07:00)', 7),
(106, 'Hanoi', '(GMT+07:00)', 7),
(107, 'Jakarta', '(GMT+07:00)', 7),
(108, 'Krasnoyarsk', '(GMT+07:00)', 7),
(109, 'Beijing', '(GMT+08:00)', 8),
(110, 'Chongqing', '(GMT+08:00)', 8),
(111, 'Hong Kong', '(GMT+08:00)', 8),
(112, 'Irkutsk', '(GMT+08:00)', 8),
(113, 'Kuala Lumpur', '(GMT+08:00)', 8),
(114, 'Perth', '(GMT+08:00)', 8),
(115, 'Singapore', '(GMT+08:00)', 8),
(116, 'Taipei', '(GMT+08:00)', 8),
(117, 'Ulaan Bataar', '(GMT+08:00)', 8),
(118, 'Urumqi', '(GMT+08:00)', 8),
(119, 'Osaka', '(GMT+09:00)', 9),
(120, 'Sapporo', '(GMT+09:00)', 9),
(121, 'Seoul', '(GMT+09:00)', 9),
(122, 'Tokyo', '(GMT+09:00)', 9),
(123, 'Yakutsk', '(GMT+09:00)', 9),
(124, 'Adelaide', '(GMT+09:30)', 9),
(125, 'Darwin', '(GMT+09:30)', 9),
(126, 'Brisbane', '(GMT+10:00)', 10),
(127, 'Canberra', '(GMT+10:00)', 10),
(128, 'Guam', '(GMT+10:00)', 10),
(129, 'Hobart', '(GMT+10:00)', 10),
(130, 'Melbourne', '(GMT+10:00)', 10),
(131, 'Port Moresby', '(GMT+10:00)', 10),
(132, 'Sydney', '(GMT+10:00)', 10),
(133, 'Vladivostok', '(GMT+10:00)', 10),
(134, 'Magadan', '(GMT+11:00)', 11),
(135, 'New Caledonia', '(GMT+11:00)', 11),
(136, 'Solomon Is.', '(GMT+11:00)', 11),
(137, 'Auckland', '(GMT+12:00)', 12),
(138, 'Fiji', '(GMT+12:00)', 12),
(139, 'Kamchatka', '(GMT+12:00)', 12),
(140, 'Marshall Is.', '(GMT+12:00)', 12),
(141, 'Wellington', '(GMT+12:00)', 12),
(142, 'Nuku''alofa', '(GMT+13:00)', 13);