How to Call Java Code from MySQL?

Question

I found an article from 2008 discussing how to call Java code from MySQL. There were a lot of caveats and disclaimers because the process involved working with an experimental branch of MySQL.

For a project I have in mind, it would be very useful to be be able to access Java libraries within MySQL, analogous to Oracle's Java Stored Procedures. Does this capability now exist as a standard feature of MySQL? If not, what open source RDBMSs support something similar to Oracle's Java Stored Procedures?

Answer 1

PostgreSQL supports pluggable procedure languages, and a project exists to extend PostgreSQL with PL/Java as the language.

I don't recommend putting too much code in the RDBMS. Tools to develop, test, and debug code in the application layer are better than tools for code in the RDBMS.

Also many developers don't understand that code inside the RDBMS should obey transaction isolation. They try to send emails from triggers and so forth. I think code with side effects should be in the application layer, so you don't create phantom effects (e.g. an email may notify of a database change, even though the change was rolled back).

Answer 2

I fully agree with Bill, but I can imagine business rules being stored (not processed) in the database. I'm thinking of drools here. The engine would be in the application, but the rules could be in the database with a management front-end.

Such a beast would be interesting for scenarios where not only the parameters change, but also the formulas can change.

Answer 3

If you can use HSQLDB then you can call java methods directly from SQL: http://hsqldb.org/doc/2.0/guide/sqlroutines-chapt.html#N1240C

Answer 4

It is difficult to give good advice based on the limited information that you have provided so far. However:

... the example involves a graph-based data type (chemical structures) that can't be matched to a query using built-in MySQL functions. The Java library would convert the query and contents of a text field into an in-memory object that can by matched. Keeping this logic in the DB layer would, for example, keep joins within the database, which seems like where they belong. That's the idea, at least.

I don't think I would use database-side Java in MySQL for this. Instead, I think I would consider the following options:

• Use an object-relational mapping such as JDO or JPA (for example using Hibernate) to deal with the mapping between your graph-based data model and what the database provides. You don't necessarily have to use an RDBMS as the backend, but that is probably the best place to start ... unless you've already found that this is a performance issue.

• Take another look at your data model and data access patterns. See if you can figure out some transformation that allows your application's main queries to be implemented as (efficient) table joins without resorting to server-side application logic.

• If you do need to use server-side application logic (for performance reasons!) stick with the mechanisms supported by your RDBMS. For example, in Oracle you'd use PL/SQL and PostgreSQL you have a number of options. Be prepared to switch to a different RDBMS that better suits your application requirements.

I (personally) would avoid depending on an experimental branch of some database:

• Consider what happens if the experimental branch is not merged back into the main branch. You would be stuck with your code base depending on a branch that is not supported, and is likely to stop being maintained and fizzle out.

• Using a (currently) unsupported RDBMS branch will be an impediment to other folks who might want to use your software.

Now obviously, if the long term viability of your software is not a primary concern, you could choose to ignore this advice. But it probably matters to someone; e.g. your research supervisor.