Hibernate MS SQL Join issue

Question

I have two tables in the clients mssql database. The first is a job table - so I created an Job entity which contains the load type and load weight and all that stuff - works fine.

My problem now is that there is a second table that includes informations about the load and unload point. The second table, I call it JEP, has a primary key consisting of several items: the type (load or unload), the zip code and the customer number.

I created an entity JobEndPoint and NetBeans also created an object representing the primary key JobEndPointPK containing all that fields.

I want to add two JobEndPoint (loadPoint and unloadPoint) to my Job entity. My problem is now: how do I annotate that in Hibernate? In my opinion it is an @OneToOne relation ship. It would be perfect if I could specify a SELECT statement like SELECT * FROM JEP WHERE type="load" AND customer_nr="123" AND zip_code="123 ...". Is that possible with Hibernate?

Thanks for your help!

Regeards,

Marco

---

Here are the Entities:

@Entity
@Table(name = "Auftragsdaten", catalog = "...", schema = "dbo")
public class Job implements Comparable<Object>, Serializable {

    private static final long serialVersionUID = 4285871251915951149L;

    @Id
    @Basic(optional = false)
    @Column(name = "`id`", nullable = false)
    int id;

    @Column(name = "`AufNr`", nullable=false)
    int jobId;

    @Transient
    List<Integer> jobsAdded;

    @Column(name = "`Beladedatum`", nullable=false)
    @Temporal(TemporalType.DATE)
    Date loadDate;

    @Column(name = "`Beladezeit`")
    @Temporal(TemporalType.TIME)
    Date loadTimeFrom;

    @Transient
    Date loadTimeTo;

    @Column(name = "`Entladedatum`", nullable=false)
    @Temporal(TemporalType.DATE)
    Date unloadDate;

    @Column(name = "`Entladezeit Beginn`")
    @Temporal(TemporalType.TIME)
    Date unloadTimeFrom;

    @Column(name = "`Entladezeit Ende`")
    @Temporal(TemporalType.TIME)
    Date unloadTimeTo;

    @Transient
    List<JobEndPoint> froms;

    @OneToOne
    @JoinColumns ({
        @JoinColumn(name="`Beladetyp`", referencedColumnName = "`Ladetyp`", insertable = false, updatable = false),
        @JoinColumn(name="`AbsNr`", referencedColumnName = "`KundenNr`", insertable = false, updatable = false),
        @JoinColumn(name="`Verkehrsart`", referencedColumnName = "`VerkArt`", insertable = false, updatable = false),
        @JoinColumn(name="`von LKZ`", referencedColumnName = "`LKZ`", insertable = false, updatable = false),
        @JoinColumn(name="`von PLZ`", referencedColumnName = "`PLZ`", insertable = false, updatable = false)
    })
    JobEndPoint fromPoint;

    @Transient
    JobEndPoint toPoint;

    @Column(name = "`Verkehrsart`", length = 10, nullable=false)
    @Enumerated
    JobType type;

    @Column(name = "`Anzahl Paletten CCG1`")
    int numberCCG1;

    @Column(name = "`Anzahl Paletten CCG2`")
    int numberCCG2;

    @Transient
    int numberFullContainer;

    @Transient
    int numberEmptyContainer;

    @Column(name = "`Anzahl Container`")
    int numberContainer;

    @Column(name = "`Anz Stellplätze`")
    int numberUnits;

    @Column(name = "`Bruttogewicht`", nullable=false)
    int loadWeight;

    @ManyToOne
    @JoinColumn(name="`Kühlkennzeichen`")
    CoolingCode coolingCode;
}

@Entity
@Table(name = "BES", catalog = "...", schema = "dbo")
public class JobEndPoint implements Serializable {

    private static final long serialVersionUID = 1017986852824783744L;

    @Id
    protected JobEndPointPK jobEndPointPK;

    (...)
}

@Embeddable
public class JobEndPointPK implements Serializable {

    @Basic(optional = false)
    @Column(name = "`Ladetyp`", nullable = false, length = 50)
    @Enumerated
    EndPointType type;

    @Basic(optional = false)
    @Column(name = "`KundenNr`", nullable = false)
    int customerId;

    @Basic(optional = false)
    @Column(name = "`VerkArt`", nullable = false, length = 10)
    @Enumerated
    JobType jobType;

    @Basic(optional = false)
    @Column(name = "`LKZ`", nullable = false, length = 3)
    String countryCode;

    @Basic(optional = false)
    @Column(name = "`PLZ`", nullable = false, length = 7)
    String zipCode;
}

Answer 1

In general, I would recommend using a generated internal primary key instead of the composite key. However, if you need to stick with your composite key, here are some ideas that hopefully help.

I understand that JobEndPointPK is implemented as an identifier component (see the Hibernate Reference, chapter 8.4). Note: it is critical that it implements the equals and hashCode` methods correctly, as Hibernate relies on these.

Updated: Provided that your JobEndPoint and JobEndPointPK looks something like this:

@Embeddable
class JobEndPointPK {
    @Column(name = "type", nullable = false)
    @Enumerated
    EndPointType type;

    @Column(name = "zipCode", nullable = false)
    String zipCode;

    @Column(name = "customerNumber", nullable = false)
    int customerId;

    // equals, hasCode, getters, setters etc.
}

@Entity
class JobEndPoint {
    @Id
    private JobEndPointPK key;

    // getters, setters etc.
}

The mapping annotation would be something like:

@Entity
class Job {
    @OneToOne
    @JoinColumns ({
        @JoinColumn(name="loadPointType", referencedColumnName = "type"),
        @JoinColumn(name="loadPointZip", referencedColumnName = "zipCode"),
        @JoinColumn(name="loadPointCust", referencedColumnName = "customerNumber")
    })
    private JobEndPoint loadPoint;
    // similarly for unloadPoint
    // other properties
}

The example is adapted from here.

I am not sure how to deal with JobEndPointPK.type though, as for loadPoint it is obviously Load and for unloadPoint, Unload, so you most probably don't want to store it separately in the DB. My gues is that you can specify the value with the @Formula annotation, but I haven't seen any concrete example for this.

Note that all this code is purely experimental, I haven't tested it.

There are other variations on the theme. For more details, see the section "Composite keys with annotations" in Chapter 8 of Java Persistence with Hibernate.