[Spring Data JPA] Derived Query Methods

Entity:

Employee.java

package com.skillsoft.springdatajpa.model;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name= "Employee")
public class Employee {
    @Id
    @GeneratedValue
    private Long id;
    private String name;
    private String email;



    public Employee() {}

    public Employee(String name, String email) {
        this.name = name;
        this.email = email;
    }

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public String getEmail() {
        return email;
    }

    public void setId(final Long id) {
        this.id = id;
    }

    public void setName(final String name) {
        this.name = name;
    }

    public void setEmail(final String email) {
        this.email = email;
    }

    @Override
    public String toString() {
        return "Emplyee{" +
                "id=" + getId() +
                ", name='" + getName() + '\'' +
                ", email='" + getEmail() + '\'' +
                '}';
    }
}

 

Basic findBy method:

	@Bean
	public CommandLineRunner EmployeeDemo(EmployeeRepository employeeRepository) {
		return (args) -> {
			employeeRepository.save(new Employee("Renee Bauer", "reene_bauer@exmaple.com"));
			employeeRepository.save(new Employee("Raj Chawanda", "raj_chawanda@exmaple.com"));
			employeeRepository.save(new Employee("Neil Parks", "neil_parks@exmaple.com"));
			employeeRepository.save(new Employee("Marcia Lin", "Marcia_lin@exmaple.com"));

			// SELECT * form Empployee
			System.out.println("\n***************");
			System.out.println("\nRetrieving all employee data..");
			for (Employee em : employeeRepository.findAll()) {
				System.out.println(em.toString());
			}

			System.out.println("\n***************");
		};
	}

[Tip]: Enhacne Java For Loop

for (Employee em : employeeRepository.findAll()) {
	System.out.println(em.toString());
}

 

findById():

// SELECT * FROM Employee WHERE id = 1
Employee em = employeeRepository.findById(1L).get();
System.out.println(em.toString());

[Tips]

1. Convert int to Long

//Int 1 convert to Long
1L
    
// or
Long.valueOf(1)

2. findById(Long) migth not find a value, so it return a Optional Type, similar to Maybe, Either type in fp-js.

To get value out of Optional type, you will to call get()

import java.util.Optional;

Optional<Employee> oem = employeeRepository.findById(10L);
Employee em = oem.get();

If not value found, will throw in the code above, to improve it

Optional<Employee> oem = employeeRepository.findById(10L);
if (!oem.isEmpty()) {
	Employee em = oem.get();
	System.out.println(em.toString());
}

 

findBy*():

Currently we have an empty repository:

package com.skillsoft.springdatajpa.repository;

import org.springframework.data.repository.CrudRepository;
import com.skillsoft.springdatajpa.model.Employee;
public interface EmployeeRepository extends CrudRepository<Employee, Long> {

}

 

If we want to do findByEmail(string), we can do:

public interface EmployeeRepository extends CrudRepository<Employee, Long> {
    Employee findByEmail(String email);
}

Employee em2 = employeeRepository.findByEmail("neil_parks@exmaple.com");
System.out.println(em2.toString());

 

Handle multi results return by findBy*:

public interface EmployeeRepository extends CrudRepository<Employee, Long> {
    Employee findByEmail(String email);
    List<Employee> findByName(String name);
}

List<Employee> employees = employeeRepository.findByName("Marcia Lin");
for (Employee ee : employees) {
	System.out.println(ee.toString());
}

 

Handle empty result return by findBy*:

import java.util.List;
import java.util.Optional;

public interface EmployeeRepository extends CrudRepository<Employee, Long> {
    Optional<Employee> findByEmail(String email);
    Optional<List<Employee>> findByName(String name);
}

 

Derived Query Methods

findBy*AOr*B: search based on mulit columns (A columns OR B columns)

public interface EmployeeRepository extends CrudRepository<Employee, Long> {
    Optional<Employee> findByEmail(String email);
    Optional<List<Employee>> findByName(String name);

    // SELECT * FROM Employee WHERE name = <name> OR email = <email>
    Optional<List<Employee>> findByNameOrEmail(String name, String email);
}

List<Employee> ems = employeeRepository.findByNameOrEmail("Marcia Lin", "ryan_sato@exmaple.com").get();

 

findBy*CAnd*D:

Optional<List<Employee>> findByNameAndLevel(String name, int level);

 

findBy*Is/Equals/IsNot:

Optional<List<Employee>> findByNameIs(String name);
Optional<List<Employee>> findByNameEquals(String name);
Optional<List<Employee>> findByNameIsNot(String name);

 

Partten matching:

// SELECT * FROM Employee WHERE name LIKE '%<prefix>'
Optional<List<Employee>> findByNameStartsWith(String prefix);

Optional<List<Employee>> findByNameEndingWith(String suffix);
Optional<List<Employee>> findByNameIgnoreCase(String name);
Optional<List<Employee>> findByNameLike(String patern);
Optional<List<Employee>> findByEmailContaining(String infix);

 

Ordering and get first

Optional<Employee> findFirstByName(String name);
Optional<Employee> findFirstByNameOrderByLevelAsc(String name);
Optional<Employee> findFirstByOrderByEmail();

 

Exmaples:

employeeRepository.findByNameStartsWith("R");
employeeRepository.findByNameEndingWith("s");
employeeRepository.findByNameIgnoreCase("LIN");
employeeRepository.findByNameLike("%is%");
employeeRepository.findFirstByName("Marcia Lin");

 

Search for Records based on Range

List<Employee> findByLevelLessThan(int level);
List<Employee> findByLevelLessThanEqual(int level);
List<Employee> findByLevelGreaterThan(int level);
List<Employee> findFirst2ByLevelOrderByBirthDateDesc(int level);
List<Employee> findByBirthDateBefore(Date before);
List<Employee> findByBirthDateAfter(Date before);
List<Employee> findByBirthDateBetween(Date start, Date end);
Employee findTopByBirthDateDesc();

Date refDate = new SimpleDateFormat("dd/MM/yyyy").parse("08/05/1994");
Date startDate = new SimpleDateFormat("dd/MM/yyyy").parse("08/05/1988");
Employee emps = employeeRepository.findByBirthDateBetween(startDate, refDate);

 

Update and delete

Optional<Employee> em2 = employeeRepository.findByEmail("neil_parks@exmaple.com");
if (em2.isPresent()) {
	Employee ee = em2.get();
	ee.setEmail("neil_parks@gmail.com");
	employeeRepository.save(ee);
}

Optional<Employee> em3 = employeeRepository.findByEmail("Marcia_lina2@exmaple.com");
if (em3.isPresent()) {
	Employee ee3 = em3.get();
	employeeRepository.delete(ee3);
	employeeRepository.deleteById(1L);
}