PostgreSQL 使用 Spring Data JPA REST API 获取/提交数据时出现错误。

huangapple
go评论52阅读模式
英文:

Error when trying to GET/POST data in PostgreSQL using Spring Data JPA REST APIs

问题

I'm using Spring Boot 3 to create basic REST APIs, and trying to get content from PostgreSQL and post there too. I looked up every documentation and tutorial but GET gives an Error 500 while POST gives an Error 400. The APIs were working perfectly with the usual get, getById and post logic coded in the content repository. They stop working when trying to use Spring Data JPA.

UPDATE 1: GET is working by removing 'desc', POST still giving error 400

UPDATE 2: It was the damn enum. SOLVED

postman get error

postman post error

postgres table

Here's the code.

Content Model

package com.project.test.example.model;

import jakarta.persistence.*;
import jakarta.validation.constraints.NotBlank;

import java.time.LocalDateTime;

@Entity
@Table(name = "content")
public class Content {
        @Id
        @GeneratedValue(strategy = GenerationType.AUTO)
        @Column(name = "id")
        Integer id;
        @NotBlank
        @Column(name = "title")
        String title;
        @NotBlank
        @Column(name = "desc")
        String desc;

        @Column(name = "status")
        Status status;

        @Column(name = "type")
        Type type;

        @Column(name = "dateCreated")
        LocalDateTime dateCreated;

        @Column(name = "dateUpdated")
        LocalDateTime dateUpdated;

        @Column(name = "url")
        String url;

        public Content(Integer id, String title, String desc, Status status, Type type, LocalDateTime dateCreated, LocalDateTime dateUpdated, String url) {
                this.id = id;
                this.title = title;
                this.desc = desc;
                this.status = status;
                this.type = type;
                this.dateCreated = dateCreated;
                this.dateUpdated = dateUpdated;
                this.url = url;
        }

        public Integer getId() {
                return id;
        }

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

        public String getTitle() {
                return title;
        }

        public void setTitle(String title) {
                this.title = title;
        }

        public String getDesc() {
                return desc;
        }

        public void setDesc(String desc) {
                this.desc = desc;
        }

        public Status getStatus() {
                return status;
        }

        public void setStatus(Status status) {
                this.status = status;
        }

        public Type getType() {
                return type;
        }

        public void setType(Type type) {
                this.type = type;
        }

        public LocalDateTime getDateCreated() {
                return dateCreated;
        }

        public void setDateCreated(LocalDateTime dateCreated) {
                this.dateCreated = dateCreated;
        }

        public LocalDateTime getDateUpdated() {
                return dateUpdated;
        }

        public void setDateUpdated(LocalDateTime dateUpdated) {
                this.dateUpdated = dateUpdated;
        }

        public String getUrl() {
                return url;
        }

        public void setUrl(String url) {
                this.url = url;
        }
}

Content JpaRepository Interface

package com.project.test.example.repository;

import com.project.test.example.model.Content;
import org.springframework.data.jpa.repository.JpaRepository;

import java.util.List;

public interface ContentJPARespository extends JpaRepository<Content, Integer> {

}

Content Service

package com.project.test.example.service;

import com.project.test.example.model.Content;
import com.project.test.example.repository.ContentJPARespository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class ContentService {
    @Autowired
    private ContentJPARespository repo;

    public Content saveContent(Content con){
        return repo.save(con);
    }

    public List<Content> getContent(){
        return repo.findAll();
    }
}

Content Controller (The commented code was the one working without Spring JPA, the ContentCollectionRepository is below this controller)

package com.project.test.example.controller;

import com.project.test.example.model.Content;
import com.project.test.example.repository.ContentCollectionRepository;
import com.project.test.example.repository.ContentJPARespository;
import com.project.test.example.service.ContentService;
import io.swagger.v3.oas.annotations.tags.Tag;
import jakarta.validation.Valid;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;

import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.server.ResponseStatusException;

import java.util.List;

@RestController
@RequestMapping("/api/content") // url to access apis
@CrossOrigin(origins = "http://localhost:3000")
@Tag(name = "Content") // swagger controller name
public class ContentController {
    @Autowired
    private ContentService service;

    // get all content
    @GetMapping("/getAll")
    public List<Content> findAll() {
        return service.getContent();
    }

    // create new content
    @ResponseStatus(HttpStatus.CREATED)
    @PostMapping("/create")
    public Content saveCon(@RequestBody Content content) {


<details>
<summary>英文:</summary>

I&#39;m using Spring Boot 3 to create basic REST APIs, and trying to get content from PostgreSQL and post there too. I looked up every documentation and tutorial but GET gives an Error 500 while POST gives an Error 400. The APIs were working perfectly with the usual get, getById and post logic coded in the content repository. They stop working when trying to use Spring Data JPA. 

UPDATE 1: GET is working by removing &#39;desc&#39;, POST still giving error 400

UPDATE 2: It was the damn enum. SOLVED

[postman get error](https://i.stack.imgur.com/0PQLH.png)

[postman post error](https://i.stack.imgur.com/hJpMh.png)

[postgres table](https://i.stack.imgur.com/Jac5U.png)

Here&#39;s the code.

Content Model

package com.project.test.example.model;

import jakarta.persistence.*;
import jakarta.validation.constraints.NotBlank;

import java.time.LocalDateTime;

@Entity
@Table(name = "content")
public class Content {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "id")
Integer id;
@NotBlank
@Column(name = "title")
String title;
@NotBlank
@Column(name = "desc")
String desc;

    @Column(name = &quot;status&quot;)
Status status;
@Column(name = &quot;type&quot;)
Type type;
@Column(name = &quot;dateCreated&quot;)
LocalDateTime dateCreated;
@Column(name = &quot;dateUpdated&quot;)
LocalDateTime dateUpdated;
@Column(name = &quot;url&quot;)
String url;
public Content(Integer id, String title, String desc, Status status, Type type, LocalDateTime dateCreated, LocalDateTime dateUpdated, String url) {
this.id = id;
this.title = title;
this.desc = desc;
this.status = status;
this.type = type;
this.dateCreated = dateCreated;
this.dateUpdated = dateUpdated;
this.url = url;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getDesc() {
return desc;
}
public void setDesc(String desc) {
this.desc = desc;
}
public Status getStatus() {
return status;
}
public void setStatus(Status status) {
this.status = status;
}
public Type getType() {
return type;
}
public void setType(Type type) {
this.type = type;
}
public LocalDateTime getDateCreated() {
return dateCreated;
}
public void setDateCreated(LocalDateTime dateCreated) {
this.dateCreated = dateCreated;
}
public LocalDateTime getDateUpdated() {
return dateUpdated;
}
public void setDateUpdated(LocalDateTime dateUpdated) {
this.dateUpdated = dateUpdated;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}

}


Content JpaRepository Interface

package com.project.test.example.repository;

import com.project.test.example.model.Content;
import org.springframework.data.jpa.repository.JpaRepository;

import java.util.List;

public interface ContentJPARespository extends JpaRepository<Content, Integer> {

}


Content Service

package com.project.test.example.service;

import com.project.test.example.model.Content;
import com.project.test.example.repository.ContentJPARespository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class ContentService {
@Autowired
private ContentJPARespository repo;

public Content saveContent(Content con){
return repo.save(con);
}
public List&lt;Content&gt; getContent(){
return repo.findAll();
}

}


Content Controller (The commented code was the one working without Spring JPA, the ContentCollectionRepository is below this controller)

package com.project.test.example.controller;

import com.project.test.example.model.Content;
import com.project.test.example.repository.ContentCollectionRepository;
import com.project.test.example.repository.ContentJPARespository;
import com.project.test.example.service.ContentService;
import io.swagger.v3.oas.annotations.tags.Tag;
import jakarta.validation.Valid;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;

import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.server.ResponseStatusException;

import java.util.List;

@RestController
@RequestMapping("/api/content") // url to access apis
@CrossOrigin(origins = "http://localhost:3000")
@Tag(name = "Content") // swagger controller name
public class ContentController {
// private final ContentCollectionRepository repository; //dependency injection of ContentCollectionRepository
// public ContentController(ContentCollectionRepository repository) { // constructor
// this.repository = repository;
// }

@Autowired
private ContentService service;

// // get all content
// @GetMapping("/getAll")
// public List<Content> finAll() {
// return repository.findAll();
// }

// get all content
@GetMapping(&quot;/getAll&quot;)
public List&lt;Content&gt; findAll() {
return service.getContent();
}

// // get content by id
// @GetMapping("/getById/{id}")
// public Content findById(@PathVariable Integer id) { // @PathVariable is like useParams() in react
// return repository.findById(id).orElseThrow(() -> new ResponseStatusException(HttpStatus.NOT_FOUND, "Content not found"));
// }

// // create new content
// @ResponseStatus(HttpStatus.CREATED)
// @PostMapping("/create")
// public void create(@Valid @RequestBody Content content) {
// repository.save(content);
// }

// create new content
@ResponseStatus(HttpStatus.CREATED)
@PostMapping(&quot;/create&quot;)
public Content saveCon(@RequestBody Content content) {
return service.saveContent(content);
}

// // update content by id
// @ResponseStatus(HttpStatus.NO_CONTENT)
// @PutMapping("/update/{id}")
// public void update(@PathVariable Integer id, @RequestBody Content content) {
// if (!repository.existsById(id)) {
// throw new ResponseStatusException(HttpStatus.NOT_FOUND, "Id must be greater than 0");
// }
// repository.save(content);
//// repository.updateById(id, content);
//
// }
//
// // delete content by id
// @ResponseStatus(HttpStatus.NO_CONTENT)
// @DeleteMapping("/delete/{id}")
// public void delete(@PathVariable Integer id) {
// if (!repository.existsById(id)) {
// throw new ResponseStatusException(HttpStatus.NOT_FOUND, "Id must be greater than 0");
// }
// repository.delete(id);
// }

}


ContentCollectionRepository

package com.project.test.example.repository;

import com.project.test.example.model.Content;
import com.project.test.example.model.Status;
import com.project.test.example.model.Type;
import jakarta.annotation.PostConstruct;
import org.springframework.http.HttpStatus;
import org.springframework.stereotype.Repository;
import org.springframework.web.server.ResponseStatusException;

import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

@Repository
public class ContentCollectionRepository {
private final List<Content> contentList = new ArrayList<>();

public ContentCollectionRepository() {
}
public List&lt;Content&gt; findAll() {
return contentList;
}
public Optional&lt;Content&gt; findById(Integer id) {  // Optional -&gt; may or may not be null
return contentList.stream().filter(c -&gt; c.getId().equals(id)).findFirst();
}
public void save(Content content) {
contentList.removeIf(c -&gt; c.getId().equals(content.getId())); // remove data on the Id
contentList.add(content); // replace with updated data on the same Id
}

// public void updateById(Integer id, Content content) {
// contentList.set(id - 1, content);
// }

public boolean existsById(Integer id) {
return contentList.stream().filter(c -&gt; c.getId().equals(id)).count() == 1; // does id exist?
}
public void delete(Integer id) {
contentList.removeIf(c -&gt; c.getId().equals(id));
}
@PostConstruct // initialize dependency injection
private void init() {
Content c = new Content(
1,
&quot;My First Blog Post&quot;,
&quot;My first blog post&quot;,
Status.IDEA,
Type.ARTICLE,
LocalDateTime.now(),
null,
&quot;&quot;
);
contentList.add(c);
}

}


Application.properties

spring.datasource.url= jdbc:postgresql://localhost:5432/postgres
spring.datasource.username= postgres
spring.datasource.password= 123

spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation= true
spring.jpa.properties.hibernate.dialect= org.hibernate.dialect.PostgreSQLDialect

Hibernate ddl auto (create, create-drop, validate, update)

spring.jpa.hibernate.ddl-auto= update


Columns did get added in postgres so it kinda works but not the apis.
Error logs

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "alter table if exists content add column desc varchar(255)" via JDBC Statement
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:587) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:608) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.migrateTable(AbstractSchemaMigrator.java:325) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.tool.schema.internal.GroupedSchemaMigratorImpl.performTablesMigration(GroupedSchemaMigratorImpl.java:83) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:225) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:126) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:284) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.lambda$process$5(SchemaManagementToolCoordinator.java:143) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at java.base/java.util.HashMap.forEach(HashMap.java:1421) ~[na:na]
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:140) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:336) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:415) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1423) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:66) ~[spring-orm-6.0.8.jar:6.0.8]
at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:376) ~[spring-orm-6.0.8.jar:6.0.8]
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:409) ~[spring-orm-6.0.8.jar:6.0.8]
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:396) ~[spring-orm-6.0.8.jar:6.0.8]
at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:352) ~[spring-orm-6.0.8.jar:6.0.8]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1816) ~[spring-beans-6.0.8.jar:6.0.8]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1766) ~[spring-beans-6.0.8.jar:6.0.8]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:598) ~[spring-beans-6.0.8.jar:6.0.8]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:520) ~[spring-beans-6.0.8.jar:6.0.8]
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:326) ~[spring-beans-6.0.8.jar:6.0.8]
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-6.0.8.jar:6.0.8]
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:324) ~[spring-beans-6.0.8.jar:6.0.8]
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:200) ~[spring-beans-6.0.8.jar:6.0.8]
at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1132) ~[spring-context-6.0.8.jar:6.0.8]
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:907) ~[spring-context-6.0.8.jar:6.0.8]
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:584) ~[spring-context-6.0.8.jar:6.0.8]
at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:146) ~[spring-boot-3.0.6.jar:3.0.6]
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:732) ~[spring-boot-3.0.6.jar:3.0.6]
at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:434) ~[spring-boot-3.0.6.jar:3.0.6]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:310) ~[spring-boot-3.0.6.jar:3.0.6]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1304) ~[spring-boot-3.0.6.jar:3.0.6]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1293) ~[spring-boot-3.0.6.jar:3.0.6]
at com.project.test.example.TestExampleApplication.main(TestExampleApplication.java:14) ~[classes/:na]
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "desc"
Position: 42
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676) ~[postgresql-42.5.4.jar:42.5.4]
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366) ~[postgresql-42.5.4.jar:42.5.4]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356) ~[postgresql-42.5.4.jar:42.5.4]
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496) ~[postgresql-42.5.4.jar:42.5.4]
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413) ~[postgresql-42.5.4.jar:42.5.4]
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:333) ~[postgresql-42.5.4.jar:42.5.4]
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:319) ~[postgresql-42.5.4.jar:42.5.4]
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:295) ~[postgresql-42.5.4.jar:42.5.4]
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:290) ~[postgresql-42.5.4.jar:42.5.4]
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94) ~[HikariCP-5.0.1.jar:na]
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-5.0.1.jar:na]
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
... 36 common frames omitted

2023-05-28T19:10:25.081+05:00 INFO 18920 --- [ main] o.h.e.t.j.p.i.JtaPlatformInitiator : HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]
2023-05-28T19:10:25.102+05:00 INFO 18920 --- [ main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2023-05-28T19:10:25.865+05:00 WARN 18920 --- [ main] JpaBaseConfiguration$JpaWebConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
2023-05-28T19:10:26.703+05:00 INFO 18920 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 8080 (http) with context path ''
2023-05-28T19:10:26.719+05:00 INFO 18920 --- [ main] c.p.test.example.TestExampleApplication : Started TestExampleApplication in 8.905 seconds (process running for 9.688)
2023-05-28T19:10:34.817+05:00 INFO 18920 --- [nio-8080-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring DispatcherServlet 'dispatcherServlet'
2023-05-28T19:10:34.817+05:00 INFO 18920 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet : Initializing Servlet 'dispatcherServlet'
2023-05-28T19:10:34.817+05:00 INFO 18920 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet : Completed initialization in 0 ms
2023-05-28T19:10:35.100+05:00 WARN 18920 --- [nio-8080-exec-1] .w.s.m.s.DefaultHandlerExceptionResolver : Resolved [org.springframework.http.converter.HttpMessageNotReadableException: JSON parse error: Cannot deserialize value of type com.project.test.example.model.Content from Array value (token JsonToken.START_ARRAY)]
2023-05-28T19:10:43.732+05:00 WARN 18920 --- [nio-8080-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: 42703
2023-05-28T19:10:43.732+05:00 ERROR 18920 --- [nio-8080-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: column c1_0.desc does not exist
Position: 52
2023-05-28T19:10:43.749+05:00 ERROR 18920 --- [nio-8080-exec-4] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [select c1_0.id,c1_0.date_created,c1_0.date_updated,c1_0.desc,c1_0.status,c1_0.title,c1_0.type,c1_0.url from content c1_0]; SQL [n/a]] with root cause

org.postgresql.util.PSQLException: ERROR: column c1_0.desc does not exist
Position: 52
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676) ~[postgresql-42.5.4.jar:42.5.4]
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366) ~[postgresql-42.5.4.jar:42.5.4]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356) ~[postgresql-42.5.4.jar:42.5.4]
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496) ~[postgresql-42.5.4.jar:42.5.4]
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413) ~[postgresql-42.5.4.jar:42.5.4]
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190) ~[postgresql-42.5.4.jar:42.5.4]
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:134) ~[postgresql-42.5.4.jar:42.5.4]
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-5.0.1.jar:na]
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-5.0.1.jar:na]
at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:217) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:146) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.advanceNext(JdbcValuesResultSetImpl.java:205) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.processNext(JdbcValuesResultSetImpl.java:85) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.sql.results.jdbc.internal.AbstractJdbcValues.next(AbstractJdbcValues.java:29) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.sql.results.internal.RowProcessingStateStandardImpl.next(RowProcessingStateStandardImpl.java:88) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:197) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:33) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:443) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:166) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.list(JdbcSelectExecutorStandardImpl.java:91) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:31) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.lambda$new$0(ConcreteSqmSelectQueryPlan.java:113) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.withCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:335) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.performList(ConcreteSqmSelectQueryPlan.java:276) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.query.sqm.internal.QuerySqmImpl.doList(QuerySqmImpl.java:571) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:363) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.query.sqm.internal.QuerySqmImpl.list(QuerySqmImpl.java:1073) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.hibernate.query.Query.getResultList(Query.java:94) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:403) ~[spring-data-jpa-3.0.5.jar:3.0.5]
at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:89) ~[spring-data-jpa-3.0.5.jar:3.0.5]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
at org.springframework.data.repository.core.support.RepositoryMethodInvoker$RepositoryFragmentMethodInvoker.lambda$new$0(RepositoryMethodInvoker.java:288) ~[spring-data-commons-3.0.5.jar:3.0.5]
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:136) ~[spring-data-commons-3.0.5.jar:3.0.5]
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:120) ~[spring-data-commons-3.0.5.jar:3.0.5]
at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:516) ~[spring-data-commons-3.0.5.jar:3.0.5]
at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:285) ~[spring-data-commons-3.0.5.jar:3.0.5]
at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:628) ~[spring-data-commons-3.0.5.jar:3.0.5]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.8.jar:6.0.8]
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:168) ~[spring-data-commons-3.0.5.jar:3.0.5]
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:143) ~[spring-data-commons-3.0.5.jar:3.0.5]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.8.jar:6.0.8]
at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:77) ~[spring-data-commons-3.0.5.jar:3.0.5]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.8.jar:6.0.8]
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-6.0.8.jar:6.0.8]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:391) ~[spring-tx-6.0.8.jar:6.0.8]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-6.0.8.jar:6.0.8]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.8.jar:6.0.8]
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ~[spring-tx-6.0.8.jar:6.0.8]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.8.jar:6.0.8]
at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:163) ~[spring-data-jpa-3.0.5.jar:3.0.5]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.8.jar:6.0.8]
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97) ~[spring-aop-6.0.8.jar:6.0.8]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.8.jar:6.0.8]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:223) ~[spring-aop-6.0.8.jar:6.0.8]
at jdk.proxy2/jdk.proxy2.$Proxy116.findAll(Unknown Source) ~[na:na]


</details>
# 答案1
**得分**: 0
`desc`是Postgres中的保留字。
建议：
```java
@NotBlank
@Column(name = "description")
String desc;

ContentController.saveCon期望一个Content对象，但您的POST请求主体正在发送一个列表[{ ... }]。

英文:

desc is a reserved word in Postgres.

Proposal:

    @NotBlank
@Column(name = &quot;description&quot;)
String desc;

ContentController.saveCon expects one Content object, but your POST request body is sending a list [{ ... }].

答案2

得分: 0

I suppose, Staus and Type are enum. if yes, you have to use @JsonCreator in your enum as jackson couldn't use enum constructor, you can create static method instead which will lookup value from string for you. For example, put below code in your enums.

@JsonCreator
public static Status fromString(String status) {
    if(status.equalsIgnoreCase("")) {
        return null;
    }
    return Status.valueOf(status);
}

with above changes it's working for me.

英文:

I suppose, Staus and Type are enum. if yes, you have to use @JsonCreator in your enum as jackson couldn't use enum constructor, you can create static method instead which will lookup value from string for you. For example, put below code in your enums.

	@JsonCreator
public static Status fromString(String status) {
if(status.equalsIgnoreCase(&quot;&quot;)) {
return null;
}
return Status.valueOf(status);
}

with above changes it's working for me.

PostgreSQL 使用 Spring Data JPA REST API 获取/提交数据时出现错误。

huangapple
  • 本文由 发表于 2023年5月28日 21:35:25
  • 转载请务必保留本文链接：https://go.coder-hub.com/76351764.html
  • java
  • postgresql
  • spring
  • spring-boot
  • spring-data-jpa
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定