1、 引入数据源
import javax.sql.DataSource;
@Autowired
private DataSource dataSource;
2、Utils书写
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import javax.sql.DataSource;
import java.io.Reader;
import java.nio.charset.Charset;
import java.sql.Connection;
@Slf4j
public class ScriptRunnerExecSqlUtils {
public static void executesql(DataSource dataSource,String path) throws Exception{
Connection conn =dataSource.getConnection();
ScriptRunner runner = new ScriptRunner(conn);
Resources.setCharset(Charset.forName("UTF-8")); //设置字符集,不然中文乱码插入错误
runner.setLogWriter(null);//设置是否输出日志
// 绝对路径读取
// Reader read = new FileReader(new File("f:\\test.sql"));
// 从class目录下直接读取
Resource resource = new ClassPathResource("static/"+path);
Reader read = Resources.getResourceAsReader(((ClassPathResource) resource).getPath());
runner.runScript(read);
runner.closeConnection();
conn.close();
System.out.println("sql脚本执行完毕");
}
}
3、调用util
ScriptRunnerExecSqlUtils.executesql(dataSource,"UPDATE_13.sql");
附:
文件路径:
SQL写法 (用分号隔开逐行运行)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vw_qdtjdmh]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vw_qdtjdmh];
CREATE VIEW vw_qdtjdmh
AS
select h.djbh,kehu.khdm
from qdtjdmh as h,kehu
where kehu.TZSY=0
and( (h.XZDM=0 and kehu.QDDM=h.qddm) or (h.XZDM=0 and kehu.KHDM =h.KHDM ));
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vw_qdtjd]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vw_qdtjd];
CREATE VIEW vw_qdtjd
AS
select d.djbh,d.lxdj,h.khdm,p.SPDM,p.JE4, p.JE5, p.JE6, p.JE7, p.JE8, p.ZK,(case when (substring(d.lxdj,6,1) = 1) then 1 else 0 end) AS zktz
from qdtjd as d,vw_qdtjdmh as h,qdtjdmp p
where d.DJBH=h.djbh and d.DJBH=p.DJBH and d.JZ=1 and d.SP=0
and getDate() between d.RQ_1 and d.rq_2;