Tuesday Feb 09, 2010

TOTD #122: Creating a JPA Persistence Unit using NetBeans 6.8

Taking TOTD #121 forward, this blog explains how to create a JPA Persistence Unit for a MySQL sample database and package it as a library. This JAR file can then be easily included in other web applications.

Lets get started!

  1. Configure GlassFish for using the MySQL sample database (sakila) as described in TOTD #121.
  2. Add the GlassFish instance in NetBeans IDE using "Services" panel.
  3. Create JPA entities using NetBeans IDE.
    1. Create a Java class library:

      Our ultimate goal is to create a reusable JAR file and that's why this project type is chosen.
    2. Specify the name of project as "SakilaPU":

    3. Right-click on the project and select "New", "Entity Classes from Database ..." to initiate the process of entity generation:

    4. Choose the database connection as:

      If not configured, then can be easily done by clicking on "New Database Connection ..." in the list box.
      1. Click on "Add All >>" to generate the mapped JPA entities for all tables and views.
      2. The views do not have primary keys and will need to be appropriately annotated (described later).
      3. Click on "Next >".
    5. Give the package name as:

      and specify the package name as "sakila". Click on "Create Persistence Unit ...".
    6. Change the default PU name from "SakilaPUPU" to "SakilaPU":

      and click on "Finish". Notice that "EclipseLink", the Reference Implementation of JPA 2.0, is used as the persistence library.
    7. Add "@javax.persistence.Id" annotation to the following class/field combination:
      Class Field
      sakila.SalesByFilmCategory category
      sakila.ActorInfo actorId
      sakila.FilmList fid
      sakila.CustomerList id
      sakila.NicerButSlowerFilmList fid
      sakila.StaffList id
      sakila.SalesByStore store

      This is required because none of the "views" are defined with a primary key.
    8. Right-click on the project and select "Clean & Build". This generates "dist/SakilaPU.jar" and the structure looks like:

This JAR file can now be included in any web application. The pre-built JAR file can also be downloaded here.

In order for this PU to be used in an application server (such as GlassFish) that is pre-configured with a JDBC resource, the "persistence.xml" needs to be changed to:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
 <persistence-unit name="SakilaPU" transaction-type="JTA">

The JDBC resource name is specified using <jta-data-source>.

The key items to note about this pre-built JAR:

  • Persistence Unit Name: "SakilaPU"
  • All classes are in "sakila.\*" package.
  • Each class has a pre-defined "<CLASS-NAME>.findAll" named query that returns all elements from the underlying view/table.

This JAR can be installed to your local Maven repository as:

mvn install:install-file -Dfile=SakilaPU.jar -DgroupId=org.glassfish.samples -DartifactId=sakilapu -Dversion=1.0 -Dpackaging=jar -DgeneratePom=true

and then included in your "pom.xml" as:


Even though this blog uses a MySQL sample database, these steps can be easily followed for any other database such as Oracle or JavaDB.

Technorati: totd javaee glassfish v3 jpa eclipselink persistenceunit mysql sakila netbeans

Monday Feb 08, 2010

TOTD #121: JDBC resource for MySQL and Oracle sample database in GlassFish v3

This blog clearly explains how to configure the MySQL sample database (sakila) with GlassFish. Even though the instructions use a specific database but should work for other databases (such as Oracle, JavaDB, PostgreSQL, and others) as well. The second half of the blog provide specific syntax for the Oracle sample database.

  1. Download sakila sample database and unzip the archive.
  2. Install the database as described here - basically load and run "sakila-schema.sql" and "sakila-data.sql" extracted from the archive.
  3. Create a new MySQL user account using MySQL CLI Admin and assign the privileges
    1. Using "root" user (sudo mysql --user root)
      CREATE USER glassfish IDENTIFIED BY 'glassfish';
      GRANT ALL PRIVILEGES ON \*.\* TO 'glassfish'@'localhost' IDENTIFIED BY 'glassfish';
    2. Using "glassfish" user (sudo mysql --user glassfish)
      source sakila-schema.sql;
      source sakila-data.sql;
  4. Download Connector/J, unzip and copy "mysql-connector-java-5.x.x-bin.jar" to "glassfish/domains/domain1/lib/ext" directory.
  5. Start GlassFish server as:
    asadmin start-domain
  6. Create a JDBC resource
    1. Create JDBC connection pool as:
      asadmin create-jdbc-connection-pool --datasourceclassname com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource --restype javax.sql.DataSource --property "User=glassfish:Password=glassfish:URL=jdbc\\:mysql\\://localhost/sakila" jdbc/sakilaPool
    2. Test the JDBC connection pool as:
      asadmin ping-connection-pool jdbc/sakilaPool
    3. Create the JDBC resource as:
      asadmin create-jdbc-resource --connectionpoolid jdbc/sakilaPool jdbc/sakila

That's it!

Creating a JDBC resource for any other database requires the following updates to the steps mentioned above. Lets consider modifying these steps for the Oracle sample database.

  1. Use the client interface SQL\*PLus and connect as:
    sqlplus "/ as sysdba"

    create user and grant the privileges as:
    CREATE USER glassfish IDENTIFIED BY glassfish DEFAULT tablespace users TEMPORARY tablespace temp;
    GRANT CONNECT TO glassfish IDENTIFIED BY glassfish;
    GRANT CREATE TABLE TO glassfish;
  2. Copy the appropriate JDBC driver (ojdbc6.jar).
  3. Create the JDBC resource as:
    asadmin create-jdbc-connection-pool --datasourceclassname oracle.jdbc.pool.OracleDataSource --restype javax.sql.DataSource --property "User=hr:Password=hr:URL=jdbc\\:oracle\\:thin\\:@localhost\\:1521\\:orcl" jdbc/hr
    asadmin ping-connection-pool jdbc/hr
    asadmin create-jdbc-resource --connectionpoolid jdbc/hr jdbc/hr

    as explained in TOTD #108.

Here are a few other related entries:

Technorati: totd javaee glassfish v3 jpa mysql sakila oracle

Wednesday Jan 23, 2008

RESTful representation of "sakila" using GlassFish and NetBeans IDE

"sakila" is the sample database shipped with MySQL (pronounced as my ess-kew-ell). In the context of Sun Microsystems announcing the agreement to acquire MySQL, I'd like to dedicate this entry to show how this sample database can be exposed as a RESTful Web service endpoint and deployed on GlassFish using Jersey Tooling Plugin (0.4.1 with Jersey 0.4) in NetBeans IDE.

Lets get started!

  1. Install MySQL & the sample database "sakila".
    1. Download and Install MySQL Community Server.
    2. Download sakila sample database.
    3. Install the database as described here.
    4. Start MySQL database by giving the command 'mysqld-nt --user root --console' in bin directory on Windows or './bin/mysqld_safe' from MySQL directory on Unix flavors.
  2. Create the Project & Database Connection
    1. In NetBeans IDE, create a new Web project and name it as "sakila". Choose "GlassFish v2" as the "Server:".
    2. In the "Services" tab of NetBeans IDE, expand "Drivers" and add MySQL Connector/J driver if it does not exist already.
    3. Create a new new database connection by right-clicking on "Drivers" and specifying the parameters as shown below:

  3. Create the Persistence Unit
    1. Right-click on the project and select "New", "Entity Classes from Database...". In "Data Source", select "New Data Source..." and specify the values as shown below:

    2. Click on "film" in "Available Tables" and click on "Add >" as shown below:

      Click on "Next >".
    3. Click on "Create Persistence Unit..." and take all the defaults as shown below:

      Click on "Create".
    4. Enter the package name as "sakila" as shown below:

      and click on "Finish".
    5. In the NetBeans project explorer, expand "Configuration Files" and open "persistence.xml". Specify the username and password by replacing <properties/> with the following fragment:

        <property name="toplink.jdbc.user" value="root"/>
        <property name="toplink.jdbc.password" value=""/>

      Make sure to match the username and password to your MySQL installation.
  4. Create RESTful Web service endpoint
    1. In NetBeans IDE, click on "Tools", "Plugins", "Available Plugins", "RESTful Web Services" and then click on "Install". This installs the Jersey Tooling Plugin in the IDE.
    2. Right-click on the project, select "New", "RESTful Web Services from Entity Classes...".
    3. Click on "Add >>", take all other defaults as shown below:

      click on "Next >", take all defaults and then "Finish".
  5. Test RESTful Web Services
    1. Right-click on the project and select "Test RESTful Web Services". The following web page is presented in the browser:

    2. Click on "films" and then on "Test" as shown below:

      Clicking on "Test" button or the URL "http://localhost:8080/sakila/resources/films/" shows the RESTful representation of the "Film" table. The default representation shows 10 records from the table where each entry returns the "id" of the film and a reference to the detailed entry.

      You can view more entries (say 40) by giving the URL "http://localhost:8080/sakila/resources/films/?max=40". Additional fields from the table can be displayed by adding getter methods to "converter.FilmRefConverter" class such as:

      public String getTitle() {
        return entity.getTitle();

      to return the film title in addition to the fields already returned. The different columns in the table can be viewed by going to the "Services" tab, expanding the sakila database connection created earlier as shown below:

      The modified output (with film title included) looks as shown below:

Here are few more ideas for you to explore:

  • Create RESTful representations of other tables using the steps described above.
  • Display the data from different tables in a jMaki-wrapped Yahoo or Dojo data table as explained in TOTD #10.
  • Display the data retrieved from the database in a JSP page as described in Hello JPA World.
  • Create a CRUD application using jMaki Data Table as described in TOTD #15 or Screencast #Web10.

A JRuby-on-Rails application using MySQL is explained here. TOTD #9 explains how JDBC connection pooling in GlassFish can be used for a JRuby-on-Rails application using MySQL.

The key message here is MySQL can be very easily used with GlassFish and NetBeans IDE makes it possible! Once MySQL becomes part of Sun, this integration is going to be much more seamless for the betterment of community.

All the entries on this blog using MySQL can be found here. And last but not the least, Welcome aboard MySQL!

A NetBeans project with all the source code can be downloaded from here. You will still need to setup the database connection and need to make sure the correct version of Jersey plug-in as well :)

Technorati: glassfish netbeans jersey mysql sakila jpa jmaki rubyonrails


profile image
Arun Gupta is a technology enthusiast, a passionate runner, author, and a community guy who works for Oracle Corp.

Java EE 7 Samples

Stay Connected


« August 2016