CODEWITHSUNDEEP
springstored-procedures
Gruber
Gruber

Reputation: 4568

Calling DB stored routines in Spring

I have MySQL stored routines and need to call them from my Java Spring application. Currently I do it like this:

result = org.springframework.jdbc.core.JdbcTemplate.query(
     "CALL MyRoutine(?, ?);", 
     myRowMapper, 
     parameterOne, 
     parameterTwo);

Questions:

Upvotes: 0

Views: 498

Answers (2)

Philippe Marschall
Philippe Marschall

Reputation: 4604

  1. The approach you chose uses vendor syntax instead of JDBC call syntax (see Syntax of JDBC Connection prepareCall SQL) and is therefore not portable across databases (this may not be an issue for you). Spring JDBC offers many different ways to call a stored procedure, there is no obvious best approach.
  2. This is safe against SQL injection unless the stored procedure itself is vulnerable to SQL injection (concatenates the parameters into a query).
  3. You can bind parameters for stored procedures by name instead of index using the #setX(String, X) methods on CallableStatement. This may not be supported by your database driver. This is different from NamedParameterJdbcTemplate which still binds by index. Also note that NamedParameterJdbcTemplate has some overhead as it parses and rewrites your query.

Upvotes: 1

Wim Deblauwe
Wim Deblauwe

Reputation: 26888

You can get around the parameter order by using NamedParameterJdbcTemplate. See http://static.springsource.org/spring/docs/3.0.x/reference/jdbc.html

Upvotes: 1

Related Questions