java - com.microsoft.sqlserver.jdbc.SQLServerException : The value is not set for the parameter number 1

Question

pstmt.setLong(1, id); 行有问题。我收到一个错误，指出参数编号 1 没有设置值。如果我使用不带问号的字符串 SQL，它就可以工作。另外，当我使用 ARM 时，PreparedStatement 和 ResultSet 不会自动关闭，所以我必须关闭它们，最后似乎也不起作用

@Override  
public Company getCompany(long id) {  
    Connection con = ConnectionPool.getInstance().getConnection();  
    String sql = "SELECT * FROM Company WHERE ID=?";  
    //String sql = "SELECT * FROM Company WHERE ID=" + id;  
    Company company = new Company();  
    try (  
        PreparedStatement pstmt = con.prepareStatement(sql);  
        ResultSet rs = pstmt.executeQuery();)  
        {  
        pstmt.setLong(1, id);  
        if (rs.next()) { 
            company.setId(rs.getLong(1));  
            company.setCompName(rs.getString(2)); 
            company.setPassword(rs.getString(3));  
            company.setEmail(rs.getString(4)); 
        } else {  
            System.out.println("Company with ID: " + id + " could not be found\n"); 
        }  
        pstmt.close();  
        rs.close();  
    } catch (SQLException e) {  
        CouponSystemException ex = new CouponSystemException("Company with ID: " + id + " could not be retrieved\n", e);  
        System.out.println(ex.getMessage());  
        System.out.println(e);  
    }  
    ConnectionPool.getInstance().returnConnection(con);  
    return company;  
}

Answer 1

在执行查询之前设置参数。此外，您不需要关闭 try-with-resource 语句中定义的语句和结果集，因为它们会在您离开 try 作用域时自动关闭。

try(PreparedStatement pstmt = con.prepareStatement(sql)) {
    pstmt.setLong(1, id);
    try(ResultSet rs = pstmt.executeQuery()) {
        // do stuff
    }
}