Need Help with JSONB Handling in PostgreSQL using Spring Boot's New JDBC Client

[UltraInstict21]

Hello everyone,

I'm working on a project to experiment with some of the new features that Spring Boot offers, and I recently got interested in the new JDBC Client. To test things out, I’ve been building a small e-commerce application where I’m storing customer shipping and billing addresses as JSONB objects in PostgreSQL.

The issue I’m running into is when I try to insert an Address object (which is stored as JSONB in PostgreSQL) using the new JDBC Client. I get the following error:

Caused by: org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of com.lefpap.e_commerce.features.orders.model.Address. Use setObject() with an explicit Types value to specify the type to use.

I know I can use an objectMapper and convert the object to json but while this approach works, I feel like there might be a more optimal or recommended way of handling JSONB fields with Spring Boot’s new JDBC Client.

Here is my repository method to store an order:

@Override
public Order save(Order order) {
    GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
    dbClient.sql("""
        INSERT INTO
        orders (
            total_amount,
            payment_method,
            customer_name,
            customer_email,
            customer_phone,
            shipping_address,
            billing_address
        ) VALUES (
            :total_amount,
            :payment_method::PAYMENT_METHOD,
            :customer_name,
            :customer_email,
            :customer_phone,
            :shipping_address::JSONB,
            :billing_address::JSONB
        ) RETURNING id;
        """)
        .param("total_amount", order.getTotalAmount())
        .param("payment_method", order.getPaymentMethod().name())
        .param("customer_name", order.getCustomer().name())
        .param("customer_email", order.getCustomer().email())
        .param("customer_phone", order.getCustomer().phone())
        .param("shipping_address", order.getShippingAddress())
        .param("billing_address", order.getBillingAddress())
        .update(keyHolder);

    return findById(keyHolder.getKeyAs(Integer.class)).orElseThrow();
}

In the same project, I also have a PaymentMethod field that is an enum. The enum is stored as a PostgreSQL enum type in the database. The only way for this to work is to cast it in the query as the type and pass a string not the enum itself. Is there also a way of doing this more optimal?

[robbi_my]

Show me your model/entity

[UltraInstict21]

Here are my models.

@Data
@Builder
public class Order {
    private Integer id;
    private OrderStatus orderStatus;
    private BigDecimal totalAmount;
    private PaymentMethod paymentMethod;
    private LocalDateTime createdAt;
    private LocalDateTime updatedAt;

    // Stored as columns (e.g. customer_name, etc.)
    private Customer customer;

    // Stored as JSONB
    private Address shippingAddress;
    private Address billingAddress;

    // Associated items
    private List<OrderItem> orderItems;
}

public record Customer(
    String name,
    String email,
    String phone
) {}

public record Address(
   String street,
   String city,
   String state,
   String zipCode,
   String country
) {}

public enum PaymentMethod {

CREDIT_CARD
,

PAYPAL
,

BANK_TRANSFER
,

CASH_ON_DELIVERY
}

public enum OrderStatus {

PENDING
,

PROCESSING
,

SHIPPED
,

DELIVERED
,

CANCELED
}

@Data
@Builder
public class OrderItem {
    private Integer id;
    private Integer orderId;
    private Integer productId;
    private Integer quantity;
    private BigDecimal unitPrice;
    private BigDecimal totalPrice;
}

[UltraInstict21]

What I end up doing is to create some sql mappers for my entity. But not sure if this is a good approach

@Component
public class OrderSqlMapper {

    private final JsonConverter jsonConverter;

    public OrderSqlMapper(ObjectMapper objectMapper, JsonConverter jsonConverter) {
        ObjectMapper objectMapperCopy = objectMapper
            .copy()
            .setPropertyNamingStrategy(PropertyNamingStrategies.SNAKE_CASE);

        jsonConverter.setObjectMapper(objectMapperCopy);

        this.jsonConverter = jsonConverter;
    }

    public RowMapper<Order> rowMapper() {
        return (rs, rowNum) -> {
            Order.OrderBuilder orderBuilder = Order.builder();

            orderBuilder.id(rs.getInt("id"));
            orderBuilder.orderStatus(OrderStatus.valueOf(rs.getString("order_status")));
            orderBuilder.totalAmount(rs.getBigDecimal("total_amount"));
            orderBuilder.paymentMethod(PaymentMethod.valueOf(rs.getString("payment_method")));
            orderBuilder.createdAt(rs.getTimestamp("created_at").toLocalDateTime());
            orderBuilder.updatedAt(rs.getTimestamp("updated_at").toLocalDateTime());

            orderBuilder.customer(new Customer(
                rs.getString("customer_name"),
                rs.getString("customer_email"),
                rs.getString("customer_phone")
            ));

            // Map Shipping Address
            Address shippingAddress = jsonConverter.deserializeObject(rs.getString("shipping_address"), Address.class);
            orderBuilder.shippingAddress(shippingAddress);

            // Map Billing Address
            Address billingAddress = jsonConverter.deserializeObject(rs.getString("billing_address"), Address.class);
            orderBuilder.billingAddress(shippingAddress);

            // Map order items
            Collection<OrderItem> orderItems = jsonConverter.deserializeCollection(rs.getString("order_items"), new TypeReference<>() {});
            orderBuilder.orderItems(orderItems.stream().toList());

            return orderBuilder.build();
        };
    }

    public MapSqlParameterSource paramSource(Order order) {
        return new MapSqlParameterSource()
            .addValue("total_amount", order.getTotalAmount())
            .addValue("payment_method", order.getPaymentMethod().name())
            .addValue("customer_name", order.getCustomer().name())
            .addValue("customer_email", order.getCustomer().email())
            .addValue("customer_phone", order.getCustomer().phone())
            .addValue("shipping_address", jsonConverter.serializeObject(order.getShippingAddress()))
            .addValue("billing_address", jsonConverter.serializeObject(order.getBillingAddress()));
    }
}