hibernate4调用mysql存储过程,如何使用hibernate在spring boot中调用MySQL存储过程？

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.