java - Spring Boot - JPA 或 SQL 查询在连接表中查找最常见的项目？

Question

我使用 Spring Boot、JPA 和 MySQL 创建一个 Spring Boot 微服务，其中包含以下实体关系:

Owner can have multiple Cars.
Cars can only have one Owner.

<小时/>

pom.xml:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.5.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.myapi</groupId>
    <artifactId>car-api</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>car-api</name>
    <description>Car REST API</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

<小时/>

所有者实体:

@Entity
@Table(name = "owner")
public class Owner extends AuditModel {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @NotNull
    private String name;


    private String address,
    private String city;
    private String state;
    private int zipCode;


    @OneToMany(cascade = CascadeType.ALL,
                fetch = FetchType.EAGER,
                mappedBy = "owner")
    private List<Car> cars = new ArrayList<>();

    public Owner() {
    }

    // Getter & Setters omitted for brevity.
}

汽车实体:

@Entity
@Table(name="car")
public class Car extends AuditModel {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    String make;
    String model;
    String year;

    @JsonIgnore
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "owner_id", nullable = false)
    private Owner owner;

    // Getter & Setters omitted for brevity.
}

<小时/>

所有者存储库:

@Repository
public interface OwnerRepository extends JpaRepository<Owner, Long> {
    @Query(value = "SELECT * FROM owner WHERE name = ?", nativeQuery = true)
    Owner findOwnerByName(String name);
}

<小时/>

汽车存储库:

@Repository
public interface CarRepository extends JpaRepository<Car, Long> {
}

<小时/>

所有者服务:

public interface OwnerService {

    List<Owner> getAllOwners();

}

<小时/>

OwnerServiceImpl:

@Service
public class OwnerServiceImpl implements OwnerService {


    @Autowired
    OwnerRepository ownerRepository;

    @Override
    public List<Owner> getAllOwners() {
        return ownerRepository.findAll();
    }
}

<小时/>

所有者 Controller :

@RestController
public class OwnerController {


    private HttpHeaders headers = null;

    @Autowired
    OwnerService ownerService;

    public OwnerController() {
        headers = new HttpHeaders();
        headers.add("Content-Type", "application/json");
    }


    @RequestMapping(value = { "/owners" }, method = RequestMethod.GET, produces = "APPLICATION/JSON")
    public ResponseEntity<Object> getAllOwners() {
        List<Owner> owners = ownerService.getAllOwners();

        if (owners.isEmpty()) {
            return new ResponseEntity<Object>(HttpStatus.NOT_FOUND);
        }
        return new ResponseEntity<Object>(owners, headers, HttpStatus.OK);
    }

}

因此，当我使用以下方式调用此端点时:

获取http://localhost:8080/car-api/owners

产生以下 JSON 有效负载:

[
    {
        "id": 1,
        "name": "Tom Brady",
        "cars": [
            {
                "id": 1,
                "make": "Honda",
                "model": "Accord",
                "year": "2020"
            },
            {
                "id": 11,
                 "make": "Nissan",
                 "model": "Maxima",
                 "year": "2019"
            },
            {

                "id": 12,
                "make": "Porsche",
                "model": "911",
                "year": "2017"
            }
        ]
    },
    {
        "id": 2,
        "name": "Kobe Bryant",
        "cars": [
            {
                "id": 2,
                "make": "Porsche",
                "model": "911",
                "year": "2017"
            }
        ]
    },
    {
        "id": 3,
        "name": "Mike Tyson",
        "cars": [
            {
                "id": 3,
                "make": "Volkswagen",
                "model": "Beatle",
                "year": "1973"
            }
        ]
    },
    {
        "id": 4,
        "name": "Scottie Pippen",
        "cars": [
            {
                "id": 4,
                "make": "Ford",
                "model": "F-150",
                "year": "2010"
            }
        ]
    },
    {
        "id": 5,
        "name": "John Madden",
        "cars": [
            {
                "id": 5,
                "make": "Chevrolet",
                "model": "Silverado",
                "year": "2020"
            }
        ]
    },
    {
        "id": 6,
        "name": "Arnold Palmer",
        "cars": [
            {
                "id": 6,
                 "make": "Toyota",
                "model": "Camary",
                "year": "2018"
            }
        ]
    },
    {
        "id": 7,
        "name": "Tiger Woods",
        "cars": [
            {
                "id": 7,
                 "make": "Alfa",
                 "model": "Romeo",
                 "year": "2017"
            }
        ]
    },
    {
        "id": 8,
        "name": "Magic Johnson",
        "cars": [
            {
                "id": 8,
                "make": "Porsche",
                "model": "911",
                "year": "2017"
            }
        ]
    },
    {
        "id": 9,
        "name": "George Foreman",
        "cars": [
            {
                "id": 9,
                "make": "Toyota",
                "model": "Camary",
                "year": "2018"
            }
        ]
    },
    {
        "id": 10,
        "name": "Charles Barkley",
        "cars": [
            {
                "id": 10,
                "make": "Alfa",
                "model": "Romeo",
                "year": "2017"
            }
        ]
    }
]

<小时/>

如您所见，汤姆·布雷迪、科比·布莱恩特、魔术师约翰逊都拥有保时捷 911...这是此列表中最常见的汽车。

<小时/>

在幕后，JPA 创建如下表:

汽车表:

CREATE TABLE `car` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `battery_capacity` int(11) NOT NULL,
  `make` varchar(255) DEFAULT NULL,
  `model` varchar(255) DEFAULT NULL,
  `year` varchar(255) DEFAULT NULL,
  `owner_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK6wxv6hdekqn26n47pb7f1dt02` (`user_id`),
  CONSTRAINT `FK6wxv6hdekqn26n47pb7f1dt02` FOREIGN KEY (`owner_id`) REFERENCES `owner` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

<小时/>

所有者表:

CREATE TABLE `owner` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `address` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `state` varchar(255) DEFAULT NULL,
  `zip_code` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

<小时/>

从所有者中选择*:

+----+----------------+
| id | name           |
+----+----------------+
|  1 | Tom Brady      |
|  2 | Kobe Bryant    |
|  3 | Mike Tyson     |
|  4 | Scottie Pippen |
|  5 | John Madden    |
|  6 | Arnold Palmer  |
|  7 | Tiger Woods    |
|  8 | Magic Johnson  |
|  9 | George Foreman |
| 10 | Charles Barkley|
+----+----------------+

<小时/>

从汽车 c 中选择 id、user_id、c.make、c.model、c.year；

+----------------+--------------+------+
| id | make      | model        | year |
+----+-----------+-----------+---------+
|  1 | Honda     | Accord       | 2020 |
|  2 | Nissan    | Leaf         | 2029 |
|  3 | Volkswagen| Beatle       | 1973 |
|  4 | Porsche   | Taycan       | 2020 |
|  5 | Ford      | F-150        | 2010 |
|  6 | Chevrolet | Silverado    | 2020 |
|  7 | Toyota    | Camry        | 2018 |
|  8 | Chevrolet | Bolt         | 2020 |
|  9 | Honda     | Clarity      | 2018 |
| 10 | Hyundai   | Ioniq        | 2017 |
| 11 | Nissan    | Maxima       | 2019 |
| 12 | Porsche   | 911          | 2020 |
+----+-----------+--------------+------+

<小时/>

我可以将 JPA 或 SQL 查询(甚至可以是命名查询)放入我的存储库中，该查询将返回最常见汽车的数据(例如 Select count(*) 和可能的 Group By 语句)，其中是否表明拥有最多的汽车是保时捷 911，因为有 3 个人拥有它们？

Answer 1

声明一个用于保存汽车及其数量的类。

public class CarStatistics {

  private final Car car;
  private final Long count;

  public CarStatistics(Car car, Long count) {
    this.car = car;
    this.count = count;
  }

  public Car getCar() {
    return car;
  }

  public Long getCount() {
    return count;
  }
}

从存储库方法返回一个 bean 实例，按计数分组和排序:

public interface CarRepository extends JpaRepository<Car, Long> {

  @Query("SELECT new com.example.CarStatistics(c, COUNT(c)) "
      + " FROM Car c "
      + " GROUP BY c.make, c.model "
      + " ORDER BY COUNT(c) DESC")
  List<CarStatistics> findCarCount();
}

调用存储库方法并获取结果集中的第一个对象:

@Service
public class CarService {

  private final CarRepository carRepository;

  public CarService(CarRepository carRepository) {
    this.carRepository = carRepository;
  }

  public Car findMostCommonCar() {
    List<CarStatistics> carStatistics = carRepository.findCarCount();
    return carStatistics.get(0).getCar();
  }
}