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: JPA caching basics

One of the greatest capabilities of HazelCast is the support for hibernate’s second level cache.

JPA has two levels of cache.
The first level cache caches an object’s state for the duration of a transaction. By querying the same object twice you have to get the object your retrieved the first time.
However in case of complex queries which include the object you retrieved and access your database, chances are, that the results would be out of sync since they will not reflect the changes you applied to the object in memory during the transaction. However you can tackle this with flush().
Once a JPA session is initiated its first level cache is restricted to that session, it will not affect other sessions.
First level cache is required as a part of JPA

The second level cache on the contrary to first level cache is associated with the Session Factory, thus the second level cache is shared across sessions. Commonly used data can be stored in memory and retrieved faster.

Once you have the second level cache enabled hibernate will cache the entities retrieved in a hibernate region. To do so you have to set your entities as cachable. Under the hood the information that resides in an entity is cached in a dehydrated format.

Hazelcast can be used with second level cache in two forms of architectures.
Client-server or cluster-only architecture.
For start we will investigate a cluster only architecture.
Hazelcast creates a separate distributed map for each Hibernate cache region therefore an entity. You can easily configure these regions via Hazelcast map configuration.The name of the region has a corresponding hazelcast map. For example one of our entities is called User and the full package path is ‘com.gkatzioura.User’ then our hazelcast will have the name ‘com.gkatzioura.User’. Provided that this map is distributed across hazelcast nodes, once the entity is retrieved from one node the cached information will be shared to other hazelcast nodes. Once an entity gets updated in a node the cached information will be invalidated in the other nodes.

Hibernate also provides us with Query cache. Query cache is a cache that caches query results . For example in case of a jpql query

SELECT usr.username,usr.firstname FROM User usr

the cached result would be a map with a key composed of the query and the parameters

and the value the results retrieved. In the previous cache the data retrieved are primitive values and they are stored as it is.
However there cases in which a query might retrieve entities.
For example

SELECT c FROM Customer c

In such cases instead of storing all the information retrieved, the entities are retrieved and cached in the second level cache whilst the query cache has an entry using the query and its parameters as a key and the entity ids as the value.
Once the same query is issued again the query cache will fetch the ids and will lookup on the second level cache for the corresponding entities. If an entity does not exist in the second level cache, then a query is issued in order to fetch the entity missing.
When it comes to second level cache and query cache configuration we need to pay attention on the eviction mechanisms of the second level cache and query cache. You might stumble in cases of ids being cached in the query cache however their corresponding entities are evicted from the second level cache. In such cases there is a performance hit since hibernate will issue a query for each entity missing.

Hazelcast has support for the query cache, however it is local to the node and never distributed across hazelcast cluster.
Although the results fetched from a query remain on the specific node, the entities specified from the cached query shall be retrieved from the distributed map which is used as a second level cache.

This is the theory we need so far. On the next blog we do some spring data jpa code and some hazelcast configurations.

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'

}