CODEWITHSUNDEEP
mysqlhibernatespringstored-procedures
stdcall
stdcall

Reputation: 28870

Calling Stored Procedure with Hibernate and Spring

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:

Upvotes: 3

Views: 18441

Answers (4)

Vlad Mihalcea
Vlad Mihalcea

Reputation: 153730

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

Upvotes: 1

Sebastien Lorber
Sebastien Lorber

Reputation: 92120

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/

Upvotes: 5

Arun P Johny
Arun P Johny

Reputation: 388316

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.

Upvotes: 3

jnovak
jnovak

Reputation: 41

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

Upvotes: 3

Related Questions