代码之家 › 专栏 › 技术社区 › bsr

如何编写JPA查询

jpql orm jpa java

bsr · 技术社区 · 15 年前

学习如何编写JPA查询。请告诉我是否可以更有效地编写下面的查询,可能是在一个select语句中。可能是一个连接,但不确定如何连接。

class Relationship {

  @ManyToOne
  public String relationshipType;  //can be MANAGER, CUSTOMER etc

  @ManyToOne
  public Party partyFrom; // a person who has a relation

  @ManyToOne
  public Party partyTo; // a group a person relate to
}

查询:

        String sql = "";
        sql = "select rel.partyTo";
        sql += " from Relationship rel";
        sql += " where rel.partyFrom = :partyFrom";
        sql += " and rel.relationshipType= :typeName";
        Query query = Organization.em().createQuery(sql);
        query.setParameter("partyFrom", mgr1);
        query.setParameter("typeName", "MANAGER");
        List<Party> orgList = query.getResultList();

        String sql2 = "";
        sql2 = "select rel.partyFrom";
        sql2 += " from Relationship rel";
        sql2 += " where rel.partyTo = :partyToList";
        sql2 += " and rel.relationshipType = :typeName2";
        Query query2 = Organization.em().createQuery(sql2);
        query2.setParameter("partyToList", orgList);
        query2.setParameter("typeName2", "CUSTOMER");
        List<Party> personList2 = query2.getResultList();

这两个查询都有效。查询1返回组列表,其中该人员(MGR1)具有一个关系管理器。查询2将他们是客户的所有人员返回到查询1返回的组中。实际上,我得到了他们属于同一组(客户)的人员列表,该人员(MGR1)与之有关系经理。

是否可以将它们合并为单个SQL语句,以便只访问一个DB?

2 回复 | 直到 7 年前

mdma 15 年前

实际上,您将一个查询嵌套在另一个查询中,并使用“where in”子句指定外部查询应该从内部查询中获取客户。

select rel2.partyFrom
from Relationship rel2
where rel2.relationshipType = :typeName2 /* customer */
and rel2.partyTo.id in 
      (select rel.partyTo.id
      from Relationship rel
      where rel.partyFrom = :partyFrom
      and rel.relationshipType = :typeName)

您的呼叫码通行证 typeName , typeName2 和 partyFrom 参数如前所述。 PartyTo 不需要参数,因为数据来自嵌套select(内部查询)。

您可以使用自联接来实现相同的事情,其中一个WHERE子句在左侧过滤管理器,而客户在右侧过滤,但是使用“in”子句在语义上更清晰。

编辑:我把.id添加到子select中,我认为这是必需的。

Java_Fire_Within 7 年前

这不是问题的答案,而是帮助其他人,以防有人使用JPQL在Spring数据JPA中查看@OneTomany关系,因为这个问题与JPA有关,所以我想分享我的2美分,请提前道歉。

@Entity
@Table(name = "MY_CAR")
public class MyCar {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@Column(name = "DESCRIPTION")
private String description;

@Column(name = "MY_CAR_NUMBER")
private String myCarNumber;

@Column(name = "RELEASE_DATE")
private Date releaseDate;

@OneToMany(cascade = { CascadeType.ALL })
@JoinTable(name = "MY_CAR_VEHICLE_SERIES", joinColumns = @JoinColumn(name = "MY_CAR_ID "), inverseJoinColumns = @JoinColumn(name = "VEHICLE_SERIES_ID"))
private Set<VehicleSeries> vehicleSeries;
public MyCar() {
    super();
    vehicleSeries = new HashSet<VehicleSeries>();
}
// set and get method goes here


@Entity
@Table(name = "VEHICLE_SERIES ")
public class VehicleSeries {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@Column(name = "SERIES_NUMBER")
private String seriesNumber;

@OneToMany(cascade = { CascadeType.ALL })
@JoinTable(name = "VEHICLE_SERIES_BODY_TYPE", joinColumns = @JoinColumn(name = "VEHICLE_SERIES_ID"), inverseJoinColumns = @JoinColumn(name = "BODY_TYPE_ID"))
private Set<BodyType> bodyTypes;
public VehicleSeries() {
    super();
    bodyTypes = new HashSet<BodyType>();
}
// set and get method goes here


@Entity
@Table(name = "BODY_TYPE ")
public class BodyType implements Serializable {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@Column(name = "NAME")
private String name;
// set and get method goes here


public interface MyCarRepository extends JpaRepository<MyCar, Long> {
public Set<MyCar> findAllByOrderByIdAsc();

@Query(value = "select distinct myCar from MyCar myCar "
        + "join myCar.vehicleSeries as vs join vs.bodyTypes as bt where vs.seriesNumber like %:searchMyCar% "
        + "or lower(bt.name) like lower(:searchMyCar) or myCar.bulletinId like %:searchMyCar% "
        + "or lower(myCar.description) like lower(:searchMyCar) "
        + "or myCar.bulletinNumber like %:searchMyCar% order by myCar.id asc")
public Set<MyCar> searchByMyCar(@Param("searchMyCar") String searchMyCar);

}

表中的一些数据

从车辆系列中选择*。

ID      SERIES_NUMBER  
1       Yaris
2       Corolla

从车身类型中选择*。

ID      NAME  
1       Compact
2       Convertible 
3       Sedan