java 如何使用hibernate在spring boot中调用MySQL存储过程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/48281009/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to call MySQL stored procedure in spring boot using hibernate?
提问by Enamul Haque
I have written some logic in MySQL stored procedure.I am using spring boot with hibernate. I have a login procedure with IN OUTparameters. 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..
我在MySQL 存储过程中编写了一些逻辑e.我正在使用带有 hibernate 的 spring boot。我有一个带有IN OUT参数的登录过程。从我的登录过程中，我想将消息传递给用户。但是我不知道如何在 Spring Boot 中调用存储过程。我的代码如下..
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<LoginModel, Integer>{ }
我的登录程序是
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;我使用过实体类
@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 或密码，我想显示来自程序的消息。我在登录控制器中使用了波纹管代码
@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"; }我使用了存储库，但它没有在这里写任何东西。我已经使用了存储库波纹管像..
public interface LoginRepository extends CrudRepository<LoginModel, Integer>{ }
回答by lzagkaretos
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.
您可以使用 javax.persistence.StoredProcedureQuery 调用存储过程。你甚至不需要在你的实体上声明任何东西。
我建议将过程调用逻辑移至服务，然后从控制器调用服务方法。
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.
然后，您可以在注入您的LoginService.
回答by VAIBHAV NERLE
Try this simple solution , I use Spring boot and Sql Server- In your Repository
试试这个简单的解决方案，我使用 Spring boot 和 Sql Server-在您的存储库中
1) with no parameter
1) 无参数
@Query(value = "{call yourSpName()}", nativeQuery = true)
List<Map<String, Object>> methodName();
@Query(value = "{call yourSpName()}", nativeQuery = true)
List<Map<String, Object>> methodName();
2) with Parameter
2) 带参数
@Query(value = "{call yourSpName(:param1)}", nativeQuery = true)
List<Map<String, Object>> methodName(@Param("param1")Long param1);
@Query(value = "{call yourSpName(:param1)}", nativeQuery = true)
List<Map<String, Object>> methodName(@Param("param1")Long param1);
回答by Ahmed Shalash
This works for me using jdbctempate
这对我使用 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);
回答by ashish singla
You can try this method to call procedure from Spring
您可以尝试使用此方法从 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);
}
}
}
