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 ?

Solution 1

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/

Solution 2

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

Solution 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.

Solution 4

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");

View more solutions

Comments

[stdcall]

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.

[Arun P Johny]

Can you share the POJO class?

[stdcall]

There's no pojo, the resultset of the stored-procedure, is not mapped to a pojo, it's all scalars.

[stdcall]

The problem with this is that there's no inRange class... It sohuldn't be mapped, it's scallars.

[Arun P Johny]

Then probably you should look at using ResultTransformer as given in stackoverflow.com/questions/4863883/… along with stackoverflow.com/questions/602397/… to externalize the annotation

[stdcall]

I'm not confused, google a bit and you'll find what I'm pointing at.

[stdcall]

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...

[Sebastien Lorber]

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