SBMS 6am 24012023

Date : 24-01-2023
Spring Boot and Microservices

6AM | Mr. Raghu | (ASHOK IT)
---------------------------------------------------------------------
SQL Joins: (SELECT)
To fetch data from two/multiple different tables (connected using PK-FK)
using one SELECT SQL Query.
-> Inner Join | Join : Query will fetch connected rows.

-> Outer Join (3)
|- Left Outer Join | Left Join
=> All Rows from Left Table and connected rows from right table.
|- Right Outer Join | Right Join
=> All Rows from Right table and Connected rows from left table.
|- Full Outer Join | Full Join
=> All rows from both tables (connected and non-connected)
*) Connected : Row in Parent table is connected with Row in child table.

*) Non-Connected : Row in Parent table is not connected with any row
in child table.
SQL Joins Syntax:

SELECT
FROM ParentTable p
[Join Type]
ChildTable C
ON P.PK = C.FK | P.FK = C.PK
WHERE <condition>
===JPQL/HQL Joins==========================
*) Parent class and HAS-A Variable
Here we use HAS-A variable/Reference variable of child class present
in parent class.
Syntax:
SELECT P.<variable>, C.<variable>
FROM ParentClass P
JOIN TYPE
P.HasAVariable as C
WHERE <condition>
======code======================
1. YAML
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/boot6am
username: root
password: root
jpa:
database-platform: org.hibernate.dialect.MySQL8Dialect
show-sql: true
hibernate:
ddl-auto: create
2. Entity classes
package com.app.raghu.entity;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name="emptab")
public class Employee {
@Id
@Column(name="eid")
private Integer empId;
@Column(name="ename")
private String empName;
@Column(name="esal")
private Double empSal;
@ManyToOne
@JoinColumn(name="didFk")
private Department dob;
}
--------
package com.app.raghu.entity;
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="depttab")
public class Department {
@Id
@Column(name="did")
private Integer deptId;
@Column(name="dcode")
private String deptCode;
@Column(name="dadmin")
private String deptAdmin;
}
3. Repository interfaces
package com.app.raghu.repo;
import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import com.app.raghu.entity.Employee;
public interface EmployeeRepository extends JpaRepository<Employee, Integer> {
@Query("SELECT E.empName, D.deptCode FROM Employee E INNER JOIN E.dob as D ")

List<Object[]> fetchEmpNameAndDeptCode();
@Query("SELECT E.empName, D.deptCode FROM Employee E LEFT JOIN E.dob as D ")

List<Object[]> fetchAllEmpNameAndDeptCode();
@Query("SELECT E.empName, D.deptCode FROM Employee E RIGHT JOIN E.dob as D ")

//@Query("SELECT E.empName, D.deptCode FROM Employee E FULL JOIN E.dob as D
")
List<Object[]> fetchEmpNameAndAllDeptCode();
}
---------
package com.app.raghu.repo;
import org.springframework.data.jpa.repository.JpaRepository;
import com.app.raghu.entity.Department;
public interface DepartmentRepository

extends JpaRepository<Department, Integer> {
5. Runner class
package com.app.raghu.runner;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;
import com.app.raghu.entity.Department;
import com.app.raghu.entity.Employee;
import com.app.raghu.repo.DepartmentRepository;
import com.app.raghu.repo.EmployeeRepository;
@Component
public class TestDataRunner implements CommandLineRunner {
@Autowired
private EmployeeRepository erepo;
@Autowired
private DepartmentRepository drepo;
public void run(String... args) throws Exception {
Department d1 = new Department(101, "DEV", "XYZ");
Department d2 = new Department(102, "QA", "MMM");
Department d3 = new Department(103, "BA", "PPP");
Department d4 = new Department(104, "ART", "XYZ");
Department d5 = new Department(105, "ITHD", "OUY");
drepo.save(d1);
drepo.save(d2);
drepo.save(d3);
drepo.save(d4);
drepo.save(d5);
Employee e1 = new Employee(10, "SAM", 200.0, null);

Employee e2 = new Employee(11, "SYED", 300.0, d2);
Employee e3 = new Employee(12, "AJAY", 400.0, d3);
Employee e4 = new Employee(13, "AHMED", 500.0, null);
Employee e5 = new Employee(14, "KHAN", 600.0, null);
erepo.save(e1);
erepo.save(e2);
erepo.save(e3);
erepo.save(e4);
erepo.save(e5);
//erepo.fetchEmpNameAndDeptCode()
//erepo.fetchAllEmpNameAndDeptCode()
erepo.fetchEmpNameAndAllDeptCode()
.stream()
.map(e->e[0]+"-"+e[1])
.forEach(System.out::println);
}

SBMS 6am 24012023

Uploaded by

Copyright:

Available Formats

SBMS 6am 24012023

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

SBMS 6am 24012023

Uploaded by

Copyright:

Available Formats

Date : 24-01-2023

Spring Boot and Microservices

-> Inner Join | Join : Query will fetch connected rows.

*) Connected : Row in Parent table is connected with Row in child table.

SQL Joins Syntax:

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {

@Query("SELECT E.empName, D.deptCode FROM Employee E INNER JOIN E.dob as D ")

@Query("SELECT E.empName, D.deptCode FROM Employee E LEFT JOIN E.dob as D ")

@Query("SELECT E.empName, D.deptCode FROM Employee E RIGHT JOIN E.dob as D ")

public interface DepartmentRepository

Employee e1 = new Employee(10, "SAM", 200.0, null);

You might also like