This week's book giveaway is in the OCMJEA forum.
We're giving away four copies of OCM Java EE 6 Enterprise Architect Exam Guide and have Paul Allen & Joseph Bambara on-line!
See this thread for details.
The moose likes JDBC and the fly likes Multiple statements on single connection? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Multiple statements on single connection?" Watch "Multiple statements on single connection?" New topic
Author

Multiple statements on single connection?

Vijay Kashyap
Ranch Hand

Joined: Jul 30, 2001
Posts: 74
Hi,
I am just wondering if multiple statements can be opened and executed simultaneouly on a single connection. If yes, will they be sequential or not? Also will there be any issue involved even if the queries being fired doesn't require any transaction control( they are simple select query).
Regards,
Vijay
Reid M. Pinchback
Ranch Hand

Joined: Jan 25, 2002
Posts: 775
Statements are executed on the server in the order they were executed on the client. You can batch up statements to ship them as a block. That cuts down on network traffic, but they still have a defined order. If you literally want simultaneous independent statement execution then you want multiple connection objects, typically managed via a connection pool.
Note: Oracle does have the notion of a "connection concentrator" that can be set up in certain database configurations. I haven't used it, but from what I understand it is intended to funnel activity from multiple logical connections into a single physical connection. I'd still have to expect you have sequential behaviour at some level.


Reid - SCJP2 (April 2002)
Kevin Mukhar
Ranch Hand

Joined: Nov 28, 2000
Posts: 83
Originally posted by Reid M. Pinchback:
If you literally want simultaneous independent statement execution then you want multiple connection objects, typically managed via a connection pool.

Not true, at least according to the specification:
JDBC 3.0 Specification, Final Release
13.1.1 Creating Statements
Statement objects are created by Connection objects..... Each Connection object can create multiple Statement objects that may be used
concurrently by the program.
Reid M. Pinchback
Ranch Hand

Joined: Jan 25, 2002
Posts: 775
Yup, true, but I think it is important to compare the wording of the spec to the wording of the original question. The spec says you can have multiple objects that can be used concurrently. It doesn't talk about how concurrency is implemented. "Concurrent" is not the same as "simultaneous". The spec doesn't guarantee that execute() invocations are interleaved in some esoteric fashion that would make them all safe if one of them contained a run-away query.
Perhaps in later Oracle releases (9i) they are protected; it would be interesting to try and test. Lord knows there is a lot of whacky thread synchronization going on in Oracle JDBC drivers; I could believe just about anything of them. :-)
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
As Reid said, if you use one Connection then you'll have just one network connection to the database and all Statements will share that one channel. But that distinction is in most cases academic; you can usually treat the statements as executing simultaneously and independently.
Beware though that not all drivers support multiple Statements per Connection, most notably the JDBC-ODBC bridge (or has that limitation finally been removed now?)
- Peter
Reid M. Pinchback
Ranch Hand

Joined: Jan 25, 2002
Posts: 775
Been awhile since I looked, but I believe the limitation depended on the version of ODBC you had installed. 3.0 was something of a transition point for ODBC functionality. Prior to that you definitely could only have a single thread of execution against the database.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Multiple statements on single connection?