PlanetMysql.ru – информация о СУБД MySQL

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. 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.
• Can be easily added in "WEB-INF/lib" directory of your web application.

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

PlanetMySQL Voting: Vote UP / Vote DOWN

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';
      FLUSH PRIVILEGES;
      

   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 UNLIMITED TABLESPACE TO glassfish;
   GRANT CREATE TABLE TO glassfish;
   GRANT CREATE SEQUENCE 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:

• RESTful representation of sakila using NetBeans and GlassFish

• JPA + Servlet 3.0 Application using Sakila in Eclipse

• JSF + JPA + EJB Application using Oracle, NetBeans, and GlassFish

Technorati: totd javaee glassfish v3 jpa mysql sakila oracle

PlanetMySQL Voting: Vote UP / Vote DOWN

TOTD #97 showed how to install GlassFish Tools Bundle for Eclipse 1.1. Basically there are two options - either install Eclipse 3.4.2 with WTP and pre-bundled/configured with GlassFish v2/v3, MySQL JDBC driver and other features. Or if you are using Eclipse 3.5, then you can install the plug-in separately and get most of the functionality.

TOTD #98 showed how to create a simple Metro/JAX-WS compliant Web service using that bundle and deploy on GlassFish.

This Tip Of The Day (TOTD) shows how to create a simple Java EE 6 application that reads data from a MySQL database using JPA 2.0 and Servlet 3.0 and display the results. A more formal support of Java EE 6/Servlet 3.0 is coming but in the meanwhile the approach mentioned below will work.

Lets get started!

1. Configure database connection - The key point to notice here is that the MySQL Connector/J driver is already built into the tool so there is no need to configure it explicitly.
   1. From "Window", "Show Perspective", change to the database perspective as shown below:
      
      
   2. In the "Data Source Explorer", right-click and click on "Database Connections" and select "New ...":
      
      
   3. Search for "mysql" and type the database name as "sakila":
      
      
      
      This blog uses MySQL sample database sakila. So please download and install the sample database before proceeding further.
   4. Click on "Next >" and specify the database configuration:
      
      
      
      Notice the "Drivers" indicate that the JDBC driver is pre-bundled so there is no extra configuration required. If you are using a stand-alone Eclipse bunde and installing the plugin separately, then you need to configure the MySQL JDBC driver explictily.
      
      The URL indicates the application is connecting to the sakila database. Click on "Test Connection" to test connection with the database and see the output as:
      
      
      
      and click on "Finish" to complete. The expanded database in the explorer looks like:
      
      
      
      The expanded view shows all the tables in the database.
2. Create the Web project & configure JPA
   1. Switch to JavaEE perspective by clicking "Window", "Choose Perspective", "Other ..." and choosing "Java EE".
   2. Create a new dynamic web project with the following settings:
      
      
      
      Only the project name needs to be specified and everything else is default. Notice the target runtime indicates that this is a Java EE 6 application. Click on "Finish".
   3. Right-click on the project, search for "facets" and enable "Java Persistence" as shown below:
      
      
   4. Click on "Further configuration available ..." and modify the facet as shown below:
      
      
      
      Make sure to disable "orm.xml" since we are generating a standard Java EE 6 web application. Choose "sakila" as the database. Click on "OK" and again on "OK" to complete the dialog.
3. Generate the JPA entities
   1. Right-click on the project, select "JPA Tools", "Generate Entities" as shown:
      
      
   2. Choose the schema "sakila":
      
      
      
      and click on "Next >". If no values are shown in the schema drop-down, then click on "Reconnect ...".
   3. Specify a package name for the generated entities as "model" and select "film" and "language" table:
      
      
      
      and click on "Finish". The "film" and "language" table are related so it would be nice if all the related tables can be identified and picked accordingly.
      
      Anyway this generates "model.Film" and "model.Language" classes and "persistence.xml" as shown below:
      
      
      
      Also notice that "web.xml" and "sun-web.xml" have been explicitly removed since they are not required by a Java EE 6 application.
   4. "model.Film" class needs to modified slightly because one of the columns is mapped to "Object" which is not a Serializable obect. So change the type of "specialFeatures" from Object to String and also change the corresponding getters/setters accordingly. The error message clearly conveyed during the initial deployment and so could be fixed. But it would be nice to generate the classes that will work out-of-the-box.
4. Create a Servlet client to retrieve/display data from the database
   1. Right-click on the project, select "New", "Class" and specify the values as:
      
      
      
      and click on "Finish". This class will be our Servlet client.
   2. Change the class such that it looks like:
      

      @WebServlet(urlPatterns="/ServletClient")
      public class ServletClient extends HttpServlet {
        @PersistenceUnit
        EntityManagerFactory factory;
      
        protected void doGet(HttpServletRequest req, HttpServletResponse resp)
               throws ServletException, IOException {
          ServletOutputStream out = resp.getOutputStream();
          List list = factory.createEntityManager().createQuery("select f from Film f where f.title like 'GL%';").getResultList();
          out.println("<html><table>");
          for (Object film : list) {
            out.print("<tr><td>" + ((Film)film).getTitle() + "</tr></td>");
          }
          out.println("</table></html>");
        }
      }
      

      
      and the imports as:
      

      import java.io.IOException;
      import java.util.List;
      
      import javax.persistence.EntityManagerFactory;
      import javax.persistence.PersistenceUnit;
      import javax.servlet.ServletException;
      import javax.servlet.ServletOutputStream;
      import javax.servlet.annotation.WebServlet;
      import javax.servlet.http.HttpServlet;
      import javax.servlet.http.HttpServletRequest;
      import javax.servlet.http.HttpServletResponse;
      
      import model.Film;
      
      

      Basically, this is a Servlet 3.0 specification compliant Servlet that uses @WebServlet annotation. It uses @PersistenceUnit to inject the generated JPA Persistence Unit which is then used to query the database. The database query return all the movies whose title start with "GL" and the response is displayed in an HTML formatted table.
   3. Right-click on the project and select "Run As", "Run on Server" and select GlassFish v3 latest promoted build (this blog used build 61) as:
      
      
      
      and click on "Finish". The output at "http://localhost:8080/HelloJPA/ServletClient" looks like:
      
      

Simple, easy and clean!

How are you using Eclipse and GlassFish - the consolidated bundle or standalone Eclipse + GlassFish plugin ?

Download GlassFish Tools Bundle for Eclipse now.

Technorati: glassfish eclipse mysql jpa database

PlanetMySQL Voting: Vote UP / Vote DOWN