Java EE (Java Enterprise Edition) General Discussion

This is a JPA question. The tbHardware table has a PK identity column and a unique non-clustered index on CoxBarcode column.

I have a SFSB in a Seam2.0.0.GA app running on JBoss 4.2.1.GA. I am using flushMode=FlushModeType.MANUAL (Seam specific when beginning a conversation) and that's why you see the flush() reference at the end of the following code snippet.

Query query = entityManager.createNativeQuery("INSERT INTO tbHardware "+
									"VALUES (:coxBarCode, :serialNo, :currentStatus, :currentLocationNo, "+
									":desc, :hardwareModelId, :ownerTypeCode, :firstEnteredDate, :enteredByUser, :lastAuditDate, :hardwarePrice, null)")
								  .setParameter("coxBarCode", coxBarcode)
								  .setParameter("serialNo", serialNo)
								  .setParameter("currentStatus", curstatus)
								  .setParameter("currentLocationNo", curloc)
								  .setParameter("desc", desc)
								  .setParameter("hardwareModelId", selmodid)
								  .setParameter("ownerTypeCode", selectedOwner)
								  .setParameter("firstEnteredDate", firstEnteredDate)
								  .setParameter("enteredByUser", enteredByUser)
								  .setParameter("lastAuditDate", lastAuditedDate)
								  .setParameter("hardwarePrice", unitPrice);
					
					query.executeUpdate();
					
					query = entityManager.createNativeQuery("INSERT INTO TbHardwareHistory "+
								"VALUES (:hardwareId, :currentStatus, :currentLocationNo, :firstEnteredDate, null, :enteredByUser, :ownerTypeCode)")
								 .setParameter("hardwareId", findHardwareId(coxBarcode))								 
								 .setParameter("currentStatus", curstatus)
								 .setParameter("currentLocationNo", curloc)		
								 .setParameter("firstEnteredDate", firstEnteredDate)
								 .setParameter("enteredByUser", enteredByUser)
								 .setParameter("ownerTypeCode", selectedOwner);
					
					query.executeUpdate();
					
					//TO DO: following query should return only one entity, need to refactor and remove the for loop below
					
					TbHardware hw = (TbHardware)entityManager.createNativeQuery("SELECT t FROM tbHardware t WHERE t.coxBarCode = :coxBarCode AND t.serialNo = :serialNo", TbHardware.class)
																.setParameter("coxBarCode", coxBarcode)
																.setParameter("serialNo", serialNo)
																.getSingleResult();
										
					Integer hardwareId = hw.getHardwareId();
										
					query = entityManager.createNativeQuery("INSERT INTO tbHardwareNote VALUES (:hardwareId, :hardwareNote)")
					  				.setParameter("hardwareId", hardwareId).setParameter("hardwareNote", hardwareNote);
		
					query.executeUpdate();
					
					entityManager.flush();

I am getting the following in the server.log:

10:10:57,552 ERROR [STDERR] Caused by: org.hibernate.exception.SQLGrammarException: could not execute native bulk manipulation query
10:10:57,552 ERROR [STDERR] 	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
10:10:57,552 ERROR [STDERR] 	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
10:10:57,552 ERROR [STDERR] 	at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:174)
10:10:57,552 ERROR [STDERR] 	at org.hibernate.impl.SessionImpl.executeNativeUpdate(SessionImpl.java:1163)
10:10:57,552 ERROR [STDERR] 	at org.hibernate.impl.SQLQueryImpl.executeUpdate(SQLQueryImpl.java:334)
10:10:57,552 ERROR [STDERR] 	at org.hibernate.ejb.QueryImpl.executeUpdate(QueryImpl.java:48)
10:10:57,552 ERROR [STDERR] 	... 138 more
10:10:57,552 ERROR [STDERR] Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert duplicate key row in object 'dbo.tbHardware' with unique index 'IX_tbHardwar_coxBarCode_UNIQUE'.
10:10:57,552 ERROR [STDERR] 	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
10:10:57,552 ERROR [STDERR] 	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
10:10:57,552 ERROR [STDERR] 	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
10:10:57,552 ERROR [STDERR] 	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(Unknown Source)
10:10:57,552 ERROR [STDERR] 	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
10:10:57,552 ERROR [STDERR] 	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
10:10:57,552 ERROR [STDERR] 	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
10:10:57,552 ERROR [STDERR] 	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
10:10:57,552 ERROR [STDERR] 	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(Unknown Source)
10:10:57,552 ERROR [STDERR] 	at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:251)
10:10:57,552 ERROR [STDERR] 	at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:165)
10:10:57,552 ERROR [STDERR] 	... 141 more

How should I handle this SQLServerException? What is the best practice regarding this from a JPA perspective? I thought about executing a select query prior to the insert in tbHardware to check to see if a record with that particular coxBarCode exists or not. Would the exception be handled differently if I used the persist() method instead of the createNativeQuery() and executeUpdate() combination? thx.

Edited by: asookazian on May 7, 2008 11:07 AM