JSP页面无法保存数据到数据库：SQL语法错误排查求助

错误栈信息 #

org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:478)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:380)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:328)
jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
Root Cause

jakarta.servlet.ServletException: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?,?,?)' at line 1
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:666)
org.apache.jsp.add_005fstu_jsp._jspService(add_005fstu_jsp.java:197)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:456)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:380)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:328)
jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
Root Cause

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?,?,?)' at line 1
com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:121)
com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1334)
com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2084)
com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1245)
org.apache.jsp.add_005fstu_jsp._jspService(add_005fstu_jsp.java:151)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:456)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:380)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:328)
jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)

前端HTML页面代码 #

<!DOCTYPE html>
<html>
    <head>
        <link rel="stylesheet" type="text/css" href="style1.css"/>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Student Registration</title>
    </head>
    <body>
        <div class="container">
        <h2>Student Registration</h2>
        <form id="add_stu_form" action="add_stu.jsp" method="POST">
            
            <label for="enrol" style="color:black">Enrollment no:</label>
            <input type="text" name="enrol" id="enrol" placeholder="Enrollment Number" required>
            
            <label for="fullname" style="color:black">Name:</label>
            <input type="text" name="fullname" id="fullname" placeholder="Full Name" required>
            
            <!--button type="submit">Add Student</button-->
            <p class="submit">
                <input type="submit" name="action" value="Save"> 
                <input type="submit" name="action" value="Delete"> 
                <input type="submit" name="action" value="Update"> 
            </p>
            <script></script>
        </form>
    </div>
    </body>
</html>

JSP处理代码 #

<%-- 
    Document   : add_stu
    Created on : 08-Apr-2024, 11:06:07 pm
    Author     : DC
--%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%
    Class.forName("com.mysql.cj.jdbc.Driver"); 
    Connection con =DriverManager.getConnection("jdbc:mysql://localhost:3306/ams","root","");
   
    long enrl = Long.parseLong(request.getParameter("enrol"));
    String name = request.getParameter("fullname");
    Statement s0 = con.createStatement();
    ResultSet rs0 = s0.executeQuery("SELECT `No` FROM `stu_att`");
    int size = 0;
        while (rs0.next()) {
            size++;
        }
    int count = size + 1;
    int result;
    String sql;
    String button = request.getParameter("action");
    if("Save".equals(button)){
        sql = "INSERT INTO `stu_att`(`No`, `name`, `enrl`) VALUES (?,?,?)";
        PreparedStatement ps1 = con.prepareStatement(sql);
        ps1.setInt(1, count);
        ps1.setString(2, name);
        ps1.setLong(3, enrl);
        result = ps1.executeUpdate(sql);
    }
    else if("Delete".equals(button)){
        long del = enrl; //button2;
        sql = "DELETE FROM `stu_att` WHERE `enrl`=?";
        PreparedStatement ps2 = con.prepareStatement(sql);
        ps2.setLong(1, del);
        result = ps2.executeUpdate(sql);
    }
    else if("Update".equals(button)){
        long upd = enrl; //button2;
        sql = "UPDATE `stu_att` SET `name`='?' WHERE `enrl`=?";
        PreparedStatement ps3 = con.prepareStatement(sql);
        ps3.setString(1, name);
        ps3.setLong(1, upd);
        result = ps3.executeUpdate(sql);
    }
    else{
        result=001;
    }
%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Add Student</title>
    </head>
    <body>
        <p><%= result %> Record inserted!</p>
        <a href="">Back to home page</a>
    </body>
</html>

问题排查与修复方案 #

错误点1：PreparedStatement调用带参数的executeUpdate(sql) #

使用PreparedStatement时，调用带SQL字符串参数的executeUpdate(sql)会退化为普通Statement执行，导致占位符?未被替换，直接作为SQL语法的一部分触发错误。
修复：调用无参的executeUpdate()，使用预编译好的SQL语句。

错误点2：Update语句中占位符加单引号 #

UPDATE语句里name='?'的写法错误，占位符?无需加单引号，加单引号后会被当作字符串字面量，而非参数占位符。
修复：将SQL改为UPDATE stu_attSETname=? WHERE enrl=?

错误点3：Update部分参数索引重复 #

设置Update语句参数时，两次使用索引1，导致第一个参数被覆盖，第二个占位符未赋值。
修复：调整索引顺序，name对应索引1，enrl对应索引2。

修复后的JSP核心代码片段 #

if("Save".equals(button)){
    sql = "INSERT INTO `stu_att`(`No`, `name`, `enrl`) VALUES (?,?,?)";
    PreparedStatement ps1 = con.prepareStatement(sql);
    ps1.setInt(1, count);
    ps1.setString(2, name);
    ps1.setLong(3, enrl);
    result = ps1.executeUpdate(); // 移除sql参数
}
else if("Delete".equals(button)){
    long del = enrl;
    sql = "DELETE FROM `stu_att` WHERE `enrl`=?";
    PreparedStatement ps2 = con.prepareStatement(sql);
    ps2.setLong(1, del);
    result = ps2.executeUpdate(); // 移除sql参数
}
else if("Update".equals(button)){
    long upd = enrl;
    sql = "UPDATE `stu_att` SET `name`=? WHERE `enrl`=?"; // 移除单引号
    PreparedStatement ps3 = con.prepareStatement(sql);
    ps3.setString(1, name); // 索引1对应name
    ps3.setLong(2, upd); // 索引2对应enrl
    result = ps3.executeUpdate(); // 移除sql参数
}

内容的提问来源于stack exchange，提问作者Effie