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'
}| Reference: | Hibernate: use join table alias on sqlRestriction from our JCG partner Emmanouil Gkatziouras at the gkatzioura blog. |
