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

JDBC Program Design Question

Elton Hughes
Ranch Hand

Joined: Jun 19, 2003
Posts: 72
Hello All,

I am learning JDBC and OOP, so please bear with me. I work for a non-profit agency and I need to develop a program that will connect to a MS SQL server and generate a summary report on our clients. I need to enter in a grant number, followed by and start and end date.

So far so good. I have written a program that will connect to the database and it will return the number of enrolled clients. It works perfectly.

But here is my problem. I also need the number of exited clients. The number of entered employed clients. The average wage at placement. The average number of weeks of participation, and so on. The SQL statement used in executeQuery cannot handle all that is needed.

As I see it, I have three options. First, have multiple statements, resultsets, etc is a single class. But that could lead to a huge program that will be hard to maintain. Second, have a class for each bit of data that needs to be collected. While it would be easier to create, test, and maintain, but it would also mean multiple connection objects and lots of duplicated code, and that cannot be good. Third, create a super class that contains the driver and connection parts and then sub-class the statement and resultsets. That seems the best, but I am too much of a greenhorn to even know if it is possible, let alone figure out how to do it.

So ranchhands, what are your thoughts? What book would you recommend to help me out with this problem? Any words of encouragement?

Thanks!

Elton

p.s. Right now it is a command line program. In time, is there any reason I could not put a Swing interface on this? (First things first though.)

p.p.s. Sheriff, if you kick this over to the OOP corral, I will not be offended. Since it is a JDBC program, it made sense to me to start here.
[ May 14, 2004: Message edited by: Elton Hughes ]
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31075
    
232

Elton,
I think the post makes sense here too.

Option three is definitely the best. If you don't feel comfortable with a superclass, option two is second best. The duplicated code is a problem, but the multiple connections is not. Since only one connection is open at a time that is ok.

You could definitely put a swing interface on the program. I would focus on getting the jdbc working first. Once you know it works, you can make the interface pretty.

If you haven't already, the java trails are a good place to start. There is one for JDBC and another for Inheritance


Good luck!


[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
Stefan Wagner
Ranch Hand

Joined: Jun 02, 2003
Posts: 1923

I cannot agree to Jeanne.

You need ONE connection to the database, one driver-information, username, passwort.

But have multiple queries and ResultSets.

You may split the problem into two loose coupled ones.
You create the connection-object with the driver- and user-info.
You open the connection.

For each query, you may pass the connection, create a statement, get a result-set, and close the statement, without closing the connection.

Multiple connection IS a problem.


http://home.arcor.de/hirnstrom/bewerbung
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31075
    
232

Stefan,
Why is it a problem to have different connection objects if they are not open at the same time? This is the same thing the superclass does, the only difference is that the variable names happen to be different.

It looks like there are two different flows under consideration:

Option 1:
- Create connection
- Create Statement
- Set result set
- Close result set
- Close Statement
- Create Statement
- Set result set
- Close result set
- Close Statement
- Close connection

Option 2:
- Create connection
- Create Statement
- Set result set
- Close result set
- Close Statement
- Close connection
- Create connection
- Create Statement
- Set result set
- Close result set
- Close Statement
- Close connection

If the queries are done in one request, I agree that option 1 is better because there is less overhead. I still think option 2 would work, just be slower. If the queries are done in multiple requests, I would say that option 2 should be used because it closes the connection faster.

I think the two things to avoid are having multiple connections open at the same time and having a connection open longer than necessary.
Stefan Wagner
Ranch Hand

Joined: Jun 02, 2003
Posts: 1923

Opening a connection needs additional time.
Of course the pros and cons depend on your application.

If you develop for ebay, where people do 3-10 queries, but there are 100.000 users, I would consider closing the connection.
But how many people will use this reporting-tool? A few. And meanwhile?
One connection for a workspace isn't to much for a normal database, I guess, and you get fast responses.

'Having a connection open longer than necessary' isn't easy to be known in advance.
If the user looks for 20 minutes on his wonderful report, well - you might close the connection.
But there is still no need to extend a class which manages the connection.
Instead you may have a static getConnection- Method, or pass a Connection with the methodCalls, when it is needed.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31075
    
232

Stefan,
I agree with that. I guess I was trying to say that it isn't wrong, just slower. Thanks for helping me clarify my post.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC Program Design Question