Hibernate:如何通过jpa.hibernate.ddl-auto选项创建和执行sql过程 [英] Hibernate: how to create and execute sql procedure via jpa.hibernate.ddl-auto option

问题描述

我有几个要在初始设置时执行的存储过程.因此，我所做的就是将存储过程放在data.sql中并设置jpa.hibernate.ddl-auto=create.

I have a couple of stored procedures that I want to execute as initial setup. So What I have done, placed that stored procedure in data.sql and set the jpa.hibernate.ddl-auto=create.

但是在启动我的springboot应用程序时，该应用程序由于 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException 而失败.这意味着，它无法理解该过程的语法，但是可以在sql工作表上手动执行相同的过程，而不会出现任何问题.那么如何通过data.sql

But on startup of my springboot application, the application is failing with com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException. It means, it is not able to understand the syntax of that procedure, however the same procedure can be executed manually on sql sheet without any problem. So how can I execute it via data.sql

data.sql -

DELIMITER $$

CREATE PROCEDURE `GetStocks`(int_stockcode varchar(20))
BEGIN
  DECLARE stock_name VARCHAR(100);
  SELECT name FROM stock where stock_code = int_stockcode INTO stock_name;
END $$

DELIMITER ;

错误-

由以下原因引起:org.springframework.jdbc.datasource.init.ScriptStatementFailedException:

Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException:

无法执行类路径资源[default-data.sql]的SQL脚本语句#29:DELIMITER $$ CREATE PROCEDURE GetStocks(int_stockcode varchar(20))BEGIN SELECT * FROM stock from stock where stock_code = int_stockcode;嵌套的异常是com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:您的SQL语法有错误.在第1行的'DELIMITER $$ CREATE PROCEDURE GetStocks(int_stockcode varchar(20))BEGIN SELEC'附近检查与您的MySQL服务器版本相对应的手册以使用正确的语法

Failed to execute SQL script statement #29 of class path resource [default-data.sql]: DELIMITER $$ CREATE PROCEDURE GetStocks(int_stockcode varchar(20)) BEGIN SELECT * FROM stock where stock_code = int_stockcode; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 PROCEDURE GetStocks(int_stockcode varchar(20)) BEGIN SELEC' at line 1

在org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:491) 在org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:238) 在org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:48) 在org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.runScripts(DataSourceInitializer.java:192) 在org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.runDataScripts(DataSourceInitializer.java:128) 在org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.onApplicationEvent(DataSourceInitializer.java:118) 在org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.onApplicationEvent(DataSourceInitializer.java:51) 在org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:167) 在org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:139) 在org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:393) 在org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:347) 在org.springframework.boot.autoconfigure.orm.jpa.DataSourceInitializedPublisher.publishEventIfRequired(DataSourceInitializedPublisher.java:77)处 在org.springframework.boot.autoconfigure.orm.jpa.DataSourceInitializedPublisher.postProcessAfterInitialization(DataSourceInitializedPublisher.java:68)上 在org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyBeanPostProcessorsAfterInitialization(AbstractAutowireCapableBeanFactory.java:423)处 在org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.postProcessObjectFromFactoryBean(AbstractAutowireCapableBeanFactory.java:1775)处 在org.springframework.beans.factory.support.FactoryBeanRegistrySupport.getObjectFromFactoryBean(FactoryBeanRegistrySupport.java:113) ...省略了75个通用框架 引起原因:com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:您的SQL语法有错误；在第1行的'DELIMITER $$ CREATE PROCEDURE GetStocks(int_stockcode varchar(20))BEGIN SELEC'附近使用与您的MySQL服务器版本相对应的手册，以使用正确的语法. 在sun.reflect.GeneratedConstructorAccessor71.newInstance(未知来源) 在sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) 在java.lang.reflect.Constructor.newInstance(Constructor.java:423) 在com.mysql.jdbc.Util.handleNewInstance(Util.java:425) 在com.mysql.jdbc.Util.getInstance(Util.java:408) 在com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943) 在com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) 在com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) 在com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527) 在com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) 在com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486) 在com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2444) 在com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845) 在com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745) 在sun.reflect.GeneratedMethodAccessor27.invoke(未知来源) 在sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 在java.lang.reflect.Method.invoke(Method.java:498) 在org.apache.tomcat.jdbc.pool.StatementFacade $ StatementProxy.invoke(StatementFacade.java:114) com.sun.proxy.$ Proxy106.execute(未知来源) 在org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:470) ...省略了90个常见框架

at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:491) at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:238) at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:48) at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.runScripts(DataSourceInitializer.java:192) at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.runDataScripts(DataSourceInitializer.java:128) at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.onApplicationEvent(DataSourceInitializer.java:118) at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.onApplicationEvent(DataSourceInitializer.java:51) at org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:167) at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:139) at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:393) at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:347) at org.springframework.boot.autoconfigure.orm.jpa.DataSourceInitializedPublisher.publishEventIfRequired(DataSourceInitializedPublisher.java:77) at org.springframework.boot.autoconfigure.orm.jpa.DataSourceInitializedPublisher.postProcessAfterInitialization(DataSourceInitializedPublisher.java:68) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyBeanPostProcessorsAfterInitialization(AbstractAutowireCapableBeanFactory.java:423) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.postProcessObjectFromFactoryBean(AbstractAutowireCapableBeanFactory.java:1775) at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.getObjectFromFactoryBean(FactoryBeanRegistrySupport.java:113) ... 75 common frames omitted Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 PROCEDURE GetStocks(int_stockcode varchar(20)) BEGIN SELEC' at line 1 at sun.reflect.GeneratedConstructorAccessor71.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) at com.mysql.jdbc.Util.getInstance(Util.java:408) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2444) at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845) at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745) at sun.reflect.GeneratedMethodAccessor27.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) at com.sun.proxy.$Proxy106.execute(Unknown Source) at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:470) ... 90 common frames omitted

推荐答案

对于加载和创建数据库对象，最好坚持使用liquibase或flyway. 使用以下代码可以加载存储过程.

For loading and creating the database objects it is better to stick to liquibase or flyway. Using the following code it is possible to load the stored procedure.

使用控制器来加载存储过程，但是它可以是简单的类，也可以通过事件侦听器进行配置.

Used a controller to load the stored procedure but it can be a simple class or can be configured via an event listener.

@RestController
public class SqlLoaderController {

    @Autowired
    DataSource dataSource;

    @GetMapping("/load")
    public void loadScript() throws Exception
    {

        ResourceDatabasePopulator resourceDatabasePopulator=new ResourceDatabasePopulator();
        resourceDatabasePopulator.setSeparator("DELIMITER");
        InputStream is= new ClassPathResource("sql.txt").getInputStream();
        resourceDatabasePopulator.addScript(new InputStreamResource(is));
        resourceDatabasePopulator.execute(dataSource);

    }
}

DELIMITER用于区分两个存储过程或数据库对象的开始和结束.sql.txt文件

The DELIMITER is used to distinguish between start and end of two stored procedure or database objects.The sql.txt file

CREATE PROCEDURE GetStocks(int_stockcode varchar(20))
BEGIN
  DECLARE stock_name VARCHAR(100);
  SELECT 1 FROM dual;
END;

DELIMITER

CREATE PROCEDURE GetStocks1(int_stockcode varchar(20))
BEGIN
  DECLARE stock_name VARCHAR(100);
  SELECT 1 FROM dual;
END;
DELIMITER

这篇关于Hibernate:如何通过jpa.hibernate.ddl-auto选项创建和执行sql过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！