-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 5 posts ] 
  Previous topic | Next topic 
Author Message
jdwyah
 Post subject: Insertable Views & Understanding <sql-insert>
PostPosted: Wed Mar 29, 2006 9:23 pm 
Newbie

Joined: Wed Mar 29, 2006 8:42 pm
Posts: 7
The table I'm mapping to, SOAAPI.core_patients_s is an insertable view. It calls a db function that inserts or updates a patient. This function takes a patient_ID field, if null it proceeds with an insert creating the ID inn it's own secret way, otherwise it does an update with the ID passed.

I'm trying to figure out how to map this. What kind of ID generator do I want?

If I set my ID to assigned, I can't proceed with a save without setting my ID, so that doesn't seem to work.

Sequence ID's require a key generator on the DB that I can access, so that doesn't seem to work either.

I've been trying to use the <sql-insert> but to pass NULL for the ID, but I've run into the following issue. Any help would be appreciated, thanks.
-Jeff


Hibernate version: 3.0.5

Mapping documents:

<class name="CorePatientsS" table="SOAAPI.core_patients_s">
<id name="patientId" column="patient_id" type="java.lang.Integer">
<generator class="native"/>
</id>

<property name="firstName" column="first_name" type="java.lang.String" />
<property name="lastName" column="last_name" type="java.lang.String" />
<property name="applicationName" column="application_name" type="java.lang.String" />
<property name="accountId" column="account_id" type="java.lang.Integer" />


<sql-insert>INSERT INTO SOAAPI.core_patients_s (FIRST_NAME, LAST_NAME, APPLICATION_NAME, ACCOUNT_ID, PATIENT_ID) VALUES ( ?, ?, 'People', 569, NULL)</sql-insert>


</class>

Code between sessionFactory.openSession() and session.close():

Using Spring. calling getHibernateTemplate().save()





Full stack trace of any exception that occurs:

org.springframework.dao.DataIntegrityViolationException: Hibernate operation: could not insert: [com.mms.domain.mms.CorePatientsS]; SQL [INSERT INTO SOAAPI.core_patients_s (FIRST_NAME, LAST_NAME, APPLICATION_NAME, ACCOUNT_ID, PATIENT_ID) VALUES ( ?, ?, 'People', 569, NULL)]; The column index is out of range: 3, number of columns: 2.; nested exception is org.postgresql.util.PSQLException: The column index is out of range: 3, number of columns: 2.
org.postgresql.util.PSQLException: The column index is out of range: 3, number of columns: 2.
at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:38)
at org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:72)
at org.postgresql.jdbc2.AbstractJdbc2Statement.bindString(AbstractJdbc2Statement.java:2025)
at org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2Statement.java:1155)
at org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2Statement.java:1145)
at org.apache.commons.dbcp.DelegatingPreparedStatement.setString(DelegatingPreparedStatement.java:131)
at org.hibernate.type.StringType.set(StringType.java:24)
at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:62)
at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:39)
at org.hibernate.persister.entity.BasicEntityPersister.dehydrate(BasicEntityPersister.java:1617)
at org.hibernate.persister.entity.BasicEntityPersister.dehydrate(BasicEntityPersister.java:1594)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1850)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:2200)
at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:46)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:223)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:136)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:274)
at org.hibernate.event.def.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:48)
at org.hibernate.impl.SessionImpl.autoFlushIfRequired(SessionImpl.java:711)
at org.hibernate.impl.SessionImpl.prepareQueries(SessionImpl.java:895)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:885)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:834)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at org.springframework.orm.hibernate3.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:852)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:365)
at org.springframework.orm.hibernate3.HibernateTemplate.findByNamedParam(HibernateTemplate.java:843)
at com.mms.dao.hibernate.PatientDAOHibernateImpl.getPatientNamed(PatientDAOHibernateImpl.java:232)
at com.mms.dao.hibernate.PatientDAOHibernateImplTest.testSave(PatientDAOHibernateImplTest.java:60)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)



Name and version of the database you are using:
Postgres 8.0.0

The generated SQL (show_sql=true):
select nextval ('hibernate_sequence')
INSERT INTO SOAAPI.core_patients_s (FIRST_NAME, LAST_NAME, APPLICATION_NAME, ACCOUNT_ID, PATIENT_ID) VALUES ( ?, ?, 'People', 569, NULL)

Debug level Hibernate log excerpt:
5500 ms) [main] DEBUG: org.hibernate.jdbc.AbstractBatcher#logOpenPreparedStatement : about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
Hibernate: select nextval ('hibernate_sequence')
(5500 ms) [main] DEBUG: org.hibernate.jdbc.AbstractBatcher#getPreparedStatement : preparing statement
(5500 ms) [main] DEBUG: org.hibernate.jdbc.AbstractBatcher#logClosePreparedStatement : about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
(5500 ms) [main] DEBUG: org.hibernate.jdbc.AbstractBatcher#closePreparedStatement : closing statement
(5531 ms) [main] DEBUG: org.hibernate.jdbc.AbstractBatcher#logOpenPreparedStatement : about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
Hibernate: INSERT INTO SOAAPI.core_patients_s (FIRST_NAME, LAST_NAME, APPLICATION_NAME, ACCOUNT_ID, PATIENT_ID) VALUES ( ?, ?, 'People', 569, NULL)
(5531 ms) [main] DEBUG: org.hibernate.jdbc.AbstractBatcher#getPreparedStatement : preparing statement
(5531 ms) [main] DEBUG: org.hibernate.type.StringType#nullSafeSet : binding 'TESTFIRSTNAME' to parameter: 1
(5531 ms) [main] DEBUG: org.hibernate.type.StringType#nullSafeSet : binding 'TESTLAST' to parameter: 2
(5531 ms) [main] DEBUG: org.hibernate.type.StringType#nullSafeSet : binding 'People' to parameter: 3
(5531 ms) [main] DEBUG: org.hibernate.jdbc.AbstractBatcher#logClosePreparedStatement : about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
(5531 ms) [main] DEBUG: org.hibernate.jdbc.AbstractBatcher#closePreparedStatement : closing statement
(5547 ms) [main] WARN : org.hibernate.util.JDBCExceptionReporter#logExceptions : SQL Error: 0, SQLState: 22023
(5547 ms) [main] ERROR: org.hibernate.util.JDBCExceptionReporter#logExceptions : The column index is out of range: 3, number of columns: 2.
(5547 ms) [main] ERROR: org.hibernate.event.def.AbstractFlushingEventListener#performExecutions : Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: could not insert: [com.mms.domain.mms.CorePatientsS]
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:82)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1869)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:2200)
at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:46)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239)
Top
 Profile  
 
tenwit
 Post subject:
PostPosted: Wed Mar 29, 2006 10:23 pm 
Expert
Expert

Joined: Thu Dec 23, 2004 9:08 pm
Posts: 2008
"native" generator might do it. Failing that, you can wrte your own generator, base it off assigned, but don't complain about null as a valid id. Have a look at org.hibernate.id.Assigned: you'll see it's a very simple class, easily extended.
Top
 Profile  
 
jdwyah
 Post subject:
PostPosted: Thu Mar 30, 2006 11:05 am 
Newbie

Joined: Wed Mar 29, 2006 8:42 pm
Posts: 7
Hmm... tried extending Assigned with a new 'AssignedNoNullCheck' but I get errors further downstream.

It's certainly understandable that Hibernate wants non-null ID's for things , but is it possible to set the ID after I've saved? As the return of the insert?

org.springframework.orm.hibernate3.HibernateSystemException: null id generated for:class com.mms.domain.mms.CorePatientsS; nested exception is org.hibernate.id.IdentifierGenerationException: null id generated for:class com.mms.domain.mms.CorePatientsS
org.hibernate.id.IdentifierGenerationException: null id generated for:class com.mms.domain.mms.CorePatientsS
at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:89)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:184)
at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:33)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:173)
at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:27)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:69)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:481)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:476)
at org.springframework.orm.hibernate3.HibernateTemplate$12.doInHibernate(HibernateTemplate.java:615)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:365)
at org.springframework.orm.hibernate3.HibernateTemplate.save(HibernateTemplate.java:612)
at com.mms.dao.hibernate.PatientDAOHibernateImpl.save(PatientDAOHibernateImpl.java:181)
at com.mms.dao.hibernate.PatientDAOHibernateImplTest.testSave(PatientDAOHibernateImplTest.java:55)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Top
 Profile  
 
tenwit
 Post subject:
PostPosted: Thu Mar 30, 2006 5:44 pm 
Expert
Expert

Joined: Thu Dec 23, 2004 9:08 pm
Posts: 2008
Yep. I have one right here. This works off the Identity generator, and is essentially obsolete now (because of the guid generator), but it was written before the guid generator was (or at least, before we found out about it, here where I work). See if you can make use of any of this to help:
Code:
public class SQLServerGUIDGenerator
  implements IdentifierGenerator
{
  /**
   * @see IdentifierGenerator#generate(SessionImplementor, Object)
   */
  public Serializable generate(SessionImplementor session, Object object)
    throws HibernateException
  {
    try
    {
      CallableStatement stmt = null;
      try
      {
        stmt = session.connection().prepareCall("{call stpNewID(?)}");
        stmt.registerOutParameter(1, Types.VARCHAR);
        stmt.execute();

        return stmt.getString(1).toUpperCase();
      }
      finally
      {
        if (stmt != null)
        {
          stmt.close();
        }
      }
    }
    catch (SQLException e)
    {
      throw new HibernateException(e);
    }
  }
}
Top
 Profile  
 
jdwyah
 Post subject:
PostPosted: Mon Apr 03, 2006 9:39 am 
Newbie

Joined: Wed Mar 29, 2006 8:42 pm
Posts: 7
Right, I forgot that the Generator gets the session, that seems like it should work. I still have to get the DB admin to pull the key generator function out of a larger function etc, but I guess that was a given. Thanks for now.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 5 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.