1

I wrote this query as a negative query and asked the database for a select statement, but I did not receive data to the List<MCategory>

public interface NoteRepository extends JpaRepository<PrivateNote, Long> {

    @Query(nativeQuery = true, value =
            "SELECT p.mCategory " +
            "FROM privatenote as p " +
            "WHERE p.userId = :userId AND p.sharing = :sharing " +
            "GROUP BY p.mCategory"
        )
    List<MCateogry> findGroupBymCategory(@Param("userId") Long userId, @Param("sharing") 
    boolean sharing);   

}

The following error occurred

org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [com.daehwan.notebook.model.PrivateNote]

Here are the entities

@Data
@NoArgsConstructor
@AllArgsConstructor
public class MCategory{
    
    private String mCategory;   
    
}

@Entity
@Data
@NoArgsConstructor
@AllArgsConstructor
public class PrivateNote {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(nullable = false, length = 100)
    private String title;
    
    @JsonProperty("m_category")
    @Column(nullable = false, length = 30)
    private String mCategory;
    
    @JsonProperty("s_category")
    @Column(nullable = false, length = 30)
    private String sCategory;
    
    @Lob
    private String content;
    
    @Column(nullable = false)
    private boolean sharing;
    
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "userId")
    private User user;
    
    @CreationTimestamp
    private Timestamp createDate;
    
    private Timestamp updateDate;
}

How do I change to receive the data?

eduyayo
  • 2,020
  • 2
  • 15
  • 35
DaeHwan
  • 11
  • 1
  • you have some alternatives here with custom entity bean and constructor expressions https://stackoverflow.com/questions/49500309/spring-jpa-native-query-with-projection-gives-converternotfoundexception – eduyayo May 18 '22 at 10:14

2 Answers2

2
  1. Define your query like @NamedNativeQuery with result set mapping @SqlResultSetMapping. It specifies the mapping of the result of a native SQL query or stored procedure. Also @ConstructorResult should be used in context of @SqlResultSetMapping for mapping result into not entity POJO object
@Entity
@Data
@NoArgsConstructor
@AllArgsConstructor
@NamedNativeQuery(name ="Category", 
                  query =
                         "SELECT p.m_category" +
                         "FROM privatenote as p " +
                         "WHERE p.userId = :userId AND p.sharing = :sharing " +
                         "GROUP BY p.m_category", 
                   resultSetMapping = "CategoryMapping"
)
@SqlResultSetMapping(name="CategoryMapping",
        classes = {
                @ConstructorResult(targetClass = MCateogry.class,
                        columns = {@ColumnResult(name = "m_category")
                 })
})
public class PrivateNote {
    ...
}
  1. Use defined named query 'Category' in your repository
@Repository
public interface PrivateNoteRepository extends JpaRepository<PrivateNote, Long> {

    @Query(name = "Category", nativeQuery = true)
    List<MCateogry> findGroupBymCategory(@Param("userId") Long userId, @Param("sharing") boolean sharing);

}
Eugene
  • 5,269
  • 2
  • 14
  • 22
0

I think you should check your SQL query works correctly by executing it directly.

It seems that the result of your query is a String (not MCategory) and maybe there is no need to make a group by

public interface NoteRepository extends JpaRepository<PrivateNote, Long> {

    @Query(nativeQuery = true, value =
            "SELECT p.mCategory " +
            "FROM privatenote as p " +
            "WHERE p.userId = :userId AND p.sharing = :sharing " +
            "GROUP BY p.mCategory"
        )
    List<String> findGroupBymCategory(@Param("userId") Long userId, @Param("sharing") 
boolean sharing);   
}
mohamed chadad
  • 91
  • 7