Java (JSP), way of implement UNIQUE CONSTRAINT

[b4u]

The question is about UNIQUE CONSTRAINTS, how to control them and give messages to users. A way of doing that with the best user experience I can get from it.


Let's imagine the follow situation: A JSP page displays a list of values. There is a simple SELECT statment behind, that can return for example 10000 records.

First problem, ,is that I cannot show all records at once, or the browser will have a hard time parsing the data, and it will get really slow ... to the point of a user thinking it has crashed. Also their CPU usage rockets to 100% or something similar. (ok, there are filter options for the data, but I'm not counting on them right now)

So the first thing to do, is to page the data ... so I'll put for example 100 records at a time, with controls at top/bottom of the list (first page/previrous page/next page/last page).

At the end of that list, I'll include a new record so people can insert records (something like MS Access, where we can change all data (updates) and the last record is for new record (insert)).

Now I'll have a situation which I have to solve, and given my ideas, I would like to ear about how you would do it, and if you could even give me another new idea, would be great.

Let's imagine the table I'm using has 4 fields (i'll simplify here):

ID int - Primary key (unique)
FIELD1 char(x)
FIELD2 char(x)
FIELD3 char(x)
There can only be a pair of FIELD1 & FIELD3.

The JSP displays the list with textboxes, so users can change data. The JSP that processes the list, will call a java class. That class will access the list values, and create the INSERT and/or UPDATES to the table. It will then call a specific method which will execute those SQL instructions. If any of them fails, it rolls back the transaction ... simple as we know.

How would I "solve" the uniqueness of FIELD1 & FIELD3 ?


1st Idea

Create a UNIQUE INDEX consisting of FIELD1 and FIELD3.

Advantage:

Uniqueness is checked on database, uppon insertion/updating. I only have the number of records to display on the page, ie, I'll call a class that will do the select, and based on the current display page, it will show the 100 records or so related to that page. Huge improve on JSP processing, since it doesn't have to work on all the data retrieved from the select statement.

Disadvantage:

When executing each SQL statement, I'll have to check if the reason for a fail has to do with the UNIQUE INDEX. If it has, I'll have to inform the user.

But since it's a generic method that executes the statements, I don't have a straight way of telling the user which one fails. Ok, I can find a form of returning info about which SQL instruction failed to the calling method, and it will then create a pretty message (since he knows which record originated that SQL instruction). But I wouldn't be able to tell if it would fail in the next SQL, for example.

So if the user changes data and there will be 3 SQL statements, if and all of them are not compliant with the UNIQUE CONSTRAINT, it will fail at the first one, I'll then inform the user with a message ("The record bla bla bla is duplicated, correct please, bla bla flowers bla bla ..."), he will resubmit again with a change in the first statement, but it will fail on the second, and so on, and so on ... (also the change in the first SQL might lead to the second change bumping into the UNIQUE CONSTRAINT, although that I can check before submiting the data).

I mean, he may be changing some records in the first page, to values that will bump into values of records on the 130th page ... so my messages are the only way he can "see" the mess he's trying to make.


2st Idea

Given to me by someone where I work on, it would help to some extend, but would give me lot's of troubles. I'm avoiding this one at all costs ... but here it goes ...

I would get all records from the database, show only those related to the current page number, and create a huge javascript array.

Before user submition, I will run a function where I 'll check the records on the current page against all records in the database, ie, on my javascript array.

Advantage:

I can check on the client side for duplicates on the unique fields. I can tell the user which values are bumping against which values, all at the same time. So the user can correct them in one passage.

Disadvantages (Huge Ones):

First, if someone else changes the database, I'll have a javascript array which is not consistent with data in the database :Q. So even not displaying a message to the user, I can still get some UNIQUE CONSTRAINT violations ...

Second, a user with more knowledge about the technology, can change the contents of the javascript array, leading to UNIQUE CONSTRAINT violations. :Q



So for now, I would not choose the 2nd idea at all. I would prefer the first one, but I would like to hear for some opinions, or other ideas to a new way of doing things, or to improve the disadvantages of my first idea.


Any opinions welcome. Thanks.

 

[lozina]

Yeah I'd go with the first one...

firstly, when you attempt to execute sql where the unique constraint is violated, it should spit out that unique constraint's name somewhere in the stack trace (at least with Oracle it does), so you can search the stack trace for that and be certain the failure was due to that particular constraint.

secondly, if you worried about concurrency with multiple statements you should put them in a transaction, so if one fails everything fails

 

[torpid]

Both options are not good. You don't want to rely on exceptions to catch known and predictable business errors. You should be validating the input before sending it to the DB. The most accurate way would be to read the DB to see if the record exists. This might be a performance problem, in which case you could create the unique index (option 1) and use it as a last resort, and validate against the in-memory data.

 

[kamper]

Option 2 isn't any better than putting all 10,000 rows in html in the page, you'd still have roughly the same amount of data to send to the client and running a uniqueness check against such a large array on the client side in javascript probably won't be all that quick either.

I think that allowing the database to make the uniqueness check is acceptable, so long as you can be sure that a resulting exception indicates that violation and not some other sql error. Checking on your own first would just add more overhead, both performance-wise and code maintenance-wise. Sometimes problems simply are complex enough that you have to do some dirty work to solve them.