#UDT数据处理最佳实践

# 适用范围

本文适用于Java应用通过YashanDB JDBC驱动读写用户自定义类型（UDT，User-Defined Type）的场景，演示SQLData、SQLInput、SQLOutput和Struct接口的组合使用。

UDT功能不适用于存算一体分布式集群部署。示例验证环境如下：

组件	版本
JDK	17.0.19
YashanDB JDBC驱动	1.10.11
YashanDB Server	23.4.10.100

# 预期效果

完成本实践后，应用应具备以下能力：

定义UDT类型和包含UDT列的业务表。
使用SQLData映射UDT对象。
按属性顺序读写UDT数据。
使用Struct访问UDT。
类型变更后同步更新映射。

# 实施步骤

# 1. 创建UDT和业务表

由部署账号执行以下语句。类型属性的名称、顺序和数据类型构成Java映射契约：

CREATE TYPE inventory_item_type AS OBJECT (
    item_code VARCHAR(32),
    quantity INTEGER
);

CREATE TABLE inventory_udt_item (
    item_id INTEGER PRIMARY KEY,
    item_value inventory_item_type
);

业务账号需要inventory_udt_item表的SELECT和INSERT权限，并需要使用inventory_item_type类型的权限。对象授权方式应按数据库安全管理规范执行，不使用高权限账号运行应用。

# 2. 选择访问方式和读写UDT属性顺序

方式	适用场景	关键接口
Java对象映射	应用希望使用强类型业务对象	SQLData、SQLInput、SQLOutput、类型映射表
通用属性访问	中间件或工具无法预先定义业务类	Struct、Connection.createStruct()、getAttributes()

SQLData映射类必须提供公共无参构造方法。getSQLTypeName()返回SCHEMA.TYPE_NAME全限定名称；名称大小写应与驱动元数据返回的标识保持一致。

UDT属性读写必须严格按数据库定义的顺序进行。例如inventory_item_type(item_code, quantity)：

public void readSQL(SQLInput input, String typeName) throws SQLException {
    itemCode = input.readString();
    quantity = input.readInt();
}

public void writeSQL(SQLOutput output) throws SQLException {
    output.writeString(itemCode);
    output.writeInt(quantity);
}

读取和写入顺序不能按Java字段名称自动匹配。数据库类型增加、删除或调整属性顺序后，必须同步修改映射类并重新验证存量数据读取、参数绑定和空值行为。

# 3. 执行UDT完整示例

以下程序为便于独立验证，创建jdbc_bp_item_type和jdbc_bp_udt_item临时对象。程序分别通过SQLData和Struct插入一行，再用对应方式读取，最后只删除本次创建的对象。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLData;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Struct;
import java.util.HashMap;
import java.util.Locale;
import java.util.Map;

public class UdtBestPracticeExample {
    private static final String TYPE = "jdbc_bp_item_type";
    private static final String TABLE = "jdbc_bp_udt_item";

    public static void main(String[] args) throws Exception {
        boolean typeCreated = false;
        boolean tableCreated = false;
        try (Connection connection = open()) {
            String fullType = connection.getMetaData().getUserName()
                    .toUpperCase(Locale.ROOT) + "."
                    + TYPE.toUpperCase(Locale.ROOT);
            ItemValue.setTypeName(fullType);

            try (Statement statement = connection.createStatement()) {
                statement.execute("CREATE TYPE " + TYPE
                        + " AS OBJECT (item_code VARCHAR(32), "
                        + "quantity INTEGER)");
                typeCreated = true;
                statement.execute("CREATE TABLE " + TABLE
                        + " (item_id INTEGER PRIMARY KEY, item_value "
                        + TYPE + ")");
                tableCreated = true;
            }

            insertSqlData(connection, new ItemValue("ITEM_A", 12), 1001);
            insertStruct(connection, fullType, 1002, "ITEM_B", 18);
            readSqlData(connection, fullType, 1001);
            readStruct(connection, 1002);
        } finally {
            if (tableCreated) {
                execute("DROP TABLE " + TABLE);
            }
            if (typeCreated) {
                execute("DROP TYPE " + TYPE);
            }
        }
    }

    private static void insertSqlData(
            Connection connection, ItemValue value, int id)
            throws SQLException {
        try (PreparedStatement statement = connection.prepareStatement(
                "INSERT INTO " + TABLE + " VALUES (?, ?)")) {
            statement.setInt(1, id);
            statement.setObject(2, value);
            statement.executeUpdate();
        }
    }

    private static void insertStruct(
            Connection connection, String fullType, int id,
            String code, int quantity) throws SQLException {
        Struct value = connection.createStruct(
                fullType, new Object[] {code, quantity});
        try (PreparedStatement statement = connection.prepareStatement(
                "INSERT INTO " + TABLE + " VALUES (?, ?)")) {
            statement.setInt(1, id);
            statement.setObject(2, value);
            statement.executeUpdate();
        }
    }

    private static void readSqlData(
            Connection connection, String fullType, int id)
            throws SQLException {
        Map<String, Class<?>> typeMap = new HashMap<>();
        typeMap.put(fullType, ItemValue.class);
        try (PreparedStatement statement = connection.prepareStatement(
                "SELECT item_value FROM " + TABLE
                        + " WHERE item_id = ?")) {
            statement.setInt(1, id);
            try (ResultSet resultSet = statement.executeQuery()) {
                resultSet.next();
                ItemValue value = (ItemValue) resultSet.getObject(
                        1, typeMap);
                System.out.println("SQLData: " + value.itemCode
                        + ", quantity=" + value.quantity);
            }
        }
    }

    private static void readStruct(Connection connection, int id)
            throws SQLException {
        try (PreparedStatement statement = connection.prepareStatement(
                "SELECT item_value FROM " + TABLE
                        + " WHERE item_id = ?")) {
            statement.setInt(1, id);
            try (ResultSet resultSet = statement.executeQuery()) {
                resultSet.next();
                Struct value = (Struct) resultSet.getObject(1);
                Object[] attributes = value.getAttributes();
                System.out.println("Struct: " + attributes[0]
                        + ", quantity=" + attributes[1]);
            }
        }
    }

    public static class ItemValue implements SQLData {
        private static String sqlTypeName;
        private String itemCode;
        private int quantity;

        public ItemValue() {
        }

        ItemValue(String itemCode, int quantity) {
            this.itemCode = itemCode;
            this.quantity = quantity;
        }

        static void setTypeName(String value) {
            sqlTypeName = value;
        }

        @Override
        public String getSQLTypeName() {
            return sqlTypeName;
        }

        @Override
        public void readSQL(SQLInput input, String typeName)
                throws SQLException {
            sqlTypeName = typeName;
            itemCode = input.readString();
            quantity = input.readInt();
        }

        @Override
        public void writeSQL(SQLOutput output) throws SQLException {
            output.writeString(itemCode);
            output.writeInt(quantity);
        }
    }

    private static Connection open() throws SQLException {
        return DriverManager.getConnection(required("YASDB_URL"),
                required("YASDB_USER"), required("YASDB_PASSWORD"));
    }

    private static void execute(String sql) throws SQLException {
        try (Connection connection = open();
             Statement statement = connection.createStatement()) {
            statement.execute(sql);
        }
    }

    private static String required(String name) {
        String value = System.getenv(name);
        if (value == null || value.isEmpty()) {
            throw new IllegalStateException(
                    "Missing environment variable: " + name);
        }
        return value;
    }
}

# 4. 验证结果

示例运行成功时，输出如下：

SQLData: ITEM_A, quantity=12
Struct: ITEM_B, quantity=18