java - Spring JdbcDao支持

Question

请帮帮我!我有麻烦了。我有下一个 SQL 查询

SET @sql = '';

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT( 
      'getInterviewsNumber(\'', name, 
'\', vtable.position)',
' AS \'',
      name, '\''
    ) separator ','
  ) INTO @sql
FROM interview_portal.departments;


SET @sql = CONCAT('SELECT distinct vtable.position,  ',@sql, ' FROM inter_count as vtable');

PREPARE stmt FROM @sql;
EXECUTE stmt;

DEALLOCATE PREPARE stmt;

它在 MySQL workbench 中运行良好。但是我如何执行这个查询并使用 Spring JdbcDaoSupport 获取 ResultSet？我尝试了很多变体，但我遇到了 MySQLSyntaxErrorException。

感谢您的帮助!

这是我的异常(exception)

SEVERE: Servlet.service() for servlet [generalDispatcher] in context with path [/InterviewPortal] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SET @sql = ''; SELECT GROUP_CONCAT( DISTINCT CONCAT ( 'getInterviewsNumber(\'', name, '\', vtable.position)',' AS \'', name, '\'')  separator ',') INTO @sql FROM interview_portal.departments; SET @sql = CONCAT('SELECT distinct vtable.position, ', @sql, ' FROM inter_count as vtable'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;]; nested exception is 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 ( 'getInterviewsNumber(\'', name, '\', vtab' at line 1] with root 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 'SELECT GROUP_CONCAT( DISTINCT CONCAT ( 'getInterviewsNumber(\'', name, '\', vtab' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)

这是我的代码:

String sql = "SET @sql = '';" +
                " SELECT" +
                " GROUP_CONCAT(" +
                " DISTINCT CONCAT ( " +
                "'getInterviewsNumber(\\'', name, " +
                "'\\', vtable.position)'," +
                "' AS \\'', name, '\\'') " +
                " separator ',') INTO @sql " +
                "FROM interview_portal.departments;" +
                " SET @sql = CONCAT('SELECT distinct vtable.position, '," +
                " @sql, ' FROM inter_count as vtable');" +
                " PREPARE stmt FROM @sql;" +
                " EXECUTE stmt;" +
                " DEALLOCATE PREPARE stmt;" ;
        getJdbcTemplate().batchUpdate(temp);
                //"ORDER BY " + query.getSortType().getColumnName() + ((query.isDescendingSort()) ? " DESC" : " ASC;");
        Object[] args = new Object[0];
        return getJdbcTemplate().query(sql, args,
                positionItemMapper);

Answer 1

首先，您必须使用 SQL 代码创建一个存储过程，请参阅 MySQL documentation .

然后你就可以用JDBC模板调用它了:

jdbcTemplate.update("call PROC_NAME (?, ?)", param1, param2);

Spring documentation了解更多详情。