How to map Postgres JSON data type in DTO and Aggentity class using Springboot and Hibernate

Question

I have a ResponseDto class which looks like as below:

public static class HealthGoalsHighlight {

    @ApiModelProperty(value = "Total number of eligible users")
    private Long totalEligibleUsers;
    @ApiModelProperty(value = "Total number of registered users")
    private Long totalRegisteredUsers;
    @ApiModelProperty(value = "Total number of users with atleast one goal count")
    private Long totalUsersWithGoal;
    @ApiModelProperty(value = "Top goal name selected by user")
    private String topGoal;
    @ApiModelProperty(value = "Bottom goal name selected by user")
    private String bottomGoal;
  }

This DTO was made based upon below table structure:

health_goals
(
  uid BIGSERIAL NOT NULL CONSTRAINT health_goals_pkey primary key,
  employer_key bigint not null,
  total_eligible_users bigint not null,
  total_registered_users bigint not null,
  total_users_with_goal bigint not null,
  top_goal_name varchar(255),
  bottom_goal_name varchar(255),
  created_ts TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_ts TIMESTAMP NOT NULL DEFAULT NOW(),
  created_by varchar(255),
  updated_by varchar(255)
);

Now the table structure has been changed to as below:

health_goals
(
  uid BIGSERIAL NOT NULL CONSTRAINT health_goals_pkey primary key,
  employer_key bigint not null,
  health_goals_metric_value json null,
  created_ts TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_ts TIMESTAMP NOT NULL DEFAULT NOW(),
  created_by varchar(255),
  updated_by varchar(255)
);

Basically now all these columns like total_eligible_users, total_registered_users, total_users_with_goal, top_goal_name, bottom_goal_name wil be consolidated to single columnhealth_goals_metric_value as a JSON data type.

How can I write the response DTO for JSON data type column. Also what changes needs to be done in my AggMapper class.

Please share the code for your existent AggMapper class.

eg04lt3r
– eg04lt3r

2020-05-29 10:43:19 +00:00
Commented May 29, 2020 at 10:43 — eg04lt3r
– eg04lt3r, Commented May 29, 2020 at 10:43

Kunal Vohra · Accepted Answer · 2020-06-01 06:45:01Z

2

+200

Well one way is by using converter function. You can use the converter function to get values in same format.

Change your orm.xml something like below on your column definition

<basic name="healthGoalsMetricValue">
                <column name="health_goals_metric_value" nullable="true"/>
                <convert converter="path.to.your.HealthGoalsMetricValueConverter"/>
            </basic>

Or if you have java file

aggentity will have following entry

  @Convert(converter = HealthGoalsMetricValueConverter.class)
    private HealthGoalsHighlight healthGoalsHighlight ;

and your class HealthGoalsMetricValue will look something like

//////////////////Edited converter class after comments

       import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;

import javax.persistence.AttributeConverter;
import javax.persistence.Converter;
import java.io.IOException;

@Converter
public class HealthGoalsMetricValueConverter implements AttributeConverter<HealthGoalsMetricValue, String> {

private final ObjectMapper mapper = new ObjectMapper();

//And then override like that
@Override
        public String convertToDatabaseColumn(HealthGoalsHighlight healthGoalsMetricValue) {

            try {                
                json = mapper.writeValueAsString(healthGoalsMetricValue);
            } catch (JsonProcessingException exception) {
                throw new JsonProcessingException("Error occurred while object serialization", exception);
            }
            return json;
    }

 //And then override again
@Override
public HealthGoalsMetricValue  convertToEntityAttribute(String healthGoalsMetricValuestr ) {
    HealthGoalsMetricValue  healthGoalsMetricValue  = null;
    try {
        if (healthGoalsMetricValue != null) {
            healthGoalsMetricValue = mapper.readValue(healthGoalsMetricValuestr, HealthGoalsMetricValue.class);
        }
    } catch (Exception exception) {
        throw new Exception("Error occurred while object Deserialization", exception);
    }
    return healthGoalsMetricValue;
}

This all will do the job for you.

edited Jun 1, 2020 at 6:45

answered May 29, 2020 at 11:06

Kunal Vohra

2,8562 gold badges17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

sandeep Over a year ago

convertToDatabaseColumn(HealthGoalsMetricValue healthGoalsMetricValue) -- In this method "HealthGoalsMetricValue" where should I define this class and what will be its content. Otherwise its giving me error, can't resolve symbol HealthGoalsMetricValue.

Kunal Vohra Over a year ago

This will be type of your JSON cloumn in entity class.HealthGoalsHighlight @sandeep

Kunal Vohra Over a year ago

@Sandeep let me know if any doubt still.

sandeep Over a year ago

I think I've messed up everything. Getting lot of errors. Let me try to write here step by step. this is how mu DTO looks like. public class HealthGoalsHighlightResponseDto implements Serializable { private MetadataDto metadata; private HealthGoalsHighlight healthGoalsHighlight; @EqualsAndHashCode public static class HealthGoalsHighlight { @ApiModelProperty(value = "Total metrics") private Map<String, Object> healthGoalsMetricValue; } }

sandeep Over a year ago

This is my AggEntity class public class HealthGoalsHighlightAggEntity extends AggBaseEntity { @Convert(converter = HealthGoalsMetricValueConverter.class) private HealthGoalsHighlight healthGoalsHighlight ; }

|

Babl · Accepted Answer · 2020-05-29 16:38:41Z

0

If you can add additional library have a look at https://github.com/vladmihalcea/hibernate-types project, it's super easy with that.

With this library you will end up with code as simple as this

@Entity
@Table(name = "health_goals")
@TypeDefs({
    @TypeDef(name = "json", typeClass = JsonStringType.class),
    @TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
})
public class HealthGoal {
    // all other columns
    @Type(type = "json")
    private HealthGoalsHighlight healthGoalsHighlight;

    // getters setters

}

And if using the maven add dependency

<dependency>
  <groupId>com.vladmihalcea</groupId>
  <artifactId>hibernate-types-52</artifactId>
  <version>2.9.10</version> // or newer version
</dependency>

answered May 29, 2020 at 16:38

Babl

7,67628 silver badges41 bronze badges

2 Comments

sandeep Over a year ago

I am not using Maven. How can I use this library. Do I need to download and install in my project. If yes how to do that.

Babl Over a year ago

@sandeep Just add the <dependency> part into your pom.xml. And put the @TypeDefs on your entity and the @Type on the field, and you are ready to go. See this cool doc vladmihalcea.com/…

Collectives™ on Stack Overflow

How to map Postgres JSON data type in DTO and Aggentity class using Springboot and Hibernate

2 Answers 2

11 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related