Spring Data @Query to Create MySQL Event

Question

I want to create MySQL Event programmatically with Query Method (@Query) in spring data. And my code like this:

TestService.java

@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());
};

TestRepository.java

@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);

But i get error like this:

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

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().

I guess that happen because DELIMITER |. If I change query, without delimiter and only one update statement in body (first one), it work fine.

UPDATED 27 Feb 2019 : This method is not work fine. This query also cause commit transaction immediately.

@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);

If I change query, without delimiter and with two update statement in body it work too, but transaction committed before method testEndEvent().

@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);

I am using spring-boot-starter-parent version 1.5.13.RELEASE.

How can I make a MySQL event with delimiter using the query method in Spring...?

1st.: Does the "statement" (with delimiter) run on db? (without SQL Error ... i doubt so.) — xerx593, Feb 27 '19 at 00:16

score 0 · Answer 1 · answered Feb 26 '19 at 23:58

0

The most frequently error occurred on SQL Error: 1064 is when we use reserved words in our sql definition. Try changing the name to de date parameter. I hope this can be useful to you

answered Feb 26 '19 at 23:58

Alvaro Jose Garcia Cohen

1
1

Problem is not in date parameter name. `:date` here is named parameter binding, not reserved word (MySQL Keyword). It will not threat as variable name. As I say before, if I change query without delimiter it will work even using `:date`. – Mohd Erwin Feb 27 '19 at 07:32

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

0

`delimiter`

is a

..command of the mysql (CLI tool)...

(..and only a "hack" to "deactivate the normal function of ;" in mysql (CLI tool).)

..it is not an SQL command! (and of course throws SQL Error: 1064...)

In your case (You don't need delimiter at all), you can just use the semicolons and BEGIN, DECLARE, END, ... "as needed", and "rely" on the capabilities of the driver.

->

If I change query, without delimiter and with two update statement in body it work too

The other issue:

but transaction committed before method testEndEvent().

...We have to few infos, but, according to your code, could be this:

Spring @Transaction method call by the method within the same class, does not work?

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 27 '19 at 00:47

xerx593

12,237
5
33
64

Thank you for the informations xerx593. Calling method in same class with Transactional annotation that cause this. From [documentation](https://docs.spring.io/spring/docs/4.2.x/spring-framework-reference/html/transaction.html), I found self-invocation in proxy mode transaction (which is the default) will not lead to an actual transaction at runtime even if the invoked method is marked with @Transactional. – Mohd Erwin Feb 27 '19 at 08:07

Spring Data @Query to Create MySQL Event

2 Answers2

delimiter

The other issue:

`delimiter`