X

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

Guest Author
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="//cdn.app.compendium.com/uploads/user/e7c690e8-6ff9-102a-ac6d-e4aebca50425/f4a5b21d-66fa-4885-92bf-c4e81c06d916/Image/ac63cfd35d2a4f45ac010d56fc7c3444/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="//cdn.app.compendium.com/uploads/user/e7c690e8-6ff9-102a-ac6d-e4aebca50425/f4a5b21d-66fa-4885-92bf-c4e81c06d916/Image/3108dd5b517b46b4bf81eb78bf369f18/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


Join the discussion

Comments ( 10 )
  • Arun Gupta's Blog Tuesday, April 21, 2009
    [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...
  • neon Sunday, April 26, 2009

    thank you


  • neon tabela Sunday, April 26, 2009

    thank you


  • oyun zamani Friday, May 22, 2009

    thank youu


  • HD LCD monitor Friday, June 26, 2009

    thanks,nice


  • how to grow taller Thursday, August 27, 2009

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


  • web tasar&#305;m Tuesday, November 17, 2009

    thanks


  • web tasar&#305;m Tuesday, November 17, 2009

    thanks


  • Zojirushi BBCCX20 Thursday, September 30, 2010

    Thanks for this, I've learned from it!


  • Zojirushi BBCCX20 Sunday, October 3, 2010

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


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.