Hello,
I am using hibernate 3.1.3 running from within JBoss 4.0.2, not as JMX bean but called from a stateless session bean that provides persistence services. The database is Oracle 9.2.0.4.0.
The problem I am having relates to binding and how hibernate is mapping that to jdbc calls.
Here is my hibernate.config.xml file:
Code:
<hibernate-configuration>
<session-factory>
<!-- Settings that apply to managed environments -->
<property name="connection.datasource">java:/BlueSpaceDS</property>
<property name="transaction.factory_class">org.hibernate.transaction.JTATransactionFactory</property>
<property name="transaction.manager_lookup_class">org.hibernate.transaction.JBossTransactionManagerLookup</property>
<!-- SQL dialect -->
<property name="dialect">org.hibernate.dialect.Oracle9Dialect</property>
<!-- Disable the second-level cache -->
<property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
<mapping resource="com/bluespace/core/persistence/config/hibernate/DeadLetter.hbm.xml"/>
</session-factory>
</hibernate-configuration>
Here is the DeadLetter.hbm.xml file
Code:
<hibernate-mapping>
<class name="com.bluespace.core.persistence.dlq.DeadLetterPersistentEntity" entity-name="DeadLetter" table="DLQ">
<id name="jmsMessageId" column="jmsid">
<generator class="native"/>
</id>
<property name="subscriberClassName" column="subscribertype"/>
<property name="redeliveryCounter" column="redeliverycount"/>
<property name="eventBytes" column="message"/>
</class>
<sql-query name="findByPeriod" callable="true">
<return alias="dlq" entity-name="DeadLetter">
<return-property name="jmsMessageId" column="jmsid"/>
<return-property name="eventBytes" column="message"/>
<return-property name="subscriberClassName" column="subscribertype"/>
<return-property name="redeliveryCounter" column="redeliverycount"/>
</return>
{ :result = call findByPeriod(:start, :end) }
</sql-query>
</hibernate-mapping>
Here is my current code that binds the stored procedure:
Code:
SessionFactory sessionFactory = HibernateSessionFactory.getInstance();
Session session = sessionFactory.openSession(businessContext.getConnection(this.getDataSource()));
Query finder = session.getNamedQuery(FIND_BY_PERIOD_NAME);
finder.setInteger("result", 0);
if (periodStart != null) {
Calendar start = TimeKeeper.getCalendarInstance();
start.setTimeInMillis(periodStart.getTime());
finder.setCalendar(PERIOD_START_BINDING, start);
}
else {
finder.setCalendar(PERIOD_START_BINDING, null);
}
if (periodEnd != null) {
Calendar end = TimeKeeper.getCalendarInstance();
end.setTimeInMillis(periodEnd.getTime());
finder.setCalendar(PERIOD_END_BINDING, end);
}
else {
finder.setCalendar(PERIOD_END_BINDING, null);
}
List deadLetters = finder.list();
...
protected static final String PERIOD_START_BINDING = "start";
protected static final String PERIOD_END_BINDING = "end";
The call "finder.setInteger("result", 0);" was inspired by
http://forum.hibernate.org/viewtopic.php?t=958728&highlight=markerobject which helped me getting rid of the ClassCastException.
The problem that I continue to get is:
Caused by: java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
at oracle.jdbc.driver.OraclePreparedStatement.setNullCritical(OraclePreparedStatement.java:3703)
at oracle.jdbc.driver.OraclePreparedStatement.setNullInternal(OraclePreparedStatement.java:3594)
at oracle.jdbc.driver.OracleCallableStatement.setNull(OracleCallableStatement.java:4162)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.setNull(WrappedPreparedStatement.java:351)
at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:55)
at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:44)
at org.hibernate.loader.custom.CustomLoader.bindNamedParameters(CustomLoader.java:166)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1145)
at org.hibernate.loader.Loader.doQuery(Loader.java:373)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:210)
at org.hibernate.loader.Loader.doList(Loader.java:1561)
... 65 more
When I use the oracle specific syntax:
Code:
BEGIN result := findByPeriod(:start, :end); END;
I get the strange error that not all named parameters are bound:
17:33:22,213 ERROR [LogInterceptor] RuntimeException in method: public abstract java.util.Collection com.bluespace.core.persistence.dlq.DeadLetterPersistenceManager.findByPeriod(com.bluespace.core.implementation.common.BusinessContext,java.sql.Timestamp,java.sql.Timestamp) throws javax.ejb.FinderException:
org.hibernate.QueryException: Not all named parameters have been set: [] [BEGIN :result := findByPeriod(:start, :end); END;]
at org.hibernate.impl.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:115)
at org.hibernate.impl.SQLQueryImpl.verifyParameters(SQLQueryImpl.java:165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:138)
at com.bluespace.core.persistence.dlq.DeadLetterPersistenceManagerBean.findByPeriod(DeadLetterPersistenceManagerBean.java:180)
Can anybody help me out here please?
Rudi