如何在Spring Boot+Hibernate中调用带IN OUT参数的MySQL存储过程

我来帮你搞定Spring Boot调用带IN/OUT参数的MySQL存储过程的问题，咱们一步步调整代码，完美实现你的需求：

1. 修正实体类的存储过程注解 #

你的实体类注解有两个小问题：参数名多了空格，而且不需要指定resultClasses（因为咱们用的是OUT参数，不是返回结果集）。修改后的实体类如下：

@Entity
@Table(name = "user")
@NamedStoredProcedureQueries({
    @NamedStoredProcedureQuery(
        name = "LoginModel.do_login", // 用实体类名+方法名的格式，避免命名冲突
        procedureName = "login",
        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 {
    private static final long serialVersionUID = 1L;
    
    @NotEmpty
    private String userid;
    @NotEmpty
    private String password;

    // 标准getter、setter方法
    public String getUserid() { return userid; }
    public void setUserid(String userid) { this.userid = userid; }
    public String getPassword() { return password; }
    public void setPassword(String password) { this.password = password; }
}

2. 更新Repository接口，添加存储过程调用方法 #

Spring Data JPA支持直接用@Procedure注解调用存储过程，返回的Map会自动包含所有OUT参数的键值对：

import org.springframework.data.jpa.repository.Procedure;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.util.Map;

@Repository
public interface LoginRepository extends CrudRepository<LoginModel, Integer> {

    // 注解name要和实体类里的@NamedStoredProcedureQuery.name完全一致
    @Procedure(name = "LoginModel.do_login")
    Map<String, Object> executeLogin(
        @Param("in_user_id") String userId,
        @Param("in_password") String password
    );
}

3. 调整Controller逻辑，处理存储过程返回结果 #

注入Repository后，调用存储过程，根据返回的out_code和out_message做跳转或错误提示：

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.validation.BindingResult;
import org.springframework.validation.Errors;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import java.util.Map;

@Controller
public class LoginController {

    @Autowired
    private LoginRepository loginRepository;

    @RequestMapping(value = "/login", method = RequestMethod.POST)
    public String doLogin(@ModelAttribute("webLoginForm") @Valid LoginModel loginModel, 
                          BindingResult bindingResult, Model model, Errors error) {
        // 先处理前端表单验证错误
        if (bindingResult.hasErrors() || error.hasErrors()) {
            return "login";
        }

        // 调用存储过程，获取返回的参数Map
        Map<String, Object> resultMap = loginRepository.executeLogin(loginModel.getUserid(), loginModel.getPassword());
        
        // 取出OUT参数
        Integer outCode = (Integer) resultMap.get("out_code");
        String outMessage = (String) resultMap.get("out_message");

        // 根据业务逻辑处理：假设out_code=0表示登录成功
        if (outCode != null && outCode == 0) {
            return "redirect:/dashboard"; // 跳转到仪表盘
        } else {
            // 把错误信息存入Model，在登录页展示
            model.addAttribute("errorMessage", outMessage);
            return "login";
        }
    }
}

4. 完善登录页的错误信息展示（以Thymeleaf为例） #

在你的login.html里添加错误提示区域，用来显示存储过程返回的错误信息：

<!-- 放在登录表单上方或合适位置 -->
<div th:if="${errorMessage}" class="alert alert-danger">
    <strong>错误提示:</strong> <span th:text="${errorMessage}"></span>
</div>

5. 补充MySQL存储过程的完整逻辑 #

你的存储过程目前只处理了空用户ID的情况，建议补充完整的验证逻辑，比如：

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.'; 
    ELSEIF in_password IS NULL OR in_password = '' THEN
        SET out_code = 2;
        SET out_message = 'Please Enter Your Password.';
    ELSE
        -- 验证用户账号密码是否匹配
        SELECT COUNT(*) INTO @user_count FROM user WHERE user_id = in_user_id AND password = in_password;
        IF @user_count > 0 THEN
            SET out_code = 0;
            SET out_message = 'Login Success.';
        ELSE
            SET out_code = 3;
            SET out_message = 'Invalid User ID or Password.';
        END IF;
    END IF;
END;

内容的提问来源于stack exchange，提问作者Enamul Haque