您的位置: 首页  >  技术问答  >  如何使用Spring 4 MVC PathVariable参数查询数据库

如何使用Spring 4 MVC PathVariable参数查询数据库

分类: 技术问答 • 2022-07-18 10:28:22
问题描述：

需要解决的问题：我试图根据@RequestMapping API调用中给出的参数来查询数据库。如何使用Spring 4 MVC PathVariable参数查询数据库

海贼王，我面对错误，我当前的代码：

@RequestMapping(value ="/{serialNumber}", method = RequestMethod.GET) 
    @ResponseBody 
    public void searchByStudent(@PathVariable("serialNumber") String serialNumber) { 
     SqlRowSet resultSet = this.jdbcTemplate.queryForRowSet("SELECT * FROM STORE_ITEMS WHERE SERIALNUMBER = :serialNumber"); 
     System.out.println(resultSet); 
    }

这是错误通过邮差访问URL时（浏览器）我得到：

HTTP Status 500 - Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT * FROM STORE_ITEMS WHERE SERIALNUMBER = :serialNumber]; nested exception is java.sql.SQLSyntaxErrorException: [Vertica][VJDBC](4856) ERROR: Syntax error at or near ":" 

type Exception report 

message Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT * FROM STORE_ITEMS WHERE SERIALNUMBER = :serialNumber]; nested exception is java.sql.SQLSyntaxErrorException: [Vertica][VJDBC](4856) ERROR: Syntax error at or near ":" 

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: StatementCallback; bad SQL grammar [SELECT * FROM STORE_ITEMS WHERE SERIALNUMBER = :serialNumber]; nested exception is java.sql.SQLSyntaxErrorException: [Vertica][VJDBC](4856) ERROR: Syntax error at or near ":" 
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:982) 
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861) 
    javax.servlet.http.HttpServlet.service(HttpServlet.java:620) 
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) 
    javax.servlet.http.HttpServlet.service(HttpServlet.java:727) 
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) 
root cause 

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT * FROM STORE_ITEMS WHERE SERIALNUMBER = :serialNumber]; nested exception is java.sql.SQLSyntaxErrorException: [Vertica][VJDBC](4856) ERROR: Syntax error at or near ":" 
    org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:91) 
    org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) 
    org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) 
    org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:415) 
    org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:470) 
    org.springframework.jdbc.core.JdbcTemplate.queryForRowSet(JdbcTemplate.java:511) 
    com.company.project.controller.DatabaseController.searchBySerialNumber(DatabaseController.java:64) 
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
    java.lang.reflect.Method.invoke(Method.java:497) 
    org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:222) 
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137) 
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110) 
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:814) 
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:737) 
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) 
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959) 
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893) 
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) 
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861) 
    javax.servlet.http.HttpServlet.service(HttpServlet.java:620) 
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) 
    javax.servlet.http.HttpServlet.service(HttpServlet.java:727) 
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) 
root cause 

java.sql.SQLSyntaxErrorException: [Vertica][VJDBC](4856) ERROR: Syntax error at or near ":" 
    com.vertica.util.ServerErrorData.buildException(Unknown Source) 
    com.vertica.dataengine.VDataEngine.prepareImpl(Unknown Source) 
    com.vertica.dataengine.VDataEngine.prepare(Unknown Source) 
    com.vertica.dataengine.VDataEngine.prepare(Unknown Source) 
    com.vertica.jdbc.common.SStatement.executeNoParams(Unknown Source) 
    com.vertica.jdbc.common.SStatement.executeQuery(Unknown Source) 
    org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:454) 
    org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:404) 
    org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:470) 
    org.springframework.jdbc.core.JdbcTemplate.queryForRowSet(JdbcTemplate.java:511) 
    com.company.project.controller.DatabaseController.searchBySerialNumber(DatabaseController.java:64) 
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
    java.lang.reflect.Method.invoke(Method.java:497) 
    org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:222) 
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137) 
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110) 
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:814) 
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:737) 
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) 
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959) 
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893) 
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) 
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861) 
    javax.servlet.http.HttpServlet.service(HttpServlet.java:620) 
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) 
    javax.servlet.http.HttpServlet.service(HttpServlet.java:727) 
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) 
root cause 

com.vertica.support.exceptions.SyntaxErrorException: [Vertica][VJDBC](4856) ERROR: Syntax error at or near ":" 
    com.vertica.util.ServerErrorData.buildException(Unknown Source) 
    com.vertica.dataengine.VDataEngine.prepareImpl(Unknown Source) 
    com.vertica.dataengine.VDataEngine.prepare(Unknown Source) 
    com.vertica.dataengine.VDataEngine.prepare(Unknown Source) 
    com.vertica.jdbc.common.SStatement.executeNoParams(Unknown Source) 
    com.vertica.jdbc.common.SStatement.executeQuery(Unknown Source) 
    org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:454) 
    org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:404) 
    org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:470) 
    org.springframework.jdbc.core.JdbcTemplate.queryForRowSet(JdbcTemplate.java:511) 
    com.company.project.controller.DatabaseController.searchBySerialNumber(DatabaseController.java:64) 
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
    java.lang.reflect.Method.invoke(Method.java:497) 
    org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:222) 
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137) 
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110) 
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:814) 
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:737) 
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) 
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959) 
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893) 
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) 
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861) 
    javax.servlet.http.HttpServlet.service(HttpServlet.java:620) 
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) 
    javax.servlet.http.HttpServlet.service(HttpServlet.java:727) 
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) 
note The full stack trace of the root cause is available in the Apache Tomcat/7.0.52 logs.
使用

  • 数据库：我的输出控制台中

    错误：

    INFO: SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase, Hana]

    其他信息Vertica的（我不知道这可能是问题的基础在输出控制台上）

  • resultSet将包含大约5个数字的对象
    •

问：我怎么能解决这个问题？我知道这不是最安全的查询方式，但我试图确保我的逻辑先行。此外，是否有可能更好的方式来使用jdbcTemplate进行查询？

答

您正试图调用创建简单的Statement而不是PreparedStatement的方法，但您正尝试使用参数。

根据JavaDoc

执行查询的SqlRowSet，由于静态SQL。

使用JDBC语句，而不是PreparedStatement。如果要使用PreparedStatement执行静态查询，请使用重载的queryForRowSet方法，并将null作为参数数组。

我从来没有真的用这种方式来查询数据库。正如你所说，这可能不是最安全的方式，更不用说最舒服的了。但尝试使用超载queryForRowSet方法

@RequestMapping(value ="/{serialNumber}", method = RequestMethod.GET) 
@ResponseBody 
public void searchByStudent(@PathVariable("serialNumber") String serialNumber) { 
    SqlRowSet resultSet = this.jdbcTemplate.queryForRowSet("SELECT * FROM STORE_ITEMS WHERE SERIALNUMBER = ?", new Object[] {serialNumber}); 
    System.out.println(resultSet); 
}

相关推荐