如何使用hibernate在spring boot中调用MySQL存储过程？ [英] how to call MySQL stored procedure in spring boot using hibernate?

问题描述

我在 MySQL存储过程中编写了一些逻辑 e。我正在使用 spring boot with hibernate 。我有一个带 IN OUT 参数的登录程序。从我的登录程序，我想传递给用户的消息。但我不知道如何在Spring启动时调用存储过程。我的代码如下..

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. 我的登录程序是

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;

我使用了实体类，如

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
  }

在我的登录控制器中我想调用我的程序，以便我可以将我的用户转发到仪表板。如果用户输入错误的用户ID或密码，我想显示来自程序的消息。我在登录控制器中使用了以下代码

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<LoginModel, Integer>{


 }

推荐答案

您可以使用javax.persistence.StoredProcedureQuery调用存储过程。您甚至不需要在您的实体上声明任何内容。

我建议将过程调用逻辑移动到服务，然后从控制器调用服务方法。

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.

例如：

@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
    }
}

然后，你可以打电话在注入 LoginService 之后，从Controller中获取此方法。

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

这篇关于如何使用hibernate在spring boot中调用MySQL存储过程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！