Question 21688 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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?

Tags:

2

Answers


  1. 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<>();
    Login or Signup to reply.
  2. 0

    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 :

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search