Streaming Large JSON Response in Spring.
Tags: java, api, performance, database
Published: 2022-10-07 19:26:51

## Introduction
Reading data from the database and generating response in **[Spring](https://spring.io)** works like a charm. But things get messy when we tried to return large data set as a response.

## Setup
As a sample data set i am using [datacharmer/test_db](https://github.com/datacharmer/test_db). It has following tables

<pre>
+--------------+------------------+
| table_name   |          records |
+--------------+------------------+
| employees    |           300024 |
| departments  |                9 |
| dept_manager |               24 |
| dept_emp     |           331603 |
| titles       |           443308 |
| salaries     |          2844047 |
+--------------+------------------+
</pre>

**Our goal is to return ~300K employees in the json response.**


I am using:

1. MySQL (v8.0.19) as database
2. Spring Boot (2.7.4)
3. Spring Web.
4. Spring Data JPA.
5. Lombok & Spring boot dev tools.

To talk with database, following is a enum and a entity

**Gender.java**
```java
public enum Gender {
	M,F;
}
```

**Employee.java**
```java
@Entity
@Getter
@Setter
@Table(name = "employees")
public class Employee {
	
	@Id
	private Long empNo;

	@Column(nullable = false)
	private Date birthDate;

	@Column(nullable = false)
	private String firstName;

	@Column(nullable = false)
	private String lastName;

	@Column(nullable = false)
	@Enumerated(EnumType.STRING)
	private Gender gender;

	@Column(nullable = false)
	private Date hireDate;
}
```
Here isn't much to describe about the entity. One thing is intentional that is, i just specified the `@Id` annotation here, without specifying the generation strategy, because i am interested in reading the already generated rows, instead of writing one.

## Traditional Way

Declare everything layer by layer. At first declare a `Repository`, `Service` and a `Controller` as follows

**EmployeeRepository.java**
```java
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
	
}
```

**EmployeeService.java**
```java
@Service
@Transactional(readOnly = true)
@RequiredArgsConstructor
public class EmployeeService {

	private final EmployeeRepository employeeRepository;

	public List<Employee> findAllAtOnce() {
		return employeeRepository.findAll();
	}
}
```
 
**EmployeeController.java**
```java
@RestController
@RequiredArgsConstructor
public class EmployeeController {
	
	private final EmployeeService employeeService;

	@GetMapping(value = "/employees", params = {"at-once"})
	public List<Employee> allEmployeeAtOnce() {
		return employeeService.findAllAtOnce();
	}
}
```

Now run the spring boot project and start your favourite terminal to do some `curl` request

```sh
curl http://localhost:8080/employees?at-once=true > response.json
```

We are requesting all 300K employees and save it into `response.json` file.

![Initial success](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/evegfhflvizelcpusjni.gif)

So we are getting a 45MB of response. Part of this response looks like following

```json
[
  {
    "empNo":10001,
    "birthDate":"1953-09-01T18:00:00.000+00:00",
    "firstName":"Georgi",
    "lastName":"Facello",
    "gender":"M",
    "hireDate":"1986-06-25T18:00:00.000+00:00"
  },
  ...
]
```

So it seems we are done here. But wait, if the response itself is 45MB alone, then how much memory is needed to generate this response ? All the employee objects and the response itself needed to be in memory at once. 

Let's try our program, limiting the heap size by providing the jvm argument `-Xmx64M`. We are limiting jvm heap size upto 64MB. Now trying to curl again

![JVM Strikes with heap oveflow](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2env8qtdcm4b68n2kdyr.gif)

Overall logs outputs following:

```log
2022-10-07 02:22:52.322 ERROR 6010 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : Java heap space
2022-10-07 02:23:15.497 ERROR 6010 --- [alina-utility-1] o.a.coyote.http11.Http11NioProtocol      : Error processing async timeouts

java.util.concurrent.ExecutionException: java.lang.OutOfMemoryError: Java heap space
        at java.base/java.util.concurrent.FutureTask.report(FutureTask.java:122) ~[na:na]
        at java.base/java.util.concurrent.FutureTask.get(FutureTask.java:191) ~[na:na]
        at org.apache.coyote.AbstractProtocol.startAsyncTimeout(AbstractProtocol.java:632) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
        at org.apache.coyote.AbstractProtocol.lambda$start$0(AbstractProtocol.java:617) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
        at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) ~[na:na]
        at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:305) ~[na:na]
        at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305) ~[na:na]
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[na:na]
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[na:na]
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
        at java.base/java.lang.Thread.run(Thread.java:834) ~[na:na]
Caused by: java.lang.OutOfMemoryError: Java heap space
        at java.base/java.util.concurrent.ConcurrentHashMap$KeySetView.iterator(ConcurrentHashMap.java:4627) ~[na:na]
        at java.base/java.util.Collections$SetFromMap.iterator(Collections.java:5567) ~[na:na]
        at org.apache.coyote.AbstractProtocol.lambda$startAsyncTimeout$1(AbstractProtocol.java:640) ~[tomcat-embed-core-9.0.65.jar:9.0.65]
        at org.apache.coyote.AbstractProtocol$$Lambda$1112/0x00000008008c2440.run(Unknown Source) ~[na:na]
        ... 7 common frames omitted
```

Clearly loading so much objects at once and keeping large serialised json buffer in memory are the culprits here. 

We can do better.

## Streaming Way

Let's point out, where are so much memories are used ?

1. When we read the managed entities from the database via JPA.
2. When generating the json response.

Now can we read the managed entities at application ease ? 

Easiest way to do so is reading the employee list, page by page. Let's say page size is 10K. First read the 1-10K employee, then read the 10001-20K employee and so forth. Reading all the 300K entries. In this approach we are keeping  10K records in memory at once. Downside of this approach is that, database has to run the query every time. If the employee list is filtered by complicated condition and ordered, then it will run that complicated condition and ordering each time a page is retrieved.

Another way is to stream the result set into the application as per application ease, instead of reading the whole result set at once. Spring data jpa's `JpaRepository` already supports streaming result set.

**EmployeeRepository.java**
```java
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
	
	@QueryHints(
		value = {
			@QueryHint(name = "org.hibernate.fetchSize", value = Integer.MIN_VALUE + "" )
		}
	)
	@Query("SELECT e FROM Employee e")
	public Stream<Employee> streamAll();
}
```

**Few word about fetch size**
In general fetch size defines, how many records to pull from database to the application server. But it depends on the database vendor and the database driver you are using. If nothing specified MySql fetch all the rows at once. For fetching one row at a time ‘Integer.MIN_VALUE’ is expected in the fetch size parameter. 

[More about MySQL fetch size.](https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-implementation-notes.html)

More about fetch size in MySql and other databases are specified in a future post 


So now we are fetching one record at a time from our database. Pressure from our java heap space is relieved.

Now time to design the service layer to safely fetch the records and output it into the http response in json format.

**EmployeeService.java**
```java
@Service
@Transactional(readOnly = true)
@RequiredArgsConstructor
public class EmployeeService {

	private final EmployeeRepository employeeRepository;
	private ObjectMapper objectMapper;


	@PostConstruct
	public void postConstruct() {
		objectMapper = new ObjectMapper();
	}

        .... previous code

	public void findAllStreaming(OutputStream outputStream) throws IOException {
		Stream<Employee> employees = employeeRepository.streamAll();

		JsonGenerator jsonGenerator = objectMapper.getFactory().createGenerator(outputStream);

		try {
	
			jsonGenerator.writeStartArray();

			Iterator<Employee> employeeIterator = employees.iterator();
			while(employeeIterator.hasNext()) {
				Employee employee = employeeIterator.next();
				jsonGenerator.writeObject(employee);
			}
	
			jsonGenerator.writeEndArray();

		} catch(Exception ex) {
			throw ex;
		} finally {
			
			if(employees != null) {
				employees.close();
			}

			if(jsonGenerator != null)  {
				jsonGenerator.close();
			}
		}
	}
}
```

To put things simply, this method `findAllStreaming()` takes an `OutputStream` to write the whole json response into. To write json into the stream chunk by chunk we are using `JsonGenerator`. `JsonGenerator` is provided by [Jackson-Core](https://github.com/FasterXML/jackson-core). More detailed about how to write stream event is covered in this [blog post](http://www.cowtowncoder.com/blog/archives/2009/01/entry_132.html) by the author of the library.

We start by writing the start of the array token into the string which outputs json array start (`[`) into the output stream. Then we write the `employee` object which we get by stream from the database. After writing all the employee object into the stream we then close the array operator by writing json array end  (`]`) into the stream.

Now time to connect the controller with the newly written method.

```java
@RestController
@RequiredArgsConstructor
public class EmployeeController {
	
	private final EmployeeService employeeService;

        ... Previous Code

	@GetMapping(value = "/employees", params = "stream")
	public void streamAllEmployee(HttpServletResponse response) throws IOException {
		response.setHeader(HttpHeaders.CONTENT_ENCODING, MediaType.APPLICATION_JSON_VALUE);
		employeeService.findAllStreaming(response.getOutputStream());
	}
}
```

Now trying with the `-Xmx64m` jvm arg

![JVM is still unhappy](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dcd8mx3vz7xuajwhm4io.gif)

We are still struggling, with the memory overflow issue. What could go wrong here ? 

If we look closely, inside the streaming portion

```java
Iterator<Employee> employeeIterator = employees.iterator();
while(employeeIterator.hasNext()) {
    Employee employee = employeeIterator.next();
    jsonGenerator.writeObject(employee);
}
```

We are fetching entities here from database. Each time a new entity is being generated. Hibernate return's persistent entity every time. So our persistent context is full of entity objects.

We can directly `@Autowired` the `EntityManager` and call `detach` on it. But we can do better. We can introduce our own custom `BaseRepository` equipped with `detach` method.

**BaseRepository.java**
```java
@NoRepositoryBean
public interface BaseRepository<T, ID> extends JpaRepository<T, ID> {

	public void detach(T entity);
}
``` 

**BaseRepositoryImpl.java**
```java
public class BaseRepositoryImpl<T, ID> extends SimpleJpaRepository<T, ID> implements BaseRepository<T, ID> {

	private final EntityManager entityManager;

	public BaseRepositoryImpl(JpaEntityInformation<T, ID> entityInformation, EntityManager entityManager) {
		super(entityInformation, entityManager);
		this.entityManager = entityManager;
	}

	public BaseRepositoryImpl(Class<T> domainClass, EntityManager em) {
		super(domainClass, em);
		this.entityManager = em;
	}

	@Override
	public void detach(T entity) {
		entityManager.detach(entity);
	}	
}
```

Register the `BaseRepositoryImpl` class as the repository base class

**StreamJsonResponseServerApplication.java**

```java
@SpringBootApplication
@EnableJpaRepositories(repositoryBaseClass = BaseRepositoryImpl.class)
public class StreamJsonResponseServerApplication {

	... Previous Code

}
```


Now update the `EmployeeRepository.java`

```java
public interface EmployeeRepository extends BaseRepository<Employee, Long> {
    ... Previous Code
}
```

And inside the service

```java
Iterator<Employee> employeeIterator = employees.iterator();
while(employeeIterator.hasNext()) {
    Employee employee = employeeIterator.next();	 
    employeeRepository.detach(employee); // Removing entity object from persistent context.
    jsonGenerator.writeObject(employee);
}
```

Now try again with the curl 

![Finally Success](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e9pb471fc2i95ry19kv6.gif)

So we the response landed successfully.

I even tried with extreme situation by passing `-Xmx32m` as jvm argument. It also works like charm.

Portion of the downloaded json looks like following

```json
[
    {
        "empNo":10001,
        "birthDate":-515397600000,
        "firstName":"Georgi",
        "lastName":"Facello",
        "gender":"M",
        "hireDate":"26 June 1986"
    }
    ...
]
```

##Downsides

In this approach, servlet containers thread pool can be drained out under heavy load, as these responses taking longer that usual. Application managed thread can be used in returning the response, which will be covered in details in a future post.

##Bonus

In order to bring some customisation in the json format is quite easy.

Say we want a custom format in the date then inside the `EmployeeService.java`

```java
@PostConstruct
public void postConstruct() {
	objectMapper = new ObjectMapper();
	objectMapper.setDateFormat(new SimpleDateFormat("dd MMMM YYYY"));
}
```

It outputs following json

```json
[
    {
        "empNo":10001,
        "birthDate":"02 September 1953",
        "firstName":"Georgi",
        "lastName":"Facello",
        "gender":"M",
        "hireDate":"26 June 1986"
    }
    ...
]
```

Another thing like we want to restructure the json, so that instead of returning the employees inside of an array, we want a json object inside which a `employees` keys will be present holding to the list of employees. For that we could customise the method `findAllStreaming` accordingly

```java
try {
    jsonGenerator.writeStartObject();			 
    jsonGenerator.writeArrayFieldStart("employees");
    Iterator<Employee> employeeIterator = 
    employees.iterator();
    while(employeeIterator.hasNext()) {
        Employee employee = employeeIterator.next();
        employeeRepository.detach(employee);
        jsonGenerator.writeObject(employee);
    }
	
    jsonGenerator.writeEndArray();
    jsonGenerator.writeEndObject();	
} ... Previous Code
```

Which generates following

```json
{
    "employees" : [
        {
            "empNo":10001,
            "birthDate":"02 September 1953",
            "firstName":"Georgi",
            "lastName":"Facello",
            "gender":"M",
            "hireDate":"26 June 1986"
        }
        ...
    ]
}
```

All the code is shared into my [github repository](https://github.com/ratulSharker/stream-json-response/tree/main/server).

Want to know, how to consume this large json responses without hitting the jvm heap boundary ? Read **[Consume Large JSON Response in Spring](https://dev.to/ratulsharker/consume-large-json-response-in-spring-421j)**.