With Join Table and Hibernate JPA and constraint violation issue

0
votes

I have upgraded code from Hibernate 2.x to 5.3.7.Final getting an error around the join table.

Imagine that there are two tables involved. TableA, TableB and a JoinTable which is a table with links between tablea and tableb.

The issue is around the JoinTable where there is a constraint issue.

TableA has fields: ARCHDEF TableB has fields: PART_REV_ID JoinTable has: ARCHDEF AND PART_REV_ID.

When adding a new entry in tableB, there is the constraint error below against the JoinTable.

Bean Pojo Code:

/// TABLEA
@Table( name = "DART_ARCHDEF_MASTER" )
public class Archdef implements Serializable {


     /// JOINTABLE
      @ManyToMany( targetEntity = PartRevision.class )
      //@JoinTable( name = "DART_ARCHDEF_PART", uniqueConstraints = { @UniqueConstraint(columnNames = "PART_REV_ID") }, joinColumns = { @JoinColumn( name = "ARCHDEF_NAME" ) }, inverseJoinColumns = { @JoinColumn( name = "PART_REV_ID" ) } )
      @JoinTable( name = "DART_ARCHDEF_PART", joinColumns = { @JoinColumn( name = "ARCHDEF_NAME" ) }, inverseJoinColumns = { @JoinColumn( name = "PART_REV_ID" ) } )
    private Set< PartRevision > parts;
 }


/// TABLE B
@Entity
@Table( name = "DART_PART_REVISION" )
public class PartRevision implements Serializable {

     @ManyToOne
     @JoinTable( name = "DART_ARCHDEF_PART", joinColumns = { @JoinColumn( name = "PART_REV_ID" ) }, inverseJoinColumns = { @JoinColumn( name = "ARCHDEF_NAME" ) } )
    private Archdef archdef;


        @Id
        @GeneratedValue( strategy = GenerationType.IDENTITY )
        @Column( name = "PART_REV_ID" )
    private int id;
}

Error happens on the tx.commit below.

  private void createDatabaseEntries() throws IOException, SvnException, InterruptedException {

        final String logMessage = message.getMessage();
        final String[] messageArray = logMessage.split( System.getProperty( "line.separator" ) );
        final String archdefName = messageArray[0];
        final List< SvnFile > partsChanged = getChangedParts();
        final Map< String, String > partsChangedMap = getChangedPartsMap();
        final String prQuery = "INSERT INTO {0}.DART_PART_REV_LEVEL(PART_REV_ID, ADDED_TS, LEVEL_ID) VALUES (:partRevId, CURRENT_TIMESTAMP, :levelId)";

        logger.info("SMARTSMIG-J8-1.4: POST COMMIT Running query against DART_PART_REV_LEVEL: "+prQuery);
        Transaction tx = null;
        Session session = null;
        Archdef archdef = null;
        Query archdefSelectQuery = null;
        Query partSelectQuery = null;
        Query levelSelectQuery = null;
        Query partRevisionQuery = null;
        Level level = null;
        PartRevision partRevision = null;
        Part part = null;
        String partFileLoc = null;
        Set< PartRevision > archdefPartsSet = null;

        if ( !"Initial check-in.".equals( archdefName ) && !"Initial tag.".equals( archdefName ) ) {

            try {

                session = HibernateUtil.getSessionFactory().openSession();
                tx = session.beginTransaction();

                levelSelectQuery = session.getNamedQuery( SmartsConstants.GET_LEVEL_BY_NAME );
                partSelectQuery = session.getNamedQuery( CommandConstants.GET_PARTS_HQL );
                archdefSelectQuery = session.getNamedQuery( SmartsConstants.GET_ARCHDEF_BY_NAME );

                archdefSelectQuery.setParameter( SmartsConstants.ARCHDEF_NAME, archdefName );

                archdef = ( Archdef ) archdefSelectQuery.uniqueResult();
                archdefPartsSet = archdef.getParts();

                levelSelectQuery.setParameter( SmartsConstants.LEVEL_NAME, SmartsConstants.DEV );
                level = ( Level ) levelSelectQuery.uniqueResult();

                logger.info("SMARTSMIG-J8-1.4 JHE-JY -------------- SvnFile file : partsChanged ");

                if (archdefPartsSet != null) {
                    for(final PartRevision partprt : archdefPartsSet) {
                      logger.info("SMARTSMIG-J8-1.4 - JHE-JY: Found Commit : xxxxx "+partprt.getId() + " ;; "+ partprt);
                    }
                }

                for ( SvnFile file : partsChanged ) {

                    if ( !file.getFileLoc().matches( fileFilter ) ) {

                        partFileLoc = file.getFileLoc();
                        partSelectQuery.setParameter( "partFileLoc", partFileLoc );                        
                        part = ( Part ) partSelectQuery.uniqueResult();                        
                        partRevision = new PartRevision();
                        partRevision.setPart( part );
                        partRevision.setUserId( author );
                        partRevision.setCommitted( Boolean.TRUE );
                        partRevision.setRevisionNumber( Integer.parseInt( svnArgument ) );
                        partRevision.setStatus( partsChangedMap.get( partFileLoc ).equals( "D" ) ? "D" : "A" );
                        partRevision.setArchdef(archdef);

                        logger.debug( partRevision );
                        session.save( partRevision );
                        if (!archdefPartsSet.contains(partRevision)) {
                            logger.info("SMARTSMIG-J8-1.4 - JHE-JY: Adding : archdefPartsSet.add( partRevision ) - JHE-JY");
                            logger.info("SMARTSMIG-J8-1.4 - JHE-JY: Adding : archdefPartsSet.add( partRevision ) - JHE-JY xxxx "
                                 +partRevision.getRevisionNumber() + " // " + partRevision.getArchdef() + " -- " + partRevision.getPart() + " :: "+partRevision.getId() );

                             archdefPartsSet.add( partRevision );
                        } else {
                            logger.warn("SMARTSMIG-J8-1.4 - JHE-JY: Not Adding : archdefPartsSet.add( partRevision ) - JHE-JY <duplicate> " + partRevision.getId());
                        }


                        if ( partsChangedMap.get( partFileLoc ).equals( "D" ) && partFileLoc.endsWith( "/" ) ) {

                            for ( PartRevision archdefPart : archdefPartsSet ) {
                                if ( archdefPart.getPart().getPartFileLoc().startsWith( partFileLoc ) ) {
                                    archdefPart.setStatus( "D" );
                                }
                            }

                            session.saveOrUpdate( archdef );

                        }

                        partRevisionQuery = session.createNativeQuery( prQuery.replaceAll( SmartsConstants.PARAM_0, System.getProperty( "db2.qualifier" ) ) );
                        partRevisionQuery.setParameter( "partRevId", partRevision.getId() );
                        partRevisionQuery.setParameter( "levelId", level.getId() );
                        partRevisionQuery.executeUpdate();



                    }

                }

                if (archdefPartsSet != null) {
                     logger.info("SMARTSMIG-J8-1.4 - JHE-JY: POST COMMIT Entering transaction commit and session save with archdefPartsSet.size() " +  archdefPartsSet.size());
                     // Attempting to check archderffile
                     for(final PartRevision partprt : archdefPartsSet) {
                         logger.info("SMARTSMIG-J8-1.4 - JHE-JY: Found Commit : "+partprt);
                     }
                }

                if ( !archdef.getLevel().equals( level ) ) {
                    archdef.setLevel( level );
                    session.saveOrUpdate( archdef );
                }

                tx.commit();

            } catch ( HibernateException h_ex ) {
                SmartsUtil.logException( h_ex );
                if ( tx != null ) {
                    tx.rollback();
                }
            } finally {
                if ( session.isOpen() ) {
                    session.close();
                }
            }

        }

    }

The error is listed below.

07 Nov 2019 11:48:25 ERROR [Thread-14] com.pfs.smarts.util.SmartsUtil.logException(SmartsUtil.java:212) : javax.persistence.PersistenceException: org.hiber                                                    nate.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.doFlush(SessionImpl.java:1460)
        at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:511)
        at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3283)
        at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2479)
        at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:473)
        at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalT                                                    ransactionCoordinatorImpl.java:178)
        at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300(JdbcResourceLocalTransactionCoor                                                    dinatorImpl.java:39)
        at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcRes                                                    ourceLocalTransactionCoordinatorImpl.java:271)
        at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:98)
        at com.pfs.smarts.subversion.hooks.command.PostCommitCheckCommand.createDatabaseEntries(PostCommitCheckCommand.java:306)
        at com.pfs.smarts.subversion.hooks.command.PostCommitCheckCommand.execute(PostCommitCheckCommand.java:59)
        at com.pfs.smarts.subversion.hooks.command.AbstractCommand.executeCommand(AbstractCommand.java:87)
        at com.pfs.smarts.server.svn.workers.ProcessRequestThread.run(ProcessRequestThread.java:87)
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:42)
        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:178)
        at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:45)
        at org.hibernate.persister.collection.AbstractCollectionPersister.insertRows(AbstractCollectionPersister.java:1575)
        at org.hibernate.action.internal.CollectionUpdateAction.execute(CollectionUpdateAction.java:85)
        at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
        at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:478)
        at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:356)
        at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39)
        at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1454)
        ... 12 more
Caused by: com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505, SQLERRMC=DARTARP1;00000083CA, DRIVER=4                                                    .26.14
        at com.ibm.db2.jcc.am.b7.a(b7.java:806)
        at com.ibm.db2.jcc.am.b7.a(b7.java:66)
        at com.ibm.db2.jcc.am.b7.a(b7.java:140)
        at com.ibm.db2.jcc.am.k4.b(k4.java:2471)
        at com.ibm.db2.jcc.am.k4.c(k4.java:2452)
        at com.ibm.db2.jcc.t4.ab.n(ab.java:914)
        at com.ibm.db2.jcc.t4.ab.a(ab.java:120)
        at com.ibm.db2.jcc.t4.p.a(p.java:50)
        at com.ibm.db2.jcc.t4.aw.b(aw.java:220)
        at com.ibm.db2.jcc.am.k5.bm(k5.java:3599)
        at com.ibm.db2.jcc.am.k5.a(k5.java:4644)
        at com.ibm.db2.jcc.am.k5.b(k5.java:4182)
        at com.ibm.db2.jcc.am.k5.be(k5.java:827)
        at com.ibm.db2.jcc.am.k5.executeUpdate(k5.java:801)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:384)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175)
        ... 20 more

SQL at time of error:

 insert                                                                   
    into IDSDPFS.DART_ARCHDEF_PART ( ARCHDEF_NAME , PART_REV_ID )         
  values ( ? , ? )                                                        

 HOST VARIABLE DATA USED WITH THIS SQL REQUEST                            
 PARAMETER MARKER     1         = 'COBJY10'                               
 PARAMETER MARKER     2         = 260403
1
javahibernatejpadb2
Removed redundant saves, still same issue - Berlin Brown
Check if your table DART_ARCHDEF_PART exists and also check if the user you are connecting from have access to the database. - Imran Khan
It is exists. Some inserts work but most don't especially when there are multiple PART_REV_id records. It exists, see the constraint error. - Berlin Brown
I don't see any constraint errors; SQLCODE=-204 means the table IDSDPFS.DART_ARCHDEF_MASTER_DART_PART_REVISION does not exist. Looks like you're supplying an incorrect value in System.getProperty( "db2.qualifier" ) - mustaccio
Updated with correct error - Berlin Brown

1 Answers

0
votes

Your error message says:

DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505

-803 represents Db2 Error SQL0803N and here is explanation:

SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by index-id constrains table table-name from having duplicate values for the index key.
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.messages.sql.doc/com.ibm.db2.luw.messages.sql.doc-gentopic4.html#sql0803n

Here is User response section about INSERT statement from above page:

Examine the definition for the index identified by index-id.
:
For an INSERT statement, examine the object table content to determine which of the values in the specified value list violates the uniqueness constraint. Alternatively, if the INSERT statement contains a subquery, the object table contents addressed by that subquery must be matched against the object table contents to determine the cause of the problem. Note that if the table is protected using a security policy, your LBAC credentials may not allow you to see the rows that are causing the error.

If the index is on an XML column and the statement is either INSERT or UPDATE, consider whether the XML document would result in duplicate values from within the single XML document.

So you may need to examine the definition of the table and index.

Hope this helps.