Multiple similar onetomany cause database error

Leto · **Joined:** Fri Mar 31, 2006 5:18 am **Posts:** 9

Hi folks,

I am having a problem with the following relationships (summarized).

Code:

class Mailbox
{
    @OneToMany(fetch=FetchType.LAZY,cascade=CascadeType.ALL)
    @OrderBy(value="date desc")
    private Collection<Message> sent;
    
    
    @OneToMany(fetch=FetchType.LAZY,cascade=CascadeType.ALL)
    @OrderBy(value="date desc")
    private Collection<Message> drafts;
    
    
    @OneToMany(fetch=FetchType.LAZY,cascade=CascadeType.ALL)
    @OrderBy(value="date desc")
    private Collection<Message> received;
}

Code:

class Message
{
    @Column(nullable=false,length=3000
    ,columnDefinition="VARCHAR(3000) CHARACTER SET UTF8")
    String body;

    @Column(nullable=false)
    @Temporal(value=TemporalType.TIMESTAMP)
    private Date date;
}

Using hibernate 3.1cr1, annotations beta9, entitymanager beta7 and mysql 5.0.11.

Hibernate generates the following join table with appropriate foreign keys (trying to keep the post small, so I cut them out).

Code:

    create table dtg_mailbox_dtg_message (
        dtg_mailbox_id varchar(32) not null,
        received_id varchar(32) not null,
        drafts_id varchar(32) not null,
        sent_id varchar(32) not null,
        unique (drafts_id),
        unique (received_id),
        unique (sent_id)
    ) type=InnoDB;

Adding a message to drafts creates the following SQL.

Code:

    insert 
        into
            dtg_mailbox_dtg_message
            (dtg_mailbox_id, drafts_id) 
        values
            (?, ?)
09:55:41,843 DEBUG StringType:80 - binding '402882960a830363010a830371580001' to parameter: 1
09:55:41,843 DEBUG StringType:80 - binding '402882960a830363010a830371770003' to parameter: 2

Which (you may have guessed the next bit) causes the following error.

Code:

09:55:41,859  WARN JDBCExceptionReporter:71 - SQL Error: 1364, SQLState: HY000
09:55:41,859 ERROR JDBCExceptionReporter:72 - Field 'received_id' doesn't have a default value

This has a bug feel about it, since that join table can never be correct. I could probably fix it with a @JoinColumn annotation, or by having inverse mappings from the target entity, but I still can't shake the feeling that this should have worked and generated correct ddl.

Any pointers for the bemused?

Many thanks.

emmanuel · **Posted:** Sun Apr 16, 2006 1:19 pm

you don't show enough of your mapping

Leto · **Joined:** Fri Mar 31, 2006 5:18 am **Posts:** 9

I'd rather not show too much of my mapping, so to help I've constructed a simplified example that I can post in full. It shows the problem in an easily reproducable way.

I didn't add accessor methods, because I am testing for generated DDL, and not business logic.

First the classes, "First" and "Second", with two independant relationships between them.

Code:

package com.washery.test;

import javax.persistence.*;
import java.util.*;

@Entity(name="tst_first") 
public class First
{
    @Id()
    private Long id;
    
    @OneToMany()
    private Collection<Second> rel1;
    
    @OneToMany()
    private Collection<Second> rel2;        
}

Code:

package com.washery.test;

import javax.persistence.*;

@Entity(name="tst_second")
public class Second
{
    @Id()
    private Long id;
}

They compile and generate the following ddl.

Code:

    create table First (
        id bigint not null,
        primary key (id)
    ) type=InnoDB;

    create table First_Second (
        First_id bigint not null,
        rel2_id bigint not null,
        rel1_id bigint not null,
        unique (rel2_id),
        unique (rel1_id)
    ) type=InnoDB;

    create table Second (
        id bigint not null,
        primary key (id)
    ) type=InnoDB;

    alter table First_Second 
        add index FKD6AB4923F7A1DB84 (rel1_id), 
        add constraint FKD6AB4923F7A1DB84 
        foreign key (rel1_id) 
        references Second (id);

    alter table First_Second 
        add index FKD6AB492391D47EEC (First_id), 
        add constraint FKD6AB492391D47EEC 
        foreign key (First_id) 
        references First (id);

    alter table First_Second 
        add index FKD6AB4923F7A24FE3 (rel2_id), 
        add constraint FKD6AB4923F7A24FE3 
        foreign key (rel2_id) 
        references Second (id);

Notice that there is only one generated join table named 'First_Second', that has two fields referencing the target entity, where they both have the not null constraint.

This means that if I attempt to place a reference to 'Second' in either collection of 'First' there will be a database exception, since one of those fields is guarenteed to be null (assuming I don't add it to both collections, but what would be the point of that).

I'll tell the truth, this isn't holding me up, it's easy to work around and I have done so. Either specify the join table name in both cases to be different, or use 'mappedBy' to push the relationship onto the target entity.

If you tell me that it's a consequence of following the EJB3 standard, then that's fine, I can accept that (and even suspect it). It just seems a bit odd.

emmanuel · **Posted:** Mon May 01, 2006 4:23 pm

The default column name only works when you have one relationship between 2 entities, you'll have to use @JoinTable and make your association table explicit

javidjamae · **Joined:** Wed Oct 04, 2006 8:11 pm **Posts:** 6

Yes, I solved a similar problem by doing the following:

Code:

public class First
{
    @Id()
    private Long id;
   
    @OneToMany()
    @JoinTable(name="First_Rel1")
    private Collection<Second> rel1;
   
    @OneToMany()
    @JoinTable(name="First_Rel2")
    private Collection<Second> rel2;       
}