Question 348123 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Java PreparedStatement Error: "Cannot resolve query parameter '2' and '3'"

I am tyring to override a borrowBook interface. What is wrong with my code? Why there are errors such: Cannot resolve query parameter ‘2’ and Cannot resolve query parameter ‘3’.

@Override
    public void borrowBook(int bookId) throws SQLException {

        Date currentDate = new Date(System.currentTimeMillis());

        Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/library11", "postgres", "123");

        try(conn) {
            String sql = "UPDATE books SET isavailable = false WHERE bookid = ? AND isavailable = true";
            PreparedStatement stmn = conn.prepareStatement(sql);

            stmn.setInt(1, bookId);

            int rowsAffected = stmn.executeUpdate();

            if (rowsAffected > 0) {
                sql = "INSERT INTO loans (memberid, bookid, loandate) VALUES (?, ?, ?)";
                stmn = conn.prepareStatement(sql);

                stmn.setInt(1, this.getUserId());
                stmn.setInt(2, bookId); // Parameter 2 (error)
                stmn.setDate(3, currentDate); // Parameter 3 (error)

                stmn.executeUpdate();

                System.out.println("You have successfully borrowed the book!");
            } else {
                System.out.println("This book is not available");
            }
        } catch (SQLException e)  {
            System.out.println("connection error: " + e.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    System.out.println("could not close the connection: " + e.getMessage());
                }
            }
        }
    }
Tags:

2

Answers


  1. 0

    PreparedStatement is a resource that must be opened and closed carefully to avoid any weird problems. The problem seems that you are using the same stmn object without closing the previous PreparedStatement.

    Try something like the following and see if it resolves the problem

    try (Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/library11", "postgres", "123")) {
        // other code here
        try (PreparedStatement stmnUpdate = conn.prepareStatement(sql)) {
                // other code here
                try (PreparedStatement stmnInsert = conn.prepareStatement(sql)) {
                // other code here
                }
            } else {
                System.out.println("This book is not available");
            }
        }

    You can read more about preparedStatement and how you can use it from the official Java tuorials

    Login or Signup to reply.
  2. 0

    Your code compiles and runs just fine.

    Your problem is that the editor window in IntelliJ IDEA is showing a red underline for the two lines you mention. This is because you’ve defined a String variable sql at the start which contains only one parameter placeholder (i.e. only one "?") and then you’ve reset the same variable to a different String, one which now contains three "?". IntelliJ IDEA gets confused by this, and thinks your sql variable is the one you’ve defined first, so it thinks the statement only has 1 parameter, and complains about parameters 2 and 3.

    To work around this issue, define a different variable to hold your second SQL string.

    This is a workaround for an IDE problem, not an actual JDBC error in your code.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search