I have a question regarding what is the best approach to using stored procs in mysql with hibernate. I am running mysql version 5.7.14 with hibernate 4.0.0.Final as my ORM tool. Now in mysql database, I have a stored proc defined below:
DROP PROCEDURE IF EXISTS LK_spInsertBaseUser;
DELIMITER $$
CREATE PROCEDURE `LK_spInsertBaseUser`(f_name VARCHAR(255),
l_name VARCHAR(255),
n_name VARCHAR(255),
pwd VARCHAR(255),
OUT user_id INT)
BEGIN
## Declaring exit handler
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
@state = RETURNED_SQLSTATE,
@errno = MYSQL_ERRNO,
@message = MESSAGE_TEXT;
SET @full_error = CONCAT('ERROR ', @errno, ' (', @state, '): ', @message);
SELECT @full_error;
ROLLBACK;
END;
START TRANSACTION;
IF NOT EXISTS(SELECT first_name
FROM base_user
WHERE first_name = f_name AND last_name = l_name AND nick_name = n_name)
THEN
INSERT INTO base_user (first_name, last_name, nick_name, password)
VALUES (f_name, l_name, n_name, pwd);
SET user_id = LAST_INSERT_ID();
SELECT @user_id AS userId;
ELSE
SET @exiting_user = CONCAT('Base user already exists');
SELECT @exiting_user;
ROLLBACK;
END IF;
END $$
DELIMITER ;
As we can see from my proc above, if the insert works, the id of the new record is stored in the OUT parameter user_id and we do a select as well. However, if there is a error I print out the error. Now, here is the heart of the question. I ran into a few hiccups when trying to execute the stored proc via hibernate. I finally came up with a solution but I am not convinced it is the right solution. Let me go through the various attempts i went through.
Attempt 1: I decided to use @NamedNativeQueries annotation for my BaseUser Entity (note: base_user sql table maps to BaseUser pojo entity). Below is the code snippet:
@SqlResultSetMapping(name="insertUserResult", columns = { @ColumnResult(name = "userId")})
@NamedNativeQueries({
@NamedNativeQuery(
name = "spInsertBaseUser",
query = "CALL LK_spInsertBaseUser(:firstName, :lastName, :nickName, :password, @user_id)",
resultSetMapping = "insertUserResult"
)
})
Now, in the Dao class I created a method to invoke the named query via the session object like so:
Query query = getSession().getNamedQuery("spInsertBaseUser")
.setParameter("firstName", user.getFirstName())
.setParameter("lastName", user.getLastName())
.setParameter("nickName", user.getNickName())
.setParameter("password", user.getEncodedPassword());
Object data = query.list();
System.out.println(data);
Now this works partially. It inserts the data into the database however the data object is null. It seems the out parameter isn't set or even retrieved. I then decided to use a different approached and use the CallableStatement object. Below is the code:
Attempt 2:
getSession().doWork((Connection connection) -> {
CallableStatement statement = connection.prepareCall("{call LK_spInsertBaseUser(?, ? , ?, ?, ?)}");
statement.setString(1, user.getFirstName());
statement.setString(2, user.getLastName());
statement.setString(3, user.getNickName());
statement.setString(4, user.getEncodedPassword());
statement.registerOutParameter(5, Types.INTEGER);
statement.executeUpdate();
System.out.println(statement.getInt(5));
});
This works and it is fairly quick however, I have read that the instantiation of the prepareCall is expensive so I guess the real question is, is this solution the acceptable standard or should I continue to figure out the NamedNativeQuery approach in the quest for better performance?
INvars?