mysql - 执行 JPQL 查询时遇到问题

Question

我正在使用带有查询的 JPQL 使用 Spring Boot 实体管理器。以下查询工作正常：

SELECT h, uh FROM Hour h, UserHour uh

但现在我想获得一个使用查询的结果：

SELECT h FROM Hour h INNER JOIN UserHour uh WHERE uh.userId = 1

但是结果为零，这是意料之外的。

但是，当我使用 SQL 查询时：

SELECT * FROM hour
   INNER JOIN user_hour
     ON user_hour.hour_id = hour.hour_id
 WHERE user_hour.user_id = '1'

我得到了我想要的结果。

请帮帮我。

编辑：

List<Object> result = entityManager.createQuery(
                    "select distinct hr " +
                            "from Hour hr " +
                            "join hr.users hu " +
                            "where hu.id = :userID")
                    .setParameter( "userID", 1)
                    .getResultList();
            System.out.println(result);

用户实体：

package com.timely.backend.models;

import javax.persistence.*;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

@Entity
@Table(name = "USER")
public class User implements Serializable {

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

    private String username;
    private String password;
    private String fullname;
    private String email;
    private String creation_date;

    // user roles
    @ElementCollection(targetClass = Role.class, fetch = FetchType.EAGER)
    @CollectionTable(name = "user_role", joinColumns = @JoinColumn(name = "user_id"))
    @Enumerated(EnumType.STRING)
    private Set<Role> roles;

    @ManyToMany(cascade = {
            CascadeType.PERSIST,
            CascadeType.MERGE
    })
    @JoinTable(name = "user_hour",
            joinColumns = @JoinColumn(name = "userId"),
            inverseJoinColumns = @JoinColumn(name = "hourId")
    )

    private List<Hour> hours = new ArrayList<>();

    public User() {

    }

    public User(String username, String password, String fullname, String email, String creation_date) {
        this.username = username;
        this.password = password;
        this.fullname = fullname;
        this.email = email;
        this.roles = getRoles();
        this.creation_date = creation_date;
    }

    //If user is admin
    public boolean isAdmin() {
        return roles.contains(Role.ADMIN);
    }

    //region Getters & Setters
    public void setId(int id) {
        this.id = id;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public void setFullname(String fullName) {
        this.fullname = fullName;
    }
    public void setEmail(String email) { this.email = email; }
    public void setRoles(Set<Role> roles) { this.roles = roles; }
    public void setCreation_date(String creation_date) { this.creation_date = creation_date; }

    public int getId() { return id; }
    public String getUsername() {
        return username;
    }
    public String getPassword() {
        return password;
    }
    public String getFullname() {
        return fullname;
    }
    public String getEmail() { return email; }
    public Set<Role> getRoles() { return roles; }
    public String getCreation_date() { return creation_date; }
    //endregion
}

UserHour 实体：

package com.timely.backend.models;

import javax.persistence.*;

@Entity
@Table(name = "user_hour")
public class UserHour {
    public int getId() {
        return id;
    }

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

    public int getUserId() {
        return userId;
    }

    public void setUserId(int userId) {
        this.userId = userId;
    }

    public int getHourId() {
        return hourId;
    }

    public void setHourId(int hourId) {
        this.hourId = hourId;
    }

    public UserHour(int userId, int hourId) {
        this.userId = userId;
        this.hourId = hourId;
    }

    public UserHour(){

    }

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

    private int userId;
    private int hourId;
}

小时实体：

package com.timely.backend.models;

import javax.persistence.*;
import java.util.ArrayList;
import java.util.List;

@Entity
@Table(name = "HOUR")
public class Hour {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int hourId;

    private String date;
    private String startTime;
    private String endTime;


    public Hour(int hourId, String date, String startTime, String endTime) {
        this.hourId = hourId;
        this.date = date;
        this.startTime = startTime;
        this.endTime = endTime;
    }

    public Hour(){

    }

    @ManyToMany(mappedBy = "hours")
    private List<User> users = new ArrayList<>();

    public int gethourId() {
        return hourId;
    }

    public void sethourId(int hourId) {
        this.hourId = hourId;
    }

    public String getDate() {
        return date;
    }

    public void setDate(String date) {
        this.date = date;
    }

    public String getStartTime() {
        return startTime;
    }

    public void setStartTime(String startTime) {
        this.startTime = startTime;
    }

    public String getEndTime() {
        return endTime;
    }

    public void setEndTime(String endTime) {
        this.endTime = endTime;
    }
}

MySQL查询工作：

SELECT id, date, start_time, end_time FROM hour
JOIN user_hour
     ON user_hour.hour_id = hour.hour_id
 WHERE user_hour.user_id = '1'

问题：

用户表：

用户小时表：

小时表：

标签： mysqlspringjpql