Skip to content

How to correctly insert with array type (ex bigint[]) columns? #346

@iamhook

Description

@iamhook

In my case, I have a class Product and table product:

@Entity(name = "product")
@Table(schema = "delivery", name = "product")
@Getter
@Setter
public class Product extends BaseEntity<Long> {

    @Column(name = "cost")
    private Double cost;

    @Column(name = "picture")
    private String picture;

    @Column(name = "categories")
    private List<Long> categories;

}
create table product
(
    id         bigserial                              not null
        constraint product_pk
            primary key,
    title      varchar                                not null,
    created    timestamp with time zone default now() not null,
    updated    timestamp with time zone,
    cost       double precision         default 0     not null,
    categories bigint[],
    picture    varchar
);

There is a problem with categories field.

I want to insert Product object to database.
Running

Product product = new Product();

        product.setTitle("Milk");
        product.setCost(100.);
        product.setCategories(Arrays.asList(10l, 20l));

        String query = "insert into delivery.product (title, cost, categories) " +
                "values (:title, :cost, :categories)";

        try (Connection con = sql2o.beginTransaction()) {
            con.createQuery(query)
                    .bind(product)
                    .executeUpdate();

            con.commit();
        }

I get org.sql2o.Sql2oException: Error in executeUpdate, ERROR: INSERT has more expressions than target columns, because the final query looks like insert into delivery.product (title, cost, categories) values (?, ?, ?,?) RETURNING *. One parameter of type List<Long> transformed to two positions in query!

The solution I found is to use

String query = "insert into delivery.product (title, cost, categories) " +
                "values (:title, :cost, :categories)";

        try (Connection con = sql2o.beginTransaction()) {
            Query q = con.createQuery(query)
                    .bind(product);

            Long[] longs = new Long[product.getCategories().size()];
            product.getCategories().toArray(longs);
            try {
                q.addParameter("categories", con.getJdbcConnection().createArrayOf("bigint", longs));
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            q.executeUpdate();

            con.commit();
        }

But it is not good idea, I think.

The main question is: can we make Qyery.bind() work with List/Array parameters when insert?

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions