how to call MySQL stored procedure in spring boot using hibernate?

Question

I have written some logic in MySQL stored procedure.I am using spring boot with hibernate. I have a login procedure with IN OUT parameters. From my login procedure I want to pass message to user. But i don't know how to call stored procedure in Spring boot. My code is bellow..

1. My Login Procedure is

   CREATE PROCEDURE login(IN  in_user_id    varchar(100),
                              IN  in_password   varchar(100),
                              OUT out_code      INT,
                              OUT out_message   varchar(100))
    BEGIN
       IF in_user_id IS NULL OR in_user_id = ''
        THEN
        SET out_code = 1;
        SET out_message = 'Please Enter Your First Name.';
      END IF;
    /*Logi Here*/
   
    END;
   

2. I have used entity class like

   @Entity
   @Table(name = "user")
    @NamedStoredProcedureQueries({
      @NamedStoredProcedureQuery(
      name = "do_login", 
      procedureName = "login", 
      resultClasses = { LoginModel.class },    
      parameters = { 
         @StoredProcedureParameter( name = " in_user_id",  type = String.class,  mode = ParameterMode.IN),
         @StoredProcedureParameter( name = "in_password",  type = String.class,  mode = ParameterMode.IN),
         @StoredProcedureParameter( name = "out_code",  type = Integer.class,  mode = ParameterMode.OUT), 
         @StoredProcedureParameter( name = "out_message",  type = String.class,  mode = ParameterMode.OUT)
   
        }),
   
    })
   
    public class LoginModel implements Serializable {
   
      @NotEmpty
      private String userid;
   
      @NotEmpty
      private String password;
   
     //Here is getter setter
     }
   

3. In My Login Controller I want to call my procedure so that i can forward my user to dashboard.If user enter wrong user id or password , i want to show message from procedure. I have used bellow code in my login controller

   @RequestMapping(value = "/login", method = RequestMethod.POST)
    public String doLogin(@ModelAttribute("webLoginForm") @Valid LoginModel registrationModel,
       BindingResult bindingResult, Model model, Errors error) {
        if(error.hasErrors()) {
            return "login";
        }
   
      // Here I want to check My Procedure result & redirect to welcome page
      //return "redirect:/welcome";
   
     return "login";
   }
   

4. I have used repository but it did not write anything here. I have used repository bellow like..

      public interface LoginRepository  extends CrudRepository<LoginModel, Integer>{
   
   
    }
   

Answer 1

You can call a stored procedure using javax.persistence.StoredProcedureQuery. You dont even need to declare anything on your entity.
I'd suggest moving the procedure calling logic to a service and then call the service method from your controller.

For instance:

@Service
public class LoginServiceImpl implements LoginService {

    @PersistenceContext
    private EntityManager entityManager;

    public Boolean checkUsernameAndPassword(String username, String password) {

        //"login" this is the name of your procedure
        StoredProcedureQuery query = entityManager.createStoredProcedureQuery("login"); 

        //Declare the parameters in the same order
        query.registerStoredProcedureParameter(1, String.class, ParameterMode.IN);
        query.registerStoredProcedureParameter(2, String.class, ParameterMode.IN);
        query.registerStoredProcedureParameter(3, Integer.class, ParameterMode.OUT);
        query.registerStoredProcedureParameter(4, String.class, ParameterMode.OUT);

        //Pass the parameter values
        query.setParameter(1, username);
        query.setParameter(2, password);

        //Execute query
        query.execute();

        //Get output parameters
        Integer outCode = (Integer) query.getOutputParameterValue(3);
        String outMessage = (String) query.getOutputParameterValue(4);

        return true; //enter your condition
    }
}

And then, you can call this method from your Controller, after injecting your LoginService.

Answer 2

Try this simple solution , I use Spring boot and Sql Server- In your Repository

1) with no parameter

@Query(value = "{call yourSpName()}", nativeQuery = true) List<Map<String, Object>> methodName();

2) with Parameter

@Query(value = "{call yourSpName(:param1)}", nativeQuery = true) List<Map<String, Object>> methodName(@Param("param1")Long param1);

Answer 3

You can try this method to call procedure from Spring

import java.util.HashMap;
import java.util.Map;
import oracle.jdbc.driver.OracleTypes;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;

public class MainApplicationMethod 

{

public static void main(String[] args) throws Exception {

    JdbcTemplate jdbcTemplate = new JdbcTemplate();

    SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate).withProcedureName("procedureName");
    jdbcCall.addDeclaredParameter(new SqlParameter("parameter1", OracleTypes.VARCHAR));
    jdbcCall.addDeclaredParameter(new SqlOutParameter("outParam1",OracleTypes.VARCHAR));
    String value=null;
    Map<String, String> callParams = new HashMap<String, String>();
    callParams.put("parameter1", value);

    Map<String, Object> outputMap = jdbcCall.execute(callParams);
}

}

Answer 4

This works for me using jdbctempate

   SimpleJdbcCall jdbcCall =  new SimpleJdbcCall(jdbcTemplate).withProcedureName("your procedure name");

    Map<String, Object> inParamMap = new HashMap<>();

    inParamMap.put("input of your procedure", value of input);

    SqlParameterSource in = new MapSqlParameterSource(inParamMap);

    Map<String, Object> call = jdbcCall.execute(in);