Spring JdbcTemplate在创建存储过程时抛出错误

Question

我在为MariaDB创建SP时遇到了一个有趣的问题。当我直接从HeidiSQL（MySql的数据库客户端应用程序）执行DDL以下时，我可以很容易地创建SP。

DROP PROCEDURE IF EXISTS `sp_delete_merchant`;
CREATE PROCEDURE `sp_delete_merchant`(IN `mer_id` BIGINT)
BEGIN
  DECLARE stop BIT DEFAULT FALSE;
  DECLARE comm_id BIGINT;
  DECLARE commodities CURSOR FOR SELECT id FROM tbl_commodity WHERE merchant_id = mer_id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET stop = TRUE;
  # DELETE RECORDS FROM tbl_package2commodity BASED ON COMMODITY ID
  OPEN commodities;
    read_loop: LOOP
      FETCH commodities INTO comm_id;
      DELETE FROM tbl_package2commodity WHERE commodity_id = comm_id;
      IF stop THEN
        LEAVE read_loop;
      END IF;
    END LOOP;
  CLOSE commodities;
  # DELETE RECORDS FROM OTHER TABLES BASED ON MERCHANT ID
  DELETE FROM tbl_commodity WHERE merchant_id = mer_id;
  DELETE FROM tbl_package WHERE merchant_id = mer_id;
  DELETE FROM tbl_contact_person WHERE merchant_id = mer_id;
  DELETE FROM tbl_sales_force WHERE merchant_id = mer_id;
  DELETE FROM tbl_merchant WHERE id = mer_id;
END;

但是，当我尝试从Spring JdbcTemplate执行上述DDL时，如下代码：

public class DbStoreProceduresCreator implements InitializingBean {
    private static Logger log = LoggerFactory.getLogger(DbStoreProceduresCreator.class);
    @Value("classpath:com/litaal/commerce/config/setup_store_procedures.sql")
    private Resource createStoreProcedures;
    @Autowired
    private DataSource coreDS;
    @Override
    public void afterPropertiesSet() throws Exception {
        BufferedReader br = null;
        StringBuffer sql = new StringBuffer();
        try {
            InputStream is = createStoreProcedures.getInputStream();
            br = new BufferedReader(new InputStreamReader(is));
            String line;
            while ((line = br.readLine()) != null) {
                sql.append(line);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (br != null) {
                br.close();
            }
        }
        if (sql.length() > 0) {
            try {
                JdbcTemplate tmp = new JdbcTemplate(coreDS);
                tmp.execute(sql.toString());
            } catch (Exception e) {
                log.error(e.getMessage());
            }
        }
    }
}

它抛出如下错误：

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1

我已经试着在谷歌上搜索它，并更改我的脚本大约十次或更多次，但仍然找不到我的DDL有什么问题。

我之所以想从我的java应用程序创建SP，是为了让我的应用程序尽可能便携，而不需要对DB做任何事情。

Answer 1

事实证明，JdbcTemplate无法执行在选择/更新等过程中出现注释（#）（--）的SQL过程。JdbcTemplate确实支持语句开头的（--，#）注释，以便：

-- --------------------------------------------
-- Summary Comments!
-- Note: This comes before a delimited statement (;). 
# These are okay too.
-- --------------------------------------------
SELECT * 
FROM User
-- This comment fails.
WHERE id > 0;

以下是我如何分解（来自MySQL的）过程。

程序

USE `DbName`;
DROP procedure IF EXISTS `ProcName`;
DELIMITER $$
USE `DbName`$$
CREATE PROCEDURE `ProcName` ()
BEGIN
    # Here's a comment that would normally fail.
    SELECT 
        u.id        
    FROM
    User as u       
END$$
DELIMITER ;

分析器

StringBuilder builder = new StringBuilder();
String[] lines = script.split("n");
String delimiter = ";";
for (int i = 0; i < lines.length; i++) {
    String line = lines[i];
    if (line.contains("DELIMITER")) {
        String[] tokens = line.split(" ");
        delimiter = tokens[1];
        builder = new StringBuilder();
        continue;
    }
    //Comments cannot be executed properly by the JdbcTemplate, strip them out. 
    if (line.contains("#")) {               
        line = line.substring(0, line.indexOf('#'));
    }
    if (line.contains(delimiter)) {
        builder.append(line.replace(delimiter, ""));
        template.execute(builder.toString());
        builder = new StringBuilder();
    } else {
        builder.append(line);
    }
}

Answer 2

也许您的代码有几个问题，但其中之一是JdbcTemplate.execute(String sql)只执行一条语句

/**
 * Issue a single SQL execute, typically a DDL statement.
 * @param sql static SQL to execute
 * @throws DataAccessException if there is any problem
 */
void execute(String sql) throws DataAccessException;

因此，您必须将执行分为两个执行，一个用于DROP PROCEDURE IF EXISTS "sp_delete_merchant";，另一个用于其余部分

BTW：你应该看看org.springframework.jdbc.datasource.init.ScriptUtils

还有一件事让我有点担心，那就是你的字符串引号

Answer 3

终于。。。我找到了答案。问题在于JdbcTemplate如何执行SQL脚本。它逐行执行（用新行分隔）。所以我修改了部分代码，删除了所有新行，比如下面

....
if (sql.length() > 0) {
    String query = sql.toString().replaceAll("\n", " ");
    query = query.replaceAll("\t", " ");
    log.info(query);
    try {
        JdbcTemplate tmp = new JdbcTemplate(dataSource);
        tmp.execute(query);
    } catch (Exception e) {
        log.error(e.getMessage());
    }
}
...

现在一切都很有魅力。