java - How do I insert Array of PostgreSQL Composite Type using SpringBoot's JdbcClient - Stack Overflow

admin管理员组

文章数量:1222089

I'm trying to store an Array of a composite type that has no relations, Just data that I want to store and reuse during runtime hence why I'm not using multiple rows to store this data.

shop_item PostgreSQL Comspoite Type:

CREATE TYPE shop_item AS (
id                    VARCHAR(255),
quantity              INTEGER,
quantity_multiplier   INTEGER,
price                 DECIMAL(4, 2)
);

shop_orders PostgreSQL Table:

CREATE TABLE IF NOT EXISTS shop_orders (
guild_id     BIGINT        NOT NULL,
user_id      BIGINT        NOT NULL,
shop_items   shop_item[]   NOT NULL,

PRIMARY KEY (guild_id, user_id),
FOREIGN KEY (guild_id) REFERENCES guild_config (guild_id)
ON DELETE CASCADE
);

Java SpringBoot DAO Method & Query:

@Override
public void upsert(long guildId, @NotNull ShopOrder shopOrder) {
  jdbc.sql("""
           INSERT INTO shop_orders (guild_id, user_id, shop_items)
           VALUES (?, ?, ?::shop_item[])
           ON CONFLICT (guild_id, user_id)
           DO UPDATE SET shop_items = EXCLUDED.shop_items;
           """)
      .param(1, guildId)
      .param(2, shopOrder.getUserId())
      .param(3, shopOrder.getShopItems())
      .update();
}

shopOrder.getShopItems() is of type List<ShopItem>.

ShopItem.class POJO:

public class ShopItem {
private String id;
private int quantity;
private int quantityMultiplier;
private double price;
}

The issue I believe is that JdbcClient can't map the List of ShopItem(s) to the one declared as shop_item in the database, But I tried type casting, using ARRAY[?]::shop_item[] as parameter, it always produces errors such as "Can't Cast To Types.ARRAY" or Invalid Elements

本文标签： javaHow do I insert Array of PostgreSQL Composite Type using SpringBoot39s JdbcClientStack Overflow