using MySQL event scheduler with Java

Question

I am trying to create events using Java code with hibernate. I verified my syntax for CREATE EVENT in MySQL Workbench, and I verified my Java code with a simple UPDATE query. But when I am trying to use both it just doesn't work. I am not getting any error message, just that 0 rows were affected. My code is as follows:

String sql = "CREATE EVENT IF NOT EXISTS test_event ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 20 SECOND ON COMPLETION PRESERVE ENABLE DO UPDATE my_table SET last_error_message='my test' WHERE ID=17;";

session.beginTransaction();
SQLQuery query = session.createSQLQuery(sql);
int result = query.executeUpdate();
session.getTransaction().commit();
....
session.close();

thanks a lot

How you are checking that event is created or not in mysql? I tested, code is working fine. — Vicky Thakor, Oct 27 '14 at 06:02

score 0 · Answer 1 · edited May 23 '17 at 12:16

0

Get jdbc connection from your session: How to get jdbc connection from hibernate session?
Use JDBC to execute DDL

...

Statement stmt = null;
try {
    stmt = conn.createStatement();
    stmt.executeUpdate(sql);
} catch (SQLException e) {
    // ...
} finally {
    // close stmt
}

...

edited May 23 '17 at 12:16

Community

1
1

answered Oct 26 '14 at 21:03

Multisync

8,657
1
16
20

score 0 · Accepted Answer · edited Dec 23 '20 at 17:01

0

How do you know if any new events were created? You can try

show events from <SCHEME_NAME>;

This will show all the events that are registered to the given schema.try printing the session\statement warning stack...

edited Dec 23 '20 at 17:01

Dinidu Hewage

2,169
6
40
51

answered Oct 27 '14 at 12:02

Gal Mazor

100
2
10

score 0 · Answer 3 · answered Dec 23 '20 at 13:39

First, you need to make sure your database is prepared to execute an event. for that, you need to run the following command.

SHOW PROCESSLIST;

MySQL uses a special thread called event schedule thread to execute all scheduled events.

if you see your process list like the above picture. you need to run the below command to enable MySQL event execution.

SET GLOBAL event_scheduler = ON;

Then when you execute the "SHOW PROCESSLIST;" command you will see the below list which shows the specific thread for event execution in MySQL.

Now you can create your event using any MySQL client interface.

using MySQL event scheduler with Java

3 Answers3