[FIXED] How create SQL Syntax for "Update"/"Edit" using Spring-MVC?

Issue

I am building a program, and I just tried to put edit function, and then update certain data in MySQL. But, I got an error.

The error I saw it might be in the controller.

This is my controller:

package org.assignment.controller;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.assignment.dao.AssignmentDao;
import org.assignment.pojo.Assignment;
import org.assignment.service.AssignmentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.support.RedirectAttributes;

import java.util.ArrayList;
import java.util.List;


@Controller
public class AssignmentController {

    @Autowired
    public AssignmentService assignmentService;

    @Autowired
    public AssignmentDao assignmentDao;

    @RequestMapping(value = "/addAssignment", method =RequestMethod.GET)
    public ModelAndView showRegister(HttpServletRequest request, HttpServletResponse response) {
        ModelAndView mav = new ModelAndView("add_Assignment");
        mav.addObject("assignment", new Assignment());
        return mav;
    }


    @RequestMapping(value = "/successAddAssignment", method =     RequestMethod.POST)
    public ModelAndView addAssignment(HttpServletRequest request, HttpServletResponse response,
                                @ModelAttribute("assignment") Assignment assignment) {
        assignmentService.addAssignment(assignment);
        return new ModelAndView("success_Add_Assignment", "code_module", assignment.getCode_module());
    }



    @RequestMapping(value = "/showAllAssignment", method = RequestMethod.GET)
    public ModelAndView showAllAssignment(HttpServletRequest request, HttpServletResponse response) {
        List<Assignment> list = new ArrayList<Assignment>();
        list = assignmentService.showAllAssignment();
        for (Assignment a:list) {
            System.out.println(a.getId() +" "+ a.getName_module() +" "+ a. getDate() +" "+ a.getTime());
    }
        ModelAndView mav = new ModelAndView("show_All_Assignments");
        mav.addObject("assignment", list);
        return mav;
    }


    /* It updates model object. */
    @RequestMapping(value="/assignment/{id}/edit")
    public ModelAndView editAssignment(@PathVariable int id){
        Assignment a = assignmentDao.editAssignment(id);
        return new ModelAndView("edit_Assignment","command", a);
    }

    /* It updates model object. */
    @RequestMapping(value="/assignment/{id}/saveEditAssignment", method = RequestMethod.POST)
    public String editSave(HttpServletRequest request, HttpServletResponse response,
                                 @ModelAttribute("assignment") Assignment assignment, final RedirectAttributes redirectAttributes){
        redirectAttributes.addFlashAttribute("css", "Success");
        redirectAttributes.addFlashAttribute("msg", "The user is updated");

        assignmentDao.update(assignment);
        return ("redirect:/showAllAssignment");

    }
}

this is my JSP file:

<form:form id="regForm" action="saveEditAssignment" method="post">
    <table align="center">
        <tr>
            <td>
                <form:label path="code_module">Code Module</form:label>
            </td>
            <td>
                <form:input path="code_module"/>
            </td>
        </tr>
        <tr>
            <td>
                <form:label path="name_module">Name Module</form:label>
            </td>
            <td>
                <form:input path="name_module"/>
            </td>
        </tr>

        <tr>
            <td>
                <form:label path="description">Description</form:label>
            </td>
            <td>
                <form:input path="description"  />
            </td>
        </tr>
        <tr>
            <td>
                <form:label path="date">Date</form:label>
            </td>
            <td>
                <form:input type="text" path="date"  />
            </td>
        </tr>

        <tr>
            <td>
                <form:label path="time">Time</form:label>
            </td>
            <td>
                <form:input path="time" name="time" id="time" type="time" step="2"/>
            </td>
        </tr>
        <tr>
            <td>
                <form:button id="addAssignment" name="addAssignment">Submit</form:button>
            </td>
        </tr>  
    </table>
</form:form>

this is my DAO file:

@Override
public void update(Assignment assignment) {
    String sql="update assignment set date='"+ assignment.getDate()+"', " +
            "time="+assignment.getTime()+", " +
            "code_module='"+assignment.getCode_module()+", " +
            "name_module='"+assignment.getName_module()+", " +
            "description='"+assignment.getDescription()+
            "' where id="+assignment.getId()+"";
    jdbcTemplate.update(sql);
}

I got the error when I want to submit the edit data into MySQL.

This is my Assignment class:

public class Assignment {

    private int id;
    private Time time;
    private String date;
    private String code_module;
    private String name_module;

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    private String description;



    public int getId() { return id; }

    public void setId(int id) { this.id = id; }

    public Time getTime() { return time; }

    public void setTime(Time time) { this.time = time; }

    public String getDate() { return date; }

    public void setDate(String date) { this.date = date; }

    public String getCode_module() { return code_module; }

    public void setCode_module(String code_module) { this.code_module = code_module; }

    public String getName_module() { return name_module; }

    public void setName_module(String name_module) { this.name_module = name_module; }
}

This is the error, after I success update/edit the data in mysql, and want to go back to showAllAssignment directory page:

    2018-04-25 08:52:43,884 [org.springframework.web.servlet.DispatcherServlet]-[DEBUG]  DispatcherServlet with name 'spring-mvc' processing POST request for [/assignment/30/saveEditAssignment]
    2018-04-25 08:52:43,885 [org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerMapping]-[DEBUG] Looking up handler method for path /assignment/30/saveEditAssignment
    2018-04-25 08:52:43,886 [org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerMapping]-[DEBUG] Returning handler method [public java.lang.String org.assignment.controller.AssignmentController.editSave(javax.servlet.http.HttpServletRequest,javax.servlet.http.HttpServletResponse,org.assignment.pojo.Assignment,org.springframework.web.servlet.mvc.support.RedirectAttributes)]
    2018-04-25 08:52:43,886 [org.springframework.beans.factory.support.DefaultListableBeanFactory]-[DEBUG] Returning cached instance of singleton bean 'assignmentController'
    2018-04-25 08:52:43,909 [org.springframework.web.cors.DefaultCorsProcessor]-[DEBUG] Skip CORS processing: request is from same origin
    2018-04-25 08:52:43,914 [org.springframework.validation.DataBinder]-[WARN] Skipping URI variable 'id' since the request contains a bind value with the same name.
    2018-04-25 08:52:43,918 [org.springframework.core.annotation.AnnotationUtils]-[DEBUG] Failed to meta-introspect annotation [interface org.springframework.web.bind.annotation.ModelAttribute]: java.lang.NullPointerException
    2018-04-25 08:52:43,919 [org.springframework.jdbc.core.JdbcTemplate]-[DEBUG] Executing prepared SQL update
    2018-04-25 08:52:43,919 [org.springframework.jdbc.core.JdbcTemplate]-[DEBUG] Executing prepared SQL statement [update assignment set date=?, time=?, code_module=?, name_module=?, description=? where id=?]
    2018-04-25 08:52:43,919 [org.springframework.jdbc.datasource.DataSourceUtils]-[DEBUG] Fetching JDBC Connection from DataSource
    2018-04-25 08:52:43,919 [org.springframework.jdbc.datasource.DriverManagerDataSource]-[DEBUG] Creating new JDBC DriverManager Connection to [jdbc:mysql://143.167.9.232:3306/db_assignment]
    2018-04-25 08:52:44,231 [org.springframework.jdbc.core.JdbcTemplate]-[DEBUG] SQL update affected 1 rows
    2018-04-25 08:52:44,258 [org.springframework.jdbc.datasource.DataSourceUtils]-[DEBUG] Returning JDBC Connection to DataSource
    2018-04-25 08:52:44,264 [org.springframework.beans.factory.support.DefaultListableBeanFactory]-[DEBUG] Invoking afterPropertiesSet() on bean with name 'redirect:/show_All_Assignments/'
    2018-04-25 08:52:44,264 [org.springframework.web.servlet.DispatcherServlet]-[DEBUG] Rendering view [org.springframework.web.servlet.view.RedirectView: name 'redirect:/show_All_Assignments/'; URL [/show_All_Assignments/]] in DispatcherServlet with name 'spring-mvc'
    2018-04-25 08:52:44,267 [org.springframework.web.servlet.support.SessionFlashMapManager]-[DEBUG] Saving FlashMap=FlashMap [attributes={msg=The user is updated, css=Success}, targetRequestPath=/show_All_Assignments/, targetRequestParams={}]
    2018-04-25 08:52:44,268 [org.springframework.web.servlet.DispatcherServlet]-[DEBUG] Successfully completed request
    2018-04-25 08:52:44,274 [org.springframework.web.servlet.DispatcherServlet]-[DEBUG] DispatcherServlet with name 'spring-mvc' processing GET request for [/show_All_Assignments/]
    2018-04-25 08:52:44,274 [org.springframework.web.servlet.support.SessionFlashMapManager]-[DEBUG] Retrieved FlashMap(s): [FlashMap [attributes={msg=The user is updated, css=Success}, targetRequestPath=/show_All_Assignments/, targetRequestParams={}]]
    2018-04-25 08:52:44,276 [org.springframework.web.servlet.support.SessionFlashMapManager]-[DEBUG] Found matching FlashMap(s): [FlashMap [attributes={msg=The user is updated, css=Success}, targetRequestPath=/show_All_Assignments/, targetRequestParams={}]]
    2018-04-25 08:52:44,276 [org.springframework.web.servlet.support.SessionFlashMapManager]-[DEBUG] Removing FlashMap(s): [FlashMap [attributes={msg=The user is updated, css=Success}, targetRequestPath=/show_All_Assignments/, targetRequestParams={}]]
    2018-04-25 08:52:44,276 [org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerMapping]-[DEBUG] Looking up handler method for path /show_All_Assignments/
    2018-04-25 08:52:44,278 [org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerMapping]-[DEBUG] Did not find handler method for [/show_All_Assignments/]
    2018-04-25 08:52:44,278 [org.springframework.web.servlet.PageNotFound]-[WARN] No mapping found for HTTP request with URI [/show_All_Assignments/] in DispatcherServlet with name 'spring-mvc'
    2018-04-25 08:52:44,278 [org.springframework.web.servlet.DispatcherServlet]-[DEBUG] Successfully completed request

Solution

Try to modify your SQL code:

"' where id="+assignment.getId()+"";

to be like:

"'where id="+assignment.getId()+"'";

Sometimes we need to be careful to put the quotation mark on SQL.

Moreover,

Try to add modelAttribute="assignment" into form:form tag. This will be useful to set value from action attribute from form into id as attribute of Assignment object

Answered By - Agnes Palit
Answer Checked By - David Goodson (JavaFixing Volunteer)