0

I'm using Spring-Boot 2.5.0, Java 11, a Postgres DB and Lombok. I have e.g. a list of articles as an entity:

@Data
@Builder
@AllArgsConstructor
public class Articles {

    @Id
    private Long id;


    @Builder.Default
    private List<Article> articles = new ArrayList<>();
}

and the Article value:

@Data
@Builder
@AllArgsConstructor
public class Article {
    private String name;
    private Integer price;
}

and the repository:

@Repository
public interface ArticlesRepository extends CrudRepository<Articles, Long> {
}

The db schema looks like this:

CREATE TABLE "articles" (
  "id"                  BIGSERIAL  NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE "article" (
  "name"               VARCHAR(255),
  "price"              INTEGER,
  "articles"           BIGINT,
  "articles_key"       INTEGER
);

And testing it:

var article1 = Article.builder().name("T-Shirt").price(1635).build();
var article2 = Article.builder().name("Shoes").price(5670).build();
var articles = Articles.builder().articles(List.of(article1, article2)).build();
articlesRepository.save(articles);

will cause an exception:

Caused by: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO "articles" VALUES ()]; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"

If I just add a field to Articles like e.g. name in this case, it works fine:

@Data
@Builder
@AllArgsConstructor
public class Articles {

    @Id
    private Long id;

    private String name;

    @Builder.Default
    private List<Article> articles = new ArrayList<>();
}
4
  • Since id is autogenerated, it's left out of the insert statement. As your table doesn't contain anything else, the result is a syntactically incorrect insert statement. But if Articles doesn't contain anything besides an id, what's the purpose of the entity? Commented Jun 22, 2021 at 15:20
  • You're right from the db point of view. But just creating a dummy field, which is not needed on the Java side looks weird. The purpose of the entity with just an id is to have a back reference id for the article table Commented Jun 22, 2021 at 15:40
  • You're going to have to explain the purpose better. How is it useful? What does it allow you to do? You can now get a List<Article> based on id, but you can do that with a WHERE clause without adding an unnecessary table. Commented Jun 22, 2021 at 16:29
  • @Kayaman It's not an unneccessary table, it's how Spring Data JDBC see Doc works. In this case I don't have to create any db query on my own. Commented Jun 22, 2021 at 19:53

1 Answer 1

1

This is a current limitation of Spring Data JDBC with some databases.

Some databases require an insert to have at least one column.

As a workaround you could either add a dummy column that you don't actually use. Alternatively you could not autogenerate the id.

Please consider creating an issue so that this gets eventually fixed.

Sign up to request clarification or add additional context in comments.

1 Comment

Thanks Jens! Actually I have an extra column in my app, so its no big deal. I just noticed that behavior and want to make sure that I'm not missing anything. I'll create an issue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.