Hibernate version: 2.17
Name and version of the database you are using: MS SQLServer 2000


To generate keys we use a table SYSTEM_KEYS.

CREATE TABLE [dbo].[SYSTEM_KEYS] (
[KEY_CODE] [T_LONG_NAME] NOT NULL ,
[NEXT_KEY] [T_IDENTIFIER] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SYSTEM_KEYS] WITH NOCHECK ADD
CONSTRAINT [PK_SYSTEM_KEYS] PRIMARY KEY NONCLUSTERED
(
[KEY_CODE]
) ON [PRIMARY]
GO

The key_code column specify the table for which we want to generate the key while the next_key column specifies the next unique key that can be used. So for tables ITEM, CATEGORY we can have data in this table, for example, as :

key_code next_key
---------------------------
GLOBALID 11111111
ITEM 1531
CATEGORY 25

the GLOBALID is used by default if we dont specify a key_code.

My question is how can I use it with hibernate?

I have an app server running JTA.

Thanks

forget to mention that in my table I am using user-defined datatype:

T_LONG_NAME is vrachar(50)
T_IDENTIFIER is numeric(18,0)

Write a custom IdGenerator

So I should write a class which implements IdentifierGenerator interface? Which current strategy it will be more like? When I am done how can I identify in my mapping file?

Hmm.. We already have a stored procedure where the logic is written to generate keys.

CREATE PROC GetNextKey
@a_NextKey numeric(18,0) output,
@a_KeyCode varchar(50) = null
AS

begin

declare @v_CommitTransaction integer
declare @v_ReturnCode numeric(18,0)
declare @v_StartKey numeric(18,0)
declare @v_DefaultKeyCode varchar(50)
declare @v_KeyCode varchar(50)

set @v_DefaultKeyCode = 'GLOBALID'
set @v_CommitTransaction = 0
set @v_KeyCode = isNull( @a_KeyCode, @v_DefaultKeyCode )

-- Manage separate transaction if not invoked from within another transaction.
if ( @@trancount = 0 )
begin
begin transaction getnextkey
set @v_CommitTransaction = 1
end

select @a_NextKey = next_key
from system_keys
where key_code = @v_KeyCode

if ( @@rowcount <> 1 )
begin
set @v_ReturnCode = -1
goto error_handler
end

update system_keys
set next_key = ( @a_NextKey + 1 )
where key_code = @v_KeyCode

set @v_ReturnCode = @@error
if ( @v_ReturnCode <> 0 )
begin
goto error_handler
end

if ( @v_CommitTransaction = 1 )
begin
commit transaction getnextkey
end

return @v_ReturnCode


error_handler:
if ( @v_CommitTransaction = 1 )
begin
rollback transaction getnextkey
end
return @v_ReturnCode


end




My question is can I use this stored procedure to generate keys instead of writing my own code?
Also I am concerned about transactions. Suppose from a Hibernate transaction I called this stored proc and it generate a key. Another user also called this stored proc from his own transaction and he get the key after me (My key + 1). What will happen when something failed in my transaction and its rollback by Hibernate?

ok i created my Custom IdGenerator whicih closely resembles the hilo strategy. It is :

package com.sen.util;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import net.sf.hibernate.HibernateException;
import net.sf.hibernate.MappingException;
import net.sf.hibernate.dialect.Dialect;
import net.sf.hibernate.engine.SessionImplementor;
import net.sf.hibernate.id.Configurable;
import net.sf.hibernate.id.IdentifierGenerationException;
import net.sf.hibernate.id.IdentifierGenerator;
import net.sf.hibernate.type.Type;
import net.sf.hibernate.util.PropertiesHelper;

public class MyTestIdGenerator implements IdentifierGenerator, Configurable {

/**
* The keycode parameter
* The keycode specified will be used for the type of key (default: GLOBALID)
*/
public static final String KEYCODE = "keycode";

private static final Log log = LogFactory.getLog(MyTestIdGenerator.class);

private String keycodeName;
private String query;
private String update;


/* (non-Javadoc)
* @see net.sf.hibernate.id.IdentifierGenerator#generate(net.sf.hibernate.engine.SessionImplementor, java.lang.Object)
*/
public synchronized Serializable generate(SessionImplementor session, Object object)
throws SQLException, HibernateException {

// This has to be done using a different connection to the
// containing transaction because the new hi value must
// remain valid even if the containing transaction rolls
// back
Connection connection = session.getBatcher().openConnection();
int result;
int rows;
try {
connection.setAutoCommit(false);

do {
// The loop ensures atomicity of the
// select + update even for no transaction
// or read committed isolation level

PreparedStatement qps = connection.prepareStatement(query);
try {
ResultSet rs = qps.executeQuery();
if ( !rs.next() ) {
String err = "could not read a hi value - you need to populate the table: SYSTEM_KEYS";
log.error(err);
throw new IdentifierGenerationException(err);
}
result = rs.getInt(1);
rs.close();
}
catch (SQLException sqle) {
log.error("could not read a hi value", sqle);
throw sqle;
}
finally {
qps.close();
}

System.out.println( "MyTestIdGenerator: generate: result = " + result );

PreparedStatement ups = connection.prepareStatement(update);
try {
ups.setInt( 1, result + 1 );
ups.setInt( 2, result );
rows = ups.executeUpdate();
}
catch (SQLException sqle) {
log.error("could not update hi value in: SYSTEM_KEYS", sqle);
throw sqle;
}
finally {
ups.close();
}
}
while (rows==0);

connection.commit();

return new Integer(result);

}
finally {
session.getBatcher().closeConnection(connection);
}

}

/* (non-Javadoc)
* @see net.sf.hibernate.id.Configurable#configure(net.sf.hibernate.type.Type, java.util.Properties, net.sf.hibernate.dialect.Dialect)
*/
public void configure(Type type, Properties params, Dialect dialect)
throws MappingException {

this.keycodeName = PropertiesHelper.getString(KEYCODE, params, "GLOBALID");

// build the query statement
query = "select NEXT_KEY from SYSTEM_KEYS where KEY_CODE = '" + keycodeName + "'";

// build the update statement
update = "update SYSTEM_KEYS set NEXT_KEY = ? where NEXT_KEY = ?" + " and KEY_CODE = '" + keycodeName + "'";

System.out.println( "MyTestIdGenerator.java: configure: keycodeName = " + keycodeName );
System.out.println( "MyTestIdGenerator.java: configure: query = " + query );
System.out.println( "MyTestIdGenerator.java: configure: update = " + update );
}

}


Change my Location.hbm.xml to use the new Custom IdGenerator :

<id
name="locationId"
type="java.lang.Integer"
column="location_id"
>
<generator class="com.sen.util.MyTestIdGenerator">
<param name="keycode">LOCATION</param>
</generator>
</id>

In my java code I am not using the setId() method of Location class:

Location aLocation = new Location();
aLocation.setName("Detroit");
aLocationDAO.createLocation( aLocation );

When I try to update, I get this error message :

2004-12-09 18:00:19,558 DEBUG [net.sf.hibernate.util.JDBCExceptionReporter] SQL Exception
java.sql.SQLException: You cannot set autocommit during a managed transaction!
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.setJdbcAutoCommit(BaseWrapperManagedConnection.java:440)
at org.jboss.resource.adapter.jdbc.WrappedConnection.setAutoCommit(WrappedConnection.java:446)
at com.sen.util.MyTestIdGenerator.generate(MyTestIdGenerator.java:52)
at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(SessionImpl.java:765)
at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:738)
at com.sen.main.dao.base.DAOHibernate.createObject(DAOHibernate.java:45)
at com.sen.main.dao.hibernate.LocationDAOHibernate.createLocation(LocationDAOHibernate.java:57)
at org.apache.jsp._2_jsp._jspService(_2_jsp.java:87)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:137)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:210)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.jboss.web.tomcat.security.JBossSecurityMgrRealm.invoke(JBossSecurityMgrRealm.java:220)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.jboss.web.tomcat.tc4.statistics.ContainerStatsValve.invoke(ContainerStatsValve.java:76)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2417)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:65)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:577)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:197)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:781)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:549)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:605)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:677)
at java.lang.Thread.run(Thread.java:534)
2004-12-09 18:00:19,558 WARN [net.sf.hibernate.util.JDBCExceptionReporter] SQL Error: 0, SQLState: null
2004-12-09 18:00:19,558 ERROR [net.sf.hibernate.util.JDBCExceptionReporter] You cannot set autocommit during a managed transaction!
2004-12-09 18:00:19,574 ERROR [net.sf.hibernate.util.JDBCExceptionReporter] Could not save object
java.sql.SQLException: You cannot set autocommit during a managed transaction!
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.setJdbcAutoCommit(BaseWrapperManagedConnection.java:440)
at org.jboss.resource.adapter.jdbc.WrappedConnection.setAutoCommit(WrappedConnection.java:446)
at com.sen.util.MyTestIdGenerator.generate(MyTestIdGenerator.java:52)
at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(SessionImpl.java:765)
at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:738)
at com.sen.main.dao.base.DAOHibernate.createObject(DAOHibernate.java:45)
at com.sen.main.dao.hibernate.LocationDAOHibernate.createLocation(LocationDAOHibernate.java:57)
at org.apache.jsp._2_jsp._jspService(_2_jsp.java:87)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:137)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:210)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.jboss.web.tomcat.security.JBossSecurityMgrRealm.invoke(JBossSecurityMgrRealm.java:220)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.jboss.web.tomcat.tc4.statistics.ContainerStatsValve.invoke(ContainerStatsValve.java:76)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2417)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:65)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:577)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:197)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:781)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:549)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:605)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:677)
at java.lang.Thread.run(Thread.java:534)
2004-12-09 18:00:19,590 INFO [STDOUT] Trying to rollback database transaction of this thread.
2004-12-09 18:00:19,590 DEBUG [net.sf.hibernate.transaction.JTATransaction] rollback
2004-12-09 18:00:19,590 DEBUG [net.sf.hibernate.impl.SessionImpl] transaction completion
2004-12-09 18:00:19,590 INFO [STDOUT] HibernateSession: rollbackTransaction() successfully executed.
2004-12-09 18:00:19,590 INFO [STDOUT] HibernateSession: Closing Session of this thread.
2004-12-09 18:00:19,590 DEBUG [net.sf.hibernate.impl.SessionImpl] closing session
2004-12-09 18:00:19,590 INFO [STDOUT] HibernateSession: closeSession() successfully executed.
2004-12-09 18:00:19,621 DEBUG [net.sf.hibernate.impl.SessionImpl] running Session.finalize()

Any reason? Thanks

You can't use HiLo in a managed transaction environment (you need a different new connection for the generator) so the same applies for your code.