CODEWITHSUNDEEP
javapostgresqlhibernatespring-bootjpa
moussesj94
moussesj94

Reputation: 505

Sql keyword usage as a jpa entity column name

I'm using JPA and PostgreSQL, I got an error 

org.postgresql.util.PSQLException: ERROR: syntax error at or near "default"

when I want to update existing data, but it shows the error. How could I check what is the cause of this error?

This is my code

Resume.java

@Entity
@NoArgsConstructor
@AllArgsConstructor
@Getter
@Setter
@Table(name = "resume")
public class Resume extends AuditModel implements Serializable {

    @Id
    @GeneratedValue(generator = "UUID")
    @GenericGenerator(
            name = "UUID",
            strategy = "org.hibernate.id.UUIDGenerator"
    )
    @Column(name = "resume_id", updatable = false, nullable = false)
    private UUID id;

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "user_id", nullable = false)
    @OnDelete(action = OnDeleteAction.CASCADE)
    @JsonIgnore
    private User user;

    @NotNull(message = "First name may not be blank")
    @Column(name = "first_name")
    private String firstName;

    @NotNull(message = "Last name may not be blank")
    @Column(name = "last_name")
    private String lastName;

    @Column(name = "default")
    private boolean isDefault;

    @ElementCollection
    @CollectionTable(
            name = "resume_experience",
            joinColumns = @JoinColumn(name = "resume_id")
    )
    private List<Experience> experiences;

    ...

}

Experience.java

@Embeddable
@NoArgsConstructor
@AllArgsConstructor
@Getter
@Setter
public class Experience implements Serializable {

    @Column(name = "current_job")
    private boolean currentJob;

    @NotNull
    @Column(name = "start_date")
    private Date startDate;

    ...
}

This is the method of updating resume. ResumeService.java

public Resume updateResume(UUID resumeId, Resume resume) throws ResumeNotFoundException {

   Resume targetResume = resumeRepository.findById(resumeId).orElseGet(null);

   if (targetResume != null){
      resume.setId(targetResume.getId());
      resume.setUser(targetResume.getUser());
      resume.setCreatedAt(targetResume.getCreatedAt());
      return resumeRepository.save(resume);
   } else {
      throw new ResumeNotFoundException("Resume not found");
   }
}

The error message

2020-02-12 00:23:20.113 ERROR 78958 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: syntax error at or near "default"
  Position: 124
2020-02-12 00:23:20.182 ERROR 78958 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement] with root cause

org.postgresql.util.PSQLException: ERROR: syntax error at or near "default"
  Position: 124
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2505) ~[postgresql-42.2.9.jar:42.2.9]

Upvotes: 2

Views: 1845

Answers (2)

SternK
SternK

Reputation: 13041

According to the hibernate documentation:

You can force Hibernate to quote an identifier in the generated SQL by enclosing the table or column name in backticks in the mapping document. While traditionally, Hibernate used backticks for escaping SQL reserved keywords, JPA uses double quotes instead.

So, you can correct your mapping in this way:

@Column(name = "`default`")
private boolean isDefault;

or this way:

@Column(name = "\"default\"")
private boolean isDefault;

Upvotes: 2

Jayesh Patel
Jayesh Patel

Reputation: 61

The problem with the above code is @Column(name = "default") for resolving this needs to rename the "default" column name. I have also verified the same scenario with the MySQL database.

Upvotes: 1

Related Questions