over 9 years ago
Call stored procedure in JPA : Here I am going to tell you that how you can call the stored procedure using JPA entity manager. Following is the example to call stored procedure using entity manger.
Suppose we have a stored procedure called 'get_login_info' which will give you the user information by searching his/her username. Following the example code for calling stored procedure in JPA:
Example of calling stored procedure in JPA : First create a db stored procedure using following command in mysql.
- #-- create db stored procedure
- # Stored procedure to give user details when login
- DELIMITER $$
- CREATE DEFINER='root'@'localhost' PROCEDURE 'get_login_info'(IN userName varchar(300))
- BEGIN
- SET @SQLQuery = CONCAT("SELECT * FROM userInfo WHERE user_name ='" ,userName,"'");
- PREPARE stmt FROM @SQLQuery;
- EXECUTE stmt;
- END
#-- create db stored procedure
# Stored procedure to give user details when login
DELIMITER $$
CREATE DEFINER='root'@'localhost' PROCEDURE 'get_login_info'(IN userName varchar(300))
BEGIN
SET @SQLQuery = CONCAT("SELECT * FROM userInfo WHERE user_name ='" ,userName,"'");
PREPARE stmt FROM @SQLQuery;
EXECUTE stmt;
END
In your DAO code write the following code snipped :
- @SuppressWarnings("unchecked")
- public UserInfo findUserByName(String username){
- UserInfo user =null;
- try{
- entityManager.getTransaction().begin();
- List<UserInfo> results = entityManager
- .createNativeQuery("{call get_login_info(?)}", UserInfo.class)
- .setParameter(1, username)
- .getResultList();
- if(results.size()>0)user = (UserInfo) results.get(0);
- entityManager.getTransaction().commit();
- }catch (Exception e) {
- logger.error("Error at findUserByName funtion ", e);
- if (entityManager.getTransaction() != null) {
- entityManager.getTransaction().rollback();
- throw new ApplicationException("Database Exception.");
- }
- }
- return user;
- }
@SuppressWarnings("unchecked")
public UserInfo findUserByName(String username){
UserInfo user =null;
try{
entityManager.getTransaction().begin();
List<UserInfo> results = entityManager
.createNativeQuery("{call get_login_info(?)}", UserInfo.class)
.setParameter(1, username)
.getResultList();
if(results.size()>0)user = (UserInfo) results.get(0);
entityManager.getTransaction().commit();
}catch (Exception e) {
logger.error("Error at findUserByName funtion ", e);
if (entityManager.getTransaction() != null) {
entityManager.getTransaction().rollback();
throw new ApplicationException("Database Exception.");
}
}
return user;
}
So finally, in the above code "{call get_login_info(?)}" will call the stored procedure and return the result.
0 Comment(s)