title: What is the best way to set a MySQL user variable in a Java, Spring MVC, Hibernate with Annotations web application?
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>To execute native MySQL queries I have been using:</p>
  
  <pre><code>HibernateEntityManager em=(HibernateEntityManager) getEntityManager();
  Connection connection=em.getSession().connection();
  Statement s = connection.createStatement();
  s.executeUpdate("SET @current_user_id="+userId);
  </code></pre>
  
  <p>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.</p>