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);
} }
No Comments Yet!!