How to do a many-to-many relationship in spring Ro

Question

i have been researching on this topic and havent found any answers yet. Im using spring roo and i would like to know if theres a way I can establish a many-to-many relationship with attributes within this relationship. For example i have two tables Employee and MedicalEquipment, Employees can reserve many equipments, and equipment could be reserved by many employee, however i want to store the date this reserve took place.

If some one can help i would appreciate it. Thanks in advance!

Answer 1

Why can't you have a separate entity to denote your relationship?

Just introduce a new entity called a MedicalEquipmentReservation which would contain all the attributes of the reservation along with the relationships between the Employee and the Medical Equipment entities.

See the following example.

class MedicalEquipmentReservation{  
    //attributes  
    Date reservationStartDate;    
    Date reservationEndDate; 

    //relationships
    Employee employee; 
    Set<Equipment> equipments; //assuming more than one equipment can be borrowed at one reservation
}

Cheers and all the best with Spring Roo!

Answer 2

• In order to achieve a many-to-many relationship with attributes, you need a join table which contains the additional columns. That is, besides Employee and MedicalEquipment, you will need a third EmployeeMedicalEquipment.

• Regarding JPA you have two options:

  1. Mapping the join table to an intermediate entity
  2. Mapping the join table to a collection of components

The former is more complicated, but it allowed you to have bidirectional navigation (because it's an entity and hence, can have shared references), the latter is simpler in both building it and using it, but you can't use it to navigate between entities (however, you can write a query to retrieve the objects you need)

• Roo is a code generator, but it lacks of all the options that JPA offers, so you have to edit the Java classes, and the test classes. The building of the view has some limitations too, so you will need to edit it as well.

In my case, I needed to create an intermediary entity because the table belongs to a legacy database that already existed.

I did something like this:

entity --class ~.domain.Employee --table T_Employee [etc...]
field [etc...]

entity --class ~.domain.MedicalEquipment --table T_MedicalEquipment [etc...]
field [etc...]

entity --class ~.domain.EmployeeMedicalEquipment --table T_Employee_MedicalEquipment --identifierType ~.domain.EmployeeMedicalEquipmentId
//to store the date this reserve took place.
field date --fieldName reserveDate --column C_reserveDate [etc...]

//Bidirectional: you´ll need @JoinColumn insertable = false and updatable = false
field reference --fieldName employee --type ~.domain.Employee --cardinality MANY_TO_ONE 

//Bidirectional: you'll need @JoinColumn insertable = false and updatable = false
field reference --fieldName medicalEquipment --type ~.MedicalEquipment --cardinality MANY_TO_ONE 

//Join table's composite primary key
field string --fieldName employeeId --column employee_ID --class ~.domain.EmployeeMedicalEquipmentId [etc...]
field string --fieldName medicalEquipmentId --column medicalEquipment_ID  --class ~.domain.EmployeeMedicalEquipmentId [etc...]

//Now, it's time to complete the relationship:
focus --class ~.domain.Employee
field set --type ~.domain.EmployeeMedicalEquipment --fieldName medicalEquipments --cardinality ONE_TO_MANY --mappedBy employee
focus --class ~.domain.MedicalEquipment
field set --type ~.domain.EmployeeMedicalEquipment --fieldName employees --cardinality ONE_TO_MANY --mappedBy medicalEquipment

Furthermore, you need to guarantees referential integrity by managing collections on either side of the association using the constructor. So you need to edit the class in such way:

@RooEntity(...
public class EmployeeMedicalEquipment {

@ManyToOne
@JoinColumn(name = "employeeId", referencedColumnName = "employeeId", insertable = false, updatable = false)
private Employee employee;

@ManyToOne
@JoinColumn(name="medicalEquipmentId", referencedColumnName="medicalEquipmentId", insertable=false, updatable=false)
private MedicalEquipment medicalEquipment;

/**
 * No-arg constructor for JavaBean tools
 */
public EmployeeMedicalEquipment() {
}


/**
 * Full constructor, the Employee and MedicalEquipment instances have to have an identifier value, they have to be in detached or persistent state.
 * This constructor takes care of the bidirectional relationship by adding the new instance to the collections on either side of the
 * many-to-many association (added to the collections)
 */
public EmployeeMedicalEquipment(Employee employee, MedicalEquipment medicalEquipment, Date reserveDate) {
    this.setReserveDate (reserveDate);

    this.employee = employee;
    this.medicalEquipment = medicalEquipment;

    this.setId(new EmployeeMedicalEquipmentId(employee.getId(),  medicalEquipment.getId());

    // If Employee or MedicalEquipment  Guarantee referential integrity
    employee.getMedicalEquipments().add(this);
    medicalEquipment.getEmployees().add(this);
}
...

}

I tried to give you an example of a Roo configuration.

You can find a better explanation of the JPA stuff in the book from Manning "Java Persistence with Hibernate", chapter 7.2.3.

Note: if you use roo 1.2.1, the count query will generate SQL with "count(id1, id2)", which is not supported by all databases, including HSQLDB. You can customize it like this:

...

-@RooJpaActiveRecord(identifierType = EmployeeMedicalEquipmentId.class, table = "T_Employee_MedicalEquipment")
+@RooJpaActiveRecord(identifierType = EmployeeMedicalEquipmentId.class, table = "T_Employee_MedicalEquipment", countMethod="")
...
public class EmployeeMedicalEquipment {
...
    // count method initially copied from ActiveRecord aspect
    public static long countEmployeeMedicalEquipments() {
-       return entityManager().createQuery("SELECT COUNT(o) FROM EmployeeMedicalEquipment o", Long.class).getSingleResult();
+       return entityManager().createQuery("SELECT COUNT(o.employee) FROM EmployeeMedicalEquipment o", Long.class).getSingleResult();
        }
    }