如何为外连接表字段编写@OrderBy批注以使用SQL对集合进行排序

问题描述

有三张桌子。 relatedCameraSet的变量需要使用SQL命令camera.name，但camera.name的字段不在RelatedCamera的表格中，位于Camera的外部连接表中。 @OrderBy的以下注释不起作用。

  @Entity 
 @Table（name =MICRO_MAP）
 public class MicroMap {// main表
 @Id 
 @GeneratedValue（strategy = GenerationType.AUTO）
 @Column（name =ID）
 private长ID; 
 
 @Column（name =NAME，length = 32，nullable = false）
私有字符串名称; 
 
 @OneToMany（mappedBy =mapId，cascade = CascadeType.ALL，fetch = FetchType.EAGER）
 @OrderBy（camera.name）// OrderByname in Camera table 
 private Set< RelatedCamera> relatedCameraSet; 
 
 public Long getId（）{
 return id; 
} 
 
 public void setId（Long id）{
 this.id = id; 
} 
 
 public String getName（）{
 return name; 
} 
 
 public void setName（String name）{
 this.name = name; 
} 
 
 public Set< RelatedCamera> getRelatedCameraSet（）{
 return relatedCameraSet; 
} 
 
 public void setRelatedCameraSet（Set< RelatedCamera> relatedCameraSet）{
 this.relatedCameraSet = relatedCameraSet; 
 
 
 $ b $实体
 @Table（name =RELATED_CAMERA）
 public class RelatedCamera {
 @Id 
 @GeneratedValue（strategy = GenerationType.AUTO）
 @Column（name =ID）
 private长ID; 
 
 @Column（name =MAP_ID）
 private String mapId; 
 
 @ManyToOne（cascade = CascadeType.ALL，fetch = FetchType.EAGER）
 @JoinColumn（name =CAMERA_ID，referencedColumnName =id，nullable = true）
私人相机; 
 
 public Long getId（）{
 return id; 
} 
 
 public void setId（Long id）{
 this.id = id; 
} 
 
 public String getMapId（）{
 return mapId; 
} 
 
 public void setMapId（String mapId）{
 this.mapId = mapId; 
} 
 
公开相机getCamera（）{
返回相机; 
} 
 
 public void setCamera（Camera camera）{
 this.camera = camera; 
 
 
 
 @Entity 
 @Table（name =CAMERA）
 public class Camera {
 @Id 
 @GeneratedValue（strategy = GenerationType.AUTO）
 @Column（name =ID）
 private长ID; 
 
 @Column（name =NAME）
私人字符串名称; 
 
 public Long getId（）{
 return id; 
} 
 
 public void setId（Long id）{
 this.id = id; 
} 
 
 public String getName（）{
 return name; 
} 
 
 public void setName（String name）{
 this.name = name; 
 
 
  

如何编写@OrderBy注释以排序收集通过摄像头名称使用SQL？

非常感谢！

解决方案

现在我意识到不可能做到通过@OrderBy，我认为通过实现Comparable接口或指定一个Comparator是无效的，因为它需要两个步骤才能完成工作，第一步是从DB查询，第二步在内存中排序。仅通过SQL获得排序的集合是有效的。

为了获得高效率，我必须改变MicroMap的类别如下：

  import javax.persistence.Column; 
 import javax.persistence.Entity; 
 import javax.persistence.GeneratedValue; 
 import javax.persistence.GenerationType; 
 import javax.persistence.Id; 
 import javax.persistence.Table; 
 $ b $ @Entity 
 @Table（name =MICRO_MAP）
 public class MicroMap {
 @Id 
 @GeneratedValue（strategy = GenerationType.AUTO） 
 @Column（name =ID）
私人长ID; 
 
 @Column（name =NAME，length = 32，nullable = false）
私有字符串名称; 
 $ b $ // @OneToMany（mappedBy =mapId，cascade = CascadeType.ALL，fetch = FetchType.EAGER）
 // @OrderBy（camera.name）// OrderBy the相机表格中的名称字段，但是JPA不支持
 // private Set< RelatedCamera> relatedCameraSet; 
 
 public Long getId（）{
 return id; 
} 
 
 public void setId（Long id）{
 this.id = id; 
} 
 
 public String getName（）{
 return name; 
} 
 
 public void setName（String name）{
 this.name = name; 
 
 
 $ / code> 

，并在DAO或Service类中添加一个方法。 / p>



  public List< RelatedCamera> getRelatedCamera（Long mapId）{
 Session session = sessionFactory.getCurrentSession（）; 
列出< RelatedCamera> list = session.createQuery（来自RelatedCamera，其中mapId =+ mapId +order by camera.name）。 
返回列表; 
} 
  




There are 3 tables. There is the variable of "relatedCameraSet" need to order by "camera.name" using SQL, but the field of "camera.name" is not in table of "RelatedCamera", is in the outer joined table of "Camera". The following annotation of @OrderBy doesn't work.

@Entity
@Table(name = "MICRO_MAP")
public class MicroMap { //main table
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "ID")
    private Long id;

    @Column(name = "NAME", length = 32, nullable = false)
    private String name;

    @OneToMany(mappedBy="mapId",cascade=CascadeType.ALL, fetch=FetchType.EAGER)
    @OrderBy("camera.name") //OrderBy the field of "name" in Camera table 
    private Set<RelatedCamera> relatedCameraSet;

    public Long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Set<RelatedCamera> getRelatedCameraSet() {
        return relatedCameraSet;
    }

    public void setRelatedCameraSet(Set<RelatedCamera> relatedCameraSet) {
        this.relatedCameraSet = relatedCameraSet;
    }    
}

@Entity
@Table(name = "RELATED_CAMERA")
public class RelatedCamera {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "ID")
    private Long id;

    @Column(name = "MAP_ID")
    private String mapId;

    @ManyToOne(cascade=CascadeType.ALL, fetch=FetchType.EAGER)
    @JoinColumn(name = "CAMERA_ID", referencedColumnName="id",nullable = true)
    private Camera camera;

    public Long getId() {
        return id;
    }

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

    public String getMapId() {
        return mapId;
    }

    public void setMapId(String mapId) {
        this.mapId = mapId;
    }

    public Camera getCamera() {
        return camera;
    }

    public void setCamera(Camera camera) {
        this.camera = camera;
    }    
}

@Entity
@Table(name = "CAMERA")
public class Camera {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "ID")
    private Long id;

    @Column(name = "NAME")
    private String name;

    public Long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }   
}

How to write @OrderBy annotation in order to sort collection by camera name using SQL?

Thanks alot!

解决方案

Now i realize it's impossible to do by @OrderBy, and i think it's not efficient to to do by implementing Comparable interface or specifying a Comparator, because it will take two steps to get job done, the step one is query from DB, the step two is sorting in memory. It's efficient to get sorted collection just by SQL.

In order to get high efficiency, i have to change the class of MicroMap as following:

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "MICRO_MAP")
public class MicroMap {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "ID")
    private Long id;

    @Column(name = "NAME", length = 32, nullable = false)
    private String name;

//    @OneToMany(mappedBy="mapId",cascade=CascadeType.ALL, fetch=FetchType.EAGER)
//    @OrderBy("camera.name") //OrderBy the field of "name" in Camera table, But JPA doesn't support
//    private Set<RelatedCamera> relatedCameraSet;

    public Long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

and add a method in DAO or Service class.

public List<RelatedCamera> getRelatedCamera(Long mapId) {
    Session session = sessionFactory.getCurrentSession();
    List<RelatedCamera> list = session.createQuery(" from RelatedCamera where mapId="+mapId+" order by camera.name").list();
    return list;
}

这篇关于如何为外连接表字段编写@OrderBy批注以使用SQL对集合进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！