java - PostgreSQL 映射 UDT

Question

我想知道PostgreSQL的JDBC驱动42.2.8是否支持UDT映射？

我在 SQLData 实现上苦苦挣扎了一段时间，但遇到了错误:

org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of persistent.udt.fiscal.Partner. Use setObject() with an explicit Types value to specify the type to use.
    at org.postgresql.jdbc@42.2.8/org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:955)
    at commons.dbcp2@2.5.0/org.apache.commons.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:519)
    at commons.dbcp2@2.5.0/org.apache.commons.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:519)

CREATE TYPE fiscal.partner AS
(
    type eik_type,
    eik character varying(20),
    vat character varying(20),
    name character varying(255),
    alternative_name character varying(255),
    city character varying(255),
    address text,
    contact character varying(255),
    description text,
    bank fiscal.bank
);

CREATE TABLE fiscal.sales
(
    id integer NOT NULL DEFAULT nextval('fiscal.sales_id_seq'::regclass),
    date timestamp without time zone NOT NULL DEFAULT now(),
    transaction_id integer NOT NULL,
    transaction_date timestamp without time zone NOT NULL,
    discount_type discount_type NOT NULL,
    discount numeric(10,2) NOT NULL,
    amount_discount numeric(10,2) NOT NULL,
    amount_total numeric(10,2) NOT NULL,
    amount_tax numeric(10,2) NOT NULL,
    supplier_partner fiscal.partner NOT NULL,
    supplier_workplace fiscal.workplace NOT NULL,
    by_user_id character(4) COLLATE pg_catalog."default" NOT NULL DEFAULT current_setting('app.user_id'::text),
    by_first_name character varying(255) COLLATE pg_catalog."default" NOT NULL DEFAULT current_setting('app.user_first_name'::text),
    by_sur_name character varying(255) COLLATE pg_catalog."default" NOT NULL DEFAULT current_setting('app.user_sur_name'::text),
    by_last_name character varying(255) COLLATE pg_catalog."default" NOT NULL DEFAULT current_setting('app.user_last_name'::text),
    CONSTRAINT sales_pkey PRIMARY KEY (id, date),
    CONSTRAINT sales_transaction_id_transaction_date_fkey FOREIGN KEY (transaction_date, transaction_id)
        REFERENCES fiscal.transactions (date, id) MATCH SIMPLE
        ON UPDATE RESTRICT
        ON DELETE RESTRICT,
    CONSTRAINT sales_transaction_id_transaction_date_fkey1 FOREIGN KEY (transaction_date, transaction_id)
        REFERENCES fiscal.transactions_default (date, id) MATCH SIMPLE
        ON UPDATE RESTRICT
        ON DELETE RESTRICT,
    CONSTRAINT sales_by_user_id_check CHECK (by_user_id ~ '[0-9A-Z]{4}'::text)
) PARTITION BY RANGE (date) ;

public class Partner implements SQLData {
    public static final String TYPE = "fiscal.partner";

    private String type;
    private String eik;
    private String vat;
    private String name;
    private String alternativeName;
    private String city;
    private String address;
    private String contact;
    private String description;
    private Bank bank;

    public Partner() {
    }

    @Override
    public String getSQLTypeName() throws SQLException {
        return TYPE;
    }

    @Override
    public void readSQL(SQLInput stream, String typeName) throws SQLException {
        ....
    }

    @Override
    public void writeSQL(SQLOutput stream) throws SQLException {
        ....
    }
}

public boolean open(Location location, DiscountType discountType, BigDecimal discount, BigDecimal amountDiscount, BigDecimal amountTotal, BigDecimal amountTax) throws SQLException {
    String query =
            "insert into fiscal.sales (transaction_id, transaction_date, discount_type, discount, amount_discount, amount_total, amount_tax, supplier_partner, supplier_workplace) " +
                    "values (?, ?, ?::public.discount_type, ?, ?, ?, ?, ?::fiscal.partner, ?) " +
                    "returning id, date";

    Map<String, Class<?>> map = new HashMap<>();
    map.put(Partner.TYPE, Partner.class);

    connection.setTypeMap(map);

    try(PreparedStatement statement = connection.prepareStatement(query)) {
        int index = 1;
        statement.setLong(index++, transaction_id);
        statement.setTimestamp(index++, Timestamp.valueOf(transaction_date));
        statement.setString(index++, discountType.name());
        statement.setBigDecimal(index++, discount);
        statement.setBigDecimal(index++, amountDiscount);
        statement.setBigDecimal(index++, amountTotal);
        statement.setBigDecimal(index++, amountTax);

        statement.setObject(index++, new Partner());
        statement.setObject(index++, new Workplace());

        try(ResultSet rs = statement.executeQuery()) {
            if(rs.next()) {
                sale_id = rs.getLong("id");
                sale_date = rs.getTimestamp("date").toLocalDateTime();

                return true;
            }

            return false;
        }
    }
}

Answer 1

这不会自动起作用；您必须自己将数据“序列化”为 PostgreSQL 复合类型的字符串表示形式，例如:

statement.setString(index++, "(42,string,\"string,with,comma\",,-1)");

在此示例中，类型由一个数字、两个字符串、一个 NULL 值和另一个数字组成。

如果您不需要它用于其他目的，您可以重写此类的 toString 方法来生成此表示形式。这会让事情变得简单。