11

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?

Rich Apodaca
  • 28,316
  • 16
  • 103
  • 129
  • 3
    +1 Interesting. I've always just stored data in a db, and implemented any logic in a business layer. In fact I've been religious about this. I'd be interested in seeing a project where calling java code from a db was a good design decision. Any links? – D.C. Jan 09 '10 at 19:33
  • 1
    Custom functions which goes beyond DB's capabilities for example. I however also highly question the value of being able to do such thing. DB's are there to store/manage data, not to do some business stuff. – BalusC Jan 09 '10 at 19:36
  • 1
    @darren, 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. – Rich Apodaca Jan 09 '10 at 20:04

5 Answers5

6

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).

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 1
    of course, nobody in their right mind sends email directly from the RDBMS (mail server might be down for example). The correct way to do this is to add the email to an email queue table, and have a separate process check this email queue table and actually send the email. This solves the transaction problem, since a rollback would roll back the addition of the email to the queue table. – Tony BenBrahim Jul 01 '16 at 16:08
2

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

Denis Tulskiy
  • 19,012
  • 6
  • 50
  • 68
1

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.

extraneon
  • 23,575
  • 2
  • 47
  • 51
1

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.

Stephen C
  • 698,415
  • 94
  • 811
  • 1,216
  • Using an ORM does not solve anything. It just masks the problem: http://blogs.tedneward.com/2006/06/26/The+Vietnam+Of+Computer+Science.aspx – Gili May 17 '14 at 22:44
1

I realise that this is quite an old article, but it bears updating. The ability to call java from a database trigger is is part of the "SQL Routines and Types for the Java Programming Language" (SQL/JRT) standard.

Read more about this on Wikipedia at https://en.wikipedia.org/wiki/SQL/JRT.

Amongst the compliant database engines are..

HyperSQL: http://hsqldb.org/ Oracle: https://www.oracle.com/database/

GCUGreyArea
  • 141
  • 2
  • 7