jpa 多对一关系的JPQL @Query

ctrmrzij 于 2022-12-23 发布在其他

关注(0)|答案(1)|浏览(191)

拜托，我需要帮助。我有两个实体：

预约类

@Entity
@Table(name = "appointment")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Appointment {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    
    @Column(name = "created_date")
    private Date createdDate;
    
    @Column(name = "modified_date")
    private Date modifiedDate;
    
    @Column(name = "appointment_date")
    private LocalDate appointmentDate;
    
    @Column(name = "start_time")
    private LocalTime startTime;
    
    private Boolean cancelled;
    
    @ManyToOne
    @JoinColumn(nullable = false, name = "client_id")
    private Client clientId;
    
    @ManyToOne
    @JoinColumn(nullable = false, name = "employee_id")
    private Employee employee; 
    
    @ManyToOne
    @JoinColumn(nullable = false, name = "service_id")
    private Service service;
}

和雇员类

@Entity
@Table(name = "employee")
@NoArgsConstructor
@AllArgsConstructor
public class Employee {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    
    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;
    
    @Column(name = "created_date")
    private Date createdDate;
    
    @Column(name = " modified_date")
    private Date modifiedDate;
    
    @OneToOne
    @JoinColumn(name = "service_id", referencedColumnName = "id")
    private Service service; 
}

我需要获取所有与给定的startTime，appointmentDate和employee匹配的约会。我想在接口AppointmentRepo中定义一个抽象方法，以便在我的AppointmentServices.class中可以使用3个参数调用该方法并获取约会实体。
AppointmentServices.class

appointmentRepo.getAppointmentByDateAndEmployee(date, employee, scheduledHour);

预约回购界面

@Repository
public interface AppointmentRepo extends JpaRepository<Appointment, Integer>{
    
    @Query("SELECT a FROM Appointment a INNER JOIN a.employee e WHERE a.appointmentDate = :appointment_date AND e = :employee AND s.startTime = :start_time")
    public List<Appointment> getAppointmentByDateAndEmployee (@Param("appointment_date") LocalDate appointmentDate, 
            @Param("employee_id") Employee employee, @Param("start_time") LocalTime startTime); 

}

我必须如何设置我的@Query才能得到一个与3个给定参数（日期、时间和对其他名为Employee的实体的引用）匹配的约会实体。我匹配整个对象是否做错了，所以我只需要使用Employee实体的ID？
请帮助我，并感谢您的时间！！节日快乐

jpa

来源：https://stackoverflow.com/questions/74893352/jpql-query-for-a-manytoone-relationship

1条答案

按热度按时间

xdnvmnnf1#

您可以使用SQL代替HQL（nativeQuery=true）

DAO层

package com.jb.app.repos;

import com.jb.app.beans.Appointment;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.time.LocalDate;
import java.time.LocalTime;
import java.util.List;

@Repository
public interface AppointmentRepository extends JpaRepository<Appointment, Integer> {

    @Query(value = "SELECT * FROM APPOINTMENT WHERE appointment_date = ?1 AND start_time = ?2 AND employee_id = ?3", nativeQuery = true)
    List<Appointment> getAppointmentByDateAndEmployee(LocalDate appointmentDate, LocalTime startTime, int employeeId);

}

服务层

package com.jb.app.services;

import com.jb.app.beans.Appointment;
import com.jb.app.beans.Employee;
import com.jb.app.repos.AppointmentRepository;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;

import java.time.LocalDate;
import java.time.LocalTime;
import java.util.List;

@Service
@RequiredArgsConstructor
public class AppointmentServiceImpl implements AppointmentService{
    
    private final AppointmentRepository appointmentRepository;
    
    @Override
    public List<Appointment> getAppointmentByDateAndEmployee(LocalDate appointmentDate, LocalTime startTime, Employee e) {
        return appointmentRepository.getAppointmentByDateAndEmployee(appointmentDate,startTime,e.getId());
    }
}

赞(0）回复(0）举报 2022-12-23

我来回答

jpa 多对一关系的JPQL @Query

1条答案

相关问题

热门标签

最新问答