findBy
findBy Methods : Data JPA *) @Query("hql/jpql") : By using this we can execute our custom query. similer to this we have findBy methods. => findBy methods are abstract methods, These are converted to SQL query at runtime No need of wiriting any query. +----------------------------------------------+ | abstractMethod(findBy____()) ===> SQL Query | +----------------------------------------------+ => It suuports only SELECT operations. => It supports even Projections. =>** If we compare with JDBC concept they are PreparedStatements. => We should follow somes rules to write this findBy methods. Syntax: (Define inside Repository Interface) ReturnType findBy<VariablesAndConditions>(<DataType> <paramName>) --ex#---------------------------------------- class Employee { int empId; String empName; Double empSal; } #3 List<Employee> findByEmpSal(Double esal); Generated SQL: SELECT * FROM EMPLOYEE WHERE empSal=:esal #1 //findByVariableName(DataType paramName) List<Employee> findByempName(String ename); // both are valid List<Employee> findByEmpName(String ename); //recomanded Generated SQL: SELECT * FROM EMPLOYEE WHERE empName=:ename #2 Method: //findByVariableName(DataType paramName) List<Employee> findByempId(int eid); List<Employee> findByEmpId(int eid); // Recomanded Equal SQL(Generated): SELECT * FROM Employee WHERE empId=:eid ====================================================== class Student { Integer stdId; String stdName; Double stdFee; } Method : List<Student> findByStdFee(Double sfee); Generated SQL: SELECT * FROM STUDENT WHERE STDFEE=:sfee =========================================================== Dynamic Proxy Generates logic for findBy methods at runtime using EntityManager code. ------------------------------- interface TestRepository extends JpaRepository { findBy___(); } class $1 implements TestRepository { findBy___(){ em.getQuery(___); } } Q) Find Student where names not having size 3 chars? A) SELECT * FROM STDNAME WHERE SNAME NOT LIKE '___'; One _(underscore) indicates one char. =================Full Code================================ 1. model package in.nareshit.raghu.model; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.Table; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; @Data @NoArgsConstructor @AllArgsConstructor @Entity @Table(name="stdtab") public class Student { @Id @Column(name="sid") private Integer stdId; @Column(name="sname") private String stdName; @Column(name="sfee") private Double stdFee; @Column(name="scourse") private String stdCourse; } 2. Repository package in.nareshit.raghu.repo; import java.util.List; import org.springframework.data.jpa.repository.JpaRepository; import in.nareshit.raghu.model.Student; public interface StudentRepository extends JpaRepository<Student, Integer> { //SQL: select * from stdtab where scourse=? //findBy<VariableName>(DataType paramName) List<Student> findByStdCourse(String course); //SQL: select * from stdtab where sfee=? List<Student> findByStdFee(Double sfee); //SQL: SELECT *FROM STUDENT WHERE stdId>=? //findBy<VariableNameKeyword>(DataType paramName) List<Student> findByStdIdGreaterThanEqual(Integer sid); //SQL: SELECT *FROM STUDENT WHERE stdFee>? List<Student> findByStdFeeGreaterThan(Double sfee); //SQL: SELECT *FROM STUDENT WHERE stdId between ? and ? List<Student> findByStdIdBetween(Integer sid1,Integer sid2); //SQL: SELECT *FROM STUDENT WHERE stdName IS NULL List<Student> findByStdNameIsNull(); //SQL: SELECT *FROM STUDENT WHERE stdName IS NOT NULL List<Student> findByStdNameNotNull(); //List<Student> findByStdNameIsNotNull(); /** Here it is treating like variable name is 'stdNamenotnull' * So Throwing exception */ // PropertyReferenceException: No property stdNamenotnull found for type Student! //List<Student> findBystdNamenotnull(); //SQL: SELECT *FROM STUDENT WHERE stdName Like ? List<Student> findByStdNameLike(String input); //SQL: SELECT *FROM STUDENT WHERE stdName not Like ? List<Student> findByStdNameNotLike(String input); } 3. Data Insert Runner package in.nareshit.raghu.runner; import java.util.Arrays; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.CommandLineRunner; import in.nareshit.raghu.model.Student; import in.nareshit.raghu.repo.StudentRepository; //@Component public class StudentDataInsertRunner implements CommandLineRunner { @Autowired private StudentRepository repo; public void run(String... args) throws Exception { repo.saveAll(Arrays.asList( new Student(10, "SAM", 300.0, "Core Java"), new Student(11, "RAM", 400.0, "Core Java"), new Student(12, "SYED", 500.0, "Adv Java"), new Student(13, "AJAY", 400.0, "Adv Java"), new Student(14, "JAI", 300.0, "MS"), new Student(15, "KUMAR", 600.0, "BOOT"), new Student(16, "SANJU", 400.0, "BOOT") )); repo.findAll() .forEach(System.out::println); ; } } 4. Test Runner package in.nareshit.raghu.runner; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.CommandLineRunner; import org.springframework.stereotype.Component; import in.nareshit.raghu.repo.StudentRepository; @Component public class TestFindByRunner implements CommandLineRunner { @Autowired private StudentRepository repo; public void run(String... args) throws Exception { //repo.findByStdCourse("Core Java") //repo.findByStdFee(300.0) //repo.findByStdIdGreaterThanEqual(10) //repo.findByStdFeeGreaterThan(300.0) //repo.findByStdIdBetween(11, 15) //repo.findByStdNameIsNull() //repo.findBystdNamenotnull() //repo.findByStdNameLike("S%") repo.findByStdNameNotLike("___") .forEach(System.out::println); } } 5. application yaml spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver password: root url: jdbc:mysql://localhost:3306/boot8pm username: root jpa: database-platform: org.hibernate.dialect.MySQL8Dialect hibernate: ddl-auto: update show-sql: true
No Comments Yet!!