spring数据JPA中的查询语法问题

Question

我正在尝试使用spring数据JPA（我的模型类用户）实现一对多关联。java是，

@Entity
@Table(name = "users")
public class Users implements Serializable{

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer id;
public String username;
public String password;
public Integer privid;

@OneToMany(cascade = CascadeType.ALL, mappedBy = "pid")
private Collection<Privillages> priviJoin;

public Integer getId() {
    return id;
}
public void setId(Integer id) {
    this.id = id;
}
@Column(name = "username")
public String getUsername() {
    return username;
}
public void setUsername(String username) {
    this.username = username;
}
@Column(name = "password")
public String getPassword() {
    return password;
}
public void setPassword(String password) {
    this.password = password;
}

@Column(name = "privid")
public Integer getPrivid() {
    return privid;
}
public void setPrivid(Integer privid) {
    this.privid = privid;
}

public Collection<Privillages> getPriviJoin() {
    return priviJoin;
}
public void setPriviJoin(Privillages priviJoin) {
    this.priviJoin = (Collection<Privillages>) priviJoin;
}

public Users() {
}
@Override
public String toString() {
    return String.format("Users[id=%d, username='%s', password='%s']", id, 
username, password);
}
}

和私人住宅。java是，

@Entity
@Table(name = "privillages")
public class Privillages implements Serializable {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
public Integer id;

public String pname;


@ManyToOne(optional = false)
@JoinColumn(name = "pid", referencedColumnName = "privid")
public Users pid;

public Integer getId() {
    return id;
}
public void setId(Integer id) {
    this.id = id;
}

@Column(name = "pname")
public String getPname() {
    return pname;
}
public void setPname(String pname) {
    this.pname = pname;
}

@Column(name = "pid")
public Users getPid() {
    return pid;
}
public void setPid(Users pid) {
    this.pid = pid;
}

public Privillages(){
} 
}

我的观点是，

<div th:each= "user: ${joinData}">

        <span th:text="${user.pname}">
        </span>
        <span th:text="${user.username}">
        </span>
    </div>

我需要从用户那里获取用户名，从PriVillage获取pname。通过组合我需要显示。现在我在我的存储库中使用下面的查询，即，

@Query("select u from Users ug inner join ug.priviJoin u")
List<Users> findByUsername();

如上所述，我需要从一个表中选择用户名，从另一个表中选择pname。我需要如何更改我的查询？

Answer 1

首先，您必须选择是将注释放在字段上还是放在setter上。规格要求只选择一种方式。

您可以省略@Colzo注释，有默认值，所以现在让我们将其保留如下：

@Entity
@Table(name = "users")
public class Users implements Serializable{

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;
    public String username;
    public String password;
    public Integer privid;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "pid")
    private Collection<Privillages> privilages;

    //getters setters
}

和

@Entity
@Table(name = "privillages")
public class Privillages implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    public Integer id;

    public String pname;


    @ManyToOne(optional = false)
    public Users pid;

}

现在您可以创建以下存储库：

public interface UserRepository extends JpaRepository<User, Integer> {

    List<User> findAllByUsername(String username);
}

方法findAllByUsername将返回具有特定用户名的用户列表。

如果您想坚持使用@Query请记住，@Table注释不会更改@Entity名称，因此在您的查询中，引用User，而不是User。

Answer 2

您遇到此问题有两个原因。

1. JPQL查询无效
2. 您尚未在实体之间创建基础JPQL查询可以利用的关联

在JPQL中执行连接时，必须确保尝试连接的实体之间存在基础关联。在您的示例中，缺少用户和Privillages实体之间的关联。为了创建这个关联，我们必须在Users类中添加Privillages字段，并建立适当的JPA映射。我已附上以下用户的来源。

用户。Java语言

 @Entity
        @Table(name = "users")
        public class Users implements Serializable{

        @Id
        @GeneratedValue(strategy = GenerationType.AUTO)
        private Integer id;
        public String username;
        public String password;
        public Integer privid;

        @OneToMany(cascade = CascadeType.ALL, mappedBy = "pid")
        private Collection<Privillages> priviJoin;

        public Integer getId() {
            return id;
        }
        public void setId(Integer id) {
            this.id = id;
        }

        @OneToOne()
        @JoinColumn(name="pname")
        private Privillages privillages;

        @Column(name = "username")
        public String getUsername() {
            return username;
        }
        public void setUsername(String username) {
            this.username = username;
        }
        @Column(name = "password")
        public String getPassword() {
            return password;
        }
        public void setPassword(String password) {
            this.password = password;
        }

        @Column(name = "privid")
        public Integer getPrivid() {
            return privid;
        }
        public void setPrivid(Integer privid) {
            this.privid = privid;
        }

        public Collection<Privillages> getPriviJoin() {
            return priviJoin;
        }
        public void setPriviJoin(Privillages priviJoin) {
            this.priviJoin = (Collection<Privillages>) priviJoin;
        }

        public Users() {
        }
        @Override
        public String toString() {
            return String.format("Users[id=%d, username='%s', password='%s']", id, 
        username, password);
        }
        /**
         * @return the pvs
         */
        public Privillages getPvs() {
            return pvs;
        }
        /**
         * @param pvs the pvs to set
         */
        public void setPvs(Privillages pvs) {
            this.pvs = pvs;
        }
    }

私人住宅。Java语言

@Entity
@Table(name = "privillages")
public class Privillages implements Serializable {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
public Integer id;
@Column(name="pname")
public String pname;


@ManyToOne(optional = false)
@JoinColumn(name = "pid", referencedColumnName = "privid")
public Users pid;

public Integer getId() {
    return id;
}


@OneToOne(fetch=FetchType.LAZY, mappedBy="privillages")
private Users user;

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

@Column(name = "pname")
public String getPname() {
    return pname;
}
public void setPname(String pname) {
    this.pname = pname;
}

@Column(name = "pid")
public Users getPid() {
    return pid;
}
public void setPid(Users pid) {
    this.pid = pid;
}

public Privillages(){
} 
}

查询：

@Query("select u.username from users u inner join u.privillages pr where pr.pname = :pname")