Wednesday Dec 02, 2009

Wicket, JPA, GlassFish and Java Derby or MySQL

This Pet Catalog app explains a web application that uses Wicket, JPA, GlassFish and MySQL. I took this example JSF 2.0, JPA, GlassFish and MySQL and modified it to use Wicket instead of JSF. 

Explanation of the usage of Wicket, Java Persistence APIs, Glassfish and MySQL in a sample Store Catalog Application

The image below shows the Catalog Listing page, which allows a user to page through a list of items in a store.






The Wicket Framework



The Wicket Web Framework provides a component-oriented programmatic manipulation of HTML markup. The Diagram below shows how the Wicket Framework fits into the MVC design pattern.




A Wicket component controller receives user input. The component uses the user input to interact with the model, to handle page navigation and events. The Model provide components with an interface to domain data. The Component view renders the User Interface HTML elements. The controller and view parts are combined into the Component  in Wicket.


Wicket Triad of HTML Templates, Components, and Model


The Wicket component, the model, and HTML template work together. The HTML template defines the static parts of the pages, components fill in the dynamic parts, and models are used by components to get the domain data for the dynamic parts.  Wicket matches wicket:id tags with components, Models are a reference to the data for the Java components.

Wicket  HTML Templates


Wicket templates are written in plain HTML, consumable by standard HTML editors. Wicket uses HTML namespace standards to extend HTML with placeholders,  where Wicket components are hooked in, so that there is no custom syntax at all. Wicket matches wicket: id attributes and attaches Java components to the tags in which these attributes are defined.

Wicket Web pages are all Java classes that map to an HTML template, with the same name as the Java class.




The ListPage.html file holds the markup for the ListPage.java.  Wicket matches the Java page instance and the HTML template file with the same name in the same Java package.

Code Sample from:  ListPage.html

<table>
  <tr>
   <th> Name </th>
   <th> Photo</th>
   <th> Price </th>
  </tr>
  <tr wicket:id="rows" >
     <td>
        <a wicket:id="details" href="#">
         <span wicket:id="name">name</span>
        </a>
     </td>
     <td> <img wicket:id="photo"/></td>
     <td><span wicket:id="price"></span></td>

   </tr>
</table>
<span wicket:id="pager">navigation controls here</span>



Pages are special top-level components that hold the root for component trees. Below is the component tree for the ListPage above:



Wicket Components


Wicket matches the wicket:id="rows" attribute in the ListPage.html with the corresponing Java component in ListPage.java,  which is a DataViewComponent which has a DataProvider model as shown in the diagram below.


The corresponding ListPage.java code is shown below:

Code Sample from: ListPage.java



public class ListPage extends WebPage {

// create the Model DataProvider
        IDataProvider itemDataProvider = new IDataProvider<Item>() {

            public Iterator iterator(int first, int count) {
                return itemController.findItemEntities(count, first).iterator();
            }
            public int size() {
                return itemController.getItemCount();
            }
            public IModel model(final Item object) {
                return new LoadableDetachableModel() {
                    @Override
                    protected Item load() {
                        return (Item) object;
                    }
                };
            }
            public void detach() {
            }
        };

// create the DataView component for the wicketid "rows" attribute in ListPage.html
        DataView dataView = new DataView<Item>("rows", itemDataProvider, ROWS_PER_PAGE) {

            @Override
            protected void populateItem(org.apache.wicket.markup.repeater.Item<Item> repItem) {
                Item item = (Item) repItem.getModelObject();
                repItem.setModel(new CompoundPropertyModel<Item>(item));
                repItem.add(ItemDetails.link("details", item));
                repItem.add(new Image("photo", new ResourceReference(this.getClass(),  item.getImagethumburl()))); 
                repItem.add(new Label("price"));

            }
        };
// add the DataView component to the page
        add(dataView);
// create the PagingNavigator component for the "pager" attribute in ListPage.html
        PagingNavigator pager = new PagingNavigator("pager", dataView);
        add(pager);



DataView is a component which makes it simple to populate a Wicket RepeatingView from a database by utilizing IDataProvider to act as an interface between the database and the dataview.  A Wicket RepeatingView renders the components added to it from data from a collection of objects.

The  DataView instantiation passes to the constructor  the wicket id "rows" , the itemDataProvider, and the number of rows for paging, and
anonymously overrides the populateItem() method, which will be called for each Item object provided by itemDataProvider.  The  populateItem method adds the child components ( Link, Image, Label)  with the Item  Model to the Dataview.

The itemDataProvider IDataProvider provides the Pet Catalog  Item data to the DataView.  The IDataProvider iterator(int first, int count) method gets an iterator for the subset of total data.  The itemDataProvider iterator method calls the  itemController.findItemEntities , which uses JPA to query the database and return a list of Item entities.  The itemDataProvider model method  is a Callback used by the consumer of this data provider to wrap objects retrieved from iterator(int, int) with a model (usually a detachable one).




Using the Java Persistence API (JPA) with Wicket

The ItemJpaController findItemEntities method is defined as shown below:

Code Sample from: ItemJpaController.java

public class ItemJpaController {

    public ItemJpaController() {
        emf = Persistence.createEntityManagerFactory("wicketCatalogPU");
    }
    private EntityManagerFactory emf = null;

    public EntityManager getEntityManager() {
        return emf.createEntityManager();
    }
    private List<Item> findItemEntities(boolean all, int maxResults, int firstResult) {
        EntityManager em = getEntityManager();
        try {
            Query q = em.createNamedQuery("Item.findAll");
            if (!all) {
                q.setMaxResults(maxResults);
                q.setFirstResult(firstResult);
            }
            return q.getResultList();
        } finally {
            em.close();
        }
    }





The ItemJpaController uses the Java Persistence API EntityManager Query object to return a list of items. The ItemJpaController calls Persistence.createEntityManagerFactory which gets an EntityManagerFactory  when it is instatiated. 

The Java Persistence Query APIs are used to create and execute queries that can return a list of results.  The JPA Query interface provides support for pagination via the setFirstResult() and setMaxResults() methods: q.setMaxResults(int maxResult) sets the maximum number of results to retrieve. q.setFirstResult(int startPosition) sets the position of the first result to retrieve.

In the code below, we show the Item entity class which maps to the  ITEM table that stores the item instances. This is a typical Java Persistence entity object. There are two requirements for an entity:
  1. annotating the class with an @Entity annotation.
  2. annotating   the primary key identifier with @Id
Because the fields name, description.... are basic mappings from the object fields to columns of the same name in the database table, they don't have to be annotated. 

Code Sample from: Item.java

@Entity
public class Item implements java.io.Serializable {

@Id
    private Integer id;

    private String name;   
    private String description;   
    private String imageurl;   
    private String imagethumburl; 
    private BigDecimal price;


    public Item() { }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }


    ...
}   


A Wicket PagingNavigator component is used to provide navigation links to the next, previous, first and last page of catalog Items.  The PagingNavigator component maintains a complete page navigator, meant to be easily added to any PageableListView.

Code Sample from: ListPage.html

<span wicket:id="pager">navigation controls here</span>


Code below for adding creating a PagingNavigator for the dataView and adding it to the ListPage:

Code Sample from: ListPage.java


public class ListPage extends WebPage {


PagingNavigator pager = new PagingNavigator("pager", dataView);
add(pager);




A Wicket Link component is used to provide a link to click on to navigate to a page with the selected item details.

Code Sample from: ListPage.html

<a wicket:id="details" href="#">
        <span wicket:id="name" >name</span>
  </a>



Code below show the call to  the ItemDetails.link method to create and add the link:

Code Sample from: ListPage.java


public class ListPage extends WebPage {

// create the DataView component corresponding to the wicketid "rows" attribute in ListPage.html
        DataView dataView = new DataView<Item>("rows", itemDataProvider, ROWS_PER_PAGE) {
            @Override
            protected void populateItem(org.apache.wicket.markup.repeater.Item<Item> repItem) {
           '    ...
// call ItemDetails to create the link component
                repItem.add(ItemDetails.link("details", item));
...

            }
        };
// add the DataView component to the page
        add(dataView);



Code below for creating a BookmarkablePageLink for adding to the dataView, clicking on this link will Navigate to the ItemDetails page, passing the selected Itemid as a parameter.

Code Sample from: ItemDetails.java

public class ItemDetails extends BasePage {

        public static BookmarkablePageLink<Void> link(final String name, final Item item) {

        final BookmarkablePageLink<Void> link = new BookmarkablePageLink<Void>(name, ItemDetails.class);

        if (item != null) {
            link.setParameter("itemid", item.getItemid());
            link.add(new Label("name", new Model<Item>(item)));
        }

        return link;
    }


The ItemDetails page, shown below, displays details about the selected Catalog Item:

Code Sample from: ItemDetails.html

<html xmlns:wicket="http://wicket.apache.org/">
    <head>
        <title></title>
        <link wicket:id='stylesheet'/>
    </head>
    <body>
        <span wicket:id='mainNavigation'/>
        <table>
            <tr>
                <td align="right">Name:</td>
                <td>
                    <span wicket:id="name">name </span>
                </td>
            </tr>
            <tr>
                <td align="right">Description:</td>
                <td> <span wicket:id = "description">
                  description
                    </span>
                </td>
            </tr>
            <tr>
                <td align="right">Photo:</td>
                <td> <img wicket:id="imagethumburl"/>   </td>
            </tr>
        </table>
    </body>
</html>




The ItemDetails constructor gets the item data, and adds Labels and a image, for the name, description and photo to the ItemDetails page.

Code Sample from: ItemDetails.java

public class ItemDetails extends BasePage {

    public ItemDetails(PageParameters params) {
        Item item = itemController.findItem(params.getString("itemid"));
        add(new Label("name", item.getName()));
        add(new Label("description", item.getDescription()));
        add(new Image("imagethumburl", new ResourceReference(this.getClass(),item.getImageurl())));
    }






Hot Deployment with Wicket and Glassfish

  • Incremental compile of all Wicket  artifacts when you save.
  • Auto-deploy of all web artifacts

Conclusion
This concludes the sample application which demonstrates a pet catalog web application which uses  Wicket, JPA, GlassFish and MySQL.

Running the Sample Application

  1. If you haven't already done so, download and install NetBeans IDE , GlassFish , and MySQL Community Server . You can download and install GlassFish with NetBeans as a single bundle.
  2. Follow these instructions to setup Netbeans with the Wicket plugin.
  3. Download the sample code.

Create the Pet Catalog database

In order to run the sample code you first have to create the Pet Catalog database and fill in  the Item table.

  1. Start NetBeans IDE
  2. Ensure that GlassFish is registered in the NetBeans IDE, as follows:
    • Click the Services tab in the NetBeans IDE.
    • Expand the Servers node. You should see GlassFish v2 in the list of servers. If not, register GlassFish v2 as follows:
      • Right-click the Servers node and select Add Server. This opens an Add Server Instance wizard.
      • Select GlassFish v2 in the server list of the wizard and click the Next button.
      • Enter the location information for the server and click the Next button.
      • Enter the admin name and password and click the Finish button.
  3. Start the MySQL or Java DB database as follows:
    • Click the Services tab in the NetBeans IDE.
    • Expand the databases node. You should see the Java DB database in the list of databases. If you have installed the MySQL server database, you should also see the MySQL database in the list of databases.. Note:  Java DB  comes bundled with Netbeans, you can  download MySQL separately.

    • Right-mouse click on the Java DB or MySQL server database and select Start.
  4. If you installed MySQL, set the properties of the MySQL server database as follows:
    • Right-click on the MySQL server database and select Properties. This opens the MySQL Server Properties dialog box, as shown in Figure 8.

      MySQL Server Basic Properties
      Figure 8. MySQL Server Basic Properties

    • In the Basic Properties tab, enter the server host name and port number. The IDE specifies localhost as the default server host name and 3306 as the default server port number.
    • Enter the administrator user name, if not displayed, and the administrator password -- the default administrator password is blank.
    • Click the Admin Properties tab.
    • Enter an appropriate path in the Path/URL to admin tool field. You can find the path by browsing to the location of a MySQL Administration application such as the MySQL Admin Tool.
    • Enter an appropriate path in the Path to start command. You can find the path by browsing to the location of the MySQL start command. To find the start command, look for mysqld in the bin folder of the MySQL installation directory.
    • Enter an appropriate path in the Path to stop command field. You can find the path by browsing to the location of the MySQL stop command. This is usually the path to mysqladmin in the bin folder of the MySQL installation directory. If the command is mysqladmin, in the Arguments field, type -u root stop to grant root permissions for stopping the server. The Admin Properties tab should look similar to Figure 9.

      MySQL Server Administration Properties
      Figure 9. MySQL Server Administration Properties

    • Click the OK button.

  5. Right-click on the MySQL server or Java DB database and select Start.
  6. Create the petcatalog database as follows:
    • Right-mouse click on the Java DB or MySQL server database and select Create Database. This will open a create Database window.
    • Enter the database name catalog for Java DB or petcatalog for MySQL.


      For Java DB enter userid password app app as shown below:


       Click O.K. to accept the displayed settings.
  7. Create the tables in the catalog database as follows:
    • Underneath Databases you should see a database connection for the petcatalog database. For example MySQL:

      or Java DB:

    • Right-mouse click on the petcatalog connection and select Connect.
    • Right-mouse click on the petcatalog connection and select Execute Command. This will open up a SQL command window.
    • Copy the contents of the catalog.sql file in the riapetcatalog\\exercises\\exercise0 directory and paste the contents into the SQL command window, as shown in below:

      Creating Tables in the Database
    • Click the Run SQL icon Run SQL icon (Ctrl+Shift+E) above the SQL command window.
    • Note: It is ok to see this: "Error code -1, SQL state 42Y55: 'DROP TABLE' cannot be performed on 'ITEM' because it does not exist. Line 2, column 1" . This just means you are deleting a table that does not exist.  If you need to delete and recreate the tables you will not  see this message the second time.
  8. View the data in the Pet Catalog database Item table as follows:
    • Underneath Databases you should see a database connection for the petcatalog database. For example MySQL:

      or Java DB:

    • If the database connection is broken like in the following diagram:

      • Right-mouse click on the petcatalog connection and select Connect. as shown below:

      • if prompted for a password, for MySQL leave it blank, for JavaDB enter user app password app.
    • Expand the Tables node below the petcatalog database in the Services window. You should see the item table under the Tables node. You can expand the item table node to see the table columns, indexes, and any foreign keys, as shown in below :
      An Expanded Table Node
      Figure 12. An Expanded Table Node

      You can view the contents of a table or column by right-clicking the table or column and selecting View Data as shown  below:

      Viewing the Contents of a Table
      Figure 13. Viewing the Contents of a Table


  9. Follow these instructions to Create a JDBC Connection pool and JDBC resource.Name the  pool mysql_petcatalog_rootPool and the jndi resource jdbc/petcatalog. Note: you do not have to create a JDBC connection pool and resource if you use the Netbeans wizard to generate JPA entities from database tables as described in this article GlassFish and MySQL, Part 2: Building a CRUD Web Application With Data Persistence.

Running the Sample solution:

If you want to run the sample solution, you have to create the catalog database tables first as described above.

  1. If you haven't already download the sample code and start the NetBeans IDE. Unzip the catalog.zip file which you downloaded, this will create a catalog directory with the project code.
  2. Open the catalog/setup/sun-resources.xml file and verify that the property values it specifies match those of the petcatalog database and jdbc resources you created. Edit the property values as necessary.

  3. Open the catalog project as follows:
    • In NetBeans IDE, click Open Project in the File menu. This opens the Open Project dialog.
    • Navigate in the Open Project dialog to the catalog  directory and click the Open Project button.

    In response, the IDE opens the catalog project.  You can view the logical structure of the project in the Projects window (Ctrl-1).
  4. Run the catalog by right-clicking on the catalog project in the Projects window and selecting Run Project. The NetBeans IDE compiles the application, deploys it on Glassfish, and brings up the default page in your browser.  (at http://localhost:8080/catalog/).

For more information see the following resources:


Friday Oct 02, 2009

Top 10 web security vulnerabilities number 2: Injection Flaws

OWASP Top 10 number 2: Injection Flaws

Number 2 in the Top 10 most critical web application security vulnerabilities identified by the Open Web Application Security Project (OWASP) is Injection Flaws. Injection happens whenever an attacker's data is able to modify a query or command sent to a database, LDAP server, operating system or other Interpreter. Types of injections are SQL, LDAP, XPath, XSLT, HTML, XML, OS command... SQL injection and Cross-Site Scripting account for more than 80% of the vulnerabilities being discovered against Web applications (SANS Top Cyber Security Risks).

SQL Injection Example

Use of string concatenation to build query: SQL Injection can happen with dynamic database queries concatenated with user supplied input, for example with the following query:
 "select \* from MYTABLE where name=" + parameter
if the user supplies "name' OR 'a'='a' " as the parameter it results in the following:
"select \* from MYTABLE where name= 'name' OR 'a'='a'; 
the OR 'a'='a' causes the where clause to always be true which is the equivalent of the following:
"select \* from MYTABLE; 
if the user supplies "name' OR 'a'='a' ; delete from MYTABLE" as the parameter it results in the following:
"select \* from MYTABLE where name= 'name' OR 'a'='a'; delete from MYTABLE;
the OR 'a'='a' causes the where clause to always be true which is the equivalent of the following:
"select \* from MYTABLE; delete from MYTABLE;
some database servers, allow multiple SQL statements separated by semicolons to be executed at once.

SQL Injection can be used to:
  • create , read , update, or delete database data

Protecting against SQL Injection

  • Don't concatenate user input data to a query or command!
    • Use Query Parameter binding with typed parameters, this ensures the input data can only be interpreted as the value for the intended parameter so the attacker can not change the intent of a query.
  • Validate all input data to the application using white list (what is allowed) for type, format, length, range, reject if invalid. (see previous blog entry)
  • don't provide too much information in error messages (like SQL Exception Information, table names..) to the user.

Java specific Protecting against SQL Injection

Don't concatenate user input data to a query or command:

  • Don't do this with JDBC:
    String empId= req.getParameter("empId") // input parameter
    String query = "SELECT \* FROM Employee WHERE 
                         id = '" + empId +"'";  
    
    
  • Don't do this with JPA:
    q = entityManager.createQuery(“select e from Employee e WHERE ”
    		+ “e.id = '” + empId + “'”);

Use Query Parameter binding with typed parameters

  • With JDBC you should use a PreparedStatement and set values by calling one of the setXXX methods on the PreparedStatement object, For example:
    String selectStatement = "SELECT \* FROM Employee WHERE id = ? ";
    PreparedStatement pStmt = con.prepareStatement(selectStatement);
    pStmt.setString(1, empId);
    This sets the first question mark placeholder to the value of the input parameter empId in the SQL command. Any dangerous characters - such as semicolons, quotes, etc.. should be automatically escaped by the JDBC driver.

  • With JPA or Hibernate you should use Named Parameters. Named parameters are parameters in a query that are prefixed with a colon (:). Named parameters in a query are bound to an argument by the javax.persistence.Query.setParameter(String name, Object value) method. For example:
    q = entityManager.createQuery(“select e from Employee e WHERE ”
                 + “e.id = ':
    id'”);
    q.setParameter(“id”,
    empId);
    This sets the id to the empId in the SQL command, again any dangerous characters should be automatically escaped by the JDBC driver.

  • With JPA 2.0 or Hibernate you can use the Criteria API. The JPA 2.0 criteria API providies a typesafe object-based Query API based on a metamodel of the Entity classes, rather than a string-based Query API. This allows you to develop queries that a Java compiler can verify for correctness at compile time. Below is an example using the Criteria API for the same query as before :

    QueryBuilder qb = em.getQueryBuilder();
    CriteriaQuery<
    Employee> q = qb.createQuery(Employee.class);
    Root<
    Employee> e = q.from(Employee.class);
    ParameterExpression<String>
    id = cb.parameter(String.class);

    TypedQuery<
    Employee> query = em.createQuery(
    q.select(e).where(cb.equal(e.get(Employee_.id), id) );
    query.setParameter(
    id, empId);

References and More Information:




Friday Aug 28, 2009

JPA Performance, Don't Ignore the Database

jpaconcurrency


Database Schema

Good Database schema design is important for performance. One of the most basic optimizations is to design your tables to take as little space on the disk as possible , this makes disk reads faster and uses less memory for query processing.

Data Types

You should use the smallest data types possible, especially for indexed fields. The smaller your data types, the more indexes (and data) can fit into a block of memory, the faster your queries will be.

Normalization

Database Normalization eliminates redundant data, which usually makes updates faster since there is less data to change. However a Normalized schema causes joins for queries, which makes queries slower, denormalization speeds retrieval. More normalized schemas are better for applications involving many transactions, less normalized are better for reporting types of applications.  You should normalize your schema first, then de-normalize later.  Applications often need to mix the approaches, for example use a partially normalized schema, and duplicate, or cache, selected columns from one table in another table. With JPA O/R mapping you can use the @Embedded annotation for denormalized columns to specify a persistent field whose @Embeddable type can be stored as an intrinsic part of the owning entity and share the identity of the entity.



Database Normalization and Mapping Inheritance Hiearchies

The Class Inheritance hierarchy shown below will be used as an example of JPA O/R mapping.


In the Single table per class mapping shown below, all classes in the hierarchy are mapped to a single table in the database. This table has a discriminator column (mapped by @DiscriminatorColumn), which identifies the subclass.  Advantages: This is fast for querying, no joins are required. Disadvantages:  wastage of space since all inherited fields are in every row, a deep inheritance hierarchy will result in wide tables with many, some empty columns.



In the Joined Subclass mapping shown below, the root of the class hierarchy is represented by a single table, and each subclass has a separate table that only contains those fields specific to that subclass. This is normalized (eliminates redundant data) which is better for storage and updates. However queries cause joins which makes queries slower especially for deep hierachies, polymorphic queries and relationships.


In the Table per Class mapping (in JPA 2.0, optional in JPA 1.0),  every concrete class is mapped to a table in the database and all the inherited state is repeated in that table. This is not normlalized, inherited data is repeated which wastes space.  Queries for Entities of the same type are fast, however  polymorphic queries cause unions which are slower.



Know what SQL is executed

You need to understand the SQL queries your application makes and evaluate their performance. Its a good idea to enable SQL logging, then go through a use case scenario to check the executed SQL.  Logging is not part of the JPA specification, With EclipseLink you can enable logging of SQL by setting the following property in the persistence.xml file:

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


With Hibernate you set the following property in the persistence.xml file:

<properties>
    <property name="hibernate.show_sql" value="true" />
</properties>


Basically you want to make your queries access less data, is your application retrieving more data than it needs, are queries accessing too many rows or columns? Is the database query analyzing more rows than it needs? Watch out for the following:
  • queries which execute too often to retrieve needed data
  • retrieving more data than needed
  • queries which are too slow
    • you can use EXPLAIN to see where you should add indexes

With MySQL you can use the slow query log to see which queries are executing slowly, or you can use the MySQL query analyzer to see slow queries, query execution counts, and results of EXPLAIN statements.

Understanding EXPLAIN

For slow queries, you can precede a SELECT statement with the keyword EXPLAIN  to get information about the query execution plan, which explains how it would process the SELECT,  including information about how tables are joined and in which order. This helps find missing indexes early in the development process.




You should index columns that are frequently used in Query WHERE, GROUP BY clauses, and columns frequently used in joins, but be aware that indexes can slow down inserts and updates.

Lazy Loading and JPA

With JPA many-to-one and many-to-many relationships lazy load by default, meaning they will be loaded when the entity in the relationship is accessed. Lazy loading is usually good, but if you need to access all of the "many" objects in a relationship, it will cause n+1 selects where n is the number of "many" objects.




You can change the relationship to be loaded eagerly as follows :




However you should be careful with eager loading which could cause SELECT statements that fetch too much data. It can cause a Cartesian product if you eagerly load entities with several related collections.


If you want to override the LAZY fetch type for specific use cases, you can use Fetch Join. For example this query would eagerly load the employee addresses:

In General you should lazily load relationships, test your use case scenarios, check the SQL log, and use @NameQueries with JOIN FETCH to eagerly load when needed.

Partitioning

the main goal of partitioning is to reduce the amount of data read for particular SQL operations so that the overall response time is reduced

Vertical Partitioning  splits tables with many columns into multiple tables with fewer columns, so that only certain columns are included in a particular dataset, with each partition including all rows.

Horizontal Partitioning segments table rows so that distinct groups of physical row-based datasets are formed. All columns defined to a table are found in each set of partitions. An example of horizontal partitioning might be a table that contains historical data being partitioned by date.

Vertical Partitioning


In the example of vertical partitioning below a table that contains a number of very wide text or BLOB columns that aren't referenced often is split into two tables with the most referenced columns in one table and the seldom-referenced text or BLOB columns in another.

By removing the large data columns from the table, you get a faster query response time for the more frequently accessed Customer data. Wide tables can slow down queries, so you should always ensure that all columns defined to a table are actually needed.

The example below shows the JPA mapping for the tables above. The Customer data table with the more frequently accessed and smaller data types  is mapped to the Customer Entity, the CustomerInfo table with the less frequently accessed and larger data types is mapped to the CustomerInfo Entity with a lazily loaded one to one relationship to the Customer.



Horizontal Partitioning

The major forms of horizontal partitioning are by Range, Hash, Hash Key, List, and Composite.

Horizontal partitioning can make queries faster because the query optimizer knows what partitions contain the data that will satisfy a particular query and will access only those necessary partitions during query execution. Horizontal Partitioning works best for large database Applications that contain a lot of query activity that targets specific ranges of database tables.


Hibernate Shards

Partitioning data horizontally into "Shards" is used by google, linkedin, and others to give extreme scalability for very large amounts of data. eBay "shards" data horizontally along its primary access path.

Hibernate Shards is a framework that is designed to encapsulate support for horizontal partitioning into the Hibernate Core.


Caching

JPA Level 2 caching avoids database access for already loaded entities, this make reading reading frequently accessed unmodified entities faster, however it can give bad scalability for frequent or concurrently updated entities.

You should configure L2 caching for entities that are:
  • read often
  • modified infrequently
  • Not critical if stale
You should also configure L2 (vendor specific) caching for maxElements, time to expire, refresh...

References and More Information:

JPA Best Practices presentation
MySQL for Developers Article
MySQL for developers presentation
MySQL for developers screencast
Keeping a Relational Perspective for Optimizing Java Persistence
Java Persistence with Hibernate
Pro EJB 3: Java Persistence API
Java Persistence API 2.0: What's New ?
High Performance MySQL book
Pro MySQL, Chapter 6: Benchmarking and Profiling
EJB 3 in Action
sharding the hibernate way
JPA Caching
Best Practices for Large-Scale Web Sites: Lessons from eBay




Friday Aug 21, 2009

JPA Caching


JPA Level 1 caching

JPA has 2 levels of caching. The first level of caching is the persistence context.

The JPA Entity Manager maintains a set of Managed Entities in the Persistence Context.

The Entity Manager guarantees that within a single Persistence Context, for any particular database row, there will be only one object instance. However the same entity could be managed in another User's transaction, so you should use either optimistic or pessimistic locking  as explained in JPA 2.0 Concurrency and locking

The code below shows that a find on a managed entity with the same id and class as another in the same persistence context , will return the same instance.

@Stateless public ShoppingCartBean implements ShoppingCart {

 @PersistenceContext EntityManager entityManager;

 public OrderLine createOrderLine(Product product,Order order) {
        OrderLine orderLine = new OrderLine(order, product);
        entityManager.persist(orderLine);   //Managed
        OrderLine orderLine2 =entityManager.find(OrderLine,
orderLine.getId()));
     (orderLine == orderLine2  // TRUE
        return (orderLine);
    }

}

The diagram below shows the life cycle of an Entity in relation to the Persistent Context.

The code below illustrates the life cycle of an Entity. A reference to a container managed EntityManager is injected using the persistence context annotation. A new order entity is created and the entity has the state of new. Persist is called, making this a managed entity. because it is a stateless session bean it is by default using container managed transactions , when this transaction commits , the order is made persistent in the database. When the orderline entity is returned at the end of the transaction it is a detached entity.

The Persistence Context can be either Transaction Scoped-- the Persistence Context 'lives' for the length of the transaction, or Extended-- the Persistence Context spans multiple transactions. With a Transaction scoped Persistence Context, Entities are "Detached" at the end of a transaction.

As shown below, to persist the changes on a detached entity, you call the EntityManager's merge() operation, which returns an updated managed entity, the entity updates will be persisted to the database at the end of the transaction.

An Extended Persistence Context spans multiple transactions, and the set of Entities in the Persistence Context stay Managed. This can be useful in a work flow scenario where a "conversation" with a user spans multiple requests.

The code below shows an example of a Stateful Session EJB with an Extended Persistence Context in a use case scenario to add line Items to an Order. After the Order is persisted in the createOrder method, it remains managed until the EJB remove method is called. In the addLineItem method , the Order Entity can be updated because it is managed, and the updates will be persisted at the end of the transaction.


The example below contrasts updating the Order using a transaction scoped Persistence Context verses an extended Persistence context. With the transaction scoped persistence context, an Entity Manager find must be done to look up the Order, this returns a Managed Entity which can be updated. With the Extended Persistence Context the find is not necessary. The performance advantage of not doing a database read to look up the Entity, must be weighed against the disadvantages of memory consumption for caching, and the risk of cached entities being updated by another transaction.  Depending on the application and the risk of contention among concurrent transactions this may or may not give better performance / scalability.

JPA second level (L2) caching

JPA second level (L2) caching shares entity state across various persistence contexts.


JPA 1.0 did not specify support of a second level cache, however, most of the persistence providers provided support for second level cache(s). JPA 2.0 specifies support for basic cache operations with the new Cache API, which is accessible from the EntityManagerFactory, shown below:


If L2 caching is enabled, entities not found in persistence context, will be loaded from L2 cache, if found.

The advantages of L2 caching are:
  • avoids database access for already loaded entities
  • faster for reading frequently accessed  unmodified entities
The disadvantages of L2 caching are:
  • memory consumption for large amount of objects
  • Stale data for updated objects
  • Concurrency for write (optimistic lock exception, or pessimistic lock)
  • Bad scalability for frequent or concurrently updated entities

You should configure L2 caching for entities that are:
  • read often
  • modified infrequently
  • Not critical if stale
You should protect any data that can be concurrently modified with a locking strategy:
  • Must handle optimistic lock failures on flush/commit
  • configure expiration, refresh policy to minimize lock failures
The Query cache is useful for queries that are run frequently with the same parameters, for not modified tables.

The EclipseLink JPA persistence provider caching Architecture

The  EclipseLink caching Architecture is shown below.


Support for second level cache in EclipseLink is turned on by default, entities read are L2 cached. You can disable the L2 cache. EclipseLink caches entities in L2, Hibernate caches entity id and state in L2. You can configure caching by Entity type or Persistence Unit with the following configuration parameters:
  • Cache isolation, type, size, expiration, coordination, invalidation,refreshing
  • Coordination (cluster-messaging)
  • Messaging: JMS, RMI, RMI-IIOP, …
  • Mode: SYNC, SYNC+NEW, INVALIDATE, NONE
The example below shows configuring the L2 cache for an entity using the @Cache annotation

The Hibernate JPA persistence provider caching Architecture

The Hibernate JPA persistence provider caching architecture is different than EclipseLink: it is not configured by default, it does not cache enities just id and state, and you can plug in different L2 caches. The diagram below shows the different L2 cache types that you can plug into Hibernate.

The configuration of the cache depends on the type of caching plugged in. The example below shows configuring the hibernate L2 cache for an entity using the @Cache annotation

For More Information:

Introducing EclipseLink
EclipseLink JPA User Guide
Hibernate Second Level Cache
Speed Up Your Hibernate Applications with Second-Level Caching
Hibernate caching
Java Persistence API 2.0: What's New ?
Beginning Java™ EE 6 Platform with GlassFish™ 3
Pro EJB 3: Java Persistence API (JPA 1.0)





Tuesday Aug 11, 2009

Java EE 6 Pet Catalog with GlassFish v3 preview and MySQL


Java EE 6 Pet Catalog with GlassFish v3 preview and MySQL


This Pet Catalog app explains a web application that uses JSF 2.0, Java EE 6, GlassFish and MySQL. I took this example  GlassFish and MySQL, Part 2: Building a CRUD Web Application With Data Persistence and modified it to use some of the new features of JSF 2.0 and Java EE 6.
http://www.netbeans.org/kb/samples/index.html 

Explanation of the usage of JSF 2.0 and Java EE 6 in a sample Store Catalog Application

The image below shows the Catalog Listing page, which allows a user to page through a list of items in a store.



JSF 2.0 Facelets XHTML instead of JSP

For JSF 2.0, Facelets XHTML is the preferred way to declare JSF Web Pages. JSP is supported for backwards compatibility, but not all JSF 2.0 features will be available for views using JSP as their page declaration language.  JSF 2.0 Facelets has some nice features like templating (similar in functionality to Tiles) and composite components, which I'm not going to discuss here but you can read about that in this article: http://www.ibm.com/developerworks/java/library/j-jsf2fu2/index.html and in this Tech Tip Composite UI Components in JSF 2.0.


The Catalog application's resources


JSF 2.0 standardizes how to define web resources. Resources are any artifacts that a component may need in order to be rendered properly -- images, CSS, or JavaScript files.  With JSF 2.0 you put resources in a resources directory or a subdirectory.


In your Facelets pages, you can access css files with the  <h:outputStylesheet>,  javascript files with the <h:outputScript> , and images with the <h:graphicImage> JSF tags. The list.xhtml uses the  <h:outputStylesheet tag to load the styles.css stylesheet , and the <h:graphicImage tag to display images from the resources as shown below:

Code Sample from:  list.xhtml

<h:outputStylesheet name="css/styles.css" target="body"/>

<h:graphicImage library="images" name="banner_logo.gif"  />   




The Catalog application uses a resource bundle to contain the static text and error messages used by the Facelets pages. Putting messages in a resource bundle makes it easier to modify and internationalize your Application text.  The messages are in a properties file in a java package directory.

Code Sample from:  messages.properties 

Title=Pet Catalog
Next=Next
Previous=Prev
Name=Name



The resource bundle is configured in the faces-config.xml File (you don't need any other configuration in the faces-config.xml for JSF 2.0, as explained later you no longer have to configure managed beans and navigation with XML).


Code Sample from:  faces-config.xml

<application>
    <resource-bundle>
        <base-name>web.WebMessages</base-name>
        <var>msgs</var>
    </resource-bundle>
</application>




The List.xhtml facelets page uses a JSF dataTable component to display a list of catalog items in an html table.  The dataTable component is useful when you want to show a set of results in a table. In a JavaServer Faces application, the UIData component (the superclass of dataTable)  supports binding to a collection of data objects. It does the work of iterating over each record in the data source. The HTML dataTable renderer displays the data as an HTML table.

In the list.xhtml web page the dataTable is defined as shown below:  (Note: Red colors are for Java EE tags, annotations code,  and Green is for my code or variables)

Code Sample from:  list.xhtml

<h:dataTable value='#{catalog.items}' var='row' border="1"
      cellpadding="2" cellspacing="0">



The value attribute of a dataTable tag references the data to be included in the table. The var attribute specifies a name that is used by the components within the dataTable tag as an alias to the data referenced in the value attribute of dataTable.  In the dataTable tag from the List.jsp page, the value attribute points to a list of catalog items. The var attribute points to a single item in that list. As the dataTable component iterates through the list, each reference to dataTableItem points to the current item in the list.

JSF 2.0 Annotations instead of XML configuration

The dataTable's value is bound to the items property of the catalog managed bean. With JSF 2.0 managed beans do not have to be configured in the faces-config.xml file, you annotate the managed beans instead as shown below:

Code Sample from: Catalog.java


@ManagedBean
@SessionScoped
public class Catalog implements Serializable {




By convention, the name of a managed bean is the same as the class name, with the first letter of the class name in lowercase. To specify a managed bean name you can use the name attribute of the ManagedBean annotation, like this: @ManagedBean(name = "Catalog").


This Catalog ManagedBean items property is defined as shown below:

Code Sample from: Catalog.java

    private List<Item> items = null;

    public List<Item> getItems() {
        if (items == null) {
            getPagingInfo();
            items = getNextItems(pagingInfo.getBatchSize(), pagingInfo.getFirstItem());
        }
        return items;
    }





The getItems() method returns a List of item objects. The JSF dataTable, supports data binding to a collection of data objects.  The dataTable object is modeled as a collection of row objects that can be accessed by a row index.  The APIs provide mechanisms to position to a specified row index, and to retrieve an object that represents the data that corresponds to the current row index.   

The Item properties name, imagethumburl, and price are displayed with the column component:

Code Sample from: list.xhtml

<h:dataTable var="row" value="#{catalog.items}">
  <h:column>
      <f:facet name="header">
          <h:outputText value="#{msgs.Name}"/>
      </f:facet>
      <h:commandLink action="#{catalog.showDetail(row)}" value="#{row.name}" />
  </h:column>

  <h:column>
      <f:facet name="header">
          <h:outputText value="#{msgs.Photo}"/>
      </f:facet>
<h:graphicImage library="images" name="#{row.imagethumburl}"/>
  </h:column>

  <h:column>
      <f:facet name="header">
          <h:outputText value="#{msgs.Price}"/>
      </f:facet>
      <h:outputText value="#{row.price}"/>
  </h:column>
</h:dataTable>


The column tags represent columns of data in a dataTable component. While the dataTable component is iterating over the rows of data, it processes the UIColumn component associated with each column tag for each row in the table.

The dataTable component  iterates through the list of items (catalog.items)  and displays the item (var="row") attribute value. Each time UIData iterates through the list of items, it renders one cell in each column.

The dataTable and column tags use facet to represent parts of the table that are not repeated or updated. These include headers, footers, and captions.

Java EE 6: JSF 2.0,  EJB 3.1, and Java Persistence API (JPA)  2.0

The Catalog ManagedBean annotates the field private ItemFacade itemFacade;  with @EJB , which causes an itemFacade EJB to be injected when the managed bean is instatiated. The Catalog getNextItems method calls the ItemFacade Stateless EJB which uses the Java Persistence API EntityManager Query object to return a list of items.


Code Sample from: Catalog.java

@ManagedBean
@SessionScoped
public class Catalog implements Serializable {


@EJB
    private ItemFacade itemFacade;

    public List<Item>  getNextItems(int maxResults, int firstResult) { 
       return itemFacade.findRange(maxResults, firstResult);    
   }


EJB 3.1 No-interface local client View

With EJB 3.1, local EJBs  do not  have to a implement separate interface, that is, all public methods of the bean class are automatically exposed to the caller.

Simplified Packaging

With Java EE 6, EJBs can be directly packaged in a WAR file just like web components.

The ItemFacade EJB uses the Java Persistence API EntityManager Query object to return a list of items. The ItemFacade EJB annotates the field private EntityManager em;  with @PersistenceContext , which causes an entity manager to be injected when it is instatiated.

Code Sample from: ItemFacade.java

@Stateless
public class ItemFacade {


@PersistenceContext(unitName = "catalogPU")
    private EntityManager em;

    public List<Item> findRange(int maxResults, int firstResult) {
Query q = em.createQuery("select object(o) from Item as o");
        q.setMaxResults(maxResults);
        q.setFirstResult(firstResult);
        return q.getResultList();
    }


The Java Persistence Query APIs are used to create and execute queries that can return a list of results.  The JPA Query interface provides support for pagination via the setFirstResult() and setMaxResults() methods: q.setMaxResults(int maxResult) sets the maximum number of results to retrieve. q.setFirstResult(int startPosition) sets the position of the first result to retrieve.

In the code below, we show the Item entity class which maps to the  ITEM table that stores the item instances. This is a typical Java Persistence entity object. There are two requirements for an entity:
  1. annotating the class with an @Entity annotation.
  2. annotating   the primary key identifier with @Id
Because the fields name, description.... are basic mappings from the object fields to columns of the same name in the database table, they don't have to be annotated.  The O/R  relationships with Address and Product are also annotated. For more information on defining JPA entities see Pro EJB 3: Java Persistence API book.

Code Sample from: Item.java

@Entity
public class Item implements java.io.Serializable {

@Id
    private Integer id;

    private String name;   
    private String description;   
    private String imageurl;   
    private String imagethumburl; 
    private BigDecimal price;
@ManyToOne
    private Address address;
@ManyToOne
    private Product product;


    public Item() { }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }


    ...
}   




The Catalog ManagedBean pages through the list of Items by maintaining the PagingInfo.firstItem and PagingInfo.batchSize attributes and passing these as parameters to the  getNextItems(firstItem, batchSize) method. The catalog's scope  is defined with the annotation @SessionScoped, a JSF Managedbean with session scope will be stored in the session meaning that the bean's properties will stay alive for the life of the Http Session.


A JSF commandButton is  used to provide a button to click on to display the next page of items.  The commandButton tag is used to submit an action event to the application. 

Code Sample from: list.xhtml

 <h:commandButton action="#{catalog.next}" value="#{msgs.Next}" />   


This commandButton action attribute references the catalog Managed bean next() method which calculates the next page's first row number  and returns a logical outcome String, which causes the list.xhtml page to display the next page's list . The catalog next method is defined as shown below:

Code Sample from: catalog.java

   public String next() {
       if (firstItem + batchSize < itemCount()) {
           firstItem += batchSize;
       }
       return "list";
   }


JSF 2.0 Simplified Navigation


The JavaServer Faces 2.0  NavigationHandler convention adds .xhtml to the logical outcome of the action method (in this example list) and loads that file, in this case, it loads the list.xhtml page after this method returns. If the action doesn't begin with a forward slash (/), JSF assumes that it's a relative path.  You can specify an absolute path by adding the slash like this "/items/list".


A JSF commandLink is  used to provide a link to click on to display a page with the item details. This commandLink action attribute  references The catalog showDetail() method:

Code Sample from: list.xhtml

   <h:column>
       <f:facet name="header">
          <h:outputText value="Name"/>
       </f:facet>
       <h:commandLink action="#{catalog.showDetail(row)}" value="#{row.name}"/>   
   </h:column>


With JSF 2.0 you can now specify parameters in method expressions. The dataTable row object associated with the selected link is passed as a parameter in the  "#{catalog.showDetail(row)}" method expression.
The Catalog showDetail() method  gets the item data from the input parameter, and returns a string which causes the detail.xhtml page to display the item details :

Code Sample from: Catalog.java

    public String showDetail(Item item) {
        this.item = item;
        return "detail";
    }


The JavaServer Faces NavigationHandler adds .xhtml to the logical outcome of the action, detail and loads that file. In this case, the JavaServer Faces implementation loads the detail.xhtml page after this method returns.

The detail.xhtml uses the outputText component to display the catalog ManagedBean's item properties:

Code Sample from: detail.xhtml

    <h:outputText value="#{catalog.item.name}" title="Name" />
    <h:outputText value="#{catalog.item.description}" title="Description"/>
    <h:graphicImage library="images" name="#{catalog.item.imageurl}" title="Imageurl" />

    <h:outputText value="#{
catalog.item.price}" title="Price" />
    <h:outputText value="#{
catalog.item.address.city}" title="Address" />
    <h:outputText value="#{
catalog.item.contactinfo.email}" title="Address"/>  





GlassFish v3 is a lightweight server

  • OSGi-based; Embedded API; RESTful admin API; Lightweight and fast startup;
  • iterative development cycle "edit-save-refresh browser":
    • Incremental compile of all JSF 2.0 artifacts when you save.
    • Auto-deploy of all web or Java EE 6 artifacts
  • Session retention: maintain sessions across re-deployments


Conclusion
This concludes the sample application which demonstrates a pet catalog web application which uses Java EE 6, GlassFish v3 and MySQL.

Running the Sample Application

  1. Download and install NetBeans IDE 6.8 M1 with GlassFish v3 b57 (Glassfish v3 preview is Java EE 6 Preview) , and MySQL Community Server .
  2. Follow these instructions to set up a  jdbc-driver for MySQL. (Normally this is already setup with Glassfish, but I got an errror message with Glassfish v3 b57 that it was missing)
  3. Download the sample code. Unzip the catalog.zip file which you downloaded, this will create a catalog directory with the project code.

Create the Pet Catalog database

In order to run the sample code you first have to create the Pet Catalog database and fill in  the Item table.

  1. Start NetBeans IDE
  2. Ensure that GlassFish is registered in the NetBeans IDE, as follows:
    • Click the Services tab in the NetBeans IDE.
    • Expand the Servers node. You should see GlassFish v2 in the list of servers. If not, register GlassFish v2 as follows:
      • Right-click the Servers node and select Add Server. This opens an Add Server Instance wizard.
      • Select GlassFish v2 in the server list of the wizard and click the Next button.
      • Enter the location information for the server and click the Next button.
      • Enter the admin name and password and click the Finish button.
  3. Start the MySQL or Java DB database as follows:
    • Click the Services tab in the NetBeans IDE.
    • Expand the databases node. You should see the Java DB database in the list of databases. If you have installed the MySQL server database, you should also see the MySQL database in the list of databases.. Note:  Java DB  comes bundled with Netbeans, you can  download MySQL separately.

    • Right-mouse click on the Java DB or MySQL server database and select Start.
  4. If you installed MySQL, set the properties of the MySQL server database as follows:
    • Right-click on the MySQL server database and select Properties. This opens the MySQL Server Properties dialog box, as shown in Figure 8.

      MySQL Server Basic Properties
      Figure 8. MySQL Server Basic Properties

    • In the Basic Properties tab, enter the server host name and port number. The IDE specifies localhost as the default server host name and 3306 as the default server port number.
    • Enter the administrator user name, if not displayed, and the administrator password -- the default administrator password is blank.
    • Click the Admin Properties tab.
    • Enter an appropriate path in the Path/URL to admin tool field. You can find the path by browsing to the location of a MySQL Administration application such as the MySQL Admin Tool.
    • Enter an appropriate path in the Path to start command. You can find the path by browsing to the location of the MySQL start command. To find the start command, look for mysqld in the bin folder of the MySQL installation directory.
    • Enter an appropriate path in the Path to stop command field. You can find the path by browsing to the location of the MySQL stop command. This is usually the path to mysqladmin in the bin folder of the MySQL installation directory. If the command is mysqladmin, in the Arguments field, type -u root stop to grant root permissions for stopping the server. The Admin Properties tab should look similar to Figure 9.

      MySQL Server Administration Properties
      Figure 9. MySQL Server Administration Properties

    • Click the OK button.

  5. Right-click on the MySQL server or Java DB database and select Start.
  6. Create the petcatalog database as follows:
    • Right-mouse click on the Java DB or MySQL server database and select Create Database. This will open a create Database window.
    • Enter the database name catalog for Java DB or petcatalog for MySQL.


      For Java DB enter userid password app app as shown below:


       Click O.K. to accept the displayed settings.
  7. Create the tables in the catalog database as follows:
    • Underneath Databases you should see a database connection for the petcatalog database. For example MySQL:

      or Java DB:

    • Right-mouse click on the petcatalog connection and select Connect.
    • Right-mouse click on the petcatalog connection and select Execute Command. This will open up a SQL command window.
    • Copy the contents of the catalog.sql file in the catalog directory and paste the contents into the SQL command window, as shown in below:

      Creating Tables in the Database
    • Click the Run SQL icon Run SQL icon (Ctrl+Shift+E) above the SQL command window.
    • Note: It is ok to see this: "Error code -1, SQL state 42Y55: 'DROP TABLE' cannot be performed on 'ITEM' because it does not exist. Line 2, column 1" . This just means you are deleting a table that does not exist.  If you need to delete and recreate the tables you will not  see this message the second time.
  8. View the data in the Pet Catalog database Item table as follows:
    • Underneath Databases you should see a database connection for the petcatalog database. For example MySQL:

      or Java DB:

    • If the database connection is broken like in the following diagram:

      • Right-mouse click on the petcatalog connection and select Connect. as shown below:

      • if prompted for a password, for MySQL leave it blank, for JavaDB enter user app password app.
    • Expand the Tables node below the petcatalog database in the Services window. You should see the item table under the Tables node. You can expand the item table node to see the table columns, indexes, and any foreign keys, as shown in below :
      An Expanded Table Node
      Figure 12. An Expanded Table Node

      You can view the contents of a table or column by right-clicking the table or column and selecting View Data as shown  below:

      Viewing the Contents of a Table
      Figure 13. Viewing the Contents of a Table


  9. Follow these instructions to Create a JDBC Connection pool and JDBC resource. Name the  pool mysql_petcatalog_rootPool and the jndi resource jdbc/petcatalog. Note: you do not have to create a JDBC connection pool and resource if you use the Netbeans wizard to generate JPA entities from database tables as described in this article GlassFish and MySQL, Part 2: Building a CRUD Web Application With Data Persistence.
  10. Open the catalog/setup/sun-resources.xml file and verify that the property values it specifies match those of the petcatalog database and jdbc resources you created. Edit the property values as necessary.

Running the Sample solution:

If you want to run the sample solution, you have to create the catalog database tables first as described above.

  1. Open the catalog project as follows:
    • In NetBeans IDE, click Open Project in the File menu. This opens the Open Project dialog.
    • Navigate in the Open Project dialog to the catalog  directory and click the Open Project button.

    In response, the IDE opens the catalog project.  You can view the logical structure of the project in the Projects window (Ctrl-1).
  2. Run the catalog by right-clicking on the catalog project in the Projects window and selecting Run Project. The NetBeans IDE compiles the application, deploys it on Glassfish, and brings up the default page in your browser.  (at http://localhost:8080/catalog/).

For more information see the following resources:



Thursday Jul 30, 2009

JPA 2.0 Concurrency and locking

Optimistic Concurrency


Optimistic locking lets concurrent transactions process simultaneously, but detects and prevent collisions, this works best for applications where most concurrent transactions do not conflict. JPA Optimistic locking allows anyone to read and update an entity, however a version check is made upon commit and an exception is thrown if the version was updated in the database since the entity was read.  In JPA for Optimistic locking you annotate an attribute with @Version as shown below:

public class Employee {
    @ID int id;
@Version int version;

The Version attribute will be incremented with a successful commit. The Version attribute can be an int, short, long, or timestamp.  This results in SQL like the following:

“UPDATE Employee SET ..., version = version + 1
     WHERE id = ? AND version = readVersion

The advantages of optimistic locking are that no database locks are held which can give better scalability. The disadvantages are that the user or application must refresh and retry failed updates.

Optimistic Locking Example


In the optimistic locking example below, 2 concurrent transactions are updating employee e1. The transaction on the left commits first causing the e1 version attribute to be incremented with the update. The transaction on the right throws an OptimisticLockException because the e1 version attribute is higher than when e1 was read, causing the transaction to roll back.
img60.jpg

Additional Locking with JPA Entity Locking APIs


With JPA it is possible to lock an entity, this allows you to control when, where and which kind of locking to use. JPA 1.0 only supported Optimistic read or Optimistic write locking.  JPA 2.0 supports Optimistic and Pessimistic locking, this is layered on top of @Version checking described above.

JPA 2.0 LockMode values :
  • OPTIMISTIC (JPA 1.0 READ):
    • perform a version check on locked Entity before commit, throw an OptimisticLockException if Entity version mismatch.
  • OPTIMISTIC_FORCE_INCREMENT (JPA 1.0 WRITE)
    • perform a version check on locked Entity before commit, throw an OptimisticLockException if Entity version mismatch, force an increment to the version at the end of the transaction, even if the entity is not modified.
  • PESSIMISTIC:
    • lock the database row when reading
  • PESSIMISTIC_FORCE_INCREMENT
    • lock the database row when reading, force an increment to the version at the end of the transaction, even if the entity is not modified.
There are multiple APIs to specify locking an Entity:
  • EntityManager methods: lock, find, refresh
  • Query methods: setLockMode 
  • NamedQuery annotation: lockMode element

OPTIMISTIC (READ) LockMode Example


In the optimistic locking example below,  transaction1 on the left updates the department name for dep , which causes dep's version attribute to be incremented. Transaction2 on the right gives an employee a raise if he's in the "Eng" department. Version checking on the employee attribute would not throw an exception in this example since it was the dep Version attribute that was updated in transaction1. In this example the employee change should not commit if the department was changed after reading, so an OPTIMISTIC lock is used : em.lock(dep, OPTIMISTIC).  This will cause a version check on the  dep Entity before committing transaction2  which will throw an OptimisticLockException because the dep version attribute is higher than when dep was read, causing the transaction to roll back.
img62.jpg

OPTIMISTIC_FORCE_INCREMENT (write) LockMode Example


In the OPTIMISTIC_FORCE_INCREMENT locking example below,  transaction2 on the right wants to be sure that the dep name does not change during the transaction, so transaction2 locks the dep Entity em.lock(dep, OPTIMISTIC_FORCE_INCREMENT) and then calls em.flush() which causes dep's version attribute to be incremented in the database. This will cause any parallel updates to dep  to throw an OptimisticLockException and roll back. In transaction1 on the left at commit time when the dep version attribute is checked and found to be stale, an OptimisticLockException is thrown
img63.jpg

Pessimistic Concurrency

Pessimistic concurrency locks the database row when data is read, this is the equivalent of a (SELECT . . . FOR UPDATE [NOWAIT]) .  Pessimistic locking ensures that transactions do not update the same entity at the same time, which can simplify application code, but it limits concurrent access to the data which can cause bad scalability and may cause deadlocks. Pessimistic locking is better for applications with a higher risk of contention among concurrent transactions.
The examples below show:
  1. reading an entity and then locking it later
  2. reading an entity with a lock
  3. reading an entity, then later refreshing it with a lock

The Trade-offs are the longer you hold the lock the greater the risks of bad scalability and deadlocks. The later you lock the greater the risk of stale data, which can then cause an optimistic lock exception, if the entity was updated after reading but before locking.
img66.jpg
img672.jpg
The right locking approach depends on your application:
  • what is the risk of risk of contention among concurrent transactions?
  • What are the requirements for scalability?
  • What are the requirements for user re-trying on failure?

References and More Information:

Preventing Non-Repeatable Reads in JPA Using EclipseLink
Java Persistence API 2.0: What's New ?
What's New and Exciting in JPA 2.0
Beginning Java™ EE 6 Platform with GlassFish™ 3
Pro EJB 3: Java Persistence API (JPA 1.0)

Java Persistence API: Best Practices and Tips





Friday Jul 24, 2009

JSF 2.0, JPA, GlassFish and MySQL

JSF 2.0, JPA, GlassFish and MySQL

JSF 2.0, JPA, GlassFish and MySQL


This Pet Catalog app explains a web application that uses JSF 2.0, JPA, GlassFish and MySQL. I took this example  GlassFish and MySQL, Part 2: Building a CRUD Web Application With Data Persistence and modified it to use some of the new features JSF 2.0. 

Explanation of the usage of JSF 2.0, Java Persistence APIs, Glassfish and MySQL in a sample Store Catalog Application

The image below shows the Catalog Listing page, which allows a user to page through a list of items in a store.



JSF 2.0 Facelets XHTML instead of JSP

For JSF 2.0, Facelets XHTML is the preferred way to declare JSF Web Pages. JSP is supported for backwards compatibility, but not all JSF 2.0 features will be available for views using JSP as their page declaration language.  JSF 2.0 Facelets has some nice features like templating (similar in functionality to Tiles) and composite components, which I'm not going to discuss here but you can read about that in this article: http://www.ibm.com/developerworks/java/library/j-jsf2fu2/index.html and in this Tech Tip Composite UI Components in JSF 2.0.


The Catalog application's resources


JSF 2.0 standardizes how to define web resources. Resources are any artifacts that a component may need in order to be rendered properly -- images, CSS, or JavaScript files.  With JSF 2.0 you put resources in a resources directory or a subdirectory.


In your Facelets pages, you can access css files with the  <h:outputStylesheet>,  javascript files with the <h:outputScript> , and images with the <h:graphicImage> JSF tags. The list.xhtml uses the  <h:outputStylesheet tag to load the styles.css stylesheet , and the <h:graphicImage tag to display images from the resources as shown below:

Code Sample from:  list.xhtml

<h:outputStylesheet name="css/styles.css" target="body"/>

<h:graphicImage library="images" name="banner_logo.gif"  />   




The Catalog application uses a resource bundle to contain the static text and error messages used by the Facelets pages. Putting messages in a resource bundle makes it easier to modify and internationalize your Application text.  The messages are in a properties file in a java package directory.

Code Sample from:  messages.properties 

Title=Pet Catalog
Next=Next
Previous=Prev
Name=Name



The resource bundle is configured in the faces-config.xml File (you don't need any other configuration in the faces-config.xml for JSF 2.0, as explained later you no longer have to configure managed beans and navigation with XML).


Code Sample from:  faces-config.xml

<application>
    <resource-bundle>
        <base-name>web.WebMessages</base-name>
        <var>msgs</var>
    </resource-bundle>
</application>




The List.xhtml facelets page uses a JSF dataTable component to display a list of catalog items in an html table.  The dataTable component is useful when you want to show a set of results in a table. In a JavaServer Faces application, the UIData component (the superclass of dataTable)  supports binding to a collection of data objects. It does the work of iterating over each record in the data source. The HTML dataTable renderer displays the data as an HTML table.

In the list.xhtml web page the dataTable is defined as shown below:  (Note: Red colors are for Java EE tags, annotations code,  and Green is for my code or variables)

Code Sample from:  list.xhtml

<h:dataTable value='#{catalog.items}' var='row' border="1"
      cellpadding="2" cellspacing="0">



The value attribute of a dataTable tag references the data to be included in the table. The var attribute specifies a name that is used by the components within the dataTable tag as an alias to the data referenced in the value attribute of dataTable.  In the dataTable tag from the List.jsp page, the value attribute points to a list of catalog items. The var attribute points to a single item in that list. As the dataTable component iterates through the list, each reference to dataTableItem points to the current item in the list.

JSF 2.0 Annotations instead of XML configuration

The dataTable's value is bound to the items property of the catalog managed bean. With JSF 2.0 managed beans do not have to be configured in the faces-config.xml file, you annotate the managed beans instead as shown below:

Code Sample from: Catalog.java


@ManagedBean
@SessionScoped
public class Catalog implements Serializable {




By convention, the name of a managed bean is the same as the class name, with the first letter of the class name in lowercase. To specify a managed bean name you can use the name attribute of the ManagedBean annotation, like this: @ManagedBean(name = "Catalog").


This Catalog ManagedBean items property is defined as shown below:

Code Sample from: Catalog.java

    private DataModel items = null;

    public DataModel getItems() {
        if (items == null) {
            getPagingInfo();
            items = new ListDataModel(getNextItems(pagingInfo.getBatchSize(), pagingInfo.getFirstItem()));
        }
        return items;
    }





The getItems() method wraps a List of item objects in a DataModel. UIData, the superclass of dataTable, supports data binding to a collection of data objects represented by a DataModel instance.  The data collection underlying a DataModel instance is modeled as a collection of row objects that can be accessed by a row index.  The APIs provide mechanisms to position to a specified row index, and to retrieve an object that represents the data that corresponds to the current row index.   

The Item properties Name, Photo, and price are displayed with the column component:

Code Sample from: list.xhtml

<h:dataTable var="row" value="#{catalog.items}">
  <h:column>
      <f:facet name="header">
          <h:outputText value="#{msgs.Name}"/>
      </f:facet>
      <h:outputText value="#{row.name}"/>
  </h:column>

  <h:column>
      <f:facet name="header">
          <h:outputText value="#{msgs.Photo}"/>
      </f:facet>
<h:graphicImage library="images" name="#{row.imagethumburl}"/>
  </h:column>

  <h:column>
      <f:facet name="header">
          <h:outputText value="#{msgs.Price}"/>
      </f:facet>
      <h:outputText value="#{row.price}"/>
  </h:column>
</h:dataTable>


The column tags represent columns of data in a UIData component. While the UIData component is iterating over the rows of data, it processes the UIColumn component associated with each column tag for each row in the table.

The UIData component  iterates through the list of items (catalog.items)  and displays the row.price. Each time UIData iterates through the list of items, it renders one cell in each column.

The dataTable and column tags use facet to represent parts of the table that are not repeated or updated. These include headers, footers, and captions.

Using the Java Persistence API (JPA) with JSF

The Catalog ManagedBean uses the Java Persistence API EntityManager Query object to return a list of items. The Catalog ManagedBean annotates the field private EntityManager em;  with @PersistenceContext , which causes an entity manager to be injected when the managed bean is instatiated.


Code Sample from: Catalog.java

@ManagedBean
@SessionScoped
public class Catalog implements Serializable {


@PersistenceUnit(unitName = "catalogPU")
    private EntityManagerFactory emf;

    private EntityManager getEntityManager() {
        return emf.createEntityManager();
    }

    public List<Item>  getNextItems(int maxResults, int firstResult) { 
       EntityManager em = getEntityManager();
       try {    
Query q = em.createQuery("select object(o) from Item as o");
         q.setMaxResults(maxResults);
         q.setFirstResult(firstResult);
         return q.getResultList();
        } finally {
            em.close();
        }      
   }


The Java Persistence Query APIs are used to create and execute queries that can return a list of results.  The JPA Query interface provides support for pagination via the setFirstResult() and setMaxResults() methods: q.setMaxResults(int maxResult) sets the maximum number of results to retrieve. q.setFirstResult(int startPosition) sets the position of the first result to retrieve.

In the code below, we show the Item entity class which maps to the  ITEM table that stores the item instances. This is a typical Java Persistence entity object. There are two requirements for an entity:
  1. annotating the class with an @Entity annotation.
  2. annotating   the primary key identifier with @Id
Because the fields name, description.... are basic mappings from the object fields to columns of the same name in the database table, they don't have to be annotated.  The O/R  relationships with Address and Product are also annotated. For more information on defining JPA entities see Pro EJB 3: Java Persistence API book.

Code Sample from: Item.java

@Entity
public class Item implements java.io.Serializable {

@Id
    private Integer id;

    private String name;   
    private String description;   
    private String imageurl;   
    private String imagethumburl; 
    private BigDecimal price;
@ManyToOne
    private Address address;
@ManyToOne
    private Product product;


    public Item() { }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }


    ...
}   




The Catalog ManagedBean pages through the list of Items by maintaining the PagingInfo.firstItem and PagingInfo.batchSize attributes and passing these as parameters to the  getNextItems(firstItem, batchSize) method. The catalog's scope  is defined with the annotation @SessionScoped, a JSF Managedbean with session scope will be stored in the session meaning that the bean's properties will stay alive for the life of the Http Session.


A JSF commandButton is  used to provide a button to click on to display the next page of items.  The commandButton tag is used to submit an action event to the application. 

Code Sample from: list.xhtml

 <h:commandButton action="#{catalog.next}" value="#{msgs.Next}" />   


This commandButton action attribute references the catalog Managed bean next() method which calculates the next page's first row number  and returns a logical outcome String, which causes the list.xhtml page to display the next page's list . The catalog next method is defined as shown below:

Code Sample from: catalog.java

   public String next() {
       if (firstItem + batchSize < itemCount()) {
           firstItem += batchSize;
       }
       return "list";
   }


JSF 2.0 Simplified Navigation


The JavaServer Faces 2.0  NavigationHandler convention adds .xhtml to the logical outcome of the action method (in this example list) and loads that file, in this case, it loads the list.xhtml page after this method returns. If the action doesn't begin with a forward slash (/), JSF assumes that it's a relative path.  You can specify an absolute path by adding the slash like this "/items/list".


A JSF commandLink is  used to provide a link to click on to display a page with the item details. This commandLink action attribute  references The catalog getDetail() method:

Code Sample from: list.xhtml

   <h:column>
       <f:facet name="header">
          <h:outputText value="Name"/>
       </f:facet>
       <h:commandLink action="#{catalog.getDetail}" value="#{row.name}"/>   
   </h:column>


The catalog getDetail() method  gets the item data from the current row of the dataModel, and returns a string which causes the detail.xhtml page to display the item details :

Code Sample from: Catalog.java

    public String getDetail() {
        item = (Item) model.getRowData();
        return "detail";
    }


The JavaServer Faces NavigationHandler adds .xhtml to the logical outcome of the action, detail and loads that file. In this case, the JavaServer Faces implementation loads the detail.xhtml page after this method returns.

The detail.xhtml uses the outputText component to display the catalog ManagedBean's item properties:

Code Sample from: detail.xhtml

    <h:outputText value="#{catalog.item.name}" title="Name" />
    <h:outputText value="#{catalog.item.description}" title="Description"/>
    <h:graphicImage library="images" name="#{catalog.item.imageurl}" title="Imageurl" />

    <h:outputText value="#{
catalog.item.price}" title="Price" />
    <h:outputText value="#{
catalog.item.address.city}" title="Address" />
    <h:outputText value="#{
catalog.item.contactinfo.email}" title="Address"/>  





Hot Deployment and Session Retention with JSF 2.0 and Glassfish

  • Incremental compile of all JSF 2.0  artifacts when you save.
  • Auto-deploy of all web or Java EE 6 artifacts
  • Session retention: maintain stateful sessions across re-deployments


Conclusion
This concludes the sample application which demonstrates a pet catalog web application which uses JSF 2.0, JPA, GlassFish and MySQL.

Running the Sample Application

  1. If you haven't already done so, download and install NetBeans IDE , GlassFish , and MySQL Community Server . You can download and install GlassFish with NetBeans as a single bundle.
  2. Follow these instructions to install JSF Mojarra 2.0.0 Beta2 on GlassFish v2 or Glassfish v3
  3. Download the sample code.

Create the Pet Catalog database

In order to run the sample code you first have to create the Pet Catalog database and fill in  the Item table.

  1. Start NetBeans IDE
  2. Ensure that GlassFish is registered in the NetBeans IDE, as follows:
    • Click the Services tab in the NetBeans IDE.
    • Expand the Servers node. You should see GlassFish v2 in the list of servers. If not, register GlassFish v2 as follows:
      • Right-click the Servers node and select Add Server. This opens an Add Server Instance wizard.
      • Select GlassFish v2 in the server list of the wizard and click the Next button.
      • Enter the location information for the server and click the Next button.
      • Enter the admin name and password and click the Finish button.

  3. Start the MySQL or Java DB database as follows:
    • Click the Services tab in the NetBeans IDE.
    • Expand the databases node. You should see the Java DB database in the list of databases. If you have installed the MySQL server database, you should also see the MySQL database in the list of databases.. Note:  Java DB  comes bundled with Netbeans, you can  download MySQL separately.

    • Right-mouse click on the Java DB or MySQL server database and select Start.
  4. If you installed MySQL, set the properties of the MySQL server database as follows:
    • Right-click on the MySQL server database and select Properties. This opens the MySQL Server Properties dialog box, as shown in Figure 8.

      MySQL Server Basic Properties
      Figure 8. MySQL Server Basic Properties

    • In the Basic Properties tab, enter the server host name and port number. The IDE specifies localhost as the default server host name and 3306 as the default server port number.
    • Enter the administrator user name, if not displayed, and the administrator password -- the default administrator password is blank.
    • Click the Admin Properties tab.
    • Enter an appropriate path in the Path/URL to admin tool field. You can find the path by browsing to the location of a MySQL Administration application such as the MySQL Admin Tool.
    • Enter an appropriate path in the Path to start command. You can find the path by browsing to the location of the MySQL start command. To find the start command, look for mysqld in the bin folder of the MySQL installation directory.
    • Enter an appropriate path in the Path to stop command field. You can find the path by browsing to the location of the MySQL stop command. This is usually the path to mysqladmin in the bin folder of the MySQL installation directory. If the command is mysqladmin, in the Arguments field, type -u root stop to grant root permissions for stopping the server. The Admin Properties tab should look similar to Figure 9.

      MySQL Server Administration Properties
      Figure 9. MySQL Server Administration Properties

    • Click the OK button.

  5. Right-click on the MySQL server or Java DB database and select Start.
  6. Create the petcatalog database as follows:
    • Right-mouse click on the Java DB or MySQL server database and select Create Database. This will open a create Database window.
    • Enter the database name catalog for Java DB or petcatalog for MySQL.


      For Java DB enter userid password app app as shown below:


       Click O.K. to accept the displayed settings.
  7. Create the tables in the catalog database as follows:
    • Underneath Databases you should see a database connection for the petcatalog database. For example MySQL:

      or Java DB:

    • Right-mouse click on the petcatalog connection and select Connect.
    • Right-mouse click on the petcatalog connection and select Execute Command. This will open up a SQL command window.
    • Copy the contents of the catalog.sql file in the riapetcatalog\\exercises\\exercise0 directory and paste the contents into the SQL command window, as shown in below:

      Creating Tables in the Database
    • Click the Run SQL icon Run SQL icon (Ctrl+Shift+E) above the SQL command window.
    • Note: It is ok to see this: "Error code -1, SQL state 42Y55: 'DROP TABLE' cannot be performed on 'ITEM' because it does not exist. Line 2, column 1" . This just means you are deleting a table that does not exist.  If you need to delete and recreate the tables you will not  see this message the second time.
  8. View the data in the Pet Catalog database Item table as follows:
    • Underneath Databases you should see a database connection for the petcatalog database. For example MySQL:

      or Java DB:

    • If the database connection is broken like in the following diagram:

      • Right-mouse click on the petcatalog connection and select Connect. as shown below:

      • if prompted for a password, for MySQL leave it blank, for JavaDB enter user app password app.
    • Expand the Tables node below the petcatalog database in the Services window. You should see the item table under the Tables node. You can expand the item table node to see the table columns, indexes, and any foreign keys, as shown in below :
      An Expanded Table Node
      Figure 12. An Expanded Table Node

      You can view the contents of a table or column by right-clicking the table or column and selecting View Data as shown  below:

      Viewing the Contents of a Table
      Figure 13. Viewing the Contents of a Table


  9. Follow these instructions to Create a JDBC Connection pool and JDBC resource.Name the  pool mysql_petcatalog_rootPool and the jndi resource jdbc/petcatalog. Note: you do not have to create a JDBC connection pool and resource if you use the Netbeans wizard to generate JPA entities from database tables as described in this article GlassFish and MySQL, Part 2: Building a CRUD Web Application With Data Persistence.

Running the Sample solution:

If you want to run the sample solution, you have to create the catalog database tables first as described above.

  1. If you haven't already download the sample code and start the NetBeans IDE. Unzip the catalog.zip file which you downloaded, this will create a catalog directory with the project code.
  2. Open the catalog/setup/sun-resources.xml file and verify that the property values it specifies match those of the petcatalog database and jdbc resources you created. Edit the property values as necessary.

  3. Open the catalog project as follows:
    • In NetBeans IDE, click Open Project in the File menu. This opens the Open Project dialog.
    • Navigate in the Open Project dialog to the catalog  directory and click the Open Project button.

    In response, the IDE opens the catalog project.  You can view the logical structure of the project in the Projects window (Ctrl-1).
  4. Run the catalog by right-clicking on the catalog project in the Projects window and selecting Run Project. The NetBeans IDE compiles the application, deploys it on Glassfish, and brings up the default page in your browser.  (at http://localhost:8080/catalog/).

For more information see the following resources:



Tuesday Sep 30, 2008

Sleepless in Brazil


Since September 24th I've been speaking in Brazil at JUGs, Universitys, and at Sun Tech Days as part of the September Month of Java in Brazil .

MapBRAZIL-Cities2.jpg


I spoke on September 24  in Maringa to ~500 JUG members/students/professors, September 25 in Toledo to ~ 500,  September 26 in  Xanxere to ~ 200, September 27 in Porto Alegre to ~70, and in Sao Paulo there were ~1000 attendees.   We spoke in a different location each evening which meant going to sleep late and waking up early the next morning to travel to the next location.  The guys organizing this obviously do not value sleep and time to run (I'm a avid runner)  as much as I do !   They also don't have the same conception of safety, our driver was going 90mph on a 2 lane road with oncoming traffic which didn't phase Bruno and Eduardo, whereas I was seriously praying to God for my life !! :)  The attendees in each city were very friendly and enthusiastic which compensated for the difficult schedule  (but next time I will insist on more time to travel between destinations!) .   Also we drove close to the beautiful  Iguaçu_Falls  and we flew through the beautiful Florianópolis  (note to self,  next time make sure to schedule a little time to rest and see noteworthy sites ;) .

During this trip I spoke on:

I learned a little about Brazil in conversations over dinner:
It was good that USA kicked out the Brits before we discovered gold, because the Portuguese took away a lot of Brazil's gold. USA has probably had more economic stability (until recently) because we always had a strong middle class, whereas in Brazil historically there was a big gap between the rich and poor with the wealth concentrated in a small percent of the population, however this has been improving recently
Brazil had really bad inflation during the 1970s, ~ 25% per month !  Brazillians told me they used to take their salary check and buy food for the whole month the same day.  But now the Brazillian inflation is under control, they paid off their debt,  and the economy is doing well. Last year they received more foreign investment than any other country.  
Last year Brazil made a huge oil discovery  which could raise Brazil's petroleum reserves by ~40 percent and boost Brazil into the ranks of the world's major exporters (drill baby drill) .  Brazillian cars are required to run on a gas ethanol  mixture and they have a very cost-effective process to produce ethanol from sugar cane. Brazil's sugar cane-based industry is far more efficient than the U.S. corn-based industry. Sugar cane ethanol has an energy balance 7 times greater than ethanol produced from corn , and Brazil's sugar cane based ethanol industry has not caused food prices to increase.
Bruno told me that he grew up near the amazon forest and used to swim in rivers with piranhas,  he said they don't bite unless you have a cut and are bleeding, can you imagine swimming with these things ?
piran.jpg

Here are some photos from my trip :

IMG_0868_1_1.JPG IMG_0871_1_1.JPGIMG_0878_1_1.JPG IMG_0885_1_1.JPGIMG_0886_1_1.JPG IMG_0888_1_1.JPG



Tuesday Sep 16, 2008

MySQL for Developers

this is a write up of info I gathered from   Jay PipesHigh Performance MySQL , Colin Charles and others (see References below) for a Sun Tech Days MySQL presentation.

MySQL powers many high-volume Web sites,  including industry leaders such as Yahoo!, Alcatel-Lucent, Google, Nokia, YouTube, and Zappos.com.

MySQL for Developers

If you are a developer using MySQL, you should learn enough to take advantage of its strengths, because having an understanding of the database can help you develop better-performing applications. This can be especially important for Hibernate or JPA developers, since ORM frameworks tend to obscure the database schema and SQL  for the developer, which can lead to poorly-performing index and schema strategies and inefficient SQL.

MySQL Storage Engine Architecture


A key difference between MySQL and other database platforms is the pluggable storage engine architecture of MySQL, which allows you to select a specialized storage engine for a particular application need such as data warehousing, transaction processing, high availability...
A storage engine is  responsible for storing and retrieving all the data stored .  The storage engines have different functionality, capabilities and performance characteristics, in many applications choosing the right storage engine can greatly improve performance. Here is a brief summary of some of the more common engines:
  • MyISAM - offers high-speed query and insert capability, is nontransactional, provides table-level locking, full-text indexing, compression, GIS functions, and supports indexes. MyISAM is a good choice for read-only or read-mostly applications that don't require transactions and issue primarily either SELECT or INSERT queries, such as Web Catalogs or listing of jobs, auctions, real estate, etc., or data warehousing applications. You can use multiple storage engines in a single application, sometimes specific components of an application (such as logging) fall into this category. MyISAM is not a good general purpose storage engine for high concurrent UPDATEs or DELETEs since these obtain exclusive write locks, however new rows can be inserted with shared read locks.
  • InnoDB - supports ACID transactions, multi-versioning, row-level locking, foreign key constraints, crash recovery,  and  good query performance depending on indexes. InnoDB uses row-level locking with multiversion concurrency control (MVCC). MVCC can allow fewer row locks by keeping data snapshots. Depending on the isolation level, InnoDB does not require any locking for a SELECT. This makes high concurrency possible, with some trade-offs: InnoDB requires more disk space compared to MyISAM, and for the best performance, lots of memory is required for the InnoDB buffer pool. InnoDB is a good choice for any order processing application, any application where transactions are required.  
  • Memory - stores all data in RAM for extremely fast access. Useful when you need fast access to data that doesn't change or doesn't need to persist after a restart.  Good for "lookup" or "mapping" tables, for caching the results of periodically aggregated data, for intermediate results when analyzing data.
    MEMORY tables do not support variable-length columns, this means that VARCHAR columns get implicitly converted to CHAR columns, and it is impossible to create a MEMORY table with TEXT or BLOB columns.
  • Merge - allows to logically group together a series of identical MyISAM tables and reference them as one object. Good for very large DBs like data warehousing.
  • Archive - provides for storing and retrieving large amounts of seldom-referenced historical, archived, or security audit information.
More specialized engines:
  • Federated - Allows a remote server's tables to be used as if they were local, by linking together separate MySQL servers as one logical database. Can be good for distributed or data mart environments. 
  • Cluster/NDB -Highly-available clustered storage engine.  Should be used where very high speed, availability and redundancy are absolute requirements. Other tables (even in the same database) should use one or more of the other storage engines.
  • CSV-references comma-separated files as database tables.Useful for large bulk imports or exports
  • Blackhole- the /dev/null storage engine for temporarily disabling application input to the database .  Useful for benchmarking and some replication scenarios.
You can use multiple storage engines in a single application, a storage engine for the same table on a slave can be different than that of the master. This can be very useful for taking advantage of an engine's abilities.

Schema, the basic foundation of performance

Database normalization minimizes duplication of information, this makes updates simpler and faster because the same information doesn't have to be updated in multiple tables.  In a denormalized database, information is duplicated, or stored in multiple places. With a normalized database:
  • updates are usually faster.
  • there's less data to change.
  • tables are usually smaller, use less memory, which can give better performance.
  • better performance for distinct or group by queries
The disadvantages of a normalized schema are queries typically involve more tables and require more joins which can reduce performance.  Also normalizing may place columns in different tables that would benefit from belonging to the same index, which can also reduce query performance.  More normalized schemas are better for applications involving many transactions, less normalized are better for reporting types of application.  You should normalize your schema first, then de-normalize later.  Applications often need to mix the approaches, for example use a partially normalized schema, and duplicate, or cache, selected columns from one table in another table.

Data Types

In general, try to use the smallest data type that you can.  Small and simple data types usually give better performance because it  means fewer disk accesses (less I/O), more data in memory, and less CPU to process operations.

Numeric Data Types

MySQL has 9 numeric data types.

Whole Numbers:
TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT:   require 8, 16, 24, 32, and 64 bits of storage space, respectively. They can store values from –2(n-1) to 2(n-1)–1, where N is the number of bits of storage space they use. 

Use UNSIGNED when you don't need negative numbers, this doubles the bits of storage space.  BIGINT is not needed for AUTO_INCREMENT,  INT UNSIGNED stores 4.3 billion values!

INT(1) does not mean 1 digit!  The number in parentheses is the ZEROFILL argument, and specifies the number of characters some tools reserve for display purposes. For storage and computational purposes, INT(1) is identical to INT(20).

Integer data types work best for primary key data types. 

Real Numbers
:
FLOAT, DOUBLE: supports approximate calculations with standard floating-point math.
DECIMAL: use DECIMAL when you need exact results, always use for monetary/currency fields.

Other:
BIT: to store  0,1 values. BIT(1) stores 1 bit, BIT(2) stores 2... max is 64.

Character Data Types

The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters to store.
VARCHAR(n)
stores variable-length character strings. VARCHAR uses only as much space as it needs, which helps performance because it saves disk space. Use when the maximum column length is larger than the average length and when updates to the field are rare, so fragmentation is not a problem.  VARCHAR gives more efficient disk usage, however MySQL internal memory buffers are fixed width, so  internal memory usage isn't more efficient, therefore try to keep VARCHAR Max Length short.
CHAR(n) is fixed-length: MySQL allocates enough space for the specified number of characters. Useful to store very short strings, when all the values are nearly the same length, and  for data that's changed frequently.

Use NOT NULL

Always define columns as NOT NULL unless there is a very good reason not to do so:
  • can save up to a byte per column per row of data
  • nullable columns make indexes, index statistics, and value comparisons more complicated.

Indexes

Indexes are data structures that help retrieve row data with specific column values faster. Indexes can especially improve performance for larger data bases.  Most MySQL storage engines support  B-tree indexes. a B-tree is (remember data strucutres 101 ?) a tree data structure that sorts data values, tree nodes define the upper and lower bounds of the values in the child nodes.  Leaf nodes have pointers to the data instead of pointers  to child nodes. B-trees are kept balanced by requiring that all leaf nodes are at the same depth.  MyISAM leaf nodes refer to the indexed row by the position of the row,  InnoDB leaf nodes refers to the index by its primary key values.

InnoDB's clustered indexes store the row data in the leaf nodes, it's called clustered because rows with close primary key values are stored close to each other.  This can make retrieving indexed data fast, since the data is in the index.  But this can be slower for updates , secondary indexes, and for full table scans.

Covering Indexes are indexes that contain all the data values needed for a query, these queries can improve performance because the row does not have to be read.

Know Every SQL Statement

You need to understand the SQL queries your application makes and  evaluate their performance. For this you can harness the MySQL slow query log and use EXPLAIN.  Consider adding an index for queries that are taking too long, or cause a lot of load. Know how your query is executed by MySQL, consider disk I/O in particular.  Basically you want to make your queries access less data, Indexes are a good way to reduce data access.

Know Every SQL Statement for Hibernate/JPA developers

If you are using Hibernate, JPA, or another ORM framework you want to look at the generated SQL-statements. If you are using Hibernate, enable the Hibernate SQL log.  If you are using Glassfish with Toplink as the JPA provider, you can log the SQL that is being sent to the database by adding this property in the definition of the persistence unit in the persistence.xml file, as follows: <property name="toplink.logging.level" value="FINE">. Then run a use case of your application and examine the SQL statements which are executed by JPA or your ORM framework. You want to make sure you are only retrieving the data your application needs, that is you want to optimize the number of SQL statements executed (see lazy loading below), and you want to examine the execution plan for queries.

Optimize data access

Basically you want to make your queries access less data:
  • is your application retrieving more data than it needs, are queries accessing too many rows or columns?
  • is MySQL analyzing more rows than it needs?

Understanding EXPLAIN

EXPLAIN provides the execution plan chosen by the MySQL optimiser for a specific SELECT statement.  It is important to run EXPLAIN on all SELECT statements that your code is executing against the database.  This step ensures that missing indexes are picked up early in the development process and gives developers insight into how the MySQL optimizer has chosen to execute the query.

To use it just precede a SELECT statement with the keyword EXPLAIN and  MySQL will display information from the optimizer about the query execution plan, which explains how it would process the SELECT,  including information about how tables are joined and in which order. The EXPLAIN EXTENDED SELECT variant, followed by SHOW WARNINGS gives some additional information, including the final rewritten query as used by the optimizer.

With the help of EXPLAIN, you can see where you should add indexes to tables to get a faster SELECT that uses indexes to find rows. You can also use EXPLAIN to check whether the optimizer joins the tables in an optimal order.

EXPLAIN
returns a row of information for each "table" used in the SELECT statement, which shows each part and the order of the execution plan.  The "table" can mean a real schema table, a derived or temporary table, a subquery, a union result. Here is an explanation of some of the output for each row:
  • table:  the real table name or an alias.
  • select_type: shows whether the row is a simple or complex SELECT. If complex it can be: PRIMARY (Outermost SELECT), UNION, SUBQUERY, DERIVED, or UNCACHEABLE.
  • type: shows the "access strategy" type to find rows. The different access types ordered from the best  to worst:
    • system, or const: very fast because  the table has at most one matching row (For example a primary key used in the WHERE)
    • eq_ref: index lookup returning one value
    • ref: index access
    • fulltext:  FULLTEXT index.
    • ref_or_null:  like ref, but with an extra search for rows that contain NULL values.
    • range:   index scan to select the rows in given range
    • index:   index tree is scanned.
    • ALL: A full table scan
  • key: the index that MySQL decided to use, from the possible_keys, to look up rows.
  • rows: the number of rows MySQL estimates it must examine to execute the query.
  • ref: columns or constants which are compared to key to select rows .
  • Extra: additional information about how MySQL resolves the query. Watch out for Extra values of Using filesort and Using temporaryUsing index means information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index (Covering Index).

When do you get a full table scan?

  • No WHERE condition
  • No index on any field in WHERE condition
  • When your range returns a large number of rows, i.e. too many records in WHERE condition
  • When optimizer sees that the condition will return > ~20% of the records in a table, it will prefer a scan versus many seeks
  • Pre-MySQL 5, using OR in a WHERE clause
    • now fixed with an index merge, so the optimiser can use more than one index to satisfy a join condition
How do you know if a scan is used?
In the EXPLAIN output, the “type” for the table/set will be “ALL” or “index”.  “ALL” means a full table data record scan is performed.  “index” means a full index record scan.  Avoid this by ensuring indexes are on columns that are used in the WHERE, ON, and GROUP BY clauses.

Scans and seeks

A seek jumps into a random place (on disk or in memory) to fetch data. A scan will jump to the start of the data, and sequentially read (from either disk or memory) until the end of the data. With large amounts of data sequentially scanning through contiguous data on disk or in memory is faster than performing many random seek operations.  But scans can also be a sign of poor indexing, if an index contains  many rows with the same value it is not very useful.  MySQL keeps stats about the  uniqueness of values in an index in order to the estimated rows returned (rows in the explain output). If the rows is greater than a certain  % of rows in the table, then MySQL will do a scan.

Indexed columns and functions don't mix

Indexes can quickly find the rows that match a WHERE clause, however this works only if the index is NOT used in a function or expression in the WHERE clause. Below is an example where an index is used :

mysql> EXPLAIN SELECT \* FROM film WHERE title LIKE 'Tr%'\\G
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
           id: 1
  select_type: SIMPLE
        table: film
type: range
possible_keys: idx_title
key: idx_title
      key_len: 767
          ref: NULL
         rows: 15
        Extra: Using where


In the example above, a  fast range "access strategy" is chosen by the optimizer, and the index scan on title is used to winnow the query results down.
Below is an example where an index can not be used :

mysql> EXPLAIN SELECT \* FROM film WHERE LEFT(title,2) = 'Tr' \\G
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
           id: 1
  select_type: SIMPLE
        table: film
type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 951
        Extra: Using where


A slow full table scan (the ALL"access strategy") is used because a function (LEFT) is operating on the title column. Operating on an indexed column with a function (in this case the LEFT() function) means the optimizer cannot use the index to satisfy the query.  Typically, you can rewrite queries in order to not operate on an indexed column with a function.

Solving multiple problems in one query

Here is an example of improving a query:

SELECT \* FROM Orders WHERE TO_DAYS(CURRENT_DATE()) – TO_DAYS(order_created) <= 7;

First, we are operating on an indexed column (order_created) with a function TO_DAYS – let's fix that:

SELECT \* FROM Orders WHERE order_created >= CURRENT_DATE() - INTERVAL 7 DAY;

Although we rewrote the WHERE expression to remove the function on the index, we still have a non-deterministic function CURRENT_DATE() in the statement, which eliminates this query from being placed in the query cache. Any time a non-deterministic function is used in a SELECT statement, the query cache ignores the query.  In read-intensive applications, this can be a significant performance problem. – let's fix that:

SELECT \* FROM Orders WHERE order_created >= '2008-01-11' - INTERVAL 7 DAY;

We replaced the function with a constant (probably using our application programming language).  However, we are specifying SELECT \* instead of the actual fields we need from the table.  What if there is a TEXT field in Orders called order_memo that we don't need to see?  Well, having it included in the result means a larger result set which may not fit into the query cache and may force a disk-based temporary table. – let's fix that:

SELECT order_id, customer_id, order_total, order_created
FROM Orders WHERE order_created >= '2008-01-11' - INTERVAL 7 DAY;


A Few Things to consider for  JPA/Hibernate  devlopers:

Lazy loading and JPA

With JPA many-to-one and many-to-many relationships lazy load by default , meaning they will be loaded when the entity in the relationship is accessed. Lazy loading is usually good, but if you need to access all of the "many" objects in a relationship, it will cause n+1 selects where n is the number of  "many" objects.  You can change the relationship to be loaded eagerly as follows :
public class Employee{

    @OneToMany(mappedBy = "employee", fetch = FetchType.EAGER)
    private Collection<Address> addresses;

 .....
}
However you should be careful with eager loading which could cause SELECT statements that fetch too much data. It can cause a Cartesian product  if you eagerly load entities with several related collections. 

If you want to temporarily override the LAZY fetch type, you could use Fetch Join.  For example this query would eagerly load the employee addresses: 
@NamedQueries({ @NamedQuery(name="getItEarly", 
                 query="SELECT e FROM Employee e JOIN FETCH e.addresses")})

public class Employee{
.....
}
 

Optimistic locking and JPA

The Java Persistence API 1.0 persistence providers support optimistic locking, where your application will get an exception whenever a transaction tries to commit an object that was updated in the database since this transaction began. To enable this for a entity, you need to add a version attribute:

@Entity
public class Item {
    ...
    @Version
    private int version;
    ...
}


In Conclusion

  • Understand the storage engines
  • Keep data types small and compact
  • Understand your query execution plans with the EXPLAIN output
  • Understand the scan vs. seek choice the optimizer must make
  • Don't mix functions and indexed columns

References

High Performance MySQL book
MySQL Pluggable Storage Engine Architecture
MySQL Storage Engine Architecture, Part 2: An In-Depth Look
Optimizing Queries with EXPLAIN
Java Persistence with Hibernate book
Jay Pipes blog
Colin Charles blog
mysql performance blog
Ronald Bradford blog
Taking JPA for a Test Drive
Pro EJB 3: Java Persistence API
Pro MySQL, Chapter 6: Benchmarking and Profiling

Thursday Aug 07, 2008

Sample Application using JSF, Seam 2.0, and Java Persistence APIs on Glassfish V2



This Sample Store Catalog app demonstrates the usage of JavaServer Faces, a Catalog Stateful Session Bean, the Java Persistence APIs, and Seam 2. I took this example  Sample Application using JSF, Catalog Facade Stateless Session, and Java Persistence APIs  and refactored it to use Seam on Glassfish by following the steps in Brian Leonards blog Seam Refresh  and the clickable list example in the Seam Tutorial

Download the Seam Sample Application Code

Explanation of the usage of JSF, Seam, and Java Persistence APIs in a sample Store Catalog Application

The image below shows the Catalog Listing page, which allows a user to page through a list of items in a store.

listpage.jpg

DataTable  JSF component

The List.jsp page uses a JSF dataTable component to display a list of catalog items.

The dataTable component is useful when you want to show a set of results in a table. In a JavaServer Faces application, the UIData component (the superclass of dataTable)  supports binding to a collection of data objects. It does the work of iterating over each record in the data source. The HTML dataTable renderer displays the data as an HTML table.

In the List.jsp web page the dataTable is defined as shown below:   (Note: Red colors are for Java EE tags, annotations code, Blue for Seam specific and Green for my code or variables)

Code Sample from:  List.jsp

<h:dataTable value='#{items}' var='dataTableItem' border="1"
      cellpadding="2" cellspacing="0">


The value attribute of a dataTable tag references the data to be included in the table. The var attribute specifies a name that is used by the components within the dataTable tag as an alias to the data referenced in the value attribute of dataTable.  In the dataTable tag from the List.jsp page, the value attribute points to a list of catalog items. The var attribute points to a single item in that list. As the UIData component iterates through the list, each reference to dataTableItem points to the current item in the list.

The dataTable's value is bound to the items attribute of the CatalogBean class:


Code Sample from: CatalogBean.java

@Stateful

@Scope(SESSION)
@Name("catalog")
@Interceptors({org.jboss.seam.ejb.SeamInterceptor.class})

public class CatalogBean implements Serializable, Catalog {

@DataModel
    private List<Item> items=null;

@Factory("items")
    public void getItems() {
        if ((items==null)  || (index != firstItem) ){
getNextItems();
        }
    }




The @DataModel Seam annotation exposes an attribute of type java.util.List to a JSF page as an instance of javax.faces.model.DataModel. The  <h:dataTable> supports data binding to a collection of data objects represented by a DataModel instance.  The data collection underlying a DataModel instance is modeled as a collection of row objects that can be accessed by a row index.  The APIs provide mechanisms to position to a specified row index, and to retrieve an object that represents the data that corresponds to the current row index.  In this case, the DataModel is made available in a session context variable named items.

When the List.jsp page is diplayed it will try to resolve the items context variable. Since this context variable is not initialized, Seam will call the @Factory method getItems(), which performs a JPA query (see getNextItems() code below) and results in a DataModel being outjected.  The @Factory annotation tells Seam to invoke the getItems() method to initialize the items value.

The @Name Seam annotation specifies catalog as the application unique component name which Seam will use to resolve references to the catalog context variable. Seam will instantiate the component and bind a new instance to the context variable the first time JSF encounters the variable name catalog. The instance will be bound to the context specified by the @Scope Seam annotation. The CatalogBean is a org.jboss.seam.ScopeType.SESSION scoped component. This means that the JSF components  can bind to the catalog  managed bean without configuring this in the faces-config.xml.

The @Stateful EJB 3.0 annotation marks this as a Stateful EJB. A Stateful EJB is used because the current chunk of items, and the user's position in the count of items in the db table, is maintained for the user's session.

The @Interceptors EJB 3.0 annotation registers the SeamInterceptor.class as an EJB interceptor for this session bean component.
The Seam framework uses  EJB interceptors to perform bijection, context demarcation, validation, etc, (the interceptor could be defined in the ejb-jar.xml instead).

Column JSF component

On the List.jsp page the Item Name, Photo, and Price  properties are displayed with the column component:

Code Sample from: List.jsp
<h:dataTable value='#{items}' var='dataTableItem' border="1"
      cellpadding="2" cellspacing="0">
...
  <h:column>
      <f:facet name="header">
          <h:outputText value="Price"/>
      </f:facet>
      <h:outputText value="#{dataTableItem.price}"/>
  </h:column>


The column tags represent columns of data in the dataTable component. While the dataTable component is iterating over the rows of data, it processes the column component associated with each column tag for each row in the table. As the dataTable component iterates through the list, each reference to dataTableItem points to the current item in the list.

The dataTable component  iterates through the list of items and displays the names, photos, and prices. Each time the dataTable iterates through the list of items, it renders one row in each column.

The dataTable and column tags use facets to represent rows of the table that are not repeated or updated. These include headers, footers, and captions.

Java Persistence Query API

The CatalogBean Session EJB uses the Java Persistence API Query object to return a list of items. With the @PersistenceContext annotation the CatalogBean uses dependency injection to lookup and obtain a Container Managed EntityManager . Since the EntityManager can be  container managed for EJB Session Beans, the application does not have to manage its lifecycle (i.e. call the EntityManagerFactory.create() and EntityManager.close() methods).

Code Sample from: CatalogBean.java

@DataModel
   private List<Item> items=null;

@PersistenceContext(unitName="PetCatalogPu")
   private EntityManager em;

   private int batchSize = 10;
   private int index = 0;
   private int firstItem = 0;

@TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
   public void getNextItems() {
Query q = em.createQuery("select object(o) from Item as o");
        q.setMaxResults(batchSize);
        q.setFirstResult(firstItem);
items= q.getResultList();
        index = firstItem;
   }


Since this query is used for Read-Only browsing, the transaction attribute in this example is specified as NOT_SUPPORTED. Queries using transaction-scoped entity managers outside of a transaction are typically more efficient than queries inside a transaction when the result type is an entity. 

The Java Persistence Query APIs are used to create and execute queries that can return a list of results.  The JPA Query interface provides support for pagination via the setFirstResult() and setMaxResults() methods: query.setMaxResults(int maxResult) sets the maximum number of results to retrieve. query.setFirstResult(int startPosition) sets the position of the first result to retrieve.

In the code below, we show the Item entity class which maps to the  ITEM table that stores the item instances. This is a typical Java Persistence entity object. There are two requirements for an entity:
  1. annotating the class with an @Entity annotation.
  2. annotating the primary key identifier with @Id
Because the fields name, description.... are basic mappings from the object fields to columns of the same name in the database table, they don't have to be annotated.  The O/R  relationships with Address and Product are also annotated. For more information on defining JPA entities see Pro EJB 3: Java Persistence API book.

Code Sample from: Item.java

@Entity
@Name("item")
@Scope(ScopeType.EVENT)

public class Item implements java.io.Serializable {

@Id
    private String itemID;

    private String name;   
    private String description;   
    private String imageurl;   
    private String imagethumburl; 
    private BigDecimal price;
@OneToOne(cascade={CascadeType.PERSIST})
    private Address address;
@ManyToOne
    private Product product;


    public Item() { }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }



    ...
}   



The @Name seam annotation specifies the (application unique) component name item, which is used in the  Detail.jsp to display the selected item's attributes. The @Scope Seam annotation binds the item instance to the org.jboss.seam.ScopeType.EVENT context.

The CatalogBean pages through the list of items by maintaining the firstItem and batchSize attributes and passing these as parameters to the query.setFirstResult(int startPosition), query.setMaxResults(int maxResult) methods. The CatalogBean's scope is defined as org.jboss.seam.ScopeType.SESSION, which corresponds to the JSF managed bean session scope.


The CatalogBean itemCount property is  used to get and display the number of Catologue items in the  data base:

Code Sample from: List.jsp

<h:outputText value="Item #{catalog.firstItem + 1}..#{catalog.lastItem} of
     #{catalog.itemCount}"/>



The CatalogBean getItemCount() method uses the JPA javax.persistence.Query interface to get the count of all items in the database item table:

Code Sample from: CatalogBean.java

private int itemCount = 0;

    public int getItemCount() {
Query q = entityManager.createQuery("select count(o) from Item as o");     
itemCount = ((Long)q.getSingleResult()).intValue();
        return
itemCount;
    }   



A JSF commandLink is  used to provide a link to click on to display the next page of items. The commandLink tag represents an HTML hyperlink and is rendered as an HTML <a> element. The commandLink tag is used to submit an action event to the application. 

Code Sample from: List.jsp

 <h:commandLink action="#{catalog.next}" value="Next #{catalog.batchSize}"
    rendered="#{catalog.lastItem + catalog.batchSize <= catalog.itemCount}"/>   


This commandLink action attribute references the CatalogBean next() method that calculates the next page's first row number  and returns a logical outcome String, which causes the List page to display the next page's list . This CatalogBean next() method is defined as shown below:

Code Sample from: CatalogBean.java

   public String next() {
       if (firstItem + batchSize < getItemCount()) {
           firstItem += batchSize;
           getNextItems();
       }
       return "item_list";
   }


The JavaServer Faces NavigationHandler matches the logical outcome,  item_list against the navigation rules in the application configuration resource file  faces-config.xml to determine which page to access next. In this case, the JavaServer Faces implementation loads the List.jsp page after this method returns.

Code Sample from: faces-config.xml

  <navigation-rule>
      <navigation-case>
          <from-outcome>item_list</from-outcome>
          <to-view-id>/item/List.jsp</to-view-id>
      </navigation-case>
  </navigation-rule>


A JSF commandLink is  used to provide a link to click on to display the previous page of items. This commandLink action attribute  references the  CatalogBean's prev() method that calculates the previous page's first row number  and returns a logical outcome String, which causes the List page to display the previous page of items :

Code Sample from: List.jsp

 <h:commandLink action="#{catalog.prev}" value="Previous #{catalog.batchSize}"   
        rendered="#{catalog.firstItem >=catalog.batchSize}"/>


 This CatalogBean prev() method  is defined as shown below: 

Code Sample from: CatalogBean.java

    public String prev() {
        firstItem -= batchSize;
        if (firstItem < 0) {
            firstItem = 0;
        }
        getNextItems();
        return "item_list";
    }     


A JSF commandLink is  used to provide a link to click on to display a page with the item details. This commandLink action attribute  references the CatalogBean select() method:

Code Sample from: List.jsp

   <h:column>
       <f:facet name="header">
          <h:outputText value="Name"/>
       </f:facet>
       <h:commandLink action="#{catalog.select}" value="#{dataTableItem.name}"/>   
   </h:column>


With Seam if you use the @DataModelSelection with the @DataModel annotation, when the user clicks on the link, Seam will propagate the selected row from the DataModel into the annotated attribute:

Code Sample from: CatalogBean.java

    @DataModelSelection
@Out(required=false)
    private Item item;

    public String select() {
        return "item_detail";
    }



The @DataModelSelection Seam annotation tells Seam to inject the DataModel List element corresponding to the clicked link into the item attribute.  The @Out Seam annotation transfers the value of this attribute to the item event context variable, making it available to a JSP page after the action catalog.select method execution. So when a row of the dataTable is selected, the selected row is injected to the item attribute of the CatalogBean Stateful bean, and then outjected to the event context variable named item which is used in the Detail.jsp page to display the item details.

The CatalogBean select() returns a string,  "item_detail", which causes the Detail.jsp page to display the item details. The JavaServer Faces NavigationHandler matches the logical outcome,  item_detail against the navigation rules in the application configuration resource file faces-config.xml to determine which page to access next. In this case, the JavaServer Faces implementation loads the Detail.jsp page after this method returns.

Code Sample from: faces-config.xml

    <navigation-rule>
        <navigation-case>
            <from-outcome>item_detail</from-outcome>
            <to-view-id>/item/Detail.jsp</to-view-id>
        </navigation-case>
    </navigation-rule>    



The Detail.jsp uses the outputText component to display the item properties:

Code Sample from:  Detail.jsp

    <h:outputText value="#{item.name}" title="Name" />
    <h:outputText value="#{
item.description}" title="Description"/>
    <h:graphicImage url="#{item.imageurl}" title="Imageurl" />
    <h:outputText value="#{item.price}" title="Price" />
    <h:outputText value="#{item.address.city}" title="Address" />
    <h:outputText value="#{item.contactinfo.email}" title="Address"/>  




detailpage.jpg


Conclusion
This concludes the sample application which demonstrates how to use Seam with the JSF dataTable and DataModel to page through a list of  Item Entities which are retrieved using  the CatalogBean Stateful Session EJB methods which use  the Java Persistence APIs.


Configuration of the Application for Seam 2.0, JSF, JPA, running on Glassfish V2

First I recommend reading Brian Leonard's blog Seam Refresh .  I will summarize and update those steps here:

To Open and Test Run the seampagination Project:

  • Use the Resolve Reference Problems dialog to map the ejb and web modules to their project, which are subdirectories beneath the seampagination directory.
  • After the references are resolved, right-click the seampagination project and select Open Required Projects.
  • Right-click the seampagination-EJBModule and select Resolve Reference Problems:
    • browse to the Seam lib directory and select jboss-seam.jar and select Open. This should resove the reference to the following jars: jboss-seam.jar, hibernate.jar, hibernate-validator.jar, hibernate-annotations.jar, hibernate-commons-annotations.jar, javassist.jar, dom4j.jar, commons-logging.jar.
  • Right-click the seampagination-WebModule and select Resolve Reference Problems:
    • Browse to the seampagination-ejb directory which is a sub-directory below the seampagination directory and select Open Project Folder.
    • Browse to the  jboss-seam-ui.jar found in Seam lib directory.  This should resolve the reference to the following jars: jboss-seam-ui.jar and jboss-el.jar.
If you want to create your own Java EE application using Seam 2.0 on Glassfish V2 with Netbeans from scratch (read the steps in Brian Leonard's blog Seam Refresh but use the SEAM 2.0 jars listed here here):
  • Use Netbeans to create a new Enterprise Application
  • Right-click the Libraries node of the EJBModule project , choose Add Jar  and add these jars:
    • Seam \\lib\\jboss-seam.jar
    • Seam \\lib\\hibernate.jar
    • Seam \\lib\\hibernate-validator.jar
    • Seam \\lib\\hibernate-annotations.jar
    • Seam \\lib\\hibernate-commons-annotations.jar
    • Seam \\lib\\javassist.jar
    • Seam \\lib\\dom4j.jar
    • Seam \\lib\\commons-logging.jar
  • Right-click the Libraries node of the WebModule project ,  choose Add Jar  and add these jars:
    • your ejbModule
    • Seam \\lib\\jboss-seam-ui.jar
    • Seam \\lib\\jboss-el.jar
  • create an empty seam.properties file in the seampagination-EJBModule src\\conf Folder.
  • add  the following phase listener to your faces-config.xml file under webpages web-inf:
    <lifecycle>
            <phase-listener>
                org.jboss.seam.jsf.SeamPhaseListener
            </phase-listener>
    </lifecycle>
    
    
  • add the following  context parameter to your web.xml file
    <context-param>
         <param-name>
               org.jboss.seam.core.init.jndiPattern
         </param-name>
         <param-value>
               java:comp/env/your ear name/#{ejbName}/local
         </param-value>    
    </context-param>
  • add the following listener class to your web.xml file
    <listener>
            <listener-class>
                org.jboss.seam.servlet.SeamListener
            </listener-class>
    </listener>
  • For any session EJB's referenced from the web, add  EJB references to your web.xml, for example:
    <ejb-local-ref>
         <ejb-ref-name>your ear name/CatalogBean/local</ejb-ref-name>
         <ejb-ref-type>Session</ejb-ref-type>
         <local-home/>
         <local>your package name.Catalog</local>
         <ejb-link>CatalogBean</ejb-link>
    </ejb-local-ref>
  • For any EJB's referenced from the web add  a Seam interceptor to the EJB, for example : @Interceptors({org.jboss.seam.ejb.SeamInterceptor.class})

References:




Sample Application using JSF, Spring 2.5, and Java Persistence APIs with Glassfish v2

[Read More]

Wednesday Aug 06, 2008

a RESTful Pet Catalog

a RESTful Pet Catalog


This Sample Pet Store Catalog application shows how to expose a  Catalog  as a RESTful Web Service for remote client applications, and it shows how to code a Dojo client which  gets and displays the Web Service responses in a dynamic Ajax table ( Dojo grid). I re-implemented this Sample Catalog application implemented with JAX-WS on the server side and JSF on the client side which is also available in the Java One Metro hands on lab.

Download the RESTful Pet Catalog Code

Dojo is an open source DHTML toolkit written in JavaScript.

JAX-RS provides a standardized API for building RESTful web services in Java. Central to the RESTful architecture is the concept of resources identified by universal resource identifiers (URIs). The API  provides a set of annotations which you can add to Plain Old Java Objects (POJOs)  to expose web resources identified by URIs .

Explanation of the usage of Dojo and JAX-RS in a sample Catalog Application

The image below shows the Catalog Listing page, which allows a user to page through a list of items in a store.
petcatalog

Quick installation and use of dojo with Netbeans

There are 3 ways to install dojo which you can read about in the book of dojo. A quick and easy way to use dojo with Netbeans is to download the JavaScript libraries from http://dojotoolkit.org/downloads.   Create a new NetBeans Web Applications project. Extract the dojo toolkit  into the project web directory: .../web , then rename dojo-release-1.1.1/ to src/  this will give you the project structure shown below.  I have already done this for the sample project so you do not have to download dojo in order to run the sample.
dojonetproj.JPG

Dojo style sheets

Every page using the dojo Grid needs to import the grid style sheet Grid.css as shown below:

Code Sample from:  index.html

    <style type="text/css">
      /\* tundraGrid.css matches Dijit Tundra style. \*/
      @import "src/dojox/grid/_grid/tundraGrid.css";
      @import "src/dijit/themes/tundra/tundra.css";
      @import "src/dojo/resources/dojo.css";
      @import "src/dojox/grid/_grid/Grid.css";
    </style>



This will load the the CSS files required by the Dojo grid widget, you can just use  dojox/grid/_grid/Grid.css instead of tundraGrid if you don't want the  tundra style. 

Loading base dojo and required modules into an application

In order to load dojo into your application,  put the relative path to the dojo.js file in a script element in the head section of your  HTML page as shown below:

Code Sample from:  index.html

 <script type="text/javascript" src="src/dojo/dojo.js"
           djConfig="isDebug: true, debugAtAllCosts: false,
           parseOnLoad: true">
 </script>




This script element will load the base dojo script which gives you access to all the dojo functionality.

Next  the application specifies which  dojo modules to load, using  the dojo.require function (kind of like  import in Java):

Code Sample from:  index.html

 <script type="text/javascript">
   dojo.require("dojox.grid.Grid");
   dojo.require("dojox.grid._data.model");
   dojo.require("dojo.parser");
 </script>


Dojo is organized into three major layers: Dojo Core, Dijit, and DojoX.   DojoX builds on  Dojo Core and provides newer extensions to the Dojo toolkit. The rest of the Java Script for this application is in the file dynamicTable.js.

The Grid Widget

You can use widgets declaratively by using special attributes inside of regular HTML tags, or programmatically through JavaScript.
The dojoType attribute declares a Dojo widget. Below is the declaration of the Grid widget for this applicaton:
Code Sample from:  index.html

<div id="grid" dojoType="dojox.Grid" model="model" structure="layout">
</div>


The model and structure attributes point to the  JavaScript variables for the model and layout structure explained below.

The Grid View

A Dojo grid  is a widget useful for displaying data sets in a table with its own scrollable views.  The dojo grid widget requires a layout. A grid layout is declared as an array of views.  Each view is a group of columns,  declared as an array of arrays. Each array element is an object, the "name" property of the object names the column. The column names will be displayed in the top row of the grid. The code below declares 4 columns: Company, City, State, Zip. This grid layout structure consists of one view as shown  below:

Code Sample from:  dynamicTable.js

formatImage = function(value) {
    if (!value)
        return '&nbsp;';   
        return "<img src='" + value + "'/>";   
};

// Data Grid layout
// A grid view is a group of columns
var view1 = {
            cells: [
        [
            {name: 'Name', field: "name"},
            {name: 'Description', field: "description", width: '200px'},
            {name: 'Photo',field: "imagethumburl", formatter: formatImage, width: '120px'},
            {name: 'Price',field: "price"}
        ]
    ]
};
// a grid layout is an array of views.
var layout = [ view1 ];




The Grid Model

The dojo grid widget requires a data model. The model variable declares the type of Dojo object that the Grid will use for the json data that will be loaded in the grid. There are different options for the model, this example uses the dojox.grid.data.Objects which is a collection of objects to be displayed in the grid.

Code Sample from:  dynamicTable.js

// the model will contain the data to be displayed in the view
model = new dojox.grid.data.Objects(null,null);

function handleResponse(responseObject, ioArgs){
    // set the model object with the returned items list
    model.setData(responseObject.items.item);       
}  

// make request to the items web service
function loadTable(page){
    start = page \* batchSize;
    var targetURL = "resources/items/?start="+ encodeURIComponent(start);   
    dojo.xhrGet({
        url: targetURL,
        handleAs: "json",
        load: handleResponse,
        error: handleError
    });
}

The loadTable function calls   dojo.xhrGet to make an XMLHttpRequest to the items JAX-RS web service specified by the url: parameter. When the response from web service is returned, the callback function  handleResponse specified by load: is called and the response is passed to the callback function in the responseObject. The handleAs  parameter specifies the response data type, handleAs: "json"  means the returned data is of the type JSON (Java Script object notation).
In the   handleResponse callback function,  model.setData is called to populate the Dojo grid  with the data returned from the  the  items JAX-RS web service. Below is an example of a JSON response from the items JAX-RS web service:

Example json data

{"items":
  {"@uri":"http://host/catalog/resources/items/",
   "item":[
     {"@uri":"http://host/catalog/resources/items/1/",
       "name":"Friendly Cat",
      "description":"This black and white colored cat is super friendly.",     
       "id":"1",
       "imageurl":"http://localhost:8080/CatalogService/images/anthony.jpg"},
     {"@uri":"http://host/catalog/resources/items/2/",
       "name":"Fluffy Cat",
       "description":"A great pet for a hair stylist!
       "id":"2",
       "imageurl":"http://localhost:8080/CatalogService/images/bailey.jpg"}
    ]
  }
}


Loading the table

The dojo.addOnLoad function allows you to call a  function after a page has loaded and after Dojo has finished its initilization. This application uses dojo.addOnLoad to call the loadTable() function (which we looked at above)  which calls the  items JAX-RS web service and sets the results in the grid data model.

Code Sample from:  dynamicTable.js

    <script type="text/javascript">
        dojo.addOnLoad(function(){
            loadTable(0);
        });
    </script>


Events for paging

The  "<<"">>" buttons call the next() previous() functions when clicked:

Code Sample from:  index.html

<input type="button" value="<<" onclick="previous();">
</input>
<input type="button" value=">>" onclick="next();">
</input>


The next() function  increments the page number and then calls the loadTable() funtion:

Code Sample from: dynamicTable.js


function next() {
    page =page + 1;
    loadTable(page);
}

function previous() {
    page =page - 1;
    if (page < 0) page = 0;
    loadTable(page);
}


RESTful  Web Services with JAX-RS

The  dojo.xhrGet url: parameter  references the URI  resources/items/ for the items   RESTful web service.  The items RESTful web service was generated using Netbeans 6.1 as explained in the Generating RESTful Web Services from Entity Classes  tutorial.  Using Netbeans 6.1 you can generate JPA Entity Classes from Database tables, then you can Generate RESTful Web Services from Entity Classes, and then you can test the Web Services with a browser interface. The items RESTful web service was generated from the item data base table (the sql is in the how to run section). 

Below is a snippet from the ItemsResource.java class which was generated by the Netbeans "Generate RESTful Web Services from Entity Classes" feature :

Code Sample from: ItemsResource.java

// Service URI path "/items/"

@Path("/items/")

public class 
ItemsResource {

  @GET
@ProduceMime("application/json")
    public
ItemsConverter get(@QueryParam("start")
@DefaultValue("0") int start, @QueryParam("max")
            @DefaultValue("4") int max, @QueryParam("expandLevel")
            @DefaultValue("1") int expandLevel, @QueryParam("query")
            @DefaultValue("SELECT e FROM Item e") String query) {
        try {
            ItemsConverter items = new ItemsConverter(
getEntities(start, max, query),
                context.getAbsolutePath(), expandLevel);
            return
items;
        } finally {
            PersistenceService.getInstance().close();
        }
    }


The ItemsResource represents a list of items. The ItemsResource get method returns a list of Item objects in JSON format. 
  • To address a resource in REST you specify its URI.  @Path is a JAX-RS annotation that identifies the URI path for the resource. For the ItemsResource  the URI path is /items/.
  • @GET specifies that the get method supports the HTTP GET method.
  • @ProduceMime specifies the MIME types that a method can produce. Here, the annotation specifies that the get method returns a JSONArray object.  The ItemsConverter class is a JAXB annotated class which is used to marshal a list of Item objects into XML or JSON format.   The getEntities method returns a list of Item entity objects and is explained below.  
  • @QueryParam specifies input parameters for methods.  When the method is invoked, the input value will be injected into the corresponding input argument. 
  • @DefaultValue specifies a default value for an arguement if no input value is given.
Here is an example of an HTTP request for this Web Service:

Request: GET http://host/catalog/resources/items/?start=0


Here is an example of an HTTP response for this Web Service:

Received:
{"items":
  {"@uri":"http://host/catalog/resources/items/",
   "item":[
     {"@uri":"http://host/catalog/resources/items/1/",
       "name":"Friendly Cat",
      "description":"This black and white colored cat is super friendly.",     
       "id":"1",
       "imageurl":"http://localhost:8080/CatalogService/images/anthony.jpg"},
     {"@uri":"http://host/catalog/resources/items/2/",
       "name":"Fluffy Cat",
       "description":"A great pet for a hair stylist!
       "id":"2",
       "imageurl":"http://localhost:8080/CatalogService/images/bailey.jpg"}
    ]
  }
}


The ItemsConverter class is a JAXB annotated class, used to marshal a list of Item objects into XML or  JSON format.  A snippet of the ItemsConverter class is shown below:


Code Sample from: ItemsConverter.java

@XmlRootElement
public class ItemsConverter {

@XmlElement
    public Collection<ItemConverter> getItem(){
     ...
       return items;
    }
    @XmlAttribute
   public URI getUri() {
        return uri;
    }



Java Persistence Query API

The ItemsResource getEntities method uses the Java Persistence API Query object to return a list of items.

Code Sample from: ItemsResource.java

@Path("/items/")

public class 
ItemsResource {

    . . .

    protected Collection<Item> getEntities(int start, int max, String query) {
PersistenceService ps = PersistenceService.getInstance();
        Query query = ps.
createQuery(query);
        query.
setFirstResult(start);
query.setMaxResults(max);
        return query.getResultList();
    }



The Java Persistence Query APIs are used to create and execute queries that can return a list of results.  The JPA Query interface provides support for pagination via the setFirstResult() and setMaxResults() methods: query.setMaxResults(int maxResult) sets the maximum number of results to retrieve. query.setFirstResult(int startPosition) sets the position of the first result to retrieve.

In the code below, we show the Item entity class which maps to the  CUSTOMER table that stores the item instances. This is a typical Java Persistence entity object. There are two requirements for an entity:
  1. annotating the class with an @Entity annotation.
  2. annotating the primary key identifier with @Id
Because the fields name, description.... are basic mappings from the object fields to columns of the same name in the database table, they don't have to be annotated. 
For more information on Netbeans and JPA see basics of developing a web application using Java™ Persistence API.


Code Sample from: Item.java

@Entity

public class Item implements Serializable {

@Id
    private Long id;

    private String name;
    private String description; 
    private String imageurl; 
    private String state; 
    private BigDecimal price;


    public
Item() { }

    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }

   . . .

}   




Conclusion
This concludes the sample application which  demonstrates a RESTful Web Service, coded using JAX-RS: Java API for RESTful Web Services (JSR-311) , which provides a list of items, and a dojo client which  gets and displays the Web Service responses in a dynamic Ajax table.

Configuration of the Application for Netbeans 6.5m1 , Glassfish V2, and MySQL


Running the Sample Code

  1. Download the sample code and extract its contents. You should now see the newly extracted directory as <sample_install_dir>/catalog, where <sample_install_dir> is the directory where you unzipped the sample package. For example, if you extracted the contents to C:\\ on a Windows machine, then your newly created directory should be at C:\\catalog.
  2. Start NetBeans IDE. Click Open Project in the File menu and select the catalog directory you just unzipped.
  3. Start the MySQL database as follows:
    • Click the Services tab in the NetBeans IDE.
    • Expand the databases node. You should see the MySQL server database in the list of databases.
      Database list
    • Right-mouse click on the MySQL server database and select Start.
  4. Create the petcatalog database as follows:
    • Right-mouse click on the MySQL server database and select Create Database.
    • Enter the database name catalog. This will open a New Database Connection window. Click O.K. to accept the displayed settings.
  5. Create the tables in the MySQL catalog database as follows:
    • Expand the Drivers node. You should a driver for the catalog database in the list of drivers.
      Driver list
    • Right-mouse click on the catalog driver and select Connect.
    • Right-mouse click on the catalog driver and select Execute Command. This will open up a SQL command window.
    • Copy the contents of the createdbmysql.sql file in the catalog directory and paste the contents into the SQL command window.
    • Click the Run SQL icon Run SQL icon (Ctrl+Shift+E) above the SQL command window.
  6. Build the project as follows:

    • Right click the catalog node in the Projects window.
    • Select Clean and Build Project.

  7. Run the project as follows:

    • Right click the catalog node in the Projects window.
    • Select Run Project.
When you run the project, your browser should display the opening page of the Sample Application (at http://localhost:8080/catalog/).

For more Information:




Tuesday Jul 29, 2008

a Dynamic Ajax table example using dojo and RESTful Web Services on Glassfish

a Dynamic Ajax table example using dojo and RESTful Web Services on Glassfish


This Sample Catalog app demonstrates a RESTful Web Service, coded using JAX-RS: Java API for RESTful Web Services (JSR-311) and Java Persistence API, which provides a list of customers, and a Dojo client which  gets and displays the Web Service responses in a dynamic Ajax table ( Dojo grid).

Download the dojo Sample Application Code

Dojo is an open source DHTML toolkit written in JavaScript.

JAX-RS provides a standardized API for building RESTful web services in Java. Central to the RESTful architecture is the concept of resources identified by universal resource identifiers (URIs). The API  provides a set of annotations which you can add to Plain Old Java Objects (POJOs)  to expose web resources identified by URIs .

Explanation of the usage of Dojo and JAX-RS in a sample Catalog Application

The image below shows the Customer Listing page, which allows the user to page through a list of customers.

table.jpg


Quick installation and use of dojo with Netbeans

There are 3 ways to install dojo which you can read about in the book of dojo. A quick and easy way to use dojo with Netbeans is to download the JavaScript libraries from http://dojotoolkit.org/downloads.   Create a new NetBeans Web Applications project. Extract the dojo toolkit  into the project web directory: .../web , then rename dojo-release-1.1.1/ to src/  this will give you the project structure shown below.  I have already done this for the sample project so you do not have to download dojo in order to run the sample.
dojonetproj.JPG

Dojo style sheets

Every page using the dojo Grid needs to import the grid style sheet Grid.css as shown below:

Code Sample from:  index.html

    <style type="text/css">
      /\* tundraGrid.css matches Dijit Tundra style. \*/
      @import "src/dojox/grid/_grid/tundraGrid.css";
      @import "src/dijit/themes/tundra/tundra.css";
      @import "src/dojo/resources/dojo.css";
      @import "src/dojox/grid/_grid/Grid.css";
    </style>



This will load the the CSS files required by the Dojo grid widget, you can just use  dojox/grid/_grid/Grid.css instead of tundraGrid if you don't want the  tundra style. 

Loading base dojo and required modules into an application

In order to load dojo into your application,  put the relative path to the dojo.js file in a script element in the head section of your  HTML page as shown below:

Code Sample from:  index.html

 <script type="text/javascript" src="src/dojo/dojo.js"
           djConfig="isDebug: true, debugAtAllCosts: false,
           parseOnLoad: true">
 </script>




This script element will load the base dojo script which gives you access to all the dojo functionality.

Next  the application specifies which  dojo modules to load, using  the dojo.require function (kind of like  import in Java):

Code Sample from:  index.html

 <script type="text/javascript">
   dojo.require("dojox.grid.Grid");
   dojo.require("dojox.grid._data.model");
   dojo.require("dojo.parser");
 </script>


Dojo is organized into three major layers: Dojo Core, Dijit, and DojoX.   DojoX builds on  Dojo Core and provides newer extensions to the Dojo toolkit. The rest of the Java Script for this application is in the file dynamicTable.js.

The Grid Widget

You can use widgets declaratively by using special attributes inside of regular HTML tags, or programmatically through JavaScript.
The dojoType attribute declares a Dojo widget. Below is the declaration of the Grid widget for this applicaton:
Code Sample from:  index.html

<div id="grid" dojoType="dojox.Grid" model="model" structure="layout">
</div>


The model and structure attributes point to the  JavaScript variables for the model and layout structure explained below.

The Grid View

A Dojo grid  is a widget useful for displaying data sets in a table with its own scrollable views.  The dojo grid widget requires a layout. A grid layout is declared as an array of views.  Each view is a group of columns,  declared as an array of arrays. Each array element is an object, the "name" property of the object names the column. The column names will be displayed in the top row of the grid. The code below declares 4 columns: Company, City, State, Zip. This grid layout structure consists of one view as shown  below:

Code Sample from:  dynamicTable.js

// Data Grid layout
// A grid view is a group of columns
var view1 = {
            cells: [
                [
                    {name: 'Company', field: "name"},
                    {name: 'City', field: "city"},
                    {name: 'State',field: "state"},
                    {name: 'Zip',field: "zip"}
                ]
            ]
};
// a grid layout is an array of views.
var layout = [ view1 ];


This grid layout for this example is shown in the figure below (note: how the data for the table gets loaded is explained below).
dojogrid.JPG

The Grid Model

The dojo grid widget requires a data model. The model variable declares the type of Dojo object that the Grid will use for the json data that will be loaded in the grid. There are different options for the model, this example uses the dojox.grid.data.Objects which is a collection of objects to be displayed in the grid.

Code Sample from:  dynamicTable.js


// the model will contain the data to be displayed in the view
model = new dojox.grid.data.Objects(null,null);

function handleResponse(responseObject, ioArgs){
    // set the model object with the returned customers list
    model.setData(responseObject.customers.customer);       
}  

// make request to the customers web service
function loadTable(page){
    start = page \* batchSize;
    var targetURL = "resources/customers/?start="+
                      encodeURIComponent(start);   
    dojo.xhrGet({
        url: targetURL,
        handleAs: "json",
        load: handleResponse,
        error: handleError
    });
}


The loadTable function calls   dojo.xhrGet to make an XMLHttpRequest to the customers JAX-RS web service specified by the url: parameter. When the response from web service is returned, the callback function  handleResponse specified by load: is called and the response is passed to the callback function in the responseObject. The handleAs  parameter specifies the response data type, handleAs: "json"  means the returned data is of the type JSON (Java Script object notation).
In the   handleResponse callback function,  model.setData is called to populate the Dojo grid  with the data returned from the  the  customers JAX-RS web service. Below is an example of a JSON response from the customers JAX-RS web service:

Example json data

{"customers":
  {"@uri":"http://host/dojoRest/resources/customers/",
   "customer":[
     {"@uri":"http://host/dojoRest/resources/customers/1/",
       "name":"JumboCom",
      "city":"Fort Lauderdale",     
       "state":"FL",
       "zip":"33015"},
     {"@uri":"http://host/dojoRest/resources/customers/2/",
       "name":"Livermore Enterprises",
       "city":"Miami",
       "state":"FL",
       "zip":"33055"}
    ]
  }
}


Loading the table

The dojo.addOnLoad function allows you to call a  function after a page has loaded and after Dojo has finished its initilization. This application uses dojo.addOnLoad to call the loadTable() function (which we looked at above)  which calls the  customers JAX-RS web service and sets the results in the grid data model.

Code Sample from:  dynamicTable.js

    <script type="text/javascript">
        dojo.addOnLoad(function(){
            loadTable(0);
        });
    </script>


Events for paging

The  "<<"">>" buttons call the next() previous() functions when clicked:

Code Sample from:  index.html

<input type="button" value="<<" onclick="previous();">
</input>
<input type="button" value=">>" onclick="next();">
</input>


The next() function  increments the page number and then calls the loadTable() funtion:

Code Sample from: dynamicTable.js


function next() {
    page =page + 1;
    loadTable(page);
}

function previous() {
    page =page - 1;
    if (page < 0) page = 0;
    loadTable(page);
}


RESTful  Web Services with JAX-RS


The  dojo.xhrGet url: parameter  references the URI  resources/customers/ for the customers   RESTful web service.  The customers RESTful web service was generated using Netbeans 6.1 as explained in the Generating RESTful Web Services from Entity Classes  tutorial.  Using Netbeans 6.1 you can generate JPA Entity Classes from Database tables, then you can Generate RESTful Web Services from Entity Classes, and then you can test the Web Services with a browser interface. The customers RESTful web service was generated from the customer data table which comes already created in the Java DB with Netbeans. 

Below is a snippet from the CustomersResource.java class which was generated by the Netbeans "Generate RESTful Web Services from Entity Classes" feature :

Code Sample from: CustomersResource.java


// Service URI path "/customers/"

@Path("/customers/")

public class CustomersResource {

  @GET
@ProduceMime("application/json")
  public
CustomersConverter get(@QueryParam("start")
@DefaultValue("0") int start, @QueryParam("max")
            @DefaultValue("4") int max, @QueryParam("expandLevel")
            @DefaultValue("1") int expandLevel,
            @QueryParam("query")
            @DefaultValue("SELECT e FROM Customer e") String query{


        try {
            CustomersConverter custs = new CustomersConverter(
                getEntities
(start, max, query),
                context.getAbsolutePath(), expandLevel);
            return
custs;
        } finally {
            PersistenceService.getInstance().close();
        }
    }


The CustomersResource represents a list of customers. The CustomersResource get method returns a list of Customer objects in JSON format. 
  • To address a resource in REST you specify its URI.  @Path is a JAX-RS annotation that identifies the URI path for the resource. For the CustomersResource  the URI path is /customers/.
  • @GET specifies that the get method supports the HTTP GET method.
  • @ProduceMime specifies the MIME types that a method can produce. Here, the annotation specifies that the get method returns a JSONArray object.  The CustomersConverter class is a JAXB annotated class which is used to marshal a list of Customer objects into XML or JSON format.   The getEntities method returns a list of Customer entity objects and is explained below.  
  • @QueryParam specifies input parameters for methods.  When the method is invoked, the input value will be injected into the corresponding input argument. 
  • @DefaultValue specifies a default value for an arguement if no input value is given.
Here is an example of an HTTP request for this Web Service:

Request: GET http://host/dojoRest/resources/customers/?start=0


Here is an example of an HTTP response for this Web Service:

Received:
{"customers":
  {"@uri":"http://host/dojoRest/resources/customers/",
   "customer":[
     {"@uri":"http://host/dojoRest/resources/customers/1/",
       "name":"JumboCom",
      "city":"Fort Lauderdale",     
       "state":"FL",
       "zip":"33015"},
     {"@uri":"http://host/dojoRest/resources/customers/2/",
       "name":"Livermore Enterprises",
       "city":"Miami",
       "state":"FL",
       "zip":"33055"}
    ]
  }
}


The CustomersConverter class is a JAXB annotated class, used to marshal a list of Customer objects into XML or  JSON format.  A snippet of the CustomersConverter class is shown below:


Code Sample from: CustomersConverter.java

@XmlRootElement
public class CustomersConverter {

@XmlElement
    public Collection<CustomerConverter> getCustomer(){
     ...
       return items;
    }
    @XmlAttribute
   public URI getUri() {
        return uri;
    }



Java Persistence Query API

The CustomersResource getEntities method uses the Java Persistence API Query object to return a list of customers.

Code Sample from: CustomersResource.java


@Path("/customers/")

public class CustomersResource {

    . . .

    protected Collection<Customer> getEntities(int start, int max,
        String query) {

        PersistenceService ps = PersistenceService.getInstance();
        Query query = ps.
createQuery(query);
        query.
setFirstResult(start);
        query.
setMaxResults(max);
        return query.getResultList();
    }



The Java Persistence Query APIs are used to create and execute queries that can return a list of results.  The JPA Query interface provides support for pagination via the setFirstResult() and setMaxResults() methods: query.setMaxResults(int maxResult) sets the maximum number of results to retrieve. query.setFirstResult(int startPosition) sets the position of the first result to retrieve.

In the code below, we show the Customer entity class which maps to the  CUSTOMER table that stores the customer instances. This is a typical Java Persistence entity object. There are two requirements for an entity:
  1. annotating the class with an @Entity annotation.
  2. annotating the primary key identifier with @Id
Because the fields name, description.... are basic mappings from the object fields to columns of the same name in the database table, they don't have to be annotated. 
For more information on Netbeans and JPA see basics of developing a web application using Java™ Persistence API.


Code Sample from: Customer.java


@Entity

public class Customer implements Serializable {

    @Id
    private Integer customerId;

    private String name;
    private String addressline1;   
    private String city;  
    private String state; 
    private String zip;


    public
Customer() { }

    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }



}   




Conclusion
This concludes the sample application which  demonstrates a RESTful Web Service, coded using JAX-RS: Java API for RESTful Web Services (JSR-311) , which provides a list of customers, and a dojo client which  gets and displays the Web Service responses in a dynamic Ajax table.

Configuration of the Application for jMaki, JPA, Netbeans 6.1 and Glassfish V2

  • Download and install NetBeans 6.1 bundled with GlassFish V2
  • Alternatively you can  Download and install GlassFish V2 separately.

Open and Run the Sample code:

  1. Download the sample code and extract its contents. You should now see the newly extracted directory as <sample_install_dir>/dojoRest, where <sample_install_dir> is the directory where you installed the sample package. For example, if you extracted the contents to C:\\ on a Windows machine, then your newly created directory should be at C:\\dojoRest.

  2. Start the NetBeans IDE. Click Open Project in the File menu and select the dojoRest directory you just unzipped.

  3. Build the project as follows:

    • Right click the dojoRest node in the Projects window.
    • Select Clean and Build Project.

  4. Run the project as follows:

    • Right click the dojoRest node in the Projects window.
    • Select Run Project.
When you run the project, your browser should display the opening page of the Sample Application (at http://localhost:8080/dojoRest/).

References:



Friday Jul 18, 2008

a Dynamic Ajax table example using jMaki and RESTful Web Services on Glassfish

a Dynamic Ajax table example using jMaki and RESTful Web Services on Glassfish


This Sample Catalog app demonstrates a RESTful Web Service, coded using JAX-RS: Java API for RESTful Web Services (JSR-311) and Java Persistence API, which provides a list of customers, and a jMaki client which  gets and displays the Web Service responses in a dynamic Ajax table.

Download the jMaki Sample Application Code

jMaki is an Ajax framework that provides a lightweight model for creating JavaScript centric Ajax-enabled web applications. jMaki provides wrapped widgets that can be used as JavaServer Pages tags, as JavaServer Faces components, within a Phobos application, or with PHP. This sample applicaton uses jMaki with JavaServer Pages.

JAX-RS provides a standardized API for building RESTful web services in Java. Central to the RESTful architecture is the concept of resources identified by universal resource identifiers (URIs). The API  provides a set of annotations which you can add to Plain Old Java Objects (POJOs)  to expose web resources identified by URIs .

Explanation of the usage of jMaki and JAX-RS in a sample Catalog Application

The image below shows the Customer Listing page, which allows the user to page through a list of customers.

pagingtable.jpg

jMaki dataTable widget

With  jMaki and JavaServer Pages, you can easily include wrapped widgets from ajax toolkits into a JavaServer Page as a custom JSP tag. With the Netbeans jMaki plugin you can drag  jMaki widgets from the Palette into a JSP. jMaki standardizes widget data and event models to simplify the programming model and to simplify interactions between widgets.

The sample application's index.jsp page uses a jMaki yahoo.dataTable widget to display a  list of customers in a dynamic table.

The jMaki table widgets (there is also a jMaki dojo table widget) are useful when you want to show a set of results in tabular data on a web page.  Table widgets provide sortable columns, row selection, and they can be updated using jMaki publish subscribe events.

In the List.jsp web page the dataTable is defined as shown below:   (Note: Red colors are for jMaki tags or variables,  and Green for my code or variables)

Code Sample from:  index.jsp

<a:widget name="yahoo.dataTable"
subscribe="/table"
service="webresources/customers/jMakiTable"/>


To determine the data format and events for the table you can refer to the  jMaki Table Data Model or look at the widget.json file for the table widget. This file is located in the resources/yahoo/dataTable directory.

The service attribute references the customers/jMakiTable RESTful web service which returns the data to be included in the table.  The data for the table should be a JSON object containing an object of columns and an array of row arrays. The column names need a unique id which is then used in the data to associate it with a given row. An example for a table of companies is shown below:


Code Sample from:  widget.json
{
 'columns':[
     {'label' :'Company', 'id' : 'name'},
     {'label':'City', 'id' : 'city'},
     {'label':'City', 'id' : 'state'}
 ],
 'rows':[
     {'name' : 'Sun Microsystems', 'city' : 'Santa Clara',
'state' : 'CA'},
     {'name' : 'IBM', 'city' : 'Raleigh','state' : 'NC'}
 ]
}



The subscribe="/table" attribute specifies a topic that events can be sent to. Publish and subscribe events can be used to tie widgets together (more on this later).

RESTful  Web Services with JAX-RS


The dataTable's service attribute references the URI  webresources/customers/jMakiTable for the customers jMakiTable  RESTful web service.  The customers RESTful web service was generated using Netbeans 6.1 as explained in the Generating RESTful Web Services from Entity Classes  tutorial.  Using Netbeans 6.1 you can generate JPA Entity Classes from Database tables, then you can Generate RESTful Web Services from Entity Classes, and then you can test the Web Services with a browser interface. The customers RESTful web service was generated from the customer data table which comes already created in the Java DB with Netbeans.  I added the jMakiTable method to the generated customers Web Service,  in order to return the customers in the jMaki table format. I followed the jMakiBackend example which comes with Jersey (the JAX-RS reference implementation) which is expained in Japods blog:  jMaki Widgets Talking To Jersey Resources In JSON.


Below is a snippet from the CustomersResource.java class which was generated by the Netbeans "Generate RESTful Web Services from Entity Classes" feature :

Code Sample from: CustomersResource.java


// Service URI path "/customers/"

@Path("/customers/")

public class CustomersResource {

  @GET
  @ProduceMime("application/json")

    public
CustomersConverter get(@QueryParam("start")
            @DefaultValue
("0") int start, @QueryParam("max")
            @DefaultValue("4") int max, @QueryParam("expandLevel")
            @DefaultValue("1") int expandLevel, @QueryParam("query")
            @DefaultValue("SELECT e FROM Customer e") String query) {
        try {
            CustomersConverter custs = new CustomersConverter(
                getEntities
(start, max, query),
                context.getAbsolutePath(), expandLevel);
            return
custs;
        } finally {
            PersistenceService.getInstance().close();
        }
    }


The CustomersResource represents a list of customers. The CustomersResource get method returns a list of Customer objects in JSON format. 
  • To address a resource in REST you specify its URI.  @Path is a JAX-RS annotation that identifies the URI path for the resource. For the CustomersResource  the URI path is /customers/.
  • @GET specifies that the get method supports the HTTP GET method.
  • @ProduceMime specifies the MIME types that a method can produce. Here, the annotation specifies that the get method returns a JSONArray object.  The CustomersConverter class is a JAXB annotated class which is used to marshal a list of Customer objects into XML or JSON format.   The getEntities method returns a list of Customer entity objects and is explained below.  
  • @QueryParam specifies input parameters for methods.  When the method is invoked, the input value will be injected into the corresponding input argument. 
  • @DefaultValue specifies a default value for an arguement if no input value is given.
Here is an example of an HTTP request for this Web Service:

Request: GET http://host/jMakiRest/webresources/customers/?start=0


Here is an example of an HTTP response for this Web Service:

Received:
{"customers":
  {"@uri":"http://host/jMakiRest/webresources/customers/",
   "customer":[
     {"@uri":"http://host/jMakiRest/webresources/customers/1/",
       "name":"JumboCom",
      "city":"Fort Lauderdale",     
       "state":"FL",
       "zip":"33015"},
     {"@uri":"http://host/jMakiRest/webresources/customers/2/",
       "name":"Livermore Enterprises",
       "city":"Miami",
       "state":"FL",
       "zip":"33055"}
    ]
  }
}


Below is the getTable method  from the CustomersResource.java class,  which returns a list of Customers in the jMaki  JSON table format.

Code Sample from: CustomersResource.java


public class CustomersResource {
     . . .

  @GET
  @Path("/jMakiTable")
  @ProduceMime("application/json")
    public CustomerTableModel getTable(@QueryParam("start")
            @DefaultValue
("0") int start, @QueryParam("max")
            @DefaultValue("4") int max, @QueryParam("expandLevel")
            @DefaultValue("1") int expandLevel, @QueryParam("query")
            @DefaultValue("SELECT e FROM Customer e") String query) {

        CustomersConverter custs = get(start, max,
                   expandLevel, query);
        return new CustomerTableModel(custs.getCustomer());
    }


The getTable method calls the CustomersResource get method, explained above,  to get a list of Customer Entities which are used to create a CustomerTableModel class. The CustomerTableModel class is a JAXB annotated class, used to marshal a list of Customer objects into the jMaki  JSON table format.  A snippet of the CustomerTableModel class is shown below:


Code Sample from: CustomerTableModel.java

@XmlRootElement
public class CustomerTableModel {

    public static class JMakiTableHeader {

        public String id;
        public String label;

        public JMakiTableHeader(String id,
           String label) {
            this.id = id;
            this.label = label;
        }
    }
    public List<JMakiTableHeader> columns =
          initHeaders();
    public List<CustomerConverter> rows;

  . ..



Java Persistence Query API

The CustomersResource getEntities method uses the Java Persistence API Query object to return a list of customers.

Code Sample from: CustomersResource.java


@Path("/customers/")

public class CustomersResource {

    . . .

    protected Collection<Customer> getEntities(int start, int max, 
        String query) {
        PersistenceService ps = PersistenceService.getInstance();
        Query query = ps.
createQuery(query);
        query.
setFirstResult(start);

        query.setMaxResults(max);
        return query.getResultList();
    }



The Java Persistence Query APIs are used to create and execute queries that can return a list of results.  The JPA Query interface provides support for pagination via the setFirstResult() and setMaxResults() methods: query.setMaxResults(int maxResult) sets the maximum number of results to retrieve. query.setFirstResult(int startPosition) sets the position of the first result to retrieve.

In the code below, we show the Customer entity class which maps to the  CUSTOMER table that stores the customer instances. This is a typical Java Persistence entity object. There are two requirements for an entity:
  1. annotating the class with an @Entity annotation.
  2. annotating the primary key identifier with @Id
Because the fields name, description.... are basic mappings from the object fields to columns of the same name in the database table, they don't have to be annotated. 
For more information on Netbeans and JPA see basics of developing a web application using Java™ Persistence API.

Code Sample from: Customer.java

@Entity

public class Customer implements Serializable {

@Id
    private Integer customerId;

    private String name;
    private String addressline1;   
    private String city;  
    private String state; 
    private String zip;


    public
Customer() { }

    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }



}   


jMaki Publish Subscribe events 

jMaki publish subscribe events tie widgets actions together. The sample app uses two jMaki yahoo.button widgets which publish to the /button/previous, /button/next topics when the respective button is clicked:

Code Sample from: List.jsp

<a:widget name="yahoo.button" value="{label : '<<',
                 action : {topic : '/button/previous'}}" />

<a:widget name="yahoo.button" value="{label : '>>',
                 action : {topic : '/button/next'}}" />


Events in jMaki are handled by jMaki Glue , which allows JavaScript components to talk to each other. You put function listeners which Subscribe to topics that your widgets Publish to in a file called glue.js (to read more about this see A practical guide to jMaki Events ).

Connecting the listener to the handler

The listener handler for the  /button/next topic is shown below. First you declare the topic to listen to and then the listener function which will handle the notification. The /button/next listener handler  increments the page number and then calls the getNextPage funtion.

Code Sample from: glue.js

var page= 0;
var start= 0;
var batchSize=4;

jmaki.subscribe("/button/next", function(args) {
    page =page + 1;
    getNextPage
(page);
});

jmaki.subscribe("/button/previous", function(args) {
    page =page - 1;
    if (page < 0) page = 0;
    getNextPage
(page);
});

function getNextPage(page) {
    start = page \* batchSize;
    jmaki.doAjax
({method: "POST",
      url:
"webresources/customers/?start="+encodeURIComponent(start),
      callback :
function(req) {
        var respObj = eval('('+ req.responseText +')');

        var rows =  respObj.customers.customer;

        jmaki.publish
("/table/clear", { });
        for(j=0;j<rows.length;j++) {
           var row = rows[j];
           jmaki.publish
("/table/addRow",{value: row});
        }
      }
  });    
}


The getNextPage function uses  jmaki.doAjax, which provides an easy way to make an  XMLHttpRequest, to call the /customers/ RESTful Web Service  passing the start index  as a URI parameter.  The  callback function uses  eval to convert the XMLHttpRequest response into a JSON object. Then  jmaki.publish is called to publish the returned customer JSON objects to the /table/addRow topic.

The yahoo.dataTable widget subscribes to the table topic.
Subscribe events allow you to manipulate a given instance of a widget. The event names are appended to the the subscribe topic name following a "/". For example  "/table/addRow" will call the yahoo.dataTable addRow function which will add the  payload value passed to the widget to the the table. This will cause the  returned customer JSON object to be displayed in the table on the html page.


Conclusion
This concludes the sample application which  demonstrates a RESTful Web Service, coded using JAX-RS: Java API for RESTful Web Services (JSR-311) , which provides a list of customers, and a  jMaki  client which  gets and displays the Web Service responses in a dynamic Ajax table.

Configuration of the Application for jMaki, JPA, Netbeans 6.1 and Glassfish V2

  • Download and install NetBeans 6.1 bundled with GlassFish V2
  • Alternatively you can  Download and install GlassFish V2 separately.
  • Download and install the jMaki plug-in in the NetBeans update center.

Open and Run the Sample code:

  1. Download the sample code and extract its contents. You should now see the newly extracted directory as <sample_install_dir>/jmakiRest, where <sample_install_dir> is the directory where you installed the sample package. For example, if you extracted the contents to C:\\ on a Windows machine, then your newly created directory should be at C:\\jmakiRest.

  2. Start the NetBeans IDE. Click Open Project in the File menu and select the jmakiRest directory you just unzipped.

  3. Build the project as follows:

    • Right click the jmakiRest node in the Projects window.
    • Select Clean and Build Project.

  4. Run the project as follows:

    • Right click the jmakiRest node in the Projects window.
    • Select Run Project.
When you run the project, your browser should display the opening page of the Sample Application (at http://localhost:8080/jmakiRest/).


If you want to create your own jMaki application:
  • check out Arun Gupta's blog and screencasts.

References:




Monday Jul 07, 2008

Sample Store Catalog using using Groovy and Grails and the Java Persistence API on Glassfish with MySQL

Sample Store Catalog using using Groovy and Grails and the Java Persistence API on Glassfish with MySQL


I modified this Groovy and Grails  Catalog Sample application to use JPA entity java classes instead of  Groovy domain classes. I followed the steps in this InfoQ article Grails + EJB Domain Models Step-by-Step  and I was really surprised at how easy it was !

download Catalog sample code

Overview of the Technologies and Frameworks in the Sample Application

The Java Persistence API provides a POJO-based persistence model for Java EE and Java SE applications. It handles the details of how relational data is mapped to Java objects, and it standardizes Object/Relational (O/R) mapping.

Grails aims to bring the "coding by convention" paradigm to Groovy. It's an open-source web application framework that leverages the Groovy language and complements Java Web development.

Groovy is an agile and dynamic language for the Java Virtual Machine, it compiles to Java bytecode, and it combines popular features from languages such as Smalltalk, Python, and Ruby.

Grails is a Model-View-Controller based framework that simplifies the development of  web applications by reducing the need for configuration files and by generating a lot of the things needed in a database-backed Web application.

mvc.gif



The Sample Application

The sample application displays an online catalog of pets sold in a pet store. The image below shows the Catalog Listing page, which allows a user to page through a list of items in a store.

listpet.jpg


The Model - JPA Entity Classes

The Model is your application's persistent business domain objects. A JPA Entity instance represents a row in a database table.  Item is an Entity class -- a typical Java Persistence entity object -- which maps to an ITEM table that stores the item instances.

The Item class has a many-to-one relationship with the Address class,  this is specified using the @ManyToOne annotation in the Item class and the @OneToMany(mappedBy = "address") annotation in the Address entity class shown below:


Code Sample from: model\\Item.java
package model;

// import ....

@Entity
@Table(name = "item")
public class Item implements Serializable{
    @Id
    private Long id;
    private String name;
    private String description;
    private String imageurl;
    private String imagethumburl;
    private BigDecimal price;
@ManyToOne(optional = false)
    @JoinColumn(name = "address_id")
    private Address address;

    // getters and setters ...
}



Code Sample from: model\\Address.java
package model;

// import ....

@Entity
@Table(name = "address")

public class Address implements Serializable{
    @Id
    private Long id;
    private String street1;
    private String street2;
    private String city;
    private String state;
    private String zip;
    private BigDecimal latitude;
    private BigDecimal longitude;
    private BigInteger version;
@OneToMany(mappedBy = "address")
    private Collection<Item> items ;

// getters and setters ...

}



classrel.gif

SQL  Sample for items table

CREATE TABLE item (
 id BIGINT NOT NULL,
 product_id BIGINT NOT NULL,
 name VARCHAR(30) NOT NULL,
 description VARCHAR(500) NOT NULL,
 imageurl VARCHAR(55),
 imagethumburl VARCHAR(55),
 price DECIMAL(14,2) NOT NULL,
 address_id BIGINT NOT NULL,
 primary key (id),
 foreign key (address_id) references address(id),
 foreign key (product_id) references product(id)
);



Using the Java Persistence API With Grails and MySQL

Entering the Grails command
> grails create-app catalog
creates a standard directory structure for a grails application named catalog.  After you have your directory structure , to use JPA entities with a grails application:
  1. copy your entity files into the application name\\src\\java directory,  in this case I copied the model.Item and the model.Address  java files into the catalog\\src\\java\\model directory.
  2. copy the MySQL jdbc driver mysql-connector-java-5.1.6-bin.jar into the directory  catalog\\lib .
  3. modify the DataSource.groovy file in the catalog\\grails-app\\conf directory to use MySQL as the data base and  the GrailsAnnotationConfiguration class to use the annotations in the JPA entities as shown below :


    Code Sample from: catalog\\grails-app\\conf\\DataSource.groovy

    import org.codehaus.groovy.grails.orm.hibernate.cfg.GrailsAnnotationConfiguration
    dataSource {
    configClass = GrailsAnnotationConfiguration.class
        pooled = false
    driverClassName = "com.mysql.jdbc.Driver"
        username = "root"
        password = ""
    dialect = "org.hibernate.dialect.MySQL5InnoDBDialect"
    }
    hibernate {
        cache.use_second_level_cache=true
        cache.use_query_cache=true
        cache.provider_class='org.hibernate.cache.EhCacheProvider'
    }
    // environment specific settings
    environments {
        development {
            dataSource {
                dbCreate = "update"
    url = "jdbc:mysql://localhost/petcatalog"
            }
        }
        test {
            dataSource {
                dbCreate = "update"
                url = "jdbc:mysql://localhost/petcatalog"
            }
        }
        production {
            dataSource {
                dbCreate = "update"
                url = "jdbc:mysql://localhost/petcatalog"
            }
        }
    }                       


  4. In order for Grails to recognize the JPA Entity classes as domain classes,  add the hibernate.cfg.xml file shown below to the catalog\\grails-app\\conf\\hibernate directory:


    Code Sample from: catalog\\grails-app\\conf\\hibernate\\hibernate.cfg.xml
    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE hibernate-configuration PUBLIC
            "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
            "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

    <hibernate-configuration>
        <session-factory>
            <mapping package="model" />
            <mapping class="model.Item" />
            <mapping class="model.Address" />
        </session-factory>
    </hibernate-configuration>

The Controller

Entering the Grails command  (in the directory catalog)
> grails generate-controller model.Item
will generate the ItemController.groovy   class for the model.Item entity class.

Controllers handle incoming http requests, interact with the model to get data and to process requests,  invoke the correct view, and direct domain data to the view for display.  In Grails, http requests are handled by Controller classes which are made up of one or more action methods that are executed on request and then either render a Groovy Server Page or redirect to another action. Grails routes requests to the controller action which corresponds to the URL mapping for the request. In Grails the default mapping from URL to action method follows this convention: http://host/app/controller/action/id .  For example the URL http://host/catalog/item/list calls the list action method in the item controller class shown below.     Grails Scaffolding provides a series of standardized Controller action methods for listing, showing, creating, updating, and deleting objects of a class.  These standardized actions come with both controller logic and default view Groovy Server Pages. The ItemController list action renders a view with a paginated list of item objects.

Code Sample from: grails-app\\controllers\\ItemController.groovy

class ItemController {

def index = { redirect(action:list,params:params) }

  def list = {
    if(!params.max) params.max = 10
    [ itemList: Item.list( params ) ]
  }
. . .


When a URL has a controller but no action (e.g. http://localhost:8080/catalog/item/  ), Grails defaults to the index action. In the ItemController code the  index action method redirects to the list action.  The ItemController list action method calls the Item.list() method which returns an ArrayList of item objects retrieved from the item database table . If there are more than params.max objects in the table, Grails creates next and previous pagination links automatically. The itemList variable  is automatically made available to the view by the framework.

After executing code, actions usually render a GSP in the views directory corresponding to the name of the controller and action, for example the list action will render the grails-app\\views\\item\\list.gsp .

The View

Entering the Grails command  (in the directory catalog)
> grails generate-views model.Item
will generate the create.gsp , edit.gsp, list.gsp, show.gsp  groovy server pages for the model.Item entity class.
The view layer generates a web page, using data from domain objects provided by the controller. In Grails, the view is rendered using Groovy Server Pages. Below is part of the list.gsp for the Catalog application (note I modified the html table format from the default generated).


Code Sample from: grails-app\\views\\item\\list.gsp

<table>
   <thead>
     <tr>
<g:sortableColumn property="name" title="Name" />
        <g:sortableColumn property="imagethumburl" title="Photo" />
        <g:sortableColumn property="price" title="Price" />
     </tr>
   </thead>
   <tbody>
<g:each in="${itemList}" status="i" var="item">
        <tr class="${(i % 2) == 0 ? 'odd' : 'even'}">
           <td>
<g:link action="show" id="${item.id}">
                ${item.name?.encodeAsHTML()}</g:link>
           </td>
           <td>
             <img src="${createLinkTo(dir:'images',file:item.imagethumburl)}"/>
           </td>
           <td>${item.price?.encodeAsHTML()}</td>
        </tr>
     </g:each>
  </tbody>
 </table>

<div class="paginateButtons">
<g:paginate total="${Item.count()}" />
</div>


The view uses instance variables set by the controller to access the data it needs to render the GSP.

GSP has a GroovyTagLib similar to the  JSP tag library. <g: are GroovyTags.

<g:sortableColumn
       The sortableColumn tag renders a sortable column to support sorting in tables.

<g:each in="${itemList}" status="i" var="item">
loops through each object in the itemList variable, which is an ordered ArrayList of Item model objects,  and assigns each Item model object to the item variable.

<g:link action="show" id="${item.id}">${item.name?.encodeAsHTML()}</g:link>
the <g:link> GroovyTag creates an html anchor tag href based on the action, id, controller parameters specified. In this example it generates a link to the item/show/id action which when clicked will display the corresponding item details. For example this line will generate the following HTML for the variable item:
<a href="/catalog/item/show/2">Friendly Cat</a>
<img src="${createLinkTo(dir:'images',file:item.imagethumburl)}"/>
The createLinkTo tag generates an HTML link for the item's imagethumburl attribute. 

${item.price?.encodeAsHTML()}
displays the value of the  item 's price attribute as escaped HTML text.

<g:paginate total="${Item.count()}" />
The paginate tag creates next/previous buttons and a breadcrumb trail to allow pagination of results using the Item.count() domain method.

The Show Action Method

In Grails the mapping for the URL http://host/item/show/1  ( http://host/controller/action/id )  will route to the show action in the ItemController passing 1 to the method as the id of the params parameter hash. The show action of the ItemController class is shown below. The ItemController show action renders a view showing the details of the item object corresponding to the id parameter.

Code Sample from: grails-app\\controllers\\ItemController.groovy

 def show = {
   def item = Item.get( params.id )

   if(!item) {
      flash.message = "Item not found with id ${params.id}"
      redirect(action:list)
   }
   else { return [ item : item ] }
 }



The show action method  calls the Item.get() method which queries the items table returning the item instance variable corresponding to the item with the attribute id (primary key) equal to the  id parameter. This is the equivalent of the following sql : select \* from items where id='1' . The item variable is automatically made available to the Show view by the framework.

The Show View GSP

After executing code in the action, the show action renders the app/views/item/show.gsp . Below is the GSP for the item show view :


Code Sample from: grails-app\\views\\item\\show.gsp

<h2> Detail of item</h2>

<table>
<tbody>
    <tr class="prop">
      <td valign="top" class="name">Name:</td>                          
      <td valign="top" class="value">${item.name}</td>                          
    </tr>                   
    <tr class="prop">
        <td valign="top" class="name">
           Description:
        </td>                           
        <td valign="top" class="value">
           ${item.description}
        </td>                          
    </tr>                       
    <tr class="prop">
        <td valign="top" class="name">Imageurl:</td>                          
        <td valign="top" class="value">
            <img src="${createLinkTo(dir:'images',file:item.imageurl)}" />
        </td>                           
    </tr>                                           
    <tr class="prop">
        <td valign="top" class="name">Price:</td>                           
        <td valign="top" class="value">$ ${item.price}</td>                           
    </tr>
    <tr class="prop">
        <td valign="top" class="name">Address:</td>                           
        <td valign="top" class="value">
          ${item?.address?.street1}, ${item?.address?.city},    
          ${item?.address?.state}
        </td>                          
    </tr>                       
</tbody>
</table>  



${item.description}
displays the value of the  item 's description attribute.
<img src="${createLinkTo(dir:'images',file:item.imageurl)}" />
generates an HTML image tag for the item's imageurl attribute.
${item?.address?.city}
displays the value of the  item's address city attribute.

The image below shows the resulting page for the url http://host/catalog/item/show/105, which displays the item 105's details:

showpet.jpg

Layouts

Grails layouts  let you put common html on multiple views (for example page headers,  footers, sidebars).  Default layout templates are in the views layouts directory with a file name corresponding to the controller, or you can associate a view with a layout using the "layout" meta tag to your page:
<meta name="layout" content="main">
To add a title and parrot image to the top of the Pet Catalog pages, I put this table in the app\\views\\layouts\\main.gsp  layout:

Code Sample from: app/views/layouts/main.gsp

<table>
  <tr>
   <td>Pet Catalog</td>
   <td>
     <img src="${createLinkTo(dir:'images',file:'pet_logo.jpg')}"/>
   </td>
 </tr>
</table>




Conclusion
This concludes the sample application which demonstrates how to work with Groovy and Grails  to page through a list of  Item JPA Entities which are retrieved using Item Controller action methods, and displayed using Item View GSPs.

Setting Things Up and Running the Sample code on MySQL and Jetty:

  1. If MySQL is already installed, then download GlassFish v2 UR1. Otherwise you can also Download GlassFish v2 UR1 and MySQL co-bundle from the usual Download Page (instructions).

  2. Download and install Grails.

  3. Download the sample code and extract its contents. You should now see the newly extracted directory as <sample_install_dir>/Catalog, where <sample_install_dir> is the directory where you unzipped the sample package. For example, if you extracted the contents to C:\\ on a Windows machine, then your newly created directory should be at C:\\Catalog
    The file  "/Catalog/grails-app/conf/DataSource.groovy" is configured for a MySQL configuration.

  4. Start the MySQL database as follows:

    • > mysqld_safe --user root --console

  5. Create the pet-catalog database:

    • > mysqladmin create petcatalog --user root

  6. Create the tables in the MySQL pet-catalog database as follows:

    • shell> mysql pet-catalog < catalog.sql
    • using the file catalog.sql file from the /Catalog directory.

  7. Run the project as follows:
    in a command window in the /Catalog directory enter the command
  8. > grails run-app
    This will run the Application using the built-in Jetty Servlet engine.
When you run the project, your browser should display the Catalog home page at http://localhost:8080/catalog/ .


Run the Sample code on Glassfish:
  1. Use the  WAR file in <sample_install_dir>/Catalog/Catalog.war or Create a WAR file:
    • > grails war
  2. Copy the WAR file (catalog-0.1.war) to  your Glassfish installation "domains/domain/autodeploy" directory. (Start Glassfish and MySQL if you haven't already)

  3. Enter the URL  http://localhost:8080/catalog-0.1/  in your browser, you should see the home page of the Sample Application.

For more information:

About

caroljmcdonald

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today