Query Method in Spring Data JPA

➤ Java Bean with Examples
➤ How to Code a Game
➤ Array Programs in Java
➤ Java Inline Thread Creation
➤ Java Custom Exception
➤ Hibernate vs JDBC
➤ Object Relational Mapping
➤ Check Oracle DB Size
➤ Check Oracle DB Version
➤ Generation of Computers
➤ XML Pros & Cons
➤ Git Analytics & Its Uses
➤ Top Skills for Cloud Professional
➤ How to Hire Best Candidates
➤ Scrum Master Roles & Work
➤ CyberSecurity in Python
➤ Protect from Cyber-Attack
➤ Solve App Development Challenges
➤ Top Chrome Extensions for Twitch Users
➤ Mistakes That Can Ruin Your Test Metric Program

Query Method in Spring Data JPA | findBy is an abstract method that derives a query at runtime based on details in the method name.

  • findBy supports both SQL(MySQL/Oracle) and NoSQL(MongoDB) Databases.
  • findBy best suitable for simple queries.
  • findBy supports only SELECT operations.
  • No Manual Query is required.

What is the difference between findBy and @Query? @Query works for SQL databases only but supports both SELECT and non-SELECT operations. In @Query custom/manual query must be given and it supports Complex Queries. Whereas findBy works for both SQL and NoSQL databases but supports only SELECT operations, not non-select operations. The findBy() can be used for simple tasks, and for complex tasks @Query can be used.

The findBy method must be written inside Repository Interface. By below syntax:-

ReturnType findBy<VariableNameConditions>(<DataTypes> <params>);

Simple one (without condition):-

ReturnType findBy<VariableName>(<params>);

Consider the Employee class and let us see some examples of findBy method:-

class Employee {
    Integer empId;
    String empName;
    Double empSal;
}
List<Employee> findByempName(String en); // valid
List<Employee> findByEmpName(String empName); // valid + following naming convension

It will be internally converted to:- SQL: select * from Employee where empName=en;

How query is formed?

  • findBy => SELECT * FROM
  • variable => where variable = param
List<Employee> findByEmpSal(double empSal);
Generated SQL:- select * from Employee where empSal=empSal;
List<Employee> findByEmpIdAndEmpName(Integer empId, double empSal);
Generated SQL:- select * from Employee where empId=empId AND empSal=empSal;

Assume we have a query:- SELECT * FROM Employee WHERE empId >=? or empName IS Not Null and empSal in (_,_,_);. It can be written using findBy() as follows:-

List<Employee> findByEmpIdAndEmpNameIsNotNullAndEmpSalIn(Integer empId, List<Double> sals)

The above method is very lengthy therefore it is not a standard even, and better to use @Query in that case.

findBy Example in Spring Boot Data JPA

In YML file:-

spring:
  datasource:
    password: root
    url: jdbc:mysql://localhost:3306/test
    username: root
  jpa:
    database-platform: org.hibernate.dialect.MySQL8Dialect
    show-sql: true
    hibernate:
      ddl-auto: update
    properties:
      hibernate:
        format_sql: true
@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity
public class Employee {
    @Id
    private Integer empId;
    private String empName;
    private Double empSal;
    private String empDept;
}
public interface EmployeeRepository extends JpaRepository<Employee, Integer> {
    // place findBy methods here
    List<Employee> findByEmpDept(String empDept);
}

Runner class to insert some data:-

@Component
@Order(1)
public class DataInsertRunner implements CommandLineRunner {

    @Autowired
    private EmployeeRepository employeeRepository;

    @Override
    public void run(String... args) throws Exception {
        employeeRepository.saveAll(
            List.of(
                new Employee(10, "SAM", 500.0, "DEV"), 
                new Employee(11, "RAM", 600.0, "DEV"),
                new Employee(12, "SYED", 450.0, "QA"), 
                new Employee(13, "VINAY", 280.0, "QA"),
                new Employee(14, "JAI", 120.0, "DEV"), 
                new Employee(15, "SUN", 590.0, "BA")
            )
        );
    }
}

Runner class to test the findBy method:-

@Component
@Order(2)
public class TestRunner implements CommandLineRunner {

    @Autowired
    private EmployeeRepository employeeRepository;

    @Override
    public void run(String... args) throws Exception {
        employeeRepository.findByEmpDept("DEV").forEach(System.out::println);
    }
}

After running the application, generated SQL query:-

Hibernate: 
    select
        e1_0.emp_id,
        e1_0.emp_dept,
        e1_0.emp_name,
        e1_0.emp_sal 
    from
        employee e1_0 
    where
        e1_0.emp_dept=?

Output:-

Employee(empId=10, empName=SAM, empSal=500.0, empDept=DEV)
Employee(empId=11, empName=RAM, empSal=600.0, empDept=DEV)
Employee(empId=14, empName=JAI, empSal=120.0, empDept=DEV)

More findBy Examples

SQLfindBy Respresentation
<LessThan
<=LessThanEqual
>GreaterThan
>=GreaterThanEqual
>x AND <yBetween
// SELECT * FROM EMPLOYEE WHERE EMPSAL<=empSal
List<Employee> findByEmpSalLessThanEqual(Double empSal);

// SELECT * FROM EMPLOYEE WHERE EMPSAL>empSal
List<Employee> findByEmpSalGreaterThan(Double empSal);

// SELECT * FROM EMPLOYEE WHERE EMPSAL between empSal1 and empSal2
List<Employee> findByEmpSalBetween(Double empSal1, Double empSal2);
SQLfindBy Respresentation
!=Not
IN OperatorIn
NOT IN OperatorNotIn
// SELECT * FROM EMPLOYEE WHERE EMPDEPT != ?
List<Employee> findByEmpDeptNot(String empDept);

// SELECT * FROM EMPLOYEE WHERE EMPDID IN (_,_,_,_,..)
List<Employee> findByEmpIdIn(List<Integer> ids);

// SELECT * FROM EMPLOYEE WHERE EMPDID NOT IN (_,_,_,_,..)
List<Employee> findByEmpIdNotIn(List<Integer> ids);

Examples of AND and OR

// SELECT * FROM EMPLOYEE WHERE EMPSAL>=? and EmpDept!=?
List<Employee> findByEmpSalGreaterThanEqualAndEmpDeptNot(Double empSal, String empDept);

// SELECT * FROM EMPLOYEE WHERE EMPSAL>=? or EmpDept!=?
List<Employee> findByEmpSalGreaterThanEqualOrEmpDeptNot(Double empSal, String empDept);

Examples of IS NULL and IS NOT NULL

// SELECT * FROM EMPLOYEE WHERE EMPNAME IS NULL
List<Employee> findByEmpNameIsNull();

// SELECT * FROM EMPLOYEE WHERE EMPNAME IS NOT NULL
List<Employee> findByEmpNameIsNotNull();

Examples of Like, NotLike, StartingWith, EndingWith, Containing

// SELECT * FROM EMPLOYEE WHERE EMPNAME like 'exp'
List<Employee> findByEmpNameLike(String expression);

// SELECT * FROM EMPLOYEE WHERE EMPNAME NOT like 'exp'
List<Employee> findByEmpNameNotLike(String expression);

// SELECT * FROM EMPLOYEE WHERE EMPNAME like 'exp%'
List<Employee> findByEmpNameStartingWith(String expression);

// SELECT * FROM EMPLOYEE WHERE EMPNAME like '%exp'
List<Employee> findByEmpNameEndingWith(String expression);

// SELECT * FROM EMPLOYEE WHERE EMPNAME like '%exp%'
List<Employee> findByEmpNameContaining(String expression);

Examples of IsTrue and IsFalse

Assume Employee also has a column “deleted” of type Boolean. Then to find deleted and non-deleted employees:-

// SELECT * FROM EMPLOYEE WHERE deleted = false
List<Employee> findByDeletedFalse();

// SELECT * FROM EMPLOYEE WHERE deleted = true
List<Employee> findByDeletedTrue();

// SELECT * FROM EMPLOYEE WHERE EMPID = id AND deleted = false
List<Employee> findByEmpIdAndDeletedFalse(Integer id);

Complete code

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {

    List<Employee> findByEmpDept(String empDept);

    // SELECT * FROM EMPLOYEE WHERE EMPSAL<=empSal
    List<Employee> findByEmpSalLessThanEqual(Double empSal);

    // SELECT * FROM EMPLOYEE WHERE EMPSAL>empSal
    List<Employee> findByEmpSalGreaterThan(Double empSal);

    // SELECT * FROM EMPLOYEE WHERE EMPSAL between empSal1 and empSal2
    List<Employee> findByEmpSalBetween(Double empSal1, Double empSal2);

    // SELECT * FROM EMPLOYEE WHERE EMPDEPT != ?
    List<Employee> findByEmpDeptNot(String empDept);

    // SELECT * FROM EMPLOYEE WHERE EMPDID IN (_,_,_,_,..)
    List<Employee> findByEmpIdIn(List<Integer> ids);

    // SELECT * FROM EMPLOYEE WHERE EMPDID NOT IN (_,_,_,_,..)
    List<Employee> findByEmpIdNotIn(List<Integer> ids);

    // SELECT * FROM EMPLOYEE WHERE EMPSAL>=? and EmpDept!=?
    List<Employee> findByEmpSalGreaterThanEqualAndEmpDeptNot(Double empSal, String empDept);

    // SELECT * FROM EMPLOYEE WHERE EMPSAL>=? or EmpDept!=?
    List<Employee> findByEmpSalGreaterThanEqualOrEmpDeptNot(Double empSal, String empDept);

    // SELECT * FROM EMPLOYEE WHERE EMPNAME IS NULL
    List<Employee> findByEmpNameIsNull();

    // SELECT * FROM EMPLOYEE WHERE EMPNAME IS NOT NULL
    List<Employee> findByEmpNameIsNotNull();

    // SELECT * FROM EMPLOYEE WHERE EMPNAME like 'exp'
    List<Employee> findByEmpNameLike(String expression);

    // SELECT * FROM EMPLOYEE WHERE EMPNAME like 'exp'
    List<Employee> findByEmpNameNotLike(String expression);

    // SELECT * FROM EMPLOYEE WHERE EMPNAME like 'exp%'
    List<Employee> findByEmpNameStartingWith(String expression);

    // SELECT * FROM EMPLOYEE WHERE EMPNAME like '%exp'
    List<Employee> findByEmpNameEndingWith(String expression);

    // SELECT * FROM EMPLOYEE WHERE EMPNAME like '%exp%'
    List<Employee> findByEmpNameContaining(String expression);

}
@Component
@Order(2)
public class TestRunner implements CommandLineRunner {

    @Autowired
    private EmployeeRepository employeeRepository;

    @Override
    public void run(String... args) throws Exception {
        employeeRepository.findByEmpDept("DEV").forEach(System.out::println);
        employeeRepository.findByEmpSalLessThanEqual(300.0).forEach(System.out::println);
        employeeRepository.findByEmpSalGreaterThan(300.0).forEach(System.out::println);
        employeeRepository.findByEmpSalBetween(300.0, 500.0).forEach(System.out::println);
        employeeRepository.findByEmpDeptNot("DEV").forEach(System.out::println);
        employeeRepository.findByEmpIdIn(Arrays.asList(10, 12, 14, 16, 18, 20, 22)).forEach(System.out::println);
        employeeRepository.findByEmpIdNotIn(Arrays.asList(12, 14, 18, 20, 22)).forEach(System.out::println);
        employeeRepository.findByEmpSalGreaterThanEqualAndEmpDeptNot(120.0, "QA").forEach(System.out::println);
        employeeRepository.findByEmpSalGreaterThanEqualOrEmpDeptNot(120.0, "QA").forEach(System.out::println);
        employeeRepository.findByEmpNameIsNull().forEach(System.out::println);
        employeeRepository.findByEmpNameIsNotNull().forEach(System.out::println);
        employeeRepository.findByEmpNameNotLike("S%").forEach(System.out::println);
        employeeRepository.findByEmpNameLike("S%").forEach(System.out::println);
        employeeRepository.findByEmpNameStartingWith("S").forEach(System.out::println);
        employeeRepository.findByEmpNameLike("%M").forEach(System.out::println);
        employeeRepository.findByEmpNameEndingWith("M").forEach(System.out::println);
        employeeRepository.findByEmpNameLike("%A%").forEach(System.out::println);
        employeeRepository.findByEmpNameContaining("A").forEach(System.out::println);
    }
}

Projections using findBy in Spring Data JPA

Instead of fetching all columns, fetching only some specific columns is known as projection.

By default, findBy is fetching all columns data (List). But now, we will be using Projections that help us to get only specific columns (List).

Step-1:- Define one interface inside the Repository (Inner Interface) that contains variables equal to get method that needs to be projected.

interface <CustomType> {
    DataType get<VariabeName>();
}

Step-2:- Use This interface as ReturnType to findBy method.

List<CustomType> findBy<VariablesConditions>(<Params>);

Step 3:- Call the above method inside Runner/Service etc For testing.

@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity
public class Student {
    @Id
    private Integer stdId;
    private String stdName;
    private Double stdFee;
    private String stdCourse;
}
public interface StudentRepository extends JpaRepository<Student, Integer> {

    interface MyView {
        String getStdName();
        Double getStdFee();
    }

    // SQL: select sname,sfee from student sid<=?
    List<MyView> findByStdIdLessThanEqual(Integer sid);

    interface MyViewTwo {
        String getStdName();
        String getStdCourse();
    }

    // SQL: select sname, course from student where sname is not null
    List<MyViewTwo> findByStdNameIsNotNull();
}
@Component
@Order(3)
public class DataInsertRunner2 implements CommandLineRunner {

    @Autowired
    private StudentRepository studentRepository;

    @Override
    public void run(String... args) throws Exception {
        studentRepository.saveAll(
            List.of(
                new Student(101, "A", 300.0, "JAVA"), 
                new Student(102, "N", 400.0, "UI"),
                new Student(103, "B", 500.0, "JAVA"), 
                new Student(104, "G", 800.0, "AWS")
            )
        );
    }
}
@Component
@Order(4)
public class TestRunner2 implements CommandLineRunner {

    @Autowired
    private StudentRepository studentRepository;

    @Override
    public void run(String... args) throws Exception {
        List<MyView> list = studentRepository.findByStdIdLessThanEqual(200);
        for (MyView m : list) {
            System.out.println(m.getStdName() + " -" + m.getStdFee());
        }

        studentRepository.findByStdNameIsNotNull().stream()
                .map(ob -> ob.getStdName() + "-" + ob.getStdCourse())
                .forEach(System.out::println);
    }
}

Output and generated SQL queries:-

Hibernate: 
    select
        s1_0.std_name,
        s1_0.std_fee 
    from
        student s1_0 
    where
        s1_0.std_id<=?

A -300.0
N -400.0
B -500.0
G -800.0

Hibernate: 
    select
        s1_0.std_name,
        s1_0.std_course 
    from
        student s1_0 
    where
        s1_0.std_name is not null

A-JAVA
N-UI
B-JAVA
G-AWS

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Leave a Comment

Your email address will not be published. Required fields are marked *