Search code examples
javamysqlhibernaterepositorydto

How to get JSON List of string from column of mysql table using custom Dto query

i want to query a list from specific column of table using dto, my actual query is so complex containing 3 joins, i have added dummy code for the problem statement.

and i'm facing this error:

org.springframework.dao.InvalidDataAccessApiUsageException: Could not locate appropriate constructor on class : com.example.demo.persistence.mysql.dto.StudentDto; nested exception is java.lang.IllegalArgumentException: Could not locate appropriate constructor on class : com.example.demo.persistence.mysql.dto.StudentDto

enter image description here

Students.java

@SqlResultSetMapping(name = "StudentMapping", classes = {
        @ConstructorResult(targetClass = StudentDto.class, columns = {
                @ColumnResult(name = "friendList", type = List.class)})})

@Data
@Table(name = "student")
public class Student {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private Long id;

    @Column(name = "name", nullable = false, length = 100)
    private String name;

    @Type(type = "json")
    @Column(name = "friend_list", columnDefinition = "json")
    @Builder.Default
    private List<String> friendsName = new ArrayList<>();
}

StudentDto.java

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class StudentDto implements Serializable {

    @Type(type = "json")
    @Column(name = "friend_list", columnDefinition = "json")
    List<String> friends;
}

StudentsCustomRepository.java

public StudentsDto fetchStudentFriends(Long id) {
        String rawQuery = String.format(
                "select friend_list as friends from student where id = '%s';",
                id);
        Query query = entityManager.createNativeQuery(rawQuery, "StudentMapping");
        return (StudentDto) query.getResultList();
    }
Solution

  • You can use custom query if you want to import directly from database as dto object.

    for example -> @Query("SELECT new com.demo.dtos.userInfoDTO(user.id, user.username) FROM User user")