CODEWITHSUNDEEP
mysqlhibernatespring-bootjpa
thedevd
thedevd

Reputation: 683

Hibernate: how to create and execute sql procedure via jpa.hibernate.ddl-auto option

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.

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 ;

error-

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

Upvotes: 2

Views: 1495

Answers (1)

user06062019
user06062019

Reputation: 691

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);

    }
}

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

Upvotes: 2

Related Questions