Spring Hibernate Integration Sybase
Spring with Hibernate is good combination of building enterprise applicaiton where Spring will handle all application level responsibility and Hibernate will interact with the database. In this tutorial I will be using Spring 4.1.5.RELEASE which is latest version and Hibernate 4.3.9.Final which is latest version while writing this tutorial. To keep this tutorial simple we will just make one call to the Sybase data to get data.
Tools needed:
- Sybase ASE(Adaptive Server Enterprise) 15.7
- Eclipse
- JDK 1.8
- Maven 3.2
Create sample table in Sybase database:
create table dbo.Employee ( Emp_ID int identity not null, Emp_name varchar (100) null, Emp_Sal decimal (10, 2) null )
- Insert some sample data for test:
INSERT INTO Employee VALUES ('Java Honk',1000) go INSERT INTO Employee VALUES ('Java Honk2',1200) go INSERT INTO Employee VALUES ('Java Honk4',1100) go INSERT INTO Employee VALUES ('Java Honk5',1300) go INSERT INTO Employee VALUES ('Java Honk6',1400)
- Now execute select statement to see if data inserted or not:
SELECT Emp_ID, Emp_name, Emp_Sal FROM dbo.Employee
- Create maven project name: SpringHibernateIntegration below is final proejct structure:
- 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> </artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging> <name>SpringHibernateIntegration</name> <url>http://maven.apache.org</url> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <org.apache.log4j.version>2.1</org.apache.log4j.version> <org.springframework.version>4.1.5.RELEASE</org.springframework.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> </properties> <dependencies> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-core</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-expression</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-beans</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-aop</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context-support</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-orm</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-web</artifactId> <version>${org.springframework.version}</version> </dependency> <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> <!-- jdbc driver --> <dependency> <groupId>com.sybase.jdbcx</groupId> <artifactId>jconn4</artifactId> <version>7.07</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>
Context files:
- applicaiton-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,com.javahonk.dao" /> <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> <property name="locations"> <list> <value>hibernate.properties</value> <value>application.properties</value> </list> </property> <property name="ignoreUnresolvablePlaceholders" value="true"/> </bean> <import resource="hibernate-context.xml"/> </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"> <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.dao</value> </list> </property> <property name="hibernateProperties"> <props> <prop key="hibernate.dialect">${hibernate.dialect}</prop> <prop key="hibernate.show_sql">${hibernate.show_sql}</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> <bean id="javaHonkDaoHanler" class="com.javahonk.dao.JavaHonkDaoHanler" /> </beans>
Properties files:
- applicaiton.properties:
#Sample properties file where you could define your own properties with key value pair as sample given below default_max_query_size=500
- hibernate.properties and please don’t forget to change this properties file data base configuration with yours:
jdbc.driverClassName=com.sybase.jdbc4.jdbc.SybDriver default_streamed_object_size=5000 default_max_query_size=1000 jdbc.url=jdbc:sybase:Tds:java00v0239.javahonk.com:12000 jdbc.username=root jdbc.password=admin hibernate.dialect=org.hibernate.dialect.SybaseASE157Dialect hibernate.show_sql=false
- hibernate.queries.xml to keep queries:
<?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> </hibernate-mapping>
- JavaHonkDao.java:
package com.javahonk.dao; import java.util.List; public interface JavaHonkDao<T> { List<T> selectRecordFromEmployee(); }
- JavaHonkDaoHanler.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.Propagation; import org.springframework.transaction.annotation.Transactional; @Repository @Transactional public class JavaHonkDaoHanler<T> implements JavaHonkDao<T> { @Value("${default_max_query_size}") private int default_max_query_size; @Autowired private SessionFactory sessionFactory; @SuppressWarnings("unchecked") @Override @Transactional(readOnly = true, propagation=Propagation.NOT_SUPPORTED) public List<T> selectRecordFromEmployee() { Session session = sessionFactory.getCurrentSession(); Integer maxSize = 0; String sqlQuery = "SELECT Emp_ID, Emp_name, Emp_Sal FROM OTC.dbo.Employee"; Query query = session.createSQLQuery(sqlQuery); if (maxSize > 0) { query.setMaxResults(maxSize); } else { query.setMaxResults(default_max_query_size); } List<T> list = query.list(); return list; } }
- Finally write JavaHonkTestApp.java class which will load application context and will call to the database, print its output on console:
package com.javahonk; import java.math.BigDecimal; import java.util.Iterator; import java.util.List; import org.springframework.context.ApplicationContext; import org.springframework.context.ConfigurableApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import com.javahonk.dao.JavaHonkDao; public class JavaHonkTestApp { @SuppressWarnings("unchecked") public static void main(String[] args) { ApplicationContext context = new ClassPathXmlApplicationContext("application-context.xml"); JavaHonkDao<Object> riskTransferDaoHanler = (JavaHonkDao<Object>)context.getBean(JavaHonkDao.class); List<Object> list = riskTransferDaoHanler.selectRecordFromEmployee(); Iterator<Object> value = list.iterator(); while (value.hasNext()) { Object[] values = (Object[]) value.next(); String name = (String) values[1]; BigDecimal sal = (BigDecimal) values[2]; System.out.println("Name: "+name +" Salary: "+sal); } ((ConfigurableApplicationContext) context).close(); } }
- Now run this application you will see below output:
- For more information please visit Spring site here
Download Project: SpringHibernateIntegration