Skip to content
Advertisement

JPA SpringBoot – Unable to Save New Entity to Database

I am attempting to add (save) a “User” to my SQL database through my Spring application (The @GetMapping requests currently work). I recently added the two annotations @CreationTimeStamp and @UpdateTimeStamp in my entity class…the version without those two annotations would produce the same errors as shown. I am testing the API with Postman-here is my Users entity class:

@Table(name="users")
@Entity
public class Users {
    @Id
    @Column(name="email")
    private String email;

    @Temporal(TemporalType.TIMESTAMP)
    @CreationTimestamp
    @Column(name="created_at", updatable = false)
    private Date createdAt;     

    @Column(name="description")
    private String description;

    @Column(name="followers_count")
    private int followersCount;

    @Column(name="friends_count")
    private int friendsCount;

    @Column(name="first_last_name")
    private String firstLastName;

    @Temporal(TemporalType.TIMESTAMP)
    @UpdateTimestamp
    @Column(name="login_at")
    private Date loginAt;

    @Column(name="password")
    private String password;

    @Column(name="username")
    private String username;

    @OneToMany(cascade=CascadeType.ALL,mappedBy="userOne")
    List<Followers> followed;

    @OneToMany(cascade=CascadeType.ALL,mappedBy="userTwo")  
    List<Followers> followers;

    //auto generated getters and setters
}

my UsersDTO class

public class UsersDTO {
    @Email
    @NotNull
    private String email;

    @PastOrPresent
    private Date createdAt;

    @Size(min=0,max=44)
    private String description;

    @NotNull
    private int followersCount;

    @NotNull
    private int friendsCount;

    @Pattern(regexp="/^[a-z ,.'-]+$/i")
    private String firstLastName;

    @PastOrPresent
    private Date loginAt;

    @NotNull
    private String password;        

    @NotNull
    private String username;
    //auto gen getters and setters
}

My UsersServiceImpl

@Service
@Transactional
public class UsersServiceImpl implements UsersService{

    @Autowired
    private UsersRepository userRepo;
    
    @Override
    public List<Users> getAll() throws SubException {
        return (List<Users>) userRepo.findAll();
    }
    
    @Override
    public UsersDTO findByUsersEmail(String email) throws SubException {
        UsersDTO userDTO = null;
        Users user = userRepo.findById(email.toLowerCase()).orElseThrow(()->new SubException("General.EXCEPTION_MESSAGE"));
        userDTO = new UsersDTO();
        
        userDTO.setEmail(user.getEmail());
        userDTO.setCreatedAt(user.getCreatedAt());
        userDTO.setDescription(user.getDescription());
        userDTO.setFollowersCount(user.getFollowersCount());
        userDTO.setFriendsCount(user.getFriendsCount());
        userDTO.setFirstLastName(user.getFirstLastName());
        userDTO.setLoginAt(user.getLoginAt());
        userDTO.setPassword(user.getPassword());
        userDTO.setUsername(user.getUsername());
        
        return userDTO;
    }



    @Override
    public String addUser(UsersDTO userDTO) throws SubException {
        boolean emailAvail = userRepo.findById(userDTO.getEmail().toLowerCase()).isEmpty(); 
        String emailAdded = null;
        if(emailAvail) {
            Users user = new Users();
            user.setEmail(userDTO.getEmail().toLowerCase());
            user.setCreatedAt(userDTO.getCreatedAt());
            user.setDescription(userDTO.getDescription());
            user.setFollowersCount(userDTO.getFollowersCount());
            user.setFriendsCount(userDTO.getFriendsCount());
            user.setFirstLastName(userDTO.getFirstLastName());
            user.setLoginAt(userDTO.getLoginAt());
            String hashedpwd = null;
            try {
                hashedpwd = HashingUtility.getHashValue(userDTO.getPassword());
            } catch (NoSuchAlgorithmException e) {}
            user.setPassword(hashedpwd);
            user.setUsername(userDTO.getUsername());
            userRepo.save(user);
            emailAdded = user.getEmail().toLowerCase();
        }
        return emailAdded;
    }
}

My UsersAPI class

@RestController
@RequestMapping("users")
@Validated
@CrossOrigin
public class UsersAPI {

    @Autowired
    Environment env;
    
    @Autowired
    private UsersServiceImpl userService;
    
    @GetMapping("/{email}")
    ResponseEntity<UsersDTO> findByUsersEmail(@PathVariable String email) throws SubException{
        return new ResponseEntity<UsersDTO>(userService.findByUsersEmail(email), HttpStatus.OK);
    }
    @GetMapping("/all") 
    ResponseEntity<List<Users>> getAll() throws SubException{
        return new ResponseEntity<List<Users>>(userService.getAll(),HttpStatus.OK);
    }
    
    @PostMapping("/add")
    ResponseEntity<String> addUser(@Valid @RequestBody UsersDTO userDTO) throws SubException {
        return new ResponseEntity<String>(userService.addUser(userDTO),HttpStatus.CREATED);
    }
}

Whenever I try to add a User through Postman I am given this error (I included the hibernate lines):

Hibernate: select users0_.email as email1_0_0_, users0_.created_at as created_2_0_0_, users0_.description as descript3_0_0_, users0_.first_last_name as first_la4_0_0_, users0_.followers_count as follower5_0_0_, users0_.friends_count as friends_6_0_0_, users0_.login_at as login_at7_0_0_, users0_.password as password8_0_0_, users0_.username as username9_0_0_ from users users0_ where users0_.email=?
Hibernate: select users0_.email as email1_0_1_, users0_.created_at as created_2_0_1_, users0_.description as descript3_0_1_, users0_.first_last_name as first_la4_0_1_, users0_.followers_count as follower5_0_1_, users0_.friends_count as friends_6_0_1_, users0_.login_at as login_at7_0_1_, users0_.password as password8_0_1_, users0_.username as username9_0_1_, followed1_.users_email as users_em2_1_3_, followed1_.followers_email as follower1_1_3_, followed1_.followers_email as follower1_1_0_, followed1_.users_email as users_em2_1_0_ from users users0_ left outer join users_has_followers followed1_ on users0_.email=followed1_.users_email where users0_.email=?
Hibernate: insert into users (created_at, description, first_last_name, followers_count, friends_count, login_at, password, username, email) values (?, ?, ?, ?, ?, ?, ?, ?, ?)
2022-01-10 11:33:43.019  WARN 1468 --- [nio-3333-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1048, SQLState: 23000
2022-01-10 11:33:43.019 ERROR 1468 --- [nio-3333-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : Column 'first_last_name' cannot be null
2022-01-10 11:33:43.020  INFO 1468 --- [nio-3333-exec-1] o.h.e.j.b.internal.AbstractBatchImpl     : HHH000010: On release of batch it still contained JDBC statements
2022-01-10 11:33:43.030 ERROR 1468 --- [nio-3333-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.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement] with root cause

java.sql.SQLIntegrityConstraintViolationException: Column 'first_last_name' cannot be null

This is the JSON body I am inputting into Postman (Body -> raw and JSON selected as options) :

{
    "email": "testemail@gmail.com",
    "created_at": "2022-01-09 11:11:11",
    "description": "added through rest api",
    "followers_count": 0,
    "friends_count": 0,
    "first_last_name": "Tester Testingson",
    "login_at": "2022-01-09 11:11:11",
    "password": "hunter2",
    "username": "ttestingson2"
}

and Postman gives this error:

"timestamp": "2022-01-10T17:37:52.275+00:00",
"status": 500,
"error": "Internal Server Error",
"trace": "org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

I am able to manually add a user in MySQL Shell with:

insert into users (email,created_at,description,followers_count,friends_count,first_last_name,login_at,password,username) values ('testemail@gmail.com','2022-01-09 11:11:11', 'added through rest api',0,0,'Tester Testingson', '2022-01-09 11:11:11', 'hunter2', 'ttestingson2');

I do have an @EmbdeddId in my Followers Entity but I don’t think that would affect me adding a User.

@Entity
@Table(name="users_has_followers")
public class Followers {
        
    @EmbeddedId
    private FollowersId followers;
    
    @ManyToOne
    @MapsId("users_email")  
    @JoinColumn(name="users_email")
    Users userOne;
    
    @ManyToOne
    @MapsId("followers_email")
    @JoinColumn(name="followers_email")
    Users userTwo;
    
}

and the @Embeddable class

@Embeddable
public class FollowersId implements Serializable{
    /**
     * 
     */
    private static final long serialVersionUID = 1L;    
    private String users_email;
    private String followers_email;
    
    public String getUsers_email() {
        return users_email;
    }
    public void setUsers_email(String users_email) {
        this.users_email = users_email;
    }
    public String getFollowers_email() {
        return followers_email;
    }
    public void setFollowers_email(String followers_email) {
        this.followers_email = followers_email;
    }
    
    
}

Here is my UML diagram for the database: https://drive.google.com/file/d/1xljVZVRvEMGkKVULBi-Suqn1RFO_b7b7/view?usp=sharing

Here is the SQL script:

-- MySQL Script generated by MySQL Workbench
-- Mon Jan 10 15:14:53 2022
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema java_subscription_app
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema java_subscription_app
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `java_subscription_app` DEFAULT CHARACTER SET utf8 ;
USE `java_subscription_app` ;

-- -----------------------------------------------------
-- Table `java_subscription_app`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `java_subscription_app`.`users` (
  `email` VARCHAR(45) NOT NULL,
  `created_at` DATETIME NOT NULL,
  `description` VARCHAR(45) NULL,
  `followers_count` INT NOT NULL,
  `friends_count` INT NOT NULL,
  `first_last_name` VARCHAR(45) NOT NULL,
  `login_at` TIMESTAMP NOT NULL,
  `password` VARCHAR(45) NOT NULL,
  `username` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`email`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `java_subscription_app`.`posts`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `java_subscription_app`.`posts` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `created_at` DATETIME NOT NULL,
  `like_count` INT NOT NULL,
  `liked` TINYINT NOT NULL,
  `text` VARCHAR(100) NULL,
  `users_email` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_posts_users1_idx` (`users_email` ASC) VISIBLE,
  CONSTRAINT `fk_posts_users1`
    FOREIGN KEY (`users_email`)
    REFERENCES `java_subscription_app`.`users` (`email`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `java_subscription_app`.`comments`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `java_subscription_app`.`comments` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `message` VARCHAR(100) NULL,
  `created_at` DATETIME NOT NULL,
  `posts_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_comments_posts1_idx` (`posts_id` ASC) VISIBLE,
  CONSTRAINT `fk_comments_posts1`
    FOREIGN KEY (`posts_id`)
    REFERENCES `java_subscription_app`.`posts` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `java_subscription_app`.`payment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `java_subscription_app`.`payment` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `expire_date` DATE NOT NULL,
  `name_on_card` VARCHAR(45) NOT NULL,
  `number` VARCHAR(45) NOT NULL,
  `security_code` INT NOT NULL,
  `users_email` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_payment_users1_idx` (`users_email` ASC) VISIBLE,
  CONSTRAINT `fk_payment_users1`
    FOREIGN KEY (`users_email`)
    REFERENCES `java_subscription_app`.`users` (`email`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `java_subscription_app`.`users_has_followers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `java_subscription_app`.`users_has_followers` (
  `users_email` VARCHAR(45) NOT NULL,
  `followers_email` VARCHAR(45) NOT NULL,
  INDEX `fk_users_has_users_users2_idx` (`followers_email` ASC) VISIBLE,
  INDEX `fk_users_has_users_users1_idx` (`users_email` ASC) VISIBLE,
  PRIMARY KEY (`followers_email`, `users_email`),
  CONSTRAINT `fk_users_has_users_users1`
    FOREIGN KEY (`users_email`)
    REFERENCES `java_subscription_app`.`users` (`email`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_users_has_users_users2`
    FOREIGN KEY (`followers_email`)
    REFERENCES `java_subscription_app`.`users` (`email`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

USE `java_subscription_app` ;

-- -----------------------------------------------------
-- Placeholder table for view `java_subscription_app`.`view1`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `java_subscription_app`.`view1` (`id` INT);

-- -----------------------------------------------------
-- View `java_subscription_app`.`view1`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `java_subscription_app`.`view1`;
USE `java_subscription_app`;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

I am not sure what’s causing my problem…please help! Thank you

Advertisement

Answer

first all keep one code style сamelCase or snake_case. You send “first_last_name”, but expects “firstLastName”. Not necessary sending fields login_at, created_at you have already put the appropriate annotations .

{
    "email": "testemail@gmail.com",
    "description": "added through rest api",
    "followersCount": 0,
    "friendsCount": 0,
    "firstLastName": "Tester Testingson",
    "password": "hunter2",
    "username": "ttestingson2"
}
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement