Thursday, December 29, 2022

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

Create a docker-compose.yml
version: '2'
services:

  ### Mysql container
  mysql:
    image: mysql:latest
    ports:
      - "3306:3306"
    volumes:
      - /var/lib/mysql:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: mypass
      MYSQL_DATABASE: test_db
      MYSQL_USER: test
      MYSQL_PASSWORD: test_pass
      MYSQL_ROOT_HOST: '%'  # needs to be enclosed with quotes

docker run --name mysql_test -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=mypass -d mysql:latest

docker-compose -f docker-compose.yml up

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


























No comments:

Post a Comment

Spring Authorization Server 1.0 with LDAP - Spring Security 6

  Introduction: Spring Authorization Server is a framework that provides implementations of the OAuth 2.1 and OpenID Connect 1.0 specificat...