利用Ant 执行SQL 脚本

短命女 2023-10-04 18:35 44阅读 0赞

`pom.xml` 添加ant

<dependency>
      <groupId>org.apache.ant</groupId>
      <artifactId>ant</artifactId>
      <version>1.10.9</version>
      <exclusions>
        <exclusion>
          <artifactId>tools</artifactId>
          <groupId>com.sun</groupId>
        </exclusion>
      </exclusions>
    </dependency>

import lombok.extern.slf4j.Slf4j;
    import org.apache.tools.ant.Project;
    import org.apache.tools.ant.taskdefs.SQLExec;
    import org.springframework.util.ResourceUtils;
    
    import java.io.File;
    import java.util.Properties;
    
    /**
     * <p>导入sql文件到数据库</p>
     *
     * @Date 2021/4/13 17:48
     */
    @Slf4j
    public class ScriptSql {
        
    	// test.sql文件放在resources目录下
        private static final String SCRIPT_SQL = "classpath:test.sql";
    
        public static void exeSQLSript(Properties prop) throws Exception {
        
            //读取sql脚本文件保存到数组中
            String[] sqlName = {
        SCRIPT_SQL};
            //循环执行sql脚本
            for (int i = 0; i < sqlName.length; i++) {
        
                //利用 Ant 的SQL Task来实现执行SQL 脚本的功能。ant 包中的 SQLExec类的扩展
                SQLExec sqlExec = new SQLExec();
                String mysqlDriver = prop.getProperty("driver","com.mysql.cj.jdbc.Driver");
                String url = prop.getProperty("url");
                // 获取用户密码，默认为root/root
                String username = prop.getProperty("username","root");
                String password = prop.getProperty("password","root");
    
                // 设置数据库参数
                sqlExec.setDriver(mysqlDriver);
                sqlExec.setUrl(url);
                sqlExec.setUserid(username);
                sqlExec.setPassword(password);
                //防止乱码，sql文件也需要设置为utf-8
                sqlExec.setEncoding("UTF8");
                //设置sql脚本
                File file = ResourceUtils.getFile(SCRIPT_SQL);
                sqlExec.setSrc(file);
                // 设置是否输出 true-打印,false-不打印
                sqlExec.setPrint(true);
                // 要指定这个属性，不然会出错
                sqlExec.setProject(new Project());
                sqlExec.setOutputEncoding("UTF8");
                sqlExec.setAutocommit(true);
                sqlExec.execute();
                log.info("sqlExec execute success!");
            }
        }
    
        public static void main(String[] args) {
        
            Properties prop = new Properties();
            prop.setProperty("url",  "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&serverTimezone=Asia/Shanghai");
            prop.setProperty("username", "root");
            prop.setProperty("password", "root");
            try {
        
                ScriptSql.exeSQLSript(prop);
            } catch (Exception e) {
        
                e.printStackTrace();
            }
        }
    }

注意：这里的`test`数据源是已经存在的，否则会报异常`Unknown database 'test'`  
执行之前  
![在这里插入图片描述][eba7a945d29448f1891aefa4c7f0bcbe.png]  
执行之后  
![在这里插入图片描述][49854993c82240a69a5bf5a2626bd383.png]  
自动创建了相关的表  
可以应用到系统升级时自动更新表结构或创建新表，避免人工干预

[eba7a945d29448f1891aefa4c7f0bcbe.png]: https://img-blog.csdnimg.cn/eba7a945d29448f1891aefa4c7f0bcbe.png
[49854993c82240a69a5bf5a2626bd383.png]: https://img-blog.csdnimg.cn/49854993c82240a69a5bf5a2626bd383.png