How to fix: "Parameter index out of range (1 > number of parameters, which is 0)" error in java?

Question

I'm trying to learn JDBC Transactions I wrote some basic code to test it. However, while I'm running the code there are shown some errors in the output. Anyone could help me why is this happening and how to get rid of this?

This is the code:

public class JDBCTransactionExample {

    private static String db_url = "jdbc:mysql://localhost:3306/test";
    private static String db_user = "root";
    private static String db_password = "root";

    private static final String INSERT_INTO_USER = "INSERT INTO user VALUES(?,?,?,?,?);";
    private static final String UPDATE_USER = "UPDATE user SET name = ? WHERE id = ?;";

    public static void main(String[] args) {

        Connection conn = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(db_url, db_user, db_password);
            conn.setAutoCommit(false);

            try {
                PreparedStatement insertStmt = conn.prepareStatement("INSERT_INTO_USER");
                PreparedStatement updateStmt = conn.prepareStatement("UPDATE_USER");

                // Create insert statement
                insertStmt.setInt(1, 4);
                insertStmt.setString(2, "BBB");
                insertStmt.setString(3, "bbb@gmail.com");
                insertStmt.setString(4, "en");
                insertStmt.setString(5, "7894");
                insertStmt.executeUpdate();

                // Create update statement
                updateStmt.setString(1, "AAA");
                updateStmt.setInt(2, 4);
                updateStmt.executeUpdate();

                // STEP 2 - Commit insert and update statement
                conn.commit();
                System.out.println("Transaction is commited successfully.");

            } catch (SQLException e) {
                e.printStackTrace();
                if (conn != null) {
                    try {
                        // STEP 3 - Roll back transaction
                        System.out.println("Transaction is being rolled back.");
                        conn.rollback();

                    } catch (Exception ex) {
                        ex.printStackTrace();
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Here is the output:

java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).Transaction is being rolled back.
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
at com.mysql.cj.jdbc.ClientPreparedStatement.checkBounds(ClientPreparedStatement.java:1372)
at com.mysql.cj.jdbc.ClientPreparedStatement.getCoreParameterIndex(ClientPreparedStatement.java:1385)
at com.mysql.cj.jdbc.ClientPreparedStatement.setInt(ClientPreparedStatement.java:1573)
at jdbc.transaction.JDBCTransactionExample.main(JDBCTransactionExample.java:29)

Answer 1

I think you meant to use the constant instead of a String here:

PreparedStatement insertStmt = conn.prepareStatement("INSERT_INTO_USER");
PreparedStatement updateStmt = conn.prepareStatement("UPDATE_USER");

should be

PreparedStatement insertStmt = conn.prepareStatement(INSERT_INTO_USER);
PreparedStatement updateStmt = conn.prepareStatement(UPDATE_USER);