java - 执行 jdbctemplate 查询时出现异常

Question

使用 jdbctemplate 执行查询时出现以下异常:

类型异常报告

message Request processing failed; nested exception is org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [select * from event where 'starting' = '?' and 'ending' = '?' and 'doj' = '?' ]; Parameter index out of range (1 > number of parameters, which is 0).; nested exception is java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).

description The server encountered an internal error that prevented it from fulfilling this request.

exception

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [select * from event where 'starting' = '?' and 'ending' = '?' and 'doj' = '?' ]; Parameter index out of range (1 > number of parameters, which is 0).; nested exception is java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:978)
    org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:868)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:842)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
root cause

org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [select * from event where 'starting' = '?' and 'ending' = '?' and 'doj' = '?' ]; Parameter index out of range (1 > number of parameters, which is 0).; nested exception is java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
    org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:108)

我的代码如下:

public List<SearchPageModel> search(SearchPageModel searchQuery) {
        List<SearchPageModel> searchResultList = new ArrayList<SearchPageModel>();
        String sql = "select * from event where 'starting' = '?' and 'ending' = '?' and 'doj' = '?' ";
        Object[] parameters = {searchQuery.getFrom(), searchQuery.getTo(),
                searchQuery.getDate()};

        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql,parameters);
        for (Map<String, Object> row : rows) {
            SearchPageModel searchResult = new SearchPageModel();
            searchResult.setFrom((String) row.get("starting"));
            searchResult.setTo((String) row.get("ending"));
            searchResult.setDate((String) row.get("doj"));
            searchResultList.add(searchResult);
        }

        return searchResultList;
    }

不明白为什么？

编辑 1:删除 '' 后出现以下异常

类型异常报告

message Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select * from event where starting = ? and ending = ? and doj = ? ]; nested exception is java.sql.SQLSyntaxErrorException: 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 'starting = 'Delhi' and ending = 'Gurgaon' and doj = '14 Feb'' at line 1

description The server encountered an internal error that prevented it from fulfilling this request.

exception

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select * from event where starting = ? and ending = ? and doj = ? ]; nested exception is java.sql.SQLSyntaxErrorException: 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 'starting = 'Delhi' and ending = 'Gurgaon' and doj = '14 Feb'' at line 1
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:978)
    org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:868)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:842)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
root cause

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select * from event where starting = ? and ending = ? and doj = ? ]; nested exception is java.sql.SQLSyntaxErrorException: 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 'starting = 'Delhi' and ending = 'Gurgaon' and doj = '14 Feb'' at line 1
    org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
    org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
    org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649)
    org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:684)
    org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:716)
    org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:726)

Answer 1

当你像这样引用问号时，你是在告诉 Spring 你想要一个 ? 的文字字符串。尝试删除引号...

String sql = "select * from event where `starting` = ? and ending = ? and doj = ? ";

编辑:抱歉，也删除了关键字周围的引号。已修复。