JPA Native Query Result Set Mapping to Entity class having child class

Question

(column and variable name changed after posting question)i am writing join query using entityManager.createNativeQuery(somequery) in jpa custom method when i run code i get following error :

com.ibm.db2.jcc.am.SqlException: [jcc][10150][10300][4.12.56] Invalid >parameter: Unknown column name exc_seq_nbr. ERRORCODE=-4460, SQLSTATE=null

i am using IBM DB2 server and spring boot

exceptionTenderPK (object in entity class) is not being mapped correctly thats why getting invalid column can someone please tell me how to map exceptionTenderPK object class

Note: i cant use @OneToMany in this case because tables are unrelated

@Entity
@Table(name = "Table_name")


@Data
public class MainPojoclass {
    
    @EmbeddedId
    @JsonProperty(value = "mainPojoclassPK")
    private MainPojoclassPK mainPojoclassPK;
    

    @Column(name = "amt")
    @JsonProperty(value = "amt")
    private BigDecimal amt;
    
    @Column(name = "tndid")
    @JsonProperty(value = "tndid")
    private String tndid;
    
    @Column(name = "cde")
    @JsonProperty(value = "cde")
    private String cde;
    
    @Column(name = "ind")
    @JsonProperty(value = "ind")
    private String ind;
    
    @Column(name = "user")
    @JsonProperty(value = "user")
    private String user;
    
    @Column(name = "updatedtime")
    @JsonProperty(value = "updatedtime")
    private Date updatedtime;
    
    @Column(name = "src")
    @JsonProperty(value = "src")
    private String src;
    
    @Column(name = "stat")
    @JsonProperty(value = "stat")
    private String stat;
    


}

@Transactional
public interface JoinQueryRepository extends JpaRepository<MainPojoclass, Long>, JoinQueryRepositoryCustom{
    
}

public interface JoinQueryRepositoryCustom {
    
    List<MainPojoclass> getGRDetails(MainPojoclass et,Date reportDate);

}

public class JoinQueryRepositoryImpl implements JoinQueryRepositoryCustom {

    @PersistenceContext
    EntityManager entityManager;

    @SuppressWarnings("all")
    @Override
    public List<MainPojoclass> getGRDetails(MainPojoclass et,Date rdate) {
String queryStr = "select et.Salss_DTE from table et"
                + " join dte etr on et.Salss_DTE = etr.Salss_DTE where et.nbr =? ";
        
        List<MainPojoclass> datalist = null;
                
        Query query =   entityManager.
                createNativeQuery(queryStr,"mapping")
                .setParameter(1, 222);
        datalist = query.getResultList();

        return datalist;

    }

}

score 0 · Answer 1 · answered May 17 '19 at 11:04

0

The error says that there is no column exc_seq_nbr and you used that in your EntityResult mapping.

In your query you only return et.SLS_DTE you have to return all columns that are in the result set mapping.

answered May 17 '19 at 11:04

Simon Martinelli

34,053
5
48
82

Hi thanks for reply i changed my query and selected all the columns still getting exactly same error – Krishna Vishwakarma May 17 '19 at 12:50

score 0 · Accepted Answer · answered May 20 '19 at 09:50

Hi all since i am not getting any solutions i am going with below solution it works for me and removing @SqlResultSetMapping below code is working without sql result set mapping

Query q = em.createNativeQuery(queryStr);
List<Object[]> resultList = q.getResultList();

for (Object[] result : resultList) {
   entityObj.setReason(result[0].toString);
//rest attribute will convert from result[1].toString to corresponding
// data type and set to entity object
}

JPA Native Query Result Set Mapping to Entity class having child class

2 Answers2