MYSQL查询成java jdbc准备语句，报错: com. mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException

Question

我有以下有效的 MYSQL 查询(它对两个表进行了数据透视连接):

SET @sql = NULL;
SELECT  
 GROUP_CONCAT(DISTINCT
    CONCAT(
    'MAX(IF(sk.keywords = ''', sk.keywords,''', kr.rank, NULL)) AS ',  CONCAT('`',sk.keywords, '`')
    )
) INTO @sql
 FROM 
     search_keywords sk
    WHERE sk.product_id = 2 AND sk.active = 'Y'; 

SET @sql = CONCAT('SELECT eval_date, ', @sql, '  
                   FROM keyword_ranking AS kr 
                   LEFT JOIN  search_keywords AS sk 
                    ON kr.search_keywords_id = sk.id 
                   GROUP BY eval_date');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

我想将此查询插入到 java 准备语句中。如果我只是复制它，我会得到一个 misform sql 语法错误:

st = connection.prepareStatement(
              "SET @sql = NULL; "
            + "SELECT "
            + "GROUP_CONCAT(DISTINCT "
            + "CONCAT("
            + "'MAX(IF(sk.keywords = ''', sk.keywords,''', kr.rank, NULL)) AS ',  CONCAT('`',sk.keywords, '`')"
            + ") INTO @sql "
            + "FROM search_keywords sk "
            + "WHERE sk.product_id = ? AND sk.active = ?; "
            + "SET @sql = CONCAT('SELECT eval_date, ', @sql, ' "
            + "FROM keyword_ranking AS kr  "
            + "LEFT JOIN  search_keywords AS sk  "
            + "ON kr.search_keywords_id = sk.id  "
            + "GROUP BY eval_date'); "
            + "PREPARE stmt FROM @sql; "
            + "EXECUTE stmt; "
            + "DEALLOCATE PREPARE stmt; "
        );

错误:

Error 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 'SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(sk.keywords = CONCAT(''', sk.keyword' at line 1

Answer 1

谢谢@Stan 和@Sirko。我相信你们都是对的。我首先尝试了交易，但我也遇到了这个错误，所以要么我处理了错误的交易，要么我仍然不得不以某种方式转义单引号。因为我已经尝试了很多不同的转义变体，所以我想尝试一下存储过程。这行得通!!!

对于其他有兴趣创建存储过程的人，我将在下面粘贴我的代码:

Mysql中的存储过程:

CREATE DEFINER=`gobiran`@`%` PROCEDURE `getKeywordHistoryByProductId`(IN PRODUCT_ID INT(11))
BEGIN

SET @sql = NULL;

SELECT  
 GROUP_CONCAT(DISTINCT
    CONCAT(
    'MAX(IF(sk.keywords = ''', sk.keywords,''', kr.rank, NULL)) AS ',  CONCAT('`',sk.keywords, '`')
    )
) INTO @sql
 FROM 
     search_keywords sk
    WHERE sk.product_id = PRODUCT_ID AND sk.active = 'Y'; 

SET @sql = CONCAT('SELECT eval_date, ', @sql, '  
                   FROM keyword_ranking AS kr 
                   LEFT JOIN  search_keywords AS sk 
                    ON kr.search_keywords_id = sk.id 
                   GROUP BY eval_date');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END

调用存储过程:

ResultSet rs = null;
        Connection connection = DataBaseHelper.getConnection();
        CallableStatement stmt = null;
        try {

            stmt = connection.prepareCall("{ call getKeywordHistoryByProductId(?) }");
            stmt.setInt(1, productId);
            rs = stmt.executeQuery();

// Thats it, now work with the Resultset ... 
            ResultSetMetaData md = rs.getMetaData();

            int columns = md.getColumnCount();
            List<Object[]> result = new ArrayList<Object[]>();
            String[] header = new String[columns];
            int index = 0;
            while (rs.next()){
                Object[] entry = new Object[columns];
                for (int i = 0; i < columns; i++) {
                    if(index == 0) {
                        header[i] = md.getColumnName(i+1);
                    }
                    entry[i] = rs.getObject(i+1);
                }
                if(index == 0) {
                    result.add(header);
                }
                result.add(entry);
                index++;                
            }

...只是形式上的:完成 try/catch/finally 子句并在 finnaly 中关闭 stmt、connection、rs