Eclipse Community Forums: EclipseLink » distinct keyword in batch fetch query

Home » Eclipse Projects » EclipseLink » distinct keyword in batch fetch query(distinct keyword in batch fetch query)

Show: Today's Messages :: Show Polls :: Message Navigator

distinct keyword in batch fetch query [message #990536]

Wed, 12 December 2012 23:21

Erik Pearson

Messages: 2
Registered: December 2012

Junior Member

I'm trying to avoid the "distinct" keyword from appearing in batch queries. Documentation suggests that when using batch type EXISTS the distinct keyword isn't used, however in my experience it's used in all cases when retrieving a many-to-one relationship. For example, I have the following classes written against the SCOTT demo schema in oracle:

@Entity
public class Emp implements Serializable {
	@Id private long empno;
	private String ename;
    @ManyToOne
	@JoinColumn(name="DEPTNO")
	private Dept dept;
    public Emp() { }
	public long getEmpno() { return this.empno; }
	public void setEmpno(long empno) { this.empno = empno; }
	public String getEname() { return this.ename; } 
	public void setEname(String ename) { this.ename = ename; } 
	public Dept getDept() { return this.dept; }
	public void setDept(Dept dept) { this.dept = dept; }
}

@Entity
public class Dept implements Serializable {
	@Id private long deptno;
	private String dname;
	public Dept() {}
	public long getDeptno() { return this.deptno; }
	public void setDeptno(long deptno) { this.deptno = deptno; }
	public String getDname() { return this.dname; }
	public void setDname(String dname) { this.dname = dname; }
}

I'm attempting to retrieve the employees and departments via batch fetch:

Query query = em.createQuery("select emp from Emp as emp ");
setQueryHint(query, QueryHints.BATCH_TYPE, BatchFetchType.EXISTS);
setQueryHint(query, QueryHints.BATCH, "emp.dept");
List<Emp> resultList = query.getResultList();
resultList.get(0).getDept();

The following SQL is generated:

[EL Fine]: sql: 2012-12-12 17:04:21.178--ServerSession(1034011695)--Connection(312759349)--SELECT EMPNO, ENAME, DEPTNO FROM SCOTT.EMP
[EL Fine]: sql: 2012-12-12 17:04:21.286--ServerSession(1034011695)--Connection(312759349)--SELECT DISTINCT t0.DEPTNO, t0.DNAME FROM SCOTT.DEPT t0 WHERE EXISTS (SELECT t1.EMPNO FROM SCOTT.EMP t1 WHERE (t0.DEPTNO = t1.DEPTNO))

Is there something additional that needs to be done in addition to using EXISTS batch type to avoid the distinct keyword in batch queries?

Report message to a moderator