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 Regular query vs JDBC Programming? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Regular query vs JDBC Programming?" Watch "Regular query vs JDBC Programming?" New topic
Author

Regular query vs JDBC Programming?

Mike London
Ranch Hand

Joined: Jul 12, 2002
Posts: 1034
I am in the middle of trying to get a HUGE SQL query to work. Uggg!

It's quite complicated in that to make it work, it will take lots of sub-queries and other things (like UNIONS).

The problem is, well, it's too complicated! And, of course, it needs to be done ASAP.

I'm therefore wondering if it wouldn't be easier to just "sort of" start over and write some JDBC code to create the final result.

I would need to be able to generate multiple queries into multiple ResultSets (sure, no problems there), but then I would want to, at runtime, create new columns in the "Master ResultSet" and programmatically add the new data to it from the other ResultSets (checking for key equality between any ResultSet and the Master ResultSet before adding any new columns, of course).

I'd still like to end up with a single ResultSet from which I can output a file.

Being able to create multiple ResultSets and combine them programmatically would save the day I think! If I can't add new fields to a ResultSet at Runtime, then I could build up some other type of data structure I suppose.

In any case, I was mainly wondering when most developers give up on a 50+ line query and say ... "time for JDBC" (or maybe a stored proc)?

Look forward to any comments.

Thanks.

Mike
Jelle Klap
Bartender

Joined: Mar 10, 2008
Posts: 1666
    
    7

I fail to see how JDBC can be viewed as an alternative to SQL? In any case, it's usually a better option to let the database handle unions, joins, orderings et al. If you can get the desired result from a single query, make the effort to do so, because that's what the relational database is there for and that's what it's best at.


Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life.
Mike London
Ranch Hand

Joined: Jul 12, 2002
Posts: 1034
Jelle Klap wrote:I fail to see how JDBC can be viewed as an alternative to SQL? In any case, it's usually a better option to let the database handle unions, joins, orderings et al. If you can get the desired result from a single query, make the effort to do so, because that's what the relational database is there for and that's what it's best at.


Well, I'd agree, generally speaking.

However, I'd counter with...if the query is too complex (too many aggregates, joins, etc.), then JDBC might be a good alternative. Using JDBC, you can create several ResultSets and programmatically search and get the results you want.

Plus, if the final output is a text file, then you're all set with JDBC programming too.

Mike
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


If you can get the desired result from a single query, make the effort to do so, because that's what the relational database is there for and that's what it's best at

I think I disagree with this. There are a whole bunch of things a database can do in a single query and its often debatable whether you should do. For example, you can format output as part of a query pushing more load onto the database when the best place to handle display logic is in the client that displays the data. Ordering too is debatable, database ordering is often defined by collation rules which are locale specific and may differ from the rules for the client.

Any query with aggregations or other functions in select or where clauses is worth considering too. Anything that tends towards business logic is better in the application not the database.

Then there is the possibility of a large, complex query bringing the database to its knees. OK, you could argue that this is a poorly designed query (or a poorly configured database) but if you push complex logic into the client you tend to break an application for one user only, rather than everyone. Not always possible I know, but worth considering if you can do it.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Mike London
Ranch Hand

Joined: Jul 12, 2002
Posts: 1034
Paul Sturrock wrote:

If you can get the desired result from a single query, make the effort to do so, because that's what the relational database is there for and that's what it's best at

I think I disagree with this. There are a whole bunch of things a database can do in a single query and its often debatable whether you should do. For example, you can format output as part of a query pushing more load onto the database when the best place to handle display logic is in the client that displays the data. Ordering too is debatable, database ordering is often defined by collation rules which are locale specific and may differ from the rules for the client.

Any query with aggregations or other functions in select or where clauses is worth considering too. Anyhting that tends towards business logic is better in the application not the database.

Then there is the possibility of a large, complex query bringing the database to its knees. OK, you could argue that this is apoorly designed query (or a poorly configured database) but if you push complex logic into the client you tend to brteak an application for one uiser only, rather than everyone. Not always possible I know, but worth considering if you can do it.


Paul,

So are you also saying/implying that a JDBC program might be better for a really complex set of queries with file output requirements?

Mike
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

I think what I'm saying is "it depends" and you need to consider what is best for your situation, rather than stick to one rule.
Mike London
Ranch Hand

Joined: Jul 12, 2002
Posts: 1034
Paul Sturrock wrote:I think what I'm saying is "it depends" and you need to consider what is best for your situation, rather than stick to one rule.


Check, thanks.

This is a one-off situation. The query won't be repeated, but I need to actually be able to get it done.

Trying to get the query working is driving me nuts.

If I create multiple ResultSets with common Keys, I can extract the data I need programmatically into class variables, then at the end of each ResultSet iteration, I can output them to the file. "Sounds easy...".

Perhaps this isn't ideal, but I think it's the best choice in my current situation.

Thanks.

Mike
Jelle Klap
Bartender

Joined: Mar 10, 2008
Posts: 1666
    
    7

Paul Sturrock wrote:I think what I'm saying is "it depends" and you need to consider what is best for your situation, rather than stick to one rule.


That should always be the case, and I feel my previous comment might have been overly generalized, or interpreted as such.
I think it's more often than not unnecessary and inefficient to query multiple ResultSets and combine them in your application code when the exact same result could be obtained with a single SQL query that groups / correlates the data by joining a few tables, using a WITH clause or defining sub-queries etc. Even if that means you'll end up with a more complex SQL query as a result. I certainly didn't mean to imply that moving business logic to the database is a good idea, or indeed necessarily a bad idea. Like you said, it depends.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Regular query vs JDBC Programming?
 
Similar Threads
select with an "in" clause...
JDBC API Tutorial - questions about the book
Dymanic queries
comparing two ResultSet
java.sql.SQLException: Closed Connection