Springboot服务实现执行SQL脚本文件_Java

springboot服务执行sql脚本文件

当sql脚本文件包含不同数据库实例sql时，遍历读取sql文件再插入时，由于是不同的数据库实例这种方式就不行了，这时就需要程序直接执行sql脚本。

java程序执行sql文件时注意：

1.当sql文件包含不同的数据库实例时，在sql脚本末尾要切回该服务所使用的数据库；如：use db1; … use db2; 该服务使用db1数据库，要末尾追加 use db1；
2.在sql文件结尾要加 'commit;'指令。

springboot执行sql脚本

/**
 * 执行sql脚本
 * @throws sqlexception
 */
private void executesqlscript(string path) throws ioexception {
    resourcedatabasepopulator resourcedatabasepopulator = new resourcedatabasepopulator();
    resourcedatabasepopulator.addscript(new classpathresource(path));
    // resourcedatabasepopulator.addscript(new filesystemresource(path));
    resourcedatabasepopulator.execute(datasource);
}

注意：文件需要在class path下

本文将文件放入到 src/main/resources/目录下，如下图：

当服务运行时，时正常时报错，报错信息如下：caused by: java.io.filenotfoundexception.

详细报错信息如下：

2024-07-19 13:20:24.360 info 19320 --- [nio-8000-exec-1] com.zaxxer.hikari.hikaridatasource : hikaripool-1 - starting...
2024-07-19 13:20:24.566 info 19320 --- [nio-8000-exec-1] com.zaxxer.hikari.hikaridatasource : hikaripool-1 - start completed.
2024-07-19 13:20:24.577 error 19320 --- [nio-8000-exec-1] a.o.e.h.globalcontrollerexceptionhandler : cannot read sql script from class path resource [too7pxuiiirtab7o0djjwewyyeel1tij90af0l4wztwj6xploh4arp7hne91pkvp.sql]; nested exception is java.io.filenotfoundexception: class path resource [too7pxuiiirtab7o0djjwewyyeel1tij90af0l4wztwj6xploh4arp7hne91pkvp.sql] cannot be opened because it does not exist

org.springframework.jdbc.datasource.init.cannotreadscriptexception: cannot read sql script from class path resource [too7pxuiiirtab7o0djjwewyyeel1tij90af0l4wztwj6xploh4arp7hne91pkvp.sql]; nested exception is java.io.filenotfoundexception: class path resource [too7pxuiiirtab7o0djjwewyyeel1tij90af0l4wztwj6xploh4arp7hne91pkvp.sql] cannot be opened because it does not exist
   at org.springframework.jdbc.datasource.init.scriptutils.executesqlscript(scriptutils.java:239) ~[spring-jdbc-5.3.24.jar:5.3.24]
   at org.springframework.jdbc.datasource.init.resourcedatabasepopulator.populate(resourcedatabasepopulator.java:254) ~[spring-jdbc-5.3.24.jar:5.3.24]
   at org.springframework.jdbc.datasource.init.databasepopulatorutils.execute(databasepopulatorutils.java:54) ~[spring-jdbc-5.3.24.jar:5.3.24]
   at org.springframework.jdbc.datasource.init.resourcedatabasepopulator.execute(resourcedatabasepopulator.java:269) ~[spring-jdbc-5.3.24.jar:5.3.24]
   at com.angel.ocean.service.impl.oemappdataserviceimpl.executesqlscript(oemappdataserviceimpl.java:239) ~[classes/:na]
   at com.angel.ocean.service.impl.oemappdataserviceimpl.importsqlfile(oemappdataserviceimpl.java:130) ~[classes/:na]
   at sun.reflect.nativemethodaccessorimpl.invoke0(native method) ~[na:1.8.0_221]
   at sun.reflect.nativemethodaccessorimpl.invoke(nativemethodaccessorimpl.java:62) ~[na:1.8.0_221]
   at sun.reflect.delegatingmethodaccessorimpl.invoke(delegatingmethodaccessorimpl.java:43) ~[na:1.8.0_221]
   at java.lang.reflect.method.invoke(method.java:498) ~[na:1.8.0_221]
   at org.springframework.aop.support.aoputils.invokejoinpointusingreflection(aoputils.java:344) ~[spring-aop-5.3.24.jar:5.3.24]
   at org.springframework.aop.framework.jdkdynamicaopproxy.invoke(jdkdynamicaopproxy.java:208) ~[spring-aop-5.3.24.jar:5.3.24]
   at com.sun.proxy.$proxy73.importsqlfile(unknown source) ~[na:na]
   at com.angel.ocean.controller.oemappdatacontroller.importsqlfile(oemappdatacontroller.java:51) ~[classes/:na]
   at sun.reflect.nativemethodaccessorimpl.invoke0(native method) ~[na:1.8.0_221]
   at sun.reflect.nativemethodaccessorimpl.invoke(nativemethodaccessorimpl.java:62) ~[na:1.8.0_221]
   at sun.reflect.delegatingmethodaccessorimpl.invoke(delegatingmethodaccessorimpl.java:43) ~[na:1.8.0_221]
   at java.lang.reflect.method.invoke(method.java:498) ~[na:1.8.0_221]
   at org.springframework.web.method.support.invocablehandlermethod.doinvoke(invocablehandlermethod.java:205) ~[spring-web-5.3.24.jar:5.3.24]
   at org.springframework.web.method.support.invocablehandlermethod.invokeforrequest(invocablehandlermethod.java:150) ~[spring-web-5.3.24.jar:5.3.24]
   at org.springframework.web.servlet.mvc.method.annotation.servletinvocablehandlermethod.invokeandhandle(servletinvocablehandlermethod.java:117) ~[spring-webmvc-5.3.24.jar:5.3.24]
   at org.springframework.web.servlet.mvc.method.annotation.requestmappinghandleradapter.invokehandlermethod(requestmappinghandleradapter.java:895) ~[spring-webmvc-5.3.24.jar:5.3.24]
   at org.springframework.web.servlet.mvc.method.annotation.requestmappinghandleradapter.handleinternal(requestmappinghandleradapter.java:808) ~[spring-webmvc-5.3.24.jar:5.3.24]
   at org.springframework.web.servlet.mvc.method.abstracthandlermethodadapter.handle(abstracthandlermethodadapter.java:87) ~[spring-webmvc-5.3.24.jar:5.3.24]
   at org.springframework.web.servlet.dispatcherservlet.dodispatch(dispatcherservlet.java:1071) ~[spring-webmvc-5.3.24.jar:5.3.24]
   at org.springframework.web.servlet.dispatcherservlet.doservice(dispatcherservlet.java:964) ~[spring-webmvc-5.3.24.jar:5.3.24]
   at org.springframework.web.servlet.frameworkservlet.processrequest(frameworkservlet.java:1006) [spring-webmvc-5.3.24.jar:5.3.24]
   at org.springframework.web.servlet.frameworkservlet.dopost(frameworkservlet.java:909) [spring-webmvc-5.3.24.jar:5.3.24]
   at javax.servlet.http.httpservlet.service(httpservlet.java:696) [tomcat-embed-core-9.0.69.jar:4.0.fr]
   at org.springframework.web.servlet.frameworkservlet.service(frameworkservlet.java:883) [spring-webmvc-5.3.24.jar:5.3.24]
   at javax.servlet.http.httpservlet.service(httpservlet.java:779) [tomcat-embed-core-9.0.69.jar:4.0.fr]
   at org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:227) [tomcat-embed-core-9.0.69.jar:9.0.69]
   at org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:162) [tomcat-embed-core-9.0.69.jar:9.0.69]
   at org.apache.tomcat.websocket.server.wsfilter.dofilter(wsfilter.java:53) [tomcat-embed-websocket-9.0.69.jar:9.0.69]
   at org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:189) [tomcat-embed-core-9.0.69.jar:9.0.69]
   at org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:162) [tomcat-embed-core-9.0.69.jar:9.0.69]
   at org.springframework.web.filter.requestcontextfilter.dofilterinternal(requestcontextfilter.java:100) [spring-web-5.3.24.jar:5.3.24]
   at org.springframework.web.filter.onceperrequestfilter.dofilter(onceperrequestfilter.java:117) [spring-web-5.3.24.jar:5.3.24]
   at org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:189) [tomcat-embed-core-9.0.69.jar:9.0.69]
   at org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:162) [tomcat-embed-core-9.0.69.jar:9.0.69]
   at org.springframework.web.filter.formcontentfilter.dofilterinternal(formcontentfilter.java:93) [spring-web-5.3.24.jar:5.3.24]
   at org.springframework.web.filter.onceperrequestfilter.dofilter(onceperrequestfilter.java:117) [spring-web-5.3.24.jar:5.3.24]
   at org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:189) [tomcat-embed-core-9.0.69.jar:9.0.69]
   at org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:162) [tomcat-embed-core-9.0.69.jar:9.0.69]
   at org.springframework.web.filter.characterencodingfilter.dofilterinternal(characterencodingfilter.java:201) [spring-web-5.3.24.jar:5.3.24]
   at org.springframework.web.filter.onceperrequestfilter.dofilter(onceperrequestfilter.java:117) [spring-web-5.3.24.jar:5.3.24]
   at org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:189) [tomcat-embed-core-9.0.69.jar:9.0.69]
   at org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:162) [tomcat-embed-core-9.0.69.jar:9.0.69]
   at org.apache.catalina.core.standardwrappervalve.invoke(standardwrappervalve.java:177) [tomcat-embed-core-9.0.69.jar:9.0.69]
   at org.apache.catalina.core.standardcontextvalve.invoke(standardcontextvalve.java:97) [tomcat-embed-core-9.0.69.jar:9.0.69]
   at org.apache.catalina.authenticator.authenticatorbase.invoke(authenticatorbase.java:541) [tomcat-embed-core-9.0.69.jar:9.0.69]
   at org.apache.catalina.core.standardhostvalve.invoke(standardhostvalve.java:135) [tomcat-embed-core-9.0.69.jar:9.0.69]
   at org.apache.catalina.valves.errorreportvalve.invoke(errorreportvalve.java:92) [tomcat-embed-core-9.0.69.jar:9.0.69]
   at org.apache.catalina.core.standardenginevalve.invoke(standardenginevalve.java:78) [tomcat-embed-core-9.0.69.jar:9.0.69]
   at org.apache.catalina.connector.coyoteadapter.service(coyoteadapter.java:360) [tomcat-embed-core-9.0.69.jar:9.0.69]
   at org.apache.coyote.http11.http11processor.service(http11processor.java:399) [tomcat-embed-core-9.0.69.jar:9.0.69]
   at org.apache.coyote.abstractprocessorlight.process(abstractprocessorlight.java:65) [tomcat-embed-core-9.0.69.jar:9.0.69]
   at org.apache.coyote.abstractprotocol$connectionhandler.process(abstractprotocol.java:891) [tomcat-embed-core-9.0.69.jar:9.0.69]
   at org.apache.tomcat.util.net.nioendpoint$socketprocessor.dorun(nioendpoint.java:1784) [tomcat-embed-core-9.0.69.jar:9.0.69]
   at org.apache.tomcat.util.net.socketprocessorbase.run(socketprocessorbase.java:49) [tomcat-embed-core-9.0.69.jar:9.0.69]
   at org.apache.tomcat.util.threads.threadpoolexecutor.runworker(threadpoolexecutor.java:1191) [tomcat-embed-core-9.0.69.jar:9.0.69]
   at org.apache.tomcat.util.threads.threadpoolexecutor$worker.run(threadpoolexecutor.java:659) [tomcat-embed-core-9.0.69.jar:9.0.69]
   at org.apache.tomcat.util.threads.taskthread$wrappingrunnable.run(taskthread.java:61) [tomcat-embed-core-9.0.69.jar:9.0.69]
   at java.lang.thread.run(thread.java:748) [na:1.8.0_221]
caused by: java.io.filenotfoundexception: class path resource [too7pxuiiirtab7o0djjwewyyeel1tij90af0l4wztwj6xploh4arp7hne91pkvp.sql] cannot be opened because it does not exist
   at org.springframework.core.io.classpathresource.getinputstream(classpathresource.java:199) ~[spring-core-5.3.24.jar:5.3.24]
   at org.springframework.core.io.support.encodedresource.getreader(encodedresource.java:146) ~[spring-core-5.3.24.jar:5.3.24]
   at org.springframework.jdbc.datasource.init.scriptutils.readscript(scriptutils.java:328) ~[spring-jdbc-5.3.24.jar:5.3.24]
   at org.springframework.jdbc.datasource.init.scriptutils.executesqlscript(scriptutils.java:236) ~[spring-jdbc-5.3.24.jar:5.3.24]
   ... 63 common frames omitted

如何解决上述报错呢？

不使用classpath相对路径

resourcedatabasepopulator.addscript(new classpathresource(path))

使用文件系统的绝对路径：

resourcedatabasepopulator.addscript(new filesystemresource(path))

如下所示：

/**
 * 执行sql脚本
 * @throws sqlexception
 */
private void executesqlscript(string path) throws ioexception {
    resourcedatabasepopulator resourcedatabasepopulator = new resourcedatabasepopulator();
    // resourcedatabasepopulator.addscript(new classpathresource(path));
    resourcedatabasepopulator.addscript(new filesystemresource(path));
    resourcedatabasepopulator.execute(datasource);
}

注意：这个path是系统的绝对路径