Can't add and update values using Spring Boot connecting to PostgreSQL database

Question

I'm trying to update a 'Student' from PostgreSQL Database, but it only deletes the old value and creates a new id value for the new Student, with all the other fields empty.

Here is the class for "Student", from "Persistence" package, "Entity" subpackage:

@Entity

@Table(name = "student")

public class Student {

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "student_id_seq")
@SequenceGenerator(name="student_id_seq", sequenceName = "student_id_seq", allocationSize=1)
@Column(name = "id", nullable = false)
private Integer id;

@Column
private String studentName;

@Column
private Integer studentGroup;

@Column
private String courseName;

@Column
private Integer studentGrades;


public Integer getId() {
    return id;
}

public void setId(Integer id) {
    this.id = id;
}

public String getStudentName() {
    return studentName;
}

public void setStudentName(String name) {
    this.studentName = name;
}

public Integer getStudentGroup() {
    return studentGroup;
}

public void setStudentGroup(Integer group) {
    this.studentGroup = group;
}

public String getCourseName() { return courseName;}

public void setCourseName(String courseName) {this.courseName = courseName;}

public Integer getStudentGrades() {
    return studentGrades;
}

public void setStudentGrades(Integer grades) {
    this.studentGrades = grades;
}
}

This is how my interface for "Repository" subpackage (also from "Persistence" package) looks like :

public interface StudentRepository extends JpaRepository<Student, Integer> {}

In "StudentService" ("Business" package) I have the operations for create, update, delete and view all the students I have :

@Service
public class StudentService {

@Inject
StudentRepository studentRepository;

public List<Student> getAllStudentssWithCourses()
{
    return studentRepository.findAll();
}

public Student create(Student newStudent)
{
    return studentRepository.save(newStudent);
}

public void update(Student newStudent, Student oldStudent){
    if(oldStudent == null){
        System.out.println("ERROR! Student does not exist !!!");
    } else{
        newStudent.setId(oldStudent.getId());
        newStudent.setStudentName(newStudent.getStudentName());
        newStudent.setStudentGroup(newStudent.getStudentGroup());
        newStudent.setCourseName(newStudent.getCourseName());
        newStudent.setStudentGrades(newStudent.getStudentGrades());
        studentRepository.save(newStudent);
    }
}


public void delete(Student student){
    studentRepository.delete(student);
}
}

In "Controller" package, I have :

@Controller
public class StudentController {

@Inject
StudentService studentService;

@RequestMapping(value = "/student", method = RequestMethod.GET)
public ModelAndView getStudents()
{
    List<Student> studentList = studentService.getAllStudentssWithCourses();

    ModelAndView mav = new ModelAndView("student_view");
    mav.addObject("studentsWithCoursesList", studentList);
    mav.addObject("newStudent", new Student());
    return mav;

}


@RequestMapping(value = "/student", method = RequestMethod.POST)
public ModelAndView postStudent(@RequestParam(value = "action") String action,@ModelAttribute(value = "newStudent") Student newStudent)
{
    if(action.equals("Create Student"))
        studentService.create(newStudent);
    else if(action.equals("Update Student"))
        studentService.update(new Student(), newStudent);
    else
        studentService.delete(newStudent);
    return new ModelAndView("redirect:student");
}
}

My "student_view" in html looks like :

 <body background = "https://www.itmagazine.us/wp-content/uploads/2017/10/6f6499a403cf64afea180b6419def4ef.jpg">

 <h1>Welcome, student user !</h1>
 <table  border="1">
 <thead>
 <tr>
  <th>student.ID</th>
  <th>student.NAME</th>
  <th>student.GROUP</th>
  <th>student.COURSE</th>
  <th>student.GRADES</th>
 </tr>
 </thead>
 <tbody>
 <tr th:each="student : ${ studentsWithCoursesList }">

  <td th:text="${ student.id }">ID</td>
  <td th:text="${ student.studentName }"></td>
  <td th:text="${ student.studentGroup }"></td>
  <td th:text="${ student.courseName }"></td>
  <td th:text="${ student.studentGrades }"></td>
  </td>
 </tr>
 </tbody>
 </table>

 <br/>
 <br/>
 <br/>

  <form   th:object="${newStudent}" method = "POST">
  <div class="col-sm-12">
    <label>Student ID</label>
    <input type = "text" th:field="*{id}"/>
  </div>
  <div class="col-sm-12">
    <label>Student Name</label>
    <input type = "text" th:field="*{studentName}"/>
  </div>
  <div class="col-sm-12">
    <label>Student Group</label>
    <input type = "text" th:field="*{studentGroup}"/>
  </div>
  <div class="col-sm-12">
    <label>Course Name</label>
    <input type = "text" th:field="*{courseName}"/>
  </div>
  <div class="col-sm-12">
    <label>Student Grades</label>
    <input type = "text" th:field="*{studentGrades}"/>
  </div>

 <button type="submit" class = "btn btn-primary" name = "action" value="Create Student">ADD Student</button>
 <button type="submit" class = "btn btn-primary" name = "action" value="Update Student">EDIT Student</button>
 <button type="submit" class = "btn btn-primary" name = "action" value="Delete Student">DELETE Student</button>
 </form>
 </body>

My main class :

@SpringBootApplication()

public class Assignment2 {
   public static void main(String[] args){
       SpringApplication.run(Assignment2.class, args);

   }
}

In my "application.properties", I have :

spring.jpa.database = POSTGRESQL
spring.jpa.show-sql = false
spring.jpa.hibernate.ddl-auto = validate
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation = true


spring.datasource.driverClassName = org.postgresql.Driver
spring.datasource.url = jdbc:postgresql://localhost:5432/ps2
spring.datasource.username = postgres
spring.datasource.password = root

spring.thymeleaf.cache = false

spring.jpa.properties.hibernate.temp.use_jdbc_metadata_defaults = false
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

server.port = 8090

My database in PostgreSQL :

CREATE TABLE public.student
(
    id integer NOT NULL,
    student_name character varying COLLATE pg_catalog."default",
    student_group integer,
    course_name character varying COLLATE pg_catalog."default",
    student_grades integer,
    CONSTRAINT "Student_pkey" PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.student
OWNER to postgres;
-----
CREATE SEQUENCE public.student_id_seq;

ALTER SEQUENCE public.student_id_seq
    OWNER TO postgres;

Finally, in "pom.xml", I have :

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>

<groupId>com.example</groupId>
<artifactId>demo1</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>demo1</name>
<description>Demo project for Spring Boot</description>

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.1.1.RELEASE</version>
</parent>


<properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    <java.version>1.8</java.version>
</properties>

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

    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>5.2.1.Final</version>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-thymeleaf</artifactId>
    </dependency>

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

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

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-devtools</artifactId>
    </dependency>

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


    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <scope>runtime</scope>
    </dependency>


    <dependency>
        <groupId>javax.inject</groupId>
        <artifactId>javax.inject</artifactId>
        <version>1</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/postgresql/postgresql -->
    <dependency>
        <groupId>postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>9.1-901-1.jdbc4</version>
    </dependency>

</dependencies>


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


</project>

Lesiak · Accepted Answer · 2019-04-21T17:53:56.673

1

The GenerationType.IDENTITY relies on a database auto-increment column to generate the id.

Your Student.id column in the dB is clearly not auto-increment.

I believe that standard syntax for auto-increment is available since Postgres10

PostgreSQL Autoincrement

See also https://thoughts-on-java.org/jpa-generate-primary-keys/ for an intro on different generation strategies. And the following question How to choose the id generation strategy when using JPA and Hibernate

Summing up SEQUENCE is the best available option, IDENTITY is only a good choice when you cannot use SEQUENCE because it disables JDBC batch updates.

edited Apr 21 '19 at 17:53

answered Apr 21 '19 at 16:05

Lesiak

22,088
2
41
65

1

I changed from IDENTITY into SEQUENCE and it still doesn't work. Is there a way to change my primary key and make it auto-increment? – Apr 21 '19 at 18:08
Try specifying sequence name with @SequenceGenerator. Also, create this sequence in SQL. – Lesiak Apr 21 '19 at 18:11
1

It worked, but now I can't update any field in my table. It just deletes the old student and creates a new one, only with an incremented id and the rest of fields are empty. – Apr 21 '19 at 19:11
Your update in studentService is wrong. Check what you are passing to this method as newStudent. Tip: check the implementation of save in your repository. It calls persist or merge depending on the id (null or not null) You dont need a special case for update. You don't need to delete anything to update. – Lesiak Apr 21 '19 at 20:14

score 0 · Answer 2 · answered Apr 21 '19 at 16:05

0

this question was already asked before.

In conclusion, you should use :

@GeneratedValue(strategy = GenerationType.SEQUENCE)

It's because IDENTITY can only be used for these dbs:

Sybase, My SQL, MS SQL Server, DB2 and HypersonicSQL.

See https://stackoverflow.com/a/29028369/6884722

answered Apr 21 '19 at 16:05

Ricardo J. Chamorro

126
3

1

I changed from IDENTITY into SEQUENCE and now I have : Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: missing sequence [hibernate_sequence] – Apr 21 '19 at 17:19

Can't add and update values using Spring Boot connecting to PostgreSQL database

2 Answers2