Spring Hibernate Multiple Database Connection
Integration of Spring with Hibernate and configuration with single database is common where you will make only one database connection. But making connection to multiple database is little tricky where you will have to tweak you context. In this demo I will show you how to make multiple database connection. I will use Sybase ASM 15.7 of-course you could configure to make connection to any database. To understand better I will create sample project and how to how to connect two database by the way I will be connecting Sybase database only but different schema.
- Create maven project name: SpringHibernateMultipleDatabase and below is details structure of the project:
Important: If you don’t want to read all documentation and looking for solution immediately then please have a look these two hibernate context file where configuration has been done for two database and how we are creating session factory beans:
- hibernate-context.xml
<?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:context="http://www.springframework.org/schema/context"
xmlns:jdbc="http://www.springframework.org/schema/jdbc"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:util="http://www.springframework.org/schema/util"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd
http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.1.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.1.xsd
http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.1.xsd">
<context:component-scan base-package="com.javahonk.dao, com.javahonk.model" />
<tx:annotation-driven transaction-manager="transactionManager" />
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="${jdbc.driverClassName}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</bean>
<bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="packagesToScan">
<list>
<value>com.javahonk.model</value>
<value>com.javahonk.dao</value>
</list>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">${hibernate.dialect}</prop>
<prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
<prop key="hibernate.c3p0.max_size">${hibernate.c3p0.max_size}</prop>
<prop key="hibernate.c3p0.min_size">${hibernate.c3p0.min_size}</prop>
<prop key="hibernate.c3p0.timeout">${hibernate.c3p0.timeout}</prop>
<prop key="hibernate.c3p0.max_statements">${hibernate.c3p0.max_statements}</prop>
</props>
</property>
<property name="mappingLocations" value="hibernate.queries.xml"></property>
</bean>
<bean id="transactionManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
<property name="sessionFactory" ref="sessionFactory"/>
</bean>
</beans>- hibernate-context-anotherdb.xml:
<?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:context="http://www.springframework.org/schema/context"
xmlns:jdbc="http://www.springframework.org/schema/jdbc"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:util="http://www.springframework.org/schema/util"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd
http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.1.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.1.xsd
http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.1.xsd">
<context:component-scan base-package="com.javahonk.dao, com.javahonk.model" />
<tx:annotation-driven transaction-manager="transactionManagerotherDB" />
<bean id="dataSourceotherDB" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="${jdbc.driverClassName.otherDB}" />
<property name="url" value="${jdbc.url.otherDB}" />
<property name="username" value="${jdbc.username.otherDB}" />
<property name="password" value="${jdbc.password.otherDB}" />
</bean>
<bean id="sessionFactoryotherDB" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<property name="dataSource" ref="dataSourceotherDB" />
<property name="packagesToScan">
<list>
<value>com.javahonk.model</value>
<value>com.javahonk.dao</value>
</list>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">${hibernate.dialect}</prop>
<prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
<prop key="hibernate.jdbc.lob.non_contextual_creation">${hibernate.jdbc.lob.non_contextual_creation}</prop>
</props>
</property>
<property name="mappingLocations" value="hibernate.queries.xml"></property>
</bean>
<bean id="transactionManagerotherDB" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
<property name="sessionFactory" ref="sessionFactoryotherDB"/>
</bean>
</beans>Now let’s go over to the full project file and its structure:
- First create table using below script in Sybase database
create table dbo.EquitySwapTest ( underlierRicCode varchar(20) null, underlierSmID int null, underlierCusip varchar(20) null, underlierAssetType varchar(20) null, div_list_id int null, eventDate int null ) on 'default'
- Insert some dummy data for test:
INSERT INTO OTC.dbo.EquitySwapTest( underlierRicCode ,underlierSmID ,underlierCusip ,underlierAssetType ,div_list_id ,eventDate ) VALUES ( 'underlierRicCode' ,12365 ,'underlierCusip' ,'underlierAssetType' ,1254 ,20151028 )
Note: If you are using any data configure table and data accordingly.
- pom.xml:
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.javahonk</groupId>
<artifactId>SpringHibernateMultipleDatabase</artifactId>
<version>0.0.1-SNAPSHOT</version>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
<apache.commons.lang.version>3.3.2</apache.commons.lang.version>
<commons.collections.version>3.2.1</commons.collections.version>
<commons.logging.version>1.2</commons.logging.version>
<org.apache.log4j.version>2.1</org.apache.log4j.version>
<objenesis.version>2.1</objenesis.version>
<cglib.version>3.2.0-SS</cglib.version>
<dom4j.version>1.6.1</dom4j.version>
<xercesImpl.version>unknown</xercesImpl.version>
<org.springframework.version>4.1.5.RELEASE</org.springframework.version>
<org.ow2.asm.version>5.0.3</org.ow2.asm.version>
<org.javassist.version>3.18.1-GA</org.javassist.version>
<org.jboss.logging.version>3.1.3.GA</org.jboss.logging.version>
<org.apache.geronimo.version>1.1.1</org.apache.geronimo.version>
<org.hibernate.core.version>4.3.9.Final</org.hibernate.core.version>
<org.hibernate.entitymanager.version>4.3.9.Final</org.hibernate.entitymanager.version>
<org.hibernate.annotations.version>4.0.5.Final</org.hibernate.annotations.version>
<org.hibernate.jpa.version>1.0.0.Final</org.hibernate.jpa.version>
<junit.version>4.12</junit.version>
</properties>
<dependencies>
<!-- JUNIT -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>${junit.version}</version>
</dependency>
<!-- Spring -->
<!-- Core utilities used by other modules. Define this if you use Spring
Utility APIs (org.springframework.core.*/org.springframework.util.*) -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${org.springframework.version}</version>
</dependency>
<!-- Expression Language (depends on spring-core) Define this if you use
Spring Expression APIs (org.springframework.expression.*) -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-expression</artifactId>
<version>${org.springframework.version}</version>
</dependency>
<!-- Bean Factory and JavaBeans utilities (depends on spring-core) Define
this if you use Spring Bean APIs (org.springframework.beans.*) -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>${org.springframework.version}</version>
</dependency>
<!-- Aspect Oriented Programming (AOP) Framework (depends on spring-core,
spring-beans) Define this if you use Spring AOP APIs (org.springframework.aop.*) -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>${org.springframework.version}</version>
</dependency>
<!-- Application Context (depends on spring-core, spring-expression, spring-aop,
spring-beans) This is the central artifact for Spring's Dependency Injection
Container and is generally always defined -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${org.springframework.version}</version>
</dependency>
<!-- Various Application Context utilities, including EhCache, JavaMail,
Quartz, and Freemarker integration Define this if you need any of these integrations -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
<version>${org.springframework.version}</version>
</dependency>
<!-- JDBC Data Access Library (depends on spring-core, spring-beans, spring-context,
spring-tx) Define this if you use Spring's JdbcTemplate API (org.springframework.jdbc.*) -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${org.springframework.version}</version>
</dependency>
<!-- Object-to-Relation-Mapping (ORM) integration with Hibernate, JPA,
and iBatis. (depends on spring-core, spring-beans, spring-context, spring-tx)
Define this if you need ORM (org.springframework.orm.*) -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
<version>${org.springframework.version}</version>
</dependency>
<!-- Web application development utilities applicable to both Servlet and
Portlet Environments (depends on spring-core, spring-beans, spring-context)
Define this if you use Spring MVC, or wish to use Struts, JSF, or another
web framework with Spring (org.springframework.web.*) -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-web</artifactId>
<version>${org.springframework.version}</version>
</dependency>
<!-- Support for testing Spring applications with tools such as JUnit and
TestNG This artifact is generally always defined with a 'test' scope for
the integration testing framework and unit testing stubs -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${org.springframework.version}</version>
<scope>test</scope>
</dependency>
<!-- Spring Aspects -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
<version>${org.springframework.version}</version>
</dependency>
<!-- Spring Tx -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${org.springframework.version}</version>
</dependency>
<!-- dom4j -->
<dependency>
<groupId>dom4j</groupId>
<artifactId>dom4j</artifactId>
<version>${dom4j.version}</version>
</dependency>
<!-- xerces -->
<dependency>
<groupId>xerces</groupId>
<artifactId>xercesImpl</artifactId>
<version>${xercesImpl.version}</version>
</dependency>
<dependency>
<groupId>com.tunnelvisionlabs</groupId>
<artifactId>antlr4</artifactId>
<version>4.4</version>
<classifier>complete</classifier>
</dependency>
<!-- objenesis -->
<dependency>
<groupId>org.objenesis</groupId>
<artifactId>objenesis</artifactId>
<version>${objenesis.version}</version>
</dependency>
<!-- apache commons -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>${apache.commons.lang.version}</version>
</dependency>
<!-- commons-io -->
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
<!-- commons collections -->
<dependency>
<groupId>commons-collections</groupId>
<artifactId>commons-collections</artifactId>
<version>${commons.collections.version}</version>
</dependency>
<!-- commons logging -->
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>${commons.logging.version}</version>
</dependency>
<!-- cglib -->
<dependency>
<groupId>cglib</groupId>
<artifactId>cglib</artifactId>
<!--<version>3.1</version> -->
<version>${cglib.version}</version>
</dependency>
<!-- jdbc driver -->
<dependency>
<groupId>com.sybase.jdbcx</groupId>
<artifactId>jconn4</artifactId>
<version>7.07</version>
</dependency>
<!-- aop alliance -->
<dependency>
<groupId>aopalliance</groupId>
<artifactId>aopalliance</artifactId>
<version>1.0</version>
</dependency>
<!-- asm -->
<dependency>
<groupId>org.ow2.asm</groupId>
<artifactId>asm</artifactId>
<version>${org.ow2.asm.version}</version>
</dependency>
<!-- hibernate -->
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>${org.hibernate.core.version}</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-entitymanager</artifactId>
<version>${org.hibernate.entitymanager.version}</version>
</dependency>
<dependency>
<groupId>org.hibernate.common</groupId>
<artifactId>hibernate-commons-annotations</artifactId>
<version>${org.hibernate.annotations.version}</version>
</dependency>
<dependency>
<groupId>org.hibernate.javax.persistence</groupId>
<artifactId>hibernate-jpa-2.1-api</artifactId>
<version>${org.hibernate.jpa.version}</version>
</dependency>
<!-- Log4j -->
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-api</artifactId>
<version>${org.apache.log4j.version}</version>
</dependency>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-core</artifactId>
<version>${org.apache.log4j.version}</version>
</dependency>
</dependencies>
</project>- application.properties:
query_getEquitySwapTestData=getEquitySwapTestData #Other DB Query query_getInterestRates=getInterestRates query_getIntereestRateBasedOnRicAndDate=getIntereestRateBasedOnRicAndDate
- hibernate.properties
default_streamed_object_size=5000 default_max_query_size=1000 #Database 1 jdbc.driverClassName=com.sybase.jdbc4.jdbc.SybDriver jdbc.url=jdbc:sybase:Tds:javahonk.com:12000 jdbc.username=javahonk jdbc.password=javahonk123 #Database 2 jdbc.driverClassName.otherDB=com.sybase.jdbc4.jdbc.SybDriver jdbc.url.otherDB=jdbc:sybase:Tds:javahonk.com:12000 jdbc.username.otherDB=javahonk jdbc.password.otherDB=javahonk123 hibernate.dialect=org.hibernate.dialect.SybaseASE157Dialect hibernate.show_sql=false hibernate.jdbc.lob.non_contextual_creation=true c3p0.acquireIncrement=2 c3p0.maxPoolSize=10 c3p0.minPoolSize=1 c3p0.maxIdleTime=1800
- hibernate.queries.xml:
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd" > <hibernate-mapping> <query name="getEquitySwapTestData"> <![CDATA[ from EquitySwapTestModel esm where esm.eventDate = :eventDate ]]> </query> <!-- Other DB query --> <query name="getInterestRates"> <![CDATA[ from EquitySwapInterestRateModel esm ]]> </query> <query name="getIntereestRateBasedOnRicAndDate"> <![CDATA[ from EquitySwapInterestRateModel esm where esm.ric_code = :ric_code AND esm.libor_rate_date = :libor_rate_date ]]> </query> </hibernate-mapping>
- spring-context.xml:
<?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:context="http://www.springframework.org/schema/context" xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:util="http://www.springframework.org/schema/util" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.1.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.1.xsd http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.1.xsd"> <context:annotation-config /> <context:component-scan base-package="com.javahonk.dao, com.javahonk.model" /> <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> <property name="locations"> <list> <value>classpath:hibernate/hibernate.properties</value> <value>application.properties</value> </list> </property> <property name="ignoreUnresolvablePlaceholders" value="true"/> <property name="ignoreResourceNotFound" value="true"/> </bean> <import resource="hibernate-context.xml"/> <import resource="hibernate-context-anotherdb.xml"/> </beans>
- EquitySwapInterestRateModel.java:
package com.javahonk.model;
import java.time.LocalDate;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import org.hibernate.annotations.Type;
@Entity
@Table(name="sample..InterestTable")
public class EquitySwapInterestRateModel {
@Id
@Column(name="symbol")
private String symbol;
@Column(name="ric_code")
private String ric_code;
@Column(name="name")
private String name;
@Column(name="libor_rate")
private float libor_rate;
@Column(name="libor_rate_date")
private int libor_rate_date;
@Column(name="extracting_date")
@Type(type = "com.javahonk.model.LocalDateHibernateUserType")
private LocalDate extracting_date;
public EquitySwapInterestRateModel() {
}
public EquitySwapInterestRateModel(String symbol, String ric_code,
String name, float libor_rate, int libor_rate_date,
LocalDate extracting_date) {
super();
this.symbol = symbol;
this.ric_code = ric_code;
this.name = name;
this.libor_rate = libor_rate;
this.libor_rate_date = libor_rate_date;
this.extracting_date = extracting_date;
}
public String getSymbol() {
return symbol;
}
public void setSymbol(String symbol) {
this.symbol = symbol;
}
public String getRic_code() {
return ric_code;
}
public void setRic_code(String ric_code) {
this.ric_code = ric_code;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public float getLibor_rate() {
return libor_rate;
}
public void setLibor_rate(float libor_rate) {
this.libor_rate = libor_rate;
}
public int getLibor_rate_date() {
return libor_rate_date;
}
public void setLibor_rate_date(int libor_rate_date) {
this.libor_rate_date = libor_rate_date;
}
public LocalDate getExtracting_date() {
return extracting_date;
}
public void setExtracting_date(LocalDate extracting_date) {
this.extracting_date = extracting_date;
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result
+ ((extracting_date == null) ? 0 : extracting_date.hashCode());
result = prime * result + Float.floatToIntBits(libor_rate);
result = prime * result + libor_rate_date;
result = prime * result + ((name == null) ? 0 : name.hashCode());
result = prime * result
+ ((ric_code == null) ? 0 : ric_code.hashCode());
result = prime * result + ((symbol == null) ? 0 : symbol.hashCode());
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
EquitySwapInterestRateModel other = (EquitySwapInterestRateModel) obj;
if (extracting_date == null) {
if (other.extracting_date != null)
return false;
} else if (!extracting_date.equals(other.extracting_date))
return false;
if (Float.floatToIntBits(libor_rate) != Float
.floatToIntBits(other.libor_rate))
return false;
if (libor_rate_date != other.libor_rate_date)
return false;
if (name == null) {
if (other.name != null)
return false;
} else if (!name.equals(other.name))
return false;
if (ric_code == null) {
if (other.ric_code != null)
return false;
} else if (!ric_code.equals(other.ric_code))
return false;
if (symbol == null) {
if (other.symbol != null)
return false;
} else if (!symbol.equals(other.symbol))
return false;
return true;
}
@Override
public String toString() {
return "EquitySwapInterestRateModel [symbol=" + symbol + ", ric_code="
+ ric_code + ", name=" + name + ", libor_rate=" + libor_rate
+ ", libor_rate_date=" + libor_rate_date + ", extracting_date="
+ extracting_date + "]";
}
}
- EquitySwapTestModel.java:
package com.javahonk.model;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name="Sample..EquitySwapTest")
public class EquitySwapTestModel {
@Id
@Column(name="underlierRicCode")
private String underlierRicCode;
@Column(name="underlierSmID")
private Integer underlierSmID;
@Column(name="underlierCusip")
private String underlierCusip;
@Column(name="underlierAssetType")
private String underlierAssetType;
@Column(name="div_list_id")
private Integer div_list_id;
@Column(name="eventDate")
private Integer eventDate;
public String getUnderlierRicCode() {
return underlierRicCode;
}
public void setUnderlierRicCode(String underlierRicCode) {
this.underlierRicCode = underlierRicCode;
}
public Integer getUnderlierSmID() {
return underlierSmID;
}
public void setUnderlierSmID(Integer underlierSmID) {
this.underlierSmID = underlierSmID;
}
public String getUnderlierCusip() {
return underlierCusip;
}
public void setUnderlierCusip(String underlierCusip) {
this.underlierCusip = underlierCusip;
}
public String getUnderlierAssetType() {
return underlierAssetType;
}
public void setUnderlierAssetType(String underlierAssetType) {
this.underlierAssetType = underlierAssetType;
}
public Integer getDiv_list_id() {
return div_list_id;
}
public void setDiv_list_id(Integer div_list_id) {
this.div_list_id = div_list_id;
}
public Integer getEventDate() {
return eventDate;
}
public void setEventDate(Integer eventDate) {
this.eventDate = eventDate;
}
@Override
public String toString() {
return "EquitySwapTestModel [underlierRicCode=" + underlierRicCode
+ ", underlierSmID=" + underlierSmID + ", underlierCusip="
+ underlierCusip + ", underlierAssetType=" + underlierAssetType
+ ", div_list_id=" + div_list_id + ", eventDate=" + eventDate
+ "]";
}
}
- LocalDateHibernateUserType.java:
package com.javahonk.model;
import java.io.Serializable;
import java.util.Date;
import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.type.StandardBasicTypes;
import org.hibernate.usertype.EnhancedUserType;
public class LocalDateHibernateUserType implements EnhancedUserType, Serializable{
private static final long serialVersionUID = -7500395414633111738L;
private static final int[] sql_types = new int[]{Types.TIMESTAMP};
@Override
public Object assemble(Serializable arg0, Object arg1)
throws HibernateException {
return arg0;
}
@Override
public Object deepCopy(Object arg0) throws HibernateException {
return arg0;
}
@Override
public Serializable disassemble(Object arg0) throws HibernateException {
return (Serializable) arg0;
}
@Override
public boolean equals(Object obj1, Object obj2) throws HibernateException {
if(obj1 == obj2)
return true;
if(obj1==null || obj2 == null)
return false;
LocalDate ldt1 = (LocalDate)obj1;
LocalDate ldt2 = (LocalDate)obj2;
return ldt1.equals(ldt2);
}
@Override
public int hashCode(Object arg0) throws HibernateException {
return arg0.hashCode();
}
@Override
public boolean isMutable() {
return false;
}
@Override
public Object nullSafeGet(ResultSet rs, String[] params,
SessionImplementor session, Object owner) throws HibernateException,
SQLException {
Object timestamp = StandardBasicTypes.DATE.nullSafeGet(rs, params, session, owner);
if(timestamp == null){
return null;
}
Date date = (Date) timestamp;
Instant instant = Instant.ofEpochMilli(date.getTime());
return LocalDateTime.ofInstant(instant, ZoneId.systemDefault()).toLocalDate();
}
@Override
public void nullSafeSet(PreparedStatement ps, Object value, int index,
SessionImplementor session) throws HibernateException, SQLException {
if(value==null){
StandardBasicTypes.DATE.nullSafeSet(ps, null, index, session);
}
else{
LocalDate ldt = (LocalDate) value;
Instant instant = ldt.atStartOfDay().atZone(ZoneId.systemDefault()).toInstant();
Date date = Date.from(instant);
StandardBasicTypes.DATE.nullSafeSet(ps, date, index, session);
}
}
@Override
public Object replace(Object original, Object target, Object owner)
throws HibernateException {
return original;
}
@SuppressWarnings("rawtypes")
@Override
public Class returnedClass() {
return LocalDate.class;
}
@Override
public int[] sqlTypes() {
return sql_types;
}
@Override
public Object fromXMLString(String arg0) {
return LocalDate.parse(arg0);
}
@Override
public String objectToSQLString(Object arg0) {
throw new UnsupportedOperationException();
}
@Override
public String toXMLString(Object arg0) {
return arg0.toString();
}
}
- LocalDateTimeHibernateUserType.java:
package com.javahonk.model;
import java.io.Serializable;
import java.util.Date;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.type.StandardBasicTypes;
import org.hibernate.usertype.EnhancedUserType;
public class LocalDateTimeHibernateUserType implements EnhancedUserType, Serializable{
private static final long serialVersionUID = 2769071855811042550L;
private static final int[] sql_types = new int[]{Types.TIMESTAMP};
@Override
public Object assemble(Serializable arg0, Object arg1)
throws HibernateException {
return arg0;
}
@Override
public Object deepCopy(Object arg0) throws HibernateException {
return arg0;
}
@Override
public Serializable disassemble(Object arg0) throws HibernateException {
return (Serializable) arg0;
}
@Override
public boolean equals(Object obj1, Object obj2) throws HibernateException {
if(obj1 == obj2)
return true;
if(obj1==null || obj2 == null)
return false;
LocalDateTime ldt1 = (LocalDateTime)obj1;
LocalDateTime ldt2 = (LocalDateTime)obj2;
return ldt1.equals(ldt2);
}
@Override
public int hashCode(Object arg0) throws HibernateException {
return arg0.hashCode();
}
@Override
public boolean isMutable() {
return false;
}
@Override
public Object nullSafeGet(ResultSet rs, String[] params,
SessionImplementor session, Object owner) throws HibernateException,
SQLException {
Object timestamp = StandardBasicTypes.TIMESTAMP.nullSafeGet(rs, params, session, owner);
if(timestamp == null){
return null;
}
Date ts = (Date) timestamp;
Instant instant = Instant.ofEpochMilli(ts.getTime());
return LocalDateTime.ofInstant(instant, ZoneId.systemDefault());
}
@Override
public void nullSafeSet(PreparedStatement ps, Object value, int index,
SessionImplementor session) throws HibernateException, SQLException {
if(value==null){
StandardBasicTypes.TIMESTAMP.nullSafeSet(ps, null, index, session);
}
else{
LocalDateTime ldt = (LocalDateTime) value;
Instant instant = ldt.atZone(ZoneId.systemDefault()).toInstant();
Date timestamp = Date.from(instant);
StandardBasicTypes.TIMESTAMP.nullSafeSet(ps, timestamp, index, session);
}
}
@Override
public Object replace(Object original, Object target, Object owner)
throws HibernateException {
return original;
}
@SuppressWarnings("rawtypes")
@Override
public Class returnedClass() {
return LocalDateTime.class;
}
@Override
public int[] sqlTypes() {
return sql_types;
}
@Override
public Object fromXMLString(String arg0) {
return LocalDateTime.parse(arg0);
}
@Override
public String objectToSQLString(Object arg0) {
throw new UnsupportedOperationException();
}
@Override
public String toXMLString(Object arg0) {
return arg0.toString();
}
}
- GenericDao.java:
package com.javahonk.dao;
import java.util.List;
public interface GenericDao<T> {
public List<T> getEquitySwapByNativeSelectQuery(int eventDate);
public List<T> getEquitySwapByHibernateORM(int eventDate);
}
- GenericDaoHandler.java:
package com.javahonk.dao;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import com.javahonk.model.EquitySwapTestModel;
@Repository
@Transactional
public class GenericDaoHandler<T> implements GenericDao<T>{
@Autowired
private SessionFactory sessionFactory;
@Value("${query_getEquitySwapTestData}")
private String query_getEquitySwapTestData;
@SuppressWarnings("unchecked")
@Override
public List<T> getEquitySwapByNativeSelectQuery(int eventDate) {
Session session = sessionFactory.getCurrentSession();
Query query = session.createSQLQuery("SELECT * FROM OTC.dbo.EquitySwapTest where eventDate = :eventDate").addEntity(EquitySwapTestModel.class);
query.setParameter("eventDate", eventDate);
List<T> dividendPayDate = query.list();
return dividendPayDate;
}
@SuppressWarnings("unchecked")
@Override
public List<T> getEquitySwapByHibernateORM(int eventDate) {
Session session = sessionFactory.getCurrentSession();
Query query = session.getNamedQuery(query_getEquitySwapTestData);
query.setParameter("eventDate", eventDate);
List<T> dividendPayDate = query.list();
return dividendPayDate;
}
}
- InterestRateDao.java:
package com.javahonk.dao;
import java.time.LocalDate;
import java.util.List;
public interface InterestRateDao<T> {
public List<T> getInterestRates();
public List<T> getInterestRateBasedOnRicAndDate(String ricCode, LocalDate localDate);
}
- InterestRateDaoHandler.java:
package com.javahonk.dao;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
@Repository
@Transactional
public class InterestRateDaoHandler<T> implements InterestRateDao<T> {
@Autowired
private SessionFactory sessionFactoryotherDB;
@Value("${query_getInterestRates}")
private String query_getInterestRates;
@Value("${query_getIntereestRateBasedOnRicAndDate}")
private String query_getIntereestRateBasedOnRicAndDate;
@SuppressWarnings("unchecked")
@Override
public List<T> getInterestRates() {
Session session = sessionFactoryotherDB.getCurrentSession();
Query query = session.getNamedQuery(query_getInterestRates);
List<T> equitySwapInterestRateList = query.list();
return equitySwapInterestRateList;
}
@SuppressWarnings("unchecked")
@Override
public List<T> getInterestRateBasedOnRicAndDate(String ricCode, LocalDate localDate) {
Session session = sessionFactoryotherDB.getCurrentSession();
Query query = session.getNamedQuery(query_getIntereestRateBasedOnRicAndDate);
query.setParameter("ric_code", ricCode);
query.setParameter("libor_rate_date", Integer.valueOf(localDate.format(DateTimeFormatter.BASIC_ISO_DATE)));
List<T> equitySwapInterestRateList = query.list();
return equitySwapInterestRateList;
}
}
- log4j2.xml:
<?xml version="1.0" encoding="UTF-8"?>
<Configuration status="INFO" shutdownHook="disable">
<Properties>
<Property name="envrionment.target">DEV</Property>
</Properties>
<Properties>
<Property name="logging.dir">./</Property>
</Properties>
<Appenders>
<Console name="Console" target="SYSTEM_OUT">
<PatternLayout pattern="%d{HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n" />
</Console>
<RollingFile name="RollingFile"
fileName="./logs/rolling-file.log" filePattern="${sys:logging.dir}/logs/rolling-file-%d{yyyy-MM-dd}-%i.log">
<PatternLayout pattern="%d{HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n" />
<!-- TODO:Change to time based policy -->
<Policies>
<TimeBasedTriggeringPolicy interval="1" modulate="true" />
<SizeBasedTriggeringPolicy size="100 MB" />
</Policies>
<DefaultRolloverStrategy max="4" />
</RollingFile>
</Appenders>
<Loggers>
<Root level="info">
<AppenderRef ref="Console" />
<!-- <AppenderRef ref="file" /> -->
<AppenderRef ref="RollingFile" />
</Root>
</Loggers>
</Configuration>
- SpringHibernateMultipleTestApp.java: This is our main test class and I will be making call to both the database through DAO:
package com.javahonk;
import java.time.LocalDate;
import java.util.List;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.javahonk.dao.GenericDao;
import com.javahonk.dao.InterestRateDao;
import com.javahonk.model.EquitySwapInterestRateModel;
import com.javahonk.model.EquitySwapTestModel;
public class SpringHibernateMultipleTestApp {
private static final Logger logger = LogManager.getLogger(SpringHibernateMultipleTestApp.class.getName());
@SuppressWarnings({ "unchecked" })
public static void main(String[] args) {
logger.info("Starting HibernateTestApp...");
ApplicationContext context = new ClassPathXmlApplicationContext("spring-context.xml");
GenericDao<?> genericDao = context.getBean(GenericDao.class);
InterestRateDao<?> interestRateDao = context.getBean(InterestRateDao.class);
List<EquitySwapTestModel> dataList2 = (List<EquitySwapTestModel>) genericDao
.getEquitySwapByNativeSelectQuery(20151028);
logger.info("Got the data: {}", dataList2);
dataList2 = (List<EquitySwapTestModel>) genericDao
.getEquitySwapByHibernateORM(20151028);
logger.info("Got the data: {}", dataList2);
//Other DB call
List<EquitySwapInterestRateModel> interestList = (List<EquitySwapInterestRateModel>) interestRateDao
.getInterestRates();
logger.info("Got the data: {}", interestList);
interestList = (List<EquitySwapInterestRateModel>) interestRateDao
.getInterestRateBasedOnRicAndDate("EUR1YFSR=", LocalDate.now());
logger.info("Got the data: {}", interestList);
((AbstractApplicationContext) context).close();
}
}
- That’s it. To test this application one all your configuration is done then right click SpringHibernateMultipleTestApp.java Run As –> Java Application you should see output based on your database and table configuration. As I did tested it on Sysbase below was my output on console:
11:41:37.726 [main] INFO com.javahonk.SpringHibernateMultipleTestApp - Got the data: [EquitySwapTestModel [underlierRicCode=underlierRicCode, underlierSmID=12365, underlierCusip=underlierCusip, underlierAssetType=underlierAssetType, div_list_id=1254, eventDate=20151028], EquitySwapTestModel [underlierRicCode=underlierRicCode, underlierSmID=12365, underlierCusip=underlierCusip, underlierAssetType=underlierAssetType, div_list_id=1254, eventDate=20151028], EquitySwapTestModel [underlierRicCode=underlierRicCode, underlierSmID=12365, underlierCusip=underlierCusip, underlierAssetType=underlierAssetType, div_list_id=1254, eventDate=20151028], EquitySwapTestModel [underlierRicCode=underlierRicCode, underlierSmID=12365, underlierCusip=underlierCusip, underlierAssetType=underlierAssetType, div_list_id=1254, eventDate=20151028], EquitySwapTestModel [underlierRicCode=underlierRicCode, underlierSmID=12365, underlierCusip=underlierCusip, underlierAssetType=underlierAssetType, div_list_id=1254, eventDate=20151028], EquitySwapTestModel [underlierRicCode=underlierRicCode, underlierSmID=12365, underlierCusip=underlierCusip, underlierAssetType=underlierAssetType, div_list_id=1254, eventDate=20151028], EquitySwapTestModel [underlierRicCode=underlierRicCode, underlierSmID=12365, underlierCusip=underlierCusip, underlierAssetType=underlierAssetType, div_list_id=1254, eventDate=20151028], EquitySwapTestModel [underlierRicCode=underlierRicCode, underlierSmID=12365, underlierCusip=underlierCusip, underlierAssetType=underlierAssetType, div_list_id=1254, eventDate=20151028]]
- For more information please visit Spring documentation here
Download Project: SpringHibernateMultipleDatabase
