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?
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 ]
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
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.
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.