使用 Spring Data JPA 查找最大值_AI阅读总结 — 包阅AI

When working with Spring Data JPA, retrieving specific values from a database is a common requirement. One such requirement is finding the maximum value in a particular column. Let us delve into understanding the different approaches to finding the max value in Spring Data JPA

• Using Derived Queries in a Repository
• Using JPQL
• Using a Native Query
• Implementing a Default Repository Method
• Using Criteria API

1.Introduction

Spring Data JPA is a part of the larger Spring Data family, which aims to simplify the implementation of data access layers by reducing the amount of boilerplate code required. It provides a consistent approach to data access while supporting a wide range of relational and non-relational databases. Spring Data JPA specifically focuses on JPA (Java Persistence API) as the persistence standard. It offers powerful abstractions and repository support, allowing developers to easily create, read, update, and delete records without writing explicit SQL queries.

2. Setting up a database on Docker

Usually, setting up the database is a tedious step but with Docker, it is a simple process. You can watch the video available at this link to understand the Docker installation on Windows OS. Once done open the terminal and trigger the below command to set and run postgresql.

-- Remember to change the password –docker run -d -p 5432:5432 -e POSTGRES_PASSWORD= --name postgres postgres-- command to stop the Postgres docker container --docker stop postgres-- command to remove the Postgres docker container --docker rm postgres

Remember to enter the password of your choice. If everything goes well the postgresql database server will be up and running on a port number – 5432 and you can connect with the Dbeaver GUI tool for connecting to the server.

2.1 Setting up pre-requisite data

To proceed further with the tutorial we will set up the required mock data in the postgresql.

drop table product;create table product (id serial primary key, name varchar(255) not null, price numeric(10, 2) not null);select * from product;

3. Code Example

3.1 Dependencies

Add the following dependencies to your build.gradle file or if you have created a spring project from start.spring.io this won’t be necessary as the file will be automatically populated with the dependencies information.

plugins {    id 'java'    id 'org.springframework.boot' version '3.3.2'    id 'io.spring.dependency-management' version '1.1.6'}group = 'jcg'version = '0.0.1-SNAPSHOT'java {    toolchain {        languageVersion = JavaLanguageVersion.of(17)    }}repositories {    mavenCentral()}dependencies {    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'    developmentOnly 'org.springframework.boot:spring-boot-devtools'    runtimeOnly 'org.postgresql:postgresql'    testImplementation 'org.springframework.boot:spring-boot-starter-test'    testRuntimeOnly 'org.junit.platform:junit-platform-launcher'}tasks.named('test') {    useJUnitPlatform()}

3.2 Configure application and database properties

Add the following properties to the application.properties file present in the resources folder.

spring.application.name=springjpafindmaxspring.main.banner-mode=off# Database configurationspring.datasource.url=jdbc:postgresql://localhost:5432/some_database_namespring.datasource.username=some_userspring.datasource.password=some_passwordspring.datasource.driver-class-name=org.postgresql.Driver# JPA/Hibernate configurationspring.jpa.hibernate.ddl-auto=updatespring.jpa.show-sql=truespring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect# SQL logginglogging.level.org.hibernate.SQL=DEBUGlogging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

• Database Configuration:
  • spring.datasource.url: URL for connecting to the PostgreSQL database. Replace localhost:5432/mydatabase with your database URL.
  • spring.datasource.username: Username for the PostgreSQL database.
  • spring.datasource.password: Password for the PostgreSQL database.
  • spring.datasource.driver-class-name: The JDBC driver class for PostgreSQL.
• JPA/Hibernate Configuration:
  • spring.jpa.hibernate.ddl-auto: Controls the schema generation. update ensures that Hibernate will only update the schema without dropping it. Other options include create, create-drop, and validate.
  • spring.jpa.show-sql: Enables logging of SQL statements executed by Hibernate.
  • spring.jpa.properties.hibernate.dialect: Specifies the SQL dialect for PostgreSQL.
• SQL Logging:
  • logging.level.org.hibernate.SQL: Logs SQL statements executed by Hibernate.
  • logging.level.org.hibernate.type.descriptor.sql.BasicBinder: Logs SQL parameter values bound to SQL statements.

3.3 Create the Model Class

Create a User entity class to interact with the JpaRepository interface and perform the SQL operations.

@Entitypublic class Product {    @Id    @GeneratedValue(strategy = GenerationType.IDENTITY)    private Long id;    private String name;    private Double price;    // Getters and Setters}

3.4 Creating the Data Interaction Layer – JPA Implementation

Create a repository interface to interact with the Product entity for interacting with the SQL table via the JpaRepository interface and perform the SQL operations.

• The method findTopByOrderByPriceDesc is a derived query method that fetches the product with the highest price. The Optional wrapper is used to handle the case where no products are found. In Spring Data JPA, derived query methods are created by defining method names in the repository interface that follows a specific naming convention. These method names are then parsed by Spring Data JPA to generate the appropriate query automatically.
• The findMaxPriceProduct method selects the product with the maximum price. The subquery (SELECT MAX(p2.price) FROM Product p2) finds the maximum price, and the main query selects the product with that price.
• The findMaxPriceProductNative method uses a native SQL query to find the product with the maximum price. The nativeQuery = true attribute indicates that this is a native SQL query. Since I’m not using the @Table annotation in the Entity class, I created the query based on the entity name instead of the table name.

Please note that this interface does not require an implementation class, as Spring handles it automatically.

@Repositorypublic interface ProductRepository extends JpaRepository<Product, Long> {    //Using derived query    Optional<Product> findTopByOrderByPriceDesc();    //Using JPQL    @Query("SELECT p FROM Product p WHERE p.price = (SELECT MAX(p2.price) FROM Product p2)")    Product findMaxPriceProduct();    //Using native query    @Query(value = "SELECT * FROM Product p WHERE p.price = (SELECT MAX(p2.price) FROM Product p2)", nativeQuery = true)    Product findMaxPriceProductNative();}

3.5 Creating the Custom Data Interaction Layer – Entity Manager & Criteria API

We’ll define a custom repository interface and provide its implementation. The implementation class will consist of the following methods-

• The custom method uses the EntityManager to execute a JPQL query that finds the product with the maximum price.
• The Criteria API allows us to build queries programmatically. We create a subquery to find the maximum price and then use it in the main query to find the product with that price.

Please note that the interface is omitted for brevity, as it only includes the method declarations.

@Repositorypublic class ProductRepositoryCustomImpl implements ProductRepositoryCustom {    @PersistenceContext    private EntityManager entityManager;    @Override    public Product findMaxPriceProduct() {        String query = "SELECT p FROM Product p WHERE p.price = (SELECT MAX(p2.price) FROM Product p2)";        return entityManager.createQuery(query, Product.class).getSingleResult();    }    @Override    public Product findMaxPriceProductWithCriteriaAPI() {        CriteriaBuilder cb = entityManager.getCriteriaBuilder();        CriteriaQuery<Product> cq = cb.createQuery(Product.class);        Root<Product> product = cq.from(Product.class);        Subquery<Double> subquery = cq.subquery(Double.class);        Root<Product> subProduct = subquery.from(Product.class);        subquery.select(cb.max(subProduct.get("price")));        cq.where(cb.equal(product.get("price"), subquery));        return entityManager.createQuery(cq).getSingleResult();    }}

3.6 Create the Main Class

The SpringjpafindmaxApplication class is the main entry point of a Spring Boot application that demonstrates how to find the maximum value in a specific column using different methods with Spring Data JPA. The class is annotated with @SpringBootApplication, which signifies it is a Spring Boot application. This class implements CommandLineRunner, allowing it to execute additional code after the Spring Boot application starts.

The ProductRepository and ProductRepositoryCustom interfaces are autowired into the class via constructor injection, promoting immutability and easier testing.

The main method launches the Spring Boot application by calling SpringApplication.run with the application class and command-line arguments. In the run method, a list of Product objects is created and populated with sample data. Each Product has a name and a price, and these products are saved to the database using the productRepository.saveAll method.

The maximum price of the products is then retrieved using various methods:

• Derived Query: The method findTopByOrderByPriceDesc is used to find the product with the highest price. If a product is found, its price is printed.
• JPQL: The findMaxPriceProduct method uses Java Persistence Query Language (JPQL) to find the product with the maximum price, and its price is printed.
• Native Query: The findMaxPriceProductNative method uses a native SQL query to find the product with the highest price, and its price is printed.
• Custom Repository Method: The findMaxPriceProduct method from ProductRepositoryCustom is used to find the product with the highest price using a custom implementation, and its price is printed.
• Criteria API: The findMaxPriceProductWithCriteriaAPI method from ProductRepositoryCustom uses the Criteria API to find the product with the maximum price, and its price is printed.

@SpringBootApplicationpublic class SpringjpafindmaxApplication implements CommandLineRunner {    private final ProductRepository productRepository;    private final ProductRepositoryCustom productRepositoryCustom;    //Doing constructor injection.    @Autowired    public SpringjpafindmaxApplication(ProductRepository pr, ProductRepositoryCustom prc) {        this.productRepository = pr;        this.productRepositoryCustom = prc;    }    public static void main(String[] args) {        SpringApplication.run(SpringjpafindmaxApplication.class, args);    }    @Override    public void run(String... args) throws Exception {        //Create some products        List<Product> products = new ArrayList<>();        for (int i = 0; i < 5; i++) {            Product product = new Product();            product.setName("Product " + i);            product.setPrice((double) (i * 100));            products.add(product);        }        productRepository.saveAll(products);        //Using derived query        productRepository.findTopByOrderByPriceDesc()                .ifPresent(p -> System.out.println("Derived Query Max Price: " + p.getPrice()));        //Using jpql        Product maxPriceProductJPQL = productRepository.findMaxPriceProduct();        System.out.println("JPQL Max Price: " + maxPriceProductJPQL.getPrice());        //Using Native Query        Product maxPriceProductNative = productRepository.findMaxPriceProductNative();        System.out.println("Native Query Max Price: " + maxPriceProductNative.getPrice());        //Using Default Repository Method        Product maxPriceProductCustom = productRepositoryCustom.findMaxPriceProduct();        System.out.println("Custom Repository Max Price: " + maxPriceProductCustom.getPrice());        // Using Criteria API        Product maxPriceProductCriteria = productRepositoryCustom.findMaxPriceProductWithCriteriaAPI();        System.out.println("Criteria API Max Price: " + maxPriceProductCriteria.getPrice());    }}

3.7 Run the application

Run your Spring Boot application and the application will be started on a port number specified in the application properties file. As soon as the application is started the following logs showing the output of various DAO methods will be shown on the IDE console.

2024-08-05T10:15:44.838+05:30 DEBUG 23068 --- [springjpafindmax] [  restartedMain] org.hibernate.SQL                        : alter table if exists product alter column id set data type bigintHibernate: alter table if exists product alter column id set data type bigint2024-08-05T10:15:44.861+05:30 DEBUG 23068 --- [springjpafindmax] [  restartedMain] org.hibernate.SQL                        : alter table if exists product alter column price set data type float(53)Hibernate: alter table if exists product alter column price set data type float(53)...2024-08-05T10:15:47.146+05:30  INFO 23068 --- [springjpafindmax] [  restartedMain] j.s.SpringjpafindmaxApplication          : Started SpringjpafindmaxApplication in 10.091 seconds (process running for 12.377)2024-08-05T10:15:47.247+05:30 DEBUG 23068 --- [springjpafindmax] [  restartedMain] org.hibernate.SQL                        : insert into product (name,price) values (?,?) returning idHibernate: insert into product (name,price) values (?,?) returning id2024-08-05T10:15:47.334+05:30 DEBUG 23068 --- [springjpafindmax] [  restartedMain] org.hibernate.SQL                        : insert into product (name,price) values (?,?) returning idHibernate: insert into product (name,price) values (?,?) returning id2024-08-05T10:15:47.337+05:30 DEBUG 23068 --- [springjpafindmax] [  restartedMain] org.hibernate.SQL                        : insert into product (name,price) values (?,?) returning idHibernate: insert into product (name,price) values (?,?) returning id2024-08-05T10:15:47.339+05:30 DEBUG 23068 --- [springjpafindmax] [  restartedMain] org.hibernate.SQL                        : insert into product (name,price) values (?,?) returning idHibernate: insert into product (name,price) values (?,?) returning id2024-08-05T10:15:47.341+05:30 DEBUG 23068 --- [springjpafindmax] [  restartedMain] org.hibernate.SQL                        : insert into product (name,price) values (?,?) returning idHibernate: insert into product (name,price) values (?,?) returning id...2024-08-05T10:15:47.604+05:30 DEBUG 23068 --- [springjpafindmax] [  restartedMain] org.hibernate.SQL                        : select p1_0.id,p1_0.name,p1_0.price from product p1_0 order by p1_0.price desc fetch first ? rows onlyHibernate: select p1_0.id,p1_0.name,p1_0.price from product p1_0 order by p1_0.price desc fetch first ? rows onlyDerived Query Max Price: 400.02024-08-05T10:15:47.642+05:30 DEBUG 23068 --- [springjpafindmax] [  restartedMain] org.hibernate.SQL                        : select p1_0.id,p1_0.name,p1_0.price from product p1_0 where p1_0.price=(select max(p2_0.price) from product p2_0)Hibernate: select p1_0.id,p1_0.name,p1_0.price from product p1_0 where p1_0.price=(select max(p2_0.price) from product p2_0)JPQL Max Price: 400.02024-08-05T10:15:47.765+05:30 DEBUG 23068 --- [springjpafindmax] [  restartedMain] org.hibernate.SQL                        : SELECT * FROM Product p WHERE p.price = (SELECT MAX(p2.price) FROM Product p2)Hibernate: SELECT * FROM Product p WHERE p.price = (SELECT MAX(p2.price) FROM Product p2)Native Query Max Price: 400.02024-08-05T10:15:47.789+05:30 DEBUG 23068 --- [springjpafindmax] [  restartedMain] org.hibernate.SQL                        : select p1_0.id,p1_0.name,p1_0.price from product p1_0 where p1_0.price=(select max(p2_0.price) from product p2_0)Hibernate: select p1_0.id,p1_0.name,p1_0.price from product p1_0 where p1_0.price=(select max(p2_0.price) from product p2_0)Custom Repository Max Price: 400.02024-08-05T10:15:47.796+05:30 DEBUG 23068 --- [springjpafindmax] [  restartedMain] org.hibernate.SQL                        : select p1_0.id,p1_0.name,p1_0.price from product p1_0 where p1_0.price=(select max(p2_0.price) from product p2_0)Hibernate: select p1_0.id,p1_0.name,p1_0.price from product p1_0 where p1_0.price=(select max(p2_0.price) from product p2_0)Criteria API Max Price: 400.0

4. Conclusion

We have explored various approaches to finding the maximum value in Spring JPA. Each method has its use case:

• Derived Queries: Simple and concise for basic queries.
• JPQL: Flexible and supports complex queries.
• Native Queries: Direct SQL for performance-critical operations.
• Custom Repository Methods: Custom logic and complex query handling.
• Criteria API: Type-safe and dynamic query construction.

Choosing the right approach depends on the specific requirements and complexity of the query you need to execute.

5. Download the source code