getting ConverterNotFoundException

Question

I am working in spring boot project. In my JPA repository, I am using sql native query with pagination enabled. However on service layer I am getting ConvertorNotFoundException.. tried many times with different data types..but all in vain. Below are my code classes:

Repository interface

public interface ContentSongsRepository extends JpaRepository<ContentSongs,Long>{

@Query(value= "SELECT date(start_date) as DATEE, sum(case when  subscription_from in ('IVR','SMS') and remarks like '%Change Song%' then 1 else 0 end) as 'song_change' from subscription   group by  date(start_date)  \n#pageable\n",nativeQuery=true)
    Page<SongChangeCount> getSongChangeCount2(Pageable page);

}

Sql query is running fine.. below is image.

Service layer method calling repository

@Override
public SongChangeCountView getSongChangeCount(Pageable page) {
    // TODO Auto-generated method stub
    Page<SongChangeCount> songChangePageList = contantSongRepository.getSongChangeCount2(page);
    List<SongChangeCount> list = songChangePageList.getContent();
    Integer pageCount = songChangePageList.getTotalPages();
    Long totalElement = songChangePageList.getTotalElements();
    SongChangeCountView sccv = new SongChangeCountView();
    sccv.setLsSongChangeCountView(list);
    sccv.setPageCount(pageCount);
    sccv.setTotalElement(totalElement);
    return sccv;
}

SongChangeCount class

import java.io.Serializable;
import java.util.Date;

import javax.persistence.Entity;


public class SongChangeCount implements Serializable{

    private static final long serialVersionUID = -5410845856201124932L;


    private Date DATEE;
    private String song_change;





    public SongChangeCount(Date dATEE, String song_change) {
        DATEE = dATEE;
        this.song_change = song_change;
    }




    public Date getDATEE() {
        return DATEE;
    }




    public void setDATEE(Date dATEE) {
        DATEE = dATEE;
    }




    public String getSong_change() {
        return song_change;
    }



    public void setSong_change(String song_change) {
        this.song_change = song_change;
    }





}

Every time when I run the code below exception comes:

org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [java.sql.Date] to type [com.crbt.api.services.bean.SongChangeCount]
    at org.springframework.core.convert.support.GenericConversionService.handleConverterNotFound(GenericConversionService.java:324) ~[spring-core-4.3.13.RELEASE.jar:4.3.13.RELEASE]
    at org.springframework.core.convert.support.GenericConversionService.convert(GenericConversionService.java:206) ~[spring-core-4.3.13.RELEASE.jar:4.3.13.RELEASE]
    at org.springframework.core.convert.support.ArrayToObjectConverter.convert(ArrayToObjectConverter.java:66) ~[spring-core-4.3.13.RELEASE.jar:4.3.13.RELEASE]
    at org.springframework.core.convert.support.ConversionUtils.invokeConverter(ConversionUtils.java:37) ~[spring-core-4.3.13.RELEASE.jar:4.3.13.RELEASE]
    at org.springframework.core.convert.support.GenericConversionService.convert(GenericConversionService.java:203) ~[spring-core-4.3.13.RELEASE.jar:4.3.13.RELEASE]
    at org.springframework.core.convert.support.GenericConversionService.convert(GenericConversionService.java:187) ~[spring-core-4.3.13.RELEASE.jar:4.3.13.RELEASE]
    at org.springframework.data.repository.query.ResultProcessor$ProjectingConverter.convert(ResultProcessor.java:288) ~[spring-data-commons-1.13.9.RELEASE.jar:na]
    at org.springframework.data.repository.query.ResultProcessor$ChainingConverter$1.convert(ResultProcessor.java:210) ~[spring-data-commons-1.13.9.RELEASE.jar:na]
    at org.springframework.data.repository.query.ResultProcessor$ChainingConverter.convert(ResultProcessor.java:221) ~[spring-data-commons-1.13.9.RELEASE.jar:na]
    at org.springframework.data.domain.Chunk.getConvertedContent(Chunk.java:168) ~[spring-data-commons-1.13.9.RELEASE.jar:na]
    at org.springframework.data.domain.PageImpl.map(PageImpl.java:104) ~[spring-data-commons-1.13.9.RELEASE.jar:na]
    at org.springframework.data.domain.PageImpl.map(PageImpl.java:28) ~[spring-data-commons-1.13.9.RELEASE.jar:na]
    at org.springframework.data.repository.query.ResultProcessor.processResult(ResultProcessor.java:143) ~[spring-data-commons-1.13.9.RELEASE.jar:na]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:121) ~[spring-data-jpa-1.11.9.RELEASE.jar:na]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:106) ~[spring-data-jpa-1.11.9.RELEASE.jar:na]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:499) ~[spring-data-commons-1.13.9.RELEASE.jar:na]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:477) ~[spring-data-commons-1.13.9.RELEASE.jar:na]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.13.RELEASE.jar:4.3.13.RELEASE]
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:56) ~[spring-data-commons-1.13.9.RELEASE.jar:na]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.13.RELEASE.jar:4.3.13.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) ~[spring-tx-4.3.13.RELEASE.jar:4.3.13.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282) ~[spring-tx-4.3.13.RELEASE.jar:4.3.13.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) ~[spring-tx-4.3.13.RELEASE.jar:4.3.13.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.13.RELEASE.jar:4.3.13.RELEASE]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136) ~[spring-tx-4.3.13.RELEASE.jar:4.3.13.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.13.RELEASE.jar:4.3.13.RELEASE]
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133) ~[spring-data-jpa-1.11.9.RELEASE.jar:na]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.13.RELEASE.jar:4.3.13.RELEASE]
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) ~[spring-aop-4.3.13.RELEASE.jar:4.3.13.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.13.RELEASE.jar:4.3.13.RELEASE]
    at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:57) ~[spring-data-commons-1.13.9.RELEASE.jar:na]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.13.RELEASE.jar:4.3.13.RELEASE]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) ~[spring-aop-4.3.13.RELEASE.jar:4.3.13.RELEASE]
    at com.sun.proxy.$Proxy186.getSongChangeCount2(Unknown Source) ~[na:na]
    at com.crbt.api.services.service.ContentManagerServiceImpl.getSongChangeCount(ContentManagerServiceImpl.java:433) ~[classes/:na]
    at com.crbt.api.services.controller.ContentManagerController.songChangeCounts(ContentManagerController.java:80) ~[classes/:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_181]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_181]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_181]

Can anyone tell me whats going wrong here..I tried changing the Date data type to java date but still it didn't solve my issue.

use `select new my.package.path.SongChangeCount(date(start_date) as DATEE, sum(case when subscription_from in ('IVR','SMS') and remarks like '%Change Song%' then 1 else 0 end) as 'song_change') from subscription group by date(start_date) \n#pageable\n`. This requires a constructor in `SongChangeCount` with the arguments — XtremeBaumer, Aug 30 '19 at 11:59
now its throwing `com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.crbt.api.services.bean.SongChangeCount(date(start_date) as DATEE, sum(case when' at line 1` — JPG, Aug 30 '19 at 12:11
do I need to include one constructor that accept string in that bean class — JPG, Aug 30 '19 at 12:12
I replaced old @query with this : `@Query(value= "select new com.crbt.api.services.bean.SongChangeCount(date(start_date) as DATEE, sum(case when subscription_from in ('IVR','SMS') and remarks like '%Change Song%' then 1 else 0 end) as 'song_change') from subscription group by date(start_date) \n#pageable\n",nativeQuery=true) ` — JPG, Aug 30 '19 at 12:15
I think you need to omit all the `as DATEE` stuff. So simple `select new ..crbt.api.services.bean.SongChangeCount(date(start_date))` also omit casts where they are not necessary — XtremeBaumer, Aug 30 '19 at 12:16
As you are using a native query, you cannot create a result object automatically or create one inside your query as the native query will be executed by your database, not by JPA. You could try this: https://stackoverflow.com/questions/29082749/spring-data-jpa-map-the-native-query-result-to-non-entity-pojo — Jochen Reinhardt, Aug 30 '19 at 12:21
removed as 'DATEE' and as 'song_change'.. but same jdbc4 error as mentioned above — JPG, Aug 30 '19 at 12:21
@JochenReinhardt your given link solution seems promising.. but as of now I changed my repository method to `@Query(value= "select date(start_date) as 'DATEE', sum(case when subscription_from in ('IVR','SMS') and remarks like '%Change Song%' then 1 else 0 end) as 'song_change' from subscription group by date(start_date) \n#pageable\n",nativeQuery=true) Page — JPG, Aug 30 '19 at 12:32
Why don't you try it the way it is described in the accepted answer, using a `@NamedNativeQuery` and `@SqlResultSetMapping`. This seems to be exactly what you need. — Jochen Reinhardt, Aug 30 '19 at 13:29

score 0 · Answer 1 · answered Jul 08 '22 at 15:23

0

Your ContentSongsRepository must extends JpaRepository<SongChangeCount, Date>

answered Jul 08 '22 at 15:23

Rodrigo Vergara

29
5

getting ConverterNotFoundException

1 Answers1