此文章发布于 60 个月前，部分信息可能已经过时，请自行斟酌确认。

1、什么是 SQL Server 语言扩展

语言扩展是 SQL Server 的一项功能，用于执行外部代码。 可以使用扩展性框架在外部代码中使用关系数据。

SQL Server 2019 支持 Java。 默认的 Java 运行时为 Zulu Open JRE。 此外，也可以使用其他 Java JRE 或 SDK。

2、使用 SQL Server 语言扩展可执行的操作

• 语言扩展使用扩展性框架来执行外部代码。 代码执行与核心引擎进程隔离，但与 SQL Server 查询执行完全集成。 它们使你可以在数据所在的位置执行代码，从而无需通过网络提取数据。
• 外部语言通过 CREATE EXTERNAL LANGUAGE 定义。
• 系统存储过程 sp_execute_external_script 用作执行代码的接口。

2.1、语言扩展可提供多种优势：

• 数据安全性：
  使外部语言执行更接近数据源，避免了浪费性的或不安全的数据移动。
• 速度：
  数据库针对基于集的操作进行了优化。 数据库方面的最新创新（如内存中表）可使汇总和聚合快速进行，并且是对数据科学的一种完美补充。
• 易于部署和集成：
  SQL Server 是许多其他数据管理任务和应用程序的操作中心点。 通过使用数据库中的数据，可以确保 Java 使用的数据最新且一致。

3、调用 Java 代码实战（Windows）

本教程以 Windwos 版 SQL Server 2019 为基础进行记录。

3.1、安装 SQL Server 语言扩展

运行 SQL Server 2019 安装程序，在安装选项卡上，选择全新 SQL Server 独立安装或向现有安装添加功能。

在功能选择 页上，选择数据库引擎服务和机器学习服务和语言扩展。之前已经安装了引擎服务的话选择已安装的实例。

需要注意：

• 使用语言扩展必须安装数据库引擎服务
• 如果要安装默认集成的 Java 运行时 Zulu Open JRE 11.0.3那么勾选 Java，如果要使用自己的 Java 运行时，那么不要勾选 Java。

3.2、配置 JRE 环境变量

上图中显示安装时勾选了 Java，所以会自动安装 AZUL-OpenJDK-JRE 到以下路径 C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\AZUL-OpenJDK-JRE\ 。

但微酷这里仍然使用了之前自己已经安装的 Open JDK 11，所以设置以下环境变量：

JRE_HOME：C:\Program Files\Java\jdk-11 （路径根据自己安装的修改）

SQL 运行 Java 时将尝试从路径 %JRE_HOME%\bin\server 加载 jvm.dll。

3.3、设置 JRE 目录权限

提示：仅在使用自已安装的 JDK 时才需要进行此步骤的操作，使用 SQL 内置 JRE 请跳过此步骤。

SQL 调用 JRE 时需要对目录有权限，通过以下命令设置目录权限（建议命令提示符而非 PowerShell）。

3.3.1、授予 SQLRUserGroup 权限

icacls "C:\Program Files\Java\jdk-11" /grant "SQLRUsergroup":(OI)(CI)RX /T

3.3.2、授予 AppContainer 权限

icacls "C:\Program Files\Java\jdk-11" /grant "ALL APPLICATION PACKAGES":(OI)(CI)RX /T

3.4、启用脚本执行

打开 SSMS，切换到需要运行 Java 代码的数据库，执行以下命令查看当前配置

sp_configure

属性 external scripts enabled 的值目前应为 0。 该功能默认处于关闭状态，必须在管理员显式启用后才能运行 Java 代码。

运行以下命令启用

EXEC sp_configure  'external scripts enabled', 1
RECONFIGURE WITH OVERRIDE

重启 SQL Server 服务，同时会自动重启相关的 SQL Server Launchpad 服务。

3.5、注册外部语言

对于想要在其中使用语言扩展的每个数据库，都需要注册外部语言。

以下示例将一种名为 Java 的外部语言添加到 Windows 上的 SQL Server 的数据库中。

其中的 zip 包中 javaextension.dll 。

create EXTERNAL LANGUAGE Java
FROM (
  CONTENT = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\java-lang-extension.zip', 
  FILE_NAME = 'javaextension.dll',
  ENVIRONMENT_VARIABLES = N'{"JRE_HOME":"C:\\Program Files\\Java\\jdk-11"}');

语法参考： CREATE EXTERNAL LANGUAGE。

3.6、创建测试数据库和表

CREATE DATABASE javatest
GO

USE javatest
GO

CREATE TABLE testdata (
    id int NOT NULL,
    "text" nvarchar(100) NOT NULL
)
GO

-- 插入测试数据
INSERT INTO testdata(id, "text") VALUES (1, 'This sentence contains java')
INSERT INTO testdata(id, "text") VALUES (2, 'This sentence does not')
INSERT INTO testdata(id, "text") VALUES (3, 'I love Java!')
GO

3.7、编写 Java 代码

创建名为 RegexSample.java 的类文件，并将以下 Java 代码复制到该文件中。

此类需要引入 mssql-java-lang-extension.jar 包，此 jar 包位于 C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn。

package pkg;

import com.microsoft.sqlserver.javalangextension.PrimitiveDataset;
import com.microsoft.sqlserver.javalangextension.AbstractSqlServerExtensionExecutor;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.ListIterator;
import java.util.regex.*;

public class RegexSample extends AbstractSqlServerExtensionExecutor {
    private Pattern expr;

    public RegexSample() {
        // Setup the expected extension version, and class to use for input and output dataset
        executorExtensionVersion = SQLSERVER_JAVA_LANG_EXTENSION_V1;
        executorInputDatasetClassName = PrimitiveDataset.class.getName();
        executorOutputDatasetClassName = PrimitiveDataset.class.getName();
    }
    
    public PrimitiveDataset execute(PrimitiveDataset input, LinkedHashMap<String, Object> params) {
        // Validate the input parameters and input column schema
        validateInput(input, params);

        int[] inIds = input.getIntColumn(0);
        String[] inValues = input.getStringColumn(1);
        int rowCount = inValues.length;

        String regexExpr = (String)params.get("regexExpr");
        expr = Pattern.compile(regexExpr);

        System.out.println("regex expression: " + regexExpr);

        // Lists to store the output data
        LinkedList<Integer> outIds = new LinkedList<Integer>();
        LinkedList<String> outValues = new LinkedList<String>();

        // Evaluate each row
        for(int i = 0; i < rowCount; i++) {
            if (check(inValues[i])) {
                outIds.add(inIds[i]);
                outValues.add(inValues[i]);
            }
        }

        int outputRowCount = outValues.size();

        int[] idOutputCol = new int[outputRowCount];
        String[] valueOutputCol = new String[outputRowCount];

        // Convert the list of output columns to arrays
        outValues.toArray(valueOutputCol);

        ListIterator<Integer> it = outIds.listIterator(0);
        int rowId = 0;

        System.out.println("Output data:");
        while (it.hasNext()) {
            idOutputCol[rowId] = it.next().intValue();

            System.out.println("ID: " + idOutputCol[rowId] + " Value: " + valueOutputCol[rowId]);
            rowId++;
        }

        // Construct the output dataset
        PrimitiveDataset output = new PrimitiveDataset();

        output.addColumnMetadata(0, "ID", java.sql.Types.INTEGER, 0, 0);
        output.addColumnMetadata(1, "Text", java.sql.Types.NVARCHAR, 0, 0);

        output.addIntColumn(0, idOutputCol, null);
        output.addStringColumn(1, valueOutputCol);

        return output;
    }

    private void validateInput(PrimitiveDataset input, LinkedHashMap<String, Object> params) {
        // Check for the regex expression input parameter
        if (params.get("regexExpr") == null) {
            throw new IllegalArgumentException("Input parameter 'regexExpr' is not found");
        }

        // The expected input schema should be at least 2 columns, (INTEGER, STRING)
        if (input.getColumnCount() < 2) {
            throw new IllegalArgumentException("Unexpected input schema, schema should be an (INTEGER, NVARCHAR or VARCHAR)");
        }

        // Check that the input column types are expected
        if (input.getColumnType(0) != java.sql.Types.INTEGER &&
                (input.getColumnType(1) != java.sql.Types.VARCHAR && input.getColumnType(1) == java.sql.Types.NVARCHAR )) {
            throw new IllegalArgumentException("Unexpected input schema, schema should be an (INTEGER, NVARCHAR or VARCHAR)");
        }
    }

    private boolean check(String text) {
        Matcher m = expr.matcher(text);

        return m.find();
    }
}

3.8、编译 jar 包

将类打包到 .jar 文件中。参考IDEA 生成 .jar 文件或如何从类文件创建 Java jar 文件 。并 将 .jar 文件命名为 regex.jar 。

3.9、创建外部语言

需要在数据库中创建外部语言。 外部语言是数据库范围内的对象，这意味着需要为要在其中使用外部语言（如 Java）的每个数据库创建外部语言。

3.10、创建外部库

使用 CREATE EXTERNAL LIBRARY 可为 .jar 文件创建外部库。 SQL Server 将有权访问 .jar 文件，而你无需对 classpath 设置任何特殊权限。

3.10.1、创建 SDK 的外部库

CREATE EXTERNAL LIBRARY sdk
FROM (CONTENT = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\mssql-java-lang-extension.jar')
WITH (LANGUAGE = 'Java');
go

3.10.2、创建 RegEx 代码的外部库

CREATE EXTERNAL LIBRARY regex
FROM (CONTENT = 'E:\\IDEA\\MSSQL\\jar\\regex.jar')
WITH (LANGUAGE = 'Java');

使用此方式不需要再为 jar 文件设置权限。

3.11、创建存储过程

创建一个存储过程，它调用 sp_execute_external_script 以从 SQL Server 调用 Java 代码。

在 script 参数中，定义要调用的 package.class。

在下面的代码中，类属于名为 pkg 的包和名为 RegexSample.java 的类文件。

代码未定义要调用的方法。 默认情况下，会调用 execute 方法。 这意味着，如果希望能够从 SQL Server 调用类，则需要遵循 SDK 接口并在 Java 类中实现 execute 方法。

存储过程采用输入查询（输入数据集）和正则表达式，并返回满足给定正则表达式的行。 它使用检查文本是否包含单词 Java 或 java 的正则表达式 [Jj]ava。

create PROCEDURE [dbo].[java_regex] 
  @expr nvarchar(200), 
  @query nvarchar(400)
AS
BEGIN

EXEC sp_execute_external_script
  @language = N'Java',
  @script = N'pkg.RegexSample',
  @input_data_1 = @query,
  @params = N'@regexExpr nvarchar(200)',
  @regexExpr = @expr
with result sets ((ID int, text nvarchar(100)));
END
go

3.12、调用 Java 类

执行以下 SQL 语句调用编写的 Java 类

EXECUTE [dbo].[java_regex] N'[Jj]ava', N'SELECT id, text FROM testdata'

执行调用之后，应获得包含两行的结果集。

完成。

4、遇到的问题

4.1、执行报错，找到不类

Error: Failed to find class pkg/RegexSample

可能的原因及解决方案：

在打包 jar 文件时我将依赖的 mssql-java-lang-extension.jar 也一起打包进 regex.jar 里面就会报这个错，只打包 RegexSample 类就没有这个问题了。

5、参考资料

• 微软官方文档
• 教程：正则表达式与 Java

6、实例脚本下载

本文脚本下载：java-in-sqlserver2019.zip