how to call stored procedure using spring and hibernate

Question

I get an error message as java.lang.reflect.UndeclaredThrowableException and Invalid column name. Can you help me why am i getting this error.

Answer 1

I suggest following the recommendation of the documentation:

16.2.2.1. Rules/limitations for using stored procedures

You cannot use stored procedures with Hibernate unless you follow some procedure/function rules. If they do not follow those rules they are not usable with Hibernate. If you still want to use these procedures you have to execute them via session.connection(). The rules are different for each database, since database vendors have different stored procedure semantics/syntax.

Stored procedure queries cannot be paged with setFirstResult()/setMaxResults().

The recommended call form is standard SQL92: { ? = call functionName(<parameters>) } or { ? = call procedureName(<parameters>}. Native call syntax is not supported.

For Oracle the following rules apply:

• A function must return a result set. The first parameter of a procedure must be an OUT that returns a result set. This is done by using a SYS_REFCURSOR type in Oracle 9 or 10. In Oracle you need to define a REF CURSOR type. See Oracle literature for further information.

For Sybase or MS SQL server the following rules apply:

• The procedure must return a result set. Note that since these servers can return multiple result sets and update counts, Hibernate will iterate the results and take the first result that is a result set as its return value. Everything else will be discarded.
• If you can enable SET NOCOUNT ON in your procedure it will probably be more efficient, but this is not a requirement.

Once your code will be aligned with the rules, if you still get an error, please provide the full error message.

Answer 2

Not sure if this helps, but based on the error message you are getting about function name not exist, I assume your selectallemployments is a stored procedure, not some database function?

If that's the case, consider removing the parentheses.

<sql-query name="selectAllEmployments">
    { call opnsutil.selectallemployments }  
</sql-query>