WSL2: Find and Delete Zone.Identifier files

Syntax

We need to create an interface which extends JpaRepository class by casting with DAO class name and Type of Primary key. This interface must be annotated with @Repository annotion which lets the Spring Container to undestand that this interface is data base repository to perform data base operations with the defined db server.

@Repository
public interface KnowyoursroRepo extends JpaRepository<Knowyoursro, Long> {
 @Query(value = "SELECT districtname FROM knowyoursro GROUP BY districtname", nativeQuery = true)
List<String> findDistinctByDistrictname(String dname);

 @Query(value = "SELECT mandalname FROM knowyoursro where districtname=?1 GROUP BY mandalname", nativeQuery = true)
List<String> findDistinctMandalsByDistrictname(String dname);

@Query(value = "SELECT villagename FROM knowyoursro where (districtname=?1 and mandalname=?2) GROUP BY villagename order by villagename", nativeQuery = true)
List<String> findAllVillagesInaMandal(String district, String mandal);

 List<Knowyoursro> findByDistrictnameAndMandalnameOrderByVillagename(String districtname, String mandalname);
List<Knowyoursro> findByDistrictnameAndMandalnameAndVillagename(String districtname, String mandalname,
			String villagename);
}

in this line we have use @Query annotation with two attributes called as value and nativeQuery, where we have provide native sql query for value and true for nativeQuery

we need to set nativeQuery as a true to execute native custom sql query in Spring Boot JPA application.

Binding value to placedholder

In the above code at line you can notice that sql query has a placed holder for districtname ( with question mark follwed by placcedholder position number that is 1 in this example ), to bind the value for ?1 we need to pass parameter to the method which annotated by @Query, in our example the parameter dname will be binding to districtname.

Binding value to Named parameters

In the following snippet of the code you can notice that :dname in a Naitve SQL query is a Named parameter which will be binded the value with @Param().

@Query(value = "SELECT mandalname FROM knowyoursro where districtname=:dname GROUP BY mandalname", nativeQuery = true)
List<String> findDistinctMandalsByDistrictname(@Param("dname") String dn);

:dname must be the same in @Param("dname") in a method parameter.