JPA OneToMany on composited column

Question

First of all, this is a legacy DB and the schema cannot be changed. Now imagine I've the following tables:

-----------      -----------
| table A |      | table B |
-----------      -----------
| id_A    |----->| id_B    |
| col_A   |      | col_B   |
-----------      -----------

Table A is the master and Table B is the detail. Both id_A and id_B are strings BUT id_B = id_A + 4 characters.

For instance, if id_A = "0000000123" then there are multiple id_B like the following ones "00000001230001","00000001230002", "00000001230003", ... yes, I know, that should have been another column. As I said this is a legacy DB and I found it that way.

I'm using Spring Data JPA, JPA2 and Hibernate. And what I need is to define the entities:

@Entity
@Table(name="A")
public class A {

    @Column(name = "id_A", length = 10, unique = true, nullable = false)
    private String idA;

    @Column(name = "col_A")
    private String colA;

    @OneToMany <-- WHAT MORE GOES HERE TO REFERENCE JUST THE SUBSTRING OF THE DETAIL id_B?
    private List<B> detail;
}

@Entity
@Table(name="B")
public class B {

    @Column(name = "id_B", length = 14, unique = true, nullable = false)
    private String idB;

    @Column(name = "col_B")
    private String colB;
}

I don't know how to reference that what I need is that substr(id_b, 1, 10) = id_A

How can I do that?

@VikrantKashyap I've tried @JoinFormula(value = "SELECT b FROM B b WHERE SUBSTRING(b.id_B, 1, 10) = id_A") and some other things commented here http://stackoverflow.com/questions/12921136/set-where-clause-on-field and http://stackoverflow.com/questions/9954937/joinformula-and-onetomany-definition-poor-documentation and http://stackoverflow.com/questions/6919686/annotation-to-filter-results-of-a-onetomany-association — JoséMi, Feb 23 '16 at 12:02
You cant do that. a B is identified by "idB" so the FK needs to be to that column. — Neil Stockton, Feb 23 '16 at 12:04
http://www.objectdb.com/api/java/jpa/OneToMany try this link too — Vikrant Kashyap, Feb 23 '16 at 12:08

Alan Hay · Accepted Answer · 2016-02-23T14:45:00.183

The only solution I can see for this is to create a view at the database level. MySQL supports updateable views if certain criteria are met and we should be okay with both reading and writing.

http://dev.mysql.com/doc/refman/5.7/en/view-updatability.html

(Recent versions of MySQL also support Generated columns which may be an alternative to a view (http://mysqlserverteam.com/generated-columns-in-mysql-5-7-5/)

Create a view:

create view VW_TABLE_B
as
select *,
substring(..) as a_id
from B

Entity B is mapped to the view:

@Entity
@Table(name="VW_TABLE_B")
public class B {

    @Id
    @Column(name = "id_B", length = 14)
    private String id;

    @Column(name = "col_B")
    private String colB;

    protected void setId(String id){
        this.id = id;
   }
}

From a JPA perspective it doesn't matter if we are using a view, concrete table or whatever. The relationship from Entity A to Entity B can then become a standard mapping with join column pointing to the derived value in the view.

@Entity
@Table(name="A")
public class A {

    @Id
    @Column(name = "id_A", length = 10)
    private String id;

    @Column(name = "col_A")
    private String colA;

    @OneToMany 
    @JoinColumn(name = "a_id")
    private List<B> detail;

    public void addB(B b){
        b.setId(this.id + new DecimalFormmat("0000").format(detail.size() + 1);
        detail.add(b);
    )
}

JPA OneToMany on composited column

1 Answers1