Spring Data with JPA and @NamedQueries

If you use Spring Data and @NamedQuery annotations at your JPA entity you can easily use them in a more convenient way using the spring data repository.

On a previous blog we created a spring data project using spring boot and docker. We will use the pretty same project and enhance our repository’s functionality.

We will implement a named query that will fetch employees only if their Last Name has as many characters as the ones specified.

package com.gkatzioura.springdata.jpa.persistence.entity;

import javax.persistence.*;

/**
 * Created by gkatzioura on 6/2/16.
 */
@Entity
@Table(name = "employee", schema="spring_data_jpa_example")
@NamedQuery(name = "Employee.fetchByLastNameLength",
        query = "SELECT e FROM Employee e WHERE CHAR_LENGTH(e.lastname) =:length "
)
public class Employee {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id;

    @Column(name = "firstname")
    private String firstName;

    @Column(name = "lastname")
    private String lastname;

    @Column(name = "email")
    private String email;

    @Column(name = "age")
    private Integer age;

    @Column(name = "salary")
    private Integer salary;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastname() {
        return lastname;
    }

    public void setLastname(String lastname) {
        this.lastname = lastname;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Integer getSalary() {
        return salary;
    }

    public void setSalary(Integer salary) {
        this.salary = salary;
    }
}

Pay extra attention to the query name and the convention we follow @{EntityName}.{queryName}.
Then we will add the method to our spring data repository.

package com.gkatzioura.springdata.jpa.persistence.repository;

import com.gkatzioura.springdata.jpa.persistence.entity.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created by gkatzioura on 6/2/16.
 */
@Repository
public interface EmployeeRepository extends JpaRepository<Employee,Long>, EmployeeRepositoryCustom {

    List<Employee> fetchByLastNameLength(@Param("length") Long length);
}

And last but not least add some functionality to our controller.

package com.gkatzioura.springdata.jpa.controller;

import com.gkatzioura.springdata.jpa.persistence.entity.Employee;
import com.gkatzioura.springdata.jpa.persistence.repository.EmployeeRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * Created by gkatzioura on 6/2/16.
 */
@RestController
public class TestController {

    @Autowired
    private EmployeeRepository employeeRepository;

    @RequestMapping("/employee")
    public List<Employee> getTest() {

        return employeeRepository.findAll();
    }

    @RequestMapping("/employee/filter")
    public List<Employee> getFiltered(String firstName,@RequestParam(defaultValue = "0") Double bonusAmount) {

        return employeeRepository.getFirstNamesLikeAndBonusBigger(firstName,bonusAmount);
    }

    @RequestMapping("/employee/lastnameLength")
    public List<Employee> fetchByLength(Long length) {
        return employeeRepository.fetchByLastNameLength(length);
    }

}

You can find the source code on github.

Hibernate Caching with HazelCast: Basic configuration

Previously we went through an introduction on JPA caching, the mechanisms and what hibernate offers.

What comes next is a hibernate project using Hazelcast as a second level cache.

We will use a basic spring boot project for this purpose with JPA. Spring boot uses hibernate as the default JPA provider.
Our setup will be pretty close to the one of a previous post.
We will use postgresql with docker for our sql database.

group 'com.gkatzioura'
version '1.0-SNAPSHOT'

buildscript {
    repositories {
        mavenCentral()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:1.5.1.RELEASE")
    }
}

apply plugin: 'java'
apply plugin: 'idea'
apply plugin: 'org.springframework.boot'


repositories {
    mavenCentral()
}

dependencies {
    compile("org.springframework.boot:spring-boot-starter-web")
    compile group: 'org.springframework.boot', name: 'spring-boot-starter-data-jpa'
    compile group: 'org.postgresql', name:'postgresql', version:'9.4-1206-jdbc42'
    compile group: 'org.springframework', name: 'spring-jdbc'
    compile group: 'com.zaxxer', name: 'HikariCP', version: '2.6.0'
    compile group: 'com.hazelcast', name: 'hazelcast-hibernate5', version: '1.2'
    compile group: 'com.hazelcast', name: 'hazelcast', version: '3.7.5'
    testCompile group: 'junit', name: 'junit', version: '4.11'
}

By examining the dependencies carefully we see the hikari pool, the postgresql driver, spring data jpa and of course hazelcast.

Instead of creating the database manually we will automate it by utilizing the database initialization feature of Spring boot.

We shall create a file called schema.sql under the resources folder.

create schema spring_data_jpa_example;
 
create table spring_data_jpa_example.employee(
    id  SERIAL PRIMARY KEY,
    firstname   TEXT    NOT NULL,
    lastname    TEXT    NOT NULL,   
    email       TEXT    not null,
    age         INT     NOT NULL,
    salary         real,
    unique(email)
);
 
insert into spring_data_jpa_example.employee (firstname,lastname,email,age,salary) 
values ('Test','Me','test@me.com',18,3000.23);

To keep it simple and avoid any further configurations we shall put the configurations for datasource, jpa and caching inside the application.yml file.

spring:
  datasource:
    continue-on-error: true
    type: com.zaxxer.hikari.HikariDataSource
    url: jdbc:postgresql://172.17.0.2:5432/postgres
    driver-class-name: org.postgresql.Driver
    username: postgres
    password: postgres
    hikari:
      idle-timeout: 10000
  jpa:
    properties:
      hibernate:
        cache:
          use_second_level_cache: true
          use_query_cache: true
          region:
            factory_class: com.hazelcast.hibernate.HazelcastCacheRegionFactory
    show-sql: true

The configuration spring.datasource.continue-on-error is crucial since once the application relaunches, there should be a second attempt to create the database and thus a crash is inevitable.

Any hibernate specific properties reside at the spring.jpa.properties path. We enabled the second level cache and the query cache.

Also we set show-sql to true. This means that once a query hits the database it shall be logged through the console.

Then create our employee entity.

package com.gkatzioura.hibernate.enitites;

import javax.persistence.*;

/**
 * Created by gkatzioura on 2/6/17.
 */
@Entity
@Table(name = "employee", schema="spring_data_jpa_example")
public class Employee {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id;

    @Column(name = "firstname")
    private String firstName;

    @Column(name = "lastname")
    private String lastname;

    @Column(name = "email")
    private String email;

    @Column(name = "age")
    private Integer age;

    @Column(name = "salary")
    private Integer salary;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastname() {
        return lastname;
    }

    public void setLastname(String lastname) {
        this.lastname = lastname;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Integer getSalary() {
        return salary;
    }

    public void setSalary(Integer salary) {
        this.salary = salary;
    }
}

Everything is setup. Spring boot will detect the entity and create an EntityManagerFactory on its own.
What comes next is the repository class for employee.

package com.gkatzioura.hibernate.repository;

import com.gkatzioura.hibernate.enitites.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.repository.CrudRepository;

/**
 * Created by gkatzioura on 2/11/17.
 */
public interface EmployeeRepository extends JpaRepository<Employee,Long> {
}

And the last one is the controller

package com.gkatzioura.hibernate.controller;

import com.gkatzioura.hibernate.enitites.Employee;
import com.gkatzioura.hibernate.repository.EmployeeRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * Created by gkatzioura on 2/6/17.
 */
@RestController
public class EmployeeController {

    @Autowired
    private EmployeeRepository employeeRepository;

    @RequestMapping("/employee")
    public List<Employee> testIt() {

        return employeeRepository.findAll();
    }

    @RequestMapping("/employee/{employeeId}")
    public Employee getEmployee(@PathVariable Long employeeId) {

        return employeeRepository.findOne(employeeId);
    }

}

Once we issue a request at
http://localhost:8080/employee/1

Console will display the query issued at the database

Hibernate: select employee0_.id as id1_0_0_, employee0_.age as age2_0_0_, employee0_.email as email3_0_0_, employee0_.firstname as firstnam4_0_0_, employee0_.lastname as lastname5_0_0_, employee0_.salary as salary6_0_0_ from spring_data_jpa_example.employee employee0_ where employee0_.id=?

The second time we issue the request, since we have the second cache enabled there won’t be a query issued upon the database. Instead the entity shall be fetched from the second level cache.

You can download the project from github.

Add Custom functionality to a Spring Data Repository

Spring Data is pretty convenient and speeds up development avoiding boilerplate code.

However there are cases where annotation queries are not enough for the custom functionality you might want to achieve.

Therefore spring data allows us to add custom methods to a Spring Data Repository.

I will use the same project structure from a previous blog post.

We have an entity called Employee

package com.gkatzioura.springdata.jpa.persistence.entity;

import javax.persistence.*;

/**
 * Created by gkatzioura on 6/2/16.
 */
@Entity
@Table(name = "employee", schema="spring_data_jpa_example")
public class Employee {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id;

    @Column(name = "firstname")
    private String firstName;

    @Column(name = "lastname")
    private String lastname;

    @Column(name = "email")
    private String email;

    @Column(name = "age")
    private Integer age;

    @Column(name = "salary")
    private Integer salary;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastname() {
        return lastname;
    }

    public void setLastname(String lastname) {
        this.lastname = lastname;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Integer getSalary() {
        return salary;
    }

    public void setSalary(Integer salary) {
        this.salary = salary;
    }
}

And the Spring Data repository

package com.gkatzioura.springdata.jpa.persistence.repository;

import com.gkatzioura.springdata.jpa.persistence.entity.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

/**
 * Created by gkatzioura on 6/2/16.
 */
@Repository
public interface EmployeeRepository extends JpaRepository<Employee,Long>{

}

Suppose that we want to add some custom sql functionality for example querying with a LIKE statement and joining with a table that is not mapped as an entity.

This is for demonstration purposes only. For your project you might have a better schema. Plus spring data comes with out of the box functionality for like statements, look at EndingWith, Containing, StartingWith.

We shall create the table bonus and add a reference to the employee table.

set schema 'spring_data_jpa_example';

create table bonus(
	id serial primary key,
	employee_id integer,
	amount real,
	foreign key (employee_id) references employee (id),
	unique (employee_id)
	);

insert into bonus
( employee_id, amount)
VALUES(1, 100);	

The sql query that we want to implement will query for employees whose name starts with a specified text and a bonus bigger than a certain amount.
In jdbc we have to pass our variable concatenated with the character ‘%’.

So what we need is a native jpa query like this one

        Query query = entityManager.createNativeQuery("select e.* from spring_data_jpa_example.bonus b, spring_data_jpa_example.employee e\n" +
                "where e.id = b.employee_id " +
                "and e.firstname LIKE ? " +
                "and b.amount> ? ", Employee.class);
        query.setParameter(1, firstName + "%");
        query.setParameter(2, bonusAmount);

In order to add this functionality to our spring data Repository we have to add an interface.
It is mandatory for our interface to follow the naming convention of ${Original Repository name}Custom.
Therefore the interface describing our custom functionality should be

package com.gkatzioura.springdata.jpa.persistence.repository;

import com.gkatzioura.springdata.jpa.persistence.entity.Employee;

import java.util.List;

/**
 * Created by gkatzioura on 6/3/16.
 */
public interface EmployeeRepositoryCustom {

    List<Employee> getFirstNamesLikeAndBonusBigger(String firstName, Double bonusAmount);

}

And the implementation should be

package com.gkatzioura.springdata.jpa.persistence.repository;

import com.gkatzioura.springdata.jpa.persistence.entity.Employee;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by gkatzioura on 6/3/16.
 */
@Transactional(readOnly = true)
public class EmployeeRepositoryImpl implements EmployeeRepositoryCustom {

    @PersistenceContext
    EntityManager entityManager;

    @Override
    public List<Employee> getFirstNamesLikeAndBonusBigger(String firstName, Double bonusAmount) {
        Query query = entityManager.createNativeQuery("select e.* from spring_data_jpa_example.bonus b, spring_data_jpa_example.employee e\n" +
                "where e.id = b.employee_id " +
                "and e.firstname LIKE ? " +
                "and b.amount> ? ", Employee.class);
        query.setParameter(1, firstName + "%");
        query.setParameter(2, bonusAmount);

        return query.getResultList();
    }
}

And we should change our original spring data repository in order to inherit the custom functionality.

package com.gkatzioura.springdata.jpa.persistence.repository;

import com.gkatzioura.springdata.jpa.persistence.entity.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

/**
 * Created by gkatzioura on 6/2/16.
 */
@Repository
public interface EmployeeRepository extends JpaRepository<Employee,Long>, EmployeeRepositoryCustom {
}

Seems like a nice way of composition.

Now let’s add a method to a controller that will call this custom method

package com.gkatzioura.springdata.jpa.controller;

        import com.gkatzioura.springdata.jpa.persistence.entity.Employee;
        import com.gkatzioura.springdata.jpa.persistence.repository.EmployeeRepository;
        import org.springframework.beans.factory.annotation.Autowired;
        import org.springframework.web.bind.annotation.RequestMapping;
        import org.springframework.web.bind.annotation.RequestParam;
        import org.springframework.web.bind.annotation.RestController;

        import java.util.List;

/**
 * Created by gkatzioura on 6/2/16.
 */
@RestController
public class TestController {

    @Autowired
    private EmployeeRepository employeeRepository;

    @RequestMapping("/employee")
    public List<Employee> getTest() {

        return employeeRepository.findAll();
    }

    @RequestMapping("/employee/filter")
    public List<Employee> getFiltered(String firstName,@RequestParam(defaultValue = "0") Double bonusAmount) {

        return employeeRepository.getFirstNamesLikeAndBonusBigger(firstName,bonusAmount);
    }

}

The source code can be found on github.

Spring boot and Spring data JPA integration

Nowadays spring and JPA integration has become a piece of cake thanks to Spring Boot and spring Data.

I am gonna setup a postgresql server

docker pull postgres
#run the container
docker run --name postgreslocal -e POSTGRES_PASSWORD=postgres -d postgres
#get the ip
docker inspect --format '{{ .NetworkSettings.IPAddress }}' postgreslocal
#get the port
docker inspect --format '{{ .NetworkSettings.Ports }}' postgreslocal

Create the employees Table

create schema spring_data_jpa_example;

create table spring_data_jpa_example.employee(
	id  SERIAL PRIMARY KEY,
	firstname	TEXT	NOT NULL,
	lastname	TEXT	NOT NULL,	
   	email		TEXT 	not null,
   	age         INT     NOT NULL,
   	salary         real,
	unique(email)
);

insert into spring_data_jpa_example.employee (firstname,lastname,email,age,salary) 
values ('Emmanouil','Gkatziouras','gkatzioura@gmail.com',18,3000.23);

Let’s begin with our gradle file

group 'com.gkatzioura'
version '1.0-SNAPSHOT'

apply plugin: 'java'

sourceCompatibility = 1.8

buildscript {
    repositories {
        mavenCentral()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:1.3.3.RELEASE")
    }
}

apply plugin: 'idea'
apply plugin: 'spring-boot'

repositories {
    mavenCentral()
}

dependencies {
    compile("org.springframework.boot:spring-boot-starter-web") {
        exclude module: "spring-boot-starter-tomcat"
    }
    compile("org.postgresql:postgresql:9.4-1206-jdbc42")
    compile("org.springframework.boot:spring-boot-starter-jetty")
    compile("org.springframework.boot:spring-boot-starter-data-jpa:1.3.3.RELEASE")
    compile("com.mchange:c3p0:0.9.5.2")
    testCompile("junit:junit:4.11");
}

As you see we added the c3p0 connection pool, the spring-boot-starter-data-jpa for hibernate and the postgres driver. That’s all we need.

The Application class

package com.gkatzioura.springdata.jpa;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ApplicationContext;

/**
 * Created by gkatzioura on 6/2/16.
 */
@SpringBootApplication
public class Application {


    public static void main(String[] args) {

        SpringApplication springApplication = new SpringApplication();
        ApplicationContext ctx = springApplication.run(Application.class, args);
    }
}

The DataSource configuration

package com.gkatzioura.springdata.jpa.config;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

/**
 * Created by gkatzioura on 6/2/16.
 */
@Configuration
public class DataSourceConfig {

    @Bean
    public DataSource createDataSource() throws Exception {

        ComboPooledDataSource ds = new ComboPooledDataSource();
        ds.setJdbcUrl("jdbc:postgresql://172.17.0.3:5432/postgres?user=postgres&password=postgres");
        ds.setDriverClass("org.postgresql.Driver");

        return ds;
    }

}

Our entity for the table employee

package com.gkatzioura.springdata.jpa.persistence.entity;

import javax.persistence.*;

/**
 * Created by gkatzioura on 6/2/16.
 */
@Entity
@Table(name = "employee", schema="spring_data_jpa_example")
public class Employee {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id;

    @Column(name = "firstname")
    private String firstName;

    @Column(name = "lastname")
    private String lastname;

    @Column(name = "email")
    private String email;

    @Column(name = "age")
    private Integer age;

    @Column(name = "salary")
    private Integer salary;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastname() {
        return lastname;
    }

    public void setLastname(String lastname) {
        this.lastname = lastname;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Integer getSalary() {
        return salary;
    }

    public void setSalary(Integer salary) {
        this.salary = salary;
    }
}

The repository that will help us access all users

package com.gkatzioura.springdata.jpa.persistence.repository;

import com.gkatzioura.springdata.jpa.persistence.entity.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

/**
 * Created by gkatzioura on 6/2/16.
 */
public interface EmployeeRepository extends JpaRepository<Employee,Long>{
}

And a controller that will fetch all the data

package com.gkatzioura.springdata.jpa.controller;

import com.gkatzioura.springdata.jpa.persistence.entity.Employee;
import com.gkatzioura.springdata.jpa.persistence.repository.EmployeeRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * Created by gkatzioura on 6/2/16.
 */
@RestController
public class TestController {

    @Autowired
    private EmployeeRepository employeeRepository;

    @RequestMapping("/employee")
    public List<Employee> getTest() {

        return employeeRepository.findAll();
    }
}

Pretty convenient considering the dependencies and the xml configuration overhead of the past.

You can find the source code on github .

Use stored procedures with Hibernate

This article shows different ways to call stored procedures from hibernate.
JDBC actions will not be covered.

Postgresql will be used as a database.

CREATE DATABASE example;

CREATE TABLE company (
  company_id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE benefit (
  benefit_id BIGSERIAL PRIMARY KEY,
  name TEXT,
  company_id BIGINT,
  CONSTRAINT fk_company FOREIGN KEY (company_id)
  REFERENCES company (company_id) MATCH SIMPLE
);

CREATE TABLE employee (
  employee_id BIGSERIAL PRIMARY KEY,
  first_name TEXT,
  last_name TEXT,
  company_id BIGINT,
  CONSTRAINT fk_company FOREIGN KEY (company_id)
    REFERENCES company (company_id) MATCH SIMPLE
);

CREATE TABLE employee_benefit (
  employee_id BIGINT,
  benefit_id BIGINT,
  CONSTRAINT fk_employee FOREIGN KEY (employee_id)
  REFERENCES employee (employee_id) MATCH SIMPLE ,
  CONSTRAINT fk_benefit FOREIGN KEY (benefit_id)
  REFERENCES benefit (benefit_id) MATCH SIMPLE
);

INSERT INTO company (name) VALUES ('TestCompany');
INSERT INTO employee (first_name, last_name, company_id) VALUES ('Emmanouil','Gkatziouras',1);
INSERT INTO benefit (name,company_id) VALUES ('gym',1);
INSERT INTO benefit (name,company_id) VALUES ('lunch',1);

Our postgresql function will return a set of employee benefits

CREATE OR REPLACE FUNCTION add_all_company_benefits(employeeId BIGINT,companyid BIGINT)
  RETURNS TABLE(benefit_id BIGINT,name TEXT,company_id BIGINT) AS $$
  DECLARE benefitid BIGINT;
  BEGIN

    FOR benefitid IN (SELECT benefit.benefit_id FROM benefit WHERE benefit.company_id=companyid) LOOP
      IF (SELECT COUNT(*) FROM employee_benefit as eb
            WHERE eb.employee_id=employeeid
            AND eb.benefit_id=benefitid) = 0
      THEN
        INSERT INTO employee_benefit (employee_id, benefit_id)
        VALUES (employeeId,benefitId);
      END IF;
    END LOOP;

    RETURN QUERY
    SELECT benefit.benefit_id,benefit.name,benefit.company_id FROM benefit
      INNER JOIN employee_benefit ON employee_benefit.benefit_id = benefit.benefit_id
      WHERE employee_benefit.employee_id=employeeId;
  END;
$$ LANGUAGE plpgsql;

The entity mappings using JPA annotations follow

The company entity.

package com.gkatzioura.example.entity;

import javax.persistence.*;
import java.util.HashSet;
import java.util.Set;

@Entity
@Table(name = "company")
public class Company {

    @Id
    @GeneratedValue
    @Column(name = "company_id")
    private Long Id;

    @Column
    String name;

    @OneToMany(cascade = CascadeType.ALL,fetch = FetchType.LAZY,mappedBy = "company")
    private Set<Benefit> benefits = new HashSet<Benefit>();

    public Long getId() {
        return Id;
    }

    public void setId(Long id) {
        Id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Set<Benefit> getBenefits() {
        return benefits;
    }

    public void setBenefits(Set<Benefit> benefits) {
        this.benefits = benefits;
    }
}

The employee entity.

package com.gkatzioura.example.entity;

import javax.persistence.*;

@Entity
@Table(name = "employee")
public class Employee {

    @Id
    @GeneratedValue
    @Column(name = "employee_id")
    private Long id;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;

    @ManyToOne(cascade = CascadeType.ALL,fetch = FetchType.LAZY)
    @JoinColumn(name = "company_id",referencedColumnName = "company_id")
    private Company company;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public Company getCompany() {
        return company;
    }

    public void setCompany(Company company) {
        this.company = company;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }
}

The benefit entity.

package com.gkatzioura.example.entity;

import javax.persistence.*;

@Entity
@Table(name = "benefit")
public class Benefit {

    @Id
    @GeneratedValue
    @Column(name = "benefit_id")
    private Long id;

    @Column(name = "name")
    private String name;

    @ManyToOne
    @JoinColumn(name = "company_id")
    private Company company;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public Company getCompany() {
        return company;
    }

    public void setCompany(Company company) {
        this.company = company;
    }
}

Our first approach would be by calling the procedure as a hibernate sql query.

        Session session = sessionFactory.openSession();

        List<Company> companies = session.createCriteria(Company.class)
                .add(Restrictions.eq("name", companyName))
                .list();

        List<Employee> employees = session.createCriteria(Employee.class)
                .add(Restrictions.eq("firstName",employeeName))
                .list();

        for(Company company:companies) {

            for(Employee employee:employees) {

                LOGGER.info("The company is "+company.getId()+" the employee is "+employee.getId());

                SQLQuery query = (SQLQuery) session.createSQLQuery("SELECT*FROM add_all_company_benefits(:employeeId,:companyId)")
                        .addEntity(Benefit.class)
                        .setParameter("employeeId", employee.getId())
                        .setParameter("companyId", company.getId());


                List result = query.list();

                for(Integer i=0;i<result.size();i++) {

                    LOGGER.info("The benefit is "+((Benefit) result.get(i)).getName());
                }
            }
        }

        session.close();

We will update the company entity by adding a NameQuery in the company entity.

@Entity
@Table(name = "company")
@NamedNativeQueries({
        @NamedNativeQuery(
                name = "AddAllCompanyBenefits",
                query = "SELECT*FROM add_all_company_benefits(:employeeId,:companyId)",
                resultClass = Benefit.class
        )
})
public class Company {
}

Our hibernate actions will be refactored to

 
Query query = session.getNamedQuery("AddAllCompanyBenefits")
    .setParameter("employeeId", employee.getId())
    .setParameter("companyId", company.getId());

List result = query.list();

for(Integer i=0;i<result.size();i++) {
    LOGGER.info("The benefit is "+((Benefit) result.get(i)).getName());
}
 

Another way is to use hibernate’s ProcedureCall which is used on hibernate’s implementation of JPA’s StoredProcedureQuery


ProcedureCall procedureCall = session.createStoredProcedureCall("add_all_company_benefits");
procedureCall.registerParameter("EMPLOYEE", Long.class, ParameterMode.IN);
procedureCall.registerParameter("COMPANY", Long.class, ParameterMode.IN);
procedureCall.getParameterRegistration("EMPLOYEE").bindValue(employee.getId());
procedureCall.getParameterRegistration("COMPANY").bindValue(company.getId());                

ProcedureOutputs procedureOutputs = procedureCall.getOutputs();
ResultSetOutput resultSetOutput = (ResultSetOutput) procedureOutputs.getCurrent();


List results = resultSetOutput.getResultList();

for(Integer i=0;i<results.size();i++) {

    Object[] objects = (Object[]) results.get(i);

    LOGGER.info("The benefit is "+objects[1]);
}

Hibernate: use join table alias on sqlRestriction

When using hibernate in case of complex queries there is the need to use sql.
Therefore sqlRestrictions come to the rescue. However using sql restrictions on join table alias is a bit tricky.

There would be three tables
The company table.
The employee table.
Each employee belongs to one company therefore we have a many to one relationship.
The benefit table.
Each company has many benefits therefore we have a one to many relationship.

Postgresql will be used for this example.

CREATE DATABASE example;

CREATE TABLE company (
  company_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE employee (
  employee_id SERIAL PRIMARY KEY,
  first_name TEXT,
  last_name TEXT,
  company_id integer,
  CONSTRAINT fk_company FOREIGN KEY (company_id)
    REFERENCES company (company_id) MATCH SIMPLE
);

CREATE TABLE benefit (
  benefit_id SERIAL PRIMARY KEY,
  name TEXT,
  company_id integer,
  CONSTRAINT fk_company FOREIGN KEY (company_id)
  REFERENCES company (company_id) MATCH SIMPLE
);

INSERT INTO company (name) VALUES ('TestCompany');
INSERT INTO employee (first_name, last_name, company_id) VALUES ('Emmanouil','Gkatziouras',1);
INSERT INTO benefit (name,company_id) VALUES ('gym',1);

JPA would be used for the entity configuration.

The company entity.

package com.gkatzioura.example.entity;

import javax.persistence.*;
import java.util.HashSet;
import java.util.Set;

/**
 * Created by gkatziourasemmanouil on 7/12/15.
 */
@Entity
@Table(name = "company")
public class Company {

    @Id
    @GeneratedValue
    @Column(name = "company_id")
    private Long Id;

    @Column
    String name;

    @OneToMany(cascade = CascadeType.ALL,fetch = FetchType.LAZY,mappedBy = "company")
    private Set<Benefit> benefits = new HashSet<Benefit>();

    public Long getId() {
        return Id;
    }

    public void setId(Long id) {
        Id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Set<Benefit> getBenefits() {
        return benefits;
    }

    public void setBenefits(Set<Benefit> benefits) {
        this.benefits = benefits;
    }
}

The employee entity.

package com.gkatzioura.example.entity;

import javax.persistence.*;

/**
 * Created by gkatziourasemmanouil on 8/2/15.
 */
@Entity
@Table(name = "employee")
public class Employee {

    @Id
    @GeneratedValue
    @Column(name = "employee_id")
    private Long id;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;

    @ManyToOne(cascade = CascadeType.ALL,fetch = FetchType.LAZY)
    @JoinColumn(name = "company_id",referencedColumnName = "company_id")
    private Company company;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public Company getCompany() {
        return company;
    }

    public void setCompany(Company company) {
        this.company = company;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }
}

The benefit entity.

package com.gkatzioura.example.entity;

import javax.persistence.*;

/**
 * Created by gkatziourasemmanouil on 8/9/15.
 */

@Entity
@Table(name = "benefit")
public class Benefit {

    @Id
    @GeneratedValue
    @Column(name = "benefit_id")
    private Long id;

    @Column(name = "name")
    private String name;

    @ManyToOne
    @JoinColumn(name = "company_id")
    private Company company;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public Company getCompany() {
        return company;
    }

    public void setCompany(Company company) {
        this.company = company;
    }
}

The hibernate configuration

<?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>

        <!-- Database connection settings -->
        <property name="connection.driver_class">org.postgresql.Driver</property>
        <property name="connection.url">jdbc:postgresql://127.0.0.1:5432/example</property>
        <property name="connection.username">postgres</property>
        <property name="connection.password">postgres</property>

        <!-- JDBC connection pool (use the built-in) -->
        <property name="connection.pool_size">1</property>

        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.PostgreSQL9Dialect</property>

        <!-- Enable Hibernate's automatic session context management -->
        <property name="current_session_context_class">thread</property>

        <!-- Disable the second-level cache  -->
        <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>

        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">true</property>

        <!-- Drop and re-create the database schema on startup -->
        <property name="hbm2ddl.auto">update</property>

        <mapping class="com.gkatzioura.example.entity.Company"/>
        <mapping class="com.gkatzioura.example.entity.Employee"/>
        <mapping class="com.gkatzioura.example.entity.Benefit"/>

    </session-factory>

</hibernate-configuration>

The main class creating the hibernate session factory

package com.gkatzioura.example;

import com.gkatzioura.example.entity.Employee;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Restrictions;
import org.hibernate.sql.JoinType;
import org.hibernate.type.StringType;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Created by gkatziourasemmanouil on 7/12/15.
 */
public class Main {

    private static SessionFactory sessionFactory;

    private static final org.slf4j.Logger LOGGER = LoggerFactory.getLogger(SessionFactory.class);

    public static void main(String args[]) {

        sessionFactory = buildSessionFactory();

        getByCompanyAndBenefit("TestCompany","gym");

        sessionFactory.close();
    }

    public static SessionFactory buildSessionFactory() {

        return new Configuration()
                .configure()
                .buildSessionFactory();
    }

    
}

Supposing we want to fetch the employees by the company name and a specific benefit name, we shall add the getByCompanyAndBenefit function.
The company name would be filtered by a normal restriction.
However the benefit name would be filtered by a sqlRestriction.

private static List<Employee> getByCompanyAndBenefit(String companyName,String benefitName) {

        Session session = sessionFactory.openSession();

        Criteria criteria = session.createCriteria(Employee.class,"employee");

        Criteria companyCriteria = criteria.createCriteria("company",JoinType.INNER_JOIN);
        companyCriteria.add(Restrictions.eq("name", companyName));
        Criteria benefitsAlias = companyCriteria.createCriteria("benefits",JoinType.LEFT_OUTER_JOIN);
        benefitsAlias.add(Restrictions.sqlRestriction("{alias}.name = ?",benefitName, StringType.INSTANCE));

        List<Employee> employees = criteria.list();

        for(Employee employee:employees) {
            LOGGER.error("The employee is "+employee.getFirstName());
        }

        session.close();

        return employees;
    }

When using the createCriteria function of a criteria for a relationship, the {alias} represents the associated entity of the new criteria.
This is not possible with the criteria class retrieved after using createAlias.

Last but not least the gradle file.

group 'com.gkatzioura.example'
version '1.0-SNAPSHOT'

apply plugin: 'application'
apply plugin: 'java'
apply plugin: 'idea'

mainClassName = "com.gkatzioura.example.Main"

sourceCompatibility = 1.8

repositories {
    mavenLocal()
    mavenCentral()
}

dependencies {
    testCompile group: 'junit', name: 'junit', version: '4.11'

    compile 'org.ancoron.postgresql:org.postgresql.net:9.1.901.jdbc4.1-rc9'
    compile 'org.slf4j:slf4j-api:1.6.6'
    compile 'ch.qos.logback:logback-classic:1.0.13'
    compile 'org.hibernate:hibernate-core:4.3.6.Final'
    compile 'org.hibernate:hibernate-entitymanager:4.3.6.Final'
    compile 'org.hibernate:hibernate-validator:5.1.1.Final'
    compile 'dom4j:dom4j:1.6.1'
    compile 'org.hibernate.javax.persistence:hibernate-jpa-2.1-api:1.0.0.Final'

    testCompile group: 'junit', name: 'junit', version:'3.8.1'

}