What is the best way to set a MySQL user variable in a Java, Spring MVC, Hibernate with Annotations web application?

Question

I need to be able to set a MySQL user variable that is used in a trigger in a Spring MVC Hibernate web ap. This user variable is used in MySQL triggers on the tables that are being manipulated by Hibernate. I need this user variable to be correctly set during all of Hibernate's write accesses to the database.

Unfortunately HQL does not have a technique for setting MySQL user variables and the method I have found for directly executing MySQL does not seem to work with the transaction. Since the user variable's life span is that of the database connection I need the MySQL to execute using the same connection that they HQL will be executed with. However my transactions seem to run the HQL after the native MySQL has been executed and thus the expected user variable is not present for the MySQL trigger.

To execute native MySQL queries I have been using:

HibernateEntityManager em=(HibernateEntityManager) getEntityManager();
Connection connection=em.getSession().connection();
Statement s = connection.createStatement();
s.executeUpdate("SET @current_user_id="+userId);

When the Spring MVC commits my transaction it runs the HQL queries and then throws an exception because the @current_user_id is causing the MySQL trigger to break. I verified this by testing without the triggers present.

Answer 1

I found this SO question that is very similar to mine: http://stackoverflow.com/questions/2712240/how-to-use-mysql-variables-with-hibernate

So I followed the suggestion and used a stored procedure and the executeNativeQuery method on the entity manager to call it.

Here is my stored procedure code:

DROP PROCEDURE IF EXISTS set_current_user
CREATE PROCEDURE set_current_user(IN user_id BIGINT)
BEGIN
  SET @current_user_id = user_id
END

And I call it with:

Query q = em.createNativeQuery("CALL set_current_user("+userId+")");
        q.executeUpdate();