AutoIncrement Id PostgreSQL and Spring Boot Data JPA

Question

I'm having problems trying to create a new record in my PostgreSQL database. I just want to POST to the REST service a new user (int:id, String:email, String:password) but, I'm having this error:

"exception": "org.springframework.dao.DataIntegrityViolationException",
"message": "could not execute statement; SQL [n/a]; constraint [id]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

These are my Java classes:

Domain

@Entity
@Table(name = "users")
public class User {
 @Id
 @GeneratedValue(strategy = GenerationType.IDENTITY)
 private Integer id;
 private String email;
 private String password;
 public User() {}
 public Integer getId() {
 return id;
 }
 public void setId(Integer id) {
 this.id = id;
 }
 public String getEmail() {
 return email;
 }
 public void setEmail(String email) {
 this.email = email;
 }
 public String getPassword() {
 return password;
 }
 public void setPassword(String password) {
 this.password = password;
 }
}

Controller

@RestController
@RequestMapping("/users")
public class UserController {
 @Autowired
 private UserService userService;
 @RequestMapping(method = RequestMethod.GET)
 public List<User> findAll() {
 return userService.findAll();
 }
 @RequestMapping(method = RequestMethod.POST)
 public User addUser(@RequestBody User user) {
 userService.addUser(user);
 return user;
 }
}

Service

@Service
public class UserService {
 @Autowired
 private UserRepository userRepository;
 public List<User> findAll() {
 return (List<User>) userRepository.findAll();
 }
 public User addUser(User user) {
 userRepository.save(user);
 return user;
 }
}

Repository

public interface UserRepository extends CrudRepository<User, Integer> {
 // TODO
}

SQL

CREATE TABLE users(
 id INT PRIMARY KEY NOT NULL,
 email TEXT NOT NULL,
 password CHAR(20) NOT NULL
);

Please, somebody help me, because I don't know how to tackle this issue.

Answer 1

I found the solution. I need to change the script for these one:

CREATE TABLE users(
 id SERIAL PRIMARY KEY NOT NULL,
 email TEXT NOT NULL,
 password TEXT NOT NULL
);

Then, the Entity should be annotated with this:

@Entity
@Table(name = "users")
public class User {
 @Id
 @GeneratedValue(strategy = GenerationType.IDENTITY)
 @Column(columnDefinition = "serial")
 private Long id;
 private String email;
 private String password;
 public User() {}
 public Long getId() {
 return id;
 }
 public void setId(Long id) {
 this.id = id;
 }
 public String getEmail() {
 return email;
 }
 public void setEmail(String email) {
 this.email = email;
 }
 public String getPassword() {
 return password;
 }
 public void setPassword(String password) {
 this.password = password;
 }
}

• Klaus Groenbaek: This may not apply to your project, but you should be aware that there are some performance implications to using id generate by the database. When the ID is generated by the database JPA must use an additional query after each insert to load the id into persistence context. In addition to doubling the number of statements, it also prevents the batch insert optimization.
• andresscode: @KlausGroenbaek Well, I don't know too much about databases and performance. So, what should I do to replace the autogenerated id in my database?
• Klaus Groenbaek: You may not need to do anything unless you are inserting lots of rows per transaction, if you do that you should take a look at @TableGenerator here wiki.eclipse.org/EclipseLink/UserGuide/JPA/…
• Mike: You don't need your create table script - the tables are auto-generated by JPA.
• Kikin-Sama: Please don't auto generate tables with Hibernare, use a migration tool like Flyway instead

Answer 2

SQL should be like this..

CREATE TABLE users(
 id INT PRIMARY KEY BIGINT NOT NULL AUTO_INCREMENT,
 email TEXT NOT NULL,
 password CHAR(20) NOT NULL
);

• andresscode: Let me try with that script
• andresscode: Thanks for the help, anyway.
• Prashant Katara: PostgreSQL has the data types smallserial, serial and bigserial.