Skip navigation

JPA Performance, Don't Ignore the Database

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.


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

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

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

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

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.


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 Shardsis a framework that is designed to encapsulate support for horizontal partitioning into the Hibernate Core.


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

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 == 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,
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.

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: 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:outputStylesheettag to load the styles.css stylesheet , and the<h:graphicImagetag to display images from the resources as shown below:

Code Sample from:  list.xhtml

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

<h:graphicImagelibrary="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: 

Title=Pet Catalog

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


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 rendererdisplays 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 formy code or variables)

Code Sample from:  list.xhtml

<h:dataTablevalue='#{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.jsppage, 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 dataTableItempoints to the current item in the list.

JSF 2.0 Annotations instead of XML configuration

The dataTable's value is bound to the itemsproperty 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:

  public class Catalogimplements 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 CatalogManagedBean itemsproperty is defined as shown below:

Code Sample from:

    private List<Item> items = null;

    public List<Item> getItems() {
        if (items == null) {
            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 Itemproperties name, imagethumburl, andprice  are displayed with the columncomponent:

Code Sample from: list.xhtml

<h:dataTablevar="row" value="#{catalog.items}">
      <f:facet name="header">
          <h:outputText value="#{msgs.Name}"/>
      <h:commandLink action="#{catalog.showDetail(row)}" value="#{}" />
      <f:facet name="header">
          <h:outputText value="#{msgs.Photo}"/>
<h:graphicImage library="images" name="#{row.imagethumburl}"/>

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

The columntags 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 eachcolumntag 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 CatalogManagedBean annotates the field private ItemFacade itemFacade;  with @EJB , which causes an itemFacadeEJB to be injected when the managed bean is instatiated. TheCataloggetNextItemsmethod calls the ItemFacadeStateless EJB which uses the Java Persistence API EntityManager Query object to return a list of items.

Code Sample from:

  public class Catalogimplements Serializable {

    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 ItemFacadeEJB uses the Java Persistence API EntityManager Query object to return a list of items. The ItemFacadeEJB annotates the field private EntityManagerem;  with @PersistenceContext , which causes an entity manager to be injected when it is instatiated.

Code Sample from:

public class ItemFacade{

    @PersistenceContext(unitName = "catalogPU")
    private EntityManagerem;

    public List<Item> findRange(int maxResults, int firstResult) {
        Query q = em.createQuery("select object(o) from Item as o");
        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 Itementity 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 @Entityannotation.
  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 Addressand Productare also annotated. For more information on defining JPA entities see Pro EJB 3: Java Persistence API book.

Code Sample from:

public class Item implements {

    private Integer id;

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

    public Item() { }

    public String getName() {
        return name;

    public void setName(String name) { = name;


The CatalogManagedBean 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 commandButtonis  used to provide a button to click on to display the next page of items.  The commandButtontag is used to submit an action event to the application. 

Code Sample from: list.xhtml

 <h:commandButtonaction="#{}" value="#{msgs.Next}" />   

This commandButton action attribute references the catalogManaged 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 catalognextmethod is defined as shown below:

Code Sample from:

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

JSF 2.0 Simplified Navigation

The JavaServer Faces 2.0  NavigationHandlerconvention 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 commandLinkis  used to provide a link to click on to display a page with the item details. This commandLink actionattribute  references ThecatalogshowDetail()method:

Code Sample from: list.xhtml

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

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 thedetail.xhtml page to display the item details :

Code Sample from:

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

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

The detail.xhtml uses the outputTextcomponent to display the catalogManagedBean's itemproperties:

Code Sample from: detail.xhtml

    <h:outputTextvalue="#{}" 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="#{}" title="Address" />
    <h:outputText value="#{}" 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

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 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 thebin 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 thecatalog 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 petcatalogdatabase in the Services window. You should see theitem table under the Tables node. You can expand theitem 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 thecatalog  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 thecatalog 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:

Filter Blog

By date: