java - 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 的原因是为了使我的应用程序尽可能可移植，而不需要对数据库执行任何操作。

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);
    }
}