我已经将代码从Hibernate 2.x升级到5.3.7。最后，在联接表中出现了一个错误。
假设有两张桌子。表a、表B和作为在表a和表b之间具有链接的表的JoinTable。
该问题围绕JoinTable，其中存在约束问题。
表A包含字段：ARCHDEF表B包含字段：PART_REV_IDJoinTable具有：ARCHDEF AND PART_REV_ID。
在表B中添加新条目时，会出现下面针对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
您的错误消息显示：
DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505
-803表示Db2错误SQL0803N，这里有解释：
SQL0803N由DELETE语句引起的INSERT语句、UPDATE语句或外键更新中的一个或多个值无效，因为主键、唯一约束或索引id标识的唯一索引约束表表名不具有重复的索引键值
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文档中的值重复
因此，您可能需要检查表和索引的定义。
希望这能有所帮助。