How to change the cycle of user variables MySQL JDBC?

Hi all.

There is such code
import org.apache.commons.dbcp2.BasicDataSource;


import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Application {
 public static void main(String[] args) throws SQLException, InterruptedException {
 List<string> scripts = new ArrayList<>();
 scripts.add("call p(@_nodeId);");
 scripts.add("select @_nodeId;");

 BasicDataSource bds = DataSource.getInstance().getBds();
 Long res = null;
 int i = 1;
 while (i < 4) {
 try (Connection conn = bds.getConnection()) {
 try (Statement stmt = conn.createStatement()) {
 for (String script : scripts) {
 if (!script.trim().isEmpty() && !script.equals(scripts.get ("scripts".size() - 1))) {
stmt.addBatch(script);
}
}
stmt.executeBatch();
 ResultSet resultSet = stmt.executeQuery(scripts.get ("scripts".size() - 1));
 if (resultSet.next()) {
 res = (Long) resultSet.getObject(1);
}
}

}
System.out.println(res);
i++;
Thread.sleep(1000);
}
}
}</string>


And there is such hranica:
CREATE DEFINER=`root`@`%` PROCEDURE `p`(INOUT `incr_param` BIGINT)
BEGIN
 INSERT INTO animals (name) VALUES ('dog');
IF
 incr_param is NULL THEN
 SET incr_param = LAST_INSERT_ID();
 ELSE 
 SET incr_param = 100500;
 END IF;
END


The problem is the following. When you first run cycle, the result is, as expected, the iD of the last record, because the variable @_nodeId == NULL, but on subsequent iterations it has returned 100500. Don't understand the following. Why the variable @_nodeId does not die between iterations of the loop, because the Connection and Statement are closed? It says herethat the custom variables live during a user session, why the session is not destroyed when you close a connection in JDBC?
June 10th 19 at 14:45
1 answer
June 10th 19 at 14:47
You use connection pooling, so the connection is not actually closed but returned to the pool.
Yes, indeed, I am using dbcp2 pooling. In the settings I vistawall it is mandatory to use a single connection pool, but it did not solve the problem. How to do to when the connection is closed "closed" and the pool? Or maybe there's another way? Thank you. - kadin_Hyatt commented on June 10th 19 at 14:50
, just don't use a pool if it is not needed. - samir39 commented on June 10th 19 at 14:53
Yes, that's the problem, pool is needed, specifically in this situation are faced with this problem. - kadin_Hyatt commented on June 10th 19 at 14:56
if you find a way to close the connection in the pool, he will completely lose meaning and become only a point of occurrence overheads. There is another option to use variables. - samir39 commented on June 10th 19 at 14:59

Find more questions by tags JavaMySQLJDBC