spring+mybatis执行SQL脚本文件

绝地灬酷狼 2023-01-07 02:29 270阅读 0赞

## spring+mybatis执行SQL脚本文件 ##

使用ScriptRunner技术执行SQL脚本文件，比原生JDBC执行SQL的有点有如下：  
1、代码更加简洁  
2、有效的记录了执行SQL的日志及错误日志  
代码如下：

package com.lylp.common.update;
    
    import org.apache.commons.codec.Charsets;
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.jdbc.ScriptRunner;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.datasource.DataSourceUtils;
    import org.springframework.stereotype.Component;
    
    import javax.sql.DataSource;
    import java.io.IOException;
    import java.io.PrintWriter;
    import java.io.Reader;
    import java.io.StringWriter;
    import java.sql.Connection;
    
    /** * mybatis执行SQL脚本 */
    @Component
    public class UpdateSqlService { 
    
        private Logger logger = LoggerFactory.getLogger(this.getClass());
    
        @Autowired
        private DataSource dataSource;
    
        /** * 使用ScriptRunner执行SQL脚本 */
        public void doExecuteSql() { 
            //通过数据源获取数据库链接
            Connection connection = DataSourceUtils.getConnection(dataSource);
            //创建脚本执行器
            ScriptRunner scriptRunner = new ScriptRunner(connection);
            //创建字符输出流，用于记录SQL执行日志
            StringWriter writer = new StringWriter();
            PrintWriter print = new PrintWriter(writer);
            //设置执行器日志输出
            scriptRunner.setLogWriter(print);
            //设置执行器错误日志输出
            scriptRunner.setErrorLogWriter(print);
            //设置读取文件格式
            Resources.setCharset(Charsets.UTF_8);
            String[] filePaths = { "sql/update1.sql", "sql/update2.sql"};
            for (String path : filePaths) { 
    
                Reader reader = null;
                try { 
                    //获取资源文件的字符输入流
                    reader = Resources.getResourceAsReader(path);
                } catch (IOException e) { 
                    //文件流获取失败，关闭链接
                    logger.error(e.getMessage(), e);
                    scriptRunner.closeConnection();
                    return;
                }
                //执行SQL脚本
                scriptRunner.runScript(reader);
                //关闭文件输入流
                try { 
                    reader.close();
                } catch (IOException e) { 
                    logger.error(e.getMessage(), e);
                }
            }
            //输出SQL执行日志
            logger.debug(writer.toString());
            //关闭输入流
            scriptRunner.closeConnection();
        }
    }

SQL脚本放在resources文件下，如下图：  
![在这里插入图片描述][20210115110755696.png]

[20210115110755696.png]: /images/20221119/6af13609772548dfa70333b24e65b257.png