Need advice on exception handling approach

[Arno Werr]

Colin,
Why should I duplicate functionality of existing database? Why Java validation is better then validation performed by database? I prefer let the trigger do its job and then handle its response. Otherwise I have to maintain two codebases - scripts (stored procedures, triggers) and Java to keep them in sync.

[fschroder]

I would say that the answer to that is performance. Maybe it is just premature optimization, but I understand if people wants to avoid doing the same check twice (code and db).

[Colin Yates]

So you don't do *any* checks in code? You don't check that the mandatory fields are set, that the min/max lengths aren't exceeded etc.?

If you do, then you have answered your own question

[Arno Werr]

I do not mix validation into a single mumbo-jumbo. I separate syntax validation (upper case, characters, max-min length which can be performed even by HMTL) from semantic validation. If part of this semantic validation is performed by database due to some complex rules set by the database schema (and, incidentally, maintained by other guys - DBA), I do not want to duplicate those rules in my code. I rather rely on db trigger and handle response. Those DBA guys changed something in their trigger code, OK my code will react to their change with a properly handled exception.

[tatvamasi]

I do not want to duplicate those rules in my code

Huh! so what the reason for having a middle tier (application layer)? I wonder if you can get done with having bunch of stored procedures and triggers doing job for ya'. Why do you need high profile language like java to do that? or why do you even need a middle tier to your application? Hummmmm, so If your application is to respond to some third party application and not your database than other third party have to make sure the data provided to them is not dirty data. Is that what you are telling us? Or even if your company decided to do a database upgrade or change you want to assume that DBA's would do their job right and your application should work as long as they do their job right. Hummm so whats the point having a saperate exception handling architecture in your application? Geez man I don't even understand why are you using Object Oriented technologies or a pattern oriented architecture or even a high profile framework like spring.

[Arthur Loder]

Hi Arno,

I definitely understand what you mean with regards to the problems associated with maintaining the validation rules in the database and Java code. But my question is this: even if you do catch the DataIntegrityViolationException, how would you provide an informative error message to the user? What if a single entity, such as a User that is stored in the USER table, has multiple columns with unique constraints (such as USERNAME and EMAIL_ADDRESS)? How would you inform the user which field(s) he entered already exists in the database so that he can fix the problem?

Maybe there is a way to inspect the DataIntegrityViolationException to determine the offending field(s), but can you provide a corresponding user-friendly error message to explain to the user?

For this reason, it makes sense to me to enforce the rules during pre-insertion validation. As Colin mentioned, there is still a window in which data that passes this initial validation could still be rejected by the database. Maybe in those cases, a generic error could be reported to the user. But subsequent attempts would be caught during pre-insertion validation, and a meaningful error message could be provided.

-Arthur Loder

[Colin Yates]

I thought I would use this topic as the subject of my first blog post!

You can find it here if you are interested http://blog.interface21.com/main/200...my-first-post/

[Arno Werr]

Huh! so what the reason for having a middle tier (application layer)? I wonder if you can get done with having bunch of stored procedures and triggers doing job for ya'. Why do you need high profile language like java to do that? or why do you even need a middle tier to your application?

...Geez man I don't even understand why are you using Object Oriented technologies or a pattern oriented architecture or even a high profile framework like spring.

Hi tatvamasi,
You know who Rod Johnson is what his relations toward Spring framework. Let's see...

J2EE developers (and Java developers in general) tend to hate stored procedures. There is some justification for this:

Stored procedures aren't object-oriented. Readers familiar with Oracle will counter that Oracle 8.1.5 introduced Java stored procedures. However, they don't solve the O/R impedance mismatch as much as move it inside the database, and they don't foster truly object-oriented use of Java.

Stored procedures aren't portable. Support for stored procedures varies much more than SQL dialects between RDBMSs. Nevertheless, it would be rare to lose the entire investment in a set of stored procedures on migrating from one RDBMS to another.

If stored procedures grow complex, they may reduce an application's maintainability.

Some other common objections have less validity:

"Using stored procedures puts business logic in the wrong place"
If we distinguish between persistence logic and business logic, the idea of putting persistence logic in a relational database makes perfect sense.

"Using stored procedures means that J2EE security may be compromised"
Security is a matter of business logic, not persistence logic: if we keep our business logic in J2EE, there is no need to restrict access to data.

"The database will become a performance bottleneck"
Especially, if a single database instance is serving a cluster of J2EE servers, the processing of stored procedures may limit overall performance. However, there are trade-offs to consider:

In my experience, it's much commoner to see network performance between application server and database limit the overall performance of a J2EE application than the performance of a well-designed database.

There's no reason to perform an operation in a stored procedure rather than a J2EE component unless the operation can be done more naturally and efficiently inside the database server than in Java. Thus if we've implemented an operation efficiently inside the RDBMS and it still eats the server's CPU, it probably indicates that the RDBMS is badly tuned or needs to run on better hardware. Performing the same heavily-requested operation less efficiently in the application server will probably result in a more severe problem, and the need for more additional hardware.

The use of stored procedures from J2EE applications is an area where we should be pragmatic, and avoid rigid positions. I feel that many J2EE developers' blanket rejection of stored procedures is a mistake. There are clear benefits in using stored procedures to implement persistence logic in some situations:

Stored procedures can handle updates spanning multiple database tables. Such updates are problematic with O/R mapping.

A more general form of the first point) Stored procedures can be used to hide the details of the RDBMS schema from Java code. Often there's no reason that Java business objects should know the structure of the database.

Round trips between the J2EE server and the database are likely to be slow. Using stored procedures can consolidate them in the same way in which we strive to consolidate remote calls in distributed J2EE applications to avoid network and invocation protocol overhead.

Stored procedures allow use of efficient RDBMS constructs. In some cases, this will lead to significantly higher performance and reduce load on the RDBMS.

Many data management problems can be solved much more easily using a database language such as PL/SQL than by issuing database commands from Java. It's a case of choosing the right tool for the job. I wouldn't consider using Perl in preference to Java to build a large application; neither would I waste my time and my employer's money by writing a text manipulation utility in Java if I could write it in Perl with a fraction of the effort.

There may be an investment in existing stored procedures that can be leveraged.

Stored procedures are easy to call from Java code, so using them tends to reduce, rather than increase, the complexity of J2EE applications.

Very few enterprises with existing IT shops have ported all their applications to J2EE, or are soon likely to. Hence persistence logic may be more useful in the RDBMS than in the J2EE server, if it can be used by other non J2EE applications (for example, custom reporting applications or in-house VB clients).

The danger in using stored procedures is the temptation to use them to implement business logic. This has many negative consequences, for example:

There is no single architectural tier that implements the application's business logic. Updates to business rules may involve changing both Java and database code.

The application's portability will reduce as stored procedures grow in complexity.

Two separate teams (J2EE and DBA) will share responsibility for business logic, raising the possibility of communication problems.

If we distinguish between persistence logic and business logic, using stored procedures will not break our architecture. Using a stored procedure is a good choice if it meets the following criteria:

The task cannot be accomplished simply using SQL (without a stored procedure). There is a higher overhead in invoking a stored procedure using JDBC than in running ordinary SQL, as well as greater complexity in the database.

The stored procedure can be viewed as a database-specific implementation of a simple Java interface.

It is concerned with persistence logic and not business logic and does not contain business rules that change frequently.

It produces a performance benefits.
The code of the stored procedure is not unduly complex. If a stored procedure is appropriate, part of the payoff will be a simpler implementation than could have been achieved in a Java object running within the J2EE server. Especially in an organization with DBA resources, 10 lines of PL/SQL will prove easier to maintain than 100 lines of Java, as such a size discrepancy would prove that PL/SQL was the right tool for the job.

ImportantВ
Do not use stored procedures to implement business logic. This should be done in Java business objects. However, stored procedures are a legitimate choice to implement some of the functionality of a DAO. There is no reason to reject use of stored procedures on design grounds.

Chapter 7: Data Access in J2EE Applications
Working with Relational Databases
Expert One-on-One J2EE Design and Development
by Rod Johnson
ISBN:0764543857
Wrox Press В© 2003
[i]'Drawing on the author's experience'[\i]

I do support this. It fits my bill in terms of my professional experience perfectly and if '10 lines of PL/SQL will prove easier to maintain than 100 lines of Java' I'll go for PL/SQL.

There is place for Java and there is place for PL/SQL. Business logic vs. Persistence logic.

[Arthur Loder]

Hi Arno,

I think Rod was talking about certain requirements and whether they should be validated in a stored procedure or in Java code. However, even if they are implemented in a stored procedure, the code is aware of the validation and is invoking the stored procedure. I would think that when it is possible to perform the validation in a stored procedure and the result from the stored procedure is enough to provide valuable feedback to the user in the event of an error, then it makes sense as an option. However, we are talking about a simple database constraint, so the question is, "Should we add an additional check on the Java-side for the benefit of the user"? I think using Rod's argument would only be applicable if you are explaining why we should consider writing a stored procedure to determine if the data is valid rather than using Java code. However, you are not suggesting that; you are suggesting that we simply rely on the database constraint.

I also disagree with your statement that "Those DBA guys changed something in their trigger code, OK my code will react to their change with a properly handled exception." Let's assume that the User entity, stored in the USER database table, currently has a single unique constraint (besides its auto-generated primary key), for the column USER.USERNAME. Then the developer who tries to save a new user and catch the DataIntegrityViolationException might be tempted to alert the user that the username chosen is already taken (given that there is only one field that could have been the offending field). Well let's say that later there is an added business rule that states each user's email address must be unique. If this is implemented only by adding the database unique constraint to the USER.EMAIL_ADDRESS column and the code is left alone, then a user may get an incorrect error message telling him that the username is already taken, when the problem might be that the email address is taken. So I don't think that similar business rules can be implemented in the database without regard for or a change to the code.

My whole argument is based on the fact that I don't understand how to provide a user-friendly error message when catching a DataIntegrityViolationException exception. If I am wrong, I apologize and void my argument

-Arthur Loder

[Arno Werr]

I definitely understand what you mean with regards to the problems associated with maintaining the validation rules in the database and Java code. But my question is this: even if you do catch the DataIntegrityViolationException, how would you provide an informative error message to the user?

Hi Arthur,
It's an issue of separation of concerns.
Whoever wrote PL/SQL code should provide semantically clear exception (if they don't - they do not deserve their salary). Not just obscure db-specific code. PL/SQL has a very advanced exception handling and propagation capabilities. PL/SQL generates semantically clear exception. I can catch it up, unwrap it, and display to the user.