How to add a JPA relationship against legacy database

I'm coming from a C# entity framework background and looking at JPA in a Java project so I'm hoping that what I'm facing is just a conceptual problem.

I've got a legacy database that I can't alter the schema of and I need to write a DAL.

I've generated (simplified for the example) the following entities...

@Entity
@Table(name = "crag", catalog = "rad_dbo")
public class CragEntity {
    private int id;

    @Column(name = "id")
    @Id
    public int getId() {
        return id;
    }

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

    private int fkSubRegionId;

    @Column(name = "fk_subRegionId")
    @Basic
    public int getFkSubRegionId() {
        return fkSubRegionId;
    }

    public void setFkSubRegionId(int fkSubRegionId) {
        this.fkSubRegionId = fkSubRegionId;
    }
}

and

@Table(name = "subRegion", catalog = "rad_d开发者_如何学运维bo")
@Entity
public class SubRegionEntity {
    private int id;

    @Column(name = "id")
    @Id
    public int getId() {
        return id;
    }

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

I've tried adding a relationship to CragEntity so that I can access its subRegion

@ManyToOne
@JoinColumn(name="fk_SubRegionId",nullable=false)
private SubRegionEntity subRegion;

but when I try to run

select c from CragEntity c where c.subRegion.region = :area

I get an exception

java.lang.RuntimeException: org.hibernate.QueryException: could

not resolve property: subRegion of: uk.co.bmc.rad.dal.CragEntity

Hopefully this is possible and I'm being slow...

Many thanks in advance for any help!

In your query you are searching for the property "subRegion" though in your entity definition you have the name "fkSubRegionId", so you must change the var name or the query. ;)

EDIT: Sorry i misreaded the relation.

Can you access the property (without making an HQL query) with the relationship inside the code?

Unless, you want to pick only certain fields in your query I would recommend a query like:

from CragEntity c where c.subRegion.region='theRegion'

It turns out there were several issues - one conceptual, one with how IntelliJ had generated a relationship I was copying and one between the chair and keyboard...

IntelliJ had picked the region to subregion relationship with the owner at the "wrong" end - probably a schema issue rather than IntelliJ's fault. Once I realised that and figured out the fix I could copy that to CragEntity and SubRegionEntity

In CragEntity I added:

private SubRegionEntity subRegion;

        @ManyToOne
        @JoinColumn(name="fk_SubRegionId",nullable=false)
        public SubRegionEntity getSubRegion() {
            return subRegion;
        }

        public void setSubRegion(SubRegionEntity subRegion) {
            this.subRegion = subRegion;
        }

and then in SubRegionEntity I added:

private List<CragEntity> crags;

    @OneToMany(mappedBy = "subRegion")
    List<CragEntity> getCrags() {
        return crags;
    }

    public void setCrags(List<CragEntity> crags) {
        this.crags = crags;
    }

Also, it seem that any entity class that is going to be one end of a relationship has to implement serializable (I guess the entities get serialized into the owner. So that needed adding onto SubRegionEntity and RegionEntity

The silliness on my part was of course that the query should have been c.subRegion.region.name otherwise I was comparing an object of type RegionEntity with a string... doh - very stupid mistake on my part.

I'm new to TDD but as always as soon as I wrote tests for what I thought should be happening with the existing code I was walked through my errors (and given google keywords by the exceptions and errors :-))