fharms fharms - 6 days ago 6
Java Question

Join table on case insensitive PK and FK with JPA

I have 2 tables I want to join on PK and FK in JPA, but the PK is upper case and the FK lower case.

How do I map case insensitive association between Person -> GroupAssociationEntity?

My current mapping is not working.

@Entity
public class Person {

@Id
@Column(name = "id", columnDefinition = "nvarchar")
private String id;


@OneToMany(mappedBy = "person")
private List<GroupAssociationEntity> groups;
}

@Entity
@IdClass(GroupAssociationKey.class)
public class GroupAssociationEntity {

@Id
private String id;

@Id
private String memberOf;

@ManyToOne
@JoinColumn(name = "id", updatable = false, insertable = false, referencedColumnName = "id")
private Group group;

@ManyToOne
@JoinColumn(name = "memberOf", updatable = false, insertable = false, referencedColumnName = "id")
private Person person;

....
}

@Entity
public class Group {

@Id
@Column(name = "id")
private String id;

@OneToMany(mappedBy = "group")
private List<GroupAssociationEntity> persons;

......
}

Answer

Try with @JoinFormula instead of @JoinColumn. If the collation would differ in any possible form, then you could try a join based on the result of a subselect:

@Entity
@IdClass(GroupAssociationKey.class)
public class GroupAssociationEntity {

    @Id
    private String id;

    @Id
    private String memberOf;

    @ManyToOne
    @JoinFormula( "(select g.id from Group g where UPPER(g.id) = UPPER(id))" )
    private Group group;

    @ManyToOne
    @JoinFormula( "(select p.id from Person p where UPPER(p.id) = UPPER(memberOf))" )
    private Person person;

   ....
}

However, if you know that only the FK is in lowercase, you can remove the select and simplify the mapping as follows:

@Entity
@IdClass(GroupAssociationKey.class)
public class GroupAssociationEntity {

    @Id
    private String id;

    @Id
    private String memberOf;

    @ManyToOne
    @JoinFormula( "UPPER(id)" )
    private Group group;

    @ManyToOne
    @JoinFormula( "UPPER(memberOf)" )
    private Person person;

   ....
}

Cool, right?