JPA – JPQL to join an entity not mapped


Today I came across a bit of a problem converting an old SQL select statement into JPQL. Essentially the problem was that the original query used a join but my two entities weren’t mapped to each other.

As an example, lets say we have the following two entities.

@Entity
@Table(name="Animal", schema="test")
public class Animal
{
    @Id @Column(name="ID", nullable=false)
    private Long animalID;

    @Column(name="COLOUR")
    private String colour;
    ...
}

Let’s say the parent entity is a generic Animal class. In this example we also have a Fish class which captures any extra attributes that a fish has in addition to an animal. Maybe the number of fins for example.

@Entity
@Table(name="Fish", schema="test")
public class Fish
{
    @Id @Column(name="ID", nullable=false)
    private Long animalID;

    @Column(name="NUMOFFINS")
    private Integer numberOfFins;
    ...
}

Given these entities, with no inheritance relationship or JPA mappings, I needed to convert the following SQL select statement to select all fish that are grey in colour.

SELECT f.* FROM test.Fish f INNER JOIN test.Animal a ON f.animalID = a.animalID WHERE a.COLOUR = "Grey"

My first stab at converting this to JPQL looked like this:

SELECT f FROM Fish f INNER JOIN Animal a WHERE f.animalID = a.animalID AND a.COLOUR = "Grey"

This is where I hit problems. In JPQL you’re not joining tables but entities and as such you can’t join two entities that don’t have a mapped relationship. Idealistically I would want to make Fish subclass Animal and map the relationship properly but for now this is out of scope. I managed to solve this by replacing the join with a sub select.

SELECT f FROM Fish f WHERE f.animalID in (SELECT a.animalID FROM Animal a WHERE a.COLOUR = "Grey")

JPA doesn’t seem to mind this and you could even change it to use exists.

SELECT f FROM Fish f WHERE EXISTS (SELECT a FROM Animal a WHERE f.animalID = a.animalID AND a.COLOUR = "Grey")

It takes some practice to stop thinking of tables and start thinking of entities!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s