Harshita Sethi Harshita Sethi - 3 months ago 19
Java Question

Execute an .sql file in unix using org.springframework.jdbc.datasource.init.ScriptUtils

I have sql file present in my unix server. I want to run that file in unix using java program.
I saw many ways online, I tried using

org.springframework.jdbc.datasource.init.ScriptUtils


The code is follows

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException ex) {
System.out.println("Error: unable to load driver class!");
System.exit(1);
}

String URL = configBean.getUrl();
String USER = configBean.getUser();
String PASS = configBean.getPassword();
System.out.println(URL + " ,"+USER+" ,"+PASS);
Connection conn = DriverManager.getConnection(URL,USER,PASS);
System.out.println(configBean.getSqlFilePath());
ScriptUtils.executeSqlScript(conn, new ClassPathResource(configBean.getSqlFilePath()));


The problem is this method is unable to detect the file in the unix server. I have path present in a config file. From there I'm reading the path, My path is "/home/applvis/JAVA/UAT/config/ABC.sql".
When I execute the jar containing this code, it shows the file not found in this location. It removes the first slash present in the file path. If I put two slashes then also it is unable to detect the file. The error which I get is

INFO: Executing SQL script from class path resource [/home/applvis/JAVA/UAT/config/ALLOT010T_OBJNAMES.sql]
Exception in thread "Main Thread" org.springframework.jdbc.datasource.init.CannotReadScriptException: Cannot read SQL script from class path resource [/home/applvis/JAVA/UAT/config/ALLOT010T_OBJNAMES.sql]; nested exception is java.io.FileNotFoundException: class path resource [/home/applvis/JAVA/UAT/config/ALLOT010T_OBJNAMES.sql] cannot be opened because it does not exist
at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:442)
at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:395)
at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:373)
at com.acc.directory.scanner.SDScanner.main(SDScanner.java:77)
Caused by: java.io.FileNotFoundException: class path resource [/home/applvis/JAVA/UAT/config/ALLOT010T_OBJNAMES.sql] cannot be opened because it does not exist
at org.springframework.core.io.ClassPathResource.getInputStream(ClassPathResource.java:143)
at org.springframework.core.io.support.EncodedResource.getReader(EncodedResource.java:92)
at org.springframework.jdbc.datasource.init.ScriptUtils.readScript(ScriptUtils.java:279)
at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:439)
... 3 more


I'm unable to understand what am I doing wrong or is there a different way to read files from unix. Please help

Answer

You wrote your file lies in your filesystem under /home/applvis/JAVA/UAT/config/ABC.sql, but your code is trying to load to file from the classpath.

So instead of

ScriptUtils.executeSqlScript(conn, new ClassPathResource(configBean.getSqlFilePath())); 

you have to use

ScriptUtils.executeSqlScript(conn, new FileSystemResource(configBean.getSqlFilePath()));