I am working on a Spring Boot project using Spring Data JPA and Hibernate but I am finding some difficulties trying to perform a simple INSERT query using the save() method of the JpaRepository interface.
As database I am using PostgreSQL, here I have the follosing table:
CREATE TABLE IF NOT EXISTS public."User"
(
id bigint NOT NULL,
first_name character varying(50) COLLATE pg_catalog."default" NOT NULL,
middle_name character varying(50) COLLATE pg_catalog."default",
surname character varying(50) COLLATE pg_catalog."default" NOT NULL,
sex "char" NOT NULL,
birthdate date NOT NULL,
tax_code character varying(255) COLLATE pg_catalog."default" NOT NULL,
e_mail character varying(255) COLLATE pg_catalog."default" NOT NULL,
contact_number character varying(50) COLLATE pg_catalog."default" NOT NULL,
created_at timestamp without time zone NOT NULL,
CONSTRAINT "User_pkey" PRIMARY KEY (id)
)
that I have mapped into an thie User entity class in my project, this one:
package com.easydefi.users.entity;
import java.io.Serializable;
import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import lombok.Data;
@Entity
@Table(name = "User")
@Data
public class User implements Serializable {
private static final long serialVersionUID = 5062673109048808267L;
@Id
@Column(name = "id")
private int id;
@Column(name = "first_name")
private String firstName;
@Column(name = "middle_name")
private String middleName;
@Column(name = "surname")
private String surname;
@Column(name = "sex")
private char sex;
@Column(name = "birthdate")
private Date birthdate;
@Column(name = "tex_code")
private String taxCode;
@Column(name = "e_mail")
private String eMail;
@Column(name = "contact_number")
private String contactNumber;
@Column(name = "created_at")
private Date createdAt;
public User(String firstName, String middleName, String surname, char sex, Date birthdate, String taxCode,
String eMail, String contactNumber, Date createdAt) {
super();
this.firstName = firstName;
this.middleName = middleName;
this.surname = surname;
this.sex = sex;
this.birthdate = birthdate;
this.taxCode = taxCode;
this.eMail = eMail;
this.contactNumber = contactNumber;
this.createdAt = createdAt;
}
}
Ok, the I created this UserRepository interface extenging the JpaRepository intreface:
public interface UserRepository extends JpaRepository<User, Integer> {
}
At the moment it is empty because I was trying to perform a first test of the save() method.
Finnally I created this simple test class containing a method to test the operation related to the insertion of a new record into the User table, this:
@SpringBootTest()
@ContextConfiguration(classes = GetUserWsApplication.class)
@TestMethodOrder(OrderAnnotation.class)
public class UserRepositoryTest {
@Autowired
private UserRepository userRepository;
@Test
@Order(1)
public void TestInsertUser()
{
User user = new User("Mario", null, "Rossi", 'M', new Date(), "XXX", "[email protected]", "329123456", new Date());
userRepository.save(user);
assertTrue(true);
}
}
the problem is that performing this method I obtain the following exception\error in my stacktrace:
2021-11-03 12:19:28.563 WARN 8094 --- [ main] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: 42601
2021-11-03 12:19:28.567 ERROR 8094 --- [ main] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: syntax error at or near "user"
Position: 13
2021-11-03 12:19:32.058 INFO 8094 --- [ionShutdownHook] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'
2021-11-03 12:19:32.065 INFO 8094 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated...
2021-11-03 12:19:32.080 INFO 8094 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed.
The problem is that it speack about a generic syntax error at or near "user" but I can not understand what is the problem.
What is the problem? How can I try to fix it? What am I missing?
CodePudding user response:
In Postgres user is a reserved SQL keyword. You can change name of the table to another word or you can use the "" tricks, but i dont recommend it.
CodePudding user response:
"user" is a reserved keyword in PostgresSQL. Either change the table's name of User to something else or use double quotes to escape it.
@Table(name = "\"User\"")