What is the right way to deal with the PreparedStatement in the Java program flow?

Issue

There are two methods in which the PreparedStatement is used.

The first method is called in the second method.

First method:

protected List<String> findResultsByMandantId(Long mandantId) {
    List<String> resultIds = new ArrayList<>();
    ResultSet rs;
    String sql = "SELECT result_id FROM results WHERE mandant_id = ?";
    PreparedStatement statement = getPreparedStatement(sql, false);
    try {
        statement.setLong(1, mandantId);
        statement.execute();
        rs = statement.getResultSet();

        while (rs.next()) {
            resultIds.add(rs.getString(1));
        }
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
    return resultIds;
}

Second method:

protected void findResultLineEntityToDelete(Long mandantId, String title, String context) {
    List<String> resultIds = findResultsByMandantId(mandantId);
    String [] resultIdsArr = resultIds.toArray(String[]::new);
    
    ResultSet rs;

    //String sql = "SELECT * FROM resultline WHERE result_id in (SELECT result_id FROM results WHERE mandant_id =" + mandantId + ")";
    String sql = "SELECT * FROM resultline WHERE result_id in (" + String.join(", ", resultIdsArr)+ ")";
    PreparedStatement statement = getPreparedStatement(sql, false);
    try {
        statement.execute();
        rs = statement.getResultSet();

        while (rs.next()) {
            if (rs.getString(3).equals(title) && rs.getString(4).equals(context)) {
                System.out.println("Titel: " + rs.getString(3) + " " + "Context: " + rs.getString(4));
            }
        }
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

The class in which both methods are located extends the JDBCBaseManager.

JDBCBaseManager:

private final String url = "jdbc:mysql://localhost:3306/database";
private final String userName = "root";
private final String password = "";
private Connection connection = null;
private PreparedStatement preparedStatement = null;
private int batchSize = 0;


public JDBCBaseManager() {
//      Dotenv env = Dotenv.configure().directory("./serverless").load();
//      url = env.get("DB_PROD_URL");
//      userName = env.get("DB_USER");
//      password = env.get("DB_PW");
}

public void getConnection() {
    try {
        if (connection == null) {
            connection = DriverManager.getConnection(url, userName, password);
        }
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

public PreparedStatement getPreparedStatement(String sql, boolean returnGeneratedKeys) {
    try {
        if (connection == null) {
            getConnection();
        }
        if (preparedStatement == null) {
            if (!returnGeneratedKeys) {
                preparedStatement = connection.prepareStatement(sql);
            } else {
                preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                connection.setAutoCommit(false);
            }
        }
        return preparedStatement;
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

public void closeConnection() {
    try {
        if (connection != null && !connection.isClosed()) {
            System.out.println("Closing Database Connection");
            connection.close();
        }
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

public void startBatch(int batchSize) throws SQLException {
    connection.setAutoCommit(false);
    setBatchSize(batchSize);
}

public void commit() {
    try {
        if (connection != null && !connection.isClosed()) {
            connection.commit();
        }
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

public int getBatchSize() {
    return batchSize;
}

public void setBatchSize(int batchSize) {
    this.batchSize = batchSize;
}

The ResultSet in the second method still contains the results from the first method.

I already tried to close the connection and open it again before the second method is executed, but then I get the errors:

java.sql.SQLException: No operations allowed after statement closed.

java.sql.SQLNonTransientConnectionException: No operations allowed after connection closed.

Can you tell me how to deal with the statement correctly in this case? Is my BaseManager incorrectly structured?

Solution

Here lies the error

public JDBCBaseManager() {

  private PreparedStatement preparedStatement = null;

    public PreparedStatement getPreparedStatement(String sql, boolean returnGeneratedKeys) {
      try {
      ......
       if (preparedStatement == null) {
            if (!returnGeneratedKeys) {
                preparedStatement = connection.prepareStatement(sql);
            } else {
                preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                connection.setAutoCommit(false);
            }
        }
        return preparedStatement;

You build the prepare statement only the first time the method getPreparedStatement is called because only the first time the field preparedStatement is null. Every next time you call the method getPreparedStatement you receive the previous preparedStatement from the previous SQL and not the new one.

Remove the check for if (preparedStatement == null) {

You need to build a new preparedStatement every time you want to execute a new SQL.

Answered By - Panagiotis Bougioukos