Java Persistence API: Relations

    create table ORMREL_PERSON (
        PERSON_ID bigint generated by default as identity,
        firstName varchar(255),
        lastName varchar(255),
        phone varchar(255),
+-----  PERSON_PHOTO bigint,
|       primary key (PERSON_ID)
|   )
|   create table ORMREL_PHOTO (
+-----> PHOTO_ID bigint generated by default as identity,
        image blob,
        primary key (PHOTO_ID)
    )
    alter table ORMREL_PERSON 
        add constraint FK14D7C425DCCB1C0D 
        foreign key (PERSON_PHOTO) 
        references ORMREL_PHOTO

    @Entity

    @Table(name="ORMREL_PERSON")

    public class Person  {

        @Id @GeneratedValue @Column(name="PERSON_ID")

        private long id;

    

        @OneToOne(cascade={ 

                CascadeType.ALL},  //have creates, deletes, etc. cascade to photo

                fetch=FetchType.LAZY)       //a hint that we don't need this

        @JoinColumn(name="PERSON_PHOTO")    //define local foreign key column

+-----  private Photo photo;

|   

|   @Entity

|   @Table(name="ORMREL_PHOTO")

|   public class Photo {

|       @Id @GeneratedValue @Column(name="PHOTO_ID")

+---->  private long id;

//create the owning side 

ejava.examples.orm.rel.annotated.Person person = new Person();

person.setFirstName("john");

person.setLastName("doe");

person.setPhone("410-555-1212");


//create the inverse side 

ejava.examples.orm.rel.annotated.Photo photo = new Photo();        

photo.setImage(image);        


//add photo to person and persist object tree

person.setPhoto(photo); //this sets the FK in person

log.info("added photo to person:" + person);

em.persist(person);        

assertTrue("personId not set", person.getId() != 0);

assertTrue("photoId not set", photo.getId() != 0);

log.info("created person:" + person);

-added photo to person:Person@1201f5bc, id=0, name=john doe, phone=410-555-1212, 
    photo=Photo@130b5045, id=0. image=46080 bytes
Hibernate:
    insert into ORMREL_PHOTO (PHOTO_ID, image) 
    values (null, ?)
Hibernate:
    insert into ORMREL_PERSON (PERSON_ID, firstName, lastName, phone, PERSON_PHOTO) 
    values (null, ?, ?, ?, ?)
-created person:Person@1201f5bc, id=1, name=john doe, phone=410-555-1212, 
        photo=Photo@130b5045, id=1. image=46080 bytes

//verify what we can get from DB

em.flush(); em.clear();

Person person2 = em.find(Person.class, person.getId());

assertNotNull(person2);

assertNotNull(person2.getPhoto());

log.info("found person:" + person2);

Hibernate:
    select
        person0_.PERSON_ID as PERSON1_24_0_,
        person0_.firstName as firstNam2_24_0_,
        person0_.lastName as lastName3_24_0_,
        person0_.phone as phone4_24_0_,
        person0_.PERSON_PHOTO as PERSON5_24_0_ 
    from ORMREL_PERSON person0_ 
    where person0_.PERSON_ID=?
Hibernate:
    select
        photo0_.PHOTO_ID as PHOTO1_25_0_,
        photo0_.image as image2_25_0_ 
    from ORMREL_PHOTO photo0_ 
    where photo0_.PHOTO_ID=?
-found person:Person@29564bb9, id=1, name=john doe, phone=410-555-1212, 
    photo=Photo@785e7845, id=1. image=46080 bytes

    create table ORMREL_APPLICANT (
        id bigint generated by default as identity,
+-----  APP_BORROWER bigint,
|       APP_PERSON bigint not null,
|       primary key (id)
|   )
|   create table ORMREL_BORROWER (
+---->  BORROWER_ID bigint not null,
        endDate date,
        startDate date,
        primary key (BORROWER_ID)
    )
    alter table ORMREL_APPLICANT 
        add constraint FKD1860812DA35F52F 
        foreign key (APP_BORROWER) 
        references ORMREL_BORROWER

    @Entity @Table(name="ORMREL_APPLICANT")

    public class Applicant  {

        @Id @GeneratedValue

        private long id;

        

  +-->  @OneToOne(optional=true)        //we may exist without Borrower 

  |     @JoinColumn(name="APP_BORROWER")//we own relationship to Borrower

+-----  private Borrower borrower;

| |

| | @Entity @Table(name="ORMREL_BORROWER")

| | public class Borrower  {

| |     @Id @Column(name="BORROWER_ID")

`---->  private long id;

  |    

  |     @OneToOne(fetch=FetchType.LAZY, 

  |             optional=true,       //lets make this optional for demo 

  |             mappedBy="borrower") //the other side owns foreign key column

  +--   private Applicant application;

        //locate them from DB

        Applicant applicant2 = em.find(Applicant.class, applicant.getId());

        Borrower borrower2 = em.find(Borrower.class, borrower.getId());

        

        //form relationship

        borrower2.setApplication(applicant2); //set inverse side

        applicant2.setBorrower(borrower2);    //set owning side

    update
        ORMREL_APPLICANT 
    set
        APP_BORROWER=?,
        APP_PERSON=? 
    where
        id=?

        

        //locate them from DB

        em.flush(); em.clear();

        Applicant applicant3 = em.find(Applicant.class, applicant.getId());

        Borrower borrower3 = em.find(Borrower.class, borrower.getId());

        assertEquals(applicant.getId(), borrower3.getApplication().getId());

        assertEquals(borrower.getId(), applicant3.getBorrower().getId());

Hibernate:
   select
        applicant0_.id as id1_12_2_,
        applicant0_.APP_BORROWER as APP2_12_2_,
        applicant0_.APP_PERSON as APP3_12_2_,
        borrower1_.BORROWER_ID as BORROWER1_15_0_,
        borrower1_.endDate as endDate2_15_0_,
        borrower1_.startDate as startDat3_15_0_,
        person2_.PERSON_ID as PERSON1_24_1_,
        ...
    from ORMREL_APPLICANT applicant0_ 
    left outer join ORMREL_BORROWER borrower1_ 
            on applicant0_.APP_BORROWER=borrower1_.BORROWER_ID 
    inner join ORMREL_PERSON person2_ 
            on applicant0_.APP_PERSON=person2_.PERSON_ID 
    where applicant0_.id=?
Hibernate:
    select
        applicant0_.id as id1_12_2_,
        applicant0_.APP_BORROWER as APP2_12_2_,
        applicant0_.APP_PERSON as APP3_12_2_,
        borrower1_.BORROWER_ID as BORROWER1_15_0_,
        borrower1_.endDate as endDate2_15_0_,
        borrower1_.startDate as startDat3_15_0_,
        person2_.PERSON_ID as PERSON1_24_1_,
        ...
    from ORMREL_APPLICANT applicant0_ 
    left outer join ORMREL_BORROWER borrower1_ 
            on applicant0_.APP_BORROWER=borrower1_.BORROWER_ID 
    inner join ORMREL_PERSON person2_ 
            on applicant0_.APP_PERSON=person2_.PERSON_ID 
    where applicant0_.APP_BORROWER=?
        -

    create table ORMREL_INVENTORY (
/---->  id bigint generated by default as identity,
|       name varchar(255),
|       primary key (id)
|   )
|   create table ORMREL_MEDIA (
|       MEDIA_ID bigint generated by default as identity,
|       title varchar(255),
`-----  INVENTORY_ID bigint,
        primary key (MEDIA_ID)
    )
    alter table ORMREL_MEDIA 
        add constraint FK639A68F4BCF517CD 
        foreign key (INVENTORY_ID) 
        references ORMREL_INVENTORY

    @Entity @Table(name="ORMREL_INVENTORY")

    public class Inventory {

        @Id @GeneratedValue

        private long id;

        private String name;

    

        @OneToMany(cascade={CascadeType.ALL})

/-----  @JoinColumn(name="INVENTORY_ID")

|       private Collection<Media> media = new ArrayList<Media>();

|   

|   @Entity @Table(name="ORMREL_MEDIA")

|   public class Media  {

|       @Id @GeneratedValue @Column(name="MEDIA_ID")

`----> private long id;

ejava.examples.orm.rel.annotated.Inventory inventory = new Inventory();

inventory.setName("testLinkCreate");


em.persist(inventory);


for(int i=0; i<5; i++) {

    ejava.examples.orm.rel.annotated.Media media = new Media();

    em.persist(media);

    log.info("created media:" + media);

    inventory.getMedia().add(media);

}

log.info("created inventory:" + inventory);

Hibernate:
    insert into ORMREL_INVENTORY (id, name) 
    values (null, ?)
Hibernate:
    insert into ORMREL_MEDIA (MEDIA_ID, title) 
    values (null, ?)
-created media:ejava.examples.orm.rel.annotated.Media@5ae9fa73, id=1, title=null, authors(0)={}
...
Hibernate:
    insert into ORMREL_MEDIA (MEDIA_ID, title) 
    values (null, ?)
-created media:Media@433a3459, id=5, title=null, authors(0)={}
-created inventory:Inventory@5b9e6638, id=1, name=testLinkCreate, media(5)={1,2,3,4,5,}
Hibernate:
    update ORMREL_MEDIA 
    set INVENTORY_ID=? 
    where MEDIA_ID=?
...
Hibernate:
    update ORMREL_MEDIA 
    set INVENTORY_ID=? 
    where MEDIA_ID=?

    create table ORMREL_BORROWER (
+---->  BORROWER_ID bigint not null,
|       endDate date,
|       startDate date,
|       primary key (BORROWER_ID)
|   )
|   create table ORMREL_CHECKOUT (
|       CHECKOUT_ID bigint generated by default as identity,
|       outDate date,
|       returnDate date,
`-----  CHECKOUT_BID bigint not null,
        primary key (CHECKOUT_ID)
    )
    alter table ORMREL_CHECKOUT 
        add constraint FK7F287E16C07B41F3 
        foreign key (CHECKOUT_BID) 
        references ORMREL_BORROWER

    @Entity @Table(name="ORMREL_CHECKOUT")

    public class Checkout {

        @Id @GeneratedValue @Column(name="CHECKOUT_ID")

        private long id;

    

  +-->  @ManyToOne(optional=false)

  |     @JoinColumn(name="CHECKOUT_BID")

+-----  private Borrower borrower;

| | 

| | @Entity @Table(name="ORMREL_BORROWER")

| | public class Borrower  {

| |     private static Log log = LogFactory.getLog(Borrower.class);

| |     @Id @Column(name="BORROWER_ID")

`---->  private long id;

  |     

  |     @OneToMany(mappedBy="borrower", //this relationship is owned by Checkout

  |             fetch=FetchType.LAZY)   //try to limit what we get back

  `---  private Collection<Checkout> checkouts = new ArrayList<Checkout>();

//get a borrower

Borrower borrower = em.find(Borrower.class, borrowerId);

assertNotNull(borrower);

assertTrue(borrower.getCheckouts().size() == 0);


//create 1st checkout

Checkout checkout = new Checkout(new Date());

checkout.setBorrower(borrower); //set owning side of the relation

borrower.addCheckout(checkout); //set inverse side of relation

    //wrapper around - borower.getCheckouts().add(checkout)

em.persist(checkout);   //persist owning side of the relation

Hibernate: -- query for the parent object, lazily loads child objects
    select
        borrower0_.BORROWER_ID as BORROWER1_15_0_,
        borrower0_.endDate as endDate2_15_0_,
        borrower0_.startDate as startDat3_15_0_ 
    from ORMREL_BORROWER borrower0_ 
    where borrower0_.BORROWER_ID=?

//get a borrower

//create a couple more

for(int i=0; i<5; i++) {

    Checkout co = new Checkout(new Date());

    co.setBorrower(borrower);   //set owning side of the relation

    borrower.addCheckout(co);   //set inverse side of relation

    em.persist(co);  //persist owning side of the relation

}

Hibernate:
    insert into ORMREL_CHECKOUT (CHECKOUT_ID, CHECKOUT_BID, outDate, returnDate) 
    values (null, ?, ?, ?)

//check the DB

em.flush(); em.clear();

Borrower borrower2 = em.find(Borrower.class, borrower.getId());

assertEquals(6, borrower2.getCheckouts().size());

    create table RELATIONEX_SUSPECT (
+---->  id integer generated by default as identity,
|       name varchar(32),
|       primary key (id)
|   )
|   create table RELATIONEX_SUSPECT_ALIASES (
`-----  SUSPECT_ID integer not null,
        ALIAS varchar(32),
        unique (SUSPECT_ID, ALIAS)
    )
    alter table RELATIONEX_SUSPECT_ALIASES 
        add constraint FK3FD160E6DE29C9CF 
        foreign key (SUSPECT_ID) 
        references RELATIONEX_SUSPECT

@Entity

@Table(name="RELATIONEX_SUSPECT")

public class Suspect {

    @Id @GeneratedValue

    private int id;

    @Column(length=32)

    private String name;

        

    @ElementCollection

    @CollectionTable(

            name="RELATIONEX_SUSPECT_ALIASES",

            joinColumns=@JoinColumn(name="SUSPECT_ID"), 

            uniqueConstraints=@UniqueConstraint(columnNames={"SUSPECT_ID", "ALIAS"}))

    @Column(name="ALIAS", length=32)

    private Set<String> aliases;

Suspect suspect = new Suspect();
suspect.setName("william");
em.persist(suspect);
suspect.getAliases().add("bill");
suspect.getAliases().add("billy");

@Entity

@Table(name="RELATIONEX_LICAPP")

public class LicenseApplication {

    @Id @GeneratedValue

    private int id;

    @Temporal(TemporalType.TIMESTAMP)

    private Date updated;

    

    @OneToOne(optional=false, fetch=FetchType.EAGER, 

            cascade={

                CascadeType.PERSIST,

                CascadeType.DETACH,

                CascadeType.REMOVE,

                CascadeType.REFRESH,

                CascadeType.MERGE

            })

    private License license;

@Entity

@Table(name="RELATIONEX_ATTENDEE")

public class Attendee {

    @Id @GeneratedValue

    private int id;

    

    //orphanRemoval will take care of dereference and DELETE from dependent Attendee 

    @OneToOne(cascade=CascadeType.PERSIST, orphanRemoval=true)

    private Residence residence;

attendee.setResidence(null);

em.flush();

Hibernate: 
    update RELATIONEX_ATTENDEE 
    set name=?, residence_id=? 
    where id=?
Hibernate: 
    delete from RELATIONEX_RESIDENCE 
    where id=?

    create table ORMREL_BORROWER (
+-----  BORROWER_ID bigint not null,
|       endDate date,
|       startDate date,
|       primary key (BORROWER_ID)
|   )
|   create table ORMREL_PERSON (
+---->  PERSON_ID bigint generated by default as identity,
        firstName varchar(255),
        lastName varchar(255),
        phone varchar(255),
        PERSON_PHOTO bigint,
        primary key (PERSON_ID)
    )
    alter table ORMREL_BORROWER 
        add constraint FKA0973E32F113D9BA 
        foreign key (BORROWER_ID) 
        references ORMREL_PERSON

    @Entity @Table(name="ORMREL_BORROWER")

    public class Borrower  {

        @Id @Column(name="BORROWER_ID")

+-----  private long id;

|       

|       @OneToOne(fetch=FetchType.LAZY, optional=false, 

|               cascade={CascadeType.PERSIST, 

|                        CascadeType.REFRESH,

|                        CascadeType.MERGE})

|       @PrimaryKeyJoinColumn  //the two tables will be joined by PKs

+-----  private Person identity;

|       

|   @Entity

|   @Table(name="ORMREL_PERSON")

|   public class Person  {

|       @Id @GeneratedValue @Column(name="PERSON_ID")

+---->  private long id;

    public Borrower(Person identity) {

        this.id = identity.getId();

        this.identity = identity;

    }

//create the person we'll use in the relationship

ejava.examples.orm.rel.annotated.Person person = new Person();

...

//create the Borrower, who requires a Person for its identity

ejava.examples.orm.rel.annotated.Borrower borrower = new Borrower(person);

borrower.setStartDate(new Date());


//persist the borrower, creating the relationship to person

em.persist(borrower);

log.info("created borrower:" + borrower);

assertEquals(person.getId(), borrower.getId()); //ctor copies PK         

    create table ORMREL_INVENTORY (

/---->  id bigint generated by default as identity,

|       name varchar(255),

|       primary key (id)

|   )

|   create table ORMREL_INVENTORY_MEDIA (

`-----  ORMREL_INVENTORY_id bigint not null,

/-----  media_MEDIA_ID bigint not null

|   )

|   create table ORMREL_MEDIA (

`---->  MEDIA_ID bigint generated by default as identity,

        title varchar(255),

        primary key (MEDIA_ID)

    )

    alter table ORMREL_INVENTORY_MEDIA 
        add constraint UK_F6FA5C31B7DAA951 unique (media_MEDIA_ID)
    alter table ORMREL_INVENTORY_MEDIA 
        add constraint FKF6FA5C31A70D4E48 
        foreign key (media_MEDIA_ID) 
        references ORMREL_MEDIA
    alter table ORMREL_INVENTORY_MEDIA 
        add constraint FKF6FA5C317DD5E49D 
        foreign key (ORMREL_INVENTORY_id) 
        references ORMREL_INVENTORY

    @Entity @Table(name="ORMREL_INVENTORY")

    public class Inventory {

        @Id @GeneratedValue

        private long id;

        private String name;

    

        @OneToMany(cascade={CascadeType.ALL})

        @JoinTable(name="ORMREL_INVENTORY_MEDIA")

/-----  private Collection<Media> media = new ArrayList<Media>();

|   

|   @Entity @Table(name="ORMREL_MEDIA")

|   public class Media  {    

|       @Id @GeneratedValue @Column(name="MEDIA_ID")

`---->  private long id;

        create table ORMREL_MEDIA (
+-------->  MEDIA_ID bigint generated by default as identity,
|           title varchar(255),
|           primary key (MEDIA_ID)
|       )
|       create table ORMREL_MEDIACOPY (
|           COPY_NO integer not null,
`---------  MEDIACOPY_MID bigint not null,
            primary key (COPY_NO, MEDIACOPY_MID)
        )
        alter table ORMREL_MEDIACOPY 
            add constraint FKCDB47669F152B359 
            foreign key (MEDIACOPY_MID) 
            references ORMREL_MEDIA

    @Entity @Table(name="ORMREL_MEDIACOPY2")

    @IdClass(MediaCopyPK2.class)

    @AttributeOverrides({

        @AttributeOverride(name="copyNo", column=@Column(name="COPY_NO"))

    })

    public class MediaCopy2 {

        @Id //mapped to COPY_NO by attribute override

        private int copyNo;    

        @Id 

        @ManyToOne 

        @JoinColumn(name="MEDIACOPY_MID")

+-----  private Media media;

|       

|       private MediaCopy2() {}

|       public MediaCopy2(Media media, int copyNo) {

|           this.media=media;

|           this.copyNo=copyNo;

|       }    

|   ...    

|   @Entity @Table(name="ORMREL_MEDIA")

|   public class Media  {

`---->  @Id @GeneratedValue @Column(name="MEDIA_ID")

        private long id;

ejava.examples.orm.rel.annotated.Media media = new Media();

media.setTitle("EJB31");


//add media to DB

assertTrue(media.getId() == 0);

em.persist(media);

log.info("created media:" + media);


//create some copies 

for(int i=0; i<5; i++) {

    ejava.examples.orm.rel.annotated.MediaCopy2 mc = 

        new MediaCopy2(media, i);

    assertNotNull(mc.getMedia());

    assertEquals(i, mc.getCopyNo());

    em.persist(mc);

    log.info("created copy:" + mc);

}

Hibernate: 
    insert into ORMREL_MEDIA (MEDIA_ID, title) 
    values (null, ?)
-created media:Media@51942b40, id=1, title=EJB31, authors(0)={}
-created copy:MediaCopy2@3787f275, mediaId=1, copyNo=0, media=Media@51942b40, id=1, title=EJB31, authors(0)={}
...
-created copy:MediaCopy2@7a7fdbb0, mediaId=1, copyNo=4, media=Media@51942b40, id=1, title=EJB31, authors(0)={}
Hibernate: 
    insert into ORMREL_MEDIACOPY2 (COPY_NO, MEDIACOPY_MID) 
    values (?, ?)
...    
Hibernate: 
    insert into ORMREL_MEDIACOPY2 (COPY_NO, MEDIACOPY_MID) 
    values (?, ?)

    create table ORMREL_WANTED (
+---->  id bigint generated by default as identity,
|       primary key (id)
|   )
|   create table ORMREL_WANTED_MEDIA (
`-----  ORMREL_WANTED_id bigint not null,
+-----  media_MEDIA_ID bigint not null
|   )
|   create table ORMREL_MEDIA (
`---->  MEDIA_ID bigint generated by default as identity,
        title varchar(255),
        primary key (MEDIA_ID)
    )
    alter table ORMREL_WANTED_MEDIA 
        add constraint FKEE528304A70D4E48 
        foreign key (media_MEDIA_ID) 
        references ORMREL_MEDIA
    alter table ORMREL_WANTED_MEDIA 
        add constraint FKEE52830486AFC6B6 
        foreign key (ORMREL_WANTED_id) 
        references ORMREL_WANTED    

    @Entity @Table(name="ORMREL_WANTED")

    public class WantList {

        @Id @GeneratedValue

        private long id;

        @ManyToMany

        @JoinTable(name="ORMREL_WANTED_MEDIA") //define table, let columns use default names

+-----  private Collection<Media> media = new ArrayList<Media>();

|      ...

|   @Entity @Table(name="ORMREL_MEDIA")

|   public class Media  {

|       @Id @GeneratedValue @Column(name="MEDIA_ID")

`---->  private long id;

        private String title;    

for(WantList w: wantLists) {

    for(Media m: media) {

        //we can only navigate this in one direction

        w.getMedia().add(m);

        log.info("added media(" + m.getId() + 

                ") to want list (" + w.getId() +")");

    }

}

 -added media(1) to want list (1)
-added media(2) to want list (1)
-added media(1) to want list (2)
-added media(2) to want list (2)
-added media(1) to want list (3)
-added media(2) to want list (3)
Hibernate: 
    insert into ORMREL_WANTED_MEDIA (ORMREL_WANTED_id, media_MEDIA_ID) 
    values (?, ?)
...

    create table ORMREL_AUTHOR (
+-----> id bigint generated by default as identity,
|       name varchar(255),
|       primary key (id)
|   )
|   create table ORMREL_AUTHOR_MEDIA (
`-----  LINK_AUTHOR_ID bigint not null,
+-----  LINK_MEDIA_ID bigint not null
|   )
|   create table ORMREL_MEDIA (
`---->  MEDIA_ID bigint generated by default as identity,
        title varchar(255),
        primary key (MEDIA_ID)
    )
    alter table ORMREL_AUTHOR_MEDIA 
        add constraint FKA0D2B4E09B01C6F2 
        foreign key (LINK_MEDIA_ID) 
        references ORMREL_MEDIA
    alter table ORMREL_AUTHOR_MEDIA 
        add constraint FKA0D2B4E089FFE922 
        foreign key (LINK_AUTHOR_ID) 
        references ORMREL_AUTHOR

    @Entity @Table(name="ORMREL_AUTHOR")

    public class Author {

        @Id @GeneratedValue

        private long id;

        private String name;

    

        @ManyToMany

        @JoinTable(name="ORMREL_AUTHOR_MEDIA", //defines the link table

                    //defines the column in the link table for author FK

                joinColumns={@JoinColumn(name="LINK_AUTHOR_ID")},

                    //defines the column in the link table for the media FK

                inverseJoinColumns={@JoinColumn(name="LINK_MEDIA_ID")})

  +-->  @OrderBy("title DESC") //order the list returned from database

+-----  private List<Media> media = new ArrayList<Media>();

| | 

| | @Entity @Table(name="ORMREL_MEDIA")

| | public class Media  {

| |     @Id @GeneratedValue @Column(name="MEDIA_ID")

| |     private long id;

| |     private String title;    

| `---- @ManyToMany(mappedBy="media")  //names property in Author that points to us

`-----> private Collection<Author> authors = new ArrayList<Author>();

for(Author a: authors) {

    for(Media m: media) {

        a.getMedia().add(m);

        log.info("added media(" + m.getId() + 

                ") to author (" + a.getId() +")");

        m.getAuthors().add(a);

        log.info("added author(" + a.getId() + 

                ") to media (" + m.getId() +")");

    }

}

 -added media(3) to author (1)
-added author(1) to media (3)
...
-added media(5) to author (5)
-added author(5) to media (5)
Hibernate: 
    insert into ORMREL_AUTHOR_MEDIA (LINK_AUTHOR_ID, LINK_MEDIA_ID) 
    values (?, ?)
...
Hibernate: 
    insert into ORMREL_AUTHOR_MEDIA (LINK_AUTHOR_ID, LINK_MEDIA_ID) 
    values (?, ?)