I have been working with a project and would like a bit of advice on how to handle a particular situation. I don't have any problems getting it to work I just want advice on the best way to set it up.
Here is the situation: I have a database that was not created or maintained by me that I cannot make changes to. This database contains a table (jobs) that among other information has two fields, jobNum and jobName.
My application needs to have a valid job number (determined by its existence in the above table) and if the job number is valid I want to look up the job name.
This is similar to what I have (without the details):
I don't really like this scenario because I end up hitting the database twice, once to verify the job number and a second time to get the corresponding job name.
I also had it where the jobName was set as part of the isValidJobNumber method but I didn't like the dual purpose method so I tried to split them.
Hopefully someone with more experience in these situations could provide some feedback or advice on handle this scenario in a clean & efficient manner.
What does getJobName() return when you pass it an invalid job number?
Maybe you don't need to call isValidJobNumber() to check the number before you call getJobName() - just call only getJobName(), and if it returns a value (or throws an exception) that indicates that the job number was invalid, handle that situation.
Well the getJobName call is only made after I call the isValidJobNumber method so it would always have a valid job number and it does not handle bad job numbers! That is why I wanted to clean this up.
I can see the idea of bypassing the isValid routine all together but I am a little fuzzy on how to make that work. Lets say I create a method similar to:
Assuming that all the database setup is correct that would return a jobName string when the job number is valid or will throw an exception when it is not. I know that jobNumber is the PK so it will either have one record returned or no records at all. So when calling getJobNum I would need to catch the SQLException?
showInvalidJobNumberError() would display a error dialog requesting that a valid job number be entered before continuing. At which point I would return out of the method and wait for the user to enter a better job number and try again (it is ran from a GUI).
Does that sound like a more reasonable approach or would you structure it differently?
By the way, you should use PreparedStatement instead of concatenating the job number into the SQL statement yourself. Aside from the fact that the program will be faster with PreparedStatement if you call the method multiple times (the database could cache the statement), there's a potential security risk associated with concatenating SQL statements like that (SQL injection).
Try something like this:
And note that you must properly close all result set and statement objects after use. [ August 31, 2006: Message edited by: Jesper Young ]
If you are using any application server and reading the jdbc connection from JNDI context of the application server then prepared statment will give performance to you. But if you are not using any application server or not taking the JDBC Connection from the JNDI connection pooling then prepared Statement will work as normal statement only.
Create a class for each table or for join output column equilent table & have one method to read the data or to fire the select statement to get the data into the attributes, after that you can call as many methods as required on the local object data to perform the validations.
We can all this as DAO or Persistance class.
More inputs are welcome.
Joined: Aug 04, 2005
Thanks for all of the great information. As it turns out the job name was stored in the accounting system in such a cryptic way that the boss decided not to use it at all. I did however switch some of the more often used queries over to prepared statements and I was able to use the idea in another area of the application.