使用JPA Hibernate创建MySQL存储过程 [英] Create MySQL stored procedure using JPA Hibernate

问题描述

我正在尝试使用文本文件的内容在MySQL数据库中创建存储过程:

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 ;

我使用本地查询执行SQL:

I execute the SQL using a native query:

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

但是它在DROP PROCEDURE上出现错误 我注释掉了DROP PRODEDURE，然后在DELIMITER上得到了一个错误 基本上，在第一个分号之后的任何行上都会出现错误.

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.

似乎JPA休眠正在解析我的查询并告诉我它有问题，而不是将纯文本传递到MySQL.

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.

sql在MySQL中运行时没有错误.

The sql runs in MySQL without error.

我无法在Google中找到任何有关通过JPA创建存储过程的信息，只能调用一个.

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.

推荐答案

如果您在url中提到以下属性，则可以实现

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

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

allowMultiQueries将指示驱动程序将定界查询发送到数据库.

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

请注意，如果使用本地查询，请注意sql注入攻击. 您不需要显式放置定界符(DELIMITER).sql语句 以下查询有效

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;

这篇关于使用JPA Hibernate创建MySQL存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！