What is a Connection Object in JDBC ? How is this Connection maintained(I mean is it a Network connection) ? Are they TCP/IP Connections ? Why is it a costly operation to create a Connection every time ? Why do these connections become stale after sometime and I need to refresh the Pool ? Why can't I use one connection to execute multiple queries ?
+1
A:
These connections are TCP/IP connections. To not have to overhead of creating every time a new connection there are connection pools that expand and shrink dynamically. You can use one connection for multiple queries. I think you mean that you release it to the pool. If you do that you might get back the same connection from the pool. In this case it just doesn't matter if you do one or multiple queries
The cost of a connection is to connect which takes some time. ANd the database prepares some stuff like sessions, etc for every connection. That would have to be done every time. Connections become stale through multiple reasons. The most prominent is a firewall in between. Connection problems could lead to connection resetting or there could be simple timeouts
Norbert Hartl
2009-05-08 06:52:18
Thanks Norbert. My question was can I run two or more queries on One Connection object at the same time ?
Geek
2009-05-08 07:08:30
I don't know for sure but I would just try. If you send a query you get back handle for your result set. So theoretically there isn't much of a reason what that shouldn't work. Maybe it differs between databases but with the bigger ones I wouldn't expect this to be a problem
Norbert Hartl
2009-05-08 07:14:55
Norbert I think we can not execute two queries on the same Conenction. There might be Synchronization issues. I too am not sure but the logic would be, if you could do it then you would never need a Pool. One connection would be sufficient.
Geek
2009-05-08 07:46:18
You can surely do it, the only thing to remember is you will share the same properties of connection and same session between queries. Possibly same transaction would be used, unless otherwise commit or rollback in any possible way.
Adeel Ansari
2009-05-08 08:06:19
Further, let me make this clear too. When you send the connection back to pool, the API would reset the state of that connection as its newly instantiated. For example you may have changed the setAutoCommit() but when you send it back to pool, the underlying pool library would revert back the default value.
Adeel Ansari
2009-05-08 08:08:08
@Geek You would need the pool anyway because the creation of a simple connection is actually quite heavy. So you keep and reuse TCP/IP connections and the already set up connection inside the database. That is the main issue about having a pool. By sharing a pool the average needed connection is at the possible lowest value. So you might need 3 connections at max per web server. That lowers the overall amount of connections at the database which is a big speedup
Norbert Hartl
2009-05-08 08:39:00
@Geek I forgot to add that having this scenario you speed up everything if you hold the connection only if you need it. Than the average will be lower. Consider not fetching a connection at the beginning of a method but first prepare everything than fetch a connection, send a query and give back the connection at earliest possible time
Norbert Hartl
2009-05-08 08:41:07
A:
The answers to your questions is that they are implementation defined. A JDBC connection is an interface that exposes methods. What happens behind the scenes can be anything that delivers the interface. For example, consider the Oracle internal JDBC driver, used for supporting java stored procedures. Simultaneous queries are not only possible on that, they are more or less inevitable, since each request for a new connection returns the one and only connection object. I don't know for sure whether it uses TCP/IP internally but I doubt it.
So you should not assume implementation details, without being clear about precisely which JDBC implementation you are using.
Alohci
2009-05-08 08:08:25
For Oracle JDBC drvier use the TCP/IP protocol that emulates Oracle's Net8. I hope it makes the thing clear.
Adeel Ansari
2009-05-08 08:12:05
+1
A:
To add to the other answers:
Yes, you can reuse the same connection for multiple queries. This is even advisable, as creating a new connection is quite expensive.
You can even execute multiple queries concurrently. You just have to use a new java.sql.Statement/PreparedStatement instance for every query. Statements are what JDBC uses to keep track of ongoing queries, so each parallel query needs its own Statement. You can and should reuse Statements for consecutive queries, though.
sleske
2009-05-08 08:20:45
What do you mean by your last statement. We can only reuse the variable not the statement object, as we usually do con.createStatement() or con.prepareStatement(). And I believe it gives you a new instance everytime.
Adeel Ansari
2009-05-08 08:29:24
Yes,con.createStatement() produces a new instance everytime. But you can use it repeatedly to execute statements, as in:stmt.execute("my sql 1"); stmt.getResultSet; // do stuff with ResultSet; stmt.execute("my sql 2")... But you must not use a ResultSet after you have reused its Statement, b/c then the ResultSet will have been reset.
sleske
2009-05-09 02:08:39
A:
since I cannot comment yet, wil post answer just to comment on Vinegar's answer, situation with setAutoCommit() returning to default state upon returning connection to pool is not mandatory behaviour and should not be taken for granted, also as closing of statements and resultsets; you can read that it should be closed, but if you do not close them, they will be automatically closed with closing of connection. Don't take it for granted, since it will take up on your resources on some versions of jdbc drivers.
We had serious problem on DB2 database on AS400, guys needing transactional isolation were calling connection.setAutoCommit(false) and after finishing job they returned such connection to pool (JNDI) without connection.setAutoCommit(old_state), so when another thread got this connection from pool, inserts and updates have not commited, and nobody could figure out why for a long time...
ante.sabo
2009-05-08 09:18:19