Retrieve million data: JdbcTemplate | Hibernate 6 | Spring Data JPA

Introduction:

In this post, I’m try to summarize the most common data access layers that can help you retrieve million (10M) data from database with JdbcTemplate or Hibernate 6 or Spring Data JPA .

Considerations:

• Optimizing the number of database roundtrips via fetch Size property of the JDBC Statement or Prepared Statement objects. In oracle the default fetch size is just 10, meaning that fetching 100 records requires 10 roundtrips whereas PostgreSQL or MySQL, you don’t have to worry about the fetch size since the JDBC driver caches the entire result set up front.

• jdbc.fetch-size for queries that return a large number of objects, you can configure the row fetch size used in the query to improve performance by reducing the number database hits required to satisfy the selection criteria.
• By default, most JDBC drivers use a fetch size of 10. , so if you are reading 1000 objects, increasing the fetch size to 256 can significantly reduce the time required to fetch the query's results. The optimal fetch size is not always obvious. Usually, a fetch size of one half or one quarter of the total expected result size is optimal.
• If you are unsure of the result set size, incorrectly setting a fetch size too large or too small can decrease performance.

• To prevent java.lang.OutOfMemoryError: Java heap space, in this post, Hibernate and Spring Data JPA retrieve per batch. however for JdbcTemplate is not needed with 10M, so if data volume increases then it should work too per batch.

• In the case Spring Data JPA retrieves the data with Stream, hints to Hibernate that the entity will be read only and Pageable, however Spring Data JPA support PageRequest.of(0, 1M), when I tried to get second PageRequest.of(1M, 2M) and third etc. display next error  Page offset exceeds Integer.MAX_VALUE, for that I created *RepositoryCustom, where to used  the EntityManagerFactory is the Jakarta Persistence equivalent of a SessionFactory and basically, those two converge into the same SessionFactory implementation.
• In the end post, I added the reference if you want to know more concepts about tuning.

Downloading and installing MySQL

docker pull mysql/mysql-server:latest

docker run -d -p  3306:3306  --name mysql-docker-container -e MYSQL_ROOT_PASSWORD=mypass -e MYSQL_DATABASE=test_db -e MYSQL_USER=test -e MYSQL_PASSWORD=test_pass mysql/mysql-server:latest

Script
CREATE TABLE customers
  (
     id   BIGINT PRIMARY KEY,
     name VARCHAR(128)
  ); 


Check the reference, I adaptive to 10M.
  
INSERT INTO customers (id, name)
SELECT n, CONCAT('Customer', n)
  FROM
(
select a.N + b.N * 10 + c.N * 100 + d.N * 1000 + e.N * 10000 + f.N * 100000 + g.N * 1000000 + 1 N
from (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) a
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) b
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) c
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) d
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) e
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) f
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) g
) t
 

Demos:

• JdbcTemplate 

Spring boot 3 with Spring JDBC 6 and MySQL 8 Docker image.

Technology

• Spring Boot 3.0.0
• Spring JDBC 6
• Java 17
• Docker
• Maven 
• IntelliJ IDEA

Configuration Spring Boot project 

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>3.0.0</version>
      <relativePath/> <!-- lookup parent from repository -->
   </parent>
   <groupId>com.henry</groupId>
   <artifactId>large-data-JdbcTemplate</artifactId>
   <version>0.0.1-SNAPSHOT</version>
   <name>large-data-JdbcTemplate</name>
   <description>Demo project for Spring Boot, large data jdbc template</description>
   <properties>
      <java.version>17</java.version>
   </properties>
   <dependencies>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-web</artifactId>
      </dependency>
      <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
      <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-jdbc</artifactId>
         <version>6.0.3</version>
      </dependency>
      <dependency>
         <groupId>org.apache.commons</groupId>
         <artifactId>commons-dbcp2</artifactId>
      </dependency>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-devtools</artifactId>
         <scope>runtime</scope>
         <optional>true</optional>
      </dependency>
      <dependency>
         <groupId>org.projectlombok</groupId>
         <artifactId>lombok</artifactId>
         <optional>true</optional>
      </dependency>
      <!-- Mysql Connector -->
      <dependency>
         <groupId>mysql</groupId>
         <artifactId>mysql-connector-java</artifactId>
         <version>8.0.30</version>
      </dependency>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-test</artifactId>
         <scope>test</scope>
      </dependency>

   </dependencies>

   <build>
      <plugins>
         <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
            <configuration>
               <excludes>
                  <exclude>
                     <groupId>org.projectlombok</groupId>
                     <artifactId>lombok</artifactId>
                  </exclude>
               </excludes>
            </configuration>
         </plugin>
      </plugins>
   </build>

</project>

db.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test_db?allowPublicKeyRetrieval=true
dbuser=test
dbpassword=test_pass

Config class

package com.henry.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;

@Configuration
@ComponentScan("com.henry")
@PropertySource("classpath:db.properties")
public class AppConfig {

    private final Environment environment;
    private final String URL = "url";
    private final String USER = "dbuser";
    private final String DRIVER = "driver";
    private final String PASSWORD = "dbpassword";

    public AppConfig(Environment environment) {
        this.environment = environment;
    }

    @Bean
    DataSource dataSource() {
        DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
        driverManagerDataSource.setUrl(environment.getProperty(URL));
        driverManagerDataSource.setUsername(environment.getProperty(USER));
        driverManagerDataSource.setPassword(environment.getProperty(PASSWORD));
        driverManagerDataSource.setDriverClassName(environment.getProperty(DRIVER));
        return driverManagerDataSource;
    }
}

Model

package com.henry.model;

import lombok.*;

@Setter
@Getter
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class Customer {

    private int id;
    private String name;

}

Repository

CustomerDAO 

package com.henry.dao;

public interface CustomerDAO {
    int findAll();
}
CustomerDAOImpl
package com.henry.dao.impl;

import com.henry.dao.CustomerDAO;
import com.henry.model.Customer;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

@Component
public class CustomerDAOImpl implements CustomerDAO {

    private final JdbcTemplate jdbcTemplate;

    public CustomerDAOImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public int findAll() {
        long start = System.nanoTime();

        String sql = """
                      SELECT  * from customers
                      """ ;
        //jdbcTemplate.setFetchSize(1000); with Oracle increase to performance
        var list = jdbcTemplate.query(sql,   (rs, rowNum) ->
                Customer.builder()
                        .id(rs.getInt("id"))
                        .name(rs.getString("name"))
                        .build()
        );
        long duration = (System.nanoTime() - start) / 1_000_000;
        System.out.println("millis " + duration);
       return list.size();
    }

}

Test Class
package com.henry;

import com.henry.config.AppConfig;
import com.henry.dao.CustomerDAO;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;

public class App1 {
    public static void main(String[] args) {
        AnnotationConfigApplicationContext context = new AnnotationConfigApplicationContext(AppConfig.class);

        CustomerDAO customerDAO = context.getBean(CustomerDAO.class);

        System.out.println("customerDAO "+customerDAO.findAll());

    }
}

Run & Test

17:06:54.998 [main] DEBUG org.springframework.beans.factory.support.DefaultListableBeanFactory - Creating shared instance of singleton bean 'org.springframework.boot.autoconfigure.web.client.RestTemplateAutoConfiguration'
17:06:55.010 [main] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing SQL query [SELECT  * from customers
]
17:06:55.013 [main] DEBUG org.springframework.jdbc.datasource.DataSourceUtils - Fetching JDBC Connection from DataSource
17:06:55.013 [main] DEBUG org.springframework.jdbc.datasource.DriverManagerDataSource - Creating new JDBC DriverManager Connection to [jdbc:mysql://localhost:3306/test_db?allowPublicKeyRetrieval=true]
millis 7764
list 10000000

Process finished with exit code 0

• Hibernate 6 with  Jakarta 3.1 

Maven project with Hibernate 6, Jakarta 3.1 and MySQL 8 Docker image.

Technology

• Hibernate 6
• Jakarta 3.1
• Java 17
• Docker
• Maven 

Configuration maven project 

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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.henry</groupId>
  <artifactId>large-data-hibernate6</artifactId>
  <version>1.0-SNAPSHOT</version>

  <name>large-data-hibernate6</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>17</maven.compiler.source>
    <maven.compiler.target>17</maven.compiler.target>
    <java.version>17</java.version>
  </properties>

  <dependencies>

    <!-- Mysql Connector -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.30</version>
    </dependency>
    <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate-core</artifactId>
      <version>6.1.6.Final</version>
    </dependency>
    <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate-c3p0</artifactId>
      <version>6.1.6.Final</version>
    </dependency>
    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.24</version>
      <scope>provided</scope>
    </dependency>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.13.2</version>
      <scope>test</scope>
    </dependency>
  </dependencies>

  <build>
    <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
      <plugins>
        <!-- clean lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#clean_Lifecycle -->
        <plugin>
          <artifactId>maven-clean-plugin</artifactId>
          <version>3.1.0</version>
        </plugin>
        <!-- default lifecycle, jar packaging: see https://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_jar_packaging -->
        <plugin>
          <artifactId>maven-resources-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-compiler-plugin</artifactId>
          <version>3.8.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-surefire-plugin</artifactId>
          <version>2.22.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-jar-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-install-plugin</artifactId>
          <version>2.5.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-deploy-plugin</artifactId>
          <version>2.8.2</version>
        </plugin>
        <!-- site lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#site_Lifecycle -->
        <plugin>
          <artifactId>maven-site-plugin</artifactId>
          <version>3.7.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-project-info-reports-plugin</artifactId>
          <version>3.0.0</version>
        </plugin>
      </plugins>
    </pluginManagement>
  </build>
</project>

persistence.xml

<persistence xmlns="https://jakarta.ee/xml/ns/persistence"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xsi:schemaLocation="https://jakarta.ee/xml/ns/persistence https://jakarta.ee/xml/ns/persistence/persistence_3_1.xsd"
          version="3.1">

   <persistence-unit name="persistence">
      <description>Hibernate Entity Manager Example</description>
      <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>

      <properties>
         <property name="jakarta.persistence.jdbc.driver" value="com.mysql.cj.jdbc.Driver" />
         <property name="jakarta.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/test_db?allowPublicKeyRetrieval=true" />
         <property name="jakarta.persistence.jdbc.user" value="test" />
         <property name="jakarta.persistence.jdbc.password" value="test_pass" />
         <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
         <property name="hibernate.show_sql" value="true" />
         <!--<property name="hibernate.jdbc.fetch_size" value="1000" />-->
      </properties>

   </persistence-unit>

</persistence>

Model

package com.henry.model;

import jakarta.persistence.*;
import lombok.*;

@Setter
@Getter
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Entity(name = "customers")
public class Customer {
    @Id
    private int id;
    private String name;

}

Test Class

Per batch to prevent java.lang.OutOfMemoryError: Java heap space

package com.henry;

import com.henry.model.Customer;
import jakarta.persistence.EntityManager;
import jakarta.persistence.EntityManagerFactory;
import jakarta.persistence.Persistence;

public class App2
{
    public static void main( String[] args )
    {
        EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("persistence");
        EntityManager entityManager = entityManagerFactory.createEntityManager();

        System.out.println("Starting Transaction");
        entityManager.getTransaction().begin();

        long start = System.nanoTime();

        int i=0;
        int counter=0;
        int batch = 1000000; // per batch
        var  list = entityManager
                .createQuery("SELECT e FROM customers e", Customer.class)
                //.setHint("org.hibernate.fetchSize", 1000)
                .setFirstResult(i)
                .setMaxResults(batch)
                .getResultList();

        counter = list.size();

        while(list.size() == batch){
            i+=batch;
            list = entityManager
                    .createQuery("SELECT e FROM customers e", Customer.class)
                    //.setHint("org.hibernate.fetchSize", 1000)
                    .setFirstResult(i)
                    .setMaxResults(batch)
                    .getResultList();
            counter=counter + list.size();
        }


        System.out.println("list "+counter);

        long duration = (System.nanoTime() - start) / 1_000_000;
        System.out.println("millis " + duration);

        // close the entity manager
        entityManager.close();
        entityManagerFactory.close();

    }
}

Run & Test

INFO: HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]
Starting Transaction
Hibernate: select c1_0.id,c1_0.name from customers c1_0 limit ?,?
Hibernate: select c1_0.id,c1_0.name from customers c1_0 limit ?,?
Hibernate: select c1_0.id,c1_0.name from customers c1_0 limit ?,?
Hibernate: select c1_0.id,c1_0.name from customers c1_0 limit ?,?
Hibernate: select c1_0.id,c1_0.name from customers c1_0 limit ?,?
Hibernate: select c1_0.id,c1_0.name from customers c1_0 limit ?,?
Hibernate: select c1_0.id,c1_0.name from customers c1_0 limit ?,?
Hibernate: select c1_0.id,c1_0.name from customers c1_0 limit ?,?
Hibernate: select c1_0.id,c1_0.name from customers c1_0 limit ?,?
Hibernate: select c1_0.id,c1_0.name from customers c1_0 limit ?,?
Hibernate: select c1_0.id,c1_0.name from customers c1_0 limit ?,?
list 10000000
millis 79857

Process finished with exit code 0

• Spring Data JPA 

Spring boot 3 with Spring Data JPA and MySQL 8 Docker image.

Technology

• Spring Boot 3.0.1
• Spring Data JPA
• Java 17
• Docker
• Maven 
• IntelliJ IDEA

Configuration Spring Boot project 

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>3.0.1</version>
      <relativePath/> <!-- lookup parent from repository -->
   </parent>
   <groupId>com.henry</groupId>
   <artifactId>large-data-spring-jpa</artifactId>
   <version>0.0.1-SNAPSHOT</version>
   <name>large-data-spring-jpa</name>
   <description>Demo project for Spring Boot</description>
   <properties>
      <java.version>17</java.version>
   </properties>
   <dependencies>
      <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>org.springframework.boot</groupId>
         <artifactId>spring-boot-devtools</artifactId>
         <scope>runtime</scope>
         <optional>true</optional>
      </dependency>
      <dependency>
         <groupId>org.projectlombok</groupId>
         <artifactId>lombok</artifactId>
         <version>1.18.24</version>
         <scope>provided</scope>
      </dependency>
      <!-- Mysql Connector -->
      <dependency>
         <groupId>mysql</groupId>
         <artifactId>mysql-connector-java</artifactId>
         <version>8.0.30</version>
      </dependency>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-test</artifactId>
         <scope>test</scope>
      </dependency>
   </dependencies>

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

</project>

application.yml

server:
  port: 9000
  servlet:
    context-path: /
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test_db?allowPublicKeyRetrieval=true
    username: test
    password: test_pass
    driver-class-name: com.mysql.cj.jdbc.Driver
  jpa:
    database-platform: org.hibernate.dialect.MySQLDialect
    show-sql: true

Model

package com.henry.model;

import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import lombok.*;

@Setter
@Getter
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Entity(name = "customers")
public class Customer {
    @Id
    private int id;
    private String name;

}

Repository

CustomerRepository
package com.henry.dao;

import com.henry.model.Customer;
import jakarta.persistence.QueryHint;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.QueryHints;
import org.springframework.data.repository.PagingAndSortingRepository;

import java.util.stream.Stream;

import static org.hibernate.jpa.HibernateHints.HINT_CACHEABLE;
import static org.hibernate.jpa.HibernateHints.HINT_READ_ONLY;

public interface CustomerRepository extends PagingAndSortingRepository<Customer, Integer>, CustomerRepositoryCustom {
    @QueryHints(value = {
            @QueryHint(name = HINT_CACHEABLE, value = "false"),
            @QueryHint(name = HINT_READ_ONLY, value = "true")
           // @QueryHint(name = HINT_FETCH_SIZE, value = "1000")
    })
    @Query(value="SELECT c from customers c")
    Stream<Customer> myQuery(Pageable pageable);
}

CustomerRepositoryCustom
package com.henry.dao;

public interface CustomerRepositoryCustom {
    int findSizeCustom();
}

CustomerRepositoryCustomImpl
package com.henry.dao.impl;

import com.henry.dao.CustomerRepositoryCustom;
import com.henry.model.Customer;
import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;

public class CustomerRepositoryCustomImpl implements CustomerRepositoryCustom {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public int findSizeCustom() {
        long start = System.nanoTime();

        int i=0;
        int counter=0;
        int batch = 1000000; // per batch
        var  list = entityManager
                .createQuery("SELECT e FROM customers e", Customer.class)
                //.setHint("org.hibernate.fetchSize", 1000)
                .setFirstResult(i)
                .setMaxResults(batch)
                .getResultList();

        counter = list.size();

        while(list.size() == batch){
            i+=batch;
            list = entityManager
                    .createQuery("SELECT e FROM customers e", Customer.class)
                    //.setHint("org.hibernate.fetchSize", 1000)
                    .setFirstResult(i)
                    .setMaxResults(batch)
                    .getResultList();
            counter=counter + list.size();
        }


        System.out.println("list "+counter);

        long duration = (System.nanoTime() - start) / 1_000_000;
        System.out.println("millis " + duration);
        return counter;
    }
}


Service
CustomerService

package com.henry.service;

public interface CustomerService {

    int findAll();

    int findAllWithEM();
}

CustomerServiceImpl
package com.henry.service.impl;

import com.henry.dao.CustomerRepository;
import com.henry.service.CustomerService;
import org.springframework.data.domain.PageRequest;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.stream.Collectors;

@Service
public class CustomerServiceImpl implements CustomerService {

    private final CustomerRepository customerDAO;

    public CustomerServiceImpl(CustomerRepository customerDAO) {
        this.customerDAO = customerDAO;
    }

    @Override
    @Transactional(readOnly = true)
    public int findAll() {
        System.out.println("start");
        long start = System.nanoTime();

        int i=0;
        int counter=0;
        int batch = 1000000;

        var list =
                customerDAO
                        .myQuery(
                                PageRequest.of(i, batch))
                        .collect(Collectors.toList());

        counter = list.size();

        /*while(list.size() == batch){
            i+=batch;
            System.out.println("i "+i);
            list =
                    customerDAO
                            .myQuery(
                                    PageRequest.of(i, batch))
                            .collect(Collectors.toList());
            counter=counter + list.size();
        }*/

        System.out.println("list "+counter);

        long duration = (System.nanoTime() - start) / 1_000_000;
        System.out.println("millis " + duration);

        return counter;

    }

    @Override
    public int findAllWithEM() {
        return customerDAO.findSizeCustom();
    }
}

Controller

package com.henry.resource;

import com.henry.service.CustomerService;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/api/customers")
public class CustomerResource {

    private final CustomerService  customerService;

    public CustomerResource(CustomerService customerService) {
        this.customerService = customerService;
    }

    @GetMapping("/findAll")
    public int findAll(){
        return customerService.findAll();
    }

    @GetMapping("/findAllWithEM")
    public int findAllWithEM(){
        return customerService.findAllWithEM();
    }
}


Run & Test

Run Spring Boot application with command: mvn spring-boot:run. by console, IntelliJ etc.

Just retrieve 1M.

curl http://localhost:9000/api/customers/findAll

Hibernate: select c1_0.id,c1_0.name from customers c1_0 limit ?,?
list 1000000
millis 4905



Retrieve 10M.

curl http://localhost:9000/api/customers/findAllWithEM

Hibernate: select c1_0.id,c1_0.name from customers c1_0 limit ?,?
Hibernate: select c1_0.id,c1_0.name from customers c1_0 limit ?,?
Hibernate: select c1_0.id,c1_0.name from customers c1_0 limit ?,?
Hibernate: select c1_0.id,c1_0.name from customers c1_0 limit ?,?
Hibernate: select c1_0.id,c1_0.name from customers c1_0 limit ?,?
Hibernate: select c1_0.id,c1_0.name from customers c1_0 limit ?,?
Hibernate: select c1_0.id,c1_0.name from customers c1_0 limit ?,?
Hibernate: select c1_0.id,c1_0.name from customers c1_0 limit ?,?
Hibernate: select c1_0.id,c1_0.name from customers c1_0 limit ?,?
Hibernate: select c1_0.id,c1_0.name from customers c1_0 limit ?,?
Hibernate: select c1_0.id,c1_0.name from customers c1_0 limit ?,?
list 10000000
millis 82283



Source Code

Here on GitHub.

References.

https://docs.jboss.org/hibernate/orm/6.0/userguide/html_single/Hibernate_User_Guide.html

https://docs.oracle.com/middleware/1212/toplink/TLJPA/q_jdbc_fetch_size.htm

https://vladmihalcea.com/hibernate-performance-tuning-tips/

https://stackoverflow.com/questions/24836533/mysql-how-to-rapidly-insert-million-of-rows