SBMS 6am 24012023
SBMS 6am 24012023
SBMS 6am 24012023
===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;
import org.springframework.data.jpa.repository.JpaRepository;
import com.app.raghu.entity.Department;
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);
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);
}