How to solve Spring Boot findBy method with underscore variable

Question

When I run the below project, I receive the following error. How can I fix it?

Caused by: java.lang.IllegalArgumentException: Failed to create query for method public abstract com.example.pharmanic.model.Rdhs_Hospital_Current_Stock com.example.pharmanic.repositories.Rdhs_Hospital_Current_StockRepository.findBysr_no(java.lang.String)! No property sr found for type Rdhs_Hospital_Current_Stock!

This is my Rdhs_Hospital_Current_Stock model class.

@Entity
@Data
@Table(name = "Rdhs_Hospital_Current_Stock")
public class Rdhs_Hospital_Current_Stock {
    @Id
    private Long batchId;
    private int quantity;
    private String expiredate;

    @ManyToOne
    private Hospital_By_Rdhs hospital_by_rdhs;


    @ManyToOne
    @JoinColumn(name = "sr_no", nullable = false, referencedColumnName = "sr_no")
    private Medicine medicine;


}

sr_no is the foreign key of the Medicine table.

This is my Medicine entity:

@Data
@Entity
public class Medicine {
    private @Id String sr_no;

    private String name;
    private String side_effect;
    private String description;

    public Medicine() {
    }

    public Medicine(String sr_no, String name, String side_effect, String description) {
        this.sr_no = sr_no;
        this.name = name;
        this.side_effect = side_effect;
        this.description = description;
    }
  }

When I use sr_no with my findBy() function:

 @GetMapping("/rhstock/{id}")
    ResponseEntity<?> getMedicine(@PathVariable String id){
        Optional<Rdhs_Hospital_Current_Stock> rdhs_hospital_current_stock = Optional.ofNullable(rdhs_hospital_current_stockRepository.findBysr_no(id));
         return rdhs_hospital_current_stock.map(response->ResponseEntity.ok().body(response)).orElse(new ResponseEntity<>(HttpStatus.NOT_FOUND));
    }

This is my repository:

public interface Rdhs_Hospital_Current_StockRepository extends JpaRepository<Rdhs_Hospital_Current_Stock,Long> {
    Rdhs_Hospital_Current_Stock findBysr_no(String id);

}

Answer 1

Inspired from: Spring-Data-Jpa Repository - Underscore on Entity Column Name

The underscore _ is a reserved character in Spring Data query derivation (see the reference docs for details) to potentially allow manual property path description.

• Stick to the Java naming conventions of using camel-case for member variable names and everything will work as expected.
• Change sr_no to srNo.

Update repository function

Rdhs_Hospital_Current_Stock findBymedicine_srNo(String id);

Answer 2

I solve this error. I change in Reposity Interface & Controller class like as below

Repository Interface -:

@Query(value="select * from Rdhs_Hospital_Current_Stock h where h.sr_no = :sr_no",nativeQuery=true)
 List<Rdhs_Hospital_Current_Stock> findBySr_no(@Param("sr_no")String sr_no);

Controller class -:

 @RequestMapping(value = "/rhstocksr/{sr_no}", method = RequestMethod.GET)
    List<Rdhs_Hospital_Current_Stock> getBatchByMedicine(@PathVariable("sr_no") String sr_no) {
        return rdhs_hospital_current_stockRepository.findBySr_no(sr_no);

    }

Answer 3

To keep using a derived query you may change sr_no to srNo.

You could solve this by starting using a native query, but my advice is to use derived querys everytime as it is possible to use it.