CODEWITHSUNDEEP
javaspringspring-mvcstored-proceduresjdbctemplate
stackUser2000
stackUser2000

Reputation: 1695

How to make a simple call to a stored procedure or function in spring mvc using JdbcTemplate

I have read a lot of questions about how to call a stored procedure using JdbcTemplate there are a lot of methods like using a beanMapper, creating a rowMapper, using callableStatement, but I have seen a lot of persons that say this:

For simple procedures you may use jdbcTemplate's update method:

jdbcTemplate.update("call SOME_PROC (?, ?)", param1, param2);

I tried doing that at and my jdbcTemplate variable is always null, this is my stored procedure

CREATE OR REPLACE PROCEDURE addProce
    (
       num1 IN number,
       num2 IN number,
       result OUT number
    )
IS
BEGIN
    result := num1 + num2;

END;
/

and this is the class where I call it 

public class UsuerDAOImpl implements UserDAO {

    private JdbcTemplate jdbcTemplate;

    public UsuerDAOImpl () {}

    public UsuerDAOImpl (DataSource datasource) {
        this.jdbcTemplate  =  new JdbcTemplate(datasource);
    }

     public int addmethodJdbc() 
     {
        int result= 0;
         int someValue= jdbcTemplate.update("addProce(?, ?, ?)", 2, 1, result);

        return someValue;
    }
}

I have this method in my class and it works my jdbctemplate is not null in there

public void insertUser(User user) {

    try {
        String sql = "INSERT INTO USER"
                + "(a, b, c, d)"
                + " VALUES (?, ?, ?, ?)";

        jdbcTemplate.update(sql, user.getA(),
                usaurio.getB(),
                usaurio.getC(),
                usaurio.getD());
    } 
    catch (DataAccessException dataAccessException) 
    {

        dataAccessException.printStackTrace();
    }
    catch(Exception e) 
    {

        e.printStackTrace();
    }
}

I also try with a function here it is :

CREATE OR REPLACE FUNCTION addFunct
    (
       num1 IN number,
       num2 IN number
    )
    return number
IS
resultado number;
BEGIN
    result := num1 + num2;
    return (result);
END;
/

but still dont work my jdbcTemplate is null too

I already know how to call them with the other ways, but I wanted to know how to call them in this easy way 

jdbcTemplate.update("call SOME_PROC (?, ?)", param1, param2);

Upvotes: 0

Views: 7834

Answers (1)

Abhishek Nayak
Abhishek Nayak

Reputation: 3748

use SimpleJdbcCall#withProcedureName() to call stored procedure. pass the name of the stored procedure as a parameter.

for example your method:

public int addmethodJdbc() {
   SimpleJdbcCall jdbcCall =  new SimpleJdbcCall(jdbcTemplate).withProcedureName("addFunct");
   SqlParameterSource in = new MapSqlParameterSource().addValue("num1", 2).addValue("num2", 1);

   Map<String, Object> out = jdbcCall.execute(in)
   int result = (Integer)out.get("result");
   return result;
}

Upvotes: 2

Related Questions