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;
    }
}