Changing the connection time of the data source URL

Question

I am working on a project that uses spring + hibernate + mysql and c3p0 to pool pools.

Currently, connection pool properties are loaded through properties defined outside of src. (for example: $ {db_uname})

It all starts fine when we create a spring bean.

It may happen that the database we connected to is unavailable for any reason, and we would like to switch hosts.

You must make a callback where you plan to connect to the new host and reinitialize the pool

Any pointers on how to discard the existing data source / connection pool elegantly will be very helpful.

This is what my spring configuration file looks like

<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:context="http://www.springframework.org/schema/context" xmlns:util="http://www.springframework.org/schema/util" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.0.xsd"> <!-- Component scans --> <import resource="component-scans-1.xml" /> <import resource="component-scans-2.xml" /> <util:properties id="serviceManagerProperties" location="classpath:servicemanagers.properties" /> <!-- Properties file --> <context:property-placeholder location="classpath:database.config.properties,classpath:framework.properties" /> <!-- context:annotation-config / --> <tx:annotation-driven transaction-manager="transactionManager" /> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> </bean> <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager"> <property name="entityManagerFactory" ref="entityManagerFactory" /> </bean> <context:mbean-server id="mbeanServer" /> <context:mbean-export server="mbeanServer" default-domain="abc" /> <bean id="cacheManager" factory-method="getInstance" class="net.sf.ehcache.CacheManager" /> <bean class="net.sf.ehcache.management.ManagementService" init-method="init"> <constructor-arg ref="cacheManager" /> <constructor-arg ref="mbeanServer" /> <constructor-arg value="false" /> <constructor-arg value="false" /> <constructor-arg value="false" /> <constructor-arg value="true" /> </bean> <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"> <property name="dataSource" ref="dataSource" /> <property name="jpaVendorAdapter" ref="jpaAdapter" /> <property name="persistenceXmlLocation" value="classpath*:META-INF/framework-persistence.xml" /> <property name="persistenceUnitName" value="PU-NAME" /> <property name="jpaProperties"> <props> <prop key="hibernate.format_sql">true</prop> <prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop> <prop key="hibernate.cache.use_query_cache">${hibernate.cache.use_query_cache}</prop> <prop key="hibernate.cache.use_second_level_cache">${hibernate.cache.use_second_level_cache}</prop> <prop key="hibernate.cache.region.factory_class"> org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory </prop> <prop key="hibernate.ejb.cfgfile">hibernate.cfg.xml</prop> <prop key="hibernate.generate_statistics">true</prop> <!-- CONNECTION SETTINGS --> <prop key="hibernate.connection.driver_class"> com.mysql.jdbc.Driver </prop> <prop key="hibernate.connection.url"> jdbc:mysql://${dbhost}/${dbschema}?zeroDateTimeBehavior=convertToNull&amp;useUnicode=true&amp;characterEncoding=UTF-8 </prop> <prop key="hibernate.connection.username">${dbuser}</prop> <prop key="hibernate.connection.password">${dbpass}</prop> <!-- CONNECTION POOLING --> <prop key="hibernate.connection.provider_class"> org.hibernate.connection.C3P0ConnectionProvider </prop> <prop key="hibernate.c3p0.maxPoolSize">${hibernate.c3p0.maxSize}</prop> <prop key="hibernate.c3p0.minPoolSize">${hibernate.c3p0.minSize}</prop> <prop key="hibernate.c3p0.acquireIncrement">${hibernate.c3p0.acquireIncrement}</prop> <prop key="hibernate.c3p0.idleConnectionTestPeriod">${hibernate.c3p0.idleTestPeriod}</prop> <prop key="hibernate.c3p0.maxStatements">${hibernate.c3p0.maxStatements}</prop> <prop key="hibernate.c3p0.timeout">${hibernate.c3p0.timeout}</prop> </props> </property> </bean> <bean id="jpaAdapter" class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" /> 

Assuming the database schema is correct, let's say I get the database credentials and host information in this case, I need to "re-establish" the connection pool.

Answer 1

AbstractRoutingDataSource is a good choice.

The xml or annotation used as follows:

 <bean id="ds1" class="..c3p0.DataSource"> ... </bean> <bean id="ds2" class="..c3p0.DataSource"> ... </bean> <bean id="dataSource" class="..xxx.RoutingDataSource"> <property name="targetDataSources"> <map key-type="java.lang.String"> <entry key="ds1" value-ref="ds1"/> <entry key="ds2" value-ref="ds2"/> </map> </property> <property name="defaultTargetDataSource" ref="ds1"/> </bean> <bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean"> <property name="dataSource" ref="dataSource" /> ... </bean> 

Then create a class to determine the current datasoruce.

 public class RoutingDataSource extends AbstractRoutingDataSource { private static final ThreadLocal<String> holder = new ThreadLocal<String>(); protected Object determineCurrentLookupKey() { return holder.get(); } public static void clear(){ holder.remove(); } public static void setDataSourceKey(String key){ holder.set(key); } } 

By the way, the expression "try-finally" is boring!

 try{ RoutingDataSource.setDataSourceKey("ds1"); myDao.doXXX(); }finally{ RoutingDataSource.clear(); } 

Answer 2

 <beans:bean id="dataSource" class="org.springframework.aop.framework.ProxyFactoryBean"> <beans:property name="targetSource" ref="swappableDataSource" /> </beans:bean> <beans:bean id="dummyDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close" /> <beans:bean name="swappableDataSource" class="org.springframework.aop.target.HotSwappableTargetSource"> <beans:constructor-arg ref="dummyDataSource" /> </beans:bean> 

and some where in your code you can do this.

 @Autowired HotSwappableTargetSource swapable; public void changeDatasource() throws Exception { swapable.swap(createNewSource(); } ComboPooledDataSource createNewSource() throws Exception { ComboPooledDataSource ds2 = new ComboPooledDataSource(); ds2.setJdbcUrl(url); ds2.setDriverClass(driver); ds2.setUser(username); ds2.setPassword(password); return ds2; } 

Answer 3

If you are requesting multiple database connections .. possibly using sleep mode, creating several hibernate.cfg.file that are incorrect

Following the line, you can achieve this.

 SessionFactory sf = new Configuration().configure("somename.cfg.xml").buildSessionFactory(); 

If your main connection is not established, you will upload another hibernate configuration ... otherwise it will not be possible.