Search code examples
javahibernatejpa

How can I get JPA hibernate to not adding a value NULL, when field is not set (null), when inserting into a db/2 default value field


I am using Java and Hibernate JPA (5.6.15.Final) to write an application to store/change/delete contracts and processing data.

  • two database tables in a IBM DB/2 database (simplified):
CREATE TABLE "Schema1"."Contracts" (
        "IDX" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY, 
        "DATA" varchar(255));

CREATE TABLE "Schema1"."Processes" (
        "IDX" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY, 
        "CONTRACTIDX" INTEGER NOT NULL DEFAULT 0, 
        "DATA" varchar(255));

The database that is used, is already existing, it is very old and other software is using it also (for one of the apps, we do not even have source code), so we cannot easily make changes to the tables and field definitions.

The reason, why CONTRACTIDX may be 0 by default is, that it should be possible to process before there is a contract here (that means: we could also be processing, when we are in long-running contract negotiations, and we could be processing, when we already have a contract) So valid values for CONTRACTIDX should be an existing contract id or 0 for no contract.

  • the entities:

src/main/java/mypackage/ProcessDTO.java

@Entity
@Table(schema = "Schema1", name = "Processes")
public class ProcessDTO
{
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "IDX")
  private Long id = null;

  @ManyToOne
  @JoinColumn(name = "CONTRACTIDX" /* , nullable=true or nullable=false: I've already tried that... it has no effect on the problem */)
  private ContractDTO contract = null;

  @Column(name="DATA")
  private String data;

  // getters and setters
}

src/main/java/mypackage/ContractDTO.java

@Entity
@Table(schema = "SCHEMA1", name = "Contracts")
public class ContractDTO {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "IDX")
  private Long id;

  @Column(name="DATA")
  private String data;

  // getters and setters
}

src/main/resources/META-INF/persistence.xml:

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
  version="2.0">
  <persistence-unit name="myPu">
    <jta-data-source>jdbc/myDb</jta-data-source>
    
    <class>mypackage.ProcessDTO</class>
    <class>mypackage.ContractDTO</class>
   
    <properties>
      <property name="hibernate.dialect" value= "org.hibernate.dialect.DB2Dialect" />
      <property name="hibernate.show_sql" value="true" />
      <property name="hibernate.format_sql" value="true" />
    </properties>
  </persistence-unit>
</persistence>

Now comes my problem: When running this with code like this:

src/main/java/mypackage/Main.java:

public class Main {

  public static void main(String[] args)
  {
    EntityManagerFactory emf = Persistence.createEntityManagerFactory("myPu");
    EntityManager em = emf.createEntityManager();

    em.getTransaction()
        .begin();
    try
    {
      // case 1: there is no contract
      ProcessDTO process = new ProcessDTO();
      process.setData("XYZ");
      em.merge(process);

      // case 2: there is a contract
      ContractDTO contract = new ContractDTO();
      contract.setData("abc");
      contract = em.merge(contract);

      ProcessDTO process2 = new ProcessDTO();
      process2.setContract(contract);
      process2.setData("def");

      em.merge(process2);
    }
    finally
    {
      em.getTransaction()
          .commit();
    }

    em.getTransaction()
        .begin();
    try
    {
      ProcessDTO process1 = new ProcessDTO();
      process1.setData("abc");
      em.merge(process1);

      ProcessDTO process2 = new ProcessDTO();
      process2.setData("def");
      em.merge(process2);
    }
    finally
    {
      em.getTransaction()
          .commit();
    }

    em.getTransaction()
        .begin();
    try
    {
      TypedQuery<ProcessDTO> query =
          em.createQuery("from ProcessDTO where data='abc'", ProcessDTO.class);

      List<ProcessDTO> processes = query.getResultList();
      if (!processes.isEmpty())
      {
        ProcessDTO process = processes.get(0);
        process.setData("abcdef");
        process = em.merge(process);
      }

      TypedQuery<ProcessDTO> queryToDelete =
          em.createQuery("from ProcessDTO where data='def'", ProcessDTO.class);
      List<ProcessDTO> processesToDelete = queryToDelete.getResultList();
      if (!processesToDelete.isEmpty())
      {
        ProcessDTO process = processesToDelete.get(0);

        em.remove(process);
      }
    }
    finally
    {
      em.getTransaction()
          .commit();
    }
  }
}

DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502, SQLERRMC=TBSPACEID=3, TABLEID=24, COLNO=1, DRIVER=4.19.66 The reason for this error is, that JPA tries a statement like this:

INSERT INTO Processes(CONTRACTIDX, DATA) values (NULL, 'XYZ');

which the database doesn't allow.

Case 1: 'there is no contract' should generate something like

INSERT INTO Processes(DATA) values ('XYZ');

Case 2: 'there is a contract' should generate something like

INSERT INTO Contracts(DATA) values ('abc');
INSERT INTO Processes(CONTRACTIDX, DATA) values (<the id value of that contract>, 'def');

A related question: I then need a solution for the problem of processes, that have CONTRACTIX==0. They should be accepted as if they were NULL;

Complete Stacktrace of Case 1:

Exception in thread "main" javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188)
    at org.hibernate.internal.SessionImpl.fireMerge(SessionImpl.java:840)
    at org.hibernate.internal.SessionImpl.merge(SessionImpl.java:816)
    at de.continentale.mvp.jpatest001.Main.main(Main.java:39)
Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:59)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:37)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:200)
    at org.hibernate.dialect.identity.GetGeneratedKeysDelegate.executeAndExtract(GetGeneratedKeysDelegate.java:58)
    at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:43)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3279)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3914)
    at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:84)
    at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:645)
    at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:282)
    at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:263)
    at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:317)
    at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:329)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:286)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:192)
    at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:122)
    at org.hibernate.event.internal.DefaultMergeEventListener.saveTransientEntity(DefaultMergeEventListener.java:273)
    at org.hibernate.event.internal.DefaultMergeEventListener.entityIsTransient(DefaultMergeEventListener.java:246)
    at org.hibernate.event.internal.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:178)
    at org.hibernate.event.internal.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:74)
    at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:107)
    at org.hibernate.internal.SessionImpl.fireMerge(SessionImpl.java:829)
    ... 2 more
Caused by: com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502, SQLERRMC=TBSPACEID=2, TABLEID=265, COLNO=1, DRIVER=4.19.66
    at com.ibm.db2.jcc.am.kd.a(kd.java:743)
    at com.ibm.db2.jcc.am.kd.a(kd.java:66)
    at com.ibm.db2.jcc.am.kd.a(kd.java:135)
    at com.ibm.db2.jcc.am.fp.c(fp.java:2788)
    at com.ibm.db2.jcc.am.fp.a(fp.java:2236)
    at com.ibm.db2.jcc.t4.bb.o(bb.java:908)
    at com.ibm.db2.jcc.t4.bb.j(bb.java:267)
    at com.ibm.db2.jcc.t4.bb.d(bb.java:55)
    at com.ibm.db2.jcc.t4.p.c(p.java:44)
    at com.ibm.db2.jcc.t4.vb.j(vb.java:157)
    at com.ibm.db2.jcc.am.fp.nb(fp.java:2231)
    at com.ibm.db2.jcc.am.gp.b(gp.java:4542)
    at com.ibm.db2.jcc.am.gp.mc(gp.java:815)
    at com.ibm.db2.jcc.am.gp.executeUpdate(gp.java:789)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
    ... 21 more

Maven dependencies from pom.xml:

        <dependency>
            <groupId>jakarta.enterprise</groupId>
            <artifactId>jakarta.enterprise.cdi-api</artifactId>
            <version>2.0.2</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>jakarta.inject</groupId>
            <artifactId>jakarta.inject-api</artifactId>
            <version>1.0</version>
        </dependency>
        <dependency>
            <groupId>jakarta.persistence</groupId>
            <artifactId>jakarta.persistence-api</artifactId>
            <version>2.2.3</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>5.6.15.Final</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>jakarta.transaction</groupId>
            <artifactId>jakarta.transaction-api</artifactId>
            <version>1.3.3</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.jboss.narayana.jta</groupId>
            <artifactId>narayana-jta</artifactId>
            <version>5.12.7.Final</version>
        </dependency>

Java: openjdk version "1.8.0_222"


Solution

  • I found a solution... but don't like it very much... is there a better (shorter, clearer) one?

    I changed my ProcessDTO:

    @Entity
    @Table(schema = "Schema1", name = "Processes")
    public class ProcessDTO
    {
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      @Column(name = "IDX")
      private Long id = null;
    
      @ManyToOne
      @NotFound(action = NotFoundAction.IGNORE)
      @JoinColumn(name = "CONTRACTIDX",
          insertable = false,
          updatable = false,
          foreignKey = @ForeignKey(ConstraintMode.NO_CONSTRAINT))
      private ContractDTO contract = null;
    
      @Column(name = "CONTRACTIDX")
      private Long contractId = null;
    
      @Column(name = "DATA")
      private String data;
    
      // getters and setters
    
      @PrePersist
      @PreUpdate
      void beforeSave()
      {
        if (contract != null)
        {
          contractId = contract.getId();
        }
        else
        {
          contractId = 0L;
        }
      }
    
      @PostLoad
      void afterLoad()
      {
        if (contractId == 0L)
        {
          contractId = 0L;
          contract = null;
        }
      }
    }
    
    

    One more 'bad thing' on it is, that I need hibernate-native annotations: NotFound and NotFoundAction...

    I would prefer something from the jpa-interface...