使用ScriptRunner对象做sql文件导入时遇到的问题

业务场景:

服务端是挂在公网上用来提供数据同步功能,考虑到有可能客户端由于某种情况是不允许连接外网的,所以服务端提供一个接口用来下载全量sql脚本(当然这些数据是某官网公布出来的数据,不考虑数据安全问题),然后客户端通过拷贝或内网传输在客户端进行手动上传,客户端代码进行执行sql脚本,将全量数据同步到客户端对应的数据库以完成数据同步。

问题复现:

ScriptRunner对象是org.apache.ibatis.jdbc.ScriptRunner包下的,在读取sql脚本时,建表语句报错如下:

Error executing: CREATE TABLE `det_appl` (
  `id` varchar(255) NOT NULL COMMENT '唯一标识',
  `task_name` varchar(50) DEFAULT NULL COMMENT '任务名称',
  `user_name` varchar(50) DEFAULT '' COMMENT '用户',
  `user_email` varchar(255) DEFAULT NULL COMMENT '邮箱',
  `company_name` varchar(100) DEFAULT '' COMMENT '所属公司',
  `company_station` varchar(255) DEFAULT NULL COMMENT '公司地址',
  `user_telephone` varchar(20) DEFAULT '' COMMENT '电话',
  `user_description` varchar(255) DEFAULT '' COMMENT '描述',
  `device_description` varchar(255) DEFAULT NULL,
  `status` varchar(20) DEFAULT '' COMMENT '状态 (0:未提交; 2:检测中; 3:检测未通过;4:检测通过
.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''状态 (0:未提交; 2:检测中; 3:检测未通过;4:检测通过' at line 13
2018-12-12 16:47:26.250 [http-nio-5552-exec-7] ERROR [com.xxxx.xxxx.controller.XXXController:75] [${spring.zipkin.service.name:${spring.application.name:-}},,,] - org.apache.ibatis.jdbc.RuntimeSqlException: Error executing: CREATE TABLE `det_appl` (
  `id` varchar(255) NOT NULL COMMENT '唯一标识',
  `task_name` varchar(50) DEFAULT NULL COMMENT '任务名称',
  `task_description` varchar(255) DEFAULT NULL COMMENT '任务名称',
  `user_name` varchar(50) DEFAULT '' COMMENT '用户',
  `user_email` varchar(255) DEFAULT NULL COMMENT '邮箱',
  `company_name` varchar(100) DEFAULT '' COMMENT '所属公司',
  `company_station` varchar(255) DEFAULT NULL COMMENT '公司地址',
  `user_telephone` varchar(20) DEFAULT '' COMMENT '电话',
  `user_description` varchar(255) DEFAULT '' COMMENT '描述',
  `device_name` varchar(50) DEFAULT NULL COMMENT '设备名称(包括软件、漏洞、芯片、硬件)',
  `device_description` varchar(255) DEFAULT NULL,
  `status` varchar(20) DEFAULT '' COMMENT '状态 (0:未提交; 2:检测中; 3:检测未通过;4:检测通过
.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''状态 (0:未提交; 2:检测中; 3:检测未通过;4:检测通过' at line 13

看的一脸懵X,建表语句没有问题啊,然后删掉这个表相关的语句,继续测试sql脚本,结果又出现另一个表的建表语句报错,基本上都是卡在了类似的"COMMENT '状态 (0:未提交; 2:检测中; .....)",由于是全量的sql脚本,这样的状态字段有很多,偏偏就这两个有问题。而且在可视化工具(SQLyogEnt)上,直接执行sql脚本时没有问题的啊。测试了好几拨,终于发现是因为"COMMENT '状态 (0:未提交; 2:检测中; .....)"中使用了英文的";",而在解析sql脚本时,ScriptRunner对象进行了一个设置,定义命令间的分隔符为英文的";",所以执行sql语句时,遇到分号就当做一个命令,结果不是一个完整的建表语句,然后就报错了。

问题解决:

感觉这个问题超智障,解决办法就是将那几个相关的表中的建表语句COMMENT 中的分号改成中文格式就可以了。

下面记录下ScriptRunner对象的使用:

import javax.servlet.http.HttpServletRequest;
import javax.sql.DataSource;

import org.apache.commons.lang.StringUtils;
import org.apache.ibatis.jdbc.ScriptRunner;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.PropertySource;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

@Service
@Transactional
@PropertySource(value = {"classpath:jdbc.properties","classpath:remote.properties"} , ignoreResourceNotFound = true)
public class XXXServiceImpl implements XXXService{
	@Autowired
	private DataSource dataSource;
	
	@Override
	@Transactional(readOnly=false)
	/** 手动更新:导入sql文件,实现数据同步 */
	public void readSqlFile(MultipartFile sqlFile, HttpServletRequest request) throws Exception {
		if (sqlFile != null && !sqlFile.isEmpty() && StringUtils.isNotEmpty(sqlFile.getOriginalFilename())) {
			execSqlFileByMysql(sqlFile);
		}
	}
		
	private void execSqlFileByMysql(MultipartFile sqlFile) throws Exception {
        Exception error = null;
        Connection conn = null;
        try {
            //由于使用的是springboot框架,所以直接使用数据源获取连接对象
            conn = dataSource.getConnection();
            //设置不自动提交
            conn.setAutoCommit(false);
            ScriptRunner runner = new ScriptRunner(conn);
            /* 设置不自动提交
             * runner.setAutoCommit(false);
             * setStopOnError参数作用：遇见错误是否停止；
             * （1）false，遇见错误不会停止，会继续执行，会打印异常信息，并不会抛出异常，当前方法无法捕捉异常无法进行回滚操作，无法保证在一个事务内执行；
             * （2）true，遇见错误会停止执行，打印并抛出异常，捕捉异常，并进行回滚，保证在一个事务内执行；
             */
            runner.setStopOnError(true);
            /* 按照那种方式执行
             * 		方式一：true则获取整个脚本并执行；
             * 		方式二：false则按照自定义的分隔符每行执行；
             */
            runner.setSendFullScript(false);
            //定义命令间的分隔符
            runner.setDelimiter(";");
            runner.setFullLineDelimiter(false);
            //设置是否输出日志，null不输出日志，不设置自动将日志输出到控制台
            runner.setLogWriter(null);
            //如果又多个sql文件，可以写多个runner.runScript(xxx),
            runner.runScript(new InputStreamReader(sqlFile.getInputStream(), "utf-8"));
            conn.commit();
        } catch (Exception e) {
            conn.rollback();
            error = e;
        } finally {
            close(conn);
        }
        if (error != null) {
            throw error;
        }
    }
    private void close(Connection conn) {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (Exception e) {
            if (conn != null) {
                conn = null;
            }
        }
    }	
}