Ufdjrw

There is WebApp with REST API. I have to consume API, which is in JSON format, then save it in RDBMS. JSON is basically list of properties and sub-properties. I have to map them to the DB columns.

DB Schema is yet to be created whatever I think is suitable. What I am looking for is the best way to map JSON to Java DTO and then DTO to Entity. I was planning to use JACKSON to map JSON to DTO. I might or might not map all of the properties. Then in the next step I was planning of writing manual mappers who will map DTO to Entity.

I have to use RDBMS (MySQL) instead of NoSQL because other tools which will read data from DB are SQL Query based.

EDIT: Commercial reporting tools who are supposed to use RDBMS have some limited SQL Query capability, can't do any advanced SQL or business logic there. So I can't save JSON as string and then tokenise it later in reporting tools.

Any idea on this? I know it might be done directly JSON to Entity, but there are advice not to do such as in this advice: Mapping JSON object to Hibernate entity

Is there any better way to do this, or my planed approach is good for start?

Only one POJO model

I agree that using the same POJO structure for Hibernate and Jackson libraries is not the best option, generally speaking. But it also depends from given scenarios you need to implement. In your case you need to load data from REST API and store result in DB. So, basically you will be invoking INSERT queries in 90% of cases. So, you can keep your POJO structure very simple without worrying about any lazy-loading problems. In this scenario using same POJO structure is not so bad. Linked question/answer says only about problems with reading and exposing DB structure by REST API which is not a case in this question.

You need to also find out how different DB model will be from JSON model. If they are similar and you can say 1:1 there is no need to create extra layer.

Two POJO models

In this scenario you need to create two POJO models: one for handling JSON deserialisation and one for working with DB via ORM. In this scenario the biggest problem is to map these two models. Also, changes in REST API propagates over every layer. You can use of course map libraries such as Dozer, Orika, MapStruct or others but there is always a need to maintain this layer. From other side this is very safe solution because you can control mapping to JSON and to DB separately and keep different structures without many annotations or custom deserialisers, adapters, etc. From other side, if these two models are really different this solution is the best.

Collections and one POJO model

There is also a third option which I see and it is using Java collection library for handling JSON payloads. JSON Object fits to Map<String, Object> and JSON array fits to List<Object>. Jackson automatically take care about choosing right types and you need to only map these collections to POJO model used on ORM side. This solution removes need to keep two models but it complicates mapping layer and makes it fuzzy.

From information you gave I would start from first option. In case, things are going to complicate generating extra model and mapping layer with help of some libraries should not be a big problem.

In general the approach is correct. But details depend on what frameworks/ORMs you're about to use (if any).

If you want to use JPA/Hibernate for persistence - you'd need an Entity class(es), which should never be used as DTOs. If you'll have some custom persistence you could probably skip entity classes altogether and just use DTO to populate query parameters.

But simplest and most common way to implement such a solution would be via using Spring framework which would automatically map your JSON to DTO using Jackson, and Spring Data JPA for persistence.

In this case the only work remaining is mapping DTO to Entity. This can be implemented manually, but can also be done using framework that would generate mappers - like MapStruct.