<aside> 💡

20.1.2

    @Query("SELECT d FROM Doctor d WHERE d.yearsOfExperience > :experience AND d.department.id = :department")
    List<Doctor> findExperiencedDoctors(@Param("experience") int experience, @Param("departmentId") Long departmentId );

</aside>

Write JPQL Query properly.

@Query("SELECT s FROM Student s WHERE s.collegeName = :collegeName ORDER BY s.name DESC")
List<Student> findByCollegeNameOrderByNameDesc(@Param("collegeName") String collegeName);

@Query("SELECT s FROM Student s WHERE s.collegeName = :collegeName AND s.yearOfAdmission = :year ORDER BY s.name DESC")
List<Student> findByCollegeNameAndYearOrderByNameDesc(@Param("collegeName") String collegeName, @Param("year") int year);

Join Query: Combines data from multiple entities based on a relationship.

SELECT p FROM Project p JOIN p.employees e WHERE e.id = :employeeId

   @Query("SELECT c FROM Course c WHERE c.courseId = :courseId")
    public Course getCourseThroughJpqlQuery(Long courseId);

ProductController.java

package com.wecp.product_inventory_system.controller;

import com.wecp.product_inventory_system.entity.Product;
import com.wecp.product_inventory_system.exception.NoSuchProductException;
import com.wecp.product_inventory_system.service.ProductService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/products")
public class ProductController {

    @Autowired
    private ProductService productService;

    @GetMapping
    public ResponseEntity<List<Product>> getAllProducts(){
        List<Product> result = productService.getAllProducts();
        
        return new ResponseEntity<>(result, HttpStatus.OK);     
    }
    
    @GetMapping("/{productName}")
    public ResponseEntity<Product> getProductByName(@PathVariable String productName){
        Product result = productService.getProductByName(productName);

        if(result != null){
            return new ResponseEntity<>(result, HttpStatus.OK);
        }
        
        return new ResponseEntity<>(null, HttpStatus.NOT_FOUND);        
}

    @PostMapping
    public ResponseEntity<Product> addProduct(@RequestBody Product product){
        Product result = productService.addProduct(product);
        return new ResponseEntity<>(result, HttpStatus.CREATED);
    }

    // ------------ ERROR ---------------
    @PutMapping("/{productName}")
    public ResponseEntity<?> updateProductByName(@PathVariable String productName, @RequestBody Product newProduct ){
        
        try {
            Product result = productService.updateProductByName(productName, newProduct);        
            return new ResponseEntity<>(result, HttpStatus.OK);            
        } catch (NoSuchProductException  e) {
            return new ResponseEntity<>(e.getMessage(), HttpStatus.NOT_FOUND);
        }
    }
    
    @DeleteMapping("/{productName}")
    public ResponseEntity<?> deleteProduct(@PathVariable String productName){
        Product p = productService.getProductByName(productName);

        if(p != null){
            productService.deleteProductByName(productName);
            return new ResponseEntity<>("Product Deleted", HttpStatus.NO_CONTENT);            
        }

        return new ResponseEntity<>("Product Deleted", HttpStatus.NOT_FOUND);
        
    }

}

ProductService.java

package com.wecp.product_inventory_system.service;

import com.wecp.product_inventory_system.entity.Product;
import com.wecp.product_inventory_system.exception.NoSuchProductException;
import com.wecp.product_inventory_system.repository.ProductRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.servlet.mvc.condition.ProducesRequestCondition;

import java.util.List;

@Service
public class ProductService {

    @Autowired
    private ProductRepository productRepository;

    public List<Product> getAllProducts(){
        return productRepository.findAll();
    }

    public Product getProductByName(String productName){
        return productRepository.findByProductName(productName);
    }

    public Product addProduct(Product product){
        return productRepository.save(product);
    }

    // public Product updateProductByName(String productName, Product productDetails){

    //     Product existingProduct = productRepository.findByProductName(productName);

    //     if( existingProduct == null){
    //         throw new NoSuchProductException("No Such Product Found " + productName);
    //     }

    //         // existingProduct.setProductName(productDetails.getProductName());
    //         // existingProduct.setCategory(productDetails.getCategory());
    //         // existingProduct.setPrice(productDetails.getPrice());
    //         // existingProduct.setQuantityInStock(productDetails.getQuantityInStock());
    //         // existingProduct.setId(productDetails.getId());

    //         productDetails.setId(existingProduct.getId());

    //         return productRepository.save(productDetails);
            
    // }

    public Product updateProductByName (String productName, Product product) {
        // Product pro = productRepository.findProductByProductName(productName);
        Product pro = productRepository.findByProductName(productName);
        if(pro == null)
            throw new NoSuchProductException("Product not found with such name :" +productName);
        product.setId(pro.getId());
        return productRepository.save(product);
    }

    public void deleteProductByName(String productName){
        Product p = productRepository.findByProductName(productName);
        productRepository.deleteById(p.getId());
    }

}

ProductRepository.java

package com.wecp.product_inventory_system.repository;

import com.wecp.product_inventory_system.entity.Product;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.util.Optional;

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    Product findByProductName(String productName);

}

Product.java

package com.wecp.product_inventory_system.entity;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String productName;
    private String category;
    private double price;
    private Integer quantityInStock;

    public Product(){}

    public Product(Long id, String productName, String category, double price, Integer quantityInStock) {
        this.id = id;
        this.productName = productName;
        this.category = category;
        this.price = price;
        this.quantityInStock = quantityInStock;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getProductName() {
        return productName;
    }

    public void setProductName(String productName) {
        this.productName = productName;
    }

    public String getCategory() {
        return category;
    }

    public void setCategory(String category) {
        this.category = category;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    public Integer getQuantityInStock() {
        return quantityInStock;
    }

    public void setQuantityInStock(Integer quantityInStock) {
        this.quantityInStock = quantityInStock;
    }

}

NoSuchProductException.java

package com.wecp.product_inventory_system.exception;

import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.ResponseStatus;

public class NoSuchProductException extends RuntimeException {
    public NoSuchProductException(String message){
        super(message);
    }
}

20.2.1