views:

300

answers:

1

Hi folks,

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;
}
+1  A: 

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.

Péter Török
Thanks! This looks like a start. Is it correct that I have the JobEndPointPK in the Job object although I want the JobEndPoint object ? Or the other way: how do I get the JobEndPoint object with the JobEndPointPK stored in the Job during runtime?
Marco Nätlitz
@Marco Damn, I messed it up :-( Now I updated the code. I also linked a better example.
Péter Török
@Marco Nothing obvious... could you post your latest code?
Péter Török
Hi again. Thanks for sticking with me. I posted my entity declartions above and hope it will help...
Marco Nätlitz
If you leave out the condition loadtype (first @JoinColumn) Hibernate reports:Caused by: org.hibernate.AnnotationException: referencedColumnNames(`KundenNr`, `VerkArt`, `LKZ`, `PLZ`) of Job.fromPoint referencing JobEndPoint not mapped to a single property
Marco Nätlitz
@Marco Now I am puzzled as to what your problem actually is. You do have `@JoinColumn(name="``von LKZ``", referencedColumnName = "``LKZ``", ...),` in your code, is that supposed to do filtering? Do you have column `LKZ` in your table? If so, is it mapped in `JobEndPointPK`?
Péter Török
Yes, I am puzzled, too. I double checked the spelling. "LKZ" is in the JobEndPointPK and in the table. This is why I don't understand the error message... Well, I need to filter. The table JobEndPoint has a primary key consisting of this 4 fields - all defined in JobEndPointPK. I am sorry that the database scheme is in German...
Marco Nätlitz
I get the same exception if I try to do an @ManyToOne instead of a @OnetoOne
Marco Nätlitz
@Marco What is `PLZ` then? Is it also a column in `JobEndPointPK`? And what is `name="von LKZ"`? I don't think you can have property names contain spaces... Also why are you putting the `@JoinColumn` attribute values between single backquotes (`)?
Péter Török
I put them between backquotes because they contain space characters. I thought this is the way in Hibernate to escape characters? "PLZ" is the zip code and "von LKZ" is the "from contry code". There is also a "from zip code" and a "to zip code" and so on... I have the problem that I cannot alter the database scheme, because the customer is already working with this...
Marco Nätlitz
@Marco Well, I really can't find any obvious mistake in what you did... :-( Could you post the `CREATE TABLE` SQL statements too?
Péter Török
@Peter: I removed the "`" in the cases they were not necessary and it worked! So - your solution was the right one. I still cannot figure out why and when you have to escape characters in the column name definition. Anyway - thanks for your help and advise in this matter!
Marco Nätlitz
@Marco Glad to hear you managed to make it work finally! AFAIK you should quote only when the column name contains whitespaces, strange characters, is case sensitive or conflicts with a reserved word. Now we learned a bit about the practical reason behind :-)
Péter Török