Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
iamhook opened this issue Jan 8, 2021 · 0 comments
Open

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

iamhook opened this issue Jan 8, 2021 · 0 comments

Comments

@iamhook
Copy link

iamhook commented Jan 8, 2021

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?

@iamhook iamhook changed the title To to correctly insert with array (ex bigint[]) columns? How to correctly insert with array (ex bigint[]) columns? Jan 8, 2021
@iamhook iamhook changed the title How to correctly insert with array (ex bigint[]) columns? How to correctly insert with array type (ex bigint[]) columns? Jan 8, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant