In this article, we will discuss SQL Server 2019 new feature—Java Language Extensions. With the advent of SQL Server 2019, Microsoft always strives to expand its footprint on the capabilities of MLS (Machine Learning Services). It builds a deeper integration between the data-platform and data science under the data science umbrella. The Microsoft SQL Server 2019 MLS extensibility framework provides a solid base for allowing extensions in R, Python, and now Java.

In this article, we will discuss the following:

1. Understand Machine Language Service
2. Discuss JAVA SDK installation and configuration
3. Configure Java Language Extension API
4. Prepare the sample JAVA code using Eclipse IDE
5. Deploy JAR file in SQL Server
6. Run the JAVA program
7. And more…

Get Started

In the last three major releases of SQL Server, SQL Server 2016, SQL Server 2017, and SQL Server 2019—integrated a framework that supports running an external programming language capability in the database server. SQL Server 2016 introduced R language; SQL Server 2017 supported Python and continued to support R. These two releases coined as the biggest game-changer in the field of data science, and these are categorized under MLS (Machine Language Service). Now, Microsoft strategically added another external language support under the Machine Language (ML). The trend continued in SQL Server 2019 with an addition of JAVA to the list of externally supported languages. In addition, SQL Server 2019 in-houses a wide range of new features and enhancements to the existing feature.

The extensibility framework APIs reference for running external scripts such as R and Python are supported in SQL Server 2017. This extensibility framework API now exposed to run JAVA programs using the sp_execute_external_script system stored procedure.

Pre-requisites

As we know that to run the Java program, you need JVM (Java Virtual Machine).

The pre-requisites to writing and running the Java program are as follows:

1. Java SE Development Kit( JDK)
2. Java Runtime Environment (JRE) is one of the components of the Java SDK. It enables and provides a platform to Java programs

Install JAVA SDK

In this section, we will discuss the steps to install and configure JAVA.

1. Download Zulu Java SE SDK for windows 64bit

2. Click the installation library

   

3. After the installation, configure the environment variable. Follow the step-by-step details to configure environment variables:

   • Go to computer properties
   • Click the Advanced system settings
   • Click Environment Variables

   • Specify the value of the PATH environment variable

     • C:\Program Files\Zulu\zulu-8\bin\
     • C:\Program Files\Zulu\zulu-8\jre\bin\server

Note: By default, SQL Server installs Zulu Open JRE (Java Runtime Environment) version 11.0.3. It is also possible to install the full JDK (Java Development Kit) for compilers and/or any other java distribution of your own choice.

Note: It is highly recommended to use Oracle Java SE and Zulu OpenJDK. In addition, during the installation process, it is advised to use the default Windows Program Files folder. 

Configure Java Language Extensions in SQL Server

SQL Server 2019 provides a platform to integrate the Java language extension library to leverage data exchange and communication using Extensibility Framework APIs. The language extensions allow you to extend SQL Server to use external code such as Python, R, and JAVA as part of SQL executions and operations.

Let us enable “external scripts” parameters using sp_configure option. Let us run the following T-SQL to validate the external scripts feature in the SQL Server configuration.

The value “0” indicates in the below output depicts the external scripts are disabled.

Now, run the following command to enable the external scripts

Let us re-run the aforementioned SQL to validate the “external scripts enabled” parameter

In the following output, we can see that R and Python are referenced in the SQL Server.

This indicates that the Java program is not registered. In the next section, we can discuss how to register the Java libraries in SQL Server.

To register the Java external language, walk-through the following step-by-step instruction.

1. In the SQL Server installation directory, you will find the Microsoft Extensibility SDK—mssql-java-lang-extension.jar. Let us locate the java-lang-extension.zip. In this case, the SQL Server installation folder G:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn. Now, browse the folder and locate the java language extension

   

2. Copy the java-lang-extension.zip to another folder. In this case, the java-lang-extension.zip is copied to G:\SQL

   

3. Run the following SQL to register Java external language

   1	CREATE EXTERNAL LANGUAGE Java FROM (CONTENT = N'G:\SQL\java-lang-extension.zip', FILE_NAME = 'javaextension.dll');

   In the following output, you can see that the Java external language registered successfully.

   

4. Let us verify the Java language registration using the following SQL

   1	SELECT * FROM sys.external_language_files

   

   We have successfully registered the Java library. We are not done yet….

5. As I mentioned above, you still need to reference the library to build a jar file. In this case, I use Eclipse editor to compile and build the java file(JAR file)

Build the JAR file

In this section, let us walk-through step-by-step instructions to build the JAR file. In this case, we will create the Java SQL2019 Demo Package and SQL2019 class.

Let us write the simple Java code in a .class file and save it in the folder G:\SQL.

Note: Ensure you have the full permissions on the folder G:\SQL folder.

Import the following packages in the class:

import com.microsoft.sqlserver.javalangextension.PrimitiveDataset;

import com.microsoft.sqlserver.javalangextension. AbstractSqlServerExtensionExecutor;

1. Right-click SQL2019 project and go to the Properties to open the Properties dialog

2. Click Java Build Path on the left navigation tree

   

3. Select the Libraries right

4. Click Add JARs

   

5. Now, browse G:\SQL and select mssql-java-lang-extension.jar

6. Copy the below code and paste it in the editor, or you can also define your own code

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

   package SQL2019Demo;   import java.util.LinkedHashMap;   import com.microsoft.sqlserver.javalangextension.*;   public class sql2019 extends com.microsoft.sqlserver.javalangextension.AbstractSqlServerExtensionExecutor{   public sql2019() {     executorExtensionVersion = SQLSERVER_JAVA_LANG_EXTENSION_V1;     executorInputDatasetClassName = PrimitiveDataset.class.getName();     executorOutputDatasetClassName = PrimitiveDataset.class.getName();   }     public static void main(String[] args) {     // TODO Auto-generated method stub     }      public AbstractSqlServerExtensionDataset execute(AbstractSqlServerExtensionDataset arg0,       LinkedHashMap<String, Object> arg1)   {   System.out.println("Welcome to SQL Server 2019");   return null;   }   }

   Please refer to the editor for more details:

   

7. Next, compile the java program

8. Now, to export the SQL2019Demo project, right-click and select Export

   

9. Select Java

10. Select Runnable JAR file and click Next

    

11. Type in the export destination and click finish

    

12. Now, create an EXTERNAL library from your program:

    1 2 3

    CREATE EXTERNAL LIBRARY javaSDK FROM (CONTENT = 'G:\SQL\java-lang-extension\mssql-java-lang-extension.jar') WITH (LANGUAGE = 'Java'); GO CREATE EXTERNAL LIBRARY test FROM (CONTENT = 'g:\SQL\welcome.jar') WITH (LANGUAGE = 'Java');

13. Let us verify the external references using the following SQL:

    1	SELECT * FROM sys.external_libraries

    

14. Finally, we are ready to run the Java program in SQL Server. The @script parameter is a combination of the package name and the class name. In this case, the package name is SQL2019Demo, and the Class name is SQL2019

    1 2	EXEC sp_execute_external_script  @language = N'Java' , @script = N'SQL2019Demo.sql2019' GO

Summary

SQL Server 2019 extensibility language framework provides a platform for allowing extensions in R, Python, and now Java. So far, we discussed Machine Language Service (MLS); Configure JAVA external language; run the JAVA program in SQL Server. In addition, we discussed the use of the Eclipse editor—Integrated Development Environment (IDE) to run and compile the JAVA programs. Also, we discussed the JAVA integration in SQL Server 2019 Machine Learning Services. SQL Server 2019 supports Python, R, and Java. Now, the support of Java language extension in SQL Server 2019 allows data scientists or data engineers to run the precompiled Java programs (JAR) and securely execute JAR code on SQL Server.

Note: SQL Server 2019 supports Java on both Windows and Linux platforms.

You can also refer to the following table to understand more about the Java Language extensions

• Author
• Recent Posts

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

• Stairway to SQL essentials - April 7, 2021
• A quick overview of database audit in SQL - January 28, 2021
• How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021