The project I have created in STS is as follows.
In no particular order lets show how we defined this project. Like most other blog entries this is based on the classic DEPT/EMP RDBMS tables. Before we start the maven STS project has the following defined. This will ensure you can use this code without compilation errors as all libraries required are defined with maven dependencies.
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>spring-hibernate-jpa-sqlfire</groupId>
<artifactId>spring-hibernate-jpa-sqlfire</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>spring-hibernate-jpa-sqlfire</name>
<properties>
<spring.version>3.1.2.RELEASE</spring.version>
</properties>
<dependencies>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-entitymanager</artifactId>
<version>4.1.6.Final</version>
</dependency>
<dependency>
<groupId>org.hibernate.javax.persistence</groupId>
<artifactId>hibernate-jpa-2.0-api</artifactId>
<version>1.0.0.Final</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jpa</artifactId>
<version>2.0.8</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.8.2</version>
</dependency>
<dependency>
<groupId>org.springframework.data</groupId>
<artifactId>spring-data-jpa</artifactId>
<version>1.2.0.RELEASE</version>
</dependency>
<dependency>
<groupId>com.vmware</groupId>
<artifactId>sqlf-client</artifactId>
<version>1.0.3</version>
<scope>system</scope>
<systemPath>/Users/papicella/sqlfire/vFabric_SQLFire_103/lib/sqlfireclient.jar</systemPath>
</dependency>
<dependency>
<groupId>com.vmware</groupId>
<artifactId>sqlf-dialect</artifactId>
<version>1.0.3</version>
<scope>system</scope>
<systemPath>/Users/papicella/sqlfire/vFabric_SQLFire_103/lib/sqlfHibernateDialect.jar</systemPath>
</dependency>
</dependencies>
</project>
1. Create a persistence.xml file in META-INF as shown below.persistence.xml
<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"
version="1.0">
<persistence-unit name="application" transaction-type="RESOURCE_LOCAL">
<mapping-file>META-INF/persistence-query.xml</mapping-file>
<class>pas.au.spring.hibernate.sqlfire.model.Dept</class>
<class>pas.au.spring.hibernate.sqlfire.model.Emp</class>
<exclude-unlisted-classes>true</exclude-unlisted-classes>
</persistence-unit>
</persistence>
2. Optionally created named queries in it's own XML. I prefer to do this then add annotations as this gives you the ability to tweak the queries without altering the code.
persistence-query.xml
<?xml version="1.0" encoding="UTF-8" ?>
<entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_1_0.xsd"
version="1.0">
<named-query name="Dept.findByDeptnoNamedQuery">
<query>
from Dept
where deptno = ?1
</query>
</named-query>
</entity-mappings>
3. Create the Dept and Emp domain model classes as shown below. These are referenced in persistence.xml above.
Dept.java
package pas.au.spring.hibernate.sqlfire.model;
import java.util.List;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
@Entity
public class Dept
{
@Id
@GeneratedValue (strategy=GenerationType.IDENTITY)
@Column(name="deptno")
private Long deptno;
@Column(length = 20, unique = true)
private String dname;
private String loc;
@OneToMany(mappedBy = "dept", fetch = FetchType.EAGER)
private List<Emp> empList;
public Dept()
{
}
public Dept(Long deptno, String dname, String loc)
{
super();
this.deptno = deptno;
this.dname = dname;
this.loc = loc;
}
public Long getDeptno() {
return deptno;
}
public void setDeptno(Long deptno) {
this.deptno = deptno;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
public List<Emp> getEmpList() {
return empList;
}
public void setEmpList(List<Emp> empList) {
this.empList = empList;
}
public Emp addEmp(Emp emp) {
getEmpList().add(emp);
emp.setDept(this);
return emp;
}
public Emp removeEmp(Emp emp) {
getEmpList().remove(emp);
emp.setDept(null);
return emp;
}
@Override
public String toString() {
return "Dept [deptno=" + deptno + ", dname=" + dname + ", loc=" + loc
+ "]";
}
}
Emp.java
package pas.au.spring.hibernate.sqlfire.model;
import java.sql.Timestamp;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
@Entity
public class Emp
{
@Id
@GeneratedValue (strategy=GenerationType.IDENTITY)
@Column(name="empno")
private Long empno;
@Column(length = 20)
private String ename;
private Timestamp hiredate;
private String job;
private Long mgr;
private Double sal;
@ManyToOne
@JoinColumn(name = "DEPTNO")
private Dept dept;
public Emp()
{
}
public Emp(Long empno, String ename, String job, Long mgr, Double sal, Timestamp hiredate, Dept dept) {
super();
this.empno = empno;
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.sal = sal;
this.hiredate = hiredate;
this.dept = dept;
}
public Long getEmpno() {
return empno;
}
public void setEmpno(Long empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public Long getMgr() {
return mgr;
}
public void setMgr(Long mgr) {
this.mgr = mgr;
}
public Double getSal() {
return sal;
}
public void setSal(Double sal) {
this.sal = sal;
}
public Dept getDept() {
return dept;
}
public void setDept(Dept dept) {
this.dept = dept;
}
public Timestamp getHiredate() {
return hiredate;
}
public void setHiredate(Timestamp hiredate) {
this.hiredate = hiredate;
}
@Override
public String toString() {
return "Emp [empno=" + empno + ", ename=" + ename + ", hiredate="
+ hiredate + ", job=" + job + ", mgr=" + mgr + ", sal=" + sal
+ ", dept=" + dept + "]";
}
}
4. Create a spring XML file in the META-INF/spring directory named "applicationContext-persistence.xml"applicationContext-persistence.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:tx="http://www.springframework.org/schema/tx"
xmlns:repository="http://www.springframework.org/schema/data/repository"
xmlns:jpa="http://www.springframework.org/schema/data/jpa"
xsi:schemaLocation="http://www.springframework.org/schema/data/repository http://www.springframework.org/schema/data/repository/spring-repository-1.4.xsd
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa-1.2.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">
<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
<property name="jpaDialect">
<bean class="org.springframework.orm.jpa.vendor.HibernateJpaDialect" />
</property>
<property name="jpaVendorAdapter">
<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
</property>
<property name="persistenceUnitName" value="application" />
<property name="persistenceUnitManager">
<bean class="org.springframework.orm.jpa.persistenceunit.DefaultPersistenceUnitManager" />
</property>
<property name="jpaProperties">
<props>
<prop key="hibernate.dialect">com.vmware.sqlfire.hibernate.SQLFireDialect</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.format_sql">true</prop>
<prop key="hibernate.connection.driver_class">com.vmware.sqlfire.jdbc.ClientDriver</prop>
<prop key="hibernate.connection.url">jdbc:sqlfire://localhost:1527</prop>
<prop key="hibernate.connection.username">APP</prop>
<prop key="hibernate.connection.password">APP</prop>
</props>
</property>
<property name="packagesToScan" value="pas.au.spring.hibernate.sqlfire.repository" />
</bean>
<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
<property name="entityManagerFactory" ref="entityManagerFactory" />
</bean>
<tx:annotation-driven/>
<!-- CRUD JPA Repositories Here loaded by default -->
<jpa:repositories base-package="pas.au.spring.hibernate.sqlfire.repository" />
</beans>
5. Create a JPA CRUD repository as shown below. This will automatically be picked up by spring JPA repository component scanner as defined in "applicationContext-persistence.xml" above.
JPADeptRepository.java
package pas.au.spring.hibernate.sqlfire.repository;
import java.util.List;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import pas.au.spring.hibernate.sqlfire.model.Dept;
/*
* Define a repository with all CRUD methods written for you
*/
public interface JPADeptRepository extends CrudRepository<Dept, Long>
{
/*
* Method which will invoke the JPQL required for a find by the attribute name
*/
public Dept findByDeptno (Long deptno);
/*
* Shows how to access a named query using a method name to identify it
* The method name must match the defined query name
*
*/
public Dept findByDeptnoNamedQuery (Long deptno);
/*
* Method that allows us to supply a JPQL query to execute for the method
*/
@Query("SELECT d FROM Dept d WHERE d.dname LIKE '%'")
public List<Dept> findDeptsWithCustomJPQLQuery();
/*
* Method that allows us to supply a JPQL query to execute for the method with a parameter
*/
@Query("SELECT d FROM Dept d WHERE d.loc = ?1")
public Dept findDeptWithCustomJPQLQueryWithParam(String loc);
@Query("SELECT d FROM Dept d WHERE d.deptno = ?1")
public Dept findEmpsInDeptno (Long deptno);
@Query(value = "SELECT * FROM DEPT where deptno = ?1", nativeQuery = true)
public Dept findDeptByNativeQuery(Long deptno);
@Query(value = "SELECT d, e FROM Dept d, Emp e where e.dept = d and d.deptno = ?1")
public List<Object[]> findWithJoin(Long deptno);
}
6. Finally create a test class to verify the repository. It's assumed you have SQLFire running and the DEPT/EMP table already exist. You can use this previous blog entry to get that setup.
http://theblasfrompas.blogspot.com.au/2011/12/sqlfire-in-few-minutes.html
JPAReposDeptTest.java
package pas.au.spring.hibernate.sqlfire.test.repository;
import static org.junit.Assert.*;
import java.util.List;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import pas.au.spring.hibernate.sqlfire.repository.JPADeptRepository;
import pas.au.spring.hibernate.sqlfire.model.Dept;
import pas.au.spring.hibernate.sqlfire.model.Emp;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:META-INF/spring/applicationContext-persistence.xml")
public class JPAReposDeptTest
{
@Autowired
JPADeptRepository deptRespository;
@Test
public void invokeFindAll()
{
List<Dept> deps = (List<Dept>) deptRespository.findAll();
System.out.println("deps size = " + deps.size());
assertNotNull(deps);
}
@Test
public void invokeFindOne()
{
Dept dept = deptRespository.findOne(new Long(20));
System.out.println(dept);
assertNotNull(dept);
assertEquals(new Long(20), dept.getDeptno());
}
@Test
public void invokeFindByDeptno()
{
Dept dept = deptRespository.findByDeptno(new Long(10));
System.out.println(dept);
assertNotNull(dept);
assertEquals(new Long(10), dept.getDeptno());
}
@Test
public void invokeNamedQuery()
{
Dept dept = deptRespository.findByDeptnoNamedQuery(new Long(30));
System.out.println(dept);
assertNotNull(dept);
assertEquals(new Long(30), dept.getDeptno());
}
@Test
public void invokefindDeptsWithCustomJPQLQuery()
{
List<Dept> deps = (List<Dept>) deptRespository.findDeptsWithCustomJPQLQuery();
System.out.println("deps size = " + deps.size());
assertNotNull(deps);
}
@Test
public void invokefindDeptsWithCustomJPQLQueryWithParam()
{
Dept dept = deptRespository.findDeptWithCustomJPQLQueryWithParam("CHICAGO");
System.out.println(dept);
assertNotNull(dept);
assertEquals(new Long(30), dept.getDeptno());
}
@Test
public void invokefindEmpInDeptno()
{
Dept dept = deptRespository.findEmpsInDeptno(new Long(20));
System.out.println(dept);
assertNotNull(dept);
List<Emp> emps = dept.getEmpList();
for (Emp e: emps)
{
System.out.println(e);
}
assertEquals(5, emps.size());
}
@Test
public void invokeFindDeptByNativeQuery()
{
Dept dept = deptRespository.findDeptByNativeQuery(new Long(40));
System.out.println(dept);
assertNotNull(dept);
assertEquals(new Long(40), dept.getDeptno());
}
@Test
public void invokeFindWithJoin()
{
List<Object[]> results = deptRespository.findWithJoin(new Long(10));
System.out.println("Size of results = " + results.size());
assertEquals(3, results.size());
for (Object[] result: results)
{
System.out.println("Dept: " + result[0] + ", Emp: " + result[1]);
}
}
}
7. Run the test class above.
Verify output as shown below.
Dec 19, 2012 9:11:41 PM org.hibernate.engine.transaction.internal.TransactionFactoryInitiator initiateService
INFO: HHH000268: Transaction strategy: org.hibernate.engine.transaction.internal.jdbc.JdbcTransactionFactory
Dec 19, 2012 9:11:41 PM org.hibernate.hql.internal.ast.ASTQueryTranslatorFactory
INFO: HHH000397: Using ASTQueryTranslatorFactory
Hibernate:
select
dept0_.deptno as deptno0_,
dept0_.dname as dname0_,
dept0_.loc as loc0_
from
Dept dept0_
Hibernate:
select
emplist0_.DEPTNO as DEPTNO0_1_,
emplist0_.empno as empno1_,
emplist0_.empno as empno1_0_,
emplist0_.DEPTNO as DEPTNO1_0_,
emplist0_.ename as ename1_0_,
emplist0_.hiredate as hiredate1_0_,
emplist0_.job as job1_0_,
emplist0_.mgr as mgr1_0_,
emplist0_.sal as sal1_0_
from
Emp emplist0_
where
emplist0_.DEPTNO=?
.......
Size of results = 3
Dept: Dept [deptno=10, dname=ACCOUNTING, loc=NEW YORK], Emp: Emp [empno=7934, ename=MILLER, hiredate=1982-01-23 00:00:00.0, job=CLERK, mgr=7782, sal=1300.0, dept=Dept [deptno=10, dname=ACCOUNTING, loc=NEW YORK]]
Dept: Dept [deptno=10, dname=ACCOUNTING, loc=NEW YORK], Emp: Emp [empno=7782, ename=CLARK, hiredate=1981-06-09 00:00:00.0, job=MANAGER, mgr=7839, sal=2450.0, dept=Dept [deptno=10, dname=ACCOUNTING, loc=NEW YORK]]
Dept: Dept [deptno=10, dname=ACCOUNTING, loc=NEW YORK], Emp: Emp [empno=7839, ename=KING, hiredate=1981-11-17 00:00:00.0, job=PRESIDENT, mgr=null, sal=5000.0, dept=Dept [deptno=10, dname=ACCOUNTING, loc=NEW YORK]]
More Information on Spring data JPA and SQLFire Hibernate Dialect can be can be found here.
Spring Data JPA
http://www.springsource.org/spring-data/jpa
vFabric SQLFire Hibernate Dialect
http://communities.vmware.com/docs/DOC-20294
