Announcement Announcement Module
Collapse
No announcement yet.
Efficient query for related entity in collection Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Efficient query for related entity in collection

    I'm using JPA and hibernate (Core 3.3.2, entity manager 3.4) with Oracle 10g.

    Question: What's the best (or a good way) to query for a objects that match a specific property of an object that us 3 joins away with a one to many relation in the middle:

    My query needs to get all objects of type Part that have at least one Location with a given Status, example:

    Code:
    @Table(name = "tbl_parts")
    public class Part implements java.io.Serializable {
    
    	private Integer id;
    ...
    	private SortedSet<Location> locations;
    ...
    
    
    	@Id
    	@Column(name = "id", unique = true, nullable = false)
    	public int getId() {
    		return this.id;
    	}
    
    	public void setId(int id) {
    		this.id = id;
    	}
    
    	@OneToMany(cascade = {CascadeType.REMOVE}, mappedBy="build")
        @JoinColumn(name="build_id", nullable=true, insertable=false, updatable=false)
        @NotFound(action=NotFoundAction.IGNORE)
        @Sort(type = SortType.COMPARATOR, comparator = LocationComparator.class)
        public SortedSet<Location> getLocations() {
    		return locations;
    	}
    
    	public void setLocations(SortedSet<Location>  locations) {
    		this.locations = locations;
    	}
    	
    }
    
    
    
    @Table(name = "tbl_locations")
    public class Location implements java.io.Serializable {
    
    	
    	private Integer id;
    ...
    	private Status status;
    ...
    
    	@Id
    	@Column(name = "id", unique = true, nullable = false)
    	public int getId() {
    		return this.id;
    	}
    
    	public void setId(int id) {
    		this.id = id;
    	}
    
    	@ManyToOne(optional=true, fetch = FetchType.LAZY, cascade={CascadeType.REFRESH})
        @JoinColumn(name="status_id", nullable=true)
    	@NotFound(action=NotFoundAction.IGNORE)	     	
    	public Status getStatus() {
    		return status;
    	}
    
    	public void setStatus(Status status) {
    		this.status = status;
    	}
    	
    }
    
    
    @Table(name = "tbl_status" )
    public class Status implements java.io.Serializable {
    
    ...
    	
    	private int id;
    	private String name;
    	
    ...
    
    	@Id
    	@Column(name = "id", unique = true, nullable = false)
    	public int getId() {
    		return this.id;
    	}
    
    	public void setId(int id) {
    		this.id = id;
    	}
    }

    The example is heavily simplified regarding other attributes of each type. My query has to be made on tbl_parts because other properties of the parts object are also queried for. I left those out to keep the example simple.

    So my query should be something along the lines of

    FROM Part p WHERE p.locations. ...


    Question is how to formulate the where clause so that the query performs and scales well rturning thousands of parts.

    Any commenst welcome.

    TIA.

    Jacques
Working...
X