Here we have 3 entities:
(on stackoverflow)- CoaCompany (company)
- CoaOrganization (organization)
- CoaAddress (address)
A company is associated with an organization, and an organization has a list of zero or more addresses.
I would like to use the Criteria Query API of JPA 2.0 to pull companies based on search patterns in arbitrary elements of address. Address has attributes like: line1, line2, city, state, zipcode, etc.
In SQL you can use a subquery like the following beginning at the organization level to search based on zipcode:
Select * from coa_organization org where exists ( select 1 from coa_address ad where ad.zipcode = 74112 and ad.org_id = org.org_id);
Notice the exists clause referencing a subquery.
If using spring-data under JPA, we can use a Query annotation in a Repository definition as follows:
@Query("select p from CoaCompany p, CoaOrganization org where (p.organization = org.id) and exists ( select 1 from CoaAddress ad where zipcode = :zipcode and ad.organization = org.id)")So, how can the equivalent operation be done using the Criteria Query API?
Class CoaCompanyRepository
package com.jgk.coa.repository;
import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import com.jgk.coa.domain.CoaCompany;
public interface CoaCompanyRepository extends JpaRepository<CoaCompany, Long>, JpaSpecificationExecutor<CoaCompany> {
@Query("select p from CoaCompany p, CoaOrganization org where (p.organization = org.id) and exists ( select 1 from CoaAddress ad where zipcode = :zipcode and ad.organization = org.id)")
List<
CoaCompany> findByZipcode(@Param("zipcode") String zipcode);}
Class CoaCompany
package com.jgk.coa.domain;
import javax.persistence.CascadeType;
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;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
@Entity
@Table(name="COA_COMPANY")
public class CoaCompany {
@Column(name = "COMPANY_ID")
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator = "COMPANY_ID_SEQUENCE")
@SequenceGenerator(name="COMPANY_ID_SEQUENCE", sequenceName = "COMPANY_ID_SEQUENCE", allocationSize = 0)
@Id private Long id;
@Column(name="COMPANY_NAME")
private String companyName;
@ManyToOne(cascade=CascadeType.ALL)
@JoinColumn(name="ORG_ID")
private CoaOrganization organization;
protected CoaCompany(){}
public static CoaCompany create(String companyName2, CoaOrganization coaOrganization) {
CoaCompany cc = new CoaCompany();
cc.setCompanyName(companyName2);
cc.setOrganization(coaOrganization);
coaOrganization.setCompany(cc);
return cc;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public CoaOrganization getOrganization() {
return organization;
}
public void setOrganization(CoaOrganization organization) {
this.organization = organization;
}
public String getCompanyName() {
return companyName;
}
public void setCompanyName(String companyName) {
this.companyName = companyName;
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result
+ ((companyName == null) ? 0 : companyName.hashCode());
result = prime * result + ((id == null) ? 0 : id.hashCode());
result = prime * result
+ ((organization == null) ? 0 : organization.hashCode());
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
CoaCompany other = (CoaCompany) obj;
if (companyName == null) {
if (other.companyName != null)
return false;
} else if (!companyName.equals(other.companyName))
return false;
if (id == null) {
if (other.id != null)
return false;
} else if (!id.equals(other.id))
return false;
if (organization == null) {
if (other.organization != null)
return false;
} else if (!organization.equals(other.organization))
return false;
return true;
}
@Override
public String toString() {
StringBuilder builder = new StringBuilder();
builder.append("CoaCompany [id=");
builder.append(id);
builder.append(", companyName=");
builder.append(companyName);
builder.append(", organization=");
builder.append(organization);
builder.append("]");
return builder.toString();
}
}
Class CoaOrganization
package com.jgk.coa.domain;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.CascadeType;
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;
import javax.persistence.OneToMany;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
@Entity
@Table(name="COA_ORGANIZATION")
public class CoaOrganization {
@Column(name = "ORG_ID")
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator = "ORG_ID_SEQUENCE")
@SequenceGenerator(name="ORG_ID_SEQUENCE", sequenceName = "ORG_ID_SEQUENCE", allocationSize = 0)
@Id Long id;
@Column(name="ORG_NAME")
private String name;
@OneToMany(cascade=CascadeType.ALL)
@JoinColumn(name="ORG_ID")
List addresses;
@ManyToOne
private CoaCompany company;
protected CoaOrganization() {}
public static CoaOrganization create(String orgName) {
CoaOrganization org = new CoaOrganization();
org.setName(orgName);
return org;
}
public void addAddress(CoaAddress address) {
if(addresses==null) {
addresses = new ArrayList();
}
address.setOrganization(this);
this.addresses.add(address);
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List getAddresses() {
return addresses;
}
public void setAddresses(List addresses) {
this.addresses = addresses;
}
public void setCompany(CoaCompany cc) {
this.company=cc;
}
public CoaCompany getCompany() {
return company;
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((id == null) ? 0 : id.hashCode());
result = prime * result + ((name == null) ? 0 : name.hashCode());
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
CoaOrganization other = (CoaOrganization) obj;
if (id == null) {
if (other.id != null)
return false;
} else if (!id.equals(other.id))
return false;
if (name == null) {
if (other.name != null)
return false;
} else if (!name.equals(other.name))
return false;
return true;
}
@Override
public String toString() {
StringBuilder builder = new StringBuilder();
builder.append("CoaOrganization [id=");
builder.append(id);
builder.append(", name=");
builder.append(name);
builder.append(", addresses=");
builder.append(addresses);
builder.append("]");
return builder.toString();
}
}
Class CoaAddress
package com.jgk.coa.domain;
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;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
@Entity
@Table(name="COA_ADDRESS")
public class CoaAddress {
@Column(name="LINE1")
private String line1;
@Column(name="LINE2")
private String line2;
@Column(name="CITY")
private String city;
@Column(name="STATE")
private String state;
@Column(name="ZIPCODE")
private String zipcode;
@Column(name="COUNTRY")
private String country;
@ManyToOne
@JoinColumn(name="ORG_ID")
private CoaOrganization organization;
@Column(name = "ADDRESS_ID")
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator = "ADDRESS_ID_SEQUENCE")
@SequenceGenerator(name="ADDRESS_ID_SEQUENCE", sequenceName = "ADDRESS_ID_SEQUENCE", allocationSize = 0)
@Id private Long id;
protected CoaAddress() {}
public static CoaAddress create(String line12, String line22, String city2, String state2, String zipcode2) {
CoaAddress address = new CoaAddress();
address.setLine1(line12);
address.setLine2(line22);
address.setCity(city2);
address.setState(state2);
address.setZipcode(zipcode2);
return address;
}
public String getLine1() {
return line1;
}
public void setLine1(String line1) {
this.line1 = line1;
}
public String getLine2() {
return line2;
}
public void setLine2(String line2) {
this.line2 = line2;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public String getState() {
return state;
}
public void setState(String state) {
this.state = state;
}
public String getZipcode() {
return zipcode;
}
public void setZipcode(String zipcode) {
this.zipcode = zipcode;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public void setOrganization(CoaOrganization coaOrganization) {
this.organization=coaOrganization;
}
public CoaOrganization getOrganization() {
return organization;
}
public String getCountry() {
return country;
}
public void setCountry(String country) {
this.country = country;
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((city == null) ? 0 : city.hashCode());
result = prime * result + ((country == null) ? 0 : country.hashCode());
result = prime * result + ((id == null) ? 0 : id.hashCode());
result = prime * result + ((line1 == null) ? 0 : line1.hashCode());
result = prime * result + ((line2 == null) ? 0 : line2.hashCode());
result = prime * result
+ ((organization == null) ? 0 : organization.hashCode());
result = prime * result + ((state == null) ? 0 : state.hashCode());
result = prime * result + ((zipcode == null) ? 0 : zipcode.hashCode());
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
CoaAddress other = (CoaAddress) obj;
if (city == null) {
if (other.city != null)
return false;
} else if (!city.equals(other.city))
return false;
if (country == null) {
if (other.country != null)
return false;
} else if (!country.equals(other.country))
return false;
if (id == null) {
if (other.id != null)
return false;
} else if (!id.equals(other.id))
return false;
if (line1 == null) {
if (other.line1 != null)
return false;
} else if (!line1.equals(other.line1))
return false;
if (line2 == null) {
if (other.line2 != null)
return false;
} else if (!line2.equals(other.line2))
return false;
if (organization == null) {
if (other.organization != null)
return false;
} else if (!organization.equals(other.organization))
return false;
if (state == null) {
if (other.state != null)
return false;
} else if (!state.equals(other.state))
return false;
if (zipcode == null) {
if (other.zipcode != null)
return false;
} else if (!zipcode.equals(other.zipcode))
return false;
return true;
}
@Override
public String toString() {
StringBuilder builder = new StringBuilder();
builder.append("CoaAddress [line1=");
builder.append(line1);
builder.append(", line2=");
builder.append(line2);
builder.append(", city=");
builder.append(city);
builder.append(", state=");
builder.append(state);
builder.append(", zipcode=");
builder.append(zipcode);
builder.append(", country=");
builder.append(country);
builder.append(", organization null=");
builder.append(organization==null);
builder.append(", id=");
builder.append(id);
builder.append("]");
return builder.toString();
}
}
JPA Descriptor (META-INF/coa-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_2_0.xsd" version="2.0">
<persistence-unit name="COA-PU" transaction-type="RESOURCE_LOCAL">
<provider>org.hibernate.ejb.HibernatePersistenceprovider>
<class>com.jgk.coa.domain.CoaAddressclass>
<class>com.jgk.coa.domain.CoaCompanyclass>
<class>com.jgk.coa.domain.CoaOrganizationclass>
<exclude-unlisted-classes>trueexclude-unlisted-classes>
<properties>
<property name="hibernate.hbm2ddl.auto" value="create-drop" />
<property name="hibernate.show_sql" value="true" />
<property name="hibernate.transaction.flush_before_completion"
value="true" />
<property name="hibernate.cache.provider_class" value="org.hibernate.cache.HashtableCacheProvider" />
properties>
persistence-unit>
persistence>
Maven descriptor (pom.xml)
<xml version="1.0" encoding="UTF-8"?>
<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/maven-v4_0_0.xsd">
<modelVersion>4.0.0modelVersion>
<groupId>com.jgkgroupId>
<artifactId>company-organization-addressesartifactId>
<packaging>warpackaging>
<version>0.0.1-SNAPSHOTversion>
<name>company-organization-addressesname>
<properties>
<project.build.sourceEncoding>UTF-8project.build.sourceEncoding>
<jetty.version>6.1.4jetty.version>
<maven.compiler.source>1.6maven.compiler.source>
<maven.compiler.target>1.6maven.compiler.target>
<antlr.version>2.7.6antlr.version>
<aopalliance.version>1.0aopalliance.version>
<c3p0.version>0.9.1.2c3p0.version>
<cglib.version>2.2.2cglib.version>
<commons-beanutils.version>1.8.3commons-beanutils.version>
<commons-collections.version>3.1commons-collections.version>
<commons-logging.version>1.1.1commons-logging.version>
<ojdbc6.version>11.1.0.7.0ojdbc6.version>
<commons-dbcp.version>1.4commons-dbcp.version>
<dom4j.version>1.6.1dom4j.version>
<javassist.version>3.9.0.GAjavassist.version>
<javax.inject.version>1.0-PFD-1javax.inject.version>
<javax.transaction.version>1.1javax.transaction.version>
<junit.version>4.9junit.version>
<log4j.version>1.2.16log4j.version>
<org.aspectj.version>1.6.10org.aspectj.version>
<org.hibernate.version>4.0.0.Finalorg.hibernate.version>
<org.hibernate-commons-annotations.version>4.0.1.Finalorg.hibernate-commons-annotations.version>
<org.hibernate.hibernate-validator.version>4.1.0.Finalorg.hibernate.hibernate-validator.version>
<org.hibernate.jpamodelgen.version>1.1.1.Finalorg.hibernate.jpamodelgen.version>
<org.slf4j.version>1.6.1org.slf4j.version>
<org.springframework.version>3.1.0.RELEASEorg.springframework.version>
<org.springframework.data.version>1.0.1.RELEASEorg.springframework.data.version>
<wagon-ssh.version>1.0-beta-6wagon-ssh.version>
properties>
<dependencies>
<dependency>
<groupId>javax.injectgroupId>
<artifactId>javax.injectartifactId>
<version>${javax.inject.version}version>
dependency>
<dependency>
<groupId>org.hibernate.commongroupId>
<artifactId>hibernate-commons-annotationsartifactId>
<version>${org.hibernate-commons-annotations.version}version>
dependency>
<dependency>
<groupId>org.hibernategroupId>
<artifactId>hibernate-entitymanagerartifactId>
<version>${org.hibernate.version}version>
dependency>
<dependency>
<groupId>org.hibernategroupId>
<artifactId>hibernate-coreartifactId>
<version>${org.hibernate.version}version>
dependency>
<dependency>
<groupId>org.hibernategroupId>
<artifactId>hibernate-ehcacheartifactId>
<version>${org.hibernate.version}version>
dependency>
<dependency>
<groupId>org.hibernategroupId>
<artifactId>hibernate-validatorartifactId>
<version>${org.hibernate.hibernate-validator.version}version>
dependency>
<dependency>
<groupId>cglibgroupId>
<artifactId>cglibartifactId>
<version>${cglib.version}version>
dependency>
<dependency>
<groupId>cglibgroupId>
<artifactId>cglib-nodepartifactId>
<version>${cglib.version}version>
dependency>
<dependency>
<groupId>antlrgroupId>
<artifactId>antlrartifactId>
<version>${antlr.version}version>
dependency>
<dependency>
<groupId>commons-collectionsgroupId>
<artifactId>commons-collectionsartifactId>
<version>${commons-collections.version}version>
dependency>
<dependency>
<groupId>javax.transactiongroupId>
<artifactId>jtaartifactId>
<version>${javax.transaction.version}version>
dependency>
<dependency>
<groupId>org.slf4jgroupId>
<artifactId>slf4j-apiartifactId>
<version>${org.slf4j.version}version>
dependency>
<dependency>
<groupId>org.slf4jgroupId>
<artifactId>slf4j-log4j12artifactId>
<version>${org.slf4j.version}version>
dependency>
<dependency>
<groupId>dom4jgroupId>
<artifactId>dom4jartifactId>
<version>${dom4j.version}version>
dependency>
<dependency>
<groupId>javassistgroupId>
<artifactId>javassistartifactId>
<version>${javassist.version}version>
dependency>
<dependency>
<groupId>commons-logginggroupId>
<artifactId>commons-loggingartifactId>
<version>${commons-logging.version}version>
dependency>
<dependency>
<groupId>log4jgroupId>
<artifactId>log4jartifactId>
<version>${log4j.version}version>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-aopartifactId>
<version>${org.springframework.version}version>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-asmartifactId>
<version>${org.springframework.version}version>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-aspectsartifactId>
<version>${org.springframework.version}version>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-coreartifactId>
<version>${org.springframework.version}version>
<exclusions>
<exclusion>
<groupId>commons-logginggroupId>
<artifactId>commons-loggingartifactId>
exclusion>
exclusions>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-beansartifactId>
<version>${org.springframework.version}version>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-contextartifactId>
<version>${org.springframework.version}version>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-context-supportartifactId>
<version>${org.springframework.version}version>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-expressionartifactId>
<version>${org.springframework.version}version>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-jdbcartifactId>
<version>${org.springframework.version}version>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-jmsartifactId>
<version>${org.springframework.version}version>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-ormartifactId>
<version>${org.springframework.version}version>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-oxmartifactId>
<version>${org.springframework.version}version>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-txartifactId>
<version>${org.springframework.version}version>
dependency>
<dependency>
<groupId>aopalliancegroupId>
<artifactId>aopallianceartifactId>
<version>${aopalliance.version}version>
dependency>
<dependency>
<groupId>org.aspectjgroupId>
<artifactId>aspectjweaverartifactId>
<version>${org.aspectj.version}version>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-instrumentartifactId>
<version>${org.springframework.version}version>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-instrument-tomcatartifactId>
<version>${org.springframework.version}version>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-testartifactId>
<version>${org.springframework.version}version>
dependency>
<dependency>
<groupId>org.springframework.datagroupId>
<artifactId>spring-data-jpaartifactId>
<version>${org.springframework.data.version}version>
dependency>
<dependency>
<groupId>org.springframework.datagroupId>
<artifactId>spring-data-commons-coreartifactId>
<version>1.1.0.RELEASEversion>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-webartifactId>
<version>${org.springframework.version}version>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-webmvcartifactId>
<version>${org.springframework.version}version>
dependency>
<dependency>
<groupId>com.oraclegroupId>
<artifactId>ojdbc6artifactId>
<version>${ojdbc6.version}version>
dependency>
<dependency>
<groupId>commons-beanutilsgroupId>
<artifactId>commons-beanutilsartifactId>
<version>${commons-beanutils.version}version>
dependency>
<dependency>
<groupId>org.hibernategroupId>
<artifactId>hibernate-jpamodelgenartifactId>
<version>${org.hibernate.jpamodelgen.version}version>
dependency>
<dependency>
<groupId>commons-dbcpgroupId>
<artifactId>commons-dbcpartifactId>
<version>${commons-dbcp.version}version>
dependency>
<dependency>
<groupId>javax.validationgroupId>
<artifactId>validation-apiartifactId>
<version>1.0.0.GAversion>
<scope>compilescope>
dependency>
<dependency>
<groupId>hsqldbgroupId>
<artifactId>hsqldbartifactId>
<version>1.8.0.10version>
dependency>
<dependency>
<groupId>taglibsgroupId>
<artifactId>standardartifactId>
<version>1.1.2version>
<type>jartype>
<scope>compilescope>
dependency>
<dependency>
<groupId>junitgroupId>
<artifactId>junitartifactId>
<version>${junit.version}version>
<scope>testscope>
dependency>
dependencies>
<repositories>
<repository>
<id>repository.jboss.orgid>
<name>JBoss Repositoryname>
<url>http://repository.jboss.org/nexus/content/groups/public-jboss/url>
repository>
<repository>
<id>org.springframework.maven.milestoneid>
<name>Spring Maven Milestone Repositoryname>
<url>http://maven.springframework.org/milestoneurl>
<snapshots>
<enabled>falseenabled>
snapshots>
repository>
repositories>
<build>
<finalName>company-organization-addressesfinalName>
<plugins>
<plugin>
<groupId>org.bsc.mavengroupId>
<artifactId>maven-processor-pluginartifactId>
<version>2.0.5version>
<executions>
<execution>
<id>processid>
<goals>
<goal>processgoal>
goals>
<phase>generate-sourcesphase>
<configuration>
<outputDirectory>src/main/javaoutputDirectory>
<processors>
<processor>org.hibernate.jpamodelgen.JPAMetaModelEntityProcessorprocessor>
processors>
configuration>
execution>
executions>
<dependencies>
<dependency>
<groupId>org.hibernategroupId>
<artifactId>hibernate-jpamodelgenartifactId>
<version>1.2.0.Finalversion>
dependency>
dependencies>
plugin>
<plugin>
<groupId>org.apache.maven.pluginsgroupId>
<artifactId>maven-eclipse-pluginartifactId>
<version>2.8version>
<configuration>
<wtpversion>2.0wtpversion>
<downloadSources>truedownloadSources>
<downloadJavadocs>truedownloadJavadocs>
configuration>
plugin>
<plugin>
<groupId>org.mortbay.jettygroupId>
<artifactId>maven-jetty-pluginartifactId>
<version>6.1.25version>
<configuration>
<scanIntervalSeconds>3scanIntervalSeconds>
configuration>
plugin>
<plugin>
<groupId>org.apache.maven.pluginsgroupId>
<artifactId>maven-compiler-pluginartifactId>
<version>2.3.2version>
<configuration>
<source>${maven.compiler.source}source>
<target>${maven.compiler.target}target>
<compilerArgument>-proc:nonecompilerArgument>
configuration>
plugin>
<plugin>
<groupId>org.codehaus.mojogroupId>
<artifactId>tomcat-maven-pluginartifactId>
<version>1.1version>
<configuration>
<path>/${project.build.finalName}path>
configuration>
plugin>
plugins>
build>
project>
