This question is regarding the better architecture to follow for API Development in Java using any RDBMS database backend.
Currently, we are using the below approach to fetch data from database and passing it to the client.
Here, Once the request is made to the rest controller (e.g. Spring Boot/Java EE), it makes a database procedure call which returns the database cursor. Using this cursor, the data will be parsed row by row and column by column (e.g Spring RowMapper/JDBC) into a list of POJOs. After that a JSON API (e.g. Jackson/GSON) serializes these POJOs to JSON response messages, which is then passed to client through the Rest controller.
We noticed some disadvantages with this approach
- Performance Issue for large result sets
- Many populated objects in the heap (too many POJOs) that keeps the garbage collector busy
- Slow Development time (creating a POJO for every result set)
Now, we have come up with a new approach which increases the performance of the application, reduces the garbage collection and optimizes the development time. With this approach, once the request is made, a database procedure call is made which returns the JSON response using the in-database JSON API features (e.g. Oracle/SQL Server JSON APIs). This JSON Response is returned as a VARCHAR/CLOB and returned to the client using Java Rest controller.
This has been found to be more effective and productive compared to the traditional way of implementation because of the following benefits:
- Better Performance because the JSON is generated when the data is fetched using the SQL commands
- Less overhead on the garbage Collection because the serialization happens in the database
- Fast Development as developers just need to worry about hand-coding their SQLs (at least in our team )
Can anyone please advise if it is better to serialize the data to JSON at database level instead of doing it in Middle Tier? Which is the better architecture ?