Retrieve JSON Array or JSON Object using JPA Projections & DTO interface

Question

I have a DTO interface which fetches data from different tables using joins. I have made a DTO interface with the abstract getter methods something like this.

    public interface HRJobsDTO {

    String getEditorName();

    String getEditorId();

    String getBillingMonth();

    Integer getEditorWordCount();

    Integer getJobCount();

    Integer getEmployeeGrade();

    Float getGrossPayableAmount();

    Float getJobBillingRate();

    Float getTaxDeduction();

    Float getTaxDeductionAmount();

    Float getNetPayableAmount();

    String getInvoiceStatus();

    String getFreelanceInvoiceId();
}

In this interface my getFreelanceInvoiceId(); method returns a JSON Array using json_arrayagg function of mysql. I changed the datatype to String, String[] and Arraylist but it returns something like this in my response

"freelanceInvoiceId": "[\"4af9e342-065b-4594-9f4f-a408d5db9819/2022121-95540\", \"4af9e342-065b-4594-9f4f-a408d5db9819/2022121-95540\", \"4af9e342-065b-4594-9f4f-a408d5db9819/20221215-53817\", \"4af9e342-065b-4594-9f4f-a408d5db9819/20221215-53817\", \"4af9e342-065b-4594-9f4f-a408d5db9819/20221215-53817\"]"

Is there any way to return only array with exclusion of backslashes?

score 0 · Answer 1 · answered Dec 20 '22 at 09:14

0

You can use @Converter from JPA (implemented by hibernate also)

@Converter
public class List2StringConveter implements AttributeConverter<List<String>, String> {

    @Override
    public String convertToDatabaseColumn(List<String> attribute) {
        if (attribute == null || attribute.isEmpty()) {
            return "";
        }
        return StringUtils.join(attribute, ",");
    }

    @Override
    public List<String> convertToEntityAttribute(String dbData) {
        if (dbData == null || dbData.trim().length() == 0) {
            return new ArrayList<String>();
        }

        String[] data = dbData.split(",");
        return Arrays.asList(data);
    }
}

And references it in the pojo class as below

@Column(name="freeLanceInvoiceId")
@Convert(converter = List2StringConveter.class)
private List<String> tags=new ArrayList<>();

answered Dec 20 '22 at 09:14

Bacem W.

92
4

Yes I am aware of this way. But I am using a DTO interface not an POJO entity class – Siddhant Bhandari Dec 20 '22 at 09:16
yes I know, but this code should be in DAO (Entity) before the mappring to DTO,and then you should change your String getFreelanceInvoiceId(); to List getFreelanceInvoiceId(); – Bacem W. Dec 20 '22 at 09:39
how that can be done? Any code snippet? – Siddhant Bhandari Dec 22 '22 at 04:31
yes, I added the response above – Bacem W. Dec 23 '22 at 14:47
I didn't got you feedback about the given response ? does it cover the needs ? thank you in advance – Bacem W. Jan 03 '23 at 08:11

score 0 · Answer 2 · answered Dec 23 '22 at 14:45

Basically I have list of names separated by camma (;),

CREATE TABLE familynames (
    id int4 NOT NULL DEFAULT nextval('family_id_seq'::regclass),
    names varchar NULL
);

INSERT INTO familynames (id, names) VALUES(1, 'Animalia;Arthropoda;Pancrustacea;Hexapoda');

The converter :

@Converter
public class NameConverter implements AttributeConverter<List<String>, String> {

    @Override
    public List<String> convertToEntityAttribute(String attribute) {
        if (attribute == null) {
            return null;
        }

        List<String> namesList = new ArrayList<String>(Arrays.asList(attribute.split(";")));

        return namesList;
    }

    @Override
    public String convertToDatabaseColumn(List<String> namesList) {
        if (namesList == null || namesList.size() == 0) {
            return null;
        }
        String result = namesList.stream().collect( Collectors.joining( ";" ) );

        return result;
    }
}

An entity that map the sql table

@Entity
@Table(name = "familynames")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class FamilyNames implements Serializable {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Long id;

    @Convert(converter = NameConverter.class, attributeName = "names")
    private List<String> names;
}

An interface for projection

public interface FamilyNamesDto {
    
    Integer getId();
    List<String> getNames();
}

The repository that ensure the selection and the projection

@Repository
public interface FamilyNamesRepository extends JpaRepository<FamilyNames, Long> {

    /** @return the whole elements of mapped  elements as dto */
    List<FamilyNamesDto> findAllProjectedBy();
}

How to autowire it

@Autowired 
private FamilyNamesRepository familyNamesRepository;

how to call repository within any service

List<FamilyNamesDto> fs = familyNamesRepository.findAllProjectedBy();

results :

Retrieve JSON Array or JSON Object using JPA Projections & DTO interface

2 Answers2