问题：

Hibernate-无法创建存储过程

澹台举

2023-03-14

我最近从使用预填充的DB切换到允许Hibernate创建我的表。当试图在我的data.sql中创建存储过程时，我得到以下错误：

Exception in thread "task-2" org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #7 of URL [file:/C:/Users/samuel/IdeaProjects/Capripol/target/classes/data.sql]: CREATE PROCEDURE CalculateUserRating( IN baseRating NUMERIC(5,2), IN userID int, IN focusID int, OUT userRating NUMERIC(5,2)) BEGIN SELECT IFNULL(baseRating + SUM(s.sightingValue), baseRating) INTO userRating FROM Sighting s WHERE s.sighteeUserID = userID AND s.sightedFocusID = focusID AND s.sighterUserID != userID; nested exception is java.sql.SQLSyntaxErrorException: 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 '' at line 1

我已经尝试添加？对我的spring.datasource.url没有区别。同样，我尝试了这个答案，也没有什么区别。

数据sql:

DROP PROCEDURE IF EXISTS CalculateUserRating;
CREATE PROCEDURE CalculateUserRating(
    IN baseRating NUMERIC(5,2),
    IN userID int,
    IN focusID int,
    OUT userRating NUMERIC(5,2))
BEGIN
    SELECT IFNULL(baseRating + SUM(s.sightingValue), baseRating)
           INTO userRating
    FROM Sighting s
    WHERE
            s.sighteeUserID = userID
      AND
            s.sightedFocusID = focusID
      AND
            s.sighterUserID != userID;

END;

DROP PROCEDURE IF EXISTS CalculateSightingValue;
CREATE PROCEDURE CalculateSightingValue(
    IN baseRating int,
    IN userID int,
    IN focusID int,
    OUT sightingValue NUMERIC(5,2))
BEGIN
    CALL CalculateUserRating(baseRating, userID, focusID, @userRating);
    SELECT (@userRating/100)
           INTO sightingValue;
END;

#If first sighting it will return 1 as user rating.

DROP PROCEDURE IF EXISTS GetSightingSummary;
CREATE PROCEDURE GetSightingSummary(
    IN userID int,
    IN focusID int,
    OUT totalSightings int,
    OUT selfSightings int,
    OUT peerSightings int,
    OUT otherSightings int)
BEGIN
    SELECT count(*) INTO totalSightings
    FROM Sighting s
    WHERE
            s.sighteeUserID = userID;
    SELECT count(*) INTO selfSightings
    FROM Sighting s
    WHERE
            s.sighteeUserID = s.sighterUserID
      AND
            s.sighteeUserID = userID;
    SELECT count(*) INTO peerSightings
    FROM Sighting s
    WHERE
            s.sighteeUserID = userID
      AND
            s.sightedFocusID = focusID
      AND
            s.sighterUserID != userID;

END;

编辑：

我尝试在脚本的开始和结束处添加DELIMITER//，并尝试在每个单独的过程之间放置分隔符，但是没有任何区别。根据这一点，分隔符应该是没有必要的。

DELIMITER //
DROP PROCEDURE IF EXISTS CalculateUserRating;
DROP PROCEDURE IF EXISTS CalculateSightingValue;
DROP PROCEDURE IF EXISTS GetSightingSummary;
CREATE PROCEDURE CalculateUserRating(
    IN baseRating NUMERIC(5,2),
    IN userID int,
    IN focusID int,
    OUT userRating NUMERIC(5,2))
BEGIN
    SELECT IFNULL(baseRating + SUM(s.sightingValue), baseRating)
           INTO userRating
    FROM Sighting s
    WHERE
            s.sighteeUserID = userID
      AND
            s.sightedFocusID = focusID
      AND
            s.sighterUserID != userID;

END;

CREATE PROCEDURE CalculateSightingValue(
    IN baseRating int,
    IN userID int,
    IN focusID int,
    OUT sightingValue NUMERIC(5,2))
BEGIN
    CALL CalculateUserRating(baseRating, userID, focusID, @userRating);
    SELECT (@userRating/100)
           INTO sightingValue;
END;

#If first sighting it will return 1 as user rating.


CREATE PROCEDURE GetSightingSummary(
    IN userID int,
    IN focusID int,
    OUT totalSightings int,
    OUT selfSightings int,
    OUT peerSightings int,
    OUT otherSightings int)
BEGIN
    SELECT count(*) INTO totalSightings
    FROM Sighting s
    WHERE
            s.sighteeUserID = userID;
    SELECT count(*) INTO selfSightings
    FROM Sighting s
    WHERE
            s.sighteeUserID = s.sighterUserID
      AND
            s.sighteeUserID = userID;
    SELECT count(*) INTO peerSightings
    FROM Sighting s
    WHERE
            s.sighteeUserID = userID
      AND
            s.sightedFocusID = focusID
      AND
            s.sighterUserID != userID;

END//
DELIMITER ;

完全错误堆栈跟踪：

Exception in thread "task-2" org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #10 of URL [file:/C:/Users/samuel/IdeaProjects/Capripol/target/classes/data.sql]: CREATE PROCEDURE CalculateUserRating( IN baseRating NUMERIC(5,2), IN userID int, IN focusID int, OUT userRating NUMERIC(5,2)) BEGIN SELECT IFNULL(baseRating + SUM(s.sightingValue), baseRating) INTO userRating FROM Sighting s WHERE s.sighteeUserID = userID AND s.sightedFocusID = focusID AND s.sighterUserID != userID; nested exception is java.sql.SQLSyntaxErrorException: 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 '' at line 1
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:622)
    at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:254)
    at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:49)
    at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.runScripts(DataSourceInitializer.java:202)
    at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.initSchema(DataSourceInitializer.java:119)
    at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializerInvoker.onApplicationEvent(DataSourceInitializerInvoker.java:91)
    at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializerInvoker.onApplicationEvent(DataSourceInitializerInvoker.java:38)
    at org.springframework.context.event.SimpleApplicationEventMulticaster.doInvokeListener(SimpleApplicationEventMulticaster.java:172)
    at org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:165)
    at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:139)
    at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:404)
    at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:361)
    at org.springframework.boot.autoconfigure.orm.jpa.DataSourceInitializedPublisher.publishEventIfRequired(DataSourceInitializedPublisher.java:99)
    at org.springframework.boot.autoconfigure.orm.jpa.DataSourceInitializedPublisher.access$100(DataSourceInitializedPublisher.java:50)
    at org.springframework.boot.autoconfigure.orm.jpa.DataSourceInitializedPublisher$DataSourceSchemaCreatedPublisher.lambda$postProcessEntityManagerFactory$0(DataSourceInitializedPublisher.java:200)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLSyntaxErrorException: 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 '' at line 1
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
    at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95)
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:601)
    ... 17 more

共有1个答案

艾仲渊

2023-03-14

MySQL需要在命令前后使用分隔符，因为它必须确定存储过程或函数的开始和结束位置。

我还重新安排了一点，因为命令不希望以其他方式运行

USE testdb;
DELIMITER //
DROP PROCEDURE IF EXISTS CalculateUserRating;
DROP PROCEDURE IF EXISTS CalculateSightingValue;
DROP PROCEDURE IF EXISTS GetSightingSummary;
CREATE PROCEDURE CalculateUserRating(
    IN baseRating NUMERIC(5,2),
    IN userID int,
    IN focusID int,
    OUT userRating NUMERIC(5,2))
BEGIN
    SELECT IFNULL(baseRating + SUM(s.sightingValue), baseRating)
           INTO userRating
    FROM Sighting s
    WHERE
            s.sighteeUserID = userID
      AND
            s.sightedFocusID = focusID
      AND
            s.sighterUserID != userID;

END;

CREATE PROCEDURE CalculateSightingValue(
    IN baseRating int,
    IN userID int,
    IN focusID int,
    OUT sightingValue NUMERIC(5,2))
BEGIN
    CALL CalculateUserRating(baseRating, userID, focusID, @userRating);
    SELECT (@userRating/100)
           INTO sightingValue;
END;

#If first sighting it will return 1 as user rating.


CREATE PROCEDURE GetSightingSummary(
    IN userID int,
    IN focusID int,
    OUT totalSightings int,
    OUT selfSightings int,
    OUT peerSightings int,
    OUT otherSightings int)
BEGIN
    SELECT count(*) INTO totalSightings
    FROM Sighting s
    WHERE
            s.sighteeUserID = userID;
    SELECT count(*) INTO selfSightings
    FROM Sighting s
    WHERE
            s.sighteeUserID = s.sighterUserID
      AND
            s.sighteeUserID = userID;
    SELECT count(*) INTO peerSightings
    FROM Sighting s
    WHERE
            s.sighteeUserID = userID
      AND
            s.sightedFocusID = focusID
      AND
            s.sighterUserID != userID;

END//
DELIMITER ;

类似资料：

使用JPA Hibernate创建MySQL存储过程

我正在尝试使用文本文件的内容在MySQL数据库中创建存储过程：我使用本机查询执行SQL：但是它在DROP PROCEDURE上得到一个错误我注释了DROP PROCEDURE，然后它在DELIMITER上得到一个错误基本上，它在第一个分号之后的任何一行上都得到一个错误。似乎JPA hibernate正在解析我的查询并告诉我它有问题，而不是将纯文本传递到MySQL。 sql在MySQL中运行，
MySQL创建存储过程

MySQL 存储过程是一些 SQL 语句的集合，比如有时候我们可能需要一大串的 SQL 语句，或者说在编写 SQL 语句的过程中需要设置一些变量的值，这个时候我们就完全有必要编写一个存储过程。编写存储过程并不是件简单的事情，但是使用存储过程可以简化操作，且减少冗余的操作步骤，同时，还可以减少操作过程中的失误，提高效率，因此应该尽可能的学会使用存储过程。下面主要介绍如何创建存储过程。可以使用
MySQL语法错误创建存储过程

问题内容：这让我发疯。还给我那将是线怎么了我尝试了一些小的更改，但总是收到相同的错误。我检查了文档，但看不到语法错误。我知道这是一个愚蠢的错误，但是我坚持了大约半个小时。是一个游标，它迭代每一行并为每一行执行一个存储过程；我已经用存储函数完成了它（它的工作原理很像魅力），但是在服务器上发布时，我看到我的共享主机不支持存储函数，只支持存储过程:(因此，这是我最好的方法我发现了。问题答案：
Hibernate无法创建表

我刚开始使用hibernate，总是会遇到这样的错误：错误:HHH000388:不成功：创建表用户（id bigint not null auto_increment,mail varchar（255）,passwort varchar（255）,primary key(id)）错误：从存储引擎获取错误-1 这是我的映射类：
无法使用存储过程pyodbc SQL SERVER创建数据库

问题内容：我正在尝试调用从pyodbc创建数据库的存储过程以下是代码的最小示例理想情况下，该代码应在SQL Server实例中创建一个名为“ Test ”的数据库。这不会产生任何错误。但是没有创建数据库。跑步问题答案： Python DB API2.0指定默认情况下，应在禁用自动提交的情况下打开连接。但是，许多数据库要求DDL语句不在事务内执行。尝试在禁用自动提交的情况下执行DDL语
在teradata上创建存储过程

在Teradata16上创建存储过程时sql出错，请问这个查询有什么问题？替换过程tutorial_db.banks（out test Varchar(20))BEGIN select name from tutorial_db.banks end；

Hibernate-无法创建存储过程

共有1个答案

相关问答

相关文章

相关阅读

相关工具

相关文档