< Insert, Update, Delete & JPA (Derby) | Java & Friends | Insert, Update, Delete & JPA (MySQL) >

 


 

 

Java Web Application:

Using JPA within a Visual Web JSF Application Part 1

(MySQL database)

 

 

Contents:

  1. Creating a Database Using MySQL Command Line Client Tool

  2. Making a Connection to the Database Using NetBeans

  3. Creating a Table and Populating a Sample Data

  4. Linking a Visual Web Project to a Java Standard Edition Project

  5. Creating the Projects

  6. Linking the Projects Together

  7. Binding to a Database Table

  8. Connecting to the Database

  9. Creating a Java Persistence Entity Class Representing the Users Database Table

  10. Creating a Persistence Unit

  11. Setting Key Attributes With Java Persistence Annotations

  12. Creating the Classes

  13. Creating the Entity Controller Class

  14. Binding the Entity Bean to the Table Component

  15. Creating a Property in the Session Bean

  16. Creating the Initialization Method

  17. Binding the Property to the Table Component

  18. Deploy and Running the Program

  19. Doing Some Customization

This tutorial provides the Java web application using JPA in JSF application that connected to MySQL database. It is a modified version of the same tutorial using Java Derby database.

 

Creating a Database Using MySQL Command Line Client Tool

 

First of all let create a database named student using MySQL Command Line Client. Execute the following SQL statement.

 

CREATE DATABASE student;

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Then, verify the database creation by issuing show database; statement. Please exit MySQL Command Line Client and launch NetBeans.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Making a Connection to the Database Using NetBeans

 

The Users table should have five columns, as follows (you can remove the photo Column, because this column will not be used in this tutorial):

 

 

Create a new database connection. Services window > expand Database node > expand the Driver node > select MySQL (Connector/J driver) > right click mouse > select Connect Using context menu.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

 

Fill in the Database URL:, database User Name: and its Password:. Select Remember password tick box and click OK. In this case the database’s user is root.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

The following Figure shows that the connection to student database was established. Click OK.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Expand the student database connection node. Well, this re-confirm the database connection.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Creating a Table and Populating a Sample Data

 

Next, we would like to create a table and populate the table with some sample data. Select the connection node > right click mouse > select Execute Command. This will launch SQL query editor.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Copy, paste and execute the following SQL script to create a Users table. You can remove the photo column/field because we will not use this column for this tutorial.

 

-- create a table named Users

CREATE TABLE Users(

studentId INTEGER(10) NOT NULL AUTO_INCREMENT,

name VARCHAR(50),

email VARCHAR(30),

contactNo VARCHAR(30),

photo BLOB,

PRIMARY KEY (studentId)

)type=innodb;

 

Note: We include the BLOB data type just for a demonstration. This BLOB data type can be used to store binary such as images, files etc. in the table.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Optionally, if you want to start the index from 8700 for example, execute the following script.

 

ALTER TABLE Users AUTO_INCREMENT = 8700;

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Verify our previous task by expanding the Tables node.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Next, insert some sample data by executing the following SQL script. You need to have some images that stored in the C:\butterfly folder for this script to be executed properly if you include the photo column/field in the previous step. Change the path and the image files name accordingly. These images will be used in another tutorial.

 

-- insert sample data

INSERT INTO Users(name, email,contactNo,photo)

 VALUES ('Stephanie Lame', 'stephanie@aol.com', '808-2341567', LOAD_FILE('C:\\butterfly\\butterfly1.jpg'));

INSERT INTO Users(name, email,contactNo,photo)

 VALUES ('Meg Ryan', 'ryan@gmail.com', '808-7681569', LOAD_FILE('C:\\butterfly\\butterfly2.jpg'));

INSERT INTO Users(name, email,contactNo,photo)

 VALUES ('Jodie Foster', 'foster@yahoo.com', '808-2348710', LOAD_FILE('C:\\butterfly\\butterfly3.jpg'));

INSERT INTO Users(name, email,contactNo,photo)

 VALUES ('Amar Maarof', 'amar@gmail.com', '604-7364518', LOAD_FILE('C:\\butterfly\\butterfly4.jpg'));

INSERT INTO Users(name, email,contactNo,photo)

 VALUES ('Alif Nijar', 'nijar@aol.com', '705-4452345', LOAD_FILE('C:\\butterfly\\butterfly5.jpg'));

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Verify our data insertion by using View Data context menu. Select Users table node > right click mouse > select View Data context menu.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Well, all looks fine until now.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

The following shows the example of an index that start from 8700.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Linking a Visual Web Project to a Java Standard Edition Project

 

To use the Java Persistence API from a Visual Web application, you actually create two projects. One project is your Visual Web project. The other is a Java Standard Edition (SE) application project that includes several classes whose code the web application will call. From within this Java SE application project, you generate a persistence unit to get a handle to the Java Persistence API. Your web application then uses the classes created in the Java SE application project to establish the database binding, and later to do the database update, delete, and add operations.

 

Creating the Projects

  1. Create the Java SE application project. Begin by creating the Java SE application project, which is a general Java project. Click the New Project icon in the toolbar (Ctrl+Shift+N), then select Java in the Categories column and Java Application in the Projects column. Then click Next.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

  1. In the New Java Application screen, set the project name to MyTestModelApp. You also need to set the package to com.samples.model. You can do this by changing the default Create Main Class entry from mytestmodelapp.Main to com.student.model.Main. Click Finish.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

  1. Create the Visual Web project. From the New Project dialog, select Web as the category and Web Application as the project, then click Next.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

  1. In the New Web Application dialog, set the project name to MyTestWebApp. The project location should default to the same location as the MyTestModelApp project else you need to browse the MyTestModelApp project. Click Next to go to the screen where you select the framework.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

  1. Select Visual Web JavaServer Faces for the framework. When you make this selection, the screen displays configuration settings. Change the Default Java Package setting from mytestwebapp to com.student.web. After completing this step, MyTestModelApp and MyTestWebApp appear as nodes in the Project window. Click Finish.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Linking the Projects Together

 

Now you want to link the two projects together. Specifically, you want the MyTestModelApp project to be a dependency of the MyTestWebApp project. To make MyTestModelApp a dependent project of MyTestWebApp, you need to add the MyTestModelApp.jar file to the MyTestWebApp project. Here's how to do this:

 

1.      In the Projects window, right Click the MyTestWebApp project node and select Properties from its context menu.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

2.      In the Project Properties dialog, click the Libraries node in the Categories section on the left. Then, click Add Project.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

3.      In the Add Project window, browse to the location with the MyTestModelApp project and select it. Then click Add Project JAR Files. This step adds the MyTestModelApp jar file to the compile-time libraries for the MyTestWebApp project, and MyTestModelApp appears in the Project Properties screen. Click OK in the Project Properties screen to complete the process.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Binding to a Database Table

 

You use the Java Persistence API to convey information back and forth from a database table to the table visual component that's used in the Visual Web JSF application. After you do this binding, you use the API to retrieve rows from the database table, but you display the data using the Visual Web Table component's built-in display features.

 

Connecting to the Database

 

To begin, you connect to the database with the Users table from within the MyTestWebApp project. If you have not yet connected to it, open the Services window, expand the Databases node, open the context menu for the jdbc:mysql://localhost:3306/student[root on Default Schema] node and choose Connect. If you are using a different DBMS, then, if needed, set up a driver for that database and create a new connection, providing the necessary connection parameters. We already completed this task.

From the Services window, verify that the connection to the database is established. Open the Databases > student database Tables node and verify that the users table is correct.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

 

Creating a Java Persistence Entity Class Representing the Users Database Table

 

Create an entity class representing the Users table. As noted previously, the entity class is the Java Persistence representation of the database table definition. JPA uses Java language annotation feature to mark Plain Old Java Objects (POJOs) as JPA entities with information on object-relation mapping. Create the entity class from within the TestModelApp project using the Entity Classes from Database function.

 

1.      In the Projects (or Files) window, right click the MyTestModelApp project. From the context menu, select New > Entity Classes from Database.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

2.      The New Entity Classes from Database Database Tables dialog displays. If the Database Connection field is blank, then select the student database from the pull-down list. The Available Tables column displays all the tables in the sample database, including the users table. Select the users table and click Add to move users to the Selected Tables column. Click Next after moving the users table.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

3.      The Entity Classes dialog displays. The IDE displays the database table name users, and suggests a class name of Users. (Double click this class name to change it, if you want.) The dialog also indicates that the package is com.student.model. Click Create Persistence Unit.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Creating a Persistence Unit

 

1.      In the Create Persistence Unit dialog, set the Persistence Unit Name to studentPU. Leave the other fields with the default values. Click Create, then click Finish in the Entity Classes screen to complete the operation and create the studentPU persistence unit.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

2.      It is a good idea to verify that the persistence unit is created correctly. To do so, expand the MyTestModelApp Source Packages > META-INF node and double click the persistence.xml file. The Design window displays information about the persistence unit, while the Navigator window shows the XML properties.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

3.      Click the XML tab to see the complete XML listing. The properties in the file should correctly reflect the database name, its url, driver, and password, along with the package and class name (com.student.model.Users). The transaction type is RESOURCE_LOCAL and the provider is oracle.toplink.essentials.PersistenceProvider. Important: Be sure that the password field is filled in correctly.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Setting Key Attributes With Java Persistence Annotations

 

If you are working with the Derby database, you need to make some modifications to the generated Users.java code to handle auto generation of the primary key value. However, other database systems may handle primary key value generation differently, and any modifications you make to the generated Users.java class must match the underlying database's primary key generation policies. In this case we are using MySQL so we will skip this step. If we fail later, we will come back to this step.

 

Creating the Classes

 

Creating the Entity Controller Class

 

You continue to work in the MyTestModelApp project, creating a new class called UserController in the com.student.model package. (We provide the code for this class below, and you can paste it into your class.) Expand the MyTestModelApp > Source Packages > com.student.model.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Notice that there are already two classes in the package: Main.java and Users.java. Right click the com.student.model node and select New > Java Class.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

In the New Java Class dialog, set the class name to UserController (leave the location as Source Packages and the package name as com.student.model). Click Finish.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

You should see the skeleton source code for this new class in the Edit window.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Add the following code to the class:

private EntityManagerFactory emf;

 

    private EntityManager getEntityManager() {

        if (emf == null) {

            emf = Persistence.createEntityManagerFactory("studentPU");

        }

        return emf.createEntityManager();

    }

 

    public Users[] getUsers() {

        EntityManager em = getEntityManager();

        try {

            javax.persistence.Query q = em.createQuery("select c from Users as c");

            return (Users[]) q.getResultList().toArray(new Users[0]);

        } finally {

            em.close();

        }

    }

 

Use the Fix Imports function to import the required classes. (Right click in the source editor window and select Fix Imports from the pop-up menu.)

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Doing so adds the following three import statements to the class:

 

import javax.persistence.EntityManager;

import javax.persistence.EntityManagerFactory;

import javax.persistence.Persistence;

 

You can use the Format function, available from the same pop-up menu, to format the code alignment.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Notice that the UserController class includes the method getUsers, which invokes the createQuery method on the javax.persistence.EntityManager interface to create a query that returns the rows of data from the Users table. The results of the query are returned as an array.

 

Binding the Entity Bean to the Table Component

 

You are ready to bind the entity component for accessing the Users database table to a Table component in the MyTestWebApp project. (Recall that you created the entity bean in the TestModelApp.) You do this binding from within the MyTestWebApp project. Here are the binding steps:

 

 

Let's examine these steps in more detail.

 

Creating a Property in the Session Bean

 

In the NetBeans IDE, you can find managed beans in several locations: in the Navigator window, in the source packages node in the Projects window, and in the src > java > project-name node in the Files window.

 

1.      First, create a property in SessionBean1 that returns an array of users objects. In the Navigator window, double click SessionBean1 to open it in the Java Source Editor. Or, double click SessionBean1 from within the Projects window MyTestWebApp > Source Packages > com.student.web node. If you do not see the Navigator window, try displaying a page from TestWebApp, such as Page1, in the Design window. The Navigator window should display beneath the Projects window. If you still do not see the Navigator window, click the Design tab in the Design window.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

2.      Add a property called users to SessionBean1. Type in the following line of code: private Users[] users;

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

3.      Fix imports. Most likely, the line you just entered will be marked as an error. If so, use the source editor's context menu Fix Imports function to fix this error. (Be sure to correct this error before continuing to the next step.) To fix the error, the IDE adds the following import statement: import com.samples.Users;

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

4.      Generate get and set methods for the users property. Right click the line of code you typed in and select the action Insert Code. Choose Getter and Setter from the popup menu. Then, select users: Users[ ].

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

-----------------------------------------------------------------------------------------------------------------------------

 

-----------------------------------------------------------------------------------------------------------------------------

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

The Insert Code action adds public get and set methods. When finished, SessionBean1 should include the following code.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Creating the Initialization Method

 

1.      Add a method called updateUsers to SessionBean1. This method will be used to initialize the users property. Here's the code for the method:

 

public void updateUsers(){

     UserController usersController = new UserController();

     users = usersController.getUsers();

      } 

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

2.      Use the Fix Imports function to fix the import needed for updateUsers. If there are errors in the code, do a Save All files to clear them.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

3.      Add a call to the updateUsers method in the SessionBean1 init method: updateUsers();. The SessionBean1 init method should look as follows (comment lines not shown):

 

public void init(){

     super.init();

     try {

          _init();

     } catch (Exception e) {

          log("SessionBean1 Initialization Failure", e);

          throw e instanceof FacesException ? (FacesException) e: new FacesException(e);

     }

     updateUsers();

 }

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

4.      Save all the files.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

5.      Build both the TestModelApp and TestWebApp projects.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Binding the Property to the Table Component

 

Now you are ready to bind the users property you just added to a Table component.

 

1.      From MyTestWebApp > Web Pages, double click Page1.jsp to open the page in the Design window.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

2.      Drag a Table component from the Palette and drop it on the page in the Design window. It should as follows:

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

3.      Right click the Table component on the page and click Bind to Data from its context menu.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

4.      In the Get Data From drop down list, select users (from SessionBean1) as the binding array. (Note that if you do not see the users property displayed in the drop down list, right click in the Design window and click the Refresh option in the context menu. Or simply click the Refresh button in the toolbar. If you still do not see the users property displayed in the drop down list, then close and reopen the TestWebApp project.) Click OK.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

5.      The Table component display in the Design window should change to the following. If needed, adjust the columns to be displayed.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Deploy and Running the Program

 

1.      Deploy and run the TestWebApp project.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Notice the progress in the Output window.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Next, run the web application. Notice the progress in the Output window for the GlassFish web server.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

The Table component displays, and, if you created sample data for the database table, that data should be displayed as shown in the following Figure using Internet Explorer.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Doing Some Customization

 

Next, let do some customization to our table. Select the Table Layout from the context menu.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Unselect the photo if you included this column/field in your table during the table creation steps.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Change the Header Text for all the column as listed in the following table.

 

From

To

contactNo

Contact No

email

Email

name

Name

studentId

Student Id

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Click the Options tab and change the table’s title to FTM Student Record. Click OK.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

Resize the table if needed by selecting and dragging the column’s border. Re-run the project to see the effect.

 

Step-by-step on Java web application using JSP, JPA and JSF with MySQL database and NetBeans screenshots

 

We will continue to add more features for this application in the next tutorial.

 

 

 

 

 

 

 


 

< Insert, Update, Delete & JPA (Derby) | Java & Friends | Insert, Update, Delete & JPA (MySQL) >