java - 如何使用 Spring-MVC 创建 "Update"/"Edit"的 SQL 语法？

Question

我正在构建一个程序，我只是尝试添加编辑功能，然后更新MySQL中的某些数据。但是，我遇到了一个错误。

我看到的错误可能是在 Controller 中。

这是我的 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");

    }
}

这是我的 JSP 文件:

<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>

这是我的 DAO 文件:

@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);
}

当我想将编辑数据提交到MySQL时，出现错误。

这是我的Assignment 类:

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; }
}

这是错误，当我成功更新/编辑mysql中的数据后，想要返回到showAllAssignment目录页面:

    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

Answer 1

尝试修改您的 SQL 代码:

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

就像:

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

有时我们需要小心在SQL上加引号。

此外，

尝试将 modelAttribute="assignment" 添加到 form:form 标记中。这对于将来自 form 的操作 attribute 的值设置为 id 作为 Assignment 对象的属性

非常有用