title: Spring Data @Query to Create MySQL Event
body: <p>I want to create MySQL Event programmatically with Query Method (@Query) in spring data. And my code like this:</p>
  
  <p>TestService.java</p>
  
  <pre><code>@Transactional(propagation=Propagation.REQUIRED)
  public void saveAndCreateEvent(Test test) {
      save(test);
  
      testStartEvent(test);
      testEndEvent(test);
  }
  
  @Transactional(propagation=Propagation.REQUIRED)
  public void save(Test test){
      testRepository.save(test);
  }
  
  @Transactional(propagation=Propagation.REQUIRED)
  public void testStartEvent(Test test){
      byte statusId = 2;//Open
      testRepository.createEventTestOpen(test.getId(), statusId, test.getStartDate());
  };
  </code></pre>
  
  <p>TestRepository.java</p>
  
  <pre><code>@Query(value="DELIMITER |"
          + " CREATE EVENT test_open"
          + "     ON SCHEDULE AT :date"
          + "     DO"
          + "         BEGIN"
          + "             UPDATE `test`"
          + "                 SET `test_status_id` = :statusId"
          + "                 WHERE `id` = :id ;"
          + "             UPDATE `user`"
          + "                 SET ...
          + "                 WHERE `id` = (SELECT `user_id` FROM `test_participant` WHERE `test_id` = :id);"
          + "         END |"
          + " DELIMITER ;", nativeQuery=true)
  public void createEventTestOpen(
          @Param("id") Long id,
          @Param("statusId") byte statusId,
          @Param("date") Date date);
  </code></pre>
  
  <p>But i get error like this:</p>
  
  <blockquote>
    <p>o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1064, SQLState: 42000
    o.h.engine.jdbc.spi.SqlExceptionHelper   : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER |
    CREATE EVENT test_open      ON SCHEDULE AT '2019-02-27 05:20:0' at line 1
    The Ex: org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement</p>
  </blockquote>
  
  <p>I call saveAndCreateEvent() method to save test details and create start_event and end_event in MySQL.
  The problem occurred in method testStartEvent() that call createEventTestOpen().</p>
  
  <p>I guess that happen because <code>DELIMITER |</code>.
  If I change query, without <code>delimiter</code> and only one update statement in body (first one), it work fine.</p>
  
  <p><strong>UPDATED 27 Feb 2019</strong> : This method is not work fine. This query also cause commit transaction immediately.</p>
  
  <pre><code>@Query(value="CREATE EVENT test_open"
          + "     ON SCHEDULE AT :date"
          + "     DO"
          + "         UPDATE `test`"
          + "             SET `test_status_id` = :statusId"
          + "             WHERE `id` = :id ;", nativeQuery=true)
  public void createEventTestOpen(
          @Param("id") Long id,
          @Param("statusId") byte statusId,
          @Param("date") Date date);
  </code></pre>
  
  <p>If I change query, without <code>delimiter</code> and with two update statement in body it work too, but transaction committed before method testEndEvent().</p>
  
  <pre><code>@Query(value="CREATE EVENT test_open"
          + "     ON SCHEDULE AT :date"
          + "     DO"
          + "         BEGIN"
          + "             UPDATE `test`"
          + "                 SET `test_status_id` = :statusId"
          + "                 WHERE `id` = :id ;"
          + "             UPDATE `user`"
          + "                 SET ...
          + "                 WHERE `id` = (SELECT `user_id` FROM `test_participant` WHERE `test_id` = :id);"
          + "         END", nativeQuery=true)
  public void createEventTestOpen(
          @Param("id") Long id,
          @Param("statusId") byte statusId,
          @Param("date") Date date);
  </code></pre>
  
  <p>I am using spring-boot-starter-parent version 1.5.13.RELEASE.</p>
  
  <p>How can I make a MySQL event with delimiter using the query method in Spring...?</p>