本章节介绍如何通过JDBC存取用户自定义类型（UDT，User-Defined Type）数据。UDT相关功能不适用于存算一体分布式集群部署。

YashanDB JDBC驱动支持通过标准JDBC对象访问UDT数据：

• java.sql.Struct用于访问数据库Object类型的属性。
• java.sql.Array用于访问数据库集合类型或数组类型的数据。
• java.sql.SQLData用于将数据库Object类型映射为自定义Java类。

详细接口方法和支持范围请参考UDT相关接口。

使用Struct读写Object类型

使用Connection.createStruct创建Struct对象，使用PreparedStatement.setObject绑定；查询后可通过ResultSet.getObject获取Struct并读取属性。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Struct;
import java.util.Properties;

public class StructExample {
    private static final String URL =
            "jdbc:yasdb://192.168.1.2:1688/yasdb";

    public static void main(String[] args) throws Exception {
        Properties properties = new Properties();
        properties.setProperty("user", "username");
        properties.setProperty("password", "passwd");
        Class.forName("com.yashandb.jdbc.Driver");

        try (Connection connection =
                     DriverManager.getConnection(URL, properties);
             Statement statement = connection.createStatement()) {
            statement.execute("DROP TABLE IF EXISTS customer_address");
            statement.execute(
                    "CREATE OR REPLACE TYPE address_type AS OBJECT "
                            + "(city VARCHAR(30), district VARCHAR(30))");
            statement.execute(
                    "CREATE TABLE customer_address "
                            + "(customer_id INTEGER PRIMARY KEY, "
                            + "address ADDRESS_TYPE)");
            writeAndRead(connection);
        }
    }

    public static void writeAndRead(Connection connection) throws SQLException {
        // ADDRESS_TYPE为数据库中已创建的Object类型名称。
        Struct address = connection.createStruct(
                "ADDRESS_TYPE", new Object[]{"Shenzhen", "Nanshan"});

        try (PreparedStatement statement = connection.prepareStatement(
                "INSERT INTO customer_address (customer_id, address) VALUES (?, ?)")) {
            statement.setInt(1, 1);
            statement.setObject(2, address);
            statement.executeUpdate();
        }

        try (PreparedStatement statement = connection.prepareStatement(
                "SELECT address FROM customer_address WHERE customer_id = ?")) {
            statement.setInt(1, 1);
            try (ResultSet resultSet = statement.executeQuery()) {
                if (resultSet.next()) {
                    Struct value = (Struct) resultSet.getObject(1);
                    Object[] attributes = value.getAttributes();
                    System.out.println(attributes[0]);
                }
            }
        }
    }
}

正常情况下，程序输出如下：

Shenzhen

使用Array读写集合类型

使用Connection.createArrayOf创建数组对象，使用PreparedStatement.setArray绑定；查询后通过ResultSet.getArray获取数组内容。

import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ArrayExample {
    public static void writeAndRead(Connection connection) throws SQLException {
        Array numbers = connection.createArrayOf("INTEGER",
                new Object[]{10, 20, 30});

        try (PreparedStatement statement = connection.prepareStatement(
                "INSERT INTO customer_score (customer_id, scores) VALUES (?, ?)")) {
            statement.setInt(1, 1);
            statement.setArray(2, numbers);
            statement.executeUpdate();
        }

        try (PreparedStatement statement = connection.prepareStatement(
                "SELECT scores FROM customer_score WHERE customer_id = ?")) {
            statement.setInt(1, 1);
            try (ResultSet resultSet = statement.executeQuery()) {
                if (resultSet.next()) {
                    Array value = resultSet.getArray(1);
                    Object[] values = (Object[]) value.getArray();
                    System.out.println(values.length);
                }
            }
        }
    }
}

Note：

示例中的ADDRESS_TYPE、customer_address和customer_score为业务对象示例名称，实际使用时应替换为数据库中已定义的Object类型、表和列名称。创建数据库UDT的语法请参考YashanDB数据库管理文档。