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