Create MySQL stored procedure using JPA Hibernate

Question

I'm trying to create a stored procedure in a MySQL database using the contents of a text file:

USE myDatabase;

DROP PROCEDURE IF EXISTS myStoredProcedure;

DELIMITER $$

CREATE PROCEDURE myStoredProcedure
(
    _description VARCHAR(50),
    _value INT
)
BEGIN

    INSERT INTO myTable
    (
        description,
        value
    ) VALUES (
        _description,
        _value
    );

    SELECT 
        id, 
        description, 
        value
    FROM myTable
    WHERE id = LAST_INSERT_ID();

END;

$$

DELIMITER ;

I execute the SQL using a native query:

Query query = entityManager.createNativeQuery(queryText);
...
query.executeUpdate();

But it gets an error on the DROP PROCEDURE I commented out the DROP PROCEDURE and then it gets an error on the DELIMITER Basically, it gets an error on any line after the first semicolon.

It seems as if JPA hibernate is parsing my query and telling me there's a problem with it rather than passing the unadulterated text onto MySQL.

The sql runs in MySQL without error.

I can't find anything in Google about creating a stored procedure with JPA, only calling one.

Does anyone have any insight on what I might be doing wrong? Or if this is even possible.

user06062019 · Accepted Answer · 2019-07-15 11:17:28Z

5

This can be possible if you mention the following property in the url

spring.datasource.url=jdbc:mysql://localhost:3306/test?allowMultiQueries=true

The allowMultiQueries will instruct the driver to sent delimited queries to the database.

Please note that if you are using native queries be-aware of sql injection attack. You dont need to put the delimiter(DELIMITER) explicitly.The sql statement The following query works

SET myDatabase; 

DROP PROCEDURE IF EXISTS myStoredProcedure; 

CREATE PROCEDURE myStoredProcedure ( _description VARCHAR(50), _value INT ) 

BEGIN 

INSERT INTO 
    myTable ( description, value ) 
VALUES ( _description, _value ); 

SELECT id, description, value 
  FROM myTable 
WHERE id = LAST_INSERT_ID(); 

END;

edited Jul 15, 2019 at 11:17

answered Jun 6, 2019 at 20:02

user06062019

6914 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user3158212 Over a year ago

That works right up until it hits DELIMITER $$ Then it flags the DELIMITER as a syntax error.

user3158212 Over a year ago

I assume that because the delimiter doesn't end in a semicolon

user06062019 Over a year ago

You can remove the DELIMITER $$ altogether and use the semi-colon instead coupled with allowMultiQueries=true it should work

user06062019 Over a year ago

The following modified query works USE myDatabase; DROP PROCEDURE IF EXISTS myStoredProcedure; CREATE PROCEDURE myStoredProcedure ( _description VARCHAR(50), _value INT ) BEGIN INSERT INTO myTable ( description, value ) VALUES ( _description, _value ); SELECT id, description, value FROM myTable WHERE id = LAST_INSERT_ID(); END;

user3158212 Over a year ago

Thanks! Normally when I run a create procedure statement in MySQL workbench, it stops the parsing when it hits the semicolon, this obviously works differently.

Collectives™ on Stack Overflow

Create MySQL stored procedure using JPA Hibernate

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related