Streaming Large JSON Response in Spring.

Introduction

Reading data from the database and generating response in Spring 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. It has following tables

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

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

I am using:

MySQL (v8.0.19) as database
Spring Boot (2.7.4)
Spring Web.
Spring Data JPA.
Lombok & Spring boot dev tools.

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

Gender.java



public enum Gender {
    M,F;
}

Employee.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



public interface EmployeeRepository extends JpaRepository<Employee, Long> {

}

EmployeeService.java



@Service
@Transactional(readOnly = true)
@RequiredArgsConstructor
public class EmployeeService {

    private final EmployeeRepository employeeRepository;

    public List<Employee> findAllAtOnce() {
        return employeeRepository.findAll();
    }
}

EmployeeController.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



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

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

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



[
  {
    "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

Overall logs outputs following:



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 ?

When we read the managed entities from the database via JPA.
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



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.

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



@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. More detailed about how to write stream event is covered in this blog post 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.



@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

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

If we look closely, inside the streaming portion



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



@NoRepositoryBean
public interface BaseRepository<T, ID> extends JpaRepository<T, ID> {

    public void detach(T entity);
}

BaseRepositoryImpl.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);
    }   
}

StreamJsonResponseServerApplication.java



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

    ... Previous Code

}

Now update the EmployeeRepository.java



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

And inside the service



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

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



[
    {
        "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



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

It outputs following 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



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



{
    "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.

Want to know, how to consume this large json responses without hitting the jvm heap boundary ? Read Consume Large JSON Response in Spring.

Blog