如果在复杂查询中使用休眠模式,则需要使用sql。
因此,sqlRestrictions可以解决。 但是,对联接表别名使用sql限制有点棘手。
将有三个表:
- 公司表。
- 员工表。
每个员工都属于一家公司,因此我们有多对一的关系。 - 福利表。
每个公司都有很多好处,因此我们有一对多的关系。
本示例将使用Postgresql。
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将用于实体配置。
公司实体。
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;
}
}
员工实体。
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;
}
}
利益实体。
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;
}
}
休眠配置
<?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>
创建休眠会话工厂的主类
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();
}
}
假设我们要通过公司名称和特定的福利名称来提取员工,我们将添加getByCompanyAndBenefit函数。
公司名称将通过常规限制进行过滤。
但是,福利名称将由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;
}
对关系使用条件的createCriteria函数时,{alias}表示新条件的关联实体。
对于使用createAlias之后检索到的条件类,这是不可能的。
最后但并非最不重要的gradle文件。
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'
}
翻译自: https://www.javacodegeeks.com/2015/08/hibernate-use-join-table-alias-on-sqlrestriction.html