spring boot data jpa custom finder methods

 spring boot data jpa finder methods

For explanation Watch video :

Directory Structure

dependencies::

lombok

Spring data JPA

Mysql Driver

application.properties

#datasource properties

spring.datasource.url=jdbc:mysql://localhost:3306/new

spring.datasource.username=root

spring.datasource.password=root

#jpa

spring.jpa.hibernate.ddl-auto=update

spring.jpa.show-sql=true

SpringBootDataJpaFinderApplication 

package com.app;

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication

public class SpringBootDataJpaFinderApplication {

public static void main(String[] args) {

SpringApplication.run(SpringBootDataJpaFinderApplication.class, args);

}

}

Employee.java

package com.app.model;

import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import javax.persistence.Table;

import lombok.AllArgsConstructor;

import lombok.Data;

import lombok.NoArgsConstructor;

@Data

@NoArgsConstructor

@AllArgsConstructor

@Entity

@Table(name = "emptab")

public class Employee {

@Id

@GeneratedValue(strategy = GenerationType.IDENTITY)

@Column(name = "eid")

private Integer empId;

@Column(name = "ename")

private String empName;

@Column(name = "dept")

private String empDept;

@Column(name = "sal")

private Double empSal;

public Employee(String empName, String empDept, Double empSal) {

super();

this.empName = empName;

this.empDept = empDept;

this.empSal = empSal;

}

}

EmployeeRepository 

package com.app.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;

import com.app.model.Employee;

public interface EmployeeRepository extends JpaRepository<Employee, Integer>{

// select * from emptab where ename = name

public List<Employee> findByEmpName(String name);

// select * from emptab where dept = name

public List<Employee> findByEmpDept(String name);

// select * from emptab where ename like 'initChars%';

public Iterable<Employee> findByEmpNameStartingWith(String initChars);

// select * from emptab where ename like '%chars';

public Iterable<Employee> findByEmpNameEndingWith(String chars);

// select * from emotab where ename like '%a%'

public Iterable<Employee> findByEmpNameContaining(String chars);

// select * from emptab where ename like '%r%'

public Iterable<Employee> findByEmpNameLike(String chars);// pass valid wild chars

// select * from emptab where empId>? and empSal<?

public Iterable<Employee> findByEmpIdGreaterThanAndEmpSalLessThan(int start, Double sal);

// select * from emptab where empId>? and empSal>?

public Iterable<Employee> findByEmpIdGreaterThanAndEmpSalGreaterThan(int start, Double sal);

// select * from emptab where empName like ? or (empSal between ? and ?);

public Iterable<Employee> findByEmpNameStartingWithOrEmpSalBetween(String nameChar, Double start, Double end);

// select * from emptab where empName like ? or (empSal between ? and ?);

public Iterable<Employee> findByEmpNameStartingWithAndEmpSalBetween(String nameChar, Double start, Double end);

//select * from emptab where dept in(?,?,?) or ename like '?' and sal between ? and ?

public Iterable<Employee> findByEmpDeptInOrEmpNameLikeAndEmpSalBetween(List<String> dept,String ch,Double s,Double e);

//select * from emptab where ename not like '?' and sal(?,?) order by ename asc

public Iterable<Employee> findByEmpNameNotLikeAndEmpSalInOrderByEmpNameAsc(String letters,List<Double> l);

}

TestRunner 

package com.app.runner;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.CommandLineRunner;

import org.springframework.stereotype.Component;

import com.app.repository.EmployeeRepository;

@Component

public class TestRunner implements CommandLineRunner{

@Autowired

private EmployeeRepository repo;

@Override

public void run(String... args) throws Exception {

//repo.findByEmpDept("BA").forEach(System.out::println);

//repo.findByEmpNameStartingWith("SA").forEach(System.out::println);

//repo.findByEmpNameEndingWith("la").forEach(System.out::println);

//repo.findByEmpNameContaining("j").forEach(System.out::println);

//repo.findByEmpNameLike("%la").forEach(System.out::println);

//repo.findByEmpIdGreaterThanAndEmpSalLessThan(5,6000.0).forEach(System.out::println);

//repo.findByEmpIdGreaterThanAndEmpSalGreaterThan(5,6000.0).forEach(System.out::println);

//repo.findByEmpNameStartingWithOrEmpSalBetween("A",6000.0,16000.0).forEach(System.out::println);

//repo.findByEmpNameStartingWithAndEmpSalBetween("A",6000.0,16000.0).forEach(System.out::println);

//repo.findByEmpDeptInOrEmpNameLikeAndEmpSalBetween(List.of("DEV","BA"),"k%",5000.0,6000.0).forEach(System.out::println);

repo.findByEmpNameNotLikeAndEmpSalInOrderByEmpNameAsc("S%",List.of(5000.0,6000.0)).forEach(System.out::println);

}

}