Tuesday, May 1, 2012

Many-to-Many table relation with Join table and extra column implementation with JPA

The many to many relation in the domain modelling can be done with the extra class that has association with the many to many class. The domain modelling follow the same as in the relational model. Here is an example.

For example we have two table called "Employee" and "Project". Each Employee can work on one to many Projects and one Project can have one to many Employee. Here is the database tables

EMPLOYEE
- EMPLOYEE_ID
- EMPLOYEE_NAME
- EMPLOYEE_TYPE

PROJECT
- PROJECT_ID
- PROJECT_TITLE
- PROJECT_DURATION

EMPLOYEE_PROJECT (Join Table)
- EMPLOYEE_ID
- PROJECT_ID
- DATE-ADDED (Extra field)

Domain Modelling

Project.java
 public class Project implements Serializable{  
      private static final long serialVersionUID = 2703915705484865029L;  
      private String projectId;  
      private String projectTitle;  
      private String projectDuration;  
      private List<ProjectEmployee> employees;  
     //getters and setters  
      public void addEmployee(Employee employee, Date dateAdded) {  
           ProjectEmployee association = new ProjectEmployee();  
           association.setEmployee(employee);  
           association.setProject(this);  
           association.setEmployeeId(employee.getEmployeeId());  
           association.setProjectId(this.getProjectId());  
           association.setDateAdded(dateAdded);  
           this.employees.add(association);  
           employee.getProjects().add(association);  
      }  
 }  

Employee.java
 public class Employee implements Serializable{  
      private static final long serialVersionUID = -8821659676348395539L;  
      private String employeeId;  
      private String employeeName;  
      private String employeeType;  
      private List<ProjectEmployee> projects;  
 }  

ProjectEmployee.java
 public class ProjectEmployee implements Serializable{  
      private static final long serialVersionUID = -2691690907714316448L;  
      private String employeeId;  
      private String projectId;  
      private Date dateAdded;  
      private Employee employee;  
      private Project project;  
      //getters and setters  
 }  

And ProjectEmployeeId.java
 public class ProjectEmployeeId implements Serializable{  
      private static final long serialVersionUID = 3284051729103828873L;  
      private String employeeId;  
      private String projectId;  
      public String getEmployeeId() {  
           return employeeId;  
      }  
      public void setEmployeeId(String employeeId) {  
           this.employeeId = employeeId;  
      }  
      public String getProjectId() {  
           return projectId;  
      }  
      public void setProjectId(String projectId) {  
           this.projectId = projectId;  
      }  
      public int hashCode() {  
           return (int) (employeeId.hashCode() + projectId.hashCode());  
      }  
      public boolean equals(Object object) {  
           if (object instanceof ProjectEmployeeId) {  
                ProjectEmployeeId otherId = (ProjectEmployeeId) object;  
                return (otherId.getEmployeeId() == this.getEmployeeId())  
                          && (otherId.getProjectId() == this.getProjectId());  
           }  
           return false;  
      }  
 }  

The persistence-mapping.xml file looks like:
 <?xml version="1.0" encoding="UTF-8" ?>  
 <entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm"  
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
      xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm   
                              http://java.sun.com/xml/ns/persistence/orm_2_0.xsd"  
      version="2.0">  
      <entity class="Employee"  
           access="FIELD">  
           <table name="EMPLOYEE" />  
           <attributes>  
                <id name="employeeId">  
                     <column name="EMPLOYEE_ID" />  
                </id>  
                <basic name="employeeName">  
                     <column name="EMPLOYEE_NAME" />  
                </basic>  
                <basic name="employeeType">  
                     <column name="EMPLOYEE_TYPE" />  
                </basic>  
                <one-to-many name="projects" fetch="LAZY" mapped-by="employee">  
                     <cascade>  
                          <cascade-all />  
                     </cascade>  
                </one-to-many>  
           </attributes>  
      </entity>  
      <entity class="Project"  
           access="FIELD">  
           <table name="PROJECT" />  
           <attributes>  
                <id name="projectId">  
                     <column name="PROJECT_ID" />  
                </id>  
                <basic name="projectTitle">  
                     <column name="PROJECT_TITLE" />  
                </basic>  
                <basic name="projectDuration">  
                     <column name="PROJECT_DURATION" />  
                </basic>  
                <one-to-many name="employees" fetch="LAZY" mapped-by="project">  
                     <cascade>  
                          <cascade-all />  
                     </cascade>  
                </one-to-many>  
           </attributes>  
      </entity>  
      <entity class="ProjectEmployee">  
           <table name="EMPLOYEE_PROJECT" />  
           <id-class class="ProjectEmployeeId"/>  
           <attributes>  
                <id name="employeeId">  
                     <column name="EMPLOYEE_ID"/>  
                </id>  
                <id name="projectId">  
                     <column name="PROJECT_ID"/>  
                </id>  
                <basic name="dateAdded">  
                     <column name="DATE_ADDED" />  
                </basic>  
                <many-to-one name="employee" id="true">  
                     <join-column name="EMPLOYEE_ID" updatable="false" insertable="false"/>  
                </many-to-one>  
                <many-to-one name="project" id="true">  
                     <join-column name="PROJECT_ID" updatable="false" insertable="false"/>  
                </many-to-one>  
           </attributes>  
      </entity>  
 </entity-mappings>  

Now Create Persistence.xml file and add these:
 <?xml version="1.0" encoding="UTF-8" ?>  
 <persistence version="2.0"  
    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">  
   <persistence-unit name="example" transaction-type="RESOURCE_LOCAL">  
     <mapping-file>META-INF/persistence-mapping.xml</mapping-file>  
     <mapping-file>META-INF/persistence-query.xml</mapping-file>  
     <class>Employee</class>  
     <class>Project</class>  
     <class>ProjectEmployee</class>  
     <class>ProjectEmployeeId</class>  
     <properties>  
          <property name="cache.provider_class" value="org.hibernate.cache.NoCacheProvider"/>  
       <property name="hibernate.max_fetch_depth" value="3"/>  
       <property name="hibernate.query.factory_class" value="org.hibernate.hql.classic.ClassicQueryTranslatorFactory"/>  
       <property name="hibernate.query.substitutions" value="true 1, false 0"/>  
       <property name="hibernate.show_sql" value="true"/>  
       <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle9iDialect"/>  
       <property name="hibernate.hbm2ddl.auto" value="validate"/>  
     </properties>  
   </persistence-unit>  
 </persistence>  

And persistence-query.xml as :
 <?xml version="1.0" encoding="UTF-8" ?>  
 <entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm"  
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
     xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm  
                              http://java.sun.com/xml/ns/persistence/orm_2_0.xsd"  
     version="2.0">  
   <named-query name="project.findAll">  
        <query>  
             from Project p  
        </query>  
   </named-query>  
   <named-query name="employee.findAll">  
        <query>  
             from Employee e;  
        </query>  
   </named-query>  
   <named-query name="employee.findByProject">  
        <query>  
             select e from Employee e JOIN e.projects p where p.project = ?  
        </query>  
   </named-query>  
 </entity-mappings>  

Finally Create Test Case as follow:
 @RunWith(SpringJUnit4ClassRunner.class)  
 @ContextConfiguration(locations = { "classpath:applicationContext/applicationContext-*.xml" })  
 @TransactionConfiguration(transactionManager = "transactionManager", defaultRollback = false)  
 public class ProjectDaoTest {  
      @Autowired  
      private ProjectDao projectDao;  
      @Autowired  
      private EmployeeDao employeeDao;  
      @Before  
      public void before() {  
      }  
      @After  
      public void after() {  
      }  
      @Test  
      @Transactional  
      public void testProjectAvailability() throws Exception{  
           Project p = projectDao.findByUuid("BEE9F3A7979F505EE040007F01001B21");  
           Employee e = new Employee();  
           e.setEmployeeId(UUIDGenerator.getUUID());  
           e.setEmployeeName("Peter");  
           e.setEmployeeType("Contractor");  
           employeeDao.persistChanges(e);  
           Employee e = employeeDao.findByUuid("785B9FDDD8DB56B14F13359027636041");  
           p.addEmployee(e, new Date());  
      }  
      @Test  
      public void testGetAllEmployeeFromProject() {  
           Project p = projectDao.findByUuid("BEE9F3A7979F505EE040007F01001B21");  
           List<Employee> employees = employeeDao.findByNamedQueryAndParams("employee.findByProject", p);  
           Assert.assertNotNull(employees);  
      }  
 }  

That's All. Enjoy Programming..................