Java/JPA/Self Join
Join Member Entities
<source lang="java">
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>
</source>
Relations on one entity
<source lang="java">
/*
* 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; }
}
</source>