This is a very worthy question to ask since almost no similar questions found on internet.
Ok, many years ago before Gwt was invented, I used HttpServlet to code Java web app & I often put the Connection object inside a Servlet.
public class JDBCServlet extends HttpServlet {
private Connection connection;
public void init(ServletConfig c) throws ServletException {
//Open the connection here
}
public void destroy() {
//Close the connection here
}
public void doGet (HttpServletRequest req, HttpServletResponse res) throws ServletException {
//Use the connection here
Statement stmt = connection.createStatement();
..<do JDBC work>..
}
}
But, now, with the invention of Gwt & RPC call. I use eclipse to develop my Java webapp with Gwt frontend. Ok, in the Server package in eclipse, i have a Class named Data.java
private static ConnectionPool connectionPool;
public static void initConnectionPool() throws UnavailableException{
try {
String username="root";
String password="";
String url ="jdbc:mysql:.....";
String driver="com.mysql.jdbc.Driver";
if(connectionPool==null)
connectionPool = new ConnectionPool(url, username, password, driver, 5, 2); //initialise 5 Connections & put them into the pull, if all connections were used it will add 2 more connections to the pool.
}
catch (Exception e) {
throw new UnavailableException("Couldn't create connection pool");
}
}
public static void testDB(){
Connection myCon = null;
PreparedStatement myPreparedStmt=null;
try{
initConnectionPool();
myCon=connectionPool.getConnection();
myPreparedStmt=myCon.prepareStatement("select * from table1");
ResultSet results=myPreparedStmt.executeQuery();
//do something here
}
Ok, let look at this scenarios, there are 5 different people living in 5 different countries all access a webpage at same time (but the 1st will open the page slightly earlier than the other 4) that calls to testDB() method.
I tested & found that the 1st person who first open the page will be make the system to create 5 connections. Then, if the 2nd, the 3rd, the 4th & the 5th visit the same page at the same time, then the system won't create more connection s(the system will first go through initConnectionPool() method & check if there are any available unused connections, if there is one then it will reuse it since that connection was created by the 1st user.
If the 2,3,4,5th all open that page at the same time, then they actually use 4 out of 5 unused connections due to the 1st finished downloading all data so the 1st already released its connection to the pool.
Suppose the 2nd refresh that page, then the system will read initConnectionPool() method & actually the 2nd will use 1 out of 5 available unused connections due to everyone already downloaded data in testDB() so they all returned the connections to the pool. If there are a lot of people open the page at the sme time then the system will add 2 or more Connection objects into the pool depending the requirement.
Here is my question:
In the Gwt RPC example, even I tried to put private static Connection connection; or public static Connection connection; it won't make any difference. So the keyword private or public won't make any differences in the scope of the JDBC connection object?.
It means:
- if we put Connection connection; inside a method in Data class then that connection will be opened only when a page call to that that method. Ex, if 5 people open that page then it will create connection 5 time. Is that correct?
- if we put Connection connection; outside a method in Data class then that connection will be opened for anyone. If the 1st created that connection (& do not close it) then other people can just reuse that connection since that connection exist in lifetime. Is that correct?
But, what about the HttpServlet example. There are 2 explanations (which explanation is correct?):
-each time a user load a page the system will call public void init(ServletConfig c) method to create a new connection. What if 5 people call the same page at the same time, the system will create 5 connections?.
-or, the connection was created permanently, there only 1 connection was created. If 5 people make the calls at the same time, & if the 3rd luckily make the Connection first then the other 4 have to wait in a queue till the 3rd finished.
In the Gwt RPC example, the connections were created & never been closed. It's only been closed if we shut down the application?
But we only shutdown our app if we shutdown tomcat.
If noone use connections for long time , then will Mysql make the idle connections automatically close by using some certain mechanism or Mysql still keep these idle connections active forever (Ex, we never shut down tomcat & mysql server for many years then these connections still active for many years? I am very confused at this point.
I hope someone can help me to clarify it.
