Getting error Could not locate appropriate constructor on class

Question

I am trying to map native SQL result to my POJO. Here is the configuration. I am using spring.

<bean id="ls360Emf" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" >
    <property name="dataSource" ref="ls360DataSource" />
    <property name="jpaVendorAdapter" ref="vendorAdaptor" />         
    <property name="packagesToScan" value="abc.xyz"/>
    <property name="jpaProperties">
        <props>
            <prop key="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</prop>
            <prop key="hibernate.max_fetch_depth">3</prop>
            <prop key="hibernate.jdbc.fetch_size">50</prop>
            <prop key="hibernate.jdbc.batch_size">10</prop>
            <prop key="hibernate.show_sql">true</prop>              
        </props>        
    </property>
</bean> 

Here is my Class

@SqlResultSetMapping(
    name="courseCompletionMapping", 
    classes = {
        @ConstructorResult(targetClass = CourseCompletion.class,
            columns={
                @ColumnResult(name = "StoreId", type = String.class),
                @ColumnResult(name = "ProductId", type = String.class),
                @ColumnResult(name = "UserName", type = String.class),
                @ColumnResult(name = "Score", type = Integer.class),
                @ColumnResult(name = "CompletionDate", type = Date.class)
             }
        )
    }
) 
@Entity
public class CourseCompletion {
    private String storeId;

    @Id
    private String productId;
    private String userName;
    private int score;
    private Date completionDate;

    public CourseCompletion() {
    }

    public CourseCompletion(String storeId, String productId, String userName, int score, Date completionDate) {
        this.storeId = storeId;
        this.productId = productId;
        this.userName = userName;
        this.score = score;
        this.completionDate = completionDate;
    }

    // getters and setters

Here how i am calling it

    Properties coursePropertiesFile = SpringUtil.loadPropertiesFileFromClassPath("course.properties");
    String queryString = coursePropertiesFile.getProperty("course.completion.sql");

    long distributorId = 1;
    String fromDate = "2009-09-22 00:00:00";
    String toDate = "2014-04-11 23:59:59";

     Query query = em.createNativeQuery(queryString, "courseCompletionMapping");

     //Query query = em.createNamedQuery("findAllEmployeeDetails");
     query.setParameter("distributorId", distributorId);
     query.setParameter("fromDate", fromDate);
     query.setParameter("toDate", toDate);

     @SuppressWarnings("unchecked")
     List<CourseCompletion> courseCompletionList = query.getResultList();

But when it comes to line

List<CourseCompletion> courseCompletionList = query.getResultList();

I get an error that

Could not locate appropriate constructor on class : mypackage.CourseCompletion

Here is the query that i am trying

select d.DISTRIBUTORCODE AS StoreId, u.USERGUID AS ProductId, u.UserName,
    lcs.HIGHESTPOSTTESTSCORE AS Score, lcs.CompletionDate 
from VU360User u 
inner join learner l on u.ID = l.VU360USER_ID 
inner join LEARNERENROLLMENT le on le.LEARNER_ID = l.ID 
inner join LEARNERCOURSESTATISTICS lcs on lcs.LEARNERENROLLMENT_ID = le.ID 
inner join customer c on c.ID = l.CUSTOMER_ID 
inner join DISTRIBUTOR d on d.ID = c.DISTRIBUTOR_ID 
where d.ID = :distributorId 
and lcs.COMPLETIONDATE is not null 
and (lcs.COMPLETIONDATE between :fromDate and :toDate) 
and lcs.COMPLETED = 1

Why i am getting this error ?

Thanks

Answer 1

This exception happens because JPA doesn't change column types returned from the database for native queries. Because of this, you have type mismatch. I'm not sure about which column causes this problem in your case (this depends on DBMS you use), but I would suspect you have BigInteger in the result set instead of Integer for score column. To be 100% sure, add a breakpoint to ConstructorResultColumnProcessor.resolveConstructor(Class targetClass, List<Type> types) and investigate. After you find a mismatch, change field type in your mapping class.

Another solution will be not to use @SqlResultSetMapping at all. As your CourseCompletion class is a managed entity, you should be able to map native query to it directly. See this question for more information.