I am dealing with a pretty extensive data set. After a few joins, the foundation table has something like 40 columns. The next 40 columns will all be calculated. I am trying to keep my Java clean of the business logic because it is fairly verbose, at times difficult to follow, and not necessarily belonging to a single model. In short, I think that because of the sheer magnitude of business logic, Java may be difficult for future users to follow.
On the flip side, I have tried doing the business logic in SQL. However, suppose I have columns A, B, C, D, where A and B are from the foundation table.
C = A + B D = C + B
Now because D is dependent on C, this requires either a nested select or a
with clause, and as a result also becomes very messy very quickly.
Recently my coworker introduced me to cursors, not something I have much experience with, but they seem ideal for this use case. I do a
select...join... to retrieve a fairly large foundation view, then I use the cursor for that view to loop through each row and calculate all the additional columns. In this way, I can do
D = C + B much more easily. There will still be a fair amount of noise here, however it feels as if my business logic will be centralized here. At the end of all calculations, I will simply insert the values as a row into some table, then my Java can query that table.
Some preliminary reading tells me that cursors are not adviseable as they introduce additional overhead. However, if all I am doing is purely arithmetic operations to populate a number of columns, is the user of a cursor valid? I have written some of the code and it is easy to follow and centralized.
I get that I can do the same in Java, but I would rather have the fully calculated table available in my DB, and it feels like the roundtrip through Java is unnecessary because the cleanliness of the code there will not be much better.
EDIT: I'd like to include also that although the column span is fairly large, the expected amount of rows is actually not that great, it is somewhere between 1-2,000. To that effect, an
INSERT in a loop seems like it wouldn't be too bad.