Instructions

This is a simple MySQL UDF that enables to call Java functions from MySQL. The library (.dll or .so) file must correspond to the architecture of 
your MySQL version, e.g. the 64bit compiled MySQLUDFJavaLauncher.dll/libMySQLUDFJavaLauncher.so requires a 64bit MySQL server 
(same for 32 bit) and needs to be copied into the plugins directory (e.g.<MySQL Server Home Directory>\lib\plugin on Windows platforms) 
along with the two jar files mysqljavatrigger.jar and utils4j-0.7.0.jar. Also any java class files whose methods will be invoked have to reside 
in this folder, otherwise the method calls will fail. Additionally, Java and MySQL paths are queried through environment variables, i.e. JAVA_HOME and MYSQL_HOME 
must be set appropriately. The UDF requires Java Development Kit 1.6, hence the environment variable should point to the installation directory of the same version. 
Furthermore, the path to the jvm.dll and libjvm.so respectively must be added to the PATH variable, e.g.%JAVA_HOME%\jre\bin\server;.    

Note: After setting all environment variables you need to restart the MySQL server! For Linux users it is advisable to add the variables system-wide
in the /etc/environment file and also grant access to the plugin directory for all potential users, otherwise you might come across 
permission issues.

Next is an example of the UDF's format:

select call_java_method('Test', 'myMethod("Hello World")');

The function call_java_method takes 2 arguments, class name (without file extension .class) and the method name with an 
optional parameter list inside the parentheses. In the example a method myMethod() of the class Test is called with a single string 
argument (the class is available in the source and can be used for testing purposes once the UDF has been installed ). The class name must be fully 
qualified, if it resides in a package, using the dot notation, e.g. de.udf.Test. Multiple parameters must be separated by commas. Starting 
and ending single/double quotes that enclose a parameter will be ignored, meaning they can be omitted in the method signature. The values will be 
treated as Strings and are later casted according to their parameter types obtained via reflection. The called method can only accept primitive types 
and the return value is always converted to String, i.e. the toString() method will be invoked on the returned object. More complex data 
structures, such as Arrays or Collections must be transferred in a specified format (e.g. JSON or XML strings) to parse them manually. When a class 
doesn't exist or the parameter list is invalid, a message is sent back to the server giving a hint to the cause of error (e.g.Insufficient parameters. 
expected: 2, received: 1). Java classes placed inside the plugin directory must respect the package structure in order to be considered by the Java Classpath, 
e.g. if the Test class from the example has the package name de.udf, it should live in the directory /de/udf/. JAR files have to be copied into
the jars directory, which will be created automatically on the first invocation if it doesn't exist yet. Following are a few sample UDF calls:


select call_java_method('Test', 'myMethod("signature consists of multiple parameters: string, double & float", 2d, 3f)') AS MySQLUDFJavaLauncher;
select call_java_method('de.test.A', 'getB("this function creates an  instance of class B inside another package and returns it")') AS MySQLUDFJavaLauncher;
select call_java_method('JSONExample', 'sendMessage("JSON messages can be parsed with 3rd party libraries inside the jars folder",{"key1":"value1","key2":"value2"})') AS MySQLUDFJavaLauncher;


The UDF can be installed with the following SQL command:

Windows:

CREATE FUNCTION call_java_method
   RETURNS STRING
SONAME 'MySQLUDFJavaLauncher.dll';


Unix:

CREATE FUNCTION call_java_method
   RETURNS STRING
SONAME 'libMySQLUDFJavaLauncher.so';


It can be used with triggers, e.g. to notify Java clients about changes in the backend. Here are two samples:


DELIMITER //
DROP TRIGGER IF EXISTS users_table_trigger //
CREATE TRIGGER users_table_trigger
  AFTER INSERT ON users
  FOR EACH ROW
BEGIN
  DECLARE c VARCHAR(200);
  SET c = (SELECT call_java_method('NotificationTrigger', 'sendNotification("phone number of user Max in table 'Users' changed", "{"phoneNo":"123 456"}")'));
  INSERT INTO trigger_log(id,message) VALUES(null, c);
END//
DELIMITER ;



DELIMITER //
DROP TRIGGER IF EXISTS user_trigger //
CREATE TRIGGER user_trigger
  AFTER INSERT ON users
  FOR EACH ROW
BEGIN
  DECLARE c,d VARCHAR(200);
  SET c = concat('setUser(', NEW.id, ',',NEW.name, ',', NEW.email, ',', NEW.created_at,',',NEW.updated_at, ')');
  SET @d = call_java_method('com.sample.UserEntity', c);
END//
DELIMITER ;
INSERT INTO users VALUES(null,'John Doe','john.doe@sample.com', NOW(),NOW());
SELECT @d;