Miles to go …

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:
      

      <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 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:
   

   @PersistenceUnit     EntityManagerFactory emf;

   and change the “processRequest” operation to:

   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);             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:

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

Related posts:

1. TOTD #121: JDBC resource for MySQL and Oracle sample database in GlassFish v3
2. TOTD #9: Using JDBC connection pool/JNDI name from GlassFish in Rails Application
3. TOTD #99: Creating a Java EE 6 application using MySQL, JPA 2.0 and Servlet 3.0 with GlassFish Tools Bundle for Eclipse
4. TOTD #93: Getting Started with Java EE 6 using NetBeans 6.8 M1 & GlassFish v3 – A simple Servlet 3.0 + JPA 2.0 app
5. TOTD #38: Creating a MySQL Persistence Unit using NetBeans IDE