Calling Stored Procedure with Hibernate and Spring

Question

There are a lot of examples over the net which describe how to call a stored procedure using Hibernate, however, when using Spring, the picture changes a bit.

I have a stored procedure in MySQL which I want to call: in SQL I need to write the following:

CALL inrange(32.342324,32.234234);
It returns a row with the following: `{INT},{INT},{FLOAT}`

With Spring, I use the HibernateTemplate way of executing hibernate operations, I know that some of you won't like it, but this is the how the project was when I started, and I'm not so eager changing it, maybe in the future...

Currently, I have the following code in Java, which tries to call the procedure:

List<Object[]> resultset = hibernateTemplate
                               .findByNamedQuery("inrange",
                                    person.getAddress().getLatitude(),
                                    person.getAddress().getLongitude());

When I run it, I get the following Hibernate exception:

org.springframework.orm.hibernate3.HibernateSystemException:
   Named query not known: inrange;

I figured that this is happening duo the fact that I didn't declare the stored procedure in hibernate. My question is:

how do I declare it ?
Is there a special way of declaring it in the Spring's application context file ?

score 5 · Accepted Answer · answered Dec 09 '11 at 23:25

5

You can call native sql queries within hibernate.

Look at this link: http://www.mkyong.com/hibernate/how-to-call-store-procedure-in-hibernate/

Btw if you want to call stored procedures you could simply use a Spring JdbcTemplate.

Notice that an hibernate extension can fit to your needs: http://www.hibernatespatial.org/

answered Dec 09 '11 at 23:25

Sebastien Lorber

89,644
67
288
419

The Hibernatespatial looks very cool, however I'm using mySQL, and it appears there that most of the features are not supported on that DB, especially the distance function... – stdcall Dec 10 '11 at 01:50
Actually each DB provides its own features/functions and you can probably contribute to that project so that it works with mySQL too. Good luck – Sebastien Lorber Dec 13 '11 at 13:42

score 3 · Answer 2 · answered Dec 04 '11 at 21:11

You're confusing Hibernate's named queries with MySQL's stored procedures.

If you want to call the MySQL stored proc, there is no benefit to doing so through Hibernate's API. I recommend you use Spring's JdbcTemplate to perform the query.

If you absolutely must use Hibernate, something like this should work:

SQLQuery query = hibernateTemplate.getCurrentSession()
  .createSQLQuery("SELECT inrange(:latitude, :longitude)";
query.setDouble("latitude", ...);
query.setDouble("longitude", ...);
List<Object[]> result = query.list(); // requires casting for generics

I'm not confused, google a bit and you'll find what I'm pointing at. — stdcall, Dec 10 '11 at 01:49

Arun P Johny · Answer 3 · 2011-12-07T06:30:34.547

3

You need to add the named query to your hibernate mapping file.

Can you share your hibernate mapping file? You can find some samples here.

Along with the previous link you can go through this also.

It will be easier if you can share the POJO, hibernate mapping and the procedure you are using.

This blog will be of help for you. I hope you will not have any problem with using the getHibernateTemplate().execute(HibernateCallback) method.

edited Dec 07 '11 at 06:30

answered Dec 07 '11 at 06:10

Arun P Johny

384,651
66
527
531

Actually, I don't have an Hibernate mapping file. I'm using Spring's context XML & Annotations for hibernate mapping. I can post spring configuration if you find it helpful. – stdcall Dec 07 '11 at 06:18
Can you share the POJO class? – Arun P Johny Dec 07 '11 at 06:20
There's no pojo, the resultset of the stored-procedure, is not mapped to a pojo, it's all scalars. – stdcall Dec 07 '11 at 06:31
The problem with this is that there's no inRange class... It sohuldn't be mapped, it's scallars. – stdcall Dec 07 '11 at 06:38
Then probably you should look at using ResultTransformer as given in http://stackoverflow.com/questions/4863883/hibernate-mapping-custom-column-names-in-stored-procedure-named-query along with http://stackoverflow.com/questions/602397/how-do-i-externalize-named-queries-in-a-hibernate-annotations-app to externalize the annotation – Arun P Johny Dec 07 '11 at 06:39

Vlad Mihalcea · Answer 4 · 2021-01-12T14:46:58.390

You can use JPA as Spring supports it either in Core or Spring Data.

Calling the stored procedure can be done using the StoredProcedureQuery as follows:

StoredProcedureQuery query = entityManager
    .createStoredProcedureQuery("count_comments")
    .registerStoredProcedureParameter(
        "postId", Long.class, ParameterMode.IN)
    .registerStoredProcedureParameter(
        "commentCount", Long.class, ParameterMode.OUT)
    .setParameter("postId", 1L);

query.execute();

Long commentCount = (Long) query
    .getOutputParameterValue("commentCount");

Calling Stored Procedure with Hibernate and Spring

4 Answers4