Skip to main content

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

Posted by arungupta on April 8, 2009 at 5:52 AM PDT

EclipseLink
JPA
replaces href="http://www.oracle.com/technology/products/ias/toplink/jpa/index.html">TopLink
Essentials as the JPA implementation in href="http://glassfish.org/v3">GlassFish v3. One
of the benefits of using EclipseLink is that it provides href="http://wiki.eclipse.org/EclipseLink/Examples/JPA/Pagination">efficient
pagination support for the MySQL database by generating
native SQL statements such as "SELECT ... FROM <table>
LIMIT <offset>, <rowcount>".



The MySQL LIMIT clause href="http://dev.mysql.com/doc/refman/5.0/en/select.html#id2572031">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
style="font-style: italic;">


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 style="font-weight: bold;">The style="font-weight: bold;">Day) explains how to
create a JPA Persistence Unit for  href="http://dev.mysql.com/doc/sakila/en/sakila.html">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 href="http://blogs.sun.com/arungupta/entry/restful_representation_of_sakila_using">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 href="http://blogs.sun.com/vkraemer/entry/7_0_and_glassfish_v3">explained
      here.
    2. In 3.3, EclipseLink
      is shown as the default Persistence Provider as shown below:



      alt=""
      src="http://blogs.sun.com/arungupta/resource/images/gf3p-sakila-eclipselink.png">
    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:


      style="text-align: left; background-color: rgb(204, 204, 255); width: 100%;"
      cellpadding="2" cellspacing="2">
      <properties>

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

      </properties>



      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 href="http://blogs.sun.com/arungupta/entry/totd_43_glassfish_v3_build">latest
    promoted build ( href="http://download.java.net/glassfish/v3/promoted/glassfish-v3-ea-b43.zip">b43
    as of this writing). Create the JDBC Connection Pool as:


    style="text-align: left; background-color: rgb(204, 204, 255); width: 100%;"
    cellpadding="2" cellspacing="2">
    ./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:


    style="text-align: left; background-color: rgb(204, 204, 255); width: 100%;"
    cellpadding="2" cellspacing="2">
    ./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:


    style="text-align: left; background-color: rgb(204, 204, 255); width: 100%;"
    cellpadding="2" cellspacing="2">
        @PersistenceUnit

        EntityManagerFactory emf;



    and change the "processRequest" operation to:


    style="text-align: left; background-color: rgb(204, 204, 255); width: 100%;"
    cellpadding="2" cellspacing="2">
           
    EntityManager em = emf.createEntityManager();



           
    response.setContentType("text/html;charset=UTF-8");

           
    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.setFirstResult(startRow); style="font-weight: bold;">
               
    q.setMaxResults(startRow + howMany);


               
    for (Object film : q.getResultList()) {

                   
    out.print(((Film)film).toString() + "<br/>");

               
    }

           
    } finally {

               
    out.close();

            }



    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:



alt=""
src="http://blogs.sun.com/arungupta/resource/images/eclipselink-v3-b43-servlet-output.png">



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


style="text-align: left; background-color: rgb(204, 204, 255); width: 100%;"
cellpadding="2" cellspacing="2">
[#|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:


style="text-align: left; background-color: rgb(204, 204, 255); width: 100%;"
cellpadding="2" cellspacing="2">
[#|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 href="http://en.oreilly.com/mysql2009/public/schedule/detail/6861">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 ( style="font-weight: bold;">Tip style="font-weight: bold;">Of style="font-weight: bold;">The style="font-weight: bold;">Day) that
you'd like to see.
A complete archive of all the tips is available href="http://blogs.sun.com/arungupta/tags/totd">here.




Technorati: totd
glassfish
v3 href="http://technorati.com/tags/eclipselink">eclipselink
jpa href="http://technorati.com/tags/mysql">mysql


Related Topics >>

Comments

Thanks Lance!

Hi Arun, Just an fyi that JDBC 4.1 will be adding additional escape syntax to support LIMIT type of functionality as this differs from vendor to vendor Also in the latest versions of Derby (10.4.x) you can use the ROW_NUMBER() window function to emulate LIMIT (this is part of the SQL Standard) Please see http://wiki.apache.org/db-derby/OLAPRowNumber for examples.