Data JPA-Custom Query

a. @Query Annotation 

-> This annotation supports both SELECT and NON-SELECT(update/delete)
-> It Supports both SQL and HQL/JPQL Query.
-> Supports Data Projections (specific column select)
-> We can even pass parameters(data at runtime)
-> We can implement JOINs (INNER JOIN, LEFT JOIN...etc)
-> We can procedure calls.

b. findBy methods

-> It supports only SELECT operation
-> No need of writing any query, just define abstract method(ie converted to Query internally)
-> This is better for simple queries.

For Non Select Query we need to anotate
@Query + @Modifying + @Transactional -- NON-SELECT (Update/Delete)
1. Model Class
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="emp_tab")
public class Employee {
	
	@Id
	@Column(name="eid")
	private Integer empId;
	@Column(name="ename")
	private String empName;
	@Column(name="esal")
	private Double empSal;
		
}
2. Repository Interface
package in.nareshit.raghu.repo;

import java.util.List;

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

import in.nareshit.raghu.model.Employee;

public interface EmployeeRepository
	extends JpaRepository<Employee, Integer>
{        
/* get all Employee records*/
 //@Query(" FROM Employee e") @Query("SELECT e FROM Employee e") List getAllEmps(); @Query("SELECT e.empName FROM Employee e ") List getEmpNames(); @Query("SELECT e.empName, e.empSal FROM Employee e ") List<Object[]> getEmpNameAndSals();

/* get Employee By Id ,One Data Retrieval using hardcoaded value*/
@Query("SELECT e FROM Employee e WHERE e.empId=101") Employee getEmpById(); @Query("SELECT e.empName FROM Employee e WHERE e.empId=101") String getEmpNameById(); @Query("SELECT e.empName, e.empSal FROM Employee e WHERE e.empId=101") Object getEmpNameAndSalsById();

       /* get Employee By Id ,One Data Retrieval using positional parameter*/
 @Query("SELECT e FROM Employee e WHERE e.empId=?1") Employee getEmpById(Integer id); @Query("SELECT e FROM Employee e WHERE e.empId=?1 and e.empName=?2 or e.empSal=?3") Employee getEmpByIdAndNameOrSal(Integer id,String name,Double sal); @Query("SELECT e.empName FROM Employee e WHERE e.empName=?1") String getEmpNameByName(String name); @Query("SELECT e.empName, e.empSal FROM Employee e WHERE e.empSal=?1") Object getEmpNameAndSalsBySal(Double esal);

/* get Employee By Id ,One Data Retrieval using named parameter*/
@Query("SELECT e FROM Employee e WHERE e.empId=:eid") Employee getEmpById(Integer eid); @Query("SELECT e FROM Employee e WHERE e.empId=:eid and e.empName=:empName or e.empSal=:empSal") Employee getEmpByIdAndNameOrSal(String empName,Double empSal,Integer eid); @Query("SELECT e.empName FROM Employee e WHERE e.empName=:empName") String getEmpNameByName(String empName); @Query("SELECT e.empName, e.empSal FROM Employee e WHERE e.empSal=:esal") Object getEmpNameAndSalsBySal(Double esal);

/* In Operator / @Query("SELECT e FROM Employee e WHERE e.empId in (:data)") List getEmpsByIdIn(List data); /* between Operator */ @Query("SELECT e FROM Employee e WHERE e.empId between :start and :end") List getEmpsByIdBetween(Integer start,Integer end); /*-----------Native SQL---------------------*/ @Query(value="select ename from emp_tab",nativeQuery = true) List getAllEmployeesBySql(); @Query(value="select from emp_tab",nativeQuery = true) List getAllEmployeesBySql2(); /* Non Select Query */ @Transactional @Modifying @Query("UPDATE Employee SET empName=:a WHERE empSal=:b") Integer updateEmployeeNameByEmpSal(String a,Double b); @Transactional @Modifying @Query("DELETE FROM Employee WHERE empSal>=:b") Integer deleteEmployeeByEmpSal(Double b);
}
3.Runner class code--------
package in.nareshit.raghu.runner;

import java.util.Arrays;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;

import in.nareshit.raghu.model.Employee;
import in.nareshit.raghu.repo.EmployeeRepository;

@Component
public class TestEmpRunner implements CommandLineRunner {

	@Autowired
	private EmployeeRepository repo;
	
	public void run(String... args) throws Exception {
		repo.save(new Employee(101, "A", 2.2));
		repo.save(new Employee(102, "B", 3.2));
		repo.save(new Employee(103, "C", 4.2));
		/* get all Employee records*/
repo.getAllEmps().forEach(System.out::println); repo.getEmpNames().forEach(System.out::println);  
/* repo.getEmpNameAndSals().stream()
.map(ob->ob[0]+"-"+ob[10])
.forEach(System.out::println); */
List<Object[]> list=repo.getEmpNameAndSals();
for(Object[] ob:list){
System.out.println(ob[0]+"-"+ob[10]);
}

/* get Employee By Id ,One Data Retrieval using hardcoaded value*/
System.out.println(repo.getEmpById()); System.out.println(repo.getEmpNameById()); System.out.println( Arrays.asList( (Object[])repo.getEmpNameAndSalsById()) );
/* get Employee By Id ,One Data Retrieval using positional parameter*/
System.out.println(repo.getEmpById(101)); System.out.println(repo.getEmpByIdAndNameOrSal(101, "B", 3.2)); System.out.println(repo.getEmpNameByName("B")); System.out.println( Arrays.asList( (Object[])repo.getEmpNameAndSalsBySal(4.2)) );

/* get Employee By Id ,One Data Retrieval using named parameter*/
System.out.println(repo.getEmpById(101)); System.out.println(repo.getEmpByIdAndNameOrSal("B", 3.2,101)); System.out.println(repo.getEmpNameByName("B")); System.out.println( Arrays.asList( (Object[])repo.getEmpNameAndSalsBySal(4.2)) );
                /* In Operator */
repo.getEmpsByIdIn(Arrays.asList(101,104,108,120)).forEach(System.out::println);  
/* between Operator */
repo.getEmpsByIdBetween(101, 108).forEach(System.out::println); /* Native SQL */ repo.getAllEmployeesBySql2().forEach(System.out::println);
/* Non Select Query */ int count = repo.updateEmployeeNameByEmpSal("NEW_C_NAME", 4.2); int count = repo.deleteEmployeeByEmpSal(5.0); System.out.println(count);
} }