java - 使用 Join Table 和 Hibernate JPA 以及约束违规问题

Question

我已将代码从 Hibernate 2.x 升级到 5.3.7。最终在连接表周围出现错误。

想象一下涉及到两个表。 TableA、TableB 和 JoinTable，JoinTable 是 tablea 和 tableb 之间有链接的表。

问题出在 JoinTable 上，其中存在约束问题。

表 A 包含字段:ARCHDEFTableB 有字段:PART_REV_IDJoinTable 具有:ARCHDEF 和 PART_REV_ID。

在tableB中添加新条目时，针对JoinTable存在以下约束错误。

Bean Pojo 代码:

/// 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;
}

下面的 tx.commit 发生错误。

  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();
                }
            }

        }

    }

下面列出了错误。

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:

 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                                  

Answer 1

您的错误消息显示:

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

-803 代表 Db2 错误 SQL0803N，解释如下:

SQL0803N INSERT 语句、UPDATE 语句或由 DELETE 语句引起的外键更新中的一个或多个值无效，因为由索引 ID 标识的主键、唯一约束或唯一索引约束表 table-因索引键具有重复值而产生的名称。
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

这是关于上面页面中的 INSERT 语句的用户响应部分:

<小时/>

检查由index-id标识的索引的定义。
:
对于 INSERT 语句，检查对象表内容以确定指定值列表中的哪些值违反了唯一性约束。或者，如果 INSERT 语句包含子查询，则该子查询寻址的对象表内容必须与对象表内容进行匹配，以确定问题的原因。请注意，如果使用安全策略保护表，您的 LBAC 凭据可能不允许您查看导致错误的行。

如果索引位于 XML 列上并且语句是 INSERT 或 UPDATE，请考虑 XML 文档是否会导致单个 XML 文档中出现重复值。

<小时/>

因此您可能需要检查表和索引的定义。

希望这有帮助。