Java中一对多关系中的where子句是如何工作的

imzjd6km  于 12个月前  发布在  Java
关注(0)|答案(1)|浏览(132)

我有两张tableA和B。

@Table(name = "table_a")
@Data
public class A {

    @Column(name = "id")
    private Integer id;

    
    @OneToMany(mappedBy = "a", cascade = CascadeType.ALL)
    private List<B> bs;
}
@Table(name = "table_b")
@Data
public class B {

    @Column(name = "id")
    private Integer b_id;

    @Column(name = "a_id)
    private Integer a_id;
    
    @Column(name = "status")
    private Integer status;

    
    @ManyToOne
    @JoinColumn(name = "a_id", insertable = false, updatable =   false)
    private A a;
}

字符串
现在的问题是,当我尝试运行下面这个查询的JPQL版本时,

select a.* from table_a a
  inner join table_b b on a.id = b.a_id 
  where b.status = -1;


它返回所有行,即使状态不是-1,即它返回table_b中与table a的id相关联的所有行。我知道我使用它不正确,我应该怎么做才能查询table_b中存在的状态

oknrviil

oknrviil1#

我建了一个复制器,它是available at github.com

技术清理

在此期间,我注意到以下几点:
1.实体AB缺少@Entitiy-注解
1.实体AB在主键上缺少@Id注解
1.实体B中的属性a_id是多余的
这导致实体AB的以下重构:
A.java

package de.turing85.spring.join;

import com.fasterxml.jackson.annotation.JsonIgnore;
import jakarta.persistence.CascadeType;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.OneToMany;
import jakarta.persistence.Table;
import lombok.Data;

import java.util.List;

@Entity
@Table(name = "table_a")
@Data
public class A {
  @Id
  @Column(name = "id")
  private Integer id;

  @OneToMany(mappedBy = "a", cascade = CascadeType.ALL)
  @JsonIgnore // This is for testing only so Jackson does not run into an endless recursion
  private List<B> bs;
}

字符串
B.java

package de.turing85.spring.join;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.Table;
import lombok.Data;

@Entity
@Table(name = "table_b")
@Data
public class B {
  @Id
  @Column(name = "id")
  private Integer id;

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

  @ManyToOne
  @JoinColumn(name = "a_id", insertable = false, updatable = false)
  A a;
}

查询构造

有了这些变化,我们就可以解决实际问题了。JPQL(jakarta.ee)中join的语法如下:

SELECT <root-alias-name> FROM <root-type-name> <root-alias-name>
INNER JOIN <root-alias-name>.<field-to-join-on> <join-alias-name>
WHERE ...


翻译成我们的例子,这导致

SELECT a FROM A a
INNER JOIN a.bs b
WHERE ...


注意,我们写了a.bs b。我们访问一个集合,但将其命名为singular。从查询的Angular 来看,该集合对我们来说是透明的,我们只能看到单数B-实体(想象查询是一个循环,对bs中的每个B执行)。
有了这些知识,我们可以用公式表示WHERE条件,然后简单明了:

SELECT a FROM A a
INNER JOIN a.bs b
WHERE b.status = -1


我们现在可以在ARepository中定义这个查询:

package de.turing85.spring.join;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface ARepository extends JpaRepository<A, Integer> {
  @Query("""
      SELECT a FROM A a 
      INNER JOIN a.bs b
      WHERE b.status = -1
      """)
  List<A> findByBStatusMinusOne();
}

测试

使用Controller.java访问实体:

package de.turing85.spring.join;

import lombok.RequiredArgsConstructor;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping(Controller.PATH)
@RequiredArgsConstructor
public class Controller {
  public static final String PATH = "as";
  private final ARepository repository;

  @GetMapping
  List<A> getAllAs() {
    return repository.findAll();
  }

  @GetMapping(path = "bMinusOne")
  List<A> getAllAsWithBMinusOne() {
    return repository.findByBStatusMinusOne();
  }
}


一些测试数据:

DELETE FROM table_b;
DELETE FROM table_a;

INSERT INTO table_a(id)
VALUES
    (0),
    (1),
    (2);
INSERT INTO table_b(id, status, a_id)
VALUES
    (0, -1, 0),
    (1, 0, 1),
    (2, -1, 1),
    (3, 0, 2);


我们现在可以运行./mvnw clean package来运行集成测试:
ControllerTest.java

package de.turing85.spring.join;

import io.restassured.RestAssured;
import org.junit.jupiter.api.DisplayName;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.test.web.server.LocalServerPort;
import org.springframework.boot.testcontainers.service.connection.ServiceConnection;
import org.springframework.http.HttpStatus;
import org.springframework.test.context.jdbc.Sql;
import org.testcontainers.containers.PostgreSQLContainer;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;

import static org.hamcrest.Matchers.*;

@Testcontainers
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
@DisplayName("Controller Tests")
@Sql({ "/data.sql" })
class ControllerTest {

  @LocalServerPort
  private Integer port;

  @Container
  @ServiceConnection
  static PostgreSQLContainer<?> postgreSQLContainer = new PostgreSQLContainer<>("postgres:16.1-alpine3.19");

  @Test
  @DisplayName("Get All A's")
  void getAll() {
    // @formatter: off
    RestAssured
        .when().get("http://localhost:%d/%s".formatted(port, Controller.PATH))
        .then().assertThat()
            .statusCode(HttpStatus.OK.value())
            .body("size()", is(3))
            .body("collect { it.id }", hasItems(0, 1, 2));
    // @formatter: on
  }

  @Test
  @DisplayName("Get All A's with at least one B in status -1")
  void getBMinusOne() {
    // @formatter: off
    RestAssured
        .when().get("http://localhost:%d/%s/bMinusOne".formatted(port, Controller.PATH))
        .then().assertThat()
            .statusCode(HttpStatus.OK.value())
            .body("size()", is(2))
            .body("collect { it.id }", hasItems(0, 1));
    // @formatter: on
  }
}


注意,集成测试使用了testcontainers,因此需要一个与docker或docker兼容的环境来执行。

备注

我将database-entity 1:1传播到rest-endpoint。这样做是为了简洁。特别注意,我必须在A@JsonIgnorebs。如果我们不这样做,Jackson将由于双向关系而陷入无限递归。对于生产级解决方案,我建议隔离数据库和响应实体。

相关问题