Hello,

I have a sql function uGetSeq() which returns an incremental alphanumeric value. I want to call it from my web application which is based on Hibernate3 and Struts.

I created a NamedQueries.hbm.xml and I put it into the place where all other hbm.xml files are located too.
NamedQueries.hbm.xml:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="de.laliluna.library" >
<sql-query name="selectPaymentRefId" callable="true">
<return alias="ref" class="Payment">
<return-property name="referenceNo" column="REF_NO"/>
</return>
{ ? = call uGetSeq() }
</sql-query>
</hibernate-mapping>

Then I configured my hibernate.config.xml accordingly:

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
<session-factory>
<!-- mysql configuration -->
<property name="connection.url">jdbc:mysql://localhost/LibraryWeb</property>
<property name="connection.username">root</property>
<property name="connection.password"></property>
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>

<!-- Enable Hibernate's automatic session context management -->
<property name="current_session_context_class">thread</property>
<property name="connection.autocommit">false</property>
<property name="show_sql">true</property>

<!-- mapping files -->
<mapping resource="de/laliluna/library/Invoice.hbm.xml" />
<mapping resource="de/laliluna/library/InvoiceItem.hbm.xml" />
<mapping resource="de/laliluna/library/Payment.hbm.xml" />
<mapping resource="de/laliluna/library/User.hbm.xml" />
<mapping resource="de/laliluna/library/Account.hbm.xml" />
<mapping resource="de/laliluna/library/NamedQueries.hbm.xml" />
</session-factory>
</hibernate-configuration>

Then I called the sql script within application code (please see below for the whole code segment):

String str = (session.createSQLQuery("selectPaymentRefId")).toString();

But it does not work, in the corresponding GUI field, "SQLQueryImpl(selectPaymentRefId)"
is displayed and not a "T000001" is displayed as expected. Do you have any suggestions for me?
Thanks.

public void savePayment(Payment paymentValue) {
/* a Hibernate session */
Session session = null;
/* we always need a transaction */
Transaction tx = null;

/* get session of the current thread */
session = HibernateSessionFactory.currentSession();

int userId=getCurrentUserId();
User user = (User) session.get(User.class, userId);
Payment payment;

tx = session.beginTransaction();
if (paymentValue.getId() == null ||paymentValue.getId().intValue() == 0) {
// create payment
paymentValue.setUserId(userId);
paymentValue.setUser(user);

String str = (session.createSQLQuery("selectPaymentRefId")).toString();

paymentValue.setReferenceNo(str);

session.save(paymentValue);

}

tx.commit();
}