• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Regular query vs JDBC Programming?

 
Bartender
Posts: 1971
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Bartender
Posts: 1952
7
Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Mike London
Bartender
Posts: 1971
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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.
 
Mike London
Bartender
Posts: 1971
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Bartender
Posts: 1971
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 1952
7
Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic