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


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


























Sunday, November 20, 2022

Spring boot 3 with Hibernate 6 and Java 21

Spring Boot 3


Spring boot 3 Features :
  • Spring Boot 3.0 will require Java 17 or later
  • Jakarta EE 9 a new top-level jakarta package, replacing EE 8’s javax top-level package. It will also be the first version of Spring Boot that makes use of Jakarta EE 9 APIs (jakarta.*) instead of EE 8 (javax.*). 
  • Since Spring Boot 2.4 changed the way that application.properties and application.yaml files were loaded.

Hibernate ORM 6.0


Hibernate  Compatibility :

There are several layers of working with persistent data in Java/Spring:

Demo

Spring boot 3 with hibernate 6, MySQL and YAML file configuration.


Technology

  • Spring Boot 3.2.0
  • Hibernate 6
  • Java 21
  • Docker Compose
  • Maven 
  • IntelliJ IDEA  2023.3.1 Community 

Project Structure


























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.2.0</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.henry</groupId>
	<artifactId>spring3-hibernate6</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>spring3-hibernate6</name>
	<description>Spring boot 3 with hibernate 6 configuration</description>
	<properties>
		<java.version>21</java.version>
	</properties>
	<dependencies>
		<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>
		<!-- POSTGRESQL database driver -->
		<dependency>
			<groupId>org.postgresql</groupId>
			<artifactId>postgresql</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.hibernate/hibernate-core -->
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-core</artifactId>
			<version>6.4.1.Final</version>
			<type>pom</type>
		</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:postgresql:postgre_test
    username: postgre_test
    password: postgre_test
    driver-class-name: org.postgresql.Driver
    initialization-mode: always
  jpa:
    show-sql: true
    hibernate:
      ddl-auto: create-drop

Model


package com.henry.model;

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


@Setter
@Getter
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(name = "first_name")
    private String firstName;
    @Column(name = "last_name")
    private String lastName;

}

Repository


  • UserRepositoryCustom
package com.henry.repository;

public interface UserRepositoryCustom {

    public Integer getSum(int a, int b);
}

  • UserRepository
package com.henry.repository;

import com.henry.model.User;
import org.springframework.data.jpa.repository.JpaRepository;

import java.util.Optional;

public interface UserRepository extends JpaRepository<User,Long>, UserRepositoryCustom {

    Optional<User> findByFirstName(String firstName);

}
  • UserRepositoryCustomImpl
package com.henry.repository.impl;

import com.henry.repository.UserRepositoryCustom;
import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import org.hibernate.Session;
import org.hibernate.jdbc.ReturningWork;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;

public class UserRepositoryCustomImpl implements UserRepositoryCustom {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public Integer getSum(int a, int b) {

        Session session = entityManager.unwrap(Session.class);

        int result = session.doReturningWork(new ReturningWork<Integer>() {
            @Override
            public Integer execute(Connection connection) throws SQLException {
                CallableStatement call = connection.prepareCall("{ ? = call get_sum(?,?) }");
                call.registerOutParameter(1, Types.INTEGER); // or whatever it is
                call.setInt(2, a);
                call.setInt(3, b);
                call.execute();
                return call.getInt(1); // propagate this back to enclosing class
            }
        });

        return  result;
    }
}

Service

  • UserService

package com.henry.service;

import com.henry.model.User;

import java.util.List;

public interface UserService {
    User save(User user);

    List<User> findAll();

    Integer getSum(int a, int b);
}

  • UserServiceImpl

package com.henry.service.impl;

import com.henry.model.User;
import com.henry.repository.UserRepository;
import com.henry.service.UserService;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserServiceImpl implements UserService {

    private final UserRepository userRepository;

    public UserServiceImpl(UserRepository userRepository) {
        this.userRepository = userRepository;
    }

    @Override
    public User save(User user) {
        return userRepository.save(user);
    }

    @Override
    public List<User> findAll() {
        return userRepository.findAll();
    }

    @Override
    public Integer getSum(int a, int b) {
        return userRepository.getSum(a,b);
    }
}


Controller

  • UserController
package com.henry.controller;

import com.henry.model.User;
import com.henry.service.UserService;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/api/users")
public class UserController {

    private final UserService userService;

    public UserController(UserService userService) {
        this.userService = userService;
    }

    @GetMapping("/hello")
    public String helloWorld() {

        return  """
                Hello World, 
                multi-line,
                text block.
                """;
    }

    @GetMapping
    public List<User> findAll() {
        return userService.findAll();
    }

    @GetMapping("/sum/{a}/{b}")
    public Integer getSum(@PathVariable int a, @PathVariable int b) {
        return userService.getSum(a, b);
    }

    @PostMapping
    @ResponseStatus(HttpStatus.CREATED)
    public User addUser(@RequestBody User user) {
        return userService.save(user);
    }

}


Test Class


package com.henry;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.henry.model.User;
import com.henry.service.UserService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.web.servlet.WebMvcTest;
import org.springframework.boot.test.mock.mockito.MockBean;
import org.springframework.http.MediaType;
import org.springframework.test.web.servlet.MockMvc;
import org.springframework.test.web.servlet.ResultActions;

import java.util.ArrayList;
import java.util.List;

import static org.hamcrest.CoreMatchers.is;
import static org.mockito.ArgumentMatchers.any;
import static org.mockito.BDDMockito.given;
import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.get;
import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.post;
import static org.springframework.test.web.servlet.result.MockMvcResultHandlers.print;
import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.jsonPath;
import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.status;


@WebMvcTest
public class UserControllerTests {

    @Autowired
    private MockMvc mockMvc;

    @MockBean
    private UserService userService;

    @Autowired
    private ObjectMapper objectMapper;

    @Test
    public void givenUserObject_whenCreateUser_thenReturnSavedUser() throws Exception{

        // given - precondition or setup
        User user = User.builder()
                .firstName("Henry")
                .lastName("Xiloj")
                .build();
        given(userService.save(any(User.class)))
                .willAnswer((invocation)-> invocation.getArgument(0));

        // when - action or behaviour that we are going test
        ResultActions response = mockMvc.perform(post("/api/users")
                .contentType(MediaType.APPLICATION_JSON)
                .content(objectMapper.writeValueAsString(user)));

        // then - verify the result or output using assert statements
        response.andDo(print()).
                andExpect(status().isCreated())
                .andExpect(jsonPath("$.firstName",
                        is(user.getFirstName())))
                .andExpect(jsonPath("$.lastName",
                        is(user.getLastName())));

    }

    // JUnit test for Get All users REST API
    @Test
    public void givenListOfUsers_whenGetAllUsers_thenReturnUsersList() throws Exception{
        // given - precondition or setup
        List<User> listOfUsers = new ArrayList<>();
        listOfUsers.add(User.builder().firstName("User1").lastName("User1").build());
        listOfUsers.add(User.builder().firstName("User2").lastName("User2").build());
        given(userService.findAll()).willReturn(listOfUsers);

        // when -  action or the behaviour that we are going test
        ResultActions response = mockMvc.perform(get("/api/users"));

        // then - verify the output
        response.andExpect(status().isOk())
                .andDo(print())
                .andExpect(jsonPath("$.size()",
                        is(listOfUsers.size())));

    }

    @Test
    public void givenEmployeeId_whenGetUsersFunSum_thenReturnUsersObject() throws Exception{
        // given - precondition or setup
        given(userService.getSum(1,2)).willReturn(3);

        // when -  action or the behaviour that we are going test
        ResultActions response = mockMvc.perform(get("/api/users/sum/{a}/{b}", 1,2));

        // then - verify the output
        ResultActions resultActions = response.andExpect(status().isOk())
                .andDo(print());


    }
}



Environment Setup with Docker Compose


To simplify the setup of your application's environment, you can use Docker Compose. Below is a docker-compose-postgresql.yml file that defines services  PostgreSQL database:

version: '3'
services:
  postgres:
    image: postgres:14.1
    container_name: postgre_test
    environment:
      POSTGRES_USER: postgre_test
      POSTGRES_PASSWORD: postgre_test
      POSTGRES_DB: postgre_test
    ports:
      - "5432:5432"

Run command:

docker-compose -f docker-compose-postgresql.yml up -d


Run & Test



Run Spring Boot application with command: mvn test -Dtest=UserControllerTests. by console, IntelliJ etc.














Source Code


Here on GitHub.




References.

https://spring.io/blog/2022/05/24/preparing-for-spring-boot-3-0
https://hibernate.org/orm/releases/6.0/
https://jakarta.ee/specifications/persistence/
https://docs.oracle.com/javase/8/docs/technotes/guides/jdbc/
https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/core/JdbcTemplate.html
https://docs.oracle.com/javaee/7/api/javax/persistence/EntityManager.html
https://docs.spring.io/spring-data/jpa/docs/current/reference/html/
https://docs.oracle.com/en/java/javase/17/


























Deploying a Spring Boot Application with Cloud SQL and Cloud Run on GCP

In this post, we'll explore how to provision Cloud SQL instances with different connectivity options using Terraform and then access the...