How to use a PostgreSQL custom type as a nested POJO in jdbi?

I am trying to do something which I think should be relatively straight-forward with JDBI v3 (using the sql-object and postgres extensions).

I have an Item class:

public class Item {
    private String itemNumber;
    private Dimension3D dimensions;

    public Item(String itemNumber, Dimension3D dimensions) {
        this.itemNumber = itemNumber;
        this.dimensions = dimensions;
    }

    public String getItemNumber() {
        return itemNumber;
    }

    public Dimension3D getDimensions() {
        return dimensions;
    }
}

The Dimension3D class is:

public class Dimension3D {
    private BigDecimal width, length, height;
    private LengthUnit lengthUnit; // This is an enum, defaults to INCH.

    public Dimension3D(BigDecimal width, BigDecimal length, BigDecimal height) {
        this.width = width;
        this.length = length;
        this.height = height;
        this.unit = LengthUnit.INCH;
    }

    // Getters and setters.
}

I am using the following table schemas for items:

@SqlUpdate("""
        CREATE TABLE items  (
            item_number VARCHAR(7) NOT NULL PRIMARY KEY CHECK (item_number ~ '^[0-9]{6}[ab]?$'),
            dimensions dimension_3d NOT NULL DEFAULT (0, 0, 0, 'inch'),
        );
        """)
void createTable();

The dimension_3d type is created thusly:

@SqlScript("""
        CREATE TYPE spatial_unit AS ENUM ('inch', 'metric');
        
        CREATE TYPE dimension_3d AS (
            width numeric,
            length numeric,
            height numeric,
            unit spatial_unit
        );
        """)
void createTypes();

I am trying to make an insertItem method which takes an Item POJO:

@SqlUpdate("insert into items (item_number, dimensions) " +
        "values (:itemNumber, :dimensions)")
@RegisterColumnMapper(Dimension3DColumnMapper.class)
void insertItem(@BindBean Item item);

In order to try and make this work I have the above referenced Dimension3DColumnMapper (not sure that this is necessary):

public class Dimension3DColumnMapper implements ColumnMapper<Dimension3D> {
    @Override
    public Dimension3D map(ResultSet r, int columnNumber, StatementContext ctx) throws SQLException {
        return new Dimension3D(r.getBigDecimal("length"), r.getBigDecimal("width"), r.getBigDecimal("height"), LengthUnit.INCH);
    }
}

Finally I call these methods thusly:

@Test
public void testCreateTables(Jdbi jdbi) {
    jdbi.installPlugin(new SqlObjectPlugin());
    jdbi.installPlugin(new PostgresPlugin());
    jdbi.withHandle(handle -> {
        handle.registerArgument(new Dimension3DArgumentFactory());
        handle.configure(PostgresTypes.class, pt -> pt.registerCustomType(Dimension3DPGType.class, "dimension_3d"));
        handle.attach(ItemQueries.class).createTypes();
        handle.attach(ItemQueries.class).insertItem(new Item("00001", new Dimension3D(new BigDecimal("1"), new BigDecimal("1"), new BigDecimal("1"), LengthUnit.INCH));
    });
}

My attempt to make this work seamlessly has led me to register a custom ArgumentFactory defined thusly:

public class Dimension3DArgumentFactory extends AbstractArgumentFactory<Dimension3D> {

    public Dimension3DArgumentFactory() {
        super(Types.NUMERIC);
    }

    @Override
    protected Argument build(Dimension3D value, ConfigRegistry config) {
        return (position, statement, ctx) -> {
            statement.setBigDecimal(position, value.getLength());
            statement.setBigDecimal(position + 1, value.getWidth());
            statement.setBigDecimal(position + 2, value.getHeight());
        };
    }
}

I also tried to register the dimension_3d custom type (as seen above) which is:

public class Dimension3DPGType extends PGobject {
    private Dimension3D dimension3D;

    public Dimension3DPGType(BigDecimal length, BigDecimal width, BigDecimal height) {
        this();
        this.dimension3D = new Dimension3D(length, width, height, LengthUnit.INCH);
    }

    public Dimension3DPGType() {
        setType("dimension_3d");
    }

    public BigDecimal getWidth() {
        return dimension3D.getWidth();
    }

    public void setWidth(BigDecimal width) {
        dimension3D = new Dimension3D(width, dimension3D.getLength(), dimension3D.getHeight(), LengthUnit.INCH);
    }

    public BigDecimal getLength() {
        return dimension3D.getLength();
    }

    public void setLength(BigDecimal length) {
        dimension3D = new Dimension3D(getWidth(), length, dimension3D.getHeight(), LengthUnit.INCH);
    }

    public BigDecimal getHeight() {
        return dimension3D.getHeight();
    }

    public void setHeight(BigDecimal height) {
        dimension3D = new Dimension3D(dimension3D.getWidth(), dimension3D.getLength(), height, LengthUnit.INCH);
    }

    @Override
    public String getValue() {
        return "(" + getWidth() + "," + getLength() + "," + getHeight() + ")";
    }

    @Override
    public void setValue(String value) {
        PGtokenizer t = new PGtokenizer(PGtokenizer.removePara(value), ',');

        BigDecimal length = new BigDecimal(t.getToken(0));
        BigDecimal width = new BigDecimal(t.getToken(1));
        BigDecimal height = new BigDecimal(t.getToken(2));
        dimension3D = new Dimension3D(length, width, height, LengthUnit.INCH);
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) {
            return true;
        }
        if (!(o instanceof Dimension3DPGType that)) {
            return false;
        }
        if (!super.equals(o)) {
            return false;
        }
        return Objects.equals(dimension3D, that.dimension3D);
    }

    @Override
    public int hashCode() {
        return dimension3D.hashCode();
    }
}

I have tried this with/without the ColumnMapper, ArgumentFactory, and custom type registration.

The current exception I'm facing is:

[ERROR]   DatabaseTest.testCreateTables:21->lambda$testCreateTables$1:29 » UnableToExecuteStatement org.postgresql.util.PSQLException: ERROR: column "dimensions" is of type dimension_3d but expression is of type numeric
  Hint: You will need to rewrite or cast the expression.

Attempting to correct this with using casts like below:

@SqlUpdate("insert into items (item_number, dimensions) " +
        "values (:itemNumber, CAST(:dimensions AS dimension_3d))")
@RegisterColumnMapper(Dimension3DColumnMapper.class)
void insertItem(@BindBean Item item);

then produces the exception:

DatabaseTest.testCreateTables:21->lambda$testCreateTables$1:29 » UnableToExecuteStatement org.postgresql.util.PSQLException: ERROR: cannot cast type numeric to dimension_3d

For reference I am using the following dependency versions:

<dependency>
    <groupId>org.jdbi</groupId>
    <artifactId>jdbi3-core</artifactId>
    <version>3.47.0</version>
</dependency>
<dependency>
    <groupId>org.jdbi</groupId>
    <artifactId>jdbi3-sqlobject</artifactId>
    <version>3.47.0</version>
</dependency>
<dependency>
    <groupId>org.jdbi</groupId>
    <artifactId>jdbi3-postgres</artifactId>
    <version>3.47.0</version>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.4</version>
</dependency>
<dependency>
    <groupId>org.jdbi</groupId>
    <artifactId>jdbi3-testing</artifactId>
    <version>3.47.0</version>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>de.softwareforge.testing</groupId>
    <artifactId>pg-embedded</artifactId>
    <version>5.2.0</version>
    <scope>test</scope>
</dependency>

Thanks very much for any assistance!

Update:

I tried the following:

package com.dow.inventory.db;

import com.dow.items.Dimension3D;
import org.jdbi.v3.core.argument.AbstractArgumentFactory;
import org.jdbi.v3.core.argument.Argument;
import org.jdbi.v3.core.config.ConfigRegistry;

import java.sql.Types;

public class Dimension3DArgumentFactory extends AbstractArgumentFactory<Dimension3D> {

    public Dimension3DArgumentFactory() {
        super(Types.OTHER);
    }

    @Override
    protected Argument build(Dimension3D value, ConfigRegistry config) {
        final Dimension3DPGType dimension3d = new Dimension3DPGType(value);
        return (i, p, cx) -> p.setObject(i, dimension3d, Types.OTHER);
    }
}

but this leads to:

UnableToExecuteStatement org.postgresql.util.PSQLException: ERROR: malformed record literal: "(10.75,8.5,7.25)"

This String is coming from:

@Override
public String getValue() {
    return "(" + getWidth() + "," + getLength() + "," + getHeight() + ")";
}

Don't know what the correct format is for this to work...

Update 2:

I think I'm getting closer with:

@Override
public String getValue() {
    return "(ROW(" + getWidth() + "," + getLength() + "," + getHeight() + "))";
}

because this then leads to the exception for trying to "deserialize":

org.jdbi.v3.core.statement.UnableToExecuteStatementException: org.postgresql.util.PSQLException: ERROR: invalid input syntax for type numeric: "ROW(10.75"

Don't know where this is coming from though because I use the following substring to set the value:

@Override
public void setValue(String value) {
    String[] parts = value.substring(5, value.length() - 2).split(",");

    BigDecimal width = new BigDecimal(parts[0]);
    BigDecimal length = new BigDecimal(parts[1]);
    BigDecimal height = new BigDecimal(parts[2]);

    dimension3D = new Dimension3D(width, length, height, LengthUnit.INCH);
}

I don't know why using just parentheses leads to a malformed record literal ERROR: malformed record literal: "(10.75,8.5,7.25)" as that it what is done here.

Update 3:

Seems like not including the length_unit doesn't work, this is now super close:

    @Override
    public String getValue() {
        return "(" + getWidth() + "," + getLength() + "," + getHeight() + ",'inch')";
    }

but this yields:

UnableToExecuteStatement org.postgresql.util.PSQLException: ERROR: invalid input value for enum spatial_unit: "'inch'"

I have figured this out.

Here's the PGObject implementation that makes this work:

package com.dow.inventory.db;

import com.dow.items.Dimension3D;
import com.dow.items.LengthUnit;
import org.postgresql.util.PGobject;
import org.postgresql.util.PGtokenizer;

import java.math.BigDecimal;
import java.util.Objects;

public class Dimension3DPGType extends PGobject {
    private Dimension3D dimension3D;

    public Dimension3DPGType(BigDecimal length, BigDecimal width, BigDecimal height) {
        this();
        this.dimension3D = new Dimension3D(length, width, height, LengthUnit.INCH);
    }

    public Dimension3DPGType(Dimension3D dimension3D) {
        this();
        this.dimension3D = dimension3D;
    }

    public Dimension3DPGType(String value) {
        setValue(value);
    }

    public Dimension3DPGType() {
        setType("dimension_3d");
    }

    public BigDecimal getWidth() {
        return dimension3D.getWidth();
    }

    public void setWidth(BigDecimal width) {
        dimension3D = new Dimension3D(width, dimension3D.getLength(), dimension3D.getHeight(), LengthUnit.INCH);
    }

    public BigDecimal getLength() {
        return dimension3D.getLength();
    }

    public void setLength(BigDecimal length) {
        dimension3D = new Dimension3D(getWidth(), length, dimension3D.getHeight(), LengthUnit.INCH);
    }

    public BigDecimal getHeight() {
        return dimension3D.getHeight();
    }

    public void setHeight(BigDecimal height) {
        dimension3D = new Dimension3D(dimension3D.getWidth(), dimension3D.getLength(), height, LengthUnit.INCH);
    }

    @Override
    public String getValue() {
        return "(" + getWidth() + "," + getLength() + "," + getHeight() + ",\"inch\")";
    }

    @Override
    public void setValue(String value) {
        String[] parts = value.substring(1, value.length() - 1).split(",");

        BigDecimal width = new BigDecimal(parts[0]);
        BigDecimal length = new BigDecimal(parts[1]);
        BigDecimal height = new BigDecimal(parts[2]);

        dimension3D = new Dimension3D(width, length, height, LengthUnit.INCH);
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) {
            return true;
        }
        if (!(o instanceof Dimension3DPGType that)) {
            return false;
        }
        if (!super.equals(o)) {
            return false;
        }
        return Objects.equals(dimension3D, that.dimension3D);
    }

    @Override
    public int hashCode() {
        return dimension3D.hashCode();
    }
}

With the following argument factory:

public class Dimension3DArgumentFactory extends AbstractArgumentFactory<Dimension3D> {

    public Dimension3DArgumentFactory() {
        super(Types.OTHER);
    }

    @Override
    protected Argument build(Dimension3D value, ConfigRegistry config) {
        final Dimension3DPGType dimension3d = new Dimension3DPGType(value);
        return (i, p, cx) -> p.setObject(i, dimension3d, Types.OTHER);
    }
}

We then register this custom type on the handle:

handle.configure(PostgresTypes.class, pt -> pt.registerCustomType(Dimension3DPGType.class, "dimension_3d"));

and our ItemQuery is successful:

@SqlUpdate("insert into items (item_number, dimensions) " +
        "values (:itemNumber, :dimensions)")
@RegisterArgumentFactory(Dimension3DArgumentFactory.class)
void insertItem(@BindBean Item item);

What's really cool is it seems like our default enum value trick does work because we can do the following:

@Override
public String getValue() {
    return "(" + getWidth() + "," + getLength() + "," + getHeight() + ",)";
}

and it will automatically set the value to inch.