I need to execute some stored procedures on all my JSF pages created using Java Studio Creator. The below is the code I use on most of the pages.
javax.naming.InitialContext ctx = new javax.naming.InitialContext();
javax.sql.DataSource ds = (javax.sql.DataSource)ctx.lookup("java:comp/env/jdbc/MySource");
java.sql.Connection con = ds.getConnection();
java.sql.CallableStatement stmt = con.prepareStatement("call test(?)");
java.sql.ResultSet rs = stmt.executeQuery();
There is no exception and I get the correct results on the page. However when I check mysql and run the "show processlist" statement, i see a new process getting kicked off on each page that executes the above code. The status of all these process is "sleep".
How can I prevent a new pool entry being added as I understand the con.close() command release the connection to the pool. But I dont need this in the pool / how can I get the other pages to resuse the same pool?
Thanks in advance