Spring Data JPA derived query returns 0 records where as @Query fetches correct record-CodePudding

My JPA derived method findByBatchIdAndInstitute(Long id, Institute inst) does not fetch correct records. It returns 0 records. However if I use @Query with native query it works fine.

Any idea on why derived method does not fetch records?. I made sure the variable names "batchId" and "institute" are spelled correctly in the derived method. I am not able to figure out anything by turning on JPA show sql in console

Below is my Entity details ....

@Entity
@Table(name = "Batch")
public class BatchEntity implements Serializable{
    
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long batchId;
    
    private String batchName;
    
    @OneToOne
    private ClassEntity clazz;
    
    @ManyToOne(targetEntity = InstituteEntity.class)
    @JoinColumn(name = "instId")
    @JsonIgnoreProperties({"batchList", "classList"})
    private InstituteEntity institute;
    
}

@Entity
@Table(name = "Institute")
public class InstituteEntity implements Serializable{
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long instId;
    
    private String instituteName;
    
    @OneToMany(targetEntity=BatchEntity.class, mappedBy="institute", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JsonIgnoreProperties("institute")
    private List<BatchEntity> batchList;
    
}


@Repository
public interface BatchRepository extends JpaRepository<BatchEntity, Long>{
    
    
    Optional<BatchEntity> findByBatchIdAndInstitute(Long batchId, InstituteEntity institute);
    
    @Query(value = 
            "SELECT * FROM batch b, institute i WHERE b.batch_id = :batchId AND i.inst_id = :instituteId", 
            nativeQuery = true)
    Optional<BatchEntity> findByBatchIdAndInstituteId(@Param("batchId") Long batchId, @Param("instituteId") Long instituteId);
    
}

JPA sql log details ....

select institutee0_.inst_id as inst_id1_3_0_, institutee0_.institute_name as institut2_3_0_ from institute institutee0_ where institutee0_.inst_id=?
select batchentit0_.batch_id as batch_id1_0_, batchentit0_.batch_name as batch_na2_0_, batchentit0_.clazz_class_id as clazz_cl3_0_, batchentit0_.inst_id as inst_id4_0_ from batch batchentit0_ where batchentit0_.batch_id=? and batchentit0_.inst_id=?

CodePudding user response：

@Query(value = 
        "SELECT * FROM batch b, institute i WHERE b.batch_id = :batchId AND i.inst_id = :instituteId", 
        nativeQuery = true)

Look if this native is correct, then the query getting SELECT *(all) records from database. *(all) means you should get a List<BatchEntity> from this query

Declare it List<BatchEntity> , i think it should solve the issue

CodePudding user response：

I don't think that Spring Data JPA derived queries really work with complext Objects as arguments of the methods. What Spring Data JPA is capable of handling is nested data, so try the following instead:

Optional<BatchEntity> findByBatchIdAndInstituteInstId(Long batchId, Long instituteId);