mysql - Hibernate:如何通过 jpa.hibernate.ddl-auto 选项创建和执行 sql 过程

Question

我有几个要作为初始设置执行的存储过程。所以我所做的就是将该存储过程放在 data.sql 中。并设置 jpa.hibernate.ddl-auto=create .

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

数据.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 ;

错误 ——

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

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

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

Answer 1

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

使用 Controller 加载存储过程，但它可以是一个简单的类，也可以通过事件监听器进行配置。

@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 文件

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