如何解决Java中Parameter index out of range(1>0)的SQL异常？

核心错误原因 #

Parameter index out of range错误的直接诱因是INSERT语句语法错误：你用单引号包裹了表名leave，导致MySQL无法正确解析SQL结构，PreparedStatement识别不到你写的6个?占位符，误以为参数数量为0，而你又尝试设置第1个参数，最终触发索引越界。

分步解决 #

1. 修复INSERT语句语法 #

MySQL中，表名/列名如果是关键字（比如leave属于流程控制关键字），需要用**反引号（`）**包裹，单引号仅用于包裹字符串值。修正后的INSERT语句：

PreparedStatement pst = con.prepareStatement("INSERT INTO `leave` (EID,EName,leaveType,StartDate,EndDate,Reason) VALUES (?,?,?,?,?,?)");

2. 正确获取EID和EName的值 #

你现在把ID和name赋值成了查询字符串，这会导致数据库插入的是select EID from...这段文本，而非实际的员工ID和姓名。必须执行查询语句拿到真实数据：

// 获取EID
String getEidSql = "SELECT EID FROM empinfo WHERE EUsername = ?";
PreparedStatement eidPst = con.prepareStatement(getEidSql);
eidPst.setString(1, username);
ResultSet rs = eidPst.executeQuery();
String ID = null;
if(rs.next()){
    ID = rs.getString("EID");
}

// 获取EName
String getNameSql = "SELECT EName FROM empinfo WHERE EUsername = ?";
PreparedStatement namePst = con.prepareStatement(getNameSql);
namePst.setString(1, username);
ResultSet nameRs = namePst.executeQuery();
String name = null;
if(nameRs.next()){
    name = nameRs.getString("EName");
}

用PreparedStatement查询能避免直接拼接字符串带来的SQL注入风险，安全性更高。

3. 其他必改细节 #

• 资源自动关闭：用完Connection、PreparedStatement、ResultSet后要释放，推荐用try-with-resources语法自动处理，避免内存泄漏：

// 示例写法，无需手动调用close
try(Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql","root","12345");
    PreparedStatement eidPst = con.prepareStatement(getEidSql)){
    // 执行查询操作
} catch(SQLException e){
    e.printStackTrace();
}

• 异常捕获：原代码未处理异常，添加try-catch块能快速定位问题，还能给用户友好提示。
• 空值检查：要判断username、leaveType等参数是否为空，避免空指针或插入无效数据。

修正后的核心代码片段 #

<%           
    String username = (String)session.getAttribute("username");   
    String leaveType = request.getParameter("leaveType");
    String startDate = request.getParameter("StartDate");
    String endDate = request.getParameter("EndDate");
    String reason = request.getParameter("reason");             

    Class.forName("com.mysql.jdbc.Driver");
    try(Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql","root","12345")){

        // 获取EID
        String getEidSql = "SELECT EID FROM empinfo WHERE EUsername = ?";
        String ID = null;
        try(PreparedStatement eidPst = con.prepareStatement(getEidSql)){
            eidPst.setString(1, username);
            try(ResultSet rs = eidPst.executeQuery()){
                if(rs.next()){
                    ID = rs.getString("EID");
                }
            }
        }

        // 获取EName
        String getNameSql = "SELECT EName FROM empinfo WHERE EUsername = ?";
        String name = null;
        try(PreparedStatement namePst = con.prepareStatement(getNameSql)){
            namePst.setString(1, username);
            try(ResultSet nameRs = namePst.executeQuery()){
                if(nameRs.next()){
                    name = nameRs.getString("EName");
                }
            }
        }

        // 插入请假记录
        String insertSql = "INSERT INTO `leave` (EID,EName,leaveType,StartDate,EndDate,Reason) VALUES (?,?,?,?,?,?)";
        try(PreparedStatement pst = con.prepareStatement(insertSql)){
            pst.setString(1, ID);
            pst.setString(2, name);
            pst.setString(3, leaveType);
            pst.setString(4, startDate);
            pst.setString(5, endDate);
            pst.setString(6, reason);

            int row = pst.executeUpdate();           
            if(row == 1){
%>
<script>        
    alert("Leave Applied");            
</script>
<jsp:include page="profile.jsp"></jsp:include> 
<% 
            } else {
                out.println("Leave Application Failed");
            }       
        }
    } catch(Exception e){
        e.printStackTrace();
        out.println("Error: " + e.getMessage());
    }
%>

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