Stored Procedure and Custom Identifier Generators

beppegg · **Joined:** Fri Mar 30, 2007 10:50 am **Posts:** 2

Hello,

I've got to integrate to legacy database.

For inserting, I have to rely upon a Stored Procedure which outputs the generated Primary Key as parameter.

I've tried to write my own StoredProcedureGenerator extending AbstractPostInsertGenerator. My problem is that I cannot use the configured <sql-insert> in any way, because in the inner StoredProcGeneratorDelegate extends AbstractReturningDelegate I have to overload prepareIdentifierGeneratingInsert, which returns the "default" Hibernate insert!

How can I retrieve my own mapped <sql-insert>?

Secondly, I'd like to customize the Binder used for associate parameters in the PreparedStatement, so that I can configure the position of the output parameter. I've found no way for doing that!

Any help would be appreciated! ;-)

david · **Posted:** Sun Apr 01, 2007 12:42 am

Hibernates support for native sql and SPs is great but often I find it simply easier to use straight JDBC for this sort of requirement.

beppegg · **Joined:** Fri Mar 30, 2007 10:50 am **Posts:** 2

Thanks for your answer!!!

Do you think I should submit a bug/request for enhancement into JIRA in order to obtain support for custom post-insert generators and custom SQL for Insert?

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Some serious threadomancy here, but I found this thread, maybe you will too..

I've created JIRA issue HHH-3735 about this, but for the moment, I have a workaround. I did pretty much what beppegg tried in the first post, but insted of using plain-old IdentifierGeneratingInsert inside the prepareIdentifierGeneratingInsert method of my AbstractReturningDelegate subclass, I used my own subclass. It ignores all the columns etc., and instead returns the sql-insert string, unmodified. And because I can't get at the sql-insert element in the class mapping, I simply duplicated it in the generator element and made my custom StoredProcedureGenerator implement Configurable.

The rest of the code in my AbstractReturningDelegate subclass is fairly straightforward JDBC:

Code:

    @Override
    protected PreparedStatement prepare(String sql, SessionImplementor session) throws SQLException
    {
      CallableStatement stmt = session.getBatcher().prepareCallableStatement(sql);
      stmt.registerOutParameter(outputParamIdx,
                                idType.sqlTypes(session.getFactory())[0]);
      return stmt;
    }

    @Override
    protected Serializable executeAndExtract(PreparedStatement stmt) throws SQLException
    {
      stmt.execute();

      CallableStatement callable = (CallableStatement) stmt;
      Object id = callable.getObject(outputParamIdx);
      return (Serializable) id;
    }

I suppose that I could have tried a bit harder to figure out the type of the id in executeAndExtract, but that code is simple, and higher layers of hibernate code do it already, so I'm happy with this.