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!!