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!!