0

I am designing a web application that retrieves records from a database according to user search criteria. After retrieving results, another stage exists in the flow in which the results are "decorated" with additional data.

For example, we may have a Persons database table with columns such as: name, age, height, weight. The program can filter that table by a certain logical computation formula, e.g. keep all Person records where the person's weight is less than her "ideal weight", defined as:

IDEAL_WEIGHT = (height/100) * age

So, we will show all records where IDEAL_WEIGHT < weight, this can be accomplished with SQL, stored procedures, etc - bottom line is that we filter with SQL. Concerning the "results decoration" stage, it can be, for example, calculating the WEIGHT_PER_INCH for each result, defined as

WEIGHT_PER_INCH = height / weight

These "decoration" computations may be a lot more complex than the above, and are actually broader than mathematical formulas - they may involve data residing on remote hosts, or rely on other computations units. So - it seems SQL and probably PL/SQL are not suited for the job, rather Java is - bottom line is that we "decorate" with Java.

Now, what we mentioned above is filtering by IDEAL_WEIGHT and decorating with WEIGHT_PER_INCH, but what happens if we want to be able to have it the other way around? (i.e. filter by WEIGHT_PER_INCH and decorate with IDEAL_WEIGHT).

You may say that all these computation units should be of one type - either stored SQL procedures, or Java classes. But if they're SQL procedures they are not expressive enough, and if they are Java classes, they cannot be used to search a database quickly.

My question is:

Is there a clean, widely-used, fast way to invoke Java from SQL, e.g.

Select * from Persons p where IDEAL_WEIGHT(p) < p.weight

where IDEAL_WEIGHT() is a Java method, not an SQL procedure.

or: Is there some other way around all this?

Thanks a bunch for your thoughts.

bloodcell
  • 601
  • 1
  • 9
  • 23
  • do you have a specific DB product in mind? – p.marino Oct 27 '11 at 14:06
  • 1
    "Is there a clean, widely-used, fast way to invoke Java from SQL?" The answer to that is "stored procedures". In many databases (including DB2 and Oracle), you can write stored procedures in java. What database are you using? **edit**: Ah, mysql. I'm not sure what it supports, but if the criteria is "free database", then PostgreSQL has java stored procedures available through [PL/Java](http://pljava.projects.postgresql.org/). – jmelesky Oct 27 '11 at 14:08

2 Answers2

2

I am afraid this idea is unfeasible in general (more so if you are using MySQL).

There has been some effort to make Java directly invokable by MySQL, as you can see here:

How to Call Java Code from MySQL?

but apparently this didn't get any further development (the only references I found through an admittedly cursory google search are from 2008).

Some other RDBMS allowed (or maybe still allow) to use Java to write stored procedure. Oracle for sure, and someone has already mentioned Postgres. The point is that even in the case of Oracle (which uses an embedded JVM) you have to deal with some limitations - I doubt you could really write a stored procedure which can use the full set of Java libraries, or that can invoke remote services etc.

Please understand that a RDBMS is a sort of "ideal world" which is supposed to be auto-consistent. The idea that a query may be filtered (or decorated) with facts from the outside world goes against the grain of the RDBMS philosophy (and would make the Query Engine unable to make any intelligent choice on how to perform the query) so I really doubt this could work in any useful way.

Community
  • 1
  • 1
p.marino
  • 6,244
  • 3
  • 25
  • 36
  • That is a nice way of putting it :) So is there some way to satisfy this need of searching a db quickly, but using the computations used in that search in Java code ? – bloodcell Oct 27 '11 at 15:06
  • Your best bet is to write your SQL query to be as restrictive as possible, and then apply the Java calculation (on the client side) on the resulting set. See if you can come up with some heuristic that can be expressed in your SQL dialect to reduce the number of returned rows before sending it to the client. – p.marino Oct 27 '11 at 15:12
1

Is the data static and deterministic? By that I mean (1) once an entry (say, for a person) is entered, does it change either by user update or by periodic/automated adjustment, and (2) for the complex calculations, if you feed in data X, will you always and inevitably get results Y?

Depending on the answers to these, you might want to consider a data warehouse-like solution. At the time all your "base" data (age, height, weight) are entered, also perform the complex calculations and store those results in the database as well. With that, you can filter your queries based on those stored results. This won't work if (for whatever reason) the calculations have to be performed each time data is retrieved, and might be inefficient if they base data changes with too-great frequency.

Philip Kelley
  • 39,426
  • 11
  • 57
  • 92
  • My thoughts exactly. @bloodcell Why are you concerned with dynamically calculating these values? You should calculate the results in Java and store them in the database only when the inputs change. – user229044 Oct 27 '11 at 14:19
  • Please assume that static calculation of values is not possible - a computation may have lots of configuration options, and pre-calculating for all configurations is not feasible. Beyond that - the data itself is dynamic, and computations are deterministic. – bloodcell Oct 27 '11 at 14:35