Home > Net >  Postman Post Request column cannot be found error(JDBC/MYSQL/AWS)
Postman Post Request column cannot be found error(JDBC/MYSQL/AWS)

Time：05-14

I am working on an independent JDBC/Springboot project and I am having a lot of trouble actually posting any data from postman into MySQL. I have tried looking everywhere and I am at the point where I feel like I should restart the project. If someone could debug my code so far to see what is going on that would be super helpful. Also, let me know if my JSON syntax is correct for postman and if I should add or subtract anything from my pom.xml file. I would appreciate any help that I can get to resolve this issue so I can move on with my project. Thank you.

This is the specific error that shows up: Caused by: java.sql.SQLIntegrityConstraintViolationException: Column 'dob' cannot be null

@RestController
@RequestMapping(value = "/api/students", method = {RequestMethod.GET, RequestMethod.POST})
@ResponseBody
public class StudentController {
    private static final Logger LOGGER = LogManager.getLogger(StudentController.class);

    private StudentService studentService;

    public StudentController(StudentService studentService) {
        super();
        this.studentService = studentService;
    }
    @ExceptionHandler
    @ResponseStatus(HttpStatus.BAD_REQUEST)
    public void handle(Exception e) {
        LOGGER.warn("Returning HTTP 400 Bad Request", e);
    }
    //build create Student Rest API
    @PostMapping
    public ResponseEntity<Student> saveStudent(@RequestBody Student student) {
        return new ResponseEntity<Student>(studentService.saveStudent(student), HttpStatus.CREATED);
    }
}

import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import javax.persistence.*;
import java.util.Date;


@Data
@Entity
@Table(name = "student")
public class Student {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long studentID;
    @Column(name = "parent_id")
    private long parentID;
    @Column(name = "email") //nullable = false, unique = true
    private String email;
    @Column(name = "password")
    private String password;
    @Column(name = "first_name")
    private String firstName;
    @Column(name = "last_name")
    private String lastName;
    @Column(name = "dob")
    @JsonFormat(shape= JsonFormat.Shape.STRING, pattern="dd-MM-yyyy")
    private Date dob;
    @Column(name = "home_phone")
    private String homePhone;
    @Column(name = "mobile")
    private String mobile;
    @Column(name = "first_day_on_campus")
    @JsonFormat(shape= JsonFormat.Shape.STRING, pattern="dd-MM-yyyy")
    private Date firstDayOnCampus;
    @Column(name = "grade_level")
    private String gradeLevel;
    @Column(name = "gpa")
    private double gpa;
    @Column(name = "sat_score")
    private int satScore;
    @Column(name = "act_score")
    private int actScore;
    @Column(name = "last_login_date")
    @JsonFormat(shape= JsonFormat.Shape.STRING, pattern="dd-MM-yyyy")
    private Date lastLoginDate;
    @Column(name = "last_login_ip")
    private String lastLoginIP;

    public long getStudentID() {
        return studentID;
    }

    public void setStudentID(long studentID) {
        this.studentID = studentID;
    }

    public long getParentID() {
        return parentID;
    }

    public void setParentID(long parentID) {
        this.parentID = parentID;
    }

    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;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public Date getDob() {
        return dob;
    }

    public void setDob(Date dob) {
        this.dob = dob;
    }

    public String getHomePhone() {
        return homePhone;
    }

    public void setHomePhone(String homePhone) {
        this.homePhone = homePhone;
    }

    public String getMobile() {
        return mobile;
    }

    public void setMobile(String mobile) {
        this.mobile = mobile;
    }

    public Date getFirstDayOnCampus() {
        return firstDayOnCampus;
    }

    public void setFirstDayOnCampus(Date firstDayOnCampus) {
        this.firstDayOnCampus = firstDayOnCampus;
    }

    public String getGradeLevel() {
        return gradeLevel;
    }

    public void setGradeLevel(String gradeLevel) {
        this.gradeLevel = gradeLevel;
    }

    public double getGpa() {
        return gpa;
    }

    public void setGpa(double gpa) {
        this.gpa = gpa;
    }

    public int getSatScore() {
        return satScore;
    }

    public void setSatScore(int satScore) {
        this.satScore = satScore;
    }

    public int getActScore() {
        return actScore;
    }

    public void setActScore(int actScore) {
        this.actScore = actScore;
    }

    public Date getLastLoginDate() {
        return lastLoginDate;
    }

    public void setLastLoginDate(Date lastLoginDate) {
        this.lastLoginDate = lastLoginDate;
    }

    public String getLastLoginIP() {
        return lastLoginIP;
    }

    public void setLastLoginIP(String lastLoginIP) {
        this.lastLoginIP = lastLoginIP;
    }
}


package com.chrisportfolio.StudentManagementSystem.service.implementations;

import com.chrisportfolio.StudentManagementSystem.model.Student;
import com.chrisportfolio.StudentManagementSystem.repository.StudentRepository;
import com.chrisportfolio.StudentManagementSystem.service.StudentService;
import org.springframework.stereotype.Service;

@Service
public class StudentServiceImpl implements StudentService {

    private StudentRepository studentRepository;

    public StudentServiceImpl(StudentRepository studentRepository) {
        super();
        this.studentRepository = studentRepository;
    }

    @Override
    public Student saveStudent(Student student) {
        return studentRepository.save(student);
    }
}


<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.4</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.chrisportfolio</groupId>
    <artifactId>StudentManagementSystem</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>StudentManagementSystem</name>
    <description>will help a school manage data, communications, and scheduling</description>
    <properties>
        <java.version>14</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-web -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.6.4</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework/spring-core -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>5.3.17</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-data-jpa -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <version>2.6.4</version>
        </dependency>


        <!-- https://mvnrepository.com/artifact/org.springframework/spring-web -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-web</artifactId>
            <version>5.3.17</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.3.17</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework/spring-webmvc -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>5.3.17</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework.data/spring-data-jpa -->
        <dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-jpa</artifactId>
            <version>2.6.3</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-test -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>5.3.17</version>
            <scope>test</scope>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework/spring-aop -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aop</artifactId>
            <version>5.3.17</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-orm -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>5.3.17</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.3.17</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
            <version>8.0.28</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/software.amazon.awssdk/rds -->
        <dependency>
            <groupId>software.amazon.awssdk</groupId>
            <artifactId>rds</artifactId>
            <version>2.17.152</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/software.aws.rds/aws-mysql-jdbc -->
        <dependency>
            <groupId>software.aws.rds</groupId>
            <artifactId>aws-mysql-jdbc</artifactId>
            <version>1.0.0</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.amazonaws/aws-java-sdk-rds -->
        <dependency>
            <groupId>com.amazonaws</groupId>
            <artifactId>aws-java-sdk-rds</artifactId>
            <version>1.12.181</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework/spring-hibernate -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-hibernate</artifactId>
            <version>1.2.9</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.hibernate.javax.persistence/hibernate-jpa-2.1-api -->
        <dependency>
            <groupId>org.hibernate.javax.persistence</groupId>
            <artifactId>hibernate-jpa-2.1-api</artifactId>
            <version>1.0.2.Final</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.hibernate.orm/hibernate-core -->
        <dependency>
            <groupId>org.hibernate.orm</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>6.0.0.CR2</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.teiid/teiid-hibernate-dialect -->
        <dependency>
            <groupId>org.teiid</groupId>
            <artifactId>teiid-hibernate-dialect</artifactId>
            <version>16.0.0</version>
        </dependency>

        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-entitymanager</artifactId>
            <version>5.2.3.Final</version>
        </dependency>

        <dependency>
            <groupId>javax.xml.bind</groupId>
            <artifactId>jaxb-api</artifactId>
            <version>2.3.0</version>
        </dependency>


        <!-- https://mvnrepository.com/artifact/javax.transaction/javax.transaction-api -->
        <dependency>
            <groupId>javax.transaction</groupId>
            <artifactId>javax.transaction-api</artifactId>
            <version>1.3</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-aspects -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aspects</artifactId>
            <version>5.3.17</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-thymeleaf -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
            <version>2.6.4</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework.kafka/spring-kafka -->
        <dependency>
            <groupId>org.springframework.kafka</groupId>
            <artifactId>spring-kafka</artifactId>
            <version>2.8.4</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.heroku.api/heroku-api -->
        <dependency>
            <groupId>com.heroku.api</groupId>
            <artifactId>heroku-api</artifactId>
            <version>0.45</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.heroku.sdk/heroku-deploy -->
        <dependency>
            <groupId>com.heroku.sdk</groupId>
            <artifactId>heroku-deploy</artifactId>
            <version>3.0.4</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.heroku.api/heroku-http-apache -->
        <dependency>
            <groupId>com.heroku.api</groupId>
            <artifactId>heroku-http-apache</artifactId>
            <version>0.45</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.heroku.api/heroku-json-jackson -->
        <dependency>
            <groupId>com.heroku.api</groupId>
            <artifactId>heroku-json-jackson</artifactId>
            <version>0.45</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework.cloud/spring-cloud-heroku-connector -->
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-heroku-connector</artifactId>
            <version>2.0.9.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.javassist</groupId>
            <artifactId>javassist</artifactId>
            <version>3.27.0-GA</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.22</version>
            <scope>provided</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-log4j2 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-log4j2</artifactId>
            <version>2.6.5</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-core -->
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-core</artifactId>
            <version>2.17.2</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-api -->
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-api</artifactId>
            <version>2.17.2</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-slf4j-impl -->
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-slf4j-impl</artifactId>
            <version>2.17.2</version>
            <scope>test</scope>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.fasterxml.jackson.core/jackson-databind -->
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
            <version>2.13.2.2</version>
        </dependency>
        
    </dependencies>





    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>


{
    "student": {
        "student_id": "578329",
        "parent_id": "34950593",
        "email": "[email protected]",
        "password": "goodgrief72",
        "first_name": "Charlie",
        "last_name": "Brown",
        "dob": "10-10-2008",
        "home_phone": "2025550186",
        "mobile": "2025559456",
        "first_day_on_campus": "09-12-2021",
        "grade_level": "9",
        "gpa": "4.0",
        "sat_score": "N/A",
        "act_score": "N/A",
        "last_login_date": "2022-03-01",
        "last_login_ip": "153.132.246.12"
    }
}

CodePudding user response：

i'd like to mention some points that i would consider to do if it was me.

  1. i don't think you need @ResponseBody
  2. specify constructor to your database model(student) in cuz it is required for hibernate
  3. it is better practice if you separate database model with rest models
  4. try to add what is the problem e.g (log, or exception that you are facing)

CodePudding user response：

There are some points to note.

1- You were sending incorrect student attributes from the postman according to the JSON you've provided. If you compare the variable's name that you declared in the Student class, they don't match. I guess you misunderstood the @Column annotation, this annotation is used for referencing columns of the database.

2- After seeing your code, you don't have to pass the studentID as it is generated automatically.

Use this JSON and make a Request.

{
        
        "parentID": "34950593",
        "email": "[email protected]",
        "password": "goodgrief72",
        "firstName": "Charlie",
        "lastName": "Brown",
        "dob": "10-10-2008",
        "homePhone": "2025550186",
        "mobile": "2025559456",
        "firstDayOnCampus": "09-12-2021",
        "gradeLevel": "9",
        "gpa": 4.0,
        "satScore": 0,
        "actScore": 0,
        "lastLoginDate": "2022-03-01",
        "lastLoginIP": "153.132.246.12"
    }

CodePudding user response：

The problem is you are passing incorrect json and spring tries to write student with null values in the database, and the database has NOT NULL constraints on some fields.

First you need to correct your json:

-keys in the json shoud correspond value names in the entity

-if your value is int/double you can't pass string, like: "sat_score": "N/A"

-remove "student" header and corresponding bracket at the end

-id in your Json won't be used as your entity uses database generated id

Also you need no arguments constructor in your entity class.

Page link：https//www.codepudding.com/net/406387.html

Prev:how to format String to Date with format dd-mm-yyyy in java
Next:Java Time & Rest API
  • Related

About Us:  Contact Us      Terms of Service       Privacy Policy

Copyright © 2010-2023，Powered By CodePudding