如何修复Spring启动中的本机查询中的转换错误

Question

我将此查询编写为否定查询并向数据库询问选择语句，但我没有收到列表中的数据

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);   

}

发生了以下错误

org.springframework.core.convert.ConverterNotFoundException：找不到能够从类型[org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap]转换为类型[com.daehwan.notebook.model.PrivateNote]的转换器

这里是实体

@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;
}

如何更改以接收数据？

Answer 1

我认为您应该通过直接执行SQL查询来检查它是否正确工作。

看起来您的查询结果是一个< code>String(而不是< code>MCategory)，也许没有必要使用< code>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);   
}

Answer 2

1. 定义您的查询，如@NamedNativeQuery和结果集映射@SqlResultSetMaps。它指定本机SQL查询或存储过程的结果的映射。此外，@ConstructorResult应在@SqlResultSetMaps的上下文中使用，用于将结果映射到非实体POJO对象

@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 {
    ...
}

@Repository
public interface PrivateNoteRepository extends JpaRepository<PrivateNote, Long> {

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

}