This week's book giveaway is in the Mac OS forum.
We're giving away four copies of a choice of "Take Control of Upgrading to Yosemite" or "Take Control of Automating Your Mac" and have Joe Kissell on-line!
See this thread for details.
The moose likes JDBC and the fly likes Null value check Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Null value check" Watch "Null value check" New topic
Author

Null value check

Mahesh Asrani
Greenhorn

Joined: Mar 03, 2005
Posts: 15
Hi All

we have to call a database stored procedure from java which inserts data into a table which has few columns as mandatory

In the tech spec it is said that we have to check whether all these mandatory columns
are being passed with appropriate values at the Java side itself
i.e. if the mandatory values are not being passed the insert database operation
would fail so if these values are not available then do not call the
database stored proc at all

Is this a good idea to introduce checks at the java code itself or would it be better to
rely on the checks that database provides


Do provide your thoughts on the same


Rgrds
Mahesh
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1134

Mahesh,
In my opinion, there is not a right or wrong answer to your question -- there is only a "what is more suitable for your situation" answer.

In order to know "what is more suitable for you", I need to know what your situation is. Unfortunately, you have not provided enough information for me to fully know what your situation is.

There are so many factors involved that affect "what is more suitable for you" that would probably make it impossible for you to describe and explain all of them via this forum alone. In fact, there are undoubtedly factors that you (and obviously I) are unaware of.

In my opinion, the only thing you can do is experiment. Use trial-and-error until you come up with the optimal solution that is most suitable for your situation.

Good Luck,
Avi.
Edwin Keeton
Ranch Hand

Joined: Jul 10, 2002
Posts: 214

While I concur there is no single correct answer (for anything in programming), I would suggest that it is probably best to do the checking in the Java code as well as in the database.

If the check fails in the Java code, don't call the stored proc. This saves a round-trip (at least one) to the database. This may be inconsequential if the database is local, or even on a LAN, but as you scale out it becomes more important.

Second, have the database do the checks as well. Why, if you're already doing it in your Java code? The database should maintain its own integrity. One day another app may want to use your database. That new app may not do the checks in its code and possibly corrupt the database.


SCJP, SCWCD
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30596
    
154

Mahesh,
I agree with Edwin. This appoach also protects your database from users trying to bring down the system.


[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
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1134

Mahesh,
Of-course, if currently you only access these stored procedures from your java code, then the (extra) database check is redundant -- and may be detrimental to performance. In the future, when (and if) you decide to access these stored procedures from another source (other than your java code), then you could always add the checks then. As we all know, this type of code modification is nowadays referred to as "refactoring". In my opinion, it is also a consideration. Why write code today that you may never need?

What Jeanne and Edwin are suggesting, are rules of thumb. They are not axioms. They may be suitable for your situation, or they may not. They may not be suitable today, but will become suitable in the future. Remember that things are always dynamic, so you need to continually be reassessing your code.

Good Luck,
Avi.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30596
    
154

Originally posted by Avi Abrami:
Of-course, if currently you only access these stored procedures from your java code, then the (extra) database check is redundant -- and may be detrimental to performance.

The key word here is may. I don't recommend making a decision based on something that may or may not impact performance. Personally, I don't think there will be any noticable impact from a validation check.

As we all know, this type of code modification is nowadays referred to as "refactoring".[/QB]

Technically, refactoring is "improving the design of existing code." True refactoring doesn't involve adding functionality. The same folks do also suggest YAGNI (you ain't gonna need it) - Avi's next point, so this isn't a deciding factor either way. In any event, this would be an enhancement, not a refactoring.

Why write code today that you may never need?[/QB]

Definitely. However, you may need it. In our apps, we do both front end (GUI) and back end (session bean/db) validation. The idea is that if someone circumvents your screen/intended execution path, you still have database integrity.

I agree that what I posted earlier is a rule of thumb. Usually, it is good to go with those unless you have a good reason not to.
Chit Ming Chong
Ranch Hand

Joined: Sep 09, 2004
Posts: 49
If the overhead (programmer's time) of putting check on the database is not that big then why not?

Just want to share some thoughts, I think there should be a good balance between good programming practice and getting the job done. The way I normally program is to get things working using what I have in my head then I will think about design structure, security, reliability and performance.
 
GeeCON Prague 2014
 
subject: Null value check