CODEWITHSUNDEEP
javamysqlhibernate
LeePhan
LeePhan

Reputation: 63

About Query statement in mysql

I use this code for paging in mysql (working with Struts 2 + Hibernate):

Query query=getSession().createQuery("from GovOffice");
query.setFirstResult(0);
query.setMaxResult(100);
List<GovOffice> list=query.list();

This will return list of GovOffice which is started from the first record, display 100 records per page.

Suppose i have 100,000 records, is this query "from GovOffice" get all 100,000 records first? By setFirstResult and setMaxReSult it will limit from 100,000 to 100 records.If this is true that means paging must be useless.

If not is there any way to prove that query will not get data from DB until we call query.list().

Upvotes: 3

Views: 291

Answers (2)

Denys S&#233;guret
Denys S&#233;guret

Reputation: 382394

Query's setFirstResult and setMaxResult use the underlying possibilities of the databases.

In the case of MySQL, it's limit.

So, no, it doesn't fetch all records and yes it's efficient.

The proof in the source code (org.hibernate.dialect.MySQLDialect):

238 public String getLimitString(String sql, boolean hasOffset) {
239     return new StringBuffer( sql.length()+20 )
240         .append(sql)
241         .append( hasOffset ? " limit ?, ?" : " limit ?")
242         .toString();
243 }

Upvotes: 5

ian.shaun.thomas
ian.shaun.thomas

Reputation: 3488

I think the documentation makes it pretty clear that only the max results will be retrieved from the database.

setMaxResults(int)

Seems to me to be the equiv of 

SELECT * FROM GovOffice LIMIT 0, 100;

Upvotes: 3

Related Questions