Home > Net >  How to fix convertion error in Nativequery in Spring-boot
How to fix convertion error in Nativequery in Spring-boot

Time:05-19

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?

CodePudding user response:

  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")
    List<MCateogry> findGroupBymCategory(@Param("userId") Long userId, @Param("sharing") boolean sharing);

}

CodePudding user response:

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);   
}
  • Related