0

I have been working on a java application and I have reached an issue i can not find an answer to. My application is a database for students in postgresql, the application is made in maven with an MCV pattern and for the web part I am using Spring and hibernate, as IDE is use Intellij.

I can start it with no issue but when accesing localhost page I get the following error:

"There was an unexpected error (type=Internal Server Error, status=500). could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet"

And in intelij I get the following error:

"2019-04-17 20:38:43.283 ERROR 14120 --- [nio-8010-exec-7] 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.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause org.postgresql.util.PSQLException: ERROR: relation "teacher" does not exist"

I can not understand why the relationship does not exist since I have @ManyToOne on the table tha relates to the teacher and in postgresql is the same relationship.

My Model class for teacher is :

package project.M.Entities;

import javax.persistence.*; import java.io.Serializable;

@Entity @Table(name = "Teacher") public class Teacher implements
Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @Column(name="name", nullable = false)
    private String name;
    @Column(name="address", nullable = true)
    private String address;
    @Column(name="email",nullable = false)
    private String email;
    @Column(name="age")
    private Integer age;
    @Column(name="passsword",nullable = false)
    private String password;

    public Teacher(Integer idTask, String description, String filepath, String str, String ema, Integer it) { }


        public Teacher(int id, String name, String password, String address, String email, int age) {
            this.id = id;
            this.name = name;
            this.address = address;
            this.email = email;
            this.age = age;
            this.password = password;
        }

        public Teacher( String name,String password, String address, String email, int age) {
            this.name = name;
            this.address = address;
            this.email = email;
            this.age = age;
                this.password = password;
        }


    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "Teacher{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", address='" + address + '\'' +
                ", email='" + email + '\'' +
                ", age=" + age +
                ", password='" + password + '\'' +
                '}';
    } }

And it is connected to my course class for which the model is

package project.M.Entities; import javax.persistence.Column;
import javax.persistence.Entity; import
javax.persistence.GeneratedValue; import
javax.persistence.GenerationType; import javax.persistence.Id; import
javax.persistence.JoinColumn; import javax.persistence.ManyToOne;
import javax.persistence.Table; import
javax.validation.constraints.NotNull; import java.io.Serializable;

@Entity @Table(name = "courses") public class Course implements
Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @Column(name="name",nullable = false)
    private String name;
    //@Column()
    //private Integer TeacherID;
    @ManyToOne
    @JoinColumn(name = "teacherID",referencedColumnName = "id")
    @NotNull
    private project.M.Entities.Teacher teacher;

    public Course(Integer id, String name, Teacher teacher) {
        this.id = id;
        this.name = name;
        this.teacher = teacher;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public project.M.Entities.Teacher getTeacher() {
        return teacher;
    }

    public void setTeacherID(Teacher teacherID) {
        this.teacher = teacherID;
    }

    @Override
    public String toString() {
        return "Course{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", teacher=" + teacher +
                '}';
    } }

As for the service I have for teacher:

package project.C;

import project.M.Entities.Teacher;
import project.M.Services.TeacherService;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.servlet.ModelAndView;

import javax.inject.Inject;
import java.util.List;

@Controller
public class TeacherController {

    @Inject
    TeacherService teacherService;

    @RequestMapping(value ="" ,method = RequestMethod.GET)
    public ModelAndView getTeachers()
    {
        List<Teacher> teacherList = teacherService.getAllTeachersWithCourses();


        ModelAndView mav = new ModelAndView("TeacherView");
        mav.addObject("teachersWithCoursesListId", teacherList);

        return mav;

    }
    @RequestMapping(value = "", method = RequestMethod.POST)
    public ModelAndView postTeacher(@ModelAttribute(value = "newTeacher") Teacher newTeacher)
    {
        teacherService.create(newTeacher);
        return new ModelAndView("redirect:teachers");

    }
    @RequestMapping(value ="", method = RequestMethod.DELETE)
    public ModelAndView deleteTeacher(@ModelAttribute(value = "newTeacher") Teacher newTeacher)
    {
        teacherService.delete(newTeacher);

        return new ModelAndView("redirect:teachers");

    }
/*
    @RequestMapping(value = "", method = RequestMethod.POST)
    @ResponseBody
    public void set(@RequestParam("idTeacher") Integer idTask, @RequestParam("name") String description, @RequestParam("password") String filepath, @RequestParam("address") String str, @RequestParam("email") String ema,@RequestParam("age")Integer it){

// assign parameters to taskDocumentEntity by constructor args or setters
        Teacher document = new Teacher(idTask,description,filepath,str,ema,it);
        teacherService.create(document);
    }
    */

}

and for the TeacherService I have the following

package project.M.Services;

import project.M.Entities.Teacher; import
project.M.Repository.TeacherRepository; import java.util.List; import
java.util.Optional;

import javax.inject.Inject;

import org.springframework.stereotype.Service;

@Service public class TeacherService {
    private int count;

    @Inject
    TeacherRepository teacherRepository;

    public List<Teacher> getAllTeachersWithCourses()
    {
        return teacherRepository.findAll();
    }

    public void create(Teacher newTeacher)
    {
        System.out.println("aaaa"+newTeacher);
         teacherRepository.save(newTeacher);
    }
    public void delete(Teacher newTeacher)
    {
         teacherRepository.delete(newTeacher);
    }

   public void update(Teacher old,Teacher st)    {
        if(old==null)
        {
            System.out.println("eror at update");
        }
        else
        {
            teacherRepository.delete(old);
            teacherRepository.save(st);
        }    }

} 

My TeacherView that I try to acces is :

  <!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org" xmlns:th1="http://www.w3.org/1999/xhtml">

<head>
    <title>Teachers</title>
    <link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap.min.css" />
    <link href="//maxcdn.bootstrapcdn.com/font-awesome/4.3.0/css/font-awesome.min.css" rel="stylesheet" />

</head>

<body>


<table  border="1">
    <thead>
    <tr>
        <th>teacher.ID</th>
        <th>teacher.NAME</th>
        <th>teacher.PASSWORD</th>
        <th>teacher.EMAIL</th>
        <th>teacher.AGE</th>
        <th>course entire data</th>
    </tr>
    </thead>
    <tbody>
    <tr th:each="teacher : ${ teachersWithCoursesList }">

        <td th:text="${ teacher.id }">ID</td>
        <td th:text="${ teacher.name }"></td>
        <td th:text="${ teacher.password }"></td>
        <td th:text="${ teacher.email }"></td>
        <td th:text="${ teacher.age }"></td>
        <td th1:text="${ teacher.courses }">
            <p th:each="course: ${ teacher.courses  }">
                <span th:text="${course.id}"></span>
                <span th:text="${course.name}"></span>
            </p>
        </td>

    </tr>
    </tbody>
</table>

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

<form   th:object="${newTeacher}" method="post">
    <div class="col-sm-12">
        <label>Name</label>
        <input placeholder="Teacher Name" name="name"/>
    </div>
    <div class="col-sm-12">
        <label>Password</label>
        <input placeholder="Teacher password" name="password"/>
    </div>
    <div class="col-sm-12">
        <label>Email</label>
        <input placeholder="Teacher email" name="email"/>
    </div>

    <button type="submit" class = "btn btn-primary">ADD Teacher</button>
</form>

</body>

My database is enter image description here

Also the sql for the DB : Course

 -- Table: public."Courses"

-- DROP TABLE public."Courses";

CREATE TABLE public."Courses"
(
    "idCourses" integer NOT NULL,
    "Name" "char"[] NOT NULL,
    "TeacherID" integer NOT NULL,
    CONSTRAINT "Courses_pkey" PRIMARY KEY ("idCourses"),
    CONSTRAINT "teacherID" FOREIGN KEY ("TeacherID")
        REFERENCES public."Teacher" ("idTeacher") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public."Courses"
    OWNER to postgres;

-- Index: fki_teacherID

-- DROP INDEX public."fki_teacherID";

CREATE INDEX "fki_teacherID"
    ON public."Courses" USING btree
    ("TeacherID")
    TABLESPACE pg_default;

Teacher

-- Table: public."Teacher"

-- DROP TABLE public."Teacher";

CREATE TABLE public."Teacher"
(
    "idTeacher" integer NOT NULL,
    name "char"[] NOT NULL,
    password "char"[] NOT NULL,
    address "char"[],
    email "char"[] NOT NULL,
    age integer,
    CONSTRAINT "Teacher_pkey" PRIMARY KEY ("idTeacher")
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public."Teacher"
    OWNER to postgres;

Student:

-- Table: public."Student"

-- DROP TABLE public."Student";

CREATE TABLE public."Student"
(
    address "char"[],
    email "char"[] NOT NULL,
    "idStudent" integer NOT NULL,
    name "char"[] NOT NULL,
    password "char"[] NOT NULL,
    age integer,
    CONSTRAINT "Student_pkey" PRIMARY KEY ("idStudent")
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public."Student"
    OWNER to postgres;

Grades:

-- Table: public.grades

-- DROP TABLE public.grades;

CREATE TABLE public.grades
(
    "IdCourses" integer NOT NULL,
    "idStudent" integer NOT NULL,
    "Grade" integer,
    "Exam" integer,
    "Midterm" integer,
    "examDate" date,
    "midtermDate" date,
    "Id" bigint NOT NULL,
    CONSTRAINT "IdGrades" PRIMARY KEY ("Id"),
    CONSTRAINT "idCourses" FOREIGN KEY ("IdCourses")
        REFERENCES public."Courses" ("idCourses") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT "idStudent" FOREIGN KEY ("idStudent")
        REFERENCES public."Student" ("idStudent") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.grades
    OWNER to postgres;

-- Index: fki_idCourses

-- DROP INDEX public."fki_idCourses";

CREATE INDEX "fki_idCourses"
    ON public.grades USING btree
    ("IdCourses")
    TABLESPACE pg_default;

-- Index: fki_idStudent

-- DROP INDEX public."fki_idStudent";

CREATE INDEX "fki_idStudent"
    ON public.grades USING btree
    ("idStudent")
    TABLESPACE pg_default;

later edit: This is the error after updating the code with the 2 relationships:

 org.hibernate.tool.schema.spi.CommandAcceptanceException: Unable to execute command [alter table courses drop constraint FKkhvrych02e4jav7oqoemsf498]
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:63) ~[hibernate-core-5.2.1.Final.jar:5.2.1.Final]
    at org.hibernate.tool.schema.internal.SchemaDropperImpl.applySqlString(SchemaDropperImpl.java:370) ~[hibernate-core-5.2.1.Final.jar:5.2.1.Final]
    at org.hibernate.tool.schema.internal.SchemaDropperImpl.applySqlStrings(SchemaDropperImpl.java:355) ~[hibernate-core-5.2.1.Final.jar:5.2.1.Final]
    at org.hibernate.tool.schema.internal.SchemaDropperImpl.applyConstraintDropping(SchemaDropperImpl.java:327) ~[hibernate-core-5.2.1.Final.jar:5.2.1.Final]
    at org.hibernate.tool.schema.internal.SchemaDropperImpl.dropFromMetadata(SchemaDropperImpl.java:229) ~[hibernate-core-5.2.1.Final.jar:5.2.1.Final]
    at org.hibernate.tool.schema.internal.SchemaDropperImpl.performDrop(SchemaDropperImpl.java:153) ~[hibernate-core-5.2.1.Final.jar:5.2.1.Final]
    at org.hibernate.tool.schema.internal.SchemaDropperImpl.doDrop(SchemaDropperImpl.java:125) ~[hibernate-core-5.2.1.Final.jar:5.2.1.Final]
    at org.hibernate.tool.schema.internal.SchemaDropperImpl.doDrop(SchemaDropperImpl.java:111) ~[hibernate-core-5.2.1.Final.jar:5.2.1.Final]
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:137) ~[hibernate-core-5.2.1.Final.jar:5.2.1.Final]
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:65) ~[hibernate-core-5.2.1.Final.jar:5.2.1.Final]
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:308) ~[hibernate-core-5.2.1.Final.jar:5.2.1.Final]
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:483) ~[hibernate-core-5.2.1.Final.jar:5.2.1.Final]
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:888) ~[hibernate-core-5.2.1.Final.jar:5.2.1.Final]
    at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:57) ~[spring-orm-5.1.6.RELEASE.jar:5.1.6.RELEASE]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) ~[spring-orm-5.1.6.RELEASE.jar:5.1.6.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:390) ~[spring-orm-5.1.6.RELEASE.jar:5.1.6.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:377) ~[spring-orm-5.1.6.RELEASE.jar:5.1.6.RELEASE]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341) ~[spring-orm-5.1.6.RELEASE.jar:5.1.6.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1837) ~[spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1774) ~[spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:593) ~[spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:515) ~[spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:320) ~[spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222) ~[spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:318) ~[spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199) ~[spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1105) ~[spring-context-5.1.6.RELEASE.jar:5.1.6.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:867) ~[spring-context-5.1.6.RELEASE.jar:5.1.6.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:549) ~[spring-context-5.1.6.RELEASE.jar:5.1.6.RELEASE]
    at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:142) ~[spring-boot-2.1.4.RELEASE.jar:2.1.4.RELEASE]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:775) ~[spring-boot-2.1.4.RELEASE.jar:2.1.4.RELEASE]
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397) ~[spring-boot-2.1.4.RELEASE.jar:2.1.4.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:316) ~[spring-boot-2.1.4.RELEASE.jar:2.1.4.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1260) ~[spring-boot-2.1.4.RELEASE.jar:2.1.4.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1248) ~[spring-boot-2.1.4.RELEASE.jar:2.1.4.RELEASE]
    at project.Assignment2Application.main(Assignment2Application.java:9) ~[classes/:na]
Caused by: org.postgresql.util.PSQLException: ERROR: relation "courses" does not exist
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:266) ~[postgresql-42.2.5.jar:42.2.5]
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95) ~[HikariCP-3.2.0.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-3.2.0.jar:na]
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:51) ~[hibernate-core-5.2.1.Final.jar:5.2.1.Final]
    ... 35 common frames omitted
Diana G
  • 261
  • 3
  • 15

2 Answers2

0

In your Course class, you are not obeying Java Beans naming convention.

You have a field teaacher, getter getTeacher, but setter setTeacherID.

Rename the setter to setTeacher

Update

@JoinColumn on the same field is incorrect. I can see Teacher_idTeacher in the db, and different values in your code

Another thing to check is whether your db user has all necessary grants to tables you use. If the user has no access to a table, the query may be generated correctly, but you will still receive sql grammar errors

Update2

Any of the following:

On top of that, your mapping is still incorrect, id column names do not match db.

Lesiak
  • 22,088
  • 2
  • 41
  • 65
  • Thank you for that, that was a mistake from when I tried to see if it works without the connection betweem the classes. I renamed it but the error is still there nothing has changed. – Diana G Apr 17 '19 at 19:14
  • Updated answer. If the problem persists, please update your question with generated Sql. – Lesiak Apr 17 '19 at 19:31
  • Updated post. I am sorry I placed the wrong image for the DB. – Diana G Apr 17 '19 at 20:04
0

1) Just let JPA build the tables and DDLs for you.

spring.jpa.generate-ddl
spring.jpa.hibernate.ddl-auto

https://docs.spring.io/spring-boot/docs/current/reference/html/howto-database-initialization.html

2) In the page, you are accessing teacher.courses but there is no relation object for courses in your Teacher entity.

Course

@ManyToOne(fetch = FetchType.LAZY, optional = false)
@JoinColumn(name = "TeacherID", nullable = false)
private Teacher teacher;

Teacher

@OneToMany(cascade = CascadeType.ALL,
        fetch = FetchType.LAZY,
        mappedBy = "teacher")
private List<Course> courses = new ArrayList<>();

for a complete working example, check https://www.callicoder.com/hibernate-spring-boot-jpa-one-to-many-mapping-example/

Sully
  • 14,672
  • 5
  • 54
  • 79
  • Hi, I have followed your advice and let spring to build my db. Also I have completed the code with the sequences from above. When I run it, the error is the same, even though the database is being constructed and tomcat is running. – Diana G Apr 23 '19 at 10:08
  • You can update your question with the new code and error stack trace. To fetch data use `CrudRepository`. use `@AutoWired` instead of `@Inject` – Sully Apr 24 '19 at 05:23