Java/JPA/Self Join
Join Member Entities
File: Professor.java
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.ManyToMany;
import javax.persistence.ManyToOne;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.OneToMany;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
@Entity
public class Professor {
@Id
private int id;
private String name;
private long salary;
@Temporal(TemporalType.DATE)
private Date startDate;
@ManyToOne
private Professor manager;
@OneToMany(mappedBy="manager")
private Collection<Professor> directs;
@ManyToOne
private Department department;
@ManyToMany
private Collection<Project> projects;
public Professor() {
projects = new ArrayList<Project>();
directs = new ArrayList<Professor>();
}
public int getId() {
return id;
}
public String getName() {
return name;
}
public long getSalary() {
return salary;
}
public Date getStartDate() {
return startDate;
}
public Department getDepartment() {
return department;
}
public Collection<Professor> getDirects() {
return directs;
}
public Professor getManager() {
return manager;
}
public Collection<Project> getProjects() {
return projects;
}
public String toString() {
return "Professor " + getId() +
": name: " + getName() +
", salary: " + getSalary() +
", dept: " + ((getDepartment() == null) ? null : getDepartment().getName());
}
}
File: ProfessorService.java
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.Query;
public class ProfessorService {
protected EntityManager em;
public ProfessorService(EntityManager em) {
this.em = em;
}
Query unassignedQuery;
public List findProjectProfessors(String projectName) {
return em.createQuery("SELECT e " +
"FROM Project p JOIN p.employees e " +
"WHERE p.name = :project " +
"ORDER BY e.name")
.setParameter("project", projectName)
.getResultList();
}
}
File: Project.java
import java.util.ArrayList;
import java.util.Collection;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.ManyToMany;
@Entity
public class Project {
@Id
protected int id;
protected String name;
@ManyToMany(mappedBy="projects")
private Collection<Professor> employees;
public Project() {
employees = new ArrayList<Professor>();
}
public int getId() {
return id;
}
public String getName() {
return name;
}
public Collection<Professor> getProfessors() {
return employees;
}
public String toString() {
return "Project id: " + getId() + ", name: " + getName();
}
}
File: Department.java
import java.util.ArrayList;
import java.util.Collection;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.OneToMany;
@Entity
public class Department {
@Id
private int id;
private String name;
@OneToMany(mappedBy="department")
private Collection<Professor> employees;
public Department() {
employees = new ArrayList<Professor>();
}
public int getId() {
return id;
}
public String getName() {
return name;
}
public Collection<Professor> getProfessors() {
return employees;
}
public String toString() {
return "Department no: " + getId() +
", name: " + getName();
}
}
File: JPAUtil.java
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
public class JPAUtil {
Statement st;
public JPAUtil() throws Exception{
Class.forName("org.hsqldb.jdbcDriver");
System.out.println("Driver Loaded.");
String url = "jdbc:hsqldb:data/tutorial";
Connection conn = DriverManager.getConnection(url, "sa", "");
System.out.println("Got Connection.");
st = conn.createStatement();
}
public void executeSQLCommand(String sql) throws Exception {
st.executeUpdate(sql);
}
public void checkData(String sql) throws Exception {
ResultSet rs = st.executeQuery(sql);
ResultSetMetaData metadata = rs.getMetaData();
for (int i = 0; i < metadata.getColumnCount(); i++) {
System.out.print("\t"+ metadata.getColumnLabel(i + 1));
}
System.out.println("\n----------------------------------");
while (rs.next()) {
for (int i = 0; i < metadata.getColumnCount(); i++) {
Object value = rs.getObject(i + 1);
if (value == null) {
System.out.print("\t ");
} else {
System.out.print("\t"+value.toString().trim());
}
}
System.out.println("");
}
}
}
File: Main.java
import java.util.Collection;
import java.util.Date;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
public class Main {
public static void main(String[] a) throws Exception {
JPAUtil util = new JPAUtil();
EntityManagerFactory emf = Persistence.createEntityManagerFactory("ProfessorService");
EntityManager em = emf.createEntityManager();
ProfessorService service = new ProfessorService(em);
em.getTransaction().begin();
service.findProjectProfessors("projName");
util.checkData("select * from Professor");
util.checkData("select * from Department");
em.getTransaction().rumit();
em.close();
emf.close();
}
}
File: persistence.xml
<persistence xmlns="http://java.sun.ru/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.ru/xml/ns/persistence http://java.sun.ru/xml/ns/persistence/persistence" version="1.0">
<persistence-unit name="JPAService" transaction-type="RESOURCE_LOCAL">
<properties>
<property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/>
<property name="hibernate.hbm2ddl.auto" value="update"/>
<property name="hibernate.connection.driver_class" value="org.hsqldb.jdbcDriver"/>
<property name="hibernate.connection.username" value="sa"/>
<property name="hibernate.connection.password" value=""/>
<property name="hibernate.connection.url" value="jdbc:hsqldb:data/tutorial"/>
</properties>
</persistence-unit>
</persistence>
Relations on one entity
/*
* Licensed to the Apache Software Foundation (ASF) under one
* or more contributor license agreements. See the NOTICE file
* distributed with this work for additional information
* regarding copyright ownership. The ASF licenses this file
* to you under the Apache License, Version 2.0 (the
* "License"); you may not use this file except in compliance
* with the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing,
* software distributed under the License is distributed on an
* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
* KIND, either express or implied. See the License for the
* specific language governing permissions and limitations
* under the License.
*/
package relations;
import java.util.*;
import javax.persistence.*;
// import the enums for MALE and FEMALE
import static relations.Deity.Gender.*;
/**
* A very simple, stand-alone program that stores a new entity in the
* database and then performs a query to retrieve it.
*/
public class Main {
@SuppressWarnings("unchecked")
public static void main(String[] args) {
// Create a new EntityManagerFactory using the System properties.
// The "relations" name will be used to configure based on the
// corresponding name in the META-INF/persistence.xml file
EntityManagerFactory factory = Persistence.
createEntityManagerFactory("relations", System.getProperties());
// Create a new EntityManager from the EntityManagerFactory. The
// EntityManager is the main object in the persistence API, and is
// used to create, delete, and query objects, as well as access
// the current transaction
EntityManager em = factory.createEntityManager();
initFamilyTree(em);
runQueries(em);
// It is always good to clean up after ourselves
em.close();
factory.close();
}
/**
* Creates a partial family tree of the Greek dieties.
*
* @param em the EntityManager to use in the persistence process
*/
public static void initFamilyTree(EntityManager em) {
// First delete all the members from the database the clean up
em.getTransaction().begin();
em.createQuery("delete from Deity").executeUpdate();
em.getTransaction().rumit();
// Generation 1
Deity uranus = new Deity("Uranus", MALE);
Deity gaea = new Deity("Gaea", FEMALE);
// Generation 2
Deity cronus = gaea.giveBirth("Cronus", uranus, MALE);
Deity rhea = gaea.giveBirth("Rhea", uranus, FEMALE);
Deity coeus = gaea.giveBirth("Coeus", uranus, MALE);
Deity phoebe = gaea.giveBirth("Phoebe", uranus, FEMALE);
Deity oceanus = gaea.giveBirth("Oceanus", uranus, MALE);
Deity tethys = gaea.giveBirth("Tethys", uranus, FEMALE);
// Generation 3
Deity leto = phoebe.giveBirth("Leto", coeus, FEMALE);
Deity hestia = rhea.giveBirth("Hestia", cronus, FEMALE);
Deity pluto = rhea.giveBirth("Pluto", cronus, MALE);
Deity poseidon = rhea.giveBirth("Poseidon", cronus, MALE);
Deity zeus = rhea.giveBirth("Zeus", cronus, MALE);
Deity hera = rhea.giveBirth("Hera", cronus, FEMALE);
Deity demeter = rhea.giveBirth("Demeter", cronus, FEMALE);
// Generation 4
Deity iapetus = tethys.giveBirth("Iapetus", coeus, MALE);
Deity clymene = new Deity("Clymene", FEMALE);
Deity apollo = leto.giveBirth("Apollo", zeus, MALE);
Deity artemis = leto.giveBirth("Artemis", zeus, MALE);
Deity persephone = demeter.giveBirth("Persephone", zeus, MALE);
Deity ares = hera.giveBirth("Ares", zeus, MALE);
Deity hebe = hera.giveBirth("Hebe", zeus, FEMALE);
Deity hephaestus = hera.giveBirth("Hephaestus", zeus, MALE);
Deity prometheus = clymene.giveBirth("Prometheus", iapetus, MALE);
Deity atlas = clymene.giveBirth("Atlas", iapetus, MALE);
Deity epimetheus = clymene.giveBirth("Epimetheus", iapetus, FEMALE);
Deity dione = new Deity("Dione", FEMALE);
dione.giveBirth("Aphrodite", zeus, FEMALE);
// Begin a new local transaction so that we can persist a new entity
em.getTransaction().begin();
// note that we only need to explicitly persist a single root of the
// object graph (the family tree, in this case), since we have the
// "cascade" annotation on all the relations
em.persist(zeus);
// Commit the transaction, which will cause the entity to
// be stored in the database
em.getTransaction().rumit();
}
/**
* Run some sample queries against the family tree model.
*
* @param em the EntityManager to use
*/
public static void runQueries(EntityManager em) {
System.out.println("Running query to find all instances..");
// Perform a simple query for all the Deity entities
Query q = em.createQuery("select x from Deity x");
// Go through each of the entities and print out each of their
// messages, as well as the date on which it was created
for (Deity m : (List<Deity>) q.getResultList()) {
System.out.println(m.getName());
}
q = em.createQuery("select x from Deity x "
+ "where x.father.name = "Zeus"");
for (Deity m : (List<Deity>) q.getResultList()) {
System.out.println("Child of Zeus: " + m.getName());
}
q = em.createNamedQuery("siblings").
setParameter(1, em.getReference(Deity.class, "Rhea"));
for (Deity m : (List<Deity>) em.createNamedQuery("siblings").
setParameter(1, em.getReference(Deity.class, "Rhea")).
getResultList()) {
System.out.println("Siblings of Rhea: " + m.getName());
}
for (Deity m : (List<Deity>) em.createNamedQuery("half-siblings").
setParameter(1, em.getReference(Deity.class, "Apollo")).
getResultList()) {
System.out.println("Half-siblings of Apollo: " + m.getName());
}
for (Deity m : (List<Deity>) em.createNamedQuery("cousins").
setParameter(1, em.getReference(Deity.class, "Leto")).
getResultList()) {
System.out.println("Cousins of Leto: " + m.getName());
}
}
}
/*
* Licensed to the Apache Software Foundation (ASF) under one
* or more contributor license agreements. See the NOTICE file
* distributed with this work for additional information
* regarding copyright ownership. The ASF licenses this file
* to you under the Apache License, Version 2.0 (the
* "License"); you may not use this file except in compliance
* with the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing,
* software distributed under the License is distributed on an
* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
* KIND, either express or implied. See the License for the
* specific language governing permissions and limitations
* under the License.
*/
package relations;
import java.io.Serializable;
import java.util.HashSet;
import java.util.Set;
import javax.persistence.Basic;
import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.Enumerated;
import javax.persistence.EnumType;
import javax.persistence.Id;
import javax.persistence.NamedQuery;
import javax.persistence.NamedQueries;
import javax.persistence.OneToMany;
import javax.persistence.OneToOne;
/**
* An entity that contains relations corresponding to family tree relations.
* This entity demonstrates the following JPA features:
*
* 1. Enum fields (gender)
* 2. @OneToOne relations
* 3. @OneToMany relations
* 4. Named queries
*/
@Entity
@NamedQueries({
// a sibling shares a mother and a father
@NamedQuery(name="siblings", query="select distinct sibling1 "
+ "from Deity sibling1, Deity sibling2 where "
+ "sibling1.father = sibling2.father "
+ "and sibling1.mother = sibling2.mother "
+ "and sibling2 = ?1 and sibling1 <> ?1"),
// a half-siling shares a mother or a father, but not both
@NamedQuery(name="half-siblings", query="select distinct sibling1 "
+ "from Deity sibling1, Deity sibling2 where "
+ "((sibling1.father = sibling2.father "
+ "and sibling1.mother <> sibling2.mother) "
+ "or (sibling1.father <> sibling2.father "
+ "and sibling1.mother = sibling2.mother)) "
+ "and sibling2 = ?1 and sibling1 <> ?1"),
// a cousin shares a grandparent, but is not a sibling
@NamedQuery(name="cousins", query="select distinct cousin1 "
+ "from Deity cousin1, Deity cousin2 where "
+ "("
+ "cousin1.father.father = cousin2.father.father "
+ "or cousin1.father.mother = cousin2.father.mother "
+ "or cousin1.mother.father = cousin2.mother.father "
+ "or cousin1.mother.mother = cousin2.mother.mother) "
+ "and (cousin1.father <> cousin2.father) "
+ "and (cousin1.mother <> cousin2.mother) "
+ "and cousin2 = ?1 and cousin1 <> ?1")
})
public class Deity implements Serializable {
// the Id is the name, which is generally a bad idea, but we are
// confident that diety names will be unique
@Id
private String name;
@Basic @Enumerated(EnumType.STRING)
private Gender gender;
@OneToOne(cascade=CascadeType.ALL)
private Deity mother;
@OneToOne(cascade=CascadeType.ALL)
private Deity father;
@OneToMany(cascade=CascadeType.ALL)
private Set<Deity> children;
public static enum Gender { MALE, FEMALE }
public Deity(String name, Gender gender) {
this.name = name;
this.gender = gender;
}
//////////////////////////
// Business methods follow
//////////////////////////
/**
* She"s having a baby...
*
* @param childName the baby name
* @return the new child
*
* @throws IllegalArgumentException if the person is not a woman, or
* if the person is unmarried (illegitimate
* children are not yet supported)
*/
public Deity giveBirth(String childName, Deity childFather, Gender gender) {
if (this.gender != Gender.FEMALE)
throw new IllegalArgumentException("Only women can have children!");
if (childName == null)
throw new IllegalArgumentException("No child name!");
// create the child
Deity child = new Deity(childName, gender);
// set the parents in the children...
child.mother = this;
// add the child to this member"s children
if (children == null)
children = new HashSet<Deity>();
children.add(child);
if (childFather != null) {
child.father = childFather;
if (childFather.children == null)
childFather.children = new HashSet<Deity>();
childFather.children.add(child);
}
return child;
}
////////////////////////////////////
// Property accessor methods follow
////////////////////////////////////
public void setName(String name) {
this.name = name;
}
public String getName() {
return this.name;
}
public void setGender(Gender gender) {
this.gender = gender;
}
public Gender getGender() {
return this.gender;
}
public void setMother(Deity mother) {
this.mother = mother;
}
public Deity getMother() {
return this.mother;
}
public void setFather(Deity father) {
this.father = father;
}
public Deity getFather() {
return this.father;
}
public void setChildren(Set<Deity> children) {
this.children = children;
}
public Set<Deity> getChildren() {
return this.children;
}
}
