Spring JPA / HIbernate - Persisting to H2 works fine but fails with Oracle with ORA-01400 "cann-CodePudding

I have 2 Oracle tables in a 1:M relationship:

EVENT(ID, NAME, DATE)

EVENT_LOG(ID, EVENT_ID, COMPLETED)

EVENT has PK=ID

EVENT_LOG has PK=ID and FK=EVENT_ID on EVENT.ID

Their corresponding JPA entities are defined like:

@Entity
@Table(name = "EVENT")
public class EventEntity implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    @Column(name = "ID")
    private Long id;

    @OneToMany(mappedBy = "event", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    private List<EventLogEntity> eventLogs;
    
    ...
}


@Entity
@Table(name = "EVENT_LOG")
public class EventLogEntity implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    @Column(name = "ID")
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "EVENT_ID")
    private EventEntity event;

}

Persisting is done by method:

private EventEntity saveEvent(EventDto eventDto) {

    EventEntity eventEntity = new EventEntity();

    ModelMapper modelMapper = new ModelMapper();
    modelMapper.map(eventDto, eventEntity);

    List<EventLogEntity> eventLogs = new ArrayList<EventLogEntity>();

    EventLogEntity eventLogEntity = new EventLogEntity();
    eventLogEntity.setCompleted(0); //0-not-completed, 1-completed
    eventLogs.add(eventLogEntity);

    eventEntity.setEventtLogs(eventLogs);

    EventEntity storedEvent = eventRepository.saveAndFlush(eventEntity);

    return storedEvent;
}

UPDATE

The EVENT and EVENT_LOG data is persisted as it should and I get data like:

EVENT(ID, NAME, DATE) = (1, 'send_msg', 20220210T11:55:10)

EVENT_LOG(ID, EVENT_ID, COMPLETED) = (1, 1, 0) // 0 = not sent

This is all good, but then I will receive response for send_msg event and I need to insert new EVENT_LOG insert for successfull send which should look like this:

EVENT_LOG(ID, EVENT_ID, COMPLETED) = (1, 1, 1) // 1 = sent

This is done by another method called saveEventLog:

private EventLog saveEventLog(EventEntity eventEntity) {

    EventLog eventLogEntity = new EventLog();
    eventLogEntity.setIsSent(1);
    eventLogEntity.setEvent(eventEntity); //WAS MISSING THIS LINE WHICH LED TO ERROR ORA-01400
    EventLog storedEventLog = eventLogRepository.saveAndFlush(eventLogEntity); 
    
    

    if (storedEventLog == null) {
        throw new Exception(...);
    }

    return storedEventLog;
}

PROBLEM

If I use H2 database, my entities are persisted like:

EVENT(ID, NAME, DATE) = (1, 'send_msg', 20220210T11:55:10)

EVENT_LOG(ID, EVENT_ID, COMPLETED) = (1, 1, 0)    //1ST ENTRY
EVENT_LOG(ID, EVENT_ID, COMPLETED) = (1, null, 1) //2ND ENTRY

The NULL for EVENT_ID above is not correct and it should be 1. This was fixed with the above missing line and is no longer concern

However, in Oracle, I get error ORA-01400: cannot insert NULL into ("MYDB"."EVENT_LOG"."EVENT_ID") and this error occurs in call to saveEventLog This was also fixed with the above missing line and is no longer concern

The reason for the above 2 issues was a missing line setting EVENT to EVENT_LOG connection: eventLogEntity.setEvent(eventEntity).

After adding this, it is working fine.

However, I would like to understand why on H2, I get NULL in EVENT_ID and no error whereas in Oracle, I get ORA-01400. I tried setting MODE=Oracle on H2 to make them behave consistently but I would still get record inserted into H2 without any issue.

CodePudding user response：

I think you are again missing to set the EventEntity for the EventLogEntity, which should be done like such:

EventLogEntity eventLogEntity = new EventLogEntity();
eventLogEntity.setCompleted(1);
eventLogEntity.setEventEntityDetails(eventEntity);
eventLogs.add(eventLogEntity);