Using Hibernate with the Java Persistence API

One of the main features of the new Java Persistence API that was introduced with the Java EE 5 platform is that you can plug in any persistence provider that you want. The Sun Java System Application Server 9 ships with Oracles's Toplink persistence provider. In this tutorial, we will install the Hibernate persistence provider on the application server and use it to develop a quick application that accesses a database with the Java Persistence API.

Software Required for this Tutorial

·                     NetBeans IDE 5.5

·                     Sun Java System Application Server, Platform Edition 9.0 (download)

·                     Hibernate Core 3.2.4.SP1 and Hibernate EntityManager 3.3.1 GA (download)

Note: This document uses the NetBeans IDE 5.5 Releases. If you are using NetBeans IDE 6.5, see Using Hibernate with the Java Persistence API.

Tutorial Exercises

·                     Setting up your Environment

·                     Creating the Entity Classes and Persistence Unit

·                     Coding the JSF Interface

·                     Testing the Project

Setting up your Environment

Before we start, we have to register the application server with the IDE and copy over the Hibernate JAR files to the application server's lib folder. We also have to create a library in the IDE for the Hibernate entity manager.

1.       If the application server is not registered in the IDE, register it by choosing Tools > Server Manager and clicking the Add Server button. You can see all registered application servers by opening the Runtime window and expanding the Servers node.

2.       Unzip the Hibernate JAR files to any temporary location and then copy the following JAR files to your application server's lib folder:

o                         From Hibernate EntityManager:

                                          hibernate-entitymanager.jar

                                          lib/hibernate-annotations.jar

                                          lib/hibernate-commons-annotations.jar

                                          lib/jboss-archive-browsing.jar

o                         From Hibernate Core

                                          hibernate3.jar

                                          lib/antlr-2.7.6.jar

                                          lib/asm-attrs.jar

                                          lib/asm.jar

                                          lib/c3p0-0.9.1.jar

                                          lib/cglib-2.1.3.jar

                                          lib/commons-collections-2.1.1.jar

                                          lib/commons-logging-1.0.4.jar

                                          lib/concurrent-1.3.2.jar

                                          lib/dom4j-1.6.1.jar

                                          lib/ehcache-1.2.3.jar

                                          lib/javassist.jar

                                          lib/log4j-1.2.11.jar

3.       Restart the server if it is running. You can restart the server by right-clicking its node in the Runtime window and choosing Restart.

4.       Choose Tools > Library Manager from the main menu. Click New Library, name the library Hibernate, and click OK.

5.       In the Libraries pane of the Library Manager dialog box, select the Hibernate library you just created. Click the Classpath tab and then click Add JAR/Folder and locate the hibernate-entitymanager.jar and javaee.jar in your application server's lib folder. Click Add JAR / Folder and then click OK.

library manager

 

Creating the Entity Classes and Persistence Unit

Now that we have added the Hibernate libraries to the lib directory of our application server, we can create the entity classes and web interface. We will create a web application named HibernateApp with entity classes and a JSF interface. We will use the NetBeans CRUD facilities.

1.       Choose File > New Project (Ctrl-Shift-N). Select Web Application from the Web category.

2.       Name the project HibernateApp, set the server to Sun Java System Application Server and the Java EE version to Java EE 5. Click Next.

3.       Select the JavaServer Faces framework and click Finish.

4.       In the Projects window, right-click the project node and choose New > Entity Classes from Database. In the Data Source drop-down, select the jdbc/sample database. If prompted for a user name and password, use app and app. Select the CUSTOMER table from the list, click Add and then click Next.

selecting the tables from the database

5.       Leave the tables and class names as their default, and set the package to hibernatesample.db.

wizard panel displaying the entity classes that will be generated from the selected tables

6.       Now it is time to create the persistence unit. Click the Create Persistence Unit button. Set HibernateApp as the persistence unit name, select Hibernate as the persistence provider, and select None as the table generation strategy. Click Create.

create persistence unit dialog box

7.       Click Finish in the Entity Classes from Database wizard. The IDE creates the persistence unit (persistence.xml under Configuration Files node) and the entity classes (under Source Packages).

8.       Finally, let's add some Hibernate-specific properties. Double-click persistence.xml and click the XML button at the top of the editor to switch to the XML view. Add the following property to the persistence unit:

9.             <persistence-unit name="HibernateApp" transaction-type="JTA">

10.           <provider>org.hibernate.ejb.HibernatePersistence</provider>

11.           <jta-data-source>jdbc/sample</jta-data-source>

12.           <properties>

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

14.           </properties>

15.         </persistence-unit>

</persistence>


This property causes all SQL statements to be printed to the application server's log file.

Coding the JSF Interface

Now we can quickly generate JSF pages for the entity classes with the NetBeans IDE CRUD generation.

1.       Right-click the project node and choose New > JSF Pages from Entity Class. Add both Customer.java and DiscountCode.java to the list of selected classes and click Next.

New JSF Pages from Entity Class Wizard

2.       Change the package to hibernatesample.controller and click Finish.

3.       To make this application work correctly, we need to make some changes to CustomerController.java and DiscountCodeController.java. We need to modify the create, edit and destroy methods in the files to change the order in which we get the entity manager.

Open CustomerController.java and DiscountCodeController.java in the Source Editor. Each of the files contains create, edit and destroy methods with the following code:

EntityManager em = getEntityManager();

        try {

            utx.begin();

       

Modify the code in each of the methods to change the order in which we get the entity manager.

EntityManager em = null;

        try {

            utx.begin();

            em = getEntityManager();

           

For example, the modified code in the create method of CustomerController.java should look like the following (changes in bold):

public String create() {

        EntityManager em = null;

        try {

            utx.begin();

            em = getEntityManager();

            em.persist(customer);

            utx.commit();

            ...

            

After creating the JSF pages and modifying the code in the controller classes, you should be able to run and test the application.

Testing the Project

Now we need to run the project to test whether the connection to our database is working correctly.

1.       Right-click the project and choose Run Project. The IDE starts the application server, builds and deploys the application, and shows index.jsp in the external web browser.

2.       Click the List of Customer link and you should see the following web page:

List of Customers JSP Page

3.       Use the New Customer link to create a customer. Then check the server log by right-clicking the application server node in the Runtime window and choosing View Server Log. The SQL statements for creating the customer should be listed in the server log.

 

 

 

To follow this tutorial, you need the following software and resources.

Software or Resource

Version Required

NetBeans IDE

Web & Java EE version 6.1

Java Development Kit (JDK)

Version 6 or
version 5

JavaServer Faces Components/
Java EE Platform

1.2 with Java EE 5* or
1.1 with J2EE 1.4

GlassFish Application Server

V2

Hibernate Plugin

available from NetBeans Update Center

Travel Database

Yes

 

 

 

 

You can download the solution to this tutorial from the Hibernate Travel App Plugin page on the Plugin Portal.

Adding Hibernate Support to the IDE

To integrate support for Hibernate into the IDE, you need to install the Hibernate plugins that are available from the NetBeans Beta Update Center. The Beta update center contains two plugins for Hibernate.

You can skip this section if you already installed the Hibernate plugins. You can check the Installed tab of the Plugins manager to see the plugins that are installed.

1.       Choose Tools > Plugins from the main menu.

2.       In the Available Plugins tab, select the following plugins and click Install.

o                         Hibernate Support

o                         Hibernate 3.2.5 Library

3.       Step through the wizard to install the plugins.

Plugins manager showing Hibernate plugins

Installing the Hibernate plugins adds support for creating Hibernate files and adding Hibernate libraries to web projects.

Creating the Web Application Project

In this exercise you will create a Visual Web JSF project and add the Hibernate libraries to the project. When you create the project, you will select the Visual Web JSF and Hibernate in the Frameworks panel of the New Project wizard. You will also specify the database.

1.       Choose File > New Project (Ctrl-Shift-N). Select Web Application from the Web category and click Next.

2.       Type HibernateTravelApp for the project name and set the project location.

3.       Deselect the Use Dedicated Folder option, if selected.
For this tutorial there is little reason to copy project libraries to a dedicated folder because you will not need to share libraries with other users.
Click Next.

4.       Set the server to GlassFish and set the Java EE Version to Java EE 5. Click Next.

5.       Select the Visual Web JavaServer Faces checkbox.

6.       Select the Hibernate 3.2.5 checkbox.

7.       Use the default session name (session1) and ensure that the travel database is selected for the Database Connection and the Connection URL. Click Finish.

Note: The IDE comes with a sample Travel database and a pre-configured connection to the database. If the travel database is not available as an option in the Frameworks panel in the wizard, check to see if the connection is listed under the Databases node in the Services window. If the connection is not there, you need to create the database connection.

Frameworks panel of New Project wizard

When you click Finish, the IDE creates the web application project and opens the hibernate.cfg.xml file and Page1 in the editor.

If you expand the Libraries node in the Projects window, you can see that the IDE added the Hibernate libraries to the project.

Screenshot of Projects window showing Hibernate libraries

Modifying the Hibernate Configuration File

When you create a new project that uses the Hibernate framework, the IDE automatically creates the hibernate.cfg.xml configuration file at the root of the context classpath of the application (in the Files window, WEB-INF/classes). The file is located under the Configuration Files node in the Projects window. The configuration file contains information about the database connection, resource mappings, and other connection properties. You can edit the file using the multi-view editor, or edit the XML directly in the XML editor.

In this exercise you will edit the default properties specified in hibernate.cfg.xml to enable debug logging for SQL statements and to enable Hibernate's session context management.

1.       Open hibernate.cfg.xml in the Design tab. You can open the file by expanding the Configuration Files node in the Projects window and double-clicking hibernate.cfg.xml.

2.       Expand the Configuration Properties node under Optional Properties.

3.       Click Add to open the Add Hibernate Property dialog box.

4.       In the dialog box, select the hibernate.show_sql property and set the value to true. This enables the debug logging of the SQL statements.
Add Hibernate Property dialog box the hibernate.show_sql property

5.       Expand the Miscellaneous Properties node and click Add.

6.       In the dialog box, select the properties hibernate.current_session_context_class and set the value to thread to enable Hibernate's automatic session context management.
Add Hibernate Property dialog box for properties hibernate.current_session_context_class

If you click the XML tab in the editor you can see the file in XML view. Your file should look like the following:

<hibernate-configuration>

    <session-factory name="session1">

        <property name="hibernate.dialect">org.hibernate.dialect.DerbyDialect</property>

        <property name="hibernate.connection.driver_class">org.apache.derby.jdbc.ClientDriver</property>

        <property name="hibernate.connection.url">jdbc:derby://localhost:1527/travel</property>

        <property name="hibernate.connection.username">travel</property>

        <property name="hibernate.connection.password">travel</property>

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

        <property name="hibernate.current_session_context_class">thread</property>

    </session-factory>

</hibernate-configuration>

7.       Save your changes to the file.

Creating Java Objects

In this tutorial you use two plain old Java objects (POJOs), Person and Trip, to represent the data in the tables PERSON and TRIP in the travel database. Each of the classes specifies fields for the columns in the tables and uses simple setters and getters to retrieve and write the data. After you create the classes you will then need to map the classes to the tables.

Create the Person Java Class

1.       Right-click the Source Packages node in the Projects window and choose New > Java Class to open the New Java Class wizard.

2.       In the wizard, type Person for the class name and type travel for the package. Click Finish.

3.       Make the following changes (displayed in bold) to the class to implement the Serializable interface and add fields for the table columns.

4.           public class Person implements Serializable {

5.               private int personId;

6.               private String name;

7.               private String jobTitle;

8.               private boolean frequentFlyer;

9.               private java.util.Set trips;

}

10.   Generate the getters and setters for the fields by right-clicking in the Source Editor, choosing Insert Code (Alt-Insert) and then selecting Getter and Setter.

11.   In the Generate Getters and Setters dialog box, select all the fields and click Generate.
Generate Getters and Setters dialog box

In the Generate Getters and Setters dialog box, you can use the Up arrow on the keyboard to move the selected item to the Person item and then hit the Space bar to select all fields in Person.

12.   Fix your imports and save your changes.

Create the Trip Java Class

1.       Right-click the Source Packages node in the Projects window and choose New > Java Class to open the New Java Class wizard.

2.       In the wizard, type Trip for the class name and type travel for the package. Click Finish.

3.       Make the following changes (displayed in bold) to implement the Serializable interface and add fields for the table columns to the class.

4.           public class Trip implements Serializable {

5.               private int tripId;

6.               private int personId;

7.               private Date depDate;

8.               private String depCity;

9.               private String destCity;

10.           private int tripTypeId;

}

11.   Generate the getters and setters for the fields by right-click in the Source Editor, choosing Insert Code (Alt-Insert) and then selecting Getter and Setter.

12.   In the Generate Getters and Setters dialog box, select all the fields and click Generate.

13.   Fix your imports and save your changes.

You can close Person.java and Trip.java because you will not need to edit the files again.

Mapping the Classes to the Database Tables

Now that you have the classes to represent the tables, you need to map each persistent class to the respective table using a Hibernate mapping file. The mapping file is an XML file that contains ORM metadata that defines how the class fields are mapped to table columns and primary keys. You will create a Hibernate mapping file for each of the classes.

In this section you use the New File wizard to create a simple .hbm.xml Hibernate mapping file for each of the classes. You then will edit the file in the XML editor to map the fields in each class to the column in the corresponding table and to set additional properties. You can use the IDE's code completion to help you edit the mapping file.

Mapping Person.java to the PERSON table

First you will create the Hibernate mapping file Person.hbm.xml to map the fields in Person.java to columns in the PERSON table.

1.       Start the JavaDB database server.

2.       In the Projects window, right-click the travel node under Source Packages and select New > Other in the popup menu to open the New File wizard.

3.       Select Hibernate from the Categories list and Hibernate Mapping File from the File Types list. Click Next.

4.       Type Person.hbm for the file name and set the folder to src/java/travel. Click Next.

5.       Type travel.Person for the Class to Map.

Note: Alternatively, you can click the Browse button and type Person in the Find Type dialog.

6.       Select PERSON from the Database Table drop-down list.

Note: If the Database Table drop-down list is empty, that probably means that the database is not running. You can continue and create the file without specifying the table, but you need to remember to supply the table in the XML.

7.       Click Finish.

When you click Finish, the IDE creates the Person.hbm.xml file in the same source package as Person.java and opens the file in the editor. By default, the XML file looks like the following:

<hibernate-mapping>

  <class dynamic-insert="false" dynamic-update="false" mutable="true"

  name="travel.Person" optimistic-lock="version" polymorphism="implicit"

  select-before-update="false" table="PERSON"/>

</hibernate-mapping>

Note: If you were unable to select the PERSON table in the drop-down list when you created the file, be sure to add table="PERSON" to the class element.

8.       In the XML editor, make the following changes (displayed in bold) to map the ID, properties and the one-to-many relationship.

9.           <hibernate-mapping>

10.         <class dynamic-insert="false" dynamic-update="false"

11.             mutable="true" name="travel.Person" optimistic-lock="version"

12.             polymorphism="implicit" select-before-update="false" table="PERSON">

13.               <id column="PERSONID" name="personId">

14.                   <generator class="increment"/>

15.               </id>

16.               <property column="NAME" name="name"/>

17.               <property column="JOBTITLE" name="jobTitle"/>

18.               <property column="FREQUENTFLYER" name="frequentFlyer"/>

19.               <set cascade="all-delete-orphan" inverse="true" lazy="true" name="trips" table="TRIP">

20.                   <key column="PERSONID"/>

21.                   <one-to-many class="travel.Trip"/>

22.               </set>

23.           </class>

</hibernate-mapping>

You can use the code completion in the XML editor to help you add properties and values. If the code completion is not working for you as in this screenshot, see the note below.

Screenshot of XML editor showing how XML code completion should appear when mapping Person.hbm.xml

Note: By default, the class element has a closing tag. Because you need to add property elements between the opening and closing class elements, you need to make the following changes (displayed in bold). After making the changes you can then use code completion between the class elements.

<hibernate-mapping>

  <class dynamic-insert="false" dynamic-update="false"

  mutable="true" name="travel.Person" optimistic-lock="version"

  polymorphism="implicit" select-before-update="false" table="PERSON">

 

  </class>

</hibernate-mapping>

24.   Validate the XML, fix any validation errors and save your file.

Mapping Trip.java to the TRIP Table

You will now create the Hibernate mapping file Trip.hbm.xml to map the fields in Trip.java to columns in the TRIP table.

1.       In the Projects window, right-click the travel node under Source Packages and select New > Other in the popup menu to open the New File wizard.

2.       Select Hibernate from the Categories list and Hibernate Mapping File from the File Types list. Click Next.

3.       Type Trip.hbm for the file name and set the folder to src/java/travel. Click Next.

4.       Type travel.Trip for the Class to Map.

Note: Alternatively, you can click the Browse button and type Trip in the Find Type dialog.

5.       Select TRIP from the Database Table drop-down list.

Note: If the Database Table drop-down list is empty, then leave it blank here. You can enter the table name later in the XML editor.

6.       Click Finish.

When you click Finish, the IDE creates Trip.hbm.xml in the same folder as the class Trip.java and opens the file in the editor.

7.       In the XML editor, make the following changes (in bold).

8.           <hibernate-mapping>

9.                   <class dynamic-insert="false" dynamic-update="false" mutable="true"

10.                   name="travel.Trip" optimistic-lock="version" polymorphism="implicit"

11.                   select-before-update="false" table="TRIP">

12.               <id column="TRIPID" name="tripId">

13.                   <generator class="increment"/>

14.               </id>

15.               <property column="PERSONID" name="personId"/>

16.               <property column="DEPDATE" name="depDate" type="date"/>

17.               <property column="DEPCITY" name="depCity"/>

18.               <property column="DESTCITY" name="destCity"/>

19.               <property column="TRIPTYPEID" name="tripTypeId"/>

20.           </class>

</hibernate-mapping>

Use the code completion in the XML editor to help you add properties and values.

21.   Validate the XML, fix any validation errors and save your file.

Creating the Hibernate Helper Class

To use Hibernate you need to create a helper class that handles startup and that accesses Hibernate's SessionFactory to obtain a Session object so that you can load and store Person and Trip objects. The helper class first calls Configuration() to load the hibernate.properties file. The class then calls configure() and loads the hibernate.cfg.xml configuration file. Finally, the helper class builds the SessionFactory to obtain the Session object.

In this section you use the New File wizard to create the helper class HibernateUtil.java.

1.       Right-click the travel source package node and select New > Other to open the New File wizard.

2.       Select Hibernate from the Categories list and HibernateUtil.java from the File Types list. Click Next.

3.       Type HibernateUtil for the class name. Click Finish.

New File wizard showing how to create HibernateUtil

When you click Finish, the class opens in the editor. You can close the file because you do not need to edit the file.

You now have all the classes for your application. If you expand the Source Packages node in the Projects window, your project should look like the following screenshot.

Screenshot of Projects window showing project structure

Creating the Visual Web JSF Page

Now that the classes are created, you can create the web pages for displaying and modifying the data. You will create a JSP page that uses the JSF framework and Visual Web JSF components that you bind to the objects.

Adding the Visual Web JSF Components to the Page

1.       Expand the Web Pages folder in the Projects window and open Page1.jsp in the Visual Designer.

2.       Drag a Drop Down List component from the Woodstock Basic component set in the Palette and drop it in the top left corner of Page1.

3.       Right-click the Drop Down List component and choose Auto-Submit on Change from the pop-up menu.

This action causes the browser to submit the page whenever the user chooses a new value from the drop-down list.

4.       Right-click the Drop Down List component again and choose Add Binding Attribute.

Note: You will specify the property bindings later in the tutorial. NetBeans IDE 6.1 features on-demand binding. Where components require Java coding, you must manually add the binding attribute to components in a Visual Web JSF application. To do so, right-click each component and choose Add Binding Attribute. For more information, see the On-demand Binding Attribute Wiki.

5.       Drag and drop a Table component below the Drop Down List component.

6.       Drag and drop a Message Group component below the Table component.

Message Group components help you to diagnose runtime problems. By default, the Message Group component displays messages about runtime errors, validation errors, and conversion errors.

7.       Save your changes.

Your web page now has the necessary components. You now need to bind the components to the data source.

Design view of editor showing page with placed components

Accessing the Data Source from SessionBean1

You now need to edit SessionBean1 to access the data source. In this exercise use the Add Property dialog box to specify the fields in SessionBean1 and to generate the getters and setters for the fields. The Session Bean opens a session context and then retrieves the data via the Java objects.

1.       Expand the hibernatetravelapp source package in the Projects window and double-click SessionBean1.java to open the file in the editor.

2.       Place the cursor in an empty space in the source code (for example, right after the constructor) and right-click and choose Insert Code > Add Property (Ctrl-I) to open the Add Property dialog box.

3.       In the Add Property dialog box, type personOptions for the Name, type Option[] for the Type and select private.

4.       Select Generate Getters and Setters, if unselected. Click OK.
Add Property dialog box when adding personOptions property

5.       Repeat the steps to add the following properties:

Name

Type

selectedPersonId

Integer

trips4Person

Trip[]

6.       Add Property dialog box when adding selectedPersonId propertyAdd Property dialog box when adding trips4Person property

7.       Add the methods buildPersonOptions and updateTrips4Person to the class by adding the following (in bold) to SessionBean1 after the getApplicationBean1 method.

8.           protected ApplicationBean1 getApplicationBean1() {

9.                   return (ApplicationBean1) getBean("ApplicationBean1");

10.           }

11.        

12.       private void buildPersonOptions() {

13.           List<Person> personList = null;

14.           try{

15.               Session session = HibernateUtil.getSessionFactory().getCurrentSession();

16.               Transaction tx = session.beginTransaction();

17.               Query q = session.createQuery("from Person");

18.               personList = (List<Person>) q.list();

19.        

20.           } catch(Exception e) {

21.               e.printStackTrace();

22.           }

23.        

24.           personOptions = new Option[personList.size()];

25.           int i=0;

26.           for(Person person : personList) {

27.               Option opt = new Option(person.getPersonId(), person.getName());

28.               personOptions[i++] = opt;

29.           }

30.       }

31.        

32.       private void updateTrips4Person() {

33.           if(selectedPersonId == null ) {

34.               trips4Person = new Trip[1];

35.               trips4Person[0] = new Trip();

36.               return;

37.           }

38.        

39.           Set personTrips = null;

40.           try{

41.              Session session =

42.                       HibernateUtil.getSessionFactory().getCurrentSession();

43.               Transaction tx = session.beginTransaction();

44.               Person person = (Person)session.load(Person.class, selectedPersonId);

45.               personTrips = (PersistentSet)person.getTrips();

46.        

47.           } catch(Exception e) {

48.               e.printStackTrace();

49.           }

50.        

51.           trips4Person = (Trip[]) personTrips.toArray(new Trip[0]);

}

The buildPersonOptions method calls a query on the Person data source and stores the results in the personOptions array. The updateTrips4Person method updates the trips for the selected person.

52.   Fix your imports.
Fix Imports dialog box

Note: When you select the fully qualified names to import, make sure you select the org.hibernate libraries and com.sun.webui.jsf.model.Option and java.util.Set.

53.   Call method buildPersonOptions by adding the following (in bold) at the end of the init() method.

54.       @Override

55.       public void init() {

56.           super.init();

57.        

58.           try {

59.               _init();

60.           } catch (Exception e) {

61.               log("SessionBean1 Initialization Failure", e);

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

63.           }

64.           // Fill in the personOptions[]

65.           buildPersonOptions();

66.        

}

67.   Call the method updateTrips4Person by adding the following (in bold) at the end of the setSelectedPersonId method.

68.       public void setSelectedPersonId(Integer selectedPersonId) {

69.           this.selectedPersonId = selectedPersonId;

70.           updateTrips4Person();

}

71.   Save your changes.

72.   Right-click the project node in the Projects window and choose Build.

Binding Components to Data

In this section, you bind the Drop Down List and Table components in the web page to the properties defined in the previous section in SessionBean1.

1.       Open Page1.jsp in the Visual Designer.

2.       Right click the Drop Down List component and choose Property Bindings to open the Property Bindings dialog window.

3.       Select items in the bindable property list and personOptions (under the SessionBean1 node) in the binding target list. Click Apply.
Property Bindings dialog box showing matching of the binding property item to the target PersonOptions

4.       In the same dialog, select selected in the bindable property list and selectedPersonId (under the SessionBean1 node) in the binding target list. Click Apply.
Property Bindings dialog box showing matching of the binding property selected to the target SeletPersonId

5.       Click Close to close the dialog window.

6.       In the Visual Designer, right click on the Table component and select Table Layout.

7.       In the dialog, choose trips4Person (SessionBean1) from the Get Data From drop-down list.

Note: If you do not see trips4Person (SessionBean1) in the drop-down list, most likely is because you forgot to build the project at the end of the previous section.

8.       Select personId in the Selected list and click the left-arrow button Left Arrow Button to move the field from the Selected list to the Available list.

9.       Use the Up and Down buttons to arrange the remaining fields in the following order, as shown below, and click OK.
Table Layout dialog box showing order of fields

10.   In the Editing toolbar, click Java to open Page1.java in the Java Editor.

11.   In the prerender method, add the following code (displayed in bold).

12.       public void prerender() {

13.           try {

14.               if (dropDown1.getSelected() == null ) {

15.                   Option firstPerson = getSessionBean1().getPersonOptions()[0];

16.                   getSessionBean1().setSelectedPersonId((Integer)firstPerson.getValue());

17.               }

18.           } catch (Exception ex) {

19.               log("Error Description", ex);

20.               error(ex.getMessage());

21.           }

}

The code in the prerender method is invoked before a web browser starts to display the page. Adding the code to the prerender method causes the page to display the information for the first person in the drop-down list when the user first visits the page.

When the browser first requests the page, the application creates an instance of Page1 and calls the prerender method. The server sends the response (the HTML page) and the Page1 instance is destroyed. The application does not call the value change event handler, because the application only generates value change events when a page is submitted (in this case, when a new person is selected).

22.   Right-click in the source and choose Fix Imports from the popup menu to open the Fix Imports dialog box. In the Fully Qualified Name drop-down list, select com.sun.webui.jsf.model.Option, as shown below.
Fix Imports dialog box

23.   Save your changes.

Running the Project

1.       Click Run Main Project in the main toolbar.

The IDE saves all changed files, rebuilds the application, and deploys the application to the server.

2.       Select a person from the drop-down list to see how the contents of the table are updated with the data for the selected person.

Screenshot of finished application in browser

Installing the Sample Plugin

You can download the solution project for this tutorial by downloading the Hibernate Travel App Plugin from the Plugin Portal. After you install the plugin, a sample project is added to the Visual Web samples available in the New Project wizard. Before you can run the sample application, you need to add the Hibernate and JSF libraries to the project. You also need to have an instance of the Glassfish application server registered with the IDE.

You need to install the Hibernate plugins before you can add the Hibernate libraries to the project.

Complete the following steps to download the plugin, create the sample project and add the required Hibernate and JSF libraries.

1.       Choose Tools > Plugins to open the Plugins manager.

2.       Select the Hibernate Travel App Sample in the Available Plugins tab. Click Install.

After you click Install, step through the Plugin installer to install the plugin.

When you see the warning that the sample app plugin is not signed, click Continue.

3.       Choose File > New Project from the main menu to open the New Project wizard.

4.       Select Samples > Web > Visual JSF in the Categories pane and select Hibernate Travel App Sample. Click Next.

5.       Specify the Project Location. Click Finish.

When you click Finish, the IDE creates the project and displays project in the Projects window. The project will not compile as it is because some of the libraries are missing. If you expand the project's Libraries node in the Projects window, you can see that the necessary Hibernate and JSF libraries are missing.

Projects window showing created sample app

6.       Right-click the Libraries node and choose Add Library from the popup menu. The Add Library dialog box displays the available libraries.

7.       Click Import to open the Import Library dialog box.

8.       Select the following Global Libraries:

o                         Hibernate

o                         JSF 1.1/1.2 Support

o                         Web UI Components

o                         Web UI Default Theme

You can select multiple libraries by holding the ctrl button when you select the libraries.

Screenshot of Import Library dialog

Click Import Library.

9.       Make sure the correct libraries are selected in the Add Library dialog and click Add Library.
Screenshot of Add Library dialog

When you click Add Library, the IDE displays the added libraries below the Libraries node in the Projects window.

Projects window showing sample app with added libraries

After you add the required libraries, the compilation error badges on your source files will disappear.

10.   Right-click the project node and choose Run.

When you click Run the IDE builds the application and deploys the application to the server. The default web page opens in your browser window.

 

 

Using Java Persistence in a J2EE 1.4 Web Application

This document takes you through the basics of how to add Java™ Persistence functionality to a J2EE 1.4 web application. Though the Java Persistence API was introduced as part of the Java EE 5 platform, it is possible to use Java Persistence in a J2EE 1.4 application. The target container needs to be running on JDK 1.5, but it does not need to be a Java EE 5 container.

We add support for Java Persistence functionality by adding the TopLink Essentials library to the classpath of the project or the container. The TopLink Essentials library is bundled with NetBeans IDE 5.5.

In this tutorial we will create the same ZooApp web application project that was created in the Java Persistence in the Java EE 5 Platform, but this time we deploy the application to the bundled Tomcat web server instead of the Sun Java System Application Server. The steps for creating the entity classes and web interface are the same, as is the source code of the classes. The only difference in the process of creating the application is in setting up our environment, the initial configuration of the project properties and the persistence unit.

 

Prerequisites

This document assumes you have some basic knowledge of, or programming experience with, the following technologies:

·                          Java Programming

·                          NetBeans IDE

·                          MySQL Database Server

Software Needed for the Tutorial

For this tutorial you need to have the following software installed on your computer:

·                          NetBeans IDE 5.5

·                          Java Standard Development Kit (JDK) version 5.0 (download)

·                          Access to a database server (For this tutorial we will use the MySQL 5.0 database server available at www.mysql.com/. If you want to use a MySQL database, make sure you have the MySQL JDBC driver. The driver is available here. If you want to use an Oracle database, you can find the current version of the Oracle JDBC driver (10.2.0.2) here.)

Tutorial Exercises

·                          Setting Up the Environment

·                          Setting Up the Project

·                          Creating the Entity Classes

·                          Creating a Web Interface

·                          Running the Project

·                          Troubleshooting

 

Setting Up the Environment

This tutorial shows you how to create the ZooApp application in an environment where there is no local installation of the Sun Java System Application Server or Java DB database server. Instead, we will use the bundled Tomcat web server as our container, and use a locally installed database server, in this case a MySQL database. The steps for setting up a database connection are also applicable to setting up a JDBC connection to other JDBC-compatible database servers, such as an Oracle database.

Before we can create our application, we need to confirm that the IDE and software are configured correctly and make sure that the database connection to our database is working correctly. We need to do the following:

·                          Confirm that the Java platform for the IDE is set to JDK 1.5

·                          Confirm that the Java platform for the Tomcat web server is set to JDK 1.5

·                          Create a database connection

Setting the Java Platform

We have to make sure that the Java platform for the IDE and the Tomcat web server are set to JDK 1.5.

1.       Choose Tools > Java Platform Manager from the main menu.

2.       In the Java Platform Manager dialog box, make sure that JDK 1.5 is set as the default platform and click Close.

3.       Choose Tools > Server Manager from the main menu.

4.       Select Bundled Tomcat in the Servers pane and click the Platform tab.

5.       Make sure that the selected Java Platform is JDK 1.5.

6.       Click OK.

Creating the Database Connection

In this example we use a locally installed MySQL database, but you can use any database that uses a JDBC driver. The IDE does not come with a database connection to the MySQL database, so we need to create a database connection in the Runtime window.

We need a MySQL JDBC driver to create a database connection to the MySQL database. If there is no MySQL JDBC driver, we need to add one.

Note: When you add a JDBC driver in the Add JDBC Driver dialog, you are providing the information necessary to allow the IDE to comunicate with the database. For example, this allows you to view the contents of a database in the Runtime window of the IDE. You may still need to add the appropriate driver to the web server library to allow the server to communicate with the database.

1.       In the Runtime window of the IDE, expand the Drivers node under the Database node.
If there is no MySQL driver listed under the Drivers node, right-click the Drivers node and choose New Driver.
In the New JDBC Driver dialog box, click Add, locate the JDBC driver for the database (
mysql-connector-java-3.1.12-bin.jar), and then click OK to add the driver.

2.       Right-click the database driver and choose Connect Using to open the New Database Connection dialog box.

3.       In the New Database Connection dialog box, enter the URL, username and password for the database and click OK.

The Add JDBC Driver wizard

The New Database Connection dialog box

When you click OK, a new database connection node appears under the Databases node. If there is no open connection to the database, right-click the database connection node, choose Connect and supply the username and password, if necessary. We can test our connection by expanding the connection node to see the tables in the database, if any. Now that we have established a connection to our database, we can start creating our web application.

Summary

In this exercise we made sure our IDE and web server were running on the correct Java platform and that we had a working database connection to our database.

Setting Up the Project

In this exercise we create the ZooApp web application project. When creating the project, we first specify the project properties such as the J2EE version, the Java source level and the target server. We then need to configure the classpath of our project so that the appropriate JAR archives are available when we build and deploy the application.

Creating the Project

Because we are deploying to Tomcat instead of a Java EE container, we need to set the J2EE version to J2EE 1.4 when we create our project. But we need to set the source level of our project to 1.5 so that can use Java EE 5 functionality in our source code.

1.       Choose File > New Project (Ctrl-Shift-N). Select Web Application from the Web category and click Next.

2.       Name the project ZooApp.

3.       Set the server to the Bundled Tomcat server.

4.       Specify the project location.

5.       Set the Java EE Version to J2EE 1.4.

6.       Deselect Set the Source Level to 1.4.
Note: By deselecting this, we are instructing the IDE to use the default source level. The default source level is determined by the default Java Platform of the IDE. In the previous exercise we set the default Java Platform to JDK 1.5.

7.       Click Next.

8.       Select the Java Server Faces framework checkbox and click Finish.

When you click Finish, the ZooApp web application appears in the Projects window of the IDE.

The New Web Application wizard

Adding Support for Java Persistence

In this tutorial we want to use Java Persistence in the Tomcat web container. Because the Tomcat web container is not a Java EE 5 container, we need to add support for Java Persistence by making sure the appropriate libraries are on the classpath. You add support for Java Persistence by adding the TopLink Essentials library to the classpath of your project. The TopLink Essentials library is bundled with the IDE and is located in <NETBEANS_HOME>\enterprise3\modules\ext\toplink, where <NETBEANS_HOME> is the installation directory of the IDE.

1.       In the Projects window, right-click the Libraries node under the ZooApp project node and choose Add Library.

2.       In the Add Library dialog box, select TopLink Essentials and click Add Library.
Note: If the TopLink Essentials library is not listed, click Manage Libraries to open the Library Manager and create a library with
toplink-essentials.jar and add the library to the project.

Adding Support for the Database Server

We now need to add the JAR archive for our database driver to the classpath.

1.       In the Projects window, right-click the Libraries node under the ZooApp project node and choose Add Jar/Folder.

2.       In the Add Jar/Folder dialog box, locate the driver for the database (mysql-connector-java-3.1.12-bin.jar) and click Open.

Summary

In this exercise we created a J2EE 1.4 web application. We then added the TopLink Essentials library and the database driver to the classpath of our web application. We can now start creating the entity classes and the web interface. The following steps are the same as in the Java Persistence in the Java EE 5 Platform tutorial.

Creating the Entity Classes

In this exercise we will create two entity classes, Animal.java and Pavilion.java, that represent the tables in the relational database we want to create. We will also use annotations to define some fields in the classes to represent the data.

We need a persistence unit in order to persist entity classes. The persistence unit specifies the datasource, the entity classes to be persisted and the entity manager for managing the life-cycle of the entities. The Tomcat server does not support container-managed persistence so we will use an application-managed entity manager. The entity manager must be specified as resource-local because Tomcat does not support JTA.

When we use the New Entity Class wizard, we are prompted to create a persistence unit if our application does not have one.

Creating the Animal Entity Class

First we will create the entity class Animal. This class represents the ANIMAL table in our database. When you create the entity class, the IDE adds the @Entity annotation to define the class as an entity class. After we create the class, we will create fields in the class to represent the data that we want in our table, and use annotations to provide additional information about some of the fields.

Each entity class must have a primary key. When you create the entity class, the IDE adds the @Id annotation to declare which field to use as the primary key. The IDE also adds the @Generated annotation to specify the key generation strategy for the primary Id.

To create the Animal class, do the following:

1.       Right-click the ZooApp project node and choose New > File/Folder.

2.       From the Persistence category, select Entity Class and click Next.

3.       Type Animal for the class name, entity for the package, and leave the Primary Key Type as Long.

4.       Click Create Persistence Unit to create the required persistence unit for our project.

5.       In the Create Persistence Unit dialog box, use the default name for the Persistence Unit and make sure the Persistence Library is TopLink Essentials.

6.       Select the MySQL database connection, make sure the table generation strategy is set to create on deploy and click Create. We set the table generation strategy to create so that the database tables represented by the entity classes are created when we run the application.

7.       Click Finish to create the new entity class.

The Create Persistence Unit wizard

When you click Finish, the new entity class Animal.java opens in the Source Editor. In the Source Editor, do the following:

1.       Add the following field declarations to the class:

2.             String name;

3.             String kind;

4.             String weight;

  Pavilion pavilion;

5.       Right-click in the Source Editor and choose Refactor > Encapsulate fields to generate getters and setters for each of the fields. In the Encapsulate Fields dialog box, make sure that the getter and setter checkboxes are selected for all of the fields.

6.       Click Next in the Encapsulate Fields dialog box and then click Do Refactoring in the Refactoring tab of the Output window.

7.       We now want to change the name of one of the columns that will be created in the Animal table. We want our column to be called animalName instead of name. We can use annotations to specify the name of the generated column by adding the following annotation (in bold) above the name field declaration:

8.               @Column(name="animalName")

    private String name;

9.       We also want the pavilion column in our Animal table to have a many-to-one relationship. We can do this using annotations by adding the following annotation (in bold) above the pavilion declaration:

10.           @ManyToOne

    private Pavilion pavilion;

11.   Press Alt-Shift-F to generate any necessary import statements for the class.

12.   Save your changes.

The error highlighting in the IDE indicates that our class still has some errors, but this will be resolved after we create the Pavilion entity class in the next step.

Creating the Pavilion Entity Class

We now will create the entity class Pavilion representing the PAVILION table in our database. We will again use annotations in our class to specify the object-relational mapping of some of our fields. To create the Pavilion class, do the following:

1.       Right-click the ZooApp project node and choose New > File/Folder.

2.       From the Persistence category, select Entity Class and click Next.

3.       Type Pavilion for the class name, entity for the package, and leave the Primary Key Type as Long. Click Finish.

When you click Finish, the new entity class Pavilion.java opens in the Source Editor. In the Source Editor, do the following:

1.       Add the following field declarations to the class:

2.             String name;

3.             String address;

  Collection <Animal> animals;

4.       Choose Refactor > Encapsulate Fields and do the refactoring to generate the getters and setters for the fields.

5.       Add the following annotation (in bold) above the name declaration to change the name of the generated column:

6.               @Column(name="pavilionName")

    private String name;

7.       Add the following annotation (in bold) to specify a One-to-Many relationship for the animals collection mapped by pavilion in the Animal class:

8.               @OneToMany(mappedBy="pavilion")

    private Collection <Animal> animals;

9.       Press Alt-Shift-F to generate any missing import statements.

10.   Save your changes.

Summary

In this exercise, we created two entity classes and defined some of the fields in the classes. We also used annotations to define the properties of some of the columns in the tables that will be generated when the application is deployed. We also created a persistence unit with details required for managing the entities in our application.

Creating a Web Interface

We now want to create some simple web pages to see if our database tables were created and if we can add data. We will add Java Server Faces (JSF) pages to the application and use the JSF Pages from Entity Class wizard to quickly create a simple web interface.

1.       Choose File > New from the main menu. Select JSF Pages from Entity Class from the Persistence category and click Next.

2.       In the New JSF Pages from Entity Class wizard, click Add All to select our two entity classes.

3.       Leave the JSF Pages Folder text field empty to save the JSF files in the default location.

4.       Specify entity as the package for the generated classes and click Finish.

The JSF Pages from Entity Class wizard

When you click Finish, the IDE generates the required JavaServer Faces files so that we can run and test our ZooApp.

Running the Project

In this exercise we will deploy our ZooApp web application project and test our application. When we run the application, the tables ANIMAL and PAVILION will be created in our database.

1.       Make sure the database server is running.

2.       Right-click the ZooApp project node and choose Run Project.

When you click Run, a page opens in your browser with a menu enabling you to see a list of the pavilions and animals.

ZooApp Main Page

You can also add, edit or delete the data for animals and pavilions. You can view the data in the database in the Runtime window of the IDE.

ZooApp Add Animal Page

Summary

In this exercise, you built the ZooApp web application. You then deployed and tested the ZooApp web application.

Troubleshooting

The following are some of the problems you may encounter when creating your project.

Problem with New JSF Pages from Entity Class Wizard

When using the wizard to create JSF pages from an entity class, you may see the following error message in the wizard:

This wizard can only be used in a web project with JSF support.

If you see this message you need to check that the Java Server Faces framework has been added to the project properties. You can add Java Server Faces support to your web project by doing the following:

1.       Right-click the web application node in the Projects window and select Properties.

2.       Select Frameworks in the Categories pane of the Project Properties dialog box and then click Add.

3.       In the Select Frameworks dialog box select Java Server Faces and click OK.

4.       Click OK in the Project Properties dialog box to close the window.

After adding the JSF Framework to the project properties, you should be able to create the JSF pages using the wizard.

Problem Establishing Connection Between Database and Server

If you can connect to the database from the IDE but you get a message that the server is unable to connect to the database, you may need to add the JDBC driver to the web server library. You should consult the documentation for your server for the correct location.

For example, to add the MySQL driver to the Sun Java System Application Server, you should add the mysql-connector-java-5.0.4-bin.jar to the APPSERVER_HOME/domains/domain1/lib/ext/ directory, where APPSERVER_HOME is the installation directory of the server.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Hibernate Tutorial 09 Hibernate Query Language

 

By Gary Mak, hibernatetutorials@metaarchit.com

 

1. Querying objects

 

When using JDBC to access databases, we write SQL statements for the query and update tasks. In

such case, we are dealing with tables, columns and joins. When using Hibernate, most update tasks

can be finished through the APIs provided by Hibernate. However, using a query language for the

query tasks is still necessary. Hibernate is providing a powerful query language called Hibernate

query language (HQL).

 

HQL is database independent and translated into SQL by Hibernate at runtime. When writing HQL,

we can concentrate on the objects and properties without knowing much detail on the underlying

database. We can treat HQL as an object-oriented variant of SQL.

In the previous chapters, we have already experienced some basic HQL statements for querying

objects. For example, we can use the following HQL to query for all books, and then call the list()

method to retrieve the result list which containing book objects.

 

Query query = session.createQuery("from Book");

List books = query.list();

 

The Query interface provides two methods for retrieving only a subset of the results, ranged by the

offset (which is zero-based) and record count. They are very useful for displaying the results in a

table with multiple pages.

 

Query query = session.createQuery("from Book");

query.setFirstResult(20);

query.setMaxResults(10);

List books = query.list();

 

Another query attribute that will have performance impact is the fetch size. It tells the underlying

JDBC driver how many rows should be transferred for a single request.

 

Query query = session.createQuery("from Book");

query.setFetchSize(100);

List books = query.list();

 

When using HQL, we can specify query parameters at the same way as we do for SQL queries. If

we are sure that there will be only one unique object returned as result, we can call the

uniqueResult() method to retrieve it. Null will be returned if nothing matched.

 

Query query = session.createQuery("from Book where isbn = ?");

query.setString(0, "1932394419");

Book book = (Book) query.uniqueResult();

 

In the example above, we use “?” to represent a query parameter and set it by index, which is

zero-based not one-based as in JDBC. This kind of parameters is called “Positional Parameters”. We

can also use “Named Parameters” for our queries. The advantages of using named parameters are

easy to understand and able to occur for multiple times.

 

Query query = session.createQuery("from Book where isbn = :isbn");

query.setString("isbn", "1932394419");

Book book = (Book) query.uniqueResult();

 

In this tutorial, we will introduce more details about HQL. It is also beneficial to monitor the SQL

statements generated for writing high performance queries.

 

2. The from clause

 

Now let’s begin with the from clause of a HQL statement. It is the only necessary part of a HQL

statement. The following HQL statement is used for querying the books whose name contains the

word “Hibernate”. Notice that the “name” is a property of Book but not a database column.

from Book

where name = 'Hibernate Quickly'

 

Or you can assign an alias for the object. It’s useful when you are querying multiple objects in one

query. We should use the naming conventions for classes and instances in Java. Notice that the “as”

keyword is optional.

 

from Book as book

where book.name = 'Hibernate Quickly'

 

We can specify more than one class in the from clause. In such case, the result will contain a list of

Object[]. For the following statement, it will be a “cross join” of book objects and publisher objects

since there’s not any where clauses.

from Book book, Publisher publisher

 

3. Joining associations

 

In HQL, we can use the “join” keyword to join our associated objects. The following query finds all

the books published by the publisher “Manning”. The result contains a list of object pairs in the

form of Object[]. Each pair consists of a book object and a publisher object.

 

from Book book join book.publisher publisher

where publisher.name = 'Manning'

 

In addition to many-to-one associations, all other kinds of associations can also be joined. For

example, we can join the one-to-many association from book to chapters as well. The following

query finds all the books containing a chapter “Hibernate Basics”. The result contains a list of

object pairs also. Each pair consists of a book object and a collection of chapters.

 

from Book book join book.chapters chapter

where chapter.title = 'Hibernate Basics'

 

3.1. Implicit Joins

 

In the above joins, we specify a keyword “join” for joining associated objects. This kind of joins is

called “explicit joins”. In fact, we can reference an association by its name directly. This will cause

an “implicit joins”. For example, the above two queries can be expressed as follows. The result will

contain a list of book objects only since no join is specified in the from clause.

 

from Book book

where book.publisher.name = 'Manning'

from Book book

where book.chapters.title = 'Hibernate Basics'

 

For a collection association, an implicit join occurs each time when it is navigated. That means if

we navigate the same collection for two times, the same table will be joined for two times also.

 

from Book book

where book.chapters.title = 'Hibernate Basics' and book.chapters.numOfPages = 25

 

So we must be careful when using implicit joins with collection association. For the collection to be

referenced more than one time, we should use “explicit join” to avoid duplicated joins.

 

from Book book join book.chapters chapter

where chapter.title = 'Hibernate Basics' and chapter.numOfPages = 25

 

3.2. Joining types

 

If we use the following HQL to query for books joining with publishers, we will find that the books

with null publisher will not be included. This type of joins is called “inner join” and it is default for

joins if we don’t specify any join type or specify as “inner join”. It has the same meaning as the

 

inner join in SQL.

from Book book join book.publisher

 

If we want to get all the books regardless whose publisher is null or not, we can use the “left join”

by specifying “left join” or “left outer join”.

from Book book left join book.publisher

 

There are another two types of joins supported by HQL, “right join” and “full join”. They have the

same meaning as in SQL also, but are seldom used.

 

3.3. Removing duplicate objects

 

The following HQL can be used to retrieve books and their associated chapters where at least one of

the chapter titles includes the word “Hibernate”. The result contains pairs of a book and a chapter.

from Book book join book.chapters chapter

where chapter.title like '%Hibernate%'

 

For the implicit version of the above query, only the book objects will be included. But to our

surprise the book objects are duplicated. The time of duplication is equal to how many chapters

have “Hibernate” like title.

from Book book

where book.chapters.title like '%Hibernate%'

 

According to the explanation given by Hibernate, it is a normal behavior since Hibernate always

returns a list of the same size as the underlying JDBC ResultSet. We can use a LinkedHashSet to

filter the duplicate objects while keeping the order of original list.

 

Query query = session.createQuery(

"from Book book where book.chapters.title like '%Hibernate%'");

List books = query.list();

Set uniqueBooks = new LinkedHashSet(books);

 

 

3.4. Fetching associations

 

We can use “join fetch” to force a lazy association to be initialized. It differs from the pure “join” in

that only the parent objects will be included in the result.

 

from Book book join fetch book.publisher publisher

 

The above “inner join fetch” query will not return book objects with null publisher. If you want to

include them also, you should use “left join fetch”.

from Book book left join fetch book.publisher publisher

 

4. The where clause

 

In HQL, we can use where clauses to filter the results just like what we do in SQL. For multiple

conditions, we can use “and”, “or”, “not” to combine them.

 

from Book book

where book.name like '%Hibernate%' and book.price between 100 and 200

 

We can check whether an associated object is null or not by “is null” or “is not null”. Notice that a

collection can not be checked.

from Book book

where book.publisher is not null

 

We can also use implicit joins in the where clause. Remember that for collection association, if you

reference it more than one time, you should use “explicit join” to avoid duplicated joins.

from Book book

where book.publisher.name in ('Manning', 'OReilly')

 

Hibernate is providing a function for you to check the size of a collection. You can use it by the

special property size or the special size() function. Hibernate will use a “select count(...)” subquery

to get the size of the collection.

from Book book

where book.chapters.size > 10

from Book book

where size(book.chapters) > 10

 

5. The select clause

 

In the previous samples, we are querying for the whole persistent objects. We can query for some

particular fields instead in the select clause. For example, the following query returns all the book

names in a list.

 

select book.name

from Book book

 

The SQL aggregate functions, such as count(), sum(), avg(), max(), min() can be used in HQL also.

They will be translated in the resulting SQL.

 

select avg(book.price)

from Book book

 

Implicit joins can also be used in the select clause. In addition, the keyword “distinct” can be used

for returning distinct result.

 

select distinct book.publisher.name

from Book book

 

Multiple fields can be queried by using comma to separate. The result list will contain elements of

Object[].

select book.isbn, book.name, book.publisher.name

from Book book

 

We can create custom type and specify in the select clause to encapsulate the results. For example,

let’s create a class “BookSummary” for the isbn, book name and publisher name fields. The custom

type must have a constructor of all fields.

 

public class BookSummary {

private String bookIsbn;

private String bookName;

private String publisherName;

public BookSummary(String bookIsbn, String bookName, String publisherName) {

this.bookIsbn = bookIsbn;

this.bookName = bookName;

this.publisherName = publisherName;

}

// Getters and Setters

}

 

select new com.metaarchit.bookshop.BookSummary(book.isbn, book.name, book.publisher.name)

from Book book

 

The results can also be encapsulated in collections, e.g. lists and maps. Then the result for the query

will be list of collections.

 

select new list(book.isbn, book.name, book.publisher.name)

from Book book

 

For the map collection, we need to use the keyword “as” to specify the map key for each field.

 

select new map(book.isbn as bookIsbn, book.name as bookName, book.publisher.name as

publisherName)

from Book book

 

6. Order by and group by

 

The result list can be sorted with an “order by” clause. Multiple fields and ascending/descending

 

order can be specified.

from Book book

order by book.name asc, book.publishDate desc

 

The “group by” and “having” clauses are also supported by HQL. They will also be translated into

SQL by Hibernate.

 

select book.publishDate, avg(book.price)

from Book book

group by book.publishDate

select book.publishDate, avg(book.price)

from Book book

group by book.publishDate

having avg(book.price) > 10

 

 

7. Subqueries

 

We can use subqueries in HQL also. Be careful that subqueries may be not effective if writing

improperly. For many cases, you can write equivalent queries with simple “select-from-where”

statements.

 

from Book expensiveBook

where expensiveBook.price > (

select avg(book.price) from Book book

)

 

 

8. Named Queries

 

We can put our HQL statements in the mapping definitions and refer them by name in the code.

They are called “Named Queries”.

The named queries can be put in any mapping definitions. But for easier maintenance, we should

centralize all the named queries in one mapping definitions, say NamedQuery.hbm.xml, or each

mapping definition for a category. In addition, setting up a mechanism for the naming of queries is

also beneficial.

 

For each named query, we need to assign a unique name to it. We should also put the query string in

a <![CDATA[...]]> block to avoid conflicts with the special XML characters.

 

<hibernate-mapping>

<query name="Book.by.isbn">

<![CDATA[from Book where isbn = ?]]>

</query>

</hibernate-mapping>

 

To reference for a named query, we can use the session.getNamedQuery() method.

Query query = session.getNamedQuery("Book.by.isbn");

query.setString(0, "1932394419");

Book book = (Book) query.uniqueResult();