java通过ScriptRunner执行存储过程sql脚本

java通过ScriptRunner执行存储过程sql脚本

执行存储过程时需要自定义结束分隔符,定义脚本中的语句一次完整的执行；

 

主要代码：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63

@Component public class InitializableDatabaseRunner implements CommandLineRunner {       @Autowired     private DataSourceProperties dataSourceProperties;           @Override     public void run(String... args) throws Exception {         try {             Connection conn =getConnection();             ScriptRunner scriptRunner = new ScriptRunner(conn);             Resources.setCharset(Charset.forName("UTF-8")); //设置字符集,不然中文乱码插入错误             scriptRunner.setEscapeProcessing(false);             scriptRunner.setRemoveCRs(true);             scriptRunner.setSendFullScript(false);             scriptRunner.setAutoCommit(true);             //分隔符，还未验证具体功能             scriptRunner.setFullLineDelimiter(false);             //每条命令间的分隔符             scriptRunner.setDelimiter("$$");             // 从class目录下直接读取             List<String> files = getFiles("F:\\resetsql\\");             for (String file : files) {                 InputStream inputStream = new FileInputStream(new File(file));                 Reader reader = new InputStreamReader(inputStream,"utf-8");                 scriptRunner.runScript(reader);             }             scriptRunner.closeConnection();             conn.close();             System.out.println("sql脚本执行完毕");         } catch (Exception e) {             System.out.println("sql脚本执行发生异常");             e.printStackTrace();         }     }       /**      * @Author：      * @Description：获取某个目录下所有直接下级文件，不包括目录下的子目录的下的文件，所以不用递归获取      * @Date：      */     public  List<String> getFiles(String path) {         List<String> files = new ArrayList<String>();         File file = new File(path);         File[] tempList = file.listFiles();           for (int i = 0; i < tempList.length; i++) {             if (tempList[i].isFile()) {                 files.add(tempList[i].toString());                 //文件名，不包含路径                 //String fileName = tempList[i].getName();             }         }         return files;     }           private Connection getConnection() throws ClassNotFoundException, SQLException {         Class.forName(dataSourceProperties.getDriverClassName());         return DriverManager.getConnection(dataSourceProperties.getUrl(),dataSourceProperties.getUsername(),dataSourceProperties.getPassword());     } }

　　脚本示例：

?

1 2 3 4

DROP PROCEDURE IF EXISTS Showjob;$$ CREATE PROCEDURE Showjob() BEGIN SELECT * FROM sys_job; END;$$