MariaDB请求在我自定义端点上出现错误

wpcxdonn  于 2023-10-20  发布在  其他
关注(0)|答案(3)|浏览(132)

我想Map两个列,所以我创建了一个Java类(pojo),其中包含两个元素(id和test1)

package com.rpImmo.BackServer.payload.request;

import com.fasterxml.jackson.annotation.JsonProperty;

import jakarta.persistence.Column;

public class TestRequestDB {
    @JsonProperty("id")
    @Column(name = "`id`")
    private Long id;

    @JsonProperty("test1")
    @Column(name = "`test1`")
    private String test1;

    public TestRequestDB(Long id, String test1) {
        this.id = id;
        this.test1 = test1;
    }

    public Long getId() {
        return id;
    }

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

    public String getTest1() {
        return test1;
    }

    public void setTest1(String test1) {
        this.test1 = test1;
    }
    
}

之后,我用我的请求创建了一个新的仓库:

@Query(value = "SELECT new com.rpImmo.BackServer.payload.request.TestRequestDB(t.id, t.test1) FROM test t WHERE id = 1", nativeQuery = true)
    List<TestRequestDB> getTestV2();

我的控制器:

@GetMapping("/findAllV2")
    ResponseEntity<List<TestRequestDB>> findAllAnnounceV2() {
        List<TestRequestDB> response_ = testRepository.getTestV2();
        if(response_.size() > 0)
            return new ResponseEntity<>(response_, HttpStatus.OK);
        return new ResponseEntity<>(response_, HttpStatus.NOT_FOUND);
    }

错误:

2023-08-14T18:00:10.739+02:00  WARN 23742 --- [nio-8080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1064, SQLState: 42000
2023-08-14T18:00:10.739+02:00 ERROR 23742 --- [nio-8080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : (conn=147) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.rpImmo.BackServer.payload.request.TestRequestDB(t.id, t.test1) FROM test t W...' at line 1
2023-08-14T18:00:10.756+02:00 ERROR 23742 --- [nio-8080-exec-3] o.a.c.c.C.[.[.[.[dispatcherServlet]      : Servlet.service() for servlet [dispatcherServlet] in context with path [/api_dev] threw exception [Request processing failed: org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [SELECT new com.rpImmo.BackServer.payload.request.TestRequestDB(t.id, t.test1) FROM test t WHERE id = 1] [(conn=147) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.rpImmo.BackServer.payload.request.TestRequestDB(t.id, t.test1) FROM test t W...' at line 1] [n/a]; SQL [n/a]] with root cause

java.sql.SQLSyntaxErrorException: (conn=147) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.rpImmo.BackServer.payload.request.TestRequestDB(t.id, t.test1) FROM test t W...' at line 1

但对我来说,这个请求是可以的,因为在终端上它工作正常

MariaDB [backServerRpImmo]> select t.id, t.test1 from test t where id = 1;
+----+-------+
| id | test1 |
+----+-------+
|  1 | 1     |
+----+-------+
1 row in set (0,000 sec)

但我不能得到一个战利品在一个解决方案在1周的(后我的工作日),但我不能修复它...你能帮我吗?如果你还需要什么别犹豫
ps:它是maven 3.1.2和java 17
做一个端点Ir的测试,但我想Map数据库上的表数据,使一些请求不同的默认情况下

**编辑

我已经在MariaDB上激活了请求的日志跟踪,我使用了另一个正常工作的项目:范例:
@Query(value = "SELECT new com.backEnd.coachingval.coachingval.model.Products(p.uuid, p.name, p.optn, p.price, p.ref, p.plans, p.promo, p.pricePromo) from Products p WHERE p.plans.id=:id ORDER BY p.name") List<Products> getProductsByID(@Param(value = "id") int id);
没有nativeQuery = true,MariaDb日志上的请求是好的:
select products0_.uuid as col_0_0_, products0_.name as col_1_0_, products0_.optn as col_2_0_, products0_.price as col_3_0_, products0_.ref as col_4_0_, products0_.plans_id as col_5_0_, products0_.promo as col_6_0_, products0_.price_promo as col_7_0_ from products products0_ inner join plans plans1_ on products0_.plans_id=plans1_.id where products0_.plans_id=1 order by products0_.name
请求是好的,但是当添加nativeQuery = true时,在mariaDB日志上获得此请求:
SELECT new com.backEnd.coachingval.coachingval.model.Products(p.uuid, p.name, p.optn, p.price, p.ref, p.plans, p.promo, p.pricePromo) from Products p WHERE p.plans.id=1 ORDER BY p.name
所以我需要删除nativeQuery = true,但在我的实际项目中,当删除nativeQuery = true时,我会得到这个错误,我不知道如何修复它
Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'testController' defined in file [/home/kevin/Desktop/rpImmo/back_RPimmo/target/classes/com/rpImmo/BackServer/controllers/TestController.class]: Unsatisfied dependency expressed through constructor parameter 0: Error creating bean with name 'testRepository' defined in com.rpImmo.BackServer.repository.TestRepository defined in @EnableJpaRepositories declared on JpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: Could not create query for public abstract com.rpImmo.BackServer.payload.request.TestRequestDB com.rpImmo.BackServer.repository.TestRepository.getTestV2(); Reason: Validation failed for query for method public abstract com.rpImmo.BackServer.payload.request.TestRequestDB com.rpImmo.BackServer.repository.TestRepository.getTestV2()
有人能帮我吗,谢谢

fkaflof6

fkaflof61#

先生
我认为你不想要这部分:
new com.rpImmo.BackServer.payload.request.TestRequestDB(t.id,t.test1)
nativeQuery = true意味着您正在尝试执行原生SQL来使您的pojo水合,因此您正在执行的测试与JPA正在执行的SQL不同。
沿着这几行,考虑注销SQL,看看到底执行了什么。

7eumitmz

7eumitmz2#

您正在尝试对本机查询使用基于JPQL类的投影。这是不支持的。本机查询使用DBMS的SQL语法,“SELECT NEW...”不是有效的SQL。关于基于类的预测的文档说:
需要指出的是,基于类的投影根本不适用于原生查询。
您需要使用非本机查询。当你尝试时,你得到了一个不同的错误。这个错误与JPA无关,但它是一个Spring错误,说它找不到合适的bean来注入你的方法。您应该查看Spring文档以理解错误的含义并定义一个合适的bean。

jjjwad0x

jjjwad0x3#

我认为你应该从查询中删除包名。还要验证JPA查询文档。您的查询是SQL和JPA查询语言的混合。
尝试删除新的和包。同时从列中删除投影。
https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.query-streaming
为此,您应该在@Repository中实现一个简单的方法。
范例:

List<TestRequestDB> findAllTestRequestDBById(Long id)

相关问题