Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » SetFirstResult and setMaxResult problem if exist @OneToMany relation
SetFirstResult and setMaxResult problem if exist @OneToMany relation [message #1063094] Wed, 12 June 2013 09:35 Go to next message
F C is currently offline F CFriend
Messages: 12
Registered: June 2013
Junior Member
Hi,
i use EclipseLink 2.4, JPA 2.0 and Spring framework.
I have the following entity:

@Entity
@Table(name="DIPENDENTE")
public class Dipendente implements Serializable {

@Id
@Column(name="C_DIPENDENTE")
private String cDipendente;

@Column(name="NOME")
private String nome;

@Column(name="COGNOME")
private String cognome;

@JoinFetch(JoinFetchType.INNER)
@OneToMany(mappedBy="dipendente", cascade = {CascadeType.PERSIST,CascadeType.MERGE})
private List<Recapito> recapito;

.......
}

@Entity
@Table(name="RECAPITO")
public class Recapito implements Serializable {

@Id
@Column(name="C_RECAPITO")
private String cRecapito;

@Column(name="VIA")
private String via;

@ManyToOne
private Dipendente dipendente
.......

}

In DB I have this data:

DIPENDENTE
C_DIPENDENTE NOME COGNOME
1 x y
2 xx yy
3 xxx yyy
4 xxxx yyyy
5 z p
6 zz pp
7 zzz ppp
8 zzzz pppp
9 k t
10 kk tt
11 kkk ttt

RECAPITO
C_RECAPITO VIA C_DIPENDENTE
1 v 1
2 vv 1
3 vvv 1
4 g 2
5 j 3
6 l 4
7 m 5
8 n 6
9 e 7
10 dd 8
11 ss 9
12 ko 10
13 ft 11

when I execute JPQL query:

SELECT DISTINCT d FROM Dipendente d ORDER BY d.COGNOME, d.NOME, d.C_DIPENDENTE

with

query.setFirstResult(0);
query.setMaxResults(10);

the result is:
C_DIPENDENTE NOME COGNOME C_RECAPITO VIA C_DIPENDENTE_1
1 x y 1 v 1
1 x y 2 vv 1
1 x y 3 vvv 1
2 xx yy 4 g 2
3 xxx yyy 5 j 3
4 xxxx yyyy 6 l 4
5 z p 7 m 5
6 zz pp 8 n 6
7 zzz ppp 9 e 7
8 zzzz pppp 10 dd 8

and the object size in java has size 8 instead of 10
How to solve this problem?
Thanks
Re: SetFirstResult and setMaxResult problem if exist @OneToMany relation [message #1063141 is a reply to message #1063094] Wed, 12 June 2013 13:03 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
The first and last Dipendente are trimmed because EclipseLink cannot be sure it got all the referenced Recapito rows. You can account for this behavior by setting max rows to 12, but a better solution might be to not use join fetch on the recapito OneToMany and instead use batch fetch with BatchFetchType.IN. This will allow the initial query to bring in exactly 10 rows from the DIPENDENTE and use a separate query to bring in all the referenced Recapito entities.

Best Regards,
Chris
Re: SetFirstResult and setMaxResult problem if exist @OneToMany relation [message #1063220 is a reply to message #1063141] Wed, 12 June 2013 17:14 Go to previous messageGo to next message
Marvin Toll is currently offline Marvin TollFriend
Messages: 30
Registered: July 2009
Member
@Chris... while we are on this topic... our multicore implementation ( http://patternenabled.com/source/html/info/soaj/jpa/rm/SjpPersistenceRM.java.html ) sets the "fetch size" and "max results" to be equivalent to the "count" for the query.

Question: Is setting these values equivalent a recommended practise when we are certain of the result set size?

09        // Check if max results were set.
10        if (0 < namedQueryPO.getMaximumFetchSizePerQuery()) {
11
12            /*
13             * "eclipselink.jdbc.fetch-size" Configures the JDBC fetch-size for
14             * the queries result-set. This can improve the performance for
15             * queries that return large result-sets. Valid values are Integer
16             * or Strings that can be parsed to int values.
17             */
18            query.setHint(QueryHints.JDBC_FETCH_SIZE,
19                    namedQueryPO.getMaximumFetchSizePerQueryAsObject());
20
21            // The combination of the two conditional statements
22            // leads one to conclude this is a multicore query.
23            if (0 < namedQueryPO.getMulticoreTaskCount()) {
24
25                query.setMaxResults(namedQueryPO.getMaximumFetchSizePerQuery());
26
27                query.setFirstResult(namedQueryPO.getQueryStartPosition());
28
29            }
30        }



Marvin Toll
CTO, Pattern Enabled Development
http://PatternEnabled.com

[Updated on: Wed, 12 June 2013 17:14]

Report message to a moderator

Re: SetFirstResult and setMaxResult problem if exist @OneToMany relation [message #1064278 is a reply to message #1063220] Tue, 18 June 2013 14:19 Go to previous message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

You should not set the maxResults unless you want to limit the results.
Setting the fetchSize can be an optimization, but it depends on your database, sometimes a fetch size of 1/2 the count performs better than the whole count for large queries.


James : Wiki : Book : Blog : Twitter
Previous Topic:Mapping Joda Interval
Next Topic:EclipseLink and MongoDB - Auto incrementing field
Goto Forum:
  


Current Time: Fri Dec 19 06:36:47 GMT 2014

Powered by FUDForum. Page generated in 0.02164 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software