Handson
1. You are developing an online store application where you need to manage products. You need to create entities for Product and use Spring Data JPA to perform the following operations:
1. Add new products to a category.
2. List all products under a specific category.
3. Update product details.
4. Delete a product by its ID.
Different ways to communicate with database
1. Using predefined method
- T save(T t) - store single object into db table
- void saveAll(Iterable) - store multiple object into db table
- Optional findById(int id) - fetch single object based on id
- Iterable findAll() - return multiple objects
- boolean existsById(int id)
- boolean exists(T t)
- void deleteById(int id)
- void delete(T t)
- void deleteAll()
2. Using Custom JPA method
- derived methods used for fetching the data based on other properties of entity class
- name of the custom JPA method should starts with findBy/readBy/getBy/queryBy
- declare custom JPA method inside repository interface, so spring data jpa will automatically write the logic on behalf of the user
Pattern matching operator - LIKE - 2 wildcard character
% - anything
_ - single value
public interface EmployeeRepository extends JpaRepository<Employee, Integer>{
List<Employee> findByDept(String dname);
List<Employee> getBySalaryBetween(double sal1, double sal2);
List<Employee> getByNameLikeAndSalaryGreaterThan(String name, double sal);
List<Employee> queryByNameLike(String name);
List<Employee> readByNameLikeAndSalaryGreaterThanEqual(String name, double sal);
List<Employee> findByDeptIsNull();
List<Employee> findByGender(String gender);
List<Employee> findByNameContainingOrEmailContaining(String name,String email);
}
private void customJPAMethod() {
List<Employee> l=empRepo.findByDept("HR");
l.forEach(System.out::println);
//List<Employee> l=empRepo.getBySalaryBetween(30000.0,40000.0);
//l.forEach(System.out::println);
//List<Employee> l=empRepo.getByNameLikeAndSalaryGreaterThan("R%", 20000.0);
//l.forEach(System.out::println);
//List<Employee> l=empRepo.findByNameContainingOrEmailContaining("am", "IM");
//l.forEach(System.out::println);
}
3. Limiting the records based on custom JPA method
- using first or top keyword
Employee findFirstByOrderBySalaryDesc()
Employee findTopByOrderBySalaryAsc()
List<Employee> findFirst4ByOrderBySalaryDesc()
List<Employee> findTop3ByOrderBySalaryAsc()
List<Employee> findFirst3ByDeptOrderBySalaryDesc(String dname)
4. Counting the record based on custom JPA method
- using countBy
long countByDept(String name);
long countByNameEndingWith(String name);
long countBySalaryGreaterThanEqual(double sal);
private void customJPAMethod() {
long l=empRepo.countByDept("HR");
System.out.println(l);
long l1=empRepo.countByNameEndingWith("am");
System.out.println(l1);
}
5. If we want to perform joins and subqueries then we cant write custom jpa method, in that case we have to write queries using @Query
1. JPAQL - query the entity class
2. SQL - query the tables directly
@Query("select e from Employee e") //JPAQL
List<Employee> fetchAllEmployee();
@Query(value="select * from empl100",nativeQuery=true) //SQL
List<Employee> fetchAllEmployee1();
6. Passing parameters to the query - 2 ways
1. Positional parameter - using ?1,?2,?3 etc
@Query("select e from Employee e where e.name like ?1 and e.salary=?2")
List<Employee> findEmpByNameAndSalary(String name,double sal);
2. Named Parameter - using : followed any name - :a, :abc, :xyz
@Param - used to assign value to named parameter
@Query("select e from Employee e where e.name like :ename and e.salary=:esal")
List<Employee> findEmpByNameAndSalary1(@Param("ename")String name,@Param("esal")double sal);
7. If we want to perform some DML operation(insert,update,delete) using @Query, apart from @Query we have to provide 2 more annotations called @Modifying and @Transactional
@Modifying
@Transactional
@Query("update Employee e set e.salary=e.salary+e.salary*:percent/100 where e.dept=:dept")
int updateSalary(@Param("dept")String dname,@Param("percent")double percentage);
8. Instead of writing queries inside repository interface, we can write queries in entity class and we refer them by their name
JPAQL - @NamedQuery
SQL - @NamedNativeQuery
List<Employee> findEmployeeBySalary(double sal);
List<Employee> findEmployeeBySalary1(@Param("esal")double sal);
@Entity
@Table(name="empl100")
@Data
@AllArgsConstructor
@NoArgsConstructor
@NamedQuery(name="Employee.findEmployeeBySalary",
query="select e from Employee e where e.salary=?1") //JPAQL
@NamedNativeQuery(name="Employee.findEmployeeBySalary1",
query="select * from empl100 where e.salary=:esal") //SQL
public class Employee {
@Id
private Integer id;
private String name;
private String gender;
private String email;
private String dept;
private Double salary;
}
9. For Sorting purpose, we use Sort class
//List<Employee> findByDeptOrderBySalaryDesc(String dname);
List<Employee> findByDept(String dname, Sort s);
List<Employee> l=empRepo.findByDept("HR",Sort.by("salary").descending());
l.forEach(System.out::println);
List<Employee> l=empRepo.findByDept("HR",Sort.by("name").ascending());
l.forEach(System.out::println);
10. Consider we have entity class with 100 properties, but we need to display only some 10 properties, but if we provide List<Employee> then it will display all 100 properties
Consider we have empl table, after performing some operation we need to display only specific column (ie) total count of male and female employees along with its gender, so the output would be
mysql> select count(gender) as Totalcount, gender from empl100 group by gender;
+------------+--------+
| Totalcount | gender |
+------------+--------+
| 5 | male |
| 2 | female |
+------------+--------+
2 rows in set (0.01 sec)
@Query(value="select count(gender) as Totalcount, gender from empl100 group by gender",nativeQuery=true)
List<Employee> countGenderWise(); -- wrong - it will display all propertis
@Query(value="select count(gender) as Totalcount, gender from empl100 group by gender",nativeQuery=true)
List<Object[]> countGenderWise(); --correct - but it is not good practise to return Object[]
@Query(value="select count(gender) as Totalcount, gender from empl100 group by gender",nativeQuery=true)
Map<Integer,String> countGenderWise(); --wrong - because Spring data jpa dosent support Map as return type
Constructor method - used when we want to display only specific properties from entity class
- create separate class based on what properties you want to display
@Data
@NoArgsConstructor
@AllArgsConstructor
public class GenderCount {
private long count;
private String gender;
}
@Query("select new com.pack.SpringDataJPA.GenderCount(count(e.gender),e.gender) from Employee e group by e.gender")
List<GenderCount> countGenderWise();
List<GenderCount> l=empRepo.countGenderWise();
l.forEach(System.out::println);
SpringBoot with Spring Data JPA
1. Create Springboot project with web, data jpa, h2 database, lombok, spring devtool dependency
2. Configure db info in application.properties
server.port=5000
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto= update
spring.h2.console.enabled=true
# default path: h2-console
spring.h2.console.path=/h2-ui
3. Create entity class
@Entity
@Table(name="movie100")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Movie {
@Id
private Integer id;
private String name;
private String language;
private String type;
private Integer rating;
}
4. Create repository interface
public interface MovieRepository extends JpaRepository<Movie, Integer>{
}
5. Create controller prg
@RestController
@RequestMapping("/api")
public class MovieController {
@Autowired
MovieService movieService;
@GetMapping("/")
public String getMovieInfo() {
return "Welcome to Movie Application";
}
@PostMapping("/movie")
public ResponseEntity<Movie> createMovie(@RequestBody Movie movie) {
Movie savedMovie=movieService.createMovie(movie);
return new ResponseEntity<Movie>(savedMovie,HttpStatus.CREATED);
}
@GetMapping("/movie")
public ResponseEntity<List<Movie>> getAllMovies() {
List<Movie> movieList = movieService.getAllMovies();
if(movieList.isEmpty())
return new ResponseEntity<>(HttpStatus.NO_CONTENT);
return new ResponseEntity<>(movieList,HttpStatus.CREATED);
}
@GetMapping("/movie/{id}")
public ResponseEntity<Movie> getMovieById(@PathVariable("id") Integer mid) {
Movie movie=movieService.getMovieById(mid);
return new ResponseEntity<>(movie,HttpStatus.CREATED);
}
}
6. Create service prg
@Service
public class MovieService {
@Autowired
MovieRepository movieRepo;
public Movie createMovie(Movie movie) {
return movieRepo.save(movie);
}
public List<Movie> getAllMovies() {
return movieRepo.findAll();
}
public Movie getMovieById(Integer mid) {
return movieRepo.findById(mid).get();
}
}
7. To give request with input and check whether ur appl endpoint is working fine or not, for that we have different tools like Postman, SOAP UI etc. Instead springboot itself provides with Swagger tool to test ur appl
Swagger is a documentation tool where it will document all the endpoints which u have created and provide UI to test ur appl
<dependency>
<groupId>org.springdoc</groupId>
<artifactId>springdoc-openapi-starter-webmvc-ui</artifactId>
<version>2.2.0</version>
</dependency>
8. Start the appl
9. To test the appl in Swagger we have to provide, http://localhost:5000/swagger-ui/index.html
10. To open h2 console, http://localhost:5000/h2-ui
No comments:
Post a Comment