ManyToMany JPA fetch

Question

Hi I have a problem fetching the skills from the volunteer. for some reason i dont get the list when using this method

public Volunteer getVolunteer(int id){

 Volunteer vo;

 Query q;

 q = em.createNamedQuery("Volunteer.findById").setParameter("id", id);
 vo = (Volunteer) q.getSingleResult();

 for(Skill s: vo.getSkills()){
  System.out.println(s);
 }

 return vo;

}

the list is empty so the fetching does not seem to work.

Im using JPA Eclipselink and Glassfish

Any help appreciated!

The skill entity:

@Entity
@Table(name="skill")
@NamedQueries({
    @NamedQuery(name = "Skill.findAll", query = "SELECT s FROM Skill s"),
    @NamedQuery(name = "Skill.findById", query = "SELECT s FROM Skill s WHERE s.id = :id"),
    @NamedQuery(name = "Skill.findBySkillDescription", query = "SELECT s FROM Skill s WHERE s.skillDescription = :skillDescription")})
public class Skill implements Serializable {
 @Override
 public String toString() {
  return "Skill [id=" + id + ", skillDescription=" + skillDescription
    + "]";
 }

 private static final long serialVersionUID = 1L;

 @Id
 @GeneratedValue(strategy=GenerationType.IDENTITY)
 @Column(unique=true, nullable=false)
 private int id;

 @Column(name="skill_description", length=130)
 private String skillDescription;

 //bi-directional many-to-many association to Volunteer
    @ManyToMany(cascade=CascadeType.ALL,fetch=FetchType.EAGER)
 @JoinTable(
  name="skill_volunteer"
  , joinColumns={
   @JoinColumn(name="skill_id", nullable=false)
   }
  , inverseJoinColumns={
   @JoinColumn(name="volunteer_id", nullable=false)
   }
  )
 private List<Volunteer> volunteers;

    public Skill() {
    }

 public int getId() {
  return this.id;
 }

 public void setId(int id) {
  this.id = id;
 }

 public String getSkillDescription() {
  return this.skillDescription;
 }

 public void setSkillDescription(String skillDescription) {
  this.skillDescription = skillDescription;
 }

 public List<Volunteer> getVolunteers() {
  return this.volunteers;
 }

 public void setVolunteers(List<Volunteer> volunteers) {
  this.volunteers = volunteers;
 }

}

And the volunteer entity:

@Entity
@Table(name="volunteer")
@NamedQueries({
    @NamedQuery(name = "Volunteer.findAll", query = "SELECT v FROM Volunteer v"),
    @NamedQuery(name = "Volunteer.findById", query = "SELECT v FROM Volunteer v WHERE v.id = :id"),
    @NamedQuery(name = "Volunteer.findByPhone", query = "SELECT v FROM Volunteer v WHERE v.phone = :phone"),
    @NamedQuery(name = "Volunteer.findByEmail", query = "SELECT v FROM Volunteer v WHERE v.email = :email"),
    @NamedQuery(name = "Volunteer.findByFirstName", query = "SELECT v FROM Volunteer v WHERE v.firstName = :firstName"),
    @NamedQuery(name = "Volunteer.findByLastName", query = "SELECT v FROM Volunteer v WHERE v.lastName = :lastName")})
public class Volunteer implements Serializable {
 @Override
 public String toString() {
  return "Volunteer [id=" + id + ", email=" + email + ", firstName="
    + firstName + ", lastName=" + lastName + ", phone=" + phone
    + "]";
 }

 private static final long serialVersionUID = 1L;

 @Id
 @GeneratedValue(strategy=GenerationType.IDENTITY)
 @Column(unique=true, nullable=false)
 private int id;

 @Column(length=255)
 private String email;

 @Column(name="first_name", length=255)
 private String firstName;

 @Column(name="last_name", length=255)
 private String lastName;

 @Column(length=255)
 private String phone;

 //bi-directional many-to-many association to Event
 @ManyToMany(mappedBy="volunteers")
 private List<Event> events;

 //bi-directional many-to-many association to Skill
 @ManyToMany(mappedBy="volunteers", cascade=CascadeType.ALL, fetch=FetchType.EAGER)
 private List<Skill> skills;

    public Volunteer() {
    }

 public int getId() {
  return this.id;
 }

 public void setId(int id) {
  this.id = id;
 }

 public String getEmail() {
  return this.email;
 }

 public void setEmail(String email) {
  this.email = email;
 }

 public String getFirstName() {
  return this.firstName;
 }

 public void setFirstName(String firstName) {
  this.firstName = firstName;
 }

 public String getLastName() {
  return this.lastName;
 }

 public void setLastName(String lastName) {
  this.lastName = lastName;
 }

 public String getPhone() {
  return this.phone;
 }

 public void setPhone(String phone) {
  this.phone = phone;
 }

 public List<Event> getEvents() {
  return this.events;
 }

 public void setEvents(List<Event> events) {
  this.events = events;
 }

 public List<Skill> getSkills() {
  return this.skills;
 }

 public void setSkills(List<Skill> skills) {
  this.skills = skills;
 }

}

Answer 1

the list is empty so the fetching does not seem to work.

Getting an empty list doesn't allow to conclude that "fetching" doesn't work and whether you use a LAZY or an EAGER fetch type, you should get skills records for a given volunteer if there are any.

And because the mapping looks correct, I would start by looking at the generated SQL and run it against the database to confirm

1. that the generated SQL is the expected result
2. that the data are correct.

To do so, set the following properties in the persistence.xml:

  <property name="eclipselink.logging.level" value="FINEST" />
  <property name="eclipselink.logging.level.sql" value="FINEST" />

Then find the SQL queries for the findById and the getSkills and run them against the database.

And please update the question with the obtained result for a given id (both the generated SQL and the confirmation that data are ok).