Stored Procedures and Functions in Java

Is Java your primary programming language? Do you need to create stored procedures for a database? Do you want to develop those procedures in Java?

If YES, then follow the guide below!

Stored Procedures and Functions in Java

Postgres

For Postgres, you can use the PL/Java extension. Below are few good resources to get to know the module:

Install and Start Postgres

You need to build PL/Java from sources. This requires you to get a Postgres instance running on your host machine.

Download and install Postgres. For Ubuntu, use the apt manager and install the following packages: https://tada.github.io/pljava/build/ubuntu.html
Start a Postgres instance. Depends on your OS: https://tableplus.com/blog/2018/10/how-to-start-stop-restart-postgresql-server.html

For Ubuntu you can use these commands to start and stop the service:
```
sudo service postgresql start
sudo service postgresql stop
```
Connect to Postgres making sure it's working:
```
#For Ubuntu
sudo -u postgres psql
```

Buld and Install PL/Java

Clone:

git clone https://github.com/tada/pljava.git

Build:
```
mvn clean install
```

Install into Postgres:

java -jar pljava-packaging/target/pljava-pgX.jar

Create the extension:

#Connect to the `postgres` database with psql
sudo -u postgres psql -d postgres

#Specify a libjli location (libjvm for other operating systems)
SET pljava.libjvm_location TO '/home/dmagda/.sdkman/candidates/java/current/lib/server/libjvm.so';

#Make the setting persistent, so that it applies to all connections to the postgres database
ALTER DATABASE postgres SET pljava.libjvm_location FROM CURRENT;

#Install the extension
CREATE EXTENSION pljava;

Make sure you see pljava in the extensions list:
```
SELECT * FROM pg_extension;
```

Run Examples Shipped with PL/Java

Test the extension by running examples that are bundled with the pljava: https://tada.github.io/pljava/examples/examples.html

Load the examples into Postgres:

SELECT sqlj.install_jar(
'file:'
'{your_project_root_dir}/pljava/pljava-examples/target/pljava-examples-2-SNAPSHOT.jar',
'examples', true);

Make sure the javatest schema appeared on search_path:
```
SHOW search_path;
```
Check that the Java classpath for that schema includes the ID for this jar:
```
SELECT sqlj.get_classpath('javatest');
```

If it doesn't, set the classpath:

SELECT sqlj.set_classpath('javatest', 'examples');

Run a sample example function:

SELECT javatest.java_addone(5);

     java_addone 
-------------
           6
(1 row)

Install and Run Custom Function

The CustomFunctionsPostgres class includes functions that you can add to Postgres and execute via the SQL interface.

Build the project:
```
mvn clean package
```

(Optional) curious to see what is in the pljava.ddr file?

unzip -c target/java-database-functions-1.0.jar pljava.ddr

#The ouput might be as follows
    Archive:  target/java-database-functions-1.0.jar
inflating: pljava.ddr              
SQLActions[]={
"BEGIN INSTALL
BEGIN PostgreSQL
CREATE OR REPLACE FUNCTION postgresSayHi()
        RETURNS pg_catalog.void
        LANGUAGE java VOLATILE
        AS e'void=com.yugabyte.examples.CustomFunctionsPostgres.postgresSayHi()'
END PostgreSQL;
BEGIN PostgreSQL
COMMENT ON FUNCTION postgresSayHi()
IS e'The Hello World function that returns information about the runtime.'
END PostgreSQL;
END INSTALL",
"BEGIN REMOVE
BEGIN PostgreSQL
DROP FUNCTION postgresSayHi()
END PostgreSQL;
END REMOVE"
}

Connect to Postgres:
```
#Ubuntu-specific
sudo -u postgres psql
```

Install the functions into Postgres:

SELECT sqlj.install_jar(
'file:'
'{your_project_root_dir}/java-database-functions/target/java-database-functions-1.0.jar',
'java_custom_functions', true);

Read this doc to understand what install_jar arguments mean: https://github.com/tada/pljava/wiki/SQL-functions

Add the jar to the Postgres public schema:

select sqlj.set_classpath('public', 'java_custom_functions');

Make sure the Jar is on the public schema's classpath:

select sqlj.get_classpath('public');

     get_classpath     
-----------------------
java_custom_functions
(1 row)

Run the following function that returns information about your runtime:
```
SELECT public.postgresSayHi();
```

Update Custom Function

As a developer you change the code all the time. Which means that our Java functions needs to be updated, recompiled and redeployed to Postgres.

Go ahead and change the implementation of the CustomFunctionsPostgres.postgresSayHi() function (at a minimum, change the message printed by the function) and redeploy the function to Postgres.
Create a new package:
```
mvn clean package
```

Redeploy the function to Postgres using the replace_jar procedure:

SELECT sqlj.replace_jar(
'file:'
'{your_project_root_dir}/java-database-functions/target/java-database-functions-1.0.jar',
'java_custom_functions', true);

Execute the updated function:
```
SELECT public.postgresSayHi();
```

Oracle

Oracle Database runs Oracle JVM natively. The JVM is deeply integrated into the database.

Enable Oracle JVM with Autonomous Database

The following guide gives a high-level overview of the configuration process. But that guide lacks precise step-by-step instructions. Instead, let's follow another guide that shows how to enable Java for the Oracle Autonomous Database (the cloud version).

Start an Oracle Autonomous Database: http://cloud.oracle.com/
Start Oracle Cloud Shell:
Follow the guide below to connect to the database from Cloud Shell: https://blogs.oracle.com/cloud-infrastructure/post/simple-steps-to-connect-autonomous-database-from-oci-cloud-shell

Note, add the TNS_ADMIN variable to the .bashrc file so it's always set if Cloud Shell is restarted.

Enable Java on the database end:

BEGIN DBMS_CLOUD_ADMIN.ENABLE_FEATURE(feature_name => 'JAVAVM');
END;
/

Restart the database to finish the Java installation

Check the Java version:

SELECT status, version FROM DBA_REGISTRY WHERE comp_id = 'JAVAVM';

Check the JDK version:

SELECT dbms_java.get_jdk_version FROM DUAL;

Install and Run Custom Functions

Compile the CustomFunctionsOracle.java class
Load the class to the Cloud Shell instance (feel free to use other methods): https://docs.oracle.com/en-us/iaas/Content/API/Concepts/devcloudshellgettingstarted.htm#Cloud_Shell_Transferring_Files
Connect to the database and load the file:
```
sqlplus {username}@{database_name}
```
replace {username} and {database_name} with your settings.

You need to have several security permissions in place before deploy the Java code:

grant JAVASYSPRIV to ADMIN;

call dbms_java.grant_permission('ADMIN', 'SYS:java.io.FilePermission', '<<ALL FILES>>',  'read,write');

Load the file onto the database:

call dbms_java.loadjava('{class_dir}/CustomFunctionsOracle.class');

Next steps, not finished yet https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdev/running-Java-stored-procedures.html#GUID-6B7D8A8A-D77B-4345-B9E5-ADC8180A6423

Apache Ignite

Apache Ignite support a comprehensive compute API for Java. You can think of it as of an advanced version of stored procedures.

Install and Start Ignite

Download Ignite 2.13.0 (the binary package): https://ignite.apache.org/download.cgi
Navigate to the bin directory:
```
cd apache-ignite-2.13.0-bin/bin
```

Create a configuration file name ignite_config.xml with the following content:

<beans xmlns="http://www.springframework.org/schema/beans"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="
   http://www.springframework.org/schema/beans
   http://www.springframework.org/schema/beans/spring-beans.xsd">

    <bean class="org.apache.ignite.configuration.IgniteConfiguration">
        <property name="peerClassLoadingEnabled" value="true"/>
    </bean>
</beans>

Start an Ignite server node with that configuration:
```
./ignite.sh ignite_config.xml
```

Run Custom Compute Task

Compile and start the CustomFunctionsIgnite.java class

Note, if you're on Java 9+ then add the following JVM arguments to the class startup:

--add-exports=java.base/jdk.internal.misc=ALL-UNNAMED --add-exports=java.base/sun.nio.ch=ALL-UNNAMED --add-exports=java.management/com.sun.jmx.mbeanserver=ALL-UNNAMED --add-exports=jdk.internal.jvmstat/sun.jvmstat.monitor=ALL-UNNAMED --add-exports=java.base/sun.reflect.generics.reflectiveObjects=ALL-UNNAMED --add-opens=jdk.management/com.sun.management.internal=ALL-UNNAMED --add-opens=java.base/java.nio=ALL-UNNAMED --add-opens=java.base/java.util=ALL-UNNAMED --illegal-access=permit

Check the logs of the Ignite server node. The logic will be executed there!

Update Custom Task

Go ahead and update the CustomFunctionsIgnite.igniteSayHi() method the way you like.
Restart the app. Check the server logs. The new version of the logic will be updated automically.

YugabyteDB

TBD

dmagda/java-database-functions