This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes Object Relational Mapping and the fly likes Mapping a Join Table with Additional Columns Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Mapping a Join Table with Additional Columns " Watch "Mapping a Join Table with Additional Columns " New topic
Author

Mapping a Join Table with Additional Columns

Abrahim Dabra
Greenhorn

Joined: Jul 22, 2009
Posts: 1
Hello,

I am trying to save a record (Join Table) in Database using JPA.
Scenario is as described below,
I have three tables employee, project and projemp.
Each employee may have multiple projects associated with it.
Each project may have multiple employee associated with it.
The association data is entered in projemp table.
projemp table has three column named as employeeId, ProjectId and isprojectlead.
where employeeId is refered from employee table, and projectId is refered from project table.

To accomplish it, I have created Entities for each of these tables and a service(TestEmpProjService.java) to store the values as described below.


*****************************************************************************
Employee.java
*****************************************************************************
package test;

import java.util.List;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

@Entity
@Table(name="employee")
public class Employee {
@Id
private int employeeid;

@OneToMany(mappedBy = "employee")
private List<ProjectAssociation> projects;



public int getEmployeeid() {
return employeeid;
}

public void setEmployeeid(int employeeid) {
this.employeeid = employeeid;
}

public List<ProjectAssociation> getProjects() {
return projects;
}

public void setProjects(List<ProjectAssociation> projects) {
this.projects = projects;
}


}

*****************************************************************************
Project.java
*****************************************************************************
package test;

import java.util.List;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

@Entity
@Table(name="project")
public class Project {
@Id
private int projectid;

@OneToMany(mappedBy = "project")
private List<ProjectAssociation> employees;

// Add an employee to the project.
// Create an association object for the relationship and set its' data.
public void addEmployee(Employee employee, String teamLead) {
ProjectAssociation association = new ProjectAssociation();
association.setEmployee(employee);
association.setProject(this);
association.setEmployee_employeeid(employee.getEmployeeid());
association.setProject_projectid(this.getProjectid());
association.setIsprojectlead(teamLead);

employees.add(association);
}



public int getProjectid() {
return projectid;
}



public void setProjectid(int projectid) {
this.projectid = projectid;
}



public List<ProjectAssociation> getEmployees() {
return employees;
}

public void setEmployees(List<ProjectAssociation> employees) {
this.employees = employees;
}

}

*******************************************************************************
ProjectAssociation.java
*******************************************************************************
package com.omgeo.ctmconformance.dashboard.entities;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.IdClass;
import javax.persistence.ManyToOne;
import javax.persistence.PrimaryKeyJoinColumn;
import javax.persistence.Table;

@Entity
@Table(name="projemp")
@IdClass(ProjectAssociationId.class)
public class ProjectAssociation {
@Id
private int employee_employeeid;
@Id
private int project_projectid;
@Column
private String isprojectlead;
@ManyToOne
@PrimaryKeyJoinColumn(name="employee_employeeid", referencedColumnName="employeeid")
private Employee employee;

@ManyToOne
@PrimaryKeyJoinColumn(name="project_projectid", referencedColumnName="projectid")
private Project project;


public int getEmployee_employeeid() {
return employee_employeeid;
}
public void setEmployee_employeeid(int employee_employeeid) {
this.employee_employeeid = employee_employeeid;
}
public int getProject_projectid() {
return project_projectid;
}
public void setProject_projectid(int project_projectid) {
this.project_projectid = project_projectid;
}
public String getIsprojectlead() {
return isprojectlead;
}
public void setIsprojectlead(String isprojectlead) {
this.isprojectlead = isprojectlead;
}
public Employee getEmployee() {
return employee;
}
public void setEmployee(Employee employee) {
this.employee = employee;
}
public Project getProject() {
return project;
}
public void setProject(Project project) {
this.project = project;
}

}

******************************************************************************
ProjectAssociationId.java
******************************************************************************
package test;

import java.io.Serializable;

import javax.persistence.Id;



public class ProjectAssociationId implements Serializable{
@Id
private int employee_employeeid;
@Id
private int project_projectid;

public int getEmployee_employeeid() {
return employee_employeeid;
}
public void setEmployee_employeeid(int employee_employeeid) {
this.employee_employeeid = employee_employeeid;
}
public int getProject_projectid() {
return project_projectid;
}
public void setProject_projectid(int project_projectid) {
this.project_projectid = project_projectid;
}
}

*******************************************************************************
TestEmpProjService.java
*******************************************************************************
package test;

import java.util.ArrayList;

import javax.ejb.Local;
import javax.ejb.Stateless;
import javax.ejb.TransactionAttribute;
import javax.ejb.TransactionAttributeType;
import javax.ejb.TransactionManagement;
import javax.ejb.TransactionManagementType;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.jboss.annotation.ejb.LocalBinding;

import test.Employee;
import test.Project;
import test.ProjectAssociation;
import test.util.LogWriter;



@Stateless(mappedName = "TestEmpProjService")
@Local( { TestEmpProjServiceIF.class })
@LocalBinding(jndiBinding = "TestEmpProjService")
@TransactionManagement(TransactionManagementType.CONTAINER)
@TransactionAttribute(TransactionAttributeType.REQUIRED)
public class TestEmpProjService implements
TestEmpProjServiceIF {

/**
* Create an EntityManager to use with our Entity Beans
*/
@PersistenceContext(unitName = "conformancetestdashboardDB")
protected EntityManager em;

/**
* Default constructor.
*/

public TestEmpProjService() {
Context initCtx;
try {
initCtx = new InitialContext();
em = (javax.persistence.EntityManager) initCtx
.lookup("java:/ConformancetestdashboardDB");

} catch (NamingException e) {
LogWriter.error(e);
}
}


public void addEmployeeProject()
{
Employee emp = new Employee();
emp.setEmployeeid(2);
em.persist(emp);

Project proj = new Project();
proj.setEmployees(new ArrayList<ProjectAssociation>());
proj.setProjectid(2);
proj.addEmployee(emp, "Test");
em.persist(proj);
//em.flush();


ProjectAssociation projAssociation = new ProjectAssociation();
projAssociation.setEmployee(emp);
projAssociation.setEmployee_employeeid(emp.getEmployeeid());
projAssociation.setProject(proj);
projAssociation.setProject_projectid(proj.getProjectid());
projAssociation.setIsprojectlead("Test");
em.persist(projAssociation);


em.flush();
}


}

******************************************************************************

When I run it, I get error "<b>could not bind value '2' to parameter: 4; Parameter index out of bounds. 4 is not between valid values of 1 and 3</b>".
Can anyone tell me, whats wrong over here? Am I missing anything ?

Thanks,
Abra
Doug MacKenzie
Greenhorn

Joined: Feb 18, 2009
Posts: 13
Try doing a search for bi-directional association mapping there are many examples. But basically all you need is a mapping for employee and one for project. Hibernate will populate the join table on it's own



Then do the same for project.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Mapping a Join Table with Additional Columns
 
Similar Threads
Problem in composite key mapping in eclipselink
Exception "javax.persistence.EntityNotFoundException: Unable to find ParkingSpace with 3
Whats wrong with the code ?
EJB3.0 example
Problem at insert with relationship JPA 2.0