I'm in an anti-pattern and I want to get out.

Question

I am developing a java webapp, using jsp/jquery/ejb/jboss.

I have a web-form that enables the user to select any combination of 100 fields (all from different unrelated tables/objects) from the database. These fields are then output, via a java servlet, to an excel spreadsheet. A stored procedure is executed that always returns all 100 fields.

The web-form sets 100 boolean values in a transfer object(TO) to determine whether data should be then be displayed. This TO is then referenced to produce the title row of the spreadsheet and also for each row from database which is iterated over.

It all works fine, however it feels wrong. I cannot think of a viable way which does not reference 100 booleans (N+1 times) to determine whether a field should be included in the outputted spreadsheet. When I say viable I mean, for example, that I don't want to rewrite stored procedure or create 100 different stored procedures.

Answer 1

You could eliminate the manual field enumeration by:

• on form load, for each of your available spreadsheet fields, you output a boolean control. Probably best to prepend some sort of prefix so you don't have conflicts with any other fields that exist on the form.

• on form submission, you display any included fields that have the prefix.

Answer 2

Instead of using a stored procedure for this couldn't you build your select SQL string dynamically in your application and then execute that SQL statement. So you only need to reference you booleans once and you only return the columns you need.

Answer 3

You'll have to evaluate whether this is really better, but you could use an approach where you use a bit array to store whether or not the fields are to be used. Each field would have a value corresponding to a single bit:

static final FIELD1 = 1;
static final FIELD2 = 2;
static final FIELD3 = 4;
static final FIELD4 = 8;
static final FIELD5 = 16;
etc

Each form field would have a value of its bit value when selected, or 0 when not selected. On form submission, total up the fields and submit the value. So if FIELD1 and FIELD3 were checked, you'd submit a value of 5 (00101 in binary).

Then you apply a simple bitmask for each field to determine which ones were selected (is there a better way than field-by-field?):

boolean field1Selected = sum & FIELD1;
boolean field2Selected = sum & FIELD2;
etc

Disadvantages: with 100 fields, you're talking about a really big number! You might have to use 2 bit arrays. I'm also not convinced that this really simplifies your problem, but maybe it does.

Answer 4

Our solution was in similar situations to create a dynamic Transfer Object. Basically, it was a Map instead of a POJO having a number of getters and setters.

The codes which fills and reads this transfer object were simple iterations.