Issue
I am trying to create a web application using Java Spring MVC. This web mostly will do CRUD function (Create-Read-Update-Delete).
Recently, I got this error:
HTTP Status 500 - Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select * from assignment where username=reza]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'reza' in 'where clause'
This is my DAO file:
@Override
public List < Assignment > showAllAssignment(String username) {
String sql = "select * from assignment where username=" + username;
return jdbcTemplate.query(sql, new AssignmentMapper());
}
This is my controller
@RequestMapping(value = "/showAllAssignment/{reqUserName}/show", method = RequestMethod.GET)
public ModelAndView showAllAssignment(@PathVariable("reqUserName") String reqUserName) {
List < Assignment > list = new ArrayList < Assignment > ();
list = assignmentService.showAllAssignment(reqUserName);
ModelAndView mav = new ModelAndView("show_All_Assignments");
mav.addObject("assignment", list);
return mav;
}
Further error that I got:
2018-05-03 01:55:08,232 [org.springframework.web.servlet.mvc.method.annotation.ExceptionHandlerExceptionResolver]-[DEBUG] Resolving exception from handler [public org.springframework.web.servlet.ModelAndView org.assignment.controller.AssignmentController.showAllAssignment(java.lang.String)]: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select * from assignment where username=reza]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'reza' in 'where clause'
2018-05-03 01:55:08,234 [org.springframework.web.servlet.mvc.annotation.ResponseStatusExceptionResolver]-[DEBUG] Resolving exception from handler [public org.springframework.web.servlet.ModelAndView org.assignment.controller.AssignmentController.showAllAssignment(java.lang.String)]: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select * from assignment where username=reza]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'reza' in 'where clause'
2018-05-03 01:55:08,234 [org.springframework.web.servlet.mvc.support.DefaultHandlerExceptionResolver]-[DEBUG] Resolving exception from handler [public org.springframework.web.servlet.ModelAndView org.assignment.controller.AssignmentController.showAllAssignment(java.lang.String)]: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select * from assignment where username=reza]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'reza' in 'where clause'
2018-05-03 01:55:08,235 [org.springframework.web.servlet.DispatcherServlet]-[DEBUG] Could not complete request
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select * from assignment where username=reza]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'reza' in 'where clause'
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:235)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1402)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:388)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:446)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:456)
at org.assignment.dao.AssignmentDaoImpl.showAllAssignment(AssignmentDaoImpl.java:67)
at org.assignment.service.AssignmentServiceImpl.showAllAssignment(AssignmentServiceImpl.java:39)
at org.assignment.controller.AssignmentController.showAllAssignment(AssignmentController.java:193)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:209)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:870)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:776)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:991)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:925)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:978)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:870)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:855)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:94)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:502)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1132)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:684)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1539)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1495)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'reza' in 'where clause'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:389)
at com.mysql.jdbc.Util.getInstance(Util.java:372)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:980)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3835)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3771)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2531)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2489)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1446)
at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:433)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:376)
... 42 more
The problem is I would like to select data inside my table assignment, where the username is define.
For example the String username1 = 'reza'; But when I try to show all data using syntax in above select * .... where username="+username1;
The result, the system read the 'reza' as column not as the value on column.
Any one can help me to solve this problem?
Solution
The SQL query you provided wasn't proper SQL, as the error suggested:
try String sql = "select * from assignment where username='"+username+"';";
instead of String sql = "select * from assignment where username="+username;
Answered By - Gewure
Answer Checked By - Candace Johnson (JavaFixing Volunteer)
