• 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

Method design advice

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

Thanks.
 
Java Cowboy
Posts: 16084
88
Android Scala IntelliJ IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Deroga Rivera
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Jesper de Jong
Java Cowboy
Posts: 16084
88
Android Scala IntelliJ IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
That sounds like a very reasonable approach.

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 ]
 
Ranch Hand
Posts: 257
Hibernate Firefox Browser Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello,

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.

You can try following ( as EJB).

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.
 
Deroga Rivera
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.

Thanks again.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic