Hello,

I'm a student at the "Hogeschool Arnhem en Nijmegen" in the Netherlands and I have to make a project for my school course. We have to make (part of) a project for a a real company, however I have a problem making an EJB join work in the EntityQuery object.

I'm working with an legacy database that needs to be kept that way. I generated the forms and entities using seam-gen. I already changed some of the forms, but I can't find anywhere how to use a join query in an EntityQuery object.

What I want is to display a list of the username of the user with the name of the organization(orgLevel). There is an orgId in the user entity that refers to the organization(orgLevel). However there is no foreign key reference. I don't know if that's a problem. I am using jsf as the frontend, however i'm not that familiar with jsf.

Furthermore I'm using seam 1.1.0.CR2 and JBoss 4.0.5.

Could you give me an example of how to make this? This would be really helpful.

I've included the files I'm using for this query below.

The User Entity:

package org.agilit.ipplus;
// Generated 4-dec-2006 17:21:37 by Hibernate Tools 3.2.0.snapshotb9

import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.UniqueConstraint;
import javax.persistence.*;
import org.hibernate.validator.Length;
import org.hibernate.validator.NotNull;

/**
 * User generated by hbm2java
 */
@Entity
@Table(name = "user", catalog = "agilit", uniqueConstraints = {@UniqueConstraint(columnNames = "id")})
public class User implements java.io.Serializable {

 // Fields

 private String username;
 private int id;
 private String password;
 private String language;
 private OrgLevel orglevel;
 private int orgId;
 private String roles;
 private int medewerkerId;
 private char loggedIn;
 private String sessionId;
 private Date lastRequest;
 private String application;
 private String email;
 @OneToOne(cascade={CascadeType.ALL}, fetch=FetchType.LAZY)
 @JoinColumn(name="orgId")
 private OrgLevel orgLevel;
 // Constructors

 /** default constructor */
 public User() {
 }

 /** minimal constructor */
 public User(String username, int id, String password, String language,
 int orgId, int medewerkerId, char loggedIn, Date lastRequest) {
 this.username = username;
 this.id = id;
 this.password = password;
 this.language = language;
 this.orgId = orgId;
 this.medewerkerId = medewerkerId;
 this.loggedIn = loggedIn;
 this.lastRequest = lastRequest;
 }
 /** full constructor */
 public User(String username, int id, String password, String language,
 int orgId, String roles, int medewerkerId, char loggedIn,
 String sessionId, Date lastRequest, String application, String email) {
 this.username = username;
 this.id = id;
 this.password = password;
 this.language = language;
 this.orgId = orgId;
 this.roles = roles;
 this.medewerkerId = medewerkerId;
 this.loggedIn = loggedIn;
 this.sessionId = sessionId;
 this.lastRequest = lastRequest;
 this.application = application;
 this.email = email;
 }

 // Property accessors
 @Id
 @Column(name = "username", unique = true, nullable = false, length = 12)
 @NotNull
 @Length(max = 12)
 public String getUsername() {
 return this.username;
 }

 public void setUsername(String username) {
 this.username = username;
 }

 @Column(name = "id", unique = true, nullable = false)
 @NotNull
 public int getId() {
 return this.id;
 }

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

 @Column(name = "password", nullable = false, length = 32)
 @NotNull
 @Length(max = 32)
 public String getPassword() {
 return this.password;
 }


 public void setPassword(String password) {
 this.password = password;
 }

 @Column(name = "email", nullable = true, length = 60)
 @Length(max = 60)
 public String getEmail() {
 return this.email;
 }

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

 @Column(name = "language", nullable = false, length = 8)
 @NotNull
 @Length(max = 8)
 public String getLanguage() {
 return this.language;
 }

 public void setLanguage(String language) {
 this.language = language;
 }

 @Column(name = "org_id", nullable = false)
 @NotNull
 public int getOrgId() {
 return this.orgId;
 }

 public void setOrgId(int orgId) {
 this.orgId = orgId;
 }

 public OrgLevel getOrgLevel() {
 return orgLevel;
 }
 public void setOrgLevel (OrgLevel orgLevel) {
 this.orgLevel = orgLevel;
 }
 @Column(name = "roles", length = 20)
 @Length(max = 20)
 public String getRoles() {
 return this.roles;
 }

 public void setRoles(String roles) {
 this.roles = roles;
 }

 @Column(name = "medewerker_id", nullable = false)
 @NotNull
 public int getMedewerkerId() {
 return this.medewerkerId;
 }

 public void setMedewerkerId(int medewerkerId) {
 this.medewerkerId = medewerkerId;
 }

 @Column(name = "logged_in", nullable = false, length = 1)
 @NotNull
 @Length(max = 1)
 public char getLoggedIn() {
 return this.loggedIn;
 }

 public void setLoggedIn(char loggedIn) {
 this.loggedIn = loggedIn;
 }

 @Column(name = "session_id")
 public String getSessionId() {
 return this.sessionId;
 }

 public void setSessionId(String sessionId) {
 this.sessionId = sessionId;
 }

 @Column(name = "last_request", nullable = false, length = 19)
 @NotNull
 public Date getLastRequest() {
 return this.lastRequest;
 }

 public void setLastRequest(Date lastRequest) {
 this.lastRequest = lastRequest;
 }

 @Column(name = "application")
 public String getApplication() {
 return this.application;
 }

 public void setApplication(String application) {
 this.application = application;
 }

 public String toString() {
 return "User id: " + getId() + " username: " + getUsername() +
 " with " + getOrgLevel();
 }
}

package org.agilit.ipplus;

import org.jboss.seam.annotations.Name;
import org.jboss.seam.annotations.RequestParameter;
import org.jboss.seam.framework.EntityQuery;

@Name("userList")
public class UserList extends EntityQuery {
 @Override
 public String getEjbql() {
 return "select user from User user";
 }

 @RequestParameter
 @Override
 public void setFirstResult(Integer firstResult) {
 super.setFirstResult(firstResult);
 }

 @Override
 public Integer getMaxResults() {
 return 25;
 }
}

package org.agilit.ipplus;
// Generated 4-dec-2006 17:21:37 by Hibernate Tools 3.2.0.snapshotb9

import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import org.hibernate.validator.Length;
import org.hibernate.validator.NotNull;

/**
 * OrgLevel generated by hbm2java
 */
@Entity
@Table(name = "org_level", catalog = "agilit", uniqueConstraints = {})
public class OrgLevel implements java.io.Serializable {

 // Fields

 private int id;
 private String address;
 private String name;
 private String level;
 private String modules;
 private String city;
 private int parentId;
 private String ide;
 private Integer protocolOrgId;
 private String zipcode;
 private String phone;
 private String fax;
 private String email;
 private String arboNaam;
 private String arboKlantnummer;
 private String arboBranche;
 private String arboAansluitnummer;
 private Character risicodragerPemba;
 private Integer arboartsId;
 private Date workfromMo;
 private Date workuntilMo;
 private Date breakMo;
 private Date workfromTu;
 private Date workuntilTu;
 private Date breakTu;
 private Date workfromWe;
 private Date workuntilWe;
 private Date breakWe;
 private Date workfromTh;
 private Date workuntilTh;
 private Date breakTh;
 private Date workfromFr;
 private Date workuntilFr;
 private Date breakFr;
 private Date workfromSa;
 private Date workuntilSa;
 private Date breakSa;
 private Date workfromSu;
 private Date workuntilSu;
 private Date breakSu;
 private String monday;
 private String tuesday;
 private String wednesday;
 private String thursday;
 private String friday;
 private String saturday;
 private String sunday;

 // Constructors

 /** default constructor */
 public OrgLevel() {
 }

 /** minimal constructor */
 public OrgLevel(int id, String address, String name, String city,
 int parentId, String zipcode, String phone, String fax,
 String arboNaam, String arboKlantnummer, String arboBranche,
 String arboAansluitnummer) {
 this.id = id;
 this.address = address;
 this.name = name;
 this.city = city;
 this.parentId = parentId;
 this.zipcode = zipcode;
 this.phone = phone;
 this.fax = fax;
 this.arboNaam = arboNaam;
 this.arboKlantnummer = arboKlantnummer;
 this.arboBranche = arboBranche;
 this.arboAansluitnummer = arboAansluitnummer;
 }
 /** full constructor */
 public OrgLevel(int id, String address, String name, String level,
 String modules, String city, int parentId, String ide,
 Integer protocolOrgId, String zipcode, String phone, String fax,
 String email, String arboNaam, String arboKlantnummer,
 String arboBranche, String arboAansluitnummer,
 Character risicodragerPemba, Integer arboartsId, Date workfromMo,
 Date workuntilMo, Date breakMo, Date workfromTu, Date workuntilTu,
 Date breakTu, Date workfromWe, Date workuntilWe, Date breakWe,
 Date workfromTh, Date workuntilTh, Date breakTh, Date workfromFr,
 Date workuntilFr, Date breakFr, Date workfromSa, Date workuntilSa,
 Date breakSa, Date workfromSu, Date workuntilSu, Date breakSu,
 String monday, String tuesday, String wednesday, String thursday,
 String friday, String saturday, String sunday) {
 this.id = id;
 this.address = address;
 this.name = name;
 this.level = level;
 this.modules = modules;
 this.city = city;
 this.parentId = parentId;
 this.ide = ide;
 this.protocolOrgId = protocolOrgId;
 this.zipcode = zipcode;
 this.phone = phone;
 this.fax = fax;
 this.email = email;
 this.arboNaam = arboNaam;
 this.arboKlantnummer = arboKlantnummer;
 this.arboBranche = arboBranche;
 this.arboAansluitnummer = arboAansluitnummer;
 this.risicodragerPemba = risicodragerPemba;
 this.arboartsId = arboartsId;
 this.workfromMo = workfromMo;
 this.workuntilMo = workuntilMo;
 this.breakMo = breakMo;
 this.workfromTu = workfromTu;
 this.workuntilTu = workuntilTu;
 this.breakTu = breakTu;
 this.workfromWe = workfromWe;
 this.workuntilWe = workuntilWe;
 this.breakWe = breakWe;
 this.workfromTh = workfromTh;
 this.workuntilTh = workuntilTh;
 this.breakTh = breakTh;
 this.workfromFr = workfromFr;
 this.workuntilFr = workuntilFr;
 this.breakFr = breakFr;
 this.workfromSa = workfromSa;
 this.workuntilSa = workuntilSa;
 this.breakSa = breakSa;
 this.workfromSu = workfromSu;
 this.workuntilSu = workuntilSu;
 this.breakSu = breakSu;
 this.monday = monday;
 this.tuesday = tuesday;
 this.wednesday = wednesday;
 this.thursday = thursday;
 this.friday = friday;
 this.saturday = saturday;
 this.sunday = sunday;
 }

 // Property accessors
 @Id
 @Column(name = "id", unique = true, nullable = false)
 @NotNull
 public int getId() {
 return this.id;
 }

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

 @Column(name = "address", nullable = false)
 @NotNull
 public String getAddress() {
 return this.address;
 }

 public void setAddress(String address) {
 this.address = address;
 }

 @Column(name = "name", nullable = false)
 @NotNull
 public String getName() {
 return this.name;
 }

 public void setName(String name) {
 this.name = name;
 }

 @Column(name = "level")
 public String getLevel() {
 return this.level;
 }

 public void setLevel(String level) {
 this.level = level;
 }

 @Column(name = "modules")
 public String getModules() {
 return this.modules;
 }

 public void setModules(String modules) {
 this.modules = modules;
 }

 @Column(name = "city", nullable = false)
 @NotNull
 public String getCity() {
 return this.city;
 }

 public void setCity(String city) {
 this.city = city;
 }

 @Column(name = "parent_id", nullable = false)
 @NotNull
 public int getParentId() {
 return this.parentId;
 }

 public void setParentId(int parentId) {
 this.parentId = parentId;
 }

 @Column(name = "ide")
 public String getIde() {
 return this.ide;
 }

 public void setIde(String ide) {
 this.ide = ide;
 }

 @Column(name = "protocol_org_id")
 public Integer getProtocolOrgId() {
 return this.protocolOrgId;
 }

 public void setProtocolOrgId(Integer protocolOrgId) {
 this.protocolOrgId = protocolOrgId;
 }

 @Column(name = "zipcode", nullable = false)
 @NotNull
 public String getZipcode() {
 return this.zipcode;
 }

 public void setZipcode(String zipcode) {
 this.zipcode = zipcode;
 }

 @Column(name = "phone", nullable = false, length = 20)
 @NotNull
 @Length(max = 20)
 public String getPhone() {
 return this.phone;
 }

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

 @Column(name = "fax", nullable = false, length = 20)
 @NotNull
 @Length(max = 20)
 public String getFax() {
 return this.fax;
 }

 public void setFax(String fax) {
 this.fax = fax;
 }

 @Column(name = "email")
 public String getEmail() {
 return this.email;
 }

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

 @Column(name = "arbo_naam", nullable = false, length = 50)
 @NotNull
 @Length(max = 50)
 public String getArboNaam() {
 return this.arboNaam;
 }

 public void setArboNaam(String arboNaam) {
 this.arboNaam = arboNaam;
 }

 @Column(name = "arbo_klantnummer", nullable = false, length = 20)
 @NotNull
 @Length(max = 20)
 public String getArboKlantnummer() {
 return this.arboKlantnummer;
 }

 public void setArboKlantnummer(String arboKlantnummer) {
 this.arboKlantnummer = arboKlantnummer;
 }

 @Column(name = "arbo_branche", nullable = false, length = 50)
 @NotNull
 @Length(max = 50)
 public String getArboBranche() {
 return this.arboBranche;
 }

 public void setArboBranche(String arboBranche) {
 this.arboBranche = arboBranche;
 }

 @Column(name = "arbo_aansluitnummer", nullable = false, length = 20)
 @NotNull
 @Length(max = 20)
 public String getArboAansluitnummer() {
 return this.arboAansluitnummer;
 }

 public void setArboAansluitnummer(String arboAansluitnummer) {
 this.arboAansluitnummer = arboAansluitnummer;
 }

 @Column(name = "risicodrager_pemba", length = 1)
 @Length(max = 1)
 public Character getRisicodragerPemba() {
 return this.risicodragerPemba;
 }

 public void setRisicodragerPemba(Character risicodragerPemba) {
 this.risicodragerPemba = risicodragerPemba;
 }

 @Column(name = "arboarts_id")
 public Integer getArboartsId() {
 return this.arboartsId;
 }

 public void setArboartsId(Integer arboartsId) {
 this.arboartsId = arboartsId;
 }
 @Temporal(TemporalType.TIME)
 @Column(name = "workfrom_mo", length = 8)
 public Date getWorkfromMo() {
 return this.workfromMo;
 }

 public void setWorkfromMo(Date workfromMo) {
 this.workfromMo = workfromMo;
 }
 @Temporal(TemporalType.TIME)
 @Column(name = "workuntil_mo", length = 8)
 public Date getWorkuntilMo() {
 return this.workuntilMo;
 }

 public void setWorkuntilMo(Date workuntilMo) {
 this.workuntilMo = workuntilMo;
 }
 @Temporal(TemporalType.TIME)
 @Column(name = "break_mo", length = 8)
 public Date getBreakMo() {
 return this.breakMo;
 }

 public void setBreakMo(Date breakMo) {
 this.breakMo = breakMo;
 }
 @Temporal(TemporalType.TIME)
 @Column(name = "workfrom_tu", length = 8)
 public Date getWorkfromTu() {
 return this.workfromTu;
 }

 public void setWorkfromTu(Date workfromTu) {
 this.workfromTu = workfromTu;
 }
 @Temporal(TemporalType.TIME)
 @Column(name = "workuntil_tu", length = 8)
 public Date getWorkuntilTu() {
 return this.workuntilTu;
 }

 public void setWorkuntilTu(Date workuntilTu) {
 this.workuntilTu = workuntilTu;
 }
 @Temporal(TemporalType.TIME)
 @Column(name = "break_tu", length = 8)
 public Date getBreakTu() {
 return this.breakTu;
 }

 public void setBreakTu(Date breakTu) {
 this.breakTu = breakTu;
 }
 @Temporal(TemporalType.TIME)
 @Column(name = "workfrom_we", length = 8)
 public Date getWorkfromWe() {
 return this.workfromWe;
 }

 public void setWorkfromWe(Date workfromWe) {
 this.workfromWe = workfromWe;
 }
 @Temporal(TemporalType.TIME)
 @Column(name = "workuntil_we", length = 8)
 public Date getWorkuntilWe() {
 return this.workuntilWe;
 }

 public void setWorkuntilWe(Date workuntilWe) {
 this.workuntilWe = workuntilWe;
 }
 @Temporal(TemporalType.TIME)
 @Column(name = "break_we", length = 8)
 public Date getBreakWe() {
 return this.breakWe;
 }

 public void setBreakWe(Date breakWe) {
 this.breakWe = breakWe;
 }
 @Temporal(TemporalType.TIME)
 @Column(name = "workfrom_th", length = 8)
 public Date getWorkfromTh() {
 return this.workfromTh;
 }

 public void setWorkfromTh(Date workfromTh) {
 this.workfromTh = workfromTh;
 }
 @Temporal(TemporalType.TIME)
 @Column(name = "workuntil_th", length = 8)
 public Date getWorkuntilTh() {
 return this.workuntilTh;
 }

 public void setWorkuntilTh(Date workuntilTh) {
 this.workuntilTh = workuntilTh;
 }
 @Temporal(TemporalType.TIME)
 @Column(name = "break_th", length = 8)
 public Date getBreakTh() {
 return this.breakTh;
 }

 public void setBreakTh(Date breakTh) {
 this.breakTh = breakTh;
 }
 @Temporal(TemporalType.TIME)
 @Column(name = "workfrom_fr", length = 8)
 public Date getWorkfromFr() {
 return this.workfromFr;
 }

 public void setWorkfromFr(Date workfromFr) {
 this.workfromFr = workfromFr;
 }
 @Temporal(TemporalType.TIME)
 @Column(name = "workuntil_fr", length = 8)
 public Date getWorkuntilFr() {
 return this.workuntilFr;
 }

 public void setWorkuntilFr(Date workuntilFr) {
 this.workuntilFr = workuntilFr;
 }
 @Temporal(TemporalType.TIME)
 @Column(name = "break_fr", length = 8)
 public Date getBreakFr() {
 return this.breakFr;
 }

 public void setBreakFr(Date breakFr) {
 this.breakFr = breakFr;
 }
 @Temporal(TemporalType.TIME)
 @Column(name = "workfrom_sa", length = 8)
 public Date getWorkfromSa() {
 return this.workfromSa;
 }

 public void setWorkfromSa(Date workfromSa) {
 this.workfromSa = workfromSa;
 }
 @Temporal(TemporalType.TIME)
 @Column(name = "workuntil_sa", length = 8)
 public Date getWorkuntilSa() {
 return this.workuntilSa;
 }

 public void setWorkuntilSa(Date workuntilSa) {
 this.workuntilSa = workuntilSa;
 }
 @Temporal(TemporalType.TIME)
 @Column(name = "break_sa", length = 8)
 public Date getBreakSa() {
 return this.breakSa;
 }

 public void setBreakSa(Date breakSa) {
 this.breakSa = breakSa;
 }
 @Temporal(TemporalType.TIME)
 @Column(name = "workfrom_su", length = 8)
 public Date getWorkfromSu() {
 return this.workfromSu;
 }

 public void setWorkfromSu(Date workfromSu) {
 this.workfromSu = workfromSu;
 }
 @Temporal(TemporalType.TIME)
 @Column(name = "workuntil_su", length = 8)
 public Date getWorkuntilSu() {
 return this.workuntilSu;
 }

 public void setWorkuntilSu(Date workuntilSu) {
 this.workuntilSu = workuntilSu;
 }
 @Temporal(TemporalType.TIME)
 @Column(name = "break_su", length = 8)
 public Date getBreakSu() {
 return this.breakSu;
 }

 public void setBreakSu(Date breakSu) {
 this.breakSu = breakSu;
 }

 @Column(name = "monday")
 public String getMonday() {
 return this.monday;
 }

 public void setMonday(String monday) {
 this.monday = monday;
 }

 @Column(name = "tuesday")
 public String getTuesday() {
 return this.tuesday;
 }

 public void setTuesday(String tuesday) {
 this.tuesday = tuesday;
 }

 @Column(name = "wednesday")
 public String getWednesday() {
 return this.wednesday;
 }

 public void setWednesday(String wednesday) {
 this.wednesday = wednesday;
 }

 @Column(name = "thursday")
 public String getThursday() {
 return this.thursday;
 }

 public void setThursday(String thursday) {
 this.thursday = thursday;
 }

 @Column(name = "friday")
 public String getFriday() {
 return this.friday;
 }

 public void setFriday(String friday) {
 this.friday = friday;
 }

 @Column(name = "saturday")
 public String getSaturday() {
 return this.saturday;
 }

 public void setSaturday(String saturday) {
 this.saturday = saturday;
 }

 @Column(name = "sunday")
 public String getSunday() {
 return this.sunday;
 }

 public void setSunday(String sunday) {
 this.sunday = sunday;
 }

}

package org.agilit.ipplus;

import org.jboss.seam.annotations.Name;
import org.jboss.seam.annotations.RequestParameter;
import org.jboss.seam.framework.EntityQuery;

@Name("orgLevelList")
public class OrgLevelList extends EntityQuery {
 @Override
 public String getEjbql() {
 return "select orgLevel from OrgLevel orgLevel";
 }

 @RequestParameter
 @Override
 public void setFirstResult(Integer firstResult) {
 super.setFirstResult(firstResult);
 }

 @Override
 public Integer getMaxResults() {
 return 25;
 }
}

<!DOCTYPE composition PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<ui:composition xmlns="http://www.w3.org/1999/xhtml"
 xmlns:s="http://jboss.com/products/seam/taglib"
 xmlns:ui="http://java.sun.com/jsf/facelets"
 xmlns:f="http://java.sun.com/jsf/core"
 xmlns:h="http://java.sun.com/jsf/html"
 template="layout/template.xhtml">

<ui:define name="body">

 <ui:include src="Userzijmenu.xhtml"/>

 <div id="topnavigation">
 <s:link view="/Organisatie.xhtml" value="Organisatie..." propagation="none"/>
 <s:link view="/toegang.xhtml" value="Toegang..." propagation="none"/>
 <s:link view="/voorkeuropleidingdb.xhtml" value="Voorkeur opleidingen-db..." propagation="none"/>
 <s:link view="/nieuws.xhtml" value="Nieuws..." propagation="none"/>
 <s:link view="/dataimport.xhtml" value="Data-import..." propagation="none"/>
 </div>

 <h:messages globalOnly="true" styleClass="message"/>

 <h:outputText value="No user exists"
 rendered="#{empty userList.resultList}"/>
 <h:dataTable id="userList" var="user"
 value="#{userList.resultList}"
 rendered="#{not empty userList.resultList}" headerClass="list-header" rowClasses="list-row" columnClasses="list-column">
 <h:column>
 <f:facet name="header"><s:link view="/UserList.xhtml">Gebruikersnaam</s:link></f:facet>
 <s:link view="/UserEdit.xhtml" value="#{user.username}">
 <f:param name="userId" value="#{user.username}"/>
 </s:link>
 </h:column>
 <h:column>
 <f:facet name="header"><s:link view="/UserList.xhtml">Organisatienaam</s:link></f:facet>
 <s:link view="/Organization.xhtml" value="#{user.orgId}">
 <f:param name="organizationId" value="#{user.orgId}"/>
 </s:link>
 </h:column>
 <h:column>
 <f:facet name="header"><div></div></f:facet>
 <s:link vieuw="/UserList.xhtml">Verwijder</s:link>
 </h:column>

 </h:dataTable>

</ui:define>

</ui:composition>