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