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..
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;
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
}
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";
}
I have used repository but it did not write anything here. I have used repository bellow like..
public interface LoginRepository extends CrudRepository{
}
解决方案
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.