File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes How to execute multiple select statements in one group? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to execute multiple select statements in one group?" Watch "How to execute multiple select statements in one group?" New topic
Author

How to execute multiple select statements in one group?

Volodymyr Levytskyi
Ranch Hand

Joined: Mar 29, 2012
Posts: 505
    
    1

Hello!

Is there a way in jdbc to execute two or more select statements but at the same time.
I do not see a way how to join my select statements in sql query.
I executed two queries consecutively with different PreparedStatements and saw that it was too slow.
And I think maybe jdbc gives me a chance to execute different sql select statements in group.
What I want is one method call of Statement to produce one or more ResultSets that represent the output of my 2(or 3) sql select statements.

Thank you!


True person is moral, false is right!
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30068
    
149

You could use a stored procedure which can return multiple result sets. But why do you think that would be significantly faster? Is the second network trip really that big a time sink? It seems more likely your queries are returning too much data or running too slowly individually.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Volodymyr Levytskyi
Ranch Hand

Joined: Mar 29, 2012
Posts: 505
    
    1

Thank you for reply!

To use stored procedure I'll need to create it at first on user's db and then execute it which will be slower than merely running 3 select statements.

My first query uses mysql UNION to do 2 selects of almost the same columns but under different conditions and tables. My second query selects another columns columns from another tables. I do not see a way to combine all 3 selects in one mysql query.
I use mysql 5 and I query special information_schema table to get data about user's database.
The first query combines two selects by union

If you are on mysql 5 where database information_schema is you may copy paste my queries(replacing ?) to see how ugly they are.
The second query is to retrieve data about triggers:

These two queries I execute one after the other in one method when user expands jtree node. They execute slowly and gui freezes for a second or two.
So, I want them to be executed faster thus I ask how to run them in one group with one method call OR in parallel with jdbc.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

It seems that your application is an SQL client, right? I think that - regardless of how much can you improve the queries - you need to perform the actions in the background. In a Swing application this can be done using SwingWorker - have a look at its javadoc or at the tutorial.

I believe that JDBC supports obtaining several resultsets per one call, since there is the Statement.getMoreResults() method. However, I don't have any experience with it - you might try to explore from there.

I'm far from sure that network latency is your problem. It should be possible to get an idea about the network latency, though - measure how long it takes to create a really simple resultset (in Oracle, I'd measure select null from dual). If it takes, say, 100 ms, you cannot hope to shave off a second by executing one query instead of three.

Why do you use like instead of equality operator in the triggers query? This might be the reason why the query performs badly, as with like the database cannot use any index.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

One more (probably small) thing: can't you use union all instead of union in the first query?

Also, I've edited your post to reformat the SQL statements. Please try to keep the lines in the code tags reasonably short (under 80 characters is best), otherwise your posts might be hard to read.
Volodymyr Levytskyi
Ranch Hand

Joined: Mar 29, 2012
Posts: 505
    
    1

Thanks @Martin for reply!

I read article written by @Jeanne Boyarsky here.
It says that we can execute multiple selects at once using ; to separate sql queries. The example is :

But whatever I do on my own I always receive error that this is not supported. My code is very similar

The error I receive is :

I use mysql-connector-java-5.1.25-bin.jar.
From command line this query works perfectly.
Please, tell me: Can I execute multiple selects separated by semicolon or not.

Thank you!
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

It is certainly possible that your particular JDBC driver doesn't support this feature. You might try to have a look at the ConnectorJ (if there is any) documentation to find out more.

However, I wouldn't try to use this feature before measuring whether this is going to help you at all. I've hinted how to measure the network lag (because it is the only possible saving) in my previous post. It's a lot of work for very, very uncertain benefit.
Volodymyr Levytskyi
Ranch Hand

Joined: Mar 29, 2012
Posts: 505
    
    1

I used mysql profiler as described here.

It is good to use.
I ran the same query(with union) many times .
The thing I've noticed is that the first run took 0.242 and subsequent runs of the same query took 0.06 sec.
The first run is the most expensive and that's why my gui freezes!

Now I try to apply SwingWorker.

I cannot find info if latest jdbc driver supports multiple selects separated by semicolon
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Volodymyr Levytskyi wrote:I used mysql profiler as described here.

Are you sure it captures network lag? (It would have to run on the client as well as in the database to be able to capture that).


The thing I've noticed is that the first run took 0.242 and subsequent runs of the same query took 0.06 sec.
The first run is the most expensive and that's why my gui freezes!

The first run is almost always slower than subsequent ones, because the query is new to the database and has to be parsed, and all the data have to be read into memory. On subsequent runs, especially if the are no other applications that would force your data out of the cache, everything is cached and therefore considerably faster.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30068
    
149

Volodymyr Levytskyi wrote:I read article written by @Jeanne Boyarsky here.
It says that we can execute multiple selects at once using ; to separate sql queries.

It also says "Also, note that not all database support sending multiple statements separated with semicolons."
Volodymyr Levytskyi
Ranch Hand

Joined: Mar 29, 2012
Posts: 505
    
    1

Thanks for reply!

My database(mysql) supports sending multiple select statements separated by semicolon.
I tried that query from command line and it resulted in two separate tables as expected.

But it might be that latest jdbc driver for mysql(mysql-connector-java-5.1.25-bin.jar) does not support this.
But how then your driver in 2005 supported this and now in 2013 it is not supported. But you could use jdbc driver for another database.

Still it is hard to believe that it is not supported.
Where to find out if mysql jdbc driver supports multiple selects separated by semicolon or not?
I could not find this in documentation of mysql jdbc driver.

BTW, SwingWorker suggested by @Martin works perfectly! And I think that two methods to fetch data from db work a little quicker thus my node is expanded quicker. This is great! And also I added JDialog with progress bar. Now whenever expansion of node takes more 200 ms this dialog appeares and blocks gui and moves progress bar correctly. How to do this I found out in this tutorial
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Volodymyr Levytskyi wrote:My database(mysql) supports sending multiple select statements separated by semicolon.
I tried that query from command line and it resulted in two separate tables as expected.

It is still possible that it was the SQL client who handled that, though. For example, in Oracle the SQL client (called SQL*plus) parses the input file, splits it into individual statements and sends them to the database. The database then processes one command at a time, not two or more. You cannot tell just by looking at the output.

Still it is hard to believe that it is not supported.

According to the documentation, the MySQL database doesn't even support prepared statements (ConnectorJ emulates them). Select batching is far less important feature than prepared statements, in my opinion, so I do believe that it simply wasn't implemented.

Where to find out if mysql jdbc driver supports multiple selects separated by semicolon or not?
I could not find this in documentation of mysql jdbc driver.

No idea. But I still don't think it is worth the hassle. It's not like you'll be saving most of the network roundtrips that way.

Glad you got the SwingWorker to work!
 
 
subject: How to execute multiple select statements in one group?
 
Similar Threads
best programming practises of JDBC
Running a procedure in JDBC
execute() and prepared statements
Prepared Statement
benefit of Statement over PreparedStatement?