SpringMVC中SQL语句执行失败，请求协助排查解决

问题场景 #

在SpringMVC项目中调用创建群组服务时，出现SQL语句执行失败的报错，核心为SQL语法异常。

相关代码 #

实体类代码 #

package com.ducnd.pojo;

/**
 *
 * @author DucND
 */
@Entity
@Table(name = "group")
@XmlRootElement
@NamedQueries({
    @NamedQuery(name = "Group1.findAll", query = "SELECT g FROM Group1 g"),
    @NamedQuery(name = "Group1.findById", query = "SELECT g FROM Group1 g WHERE g.id = :id"),
    @NamedQuery(name = "Group1.findByGroupname", query = "SELECT g FROM Group1 g WHERE g.groupname = :groupname"),
    @NamedQuery(name = "Group1.findByPurpose", query = "SELECT g FROM Group1 g WHERE g.purpose = :purpose")})
public class Group1 implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @NotNull
    @Column(name = "id")
    private Integer id;
    @Size(max = 45)
    @Column(name = "groupname")
    private String groupname;
    @Size(max = 45)
    @Column(name = "purpose")
    private String purpose;
    @JoinTable(name = "user_group", joinColumns = {
        @JoinColumn(name = "group_id", referencedColumnName = "id")}, inverseJoinColumns = {
        @JoinColumn(name = "user_id", referencedColumnName = "id")})
    @ManyToMany
    private Collection<User> userCollection;
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "group1")
    private Collection<UserSpendingGroup> userSpendingGroupCollection;
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "group1")
    private Collection<UserIncomeGroup> userIncomeGroupCollection;
    @JoinColumn(name = "user_id", referencedColumnName = "id")
    @ManyToOne
    private User userId;
}

控制器代码 #

@Controller
@ControllerAdvice
public class GroupController {
    
    @Autowired
    private GroupService groupService;
    
    
    @GetMapping("/create-group")
    public String createGroup(Model model){
        
        model.addAttribute("group", new Group1());
        
        return "createGroup";
    }
    
    @PostMapping("/create-group")
    public String createGroup(Model model, @ModelAttribute(value = "group")Group1 group, HttpSession session){
        User currentUser = (User) session.getAttribute("currentUser");
        group.setId(3);
        group.setUserId(currentUser);
        
        if(this.groupService.createGroup(group)==true){
            return "redirect:/";
        }
        return "createGroup";
    }
}

报错信息 #

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement

解决步骤 #

• 核心问题修复：表名使用SQL关键字
  group是SQL保留关键字，直接作为表名会导致语法解析错误。两种修复方案：

  1. 给表名添加转义符（无需修改数据库表名）：

     @Table(name = "`group`") // 反引号适配MySQL等数据库，其他数据库可使用对应转义符，比如Oracle用双引号
     

  2. 修改表名为非关键字，同时同步数据库表名：

     @Table(name = "groups")
     

• 额外检查项

  • 确认数据库表结构与实体类字段完全匹配，包括字段名、数据类型、非空约束等。
  • 控制器中手动设置group.setId(3)需确保该ID未被占用，避免主键冲突；如果是自增主键，建议删除手动设置ID的代码，并在实体类@Id注解上添加@GeneratedValue(strategy = GenerationType.IDENTITY)让数据库自动生成主键。

内容的提问来源于stack exchange，提问作者Đức Nguyễn Duc