TOTD #78: GlassFish, EclipseLink, and MySQL efficient pagination using LIMIT

EclipseLink JPA replaces TopLink Essentials as the JPA implementation in GlassFish v3. One of the benefits of using EclipseLink is that it provides efficient pagination support for the MySQL database by generating native SQL statements such as "SELECT ... FROM <table> LIMIT <offset>, <rowcount>".

The MySQL LIMIT clause definition says:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be non-negative integer constants (except when using prepared statements).

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT \* FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

So instead of fetching all rows from the database and then filtering from row 6-15, only rows 6 through 15 are fetched.

This TOTD (Tip Of The Day) explains how to create a JPA Persistence Unit for sakila (MySQL sample database) using NetBeans, use EclipseLink as the Persistence Provider, and then write a JPA query to leverage the pagination support - all on GlassFish v3.
  1. Create a Persistence Unit for "sakila" as explained in this blog using bullets #1 - 3. The differences are explained below:
    1. In 2.1, choose "GlassFish v3 Prelude" as the server. Even though "GlassFish v3 Prelude" is chosen as the server but it will be replaced with a recent promoted build because pagination feature is not implemented in the Prelude. Alternatively you can use NetBeans 6.7 M3 and GlassFish v3 as explained here.
    2. In 3.3, EclipseLink is shown as the default Persistence Provider as shown below:

    3. In 3.5, there is no need to specify the properties for "user" and "password as the JDBC resource is stored in the server configuration. Instead specify the following property:

          <property name="eclipselink.logging.level" value="FINE"/>

      This will log any SQL statement sent by JPA to the underlying persistence provider (EclipseLink in this case).
  2. If GlassFish v3 was configured using NetBeans 6.7 M3, then the JDBC Connection Pool and JDBC resource were created in the server directly. If not, then download and unzip the latest GlassFish v3 latest promoted build (b43 as of this writing). Create the JDBC Connection Pool as:

    ./asadmin create-jdbc-connection-pool --datasourceclassname com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource --property user=duke:password=glassfish:ServerName=localhost:portNumber=3306:databaseName=sakila jdbc-mysql-pool

    and the JDBC resource:

    ./asadmin create-jdbc-resource --connectionpoolid jdbc-mysql-pool jndi/sakila

    GlassFish v3 b43 bundles "Eclipse Persistence Services - 2.0.0.r3652-M1". A later blog will explain how to replace the bundled EclipseLink version with a newer/different EclipseLink version.
  3. Create a new Servlet "QueryServlet". Inject the javax.persistence.EntityManagerFactory resource:

        EntityManagerFactory emf;

    and change the "processRequest" operation to:

            EntityManager em = emf.createEntityManager();

            PrintWriter out = response.getWriter();
            try {
                int startRow = Integer.valueOf(request.getParameter("start_row"));
                int howMany = Integer.valueOf(request.getParameter("how_many"));
                Query q = em.createNamedQuery("Film.findAll");

                q.setMaxResults(startRow + howMany);
                for (Object film : q.getResultList()) {
                    out.print(((Film)film).toString() + "<br/>");
            } finally {

    This Servlet reads two parameters from the request and sets parameters on the JPA Query to enable pagination.
  4. Deploy the application on GlassFish v3.
    1. Using NetBeans 6.7 M3, select "Deploy" from the context-sensitive menu.
    2. Using NetBeans 6.5.1, select "Clean and Build" and then manually deploy the WAR file using "asadmin deploy dist/Pagination.war".
If the project name was "Pagination", then the Servlet is accessible at "http://localhost:8080/Pagination/QueryServlet?start_row=1&how_many=10" and shows ten rows starting at index "1". The output looks like:

The log file in "domains/domain1/logs/server.log" show the following SQL query generated by EclipseLink:

[#|2009-04-07T14:01:12.815-0700|FINE|glassfish|org.eclipse.persistence.session.file: /Users/arungupta/tools/glassfish/v3/b43/glassfishv3/glassfish/domains/domain1/applications/Pagination/WEB-INF/classes/-PaginationPU.sql| _ThreadID=15;_ThreadName=Thread-1;ClassName=null;MethodName=null;|SELECT film_id AS film_id1, special_features AS special_features2, last_update AS last_update3, rental_duration AS rental_duration4, release_year AS release_year5, title AS title6, description AS description7, replacement_cost AS replacement_cost8, length AS length9, rating AS rating10, rental_rate AS rental_rate11, language_id AS language_id12, original_language_id AS original_language_id13 FROM film LIMIT ?, ?
        bind => [1, 11]|#]

As you can see, the query uses the LIMIT clause which optimizes the data returned from the table.

If a different database, for example Derby, is used then the generated SQL query looks like as:

[#|2009-04-07T17:00:34.210-0700|FINE|glassfish|org.eclipse.persistence.session.file: /Users/arungupta/tools/glassfish/v3/b43/glassfishv3/glassfish/domains/domain1/applications/Pagination/WEB-INF/classes/-PaginationPU.sql| _ThreadID=15;_ThreadName=Thread-1;ClassName=null;MethodName=null;|SELECT film_id, special_features, last_update, rental_duration, release_year, title, description, replacement_cost, length, rating, rental_rate, language_id, original_language_id FROM film|#]

In this case, the entire table is fetched and the rows are filtered based upon the critieria specified on the client side.

If the number of rows is huge (a typical case for enterprise) then MySQL provides efficient fetching of records. And GlassFish v3, with EclipseLink JPA integrated, makes it much seamless for you.

Thanks to Mr GlassFish Persistence (aka Mitesh :) for helping me understand the inner workings.

Discuss this more at Creating Quick and Powerful Web Applications with MySQL, GlassFish, and NetBeans technical session in the upcoming MySQL Users Conference!

Please leave suggestions on other TOTD (Tip Of The Day) that you'd like to see. A complete archive of all the tips is available here.

Technorati: totd glassfish v3 eclipselink jpa mysql

[Trackback] I presented on Creating Quick and Powerful Web Applications with MySQL, GlassFish, and NetBeans. The key messages conveyed during the preso are: GlassFish is an open source community and delivers production-quality Java EE compliant Application Server...

Posted by Arun Gupta's Blog on April 21, 2009 at 02:53 PM PDT #

thank you

Posted by neon on April 26, 2009 at 07:10 AM PDT #

thank you

Posted by neon tabela on April 26, 2009 at 07:10 AM PDT #

thank youu

Posted by oyun zamani on May 22, 2009 at 07:37 AM PDT #


Posted by HD LCD monitor on June 26, 2009 at 12:28 AM PDT #

I can't make it work, are sure it's 1005 right?

Posted by how to grow taller on August 27, 2009 at 07:13 AM PDT #


Posted by web tasarım on November 17, 2009 at 12:58 AM PST #


Posted by web tasarım on November 17, 2009 at 12:59 AM PST #

Thanks for this, I've learned from it!

Posted by Zojirushi BBCCX20 on September 30, 2010 at 02:59 PM PDT #

This really helped me a lot, I was trying to figure out what's wrong with mysql statement.

Posted by Zojirushi BBCCX20 on October 02, 2010 at 06:09 PM PDT #

Post a Comment:
Comments are closed for this entry.

profile image
Arun Gupta is a technology enthusiast, a passionate runner, author, and a community guy who works for Oracle Corp.

Java EE 7 Samples

Stay Connected


« June 2016