5. Appendices

Here we describe the installation and basic use of the tools used in the document "Java 5 Persistence in Practice." The information provided below is current as of May 2007. It will quickly become obsolete. When that happens, the reader will be advised to follow similar but not identical procedures. The installations were performed on a Windows XP Professional machine.

5.1. Java

We will use the latest version of Java available from Sun [http://www.sun.com]. Downloads are accessible via the URL [http://java.sun.com/javase/downloads/index.jsp]:

Run the JDK installation from the downloaded file. By default, Java is installed in [C:\Program Files\Java]:

5.2. Eclipse

5.2.1. Basic Installation

Eclipse is an IDE available at the URL [http://www.eclipse.org/] and can be downloaded from the URL [http://www.eclipse.org/downloads/]. Below, we download Eclipse 3.2.2:

Once the ZIP file is downloaded, extract it to a folder on your hard drive:

We will refer to the Eclipse installation folder, shown above as [C:\devjava\eclipse 3.2.2\eclipse], as <eclipse>. [eclipse.exe] is the executable file, and [eclipse.ini] is its configuration file. Let’s take a look at its contents:

1
2
3

-vmargs
-Xms40m
-Xmx256m

These arguments are used when launching Eclipse as follows:

eclipse.exe -vmargs -Xms40m -Xmx256m

This achieves the same result as using the .ini file by creating a shortcut that launches Eclipse with these same arguments. Let’s explain them:

-vmargs: indicates that the following arguments are intended for the Java Virtual Machine that will run Eclipse. Eclipse is a Java application.
-Xms40m: ?
-Xmx256m: sets the memory size in MB allocated to the Java Virtual Machine (JVM) running Eclipse. By default, this size is 256 MB, as shown here. If the system allows it, 512 MB is preferable.

These arguments are passed to the JVM that will run Eclipse. The JVM is represented by a [java.exe] or [javaw.exe] file. How is this file located? In fact, it is located in several ways:

in the OS PATH
in the <JAVA_HOME>/jre/bin folder, where JAVA_HOME is a system variable defining the root folder of a JDK.
at a location passed as an argument to Eclipse in the form -vm <path>\javaw.exe

This last solution is preferable because the other two are subject to the vagaries of subsequent application installations, which can either change the OS PATH or change the JAVA_HOME variable.

We therefore create the following shortcut:

`target`	<eclipse>\eclipse.exe" -vm "C:\Program Files\Java\jre1.6.0_01\bin\javaw.exe" -vmargs -Xms40m -Xmx512m
`Start in`	Eclipse installation folder <eclipse>

Once this is done, launch Eclipse using this shortcut. A dialog box appears:

A [workspace] is a workspace. Let’s accept the default values provided. By default, Eclipse projects will be created in the <workspace> folder specified in this dialog box. There is a way to override this behavior. This is what we will do systematically. Therefore, the response given in this dialog box is not important.

Once this step is complete, the Eclipse development environment is displayed:

We close the [Welcome] view as suggested above:

Before creating a Java project, we will configure Eclipse to specify the JDK to use for compiling Java projects. To do this, we select the [Window / Preferences / Java / Installed JREs] option:

Normally, the JRE (Java Runtime Environment) used to launch Eclipse itself should be present in the list of JREs. This will usually be the only one. You can add JREs using the [Add] button. You must then specify the root directory of the JRE. The [Search] button will launch a search for JREs on the disk. This is a good way to keep track of the JREs you install and then forget to uninstall when upgrading to a newer version. Above, the checked JRE is the one that will be used to compile and run Java projects. This is the one installed in Section 5.1 and also used to launch Eclipse. Double-clicking on it opens its properties:

Now, let’s create a Java project [File / New / Project]:

Select [Java Project], then [Next] ->

In [2], we specify an empty folder where the Java project will be installed. In [1], we give the project a name. It does not have to be named after its folder, as the example above might suggest. Once this is done, we use the [Next] button to proceed to the next page of the creation wizard:

Above, we create a special folder within the project to store the source files (.java):

In [1], we see the [src] folder, which will contain the .java source files
In [2], we see the [bin] folder where the compiled .class files will be stored

We complete the wizard by clicking [Finish]. We now have a Java project skeleton:

Right-click on the [test1] project to create a Java class:

In [1], the folder where the class will be created. By default, Eclipse suggests the current project folder.
In [2], the package where the class will be placed
In [3], the name of the class
In [4], we specify that the static method [main] should be generated

We confirm the wizard by clicking [Finish]. The project is then enhanced with a class:

Eclipse has generated the class skeleton. This can be accessed by double-clicking on [Test1.java] above:

We modify the code above as follows:

We run the [Test1.java] program: [right-click on Test1.java -> Run As -> Java Application]

The result of the execution is displayed in the [Console] window:

The [Console] window should appear by default. If it does not, you can display it via [Window/Show View/Console]:

5.2.2. Choosing a Compiler

Eclipse allows you to generate code compatible with Java 1.4, Java 1.5, and Java 1.6. By default, it is configured to generate code compatible with Java 1.4. The JPA API requires Java 1.5 code. We change the type of generated code via [Window / Preferences / Java / Compiler]:

in [1]: selecting the [Java / Compiler] option
in [2]: Select Java 5.0 compatibility

5.2.3. Installing the Callisto s

The base version installed above allows you to build Java console applications but not web or Swing-type Java applications; otherwise, you have to do everything yourself. We will install various plugins:

Proceed as follows [Help/Software Updates/Find and Install]:

In [2], specify that you want to install new plugins

In [3], specify the sites to search for plugins
In [4], check the desired plugins

In [5], Eclipse indicates that you have selected a plugin that depends on other plugins that have not been selected
In [6], use the [Select Required] button to automatically select the missing plugins
In [7], accept the license terms for these various plugins

In [8], you see a list of all the plugins that will be installed
In [9], start downloading these plugins
In [10], once they have been downloaded, install them all without verifying their signatures

In [11], once the plugins are installed, let Eclipse restart
In [12], if you go to [File/New/Project], you’ll find that you can now create web applications, which wasn’t possible initially.

5.2.4. Installing the [ TestNG] plugin

TestNG (Test Next Generation) is a unit testing tool similar in concept to JUnit. However, it offers improvements that make us prefer it over JUnit here. We proceed as before: [Help/Software Updates/Find and Install]:

In [2], we indicate that we want to install new plugins

in [3a], the [TestNG] download site is not listed. We add it using [3b]
In [4b]: the plugin’s site is [http://beust.com/eclipse]. In [4a], enter whatever you want.

In [5a], the [TestNG] plugin is selected for the update. In [5b], we start the update.
In [6], the connection to the plugin’s website has been established. We are shown all the plugins available on the site. There is only one here, which we select before moving on to the next step.

In [7], accept the plugin’s license terms
In [8], we see a list of all the plugins that will be installed—one in this case. We start the download. Then everything proceeds as described above for Callisto plugins.

Once Eclipse has restarted, we can verify the presence of the new plugin by, for example, viewing the available views [Window / Show View / Other]:

As shown above, there is now a [TestNG] view that did not exist before.

5.2.5. Installing the [ Hibernate Tools] plugin

Hibernate is a JPA provider, and the [Hibernate Tools] plugin for Eclipse is useful for building JPA applications. As of May 2007, only its latest version (3.2.0beta9) supports working with Hibernate/JPA, and it is not available via the mechanism just described. Only older versions are available. We will therefore proceed differently.

The plugin is available on the Hibernate Tools website: http://tools.hibernate.org/.

In [1], select the latest version of Hibernate Tools
In [2], download it

In [3], use an unzip tool to extract the downloaded ZIP file into the <eclipse> folder (it is best to have Eclipse closed)
In [4], accept that some files will be overwritten during the operation

Restart Eclipse:

in [1]: Open a view
in [2]: there is now a [Hibernate Console] perspective

We won’t go any further with the [Hibernate Tools] plugin (Cancel in [2]). How to use it is explained in the tutorial examples.

Sometimes Eclipse does not detect new plugins. You can force it to rescan all its plugins using the -clean option. Thus, the Eclipse shortcut executable would be modified as follows:


"<eclipse>\eclipse.exe" -clean -vm "C:\Program Files\Java\jre1.6.0_01\bin\javaw.exe" -vmargs -Xms40m -Xmx512m

Once the new plugins have been detected by Eclipse, remove the -clean option above.

5.2.6. Installing the [ SQL Explorer] plugin

We will now install a plugin that will allow us to explore the contents of a database directly from Eclipse. The plugins available for Eclipse can be found on the website [http://eclipse-plugins.2y.net/eclipse/plugins.jsp]:

at [1]: the Eclipse plugins website
at [2]: select the [Database] category
[3]: In the [Database] category, select a view sorted by rating (not very reliable given the small number of people voting)
in [4]: QuantumDB ranks first
in [5]: we choose SQLExplorer, which is older, lower-ranked (3rd) but still very good. We go to the plugin’s website [plugin-homepage]

in [6] and [7]: download the plugin.

in [8]: unzip the plugin’s zip file into the Eclipse folder.

To verify, restart Eclipse, optionally using the -clean option:

in [1]: open a new perspective
in [2]: we see that a [SQL Explorer] perspective is available. We’ll come back to this later.

5.3. Tomcat 5.5 servlet container

5.3.1. Installation

To run servlets, we need a servlet container. Here we present one of them, Tomcat 5.5, available at http://tomcat.apache.org/. We outline the procedure (May 2007) for installing it. If a previous version of Tomcat is already installed, it is best to remove it first.

To download the product, follow the [Tomcat 5.x] link above:

You can download the .exe file for the Windows platform. Once downloaded, launch the Tomcat installation by double-clicking on it:

Accept the license terms ->

Click [Next] ->

Accept the suggested installation folder or change it using [Browse] ->

Set the login and password for the Tomcat server administrator. Here we used [admin / admin] ->

Tomcat 5.x requires JRE 1.5. It should normally detect the one installed on your machine. Above, the specified path is that of the JRE 1.6 downloaded in section 5.1. If no JRE is found, specify its root directory using the [1] button. Once this is done, use the [Install] button to install Tomcat 5.x ->

The [Finish] button completes the installation. The presence of Tomcat is indicated by an icon on the right side of the Windows taskbar:

Right-clicking this icon gives you access to the Start and Stop server commands:

We use the [Stop service] option to stop the web server now:

Note the change in the icon’s status. The icon can be removed from the taskbar:

Tomcat was installed in the folder chosen by the user, which we will now refer to as <tomcat>. The directory structure for the downloaded Tomcat 5.5.23 version is as follows:

The Tomcat installation has added a number of shortcuts to the [Start] menu. We use the [Monitor] link below to launch the Tomcat stop/start tool:

We then see the icon shown earlier:

The Tomcat monitor can be launched by double-clicking this icon:

The [Start - Stop - Pause] - Restart buttons allow us to start, stop, and restart the server. We start the server by clicking [Start], then, using a browser, we enter the URL http://localhost:8080. We should see a page similar to the following:

You can follow the links below to verify that Tomcat has been installed correctly:

All the links on the [http://localhost:8080] page are worth exploring, and the reader is encouraged to do so. We will have the opportunity to revisit the links that allow you to manage web applications deployed on the server:

5.3.2. Deploying a web application on the Tomcat server

5.3.3. Deployment

A web application must follow certain rules to be deployed within a servlet container. Let <webapp> be the directory of a web application. A web application consists of:

`classes`	in the <webapp>\WEB-INF\classes folder
`Java archives`	in the <webapp>\WEB-INF\lib folder
`views, resources (.jsp, .html, ...)`	in the <webapp> folder or subfolders

The web application is configured by an XML file: <webapp>\WEB-INF\web.xml. This file is not necessary in simple cases, particularly when the web application contains only static files. Let’s create the following HTML file:

<html>
    <head>
      <title>Example Application</title>
  </head>
  <body>
      Sample application running....
  </body>
</html>

and let's save it to a folder:

If we load this file in a browser, we get the following page:

The URL displayed by the browser shows that the page was not served by a web server but loaded directly by the browser. We now want it to be available via the Tomcat web server.

Let’s go back to the <tomcat> directory tree:

Web applications deployed on the Tomcat server are configured using XML files located in the [<tomcat>\conf\Catalina\localhost] folder:

These XML files can be created manually because their structure is simple. Rather than taking this approach, we will use the web tools provided by Tomcat.

5.3.4. Tomcat Administration

On its login page http://localhost:8080, the server provides links for administration:

The [Tomcat Administration] link allows us to configure the resources that Tomcat makes available to the web applications deployed within it, such as a database connection pool. Let’s follow the link:

The page that appears indicates that administering Tomcat 5.x requires a specific package called "admin." Let’s return to the Tomcat website [http://tomcat.apache.org/download-55.cgi]:

Let’s download the zip file labeled [Web Application Administration] and then unzip it. Its contents are as follows:

The [admin] folder must be copied to [<tomcat>\server\webapps], where <tomcat> is the folder where Tomcat 5.x was installed:

The [localhost] folder contains an [admin.xml] file that must be copied to [<tomcat>\conf\Catalina\localhost]:

Stop and then restart Tomcat if it was running. Then, using a browser, request the web server’s login page again:

Click the [Tomcat Administration] link. You will see a login page (you may need to reload or refresh the page to see it):

Here, you must re-enter the credentials you provided during the Tomcat installation. In our case, we enter the username and password as "admin". Clicking the [Login] button takes you to the following page:

This page allows the Tomcat administrator to define

data sources,
the information needed to send email (Mail Sessions),
environment data accessible to all applications (Environment Entries),
manage Tomcat users and administrators (Users),
manage user groups (Groups),
define roles (i.e., what a user can and cannot do),
define the characteristics of web applications deployed by the server (Catalina Service)

Let’s follow the [Roles] link above:

A role allows you to define what a user or group of users can or cannot do. Certain rights are associated with a role. Each user is associated with one or more roles and has the rights associated with them. The [manager] role below grants the right to manage web applications deployed in Tomcat (deployment, startup, shutdown, unloading). We will create a [manager] user and associate them with the [manager] role to allow them to manage Tomcat applications. To do this, we follow the [Users] link on the administration page:

We see that a number of users already exist. We use the [Create New User] option to create a new user:

We give the user manager the password manager and assign the manager role to them. We use the [Save] button to confirm this addition. The new user appears in the list of users:

This new user will be added to the file [<tomcat>\conf\tomcat-users.xml]:

whose content is as follows:

<?xml version='1.0' encoding='utf-8'?>
<tomcat-users>
  <role rolename="tomcat"/>
  <role rolename="role1"/>
  <role rolename="manager"/>
  <role rolename="admin"/>
  <user username="tomcat" password="tomcat" roles="tomcat"/>
  <user username="role1" password="tomcat" roles="role1"/>
  <user username="both" password="tomcat" roles="tomcat,role1"/>
  <user username="manager" password="manager" fullName="" roles="manager"/>
  <user username="admin" password="admin" roles="admin,manager"/>
</tomcat-users>

Line 10: the [manager] user that was created

Another way to add users is to edit this file directly. This is the procedure to follow if, for instance, you have forgotten the password for the admin or manager account.

5.3.5. Managing Deployed Web Applications

Now let’s return to the login page [http://localhost:8080] and follow the [Tomcat Manager] link:

This brings up an authentication page. We log in as manager / manager, i.e., the user with the [manager] role that we just created. In fact, only a user with this role can use this link. In line 11 of [tomcat-users.xml], we see that the user [admin] also has the [manager] role. We could therefore also use the [admin / admin] credentials.

We are taken to a page listing the applications currently deployed in Tomcat:

We can add a new application using the forms at the bottom of the page:

Here, we want to deploy the sample application we built earlier within Tomcat. We do this as follows:

`Context Path`	/example	the name used to identify the web application to be deployed
`Directory URL`	C:\data\work\2006-2007\eclipse\dvp-jpa\annexes\tomcat\example	the web application folder

To retrieve the file [C:\data\work\2006-2007\eclipse\dvp-jpa\annexes\tomcat\example\example.html], we will request the URL [http://localhost:8080/exemple/exemple.html] from Tomcat. The context is used to name the root of the deployed web application’s directory tree. We use the [Deploy] button to deploy the application. If everything goes well, we get the following response page:

and the new application appears in the list of deployed applications:

Let’s comment out the /example context line above:

`/example`	link to http://localhost:8080/exemple
`Start`	allows you to start the application
`Stop`	allows you to stop the application
`Reload`	reloads the application. This is necessary, for example, when you have added, modified, or deleted certain classes from the application.
`Undeploy`	Removes the [/example] context. The application disappears from the list of available applications.

Now that our /example application is deployed, we can run some tests. We request the [example.html] page via the URL [http://localhost:8080/exemple/vues/exemple.html]:

Another way to deploy a web application on the Tomcat server is to provide the information we entered via the web interface in a [context].xml file located in the [<tomcat>\conf\Catalina\localhost] folder, where [context] is the name of the web application.

Let’s return to the Tomcat administration interface:

Let’s remove the [/example] application using its [Undeploy] link:

The [/example] application is no longer part of the list of active applications. Now let’s define the following [example.xml] file:

1 2	`<Context docBase="C:/data/work/2006-2007/eclipse/dvp-jpa/annexes/tomcat/example"> </Context>`

The XML file consists of a single <Context> tag whose docBase attribute defines the folder containing the web application to be deployed. Let’s place this file in <tomcat>\conf\Catalina\localhost:

Stop and restart Tomcat if necessary, then view the list of active applications using the Tomcat administrator:

The [/example] application is indeed present. Let’s request the URL in a browser:

[http://localhost:8080/exemple/exemple.html]:

A web application deployed in this way can be removed from the list of deployed applications, in the same way as before, using the [Undeploy] link:

In this case, the [example.xml] file is automatically removed from the [<tomcat>\conf\Catalina\localhost] folder.

Finally, to deploy a web application within Tomcat, you can also define its context in the [<tomcat>\conf\server.xml] file. We will not cover this point here.

5.3.6. Web application with a home page

When we request the URL [http://localhost:8080/exemple/], we get the following response:

With some earlier versions of Tomcat, we would have received the contents of the application’s physical directory [/example].

We can configure the system so that, when the context is requested, a so-called home page is displayed. To do this, we create a [web.xml] file and place it in the <example>\WEB-INF folder, where <example> is the physical folder of the [/example] web application. The file is as follows:

<?xml version="1.0" encoding="ISO-8859-1"?>
<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
    version="2.4">

  <display-name>Sample Application</display-name>
  <description>Minimal Web Application</description>
    <welcome-file-list>
        <welcome-file>/example.html</welcome-file>
    </welcome-file-list>    
</web-app>

Lines 2–5: The root <web-app> tag with attributes copied and pasted from the [web.xml] file of the Tomcat [/admin] application (<tomcat>/server/webapps/admin/WEB-INF/web.xml).
line 7: the display name of the web application. This is a freely chosen name with fewer constraints than the application context name. For example, it can contain spaces, which is not possible with the context name. This name is displayed, for example, by the Tomcat administrator:

Line 8: Description of the web application. This text can then be retrieved programmatically.
Lines 9–11: The list of welcome files. The <welcome-file-list> tag is used to define the list of views to display when a client requests the application context. There can be multiple views. The first one found is presented to the client. Here we have only one: [/example.html]. Thus, when a client requests the URL [/example], it will actually be the URL [/example/example.html] that is served to them.

Let’s save this [web.xml] file in <example>\WEB-INF:

If Tomcat is still running, you can force it to reload the [/example] web application using the [Reload] link:

During this "reload" operation, Tomcat re-reads the [web.xml] file contained in [<example>\WEB-INF] if it exists. This will be the case here. If Tomcat was stopped, restart it.

Using a browser, request the URL [http://localhost:8080/exemple/]:

The host file mechanism has worked.

5.3.7. Integrating Tomcat into Eclipse

We will now integrate Tomcat into Eclipse. This integration allows you to:

start/stop Tomcat from within Eclipse
develop Java web applications and run them on Tomcat. The Eclipse/Tomcat integration allows you to trace (debug) the application’s execution, including the execution of Java classes (servlets) run by Tomcat.

Let’s launch Eclipse, then open the [Servers] view:

in [1]: Window/Show View/Other
in [2]: select the [Servers] view and click [OK]

in [1], we now have a new [Servers] view
In [2], right-click on the view and select [New/Server]
in [3], select the [Tomcat 5.5] server, then click [Next]

In [4], specify the Tomcat 5.5 installation directory
In [5], indicate that there are no Eclipse/Tomcat projects at this time. Click [Finish]

Adding the server results in a folder appearing in the Eclipse Project Explorer [6] and a server appearing in the [Servers] view [7]:

The [Servers] view displays all registered servers; here, only the Tomcat 5.5 server we just added. Right-clicking on it gives access to commands to start, stop, or restart the server:

Above, we are starting the server. Upon startup, a number of logs are written to the [Console] view:

May 16, 2007 09:51:57 org.apache.catalina.core.AprLifecycleListener lifecycleEvent
...
May 16, 2007 09:51:57 org.apache.coyote.http11.Http11BaseProtocol init
INFO: Initializing Coyote HTTP/1.1 on http-8080
...
INFO: Found registry server-registry.xml at classpath resource
May 16, 2007 09:51:58 org.apache.catalina.startup.Catalina start
INFO: Server startup in 828 ms

Understanding these logs takes some getting used to. We won’t dwell on them for now. However, it is important to verify that they do not indicate any context loading errors. Indeed, when launched, the Tomcat/Eclipse server attempts to load the context of the applications it manages. Loading an application’s context involves processing its [web.xml] file and loading one or more classes that initialize it. Several types of errors can occur:

the [web.xml] file has a syntax error. This is the most common error. It is recommended to use a tool capable of validating an XML document during its creation.
certain classes to be loaded were not found. They are searched for in [WEB-INF/classes] and [WEB-INF/lib]. You should generally verify the presence of the necessary classes and the spelling of those declared in the [web.xml] file.

The server launched from Eclipse does not have the same configuration as the one installed in Section 5.3. To verify this, access the URL [http://localhost:8080] using a browser:

This response does not indicate that the server is not working, but rather that the resource / requested is not available. With the Tomcat server integrated into Eclipse, these resources will be web projects. We will see this later. For now, let’s stop Tomcat:

The previous operating mode can be changed. Let’s return to the [Servers] view and double-click on the Tomcat server to access its properties:

Checkbox [1] is responsible for the previous behavior. When checked, web applications developed in Eclipse are not declared in the configuration files of the associated Tomcat server but in separate configuration files. As a result, the default applications within the Tomcat server—[admin] and [manager], which are two useful applications—are not available. So, let’s uncheck [1] and restart Tomcat:

Now that this is done, let's request the URL [http://localhost:8080] in a browser:

We see the behavior described in Section 5.3.4.

In our previous examples, we used a browser outside of Eclipse. We can also use a browser built into Eclipse:

Above, we select the internal browser. To launch it from Eclipse, you can use the following icon:

The browser that actually launches will be the one selected via the [Window -> Web Browser] option. Here, we get the internal browser:

If necessary, launch Tomcat from Eclipse and enter the URL [http://localhost:8080] in [1]:

Follow the [Tomcat Manager] link:

You will be prompted for the [username/password] required to access the [manager] application. Based on the Tomcat configuration we set up earlier, you can enter [admin/admin] or [manager/manager]. You will then see the list of deployed applications:

5.4. 's Firebird DBMS

5.4.1. Firebird DBMS

The Firebird DBMS is available at the URL [http://www.firebirdsql.org/]:

in [1]: use the [Download.Firebird Relational Database] option
in [2]: select the desired version of Firebird
in [3]: download the installation binary

Once the file [3] has been downloaded, double-click it to install the Firebird DBMS. The DBMS is installed in a folder with contents similar to the following:

The binaries are in the [bin] folder:

`fbguard.exe`	allows you to start/stop the DBMS
`isql.exe`	command-line client for managing databases

Note that by default, the DBMS administrator is named [SYSDBA] and the password is [masterkey]. Menus have been added to [Start]:

The [Firebird Guardian] option allows you to start/stop the DBMS. After startup, the DBMS icon remains in the Windows taskbar:

To create and manage Firebird databases using the command-line client [isql.exe], you must read the documentation included with the product, which is accessible via Firebird shortcuts in [Start/Programs/Firebird 2.0].

A quick way to work with Firebird and learn SQL is to use a graphical client. One such client is IB-Expert, described in the following section.

5.4.2. Working with the Firebird DBMS using IB- Expert

The main IB-Expert website is [http://www.ibexpert.com/].

In [1], select IBExpert
In [2], select the download after choosing your preferred language, if necessary
In [3], select the "Personal" version, as it is free. You must, however, register on the site.
In [4], download IBExpert

IBExpert is installed in a folder similar to the following:

The executable file is [ibexpert.exe]. A shortcut is normally available in the [Start] menu:

Once launched, IBExpert displays the following window:

Use the [ Database/Create Database] option to create a database:

`Server (Server)`	can be [local] or [remote]. Here, our server is on the same machine as [IBExpert]. We choose [local]
`Database (Database)`	Use the [folder] button in the dropdown menu to select the database file. Firebird stores the entire database into a single file. This is one of its advantages. You can transfer the database from one computer to another by simply copying the file. The [.fdb] suffix is added automatically.
`Username`	SYSDBA is the default administrator for current Firebird distributions
`Password (Username)`	masterkey is the password for the SYSDBA administrator in current Firebird distributions
`Dialect`	the SQL dialect to use
`Register Database (Register the database)`	If this box is checked, IBExpert will display a link to the database after it has been created

If, when clicking the [OK] button to create the database, you see the following warning:

it means you haven't started Firebird. Start it. A new window will appear:

Charset
(Character set)

The character set to use. It is recommended to select the

[ISO-8859-1] from the drop-down list, which allows the use of accented Latin characters.

Server version
(Server version)

[IBExpert] is capable of managing various DBMSs derived from Interbase.

Select the version of Firebird that you have installed.

Once this new window is confirmed by clicking [Register], you will see the result [1] in the [Database Explorer] window. This window may be closed accidentally. To bring it back up, do the following [2]:

To access the created database, simply double-click on its link. IBExpert then displays a tree structure providing access to the database properties:

5.4.3. Creating a Data Table

Let’s create a table. Right-click on [Tables] (see window above) and select the [New Table] option. This opens the window for defining the table’s properties:

Let’s start by naming the table [ARTICLES] using the input field [1]:

Use the input field [2] to define a primary key [ID]:

A field is made a primary key by double-clicking the [PK] (Primary Key) field. Let’s add fields using the button above [3]:

Until we have "compiled" our definition, the table is not created. Use the [Compile] button above to finalize the table definition. IBExpert prepares the SQL queries to generate the table and asks for confirmation:

Interestingly, IBExpert displays the SQL queries it has executed. This allows you to learn both the SQL language and any proprietary SQL dialect that may be used. The [Commit] button validates the current transaction, while [Rollback] cancels it. Here, we accept it by clicking [Commit]. Once this is done, IBExpert adds the created table to our database tree:

By double-clicking on the table, we can access its properties:

The [Constraints] panel allows us to add new integrity constraints to the table. Let’s open it:

We see the primary key constraint we created. We can add other constraints:

foreign keys [Foreign Keys]
field integrity constraints [Checks]
field uniqueness constraints [Uniques]

Let’s specify that:

the fields [ID, PRICE, CURRENTSTOCK, MINIMUMSTOCK] must be >0
the [NAME] field must be non-empty and unique

Open the [Checks] panel and right-click in the constraint definition area to add a new constraint:

Let’s define the desired constraints:

Note above that the constraint [NAME<>''] uses two single quotes, not double quotes. Compile these constraints using the [Compile] button above:

Once again, IBExpert demonstrates its user-friendliness by displaying the SQL queries it has executed. Let’s now move to the [Constraints/Unique] panel to specify that the name must be unique. This means that the same name cannot appear twice in the table.

Let’s define the constraint:

Then let’s compile it. Once that’s done, open the [DDL] (Data Definition Language) panel for the [ARTICLES] table:

This panel displays the SQL code for generating the table with all its constraints. You can save this code in a script to run it later:

SET SQL DIALECT 3;
SET NAMES ISO8859_1;
CREATE TABLE ARTICLES (
    ID            INTEGER NOT NULL,
    NAME           VARCHAR(20) NOT NULL,
    PRICE          DOUBLE PRECISION NOT NULL,
    CURRENT_STOCK   INTEGER NOT NULL,
    MINIMUM_STOCK  INTEGER NOT NULL
);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_ID check (ID>0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_PRICE check (PRICE>0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_CURRENT_STOCK check (CURRENT_STOCK > 0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_STOCKMINIMUM check (STOCKMINIMUM>0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_NAME check (NAME<>'');
ALTER TABLE ARTICLES ADD CONSTRAINT UNQ_NAME UNIQUE (NAME);
ALTER TABLE ARTICLES ADD CONSTRAINT PK_ARTICLES PRIMARY KEY (ID);

5.4.4. Inserting data into a table

It is now time to enter data into the [ARTICLES] table. To do this, use its [Data] panel:

Data is entered by double-clicking on the input fields of each row in the table. A new row is added using the [+] button, and a row is deleted using the [-] button. These operations are performed within a transaction that is committed using the [Commit Transaction] button (see above). Without this commit, the data will be lost.

5.4.5. The [IB-Expert] SQL Editor

The SQL (Structured Query Language) allows a user to:

create tables by specifying the data type they will store and the constraints that the data must satisfy
insert data into them
modify certain data
delete other data
use the data to retrieve information
...

IBExpert allows users to perform steps 1 through 4 graphically. We’ve just seen this. When a database contains many tables, each with hundreds of rows, you need information that is difficult to obtain visually. Suppose, for example, that an online store has thousands of customers per month. All purchases are recorded in a database. After six months, it is discovered that product “X” is defective. The company wants to contact everyone who purchased it so they can return the product for a free exchange. How can the addresses of these buyers be found?

You could manually go through all the tables and search for these buyers. That would take a few hours.
We can run an SQL query that will return a list of these people in a matter of seconds

SQL is useful whenever

the amount of data in the tables is large
there are many tables linked together
the information to be retrieved is spread across multiple tables
...

We will now introduce IBExpert’s SQL Editor. It can be accessed via the [Tools/SQL Editor] option or by pressing [F12]:

This gives you access to an advanced SQL query editor where you can run queries. Let’s type a query:

Execute the SQL query using the [Execute] button above. You will get the following result:

Above, the [Results] tab displays the result table for the [Select] SQL command. To issue a new SQL command, simply return to the [Edit] tab. You will then see the SQL command that was executed.

Several buttons on the toolbar are useful:

The [New Query] button allows you to move on to a new SQL query:

You will then see a blank editing page:

You can then enter a new SQL query:

and execute it:

Let’s return to the [Edit] tab. The various SQL statements issued are stored by [IBExpert]. The [Previous Query] button allows you to return to a previously issued SQL statement:

You are then returned to the previous query:

The [Next Query] button allows you to go to the next SQL statement:

You will then see the next SQL statement in the list of stored SQL statements:

The [Delete Query] button allows you to delete an SQL statement from the list of stored statements:

The [Clear Current Query] button clears the contents of the editor for the displayed SQL statement:

The [Commit] button allows you to permanently save the changes made to the database:

The [RollBack] button allows you to undo the changes made to the database since the last [Commit]. If no [Commit] has been performed since connecting to the database, then the changes made since that connection are undone.

Let’s look at an example. Let’s insert a new row into the table:

The SQL statement is executed but nothing is displayed. We don’t know if the insertion took place. To find out, let’s execute the following SQL statement [New Query]:

We get the following result:

The row has indeed been inserted. Let’s examine the table’s contents in another way now. Double-click on the [ARTICLES] table in the database explorer:

We get the following table:

The button with the arrow above allows you to refresh the table. After refreshing, the table above does not change. It appears that the new row was not inserted. Let’s return to the SQL editor (F12) and then confirm the SQL statement using the [Commit] button:

Once this is done, let’s return to the [ARTICLES] table. We can see that nothing has changed, even when using the [Refresh] button:

Above, open the [Fields] tab, then return to the [Data] tab. This time, the inserted row appears correctly:

When the execution of the various SQL statements begins, the editor opens what is called a transaction on the database. The changes made by these SQL statements in the SQL editor will only be visible as long as you remain in the same SQL editor (you can open multiple ones). It’s as if the SQL editor were working not on the actual database but on its own copy. In reality, this is not exactly how it works, but this analogy can help us understand the concept of a transaction. All changes made to the copy during a transaction will only be visible in the actual database once they have been committed via a [Commit Transaction]. The current transaction is then terminated, and a new transaction begins.

Changes made during a transaction can be undone by an operation called [Rollback]. Let’s try the following experiment. Let’s start a new transaction (simply [Commit] the current transaction) with the following SQL statement:

Let’s execute this command, which deletes all rows from the [ARTICLES] table, and then execute [New Query] with the following new SQL command:

We get the following result:

All rows have been deleted. Remember that this was done on a copy of the [ARTICLES] table. To verify this, double-click on the [ARTICLES] table below:

and view the [Data] tab:

Even if we use the [Refresh] button or switch to the [Fields] tab and then back to the [Data] tab, the content above remains unchanged. This has been explained. We are in another transaction that is working on its own copy. Now let’s return to the SQL editor (F12) and use the [RollBack] button to undo the row deletions that were made:

We are asked for confirmation:

Let’s confirm. The SQL editor confirms that the changes have been rolled back:

Let’s run the SQL query above again to verify. The rows that had been deleted are now back:

The [Rollback] operation restored the copy that the SQL editor is working on to the state it was in at the start of the transaction.

5.4.6. Exporting a Firebird database to an SQL script

When working with various DBMSs, as is the case in the tutorial "Java 5 Persistence in Practice," it is useful to be able to export a database from DBMS 1 to an SQL script and then import that script into DBMS 2. This avoids a number of manual operations. However, this is not always possible, as DBMSs often have proprietary SQL extensions.

Let’s show how to export the previous [dbarticles] database to an SQL script:

in [1]: Tools / Extract MetaData, to extract the metadata
in [2]: Meta Objects tab
in [3]: select the [Articles] table whose structure (metadata) you want to extract
in [4]: to move the object selected on the left to the right

in [5]: the [ARTICLES] table will be included in the extracted metadata
in [6]: The [Data Table] tab is used to select the tables from which you want to extract the content (in the previous step, it was the table structure that was exported)
in [7]: to move the object selected on the left to the right
in [8]: the result obtained

in [9]: the [Options] tab allows you to configure certain extraction settings
in [10]: uncheck the options related to generating SQL statements for connecting to the database. These are specific to Firebird and therefore not relevant to us.
in [11]: the [Output] tab allows you to specify where the SQL script will be generated
in [12]: specify that the script should be generated in a file
in [13]: Specify the location of this file
in [14]: Start generating the SQL script

The generated script, with comments removed, is as follows:

SET SQL DIALECT 3;
SET NAMES ISO8859_1;

CREATE TABLE ARTICLES (
    ID            INTEGER NOT NULL,
    NAME           VARCHAR(20) NOT NULL,
    PRICE          DOUBLE PRECISION NOT NULL,
    CURRENT_STOCK   INTEGER NOT NULL,
    MINIMUM_STOCK  INTEGER NOT NULL
);

INSERT INTO ITEMS (ID, NAME, PRICE, CURRENTSTOCK, MINIMUMSTOCK) VALUES (1, 'item1', 100, 10, 1);
INSERT INTO ITEMS (ID, NAME, PRICE, CURRENT_STOCK, MIN_STOCK) VALUES (2, 'item2', 200, 20, 2);
INSERT INTO ITEMS (ID, NAME, PRICE, CURRENT_STOCK, MIN_STOCK) VALUES (3, 'item3', 300, 30, 3);

COMMIT WORK;

ALTER TABLE ARTICLES ADD CONSTRAINT CHK_ID check (ID>0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_PRICE check (PRICE > 0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_CURRENTSTOCK check (CURRENTSTOCK > 0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_MINIMUM_STOCK check (MINIMUM_STOCK > 0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_NAME check (NAME<>'');
ALTER TABLE ARTICLES ADD CONSTRAINT UNQ_NAME UNIQUE (NAME);
ALTER TABLE ARTICLES ADD CONSTRAINT PK_ARTICLES PRIMARY KEY (ID);

Note: Lines 1-2 are specific to Firebird. They must be removed from the generated script to obtain generic SQL.

5.4.7. Firebird JDBC driver

A Java program accesses data from a database via a JDBC driver specific to the DBMS being used:

In a multi-tier architecture like the one above, the JDBC driver [1] is used by the [DAO] (Data Access Object) layer to access data from a database.

The Firebird JDBC driver is available at the URL where Firebird was downloaded:

in [1]: choose to download the JDBC driver
in [2]: select a JDBC driver compatible with JDK 1.5
in [3]: the archive containing the JDBC driver is [jaybird-full-2.1.1.jar]. Extract this file. It will be used for all JPA examples with Firebird.

We place it in a folder that we will refer to as <jdbc>:

To verify this JDBC driver, we will use Eclipse and the SQL Explorer plugin (Section 5.2.6). We start by declaring the Firebird JDBC driver:

in [1]: go to Window / Preferences
in [2]: select the SQL Explorer / JDBC Drivers option
in [3]: select the JDBC driver for Firebird
in [4]: proceed to the configuration phase
in [5]: go to the [Extra Class Path] tab
In [6], select the JDBC driver file. Once selected, it will appear in [7]. Here, select the driver previously placed in the <jdbc> folder
in [8]: the Java class name of the JDBC driver. It can be obtained by clicking the [8b] button.
Click [OK] to confirm the configuration

in [9]: the Firebird JDBC driver is now configured. You can proceed to use it.

In [1]: Open a new perspective
in [2]: select the [SQL Explorer] perspective

in [3]: create a new connection
Step [4]: Give it a name
in [5]: select the Firebird JDBC driver from the drop-down list
in [6]: specify the URL of the database you want to connect to, here: [jdbc:firebirdsql:localhost/3050:C:\data\2006-2007\eclipse\dvp-jpa\annexes\jpa\jpa.fdb]. [jpa.fdb] is the database previously created with IBExpert.
in [7]: the username for the connection, in this case [sysdba], the Firebird administrator
in [8]: their password [masterkey]
Confirm the connection settings by clicking [OK]

in [1]: double-click on the name of the connection you want to open
in [2]: log in (sysdba, masterkey)
in [3]: the connection is open
in [4]: the database structure is displayed. The [ARTICLES] table is visible. Select it.

In [5]: In the [Database Detail] window, you see the details of the object selected in [4], in this case the [ARTICLES] table
in [6]: the [Columns] tab displays the table structure
in [7]: the [Preview] tab shows the table structure

You can run SQL queries in the [SQL Editor] window:

in [1]: select an open connection
in [2]: type the SQL statement to execute
in [3]: execute it
in [4]: review the executed statement
in [5]: its result

5.5. The DBMS s MySQL5

5.5.1. Installation

The MySQL5 DBMS is available at the URL [http://dev.mysql.com/downloads/]:

in [1]: select the desired version
in [2]: choose a Windows version

in [3]: select the desired Windows version
in [4]: the downloaded ZIP file contains an executable [Setup.exe] [4b] that you must extract and run to install MySQL5

in [5]: select a typical installation
in [6]: Once the installation is complete, you can configure the MySQL5 server

in [7]: choose a standard configuration, the one that asks the fewest questions
in [8]: the MySQL5 server will be a Windows service

in [9]: By default, the server administrator is root with no password. You can keep this configuration or set a new password for root. If the MySQL5 installation follows the uninstallation of a previous version, this step may fail. There is no way to undo it.
in [10]: you are prompted to configure the server

The installation of MySQL5 creates a folder in [Start / Programs]:

You can use the [MySQL Server Instance Config Wizard] to reconfigure the server:

in [3]: we change the root password (here root/root)

5.5.2. Start / Stop MySQL5

The MySQL5 server was installed as a Windows service that starts automatically, i.e., it launches when Windows starts. This mode of operation is impractical. We will change it:

[Start / Control Panel / Performance and Maintenance / Administrative Tools / Services]:

in [1]: we double-click on [Services]
in [2]: we see that a service named [MySQL] is present, that it is running [3], and that it starts automatically [4].

To change this setting, double-click the [MySQL] service:

in [1]: set the service to manual startup
in [2]: stop it
in [3]: we confirm the new service configuration

To manually start and stop the MySQL service, we can create two shortcuts:

in [1]: the shortcut to start MySQL5
in [2]: the shortcut to stop it

5.5.3. MySQL Administration Clients

On the MySQL website, you can find DBMS administration clients:

[1]: Select [MySQL GUI Tools], which includes various graphical clients for either administering the DBMS or using it
in [2]: select the appropriate Windows version

in [3]: download an .msi file to run
in [4]: once the installation is complete, new shortcuts will appear in the [Start Menu / Programs / MySQL] folder.

Launch MySQL (using the shortcuts you created), then launch [MySQL Administrator] via the menu above:

in [1]: enter the root user’s password (root here)
in [2]: you are logged in and can see that MySQL is active

5.5.4. Creating a jpa user and a jpa database

This tutorial uses MySQL 5 with a database named jpa and a user of the same name. We will now create them. First, the user:

in [1]: select [User Administration]
in [2]: right-click in the [User accounts] section to create a new user
in [3]: the user is named jpa and their password is jpa
in [4]: confirm the creation
in [5]: the user [jpa] appears in the [User Accounts] window

Now the database:

in [1]: select the [Catalogs] option
in [2]: right-click on the [Schemas] window to create a new schema (designates a database)
in [3]: name the new schema
in [4]: it appears in the [Schemata] window

in [5]: select the [jpa] schema
in [6]: the objects in the [jpa] schema appear, including the tables. There aren’t any yet. Right-clicking would allow you to create them. We’ll leave that to the reader.

Let’s return to the [jpa] user to grant them full permissions on the [jpa] schema:

in [1], then [2]: select the user [jpa]
in [3]: select the [Schema Privileges] tab
in [4]: select the [jpa] schema
in [5]: grant the user [jpa] all privileges on the [jpa] schema

in [6]: confirm the changes

To verify that the user [jpa] can work with the [jpa] schema, close the MySQL administrator. Restart it and log in this time as [jpa/jpa]:

in [1]: log in (jpa/jpa)
in [2]: the connection was successful, and in [Schemas], we see the schemas for which we have permissions. We see the [jpa] schema.

We will now create the same [ARTICLES] table as with the Firebird DBMS using the SQL script [schema-articles.sql] generated in section 5.4.6.

in [1]: use the [MySQL Query Browser] application
in [2], [3], [4]: log in (jpa / jpa / jpa)

in [5]: open an SQL script to execute it
in [6]: select the script [schema-articles.sql] created in section 5.4.6.

in [7]: the script is loaded
in [8]: execute it
in [9]: the [ARTICLES] table has been created

5.5.5. 's JDBC driver for MySQL 5

The MySQL JDBC driver can be downloaded from the same location as the DBMS:

in [1]: choose the appropriate JDBC driver
in [2]: select the appropriate Windows version
in [3]: in the downloaded ZIP file, the Java archive containing the JDBC driver is [mysql-connector-java-5.0.5-bin.jar]. We will extract it to use it in the JPA tutorial examples.

We place it as before (section 5.4.7) in the <jdbc> folder:

To test this JDBC driver, we will use Eclipse and the SQL Explorer plugin. The reader is invited to follow the procedure explained in section 5.4.7. We present a few relevant screenshots:

in [1]: we have selected the MySQL5 JDBC driver archive
in [2]: the MySQL5 JDBC driver is available

in [3]: connection definition (user, password)=(jpa, jpa)
in [4]: the connection is active
in [5]: the database is connected

5.6. The PostgreSQL DBMS

5.6.1. Installation

The PostgreSQL DBMS is available at the URL [http://www.postgresql.org/download/]:

at [1]: PostgreSQL download sites
in [2]: choose a Windows version
in [3]: choose a version with an installer

[4]: the contents of the downloaded ZIP file. Double-click the [postgresql-8.2.msi] file
in [5]: the first page of the installation wizard

in [6]: choose a typical installation by accepting the default values
in [6b]: create the Windows account that will run the PostgreSQL service; here, the account is pgres with the password pgres.

in [7]: let PostgreSQL create the [pgres] account if it does not already exist
in [8]: define the DBMS administrator account, here postgres with the password postgres

in [9] and [10]: accept the default values until the end of the wizard. PostgreSQL will be installed.

The PostgreSQL installation creates a folder in [Start / Programs]:

5.6.2. Start / Stop PostgreSQL

The PostgreSQL server has been installed as a Windows service that starts automatically, i.e., it launches as soon as Windows starts. This setup is not very practical. We are going to change it:

[Start / Control Panel / Performance and Maintenance / Administrative Tools / Services]:

In [1]: Double-click [Services]
in [2]: we see that a service named [PostgreSQL] is present, that it is running [3], and that it starts automatically [4].

To change this setting, double-click the [PostgreSQL] service:

in [1]: set the service to manual startup
in [2]: stop it
in [3]: we confirm the new service configuration

To manually start and stop the PostgreSQL service, you can use the shortcuts in the [PostgreSQL] folder:

in [1]: the shortcut to start PostgreSQL
in [2]: the shortcut to stop it

5.6.3. Administering PostgreSQL

In the screenshot above, the [pgAdmin III] application (3) allows you to administer the PostgreSQL DBMS. Let’s start the DBMS, then launch [pgAdmin III] via the menu above:

at [1]: double-click on the PostgreSQL server to connect to it
in [2,3]: log in as the DBMS administrator, here (postgres / postgres)

at [4]: the only existing database
in [5]: the only existing user

5.6.4. Creating a jpa user and a jpa database

The tutorial uses PostgreSQL with a database named jpa and a user of the same name. We will now create them. First, the user:

in [1]: create a new role (~user)
in [2]: creation of the jpa user
in [3]: their password is jpa
in [4]: we repeat the password
in [5]: we grant the user permission to create databases
in [6]: the user [jpa] appears among the login roles

Now for the database:

in [1]: create a new connection to the server
in [2]: it will be named jpa
in [3]: the machine to which we want to connect
in [4]: the user logging in
in [5]: their password. We confirm the connection settings by clicking [OK]
in [6]: the new connection has been created. It belongs to the user jpa. This user will now create a new database:

n [1]: add a new database
in [2]: its name is jpa
in [3]: its owner is the jpa user created earlier. Click [OK] to confirm
in [4]: the jpa database has been created. A single click on it connects us to it and reveals its structure:

in [5]: the objects of the [jpa] schema appear, notably the tables. There are none yet. A right-click would allow us to create them. We’ll leave that to the reader.

We will now create the same [ARTICLES] table as with the previous DBMSs using the SQL script [schema-articles.sql] generated in section 5.4.6.

in [1]: open the SQL editor
in [2]: open an SQL script
in [3]: select the script [schema-articles.sql] created in section 5.4.6

in [4]: the script is loaded. We execute it.
in [5]: the [ARTICLES] table has been created.
in [6, 7]: its contents

5.6.5. PostgreSQL JDBC Driver

The PostgreSQL JDBC driver is available in the [jdbc] folder within the PostgreSQL installation directory:

We place the JDBC archive, as with the previous ones (section 5.4.7), in the <jdbc> folder:

To test this JDBC driver, we will use Eclipse and the SQL Explorer plugin. The reader is invited to follow the procedure explained in section 5.4.7. We present a few relevant screenshots:

in [1]: we have selected the PostgreSQL JDBC driver archive
in [2]: the PostgreSQL JDBC driver is available

in [3]: connection definition (user, password)=(jpa, jpa)
in [4]: the connection is active
in [5]: the connected database
in [6]: the contents of the [ARTICLES] table

5.7. The Oracle 10g Express DBMS

5.7.1. Installation

The Oracle 10g Express DBMS is available at [http://www.oracle.com/technology/software/products/database/xe/index.html]:

at [1]: the Oracle 10g Express download site
at [2]: select a Windows version. Once the file has been downloaded, run it:

in [1]: double-click the [OracleXE.exe] file
in [2]: the first page of the installation wizard

in [3]: accept the license
in [4]: Accept the default settings.

in [5,6]: the SYSTEM user will have the password "system".
in [7]: Start the installation

Installing Oracle 10g Express creates a folder in [Start / Programs]:

5.7.2. Start / Stop Oracle 10g

As with previous DBMSs, Oracle 10g has been installed as a Windows service that starts automatically. We will change this configuration:

[Start / Control Panel / Performance and Maintenance / Administrative Tools / Services]:

in [1]: we double-click on [Services]
in [2]: we see that a service called [OracleServiceXE] is present, that it is running [3], and that it starts automatically [4].
in [5]: another Oracle service, called "Listener," is also active and set to start automatically.

To change this behavior, double-click the [OracleServiceXE] service:

in [1]: set the service to manual startup
in [2]: we stop it
in [3]: we confirm the new service configuration

We will do the same with the [OracleXETNSListener] service (see [5] above). To manually start and stop the OracleServiceXE service, we can use the shortcuts in the [Oracle] folder:

in [1]: to start the DBMS
in [2]: to stop it
in [3]: to manage it (which starts it if it is not already running)

5.7.3. Creating a jpa user and a jpa database

In the screenshot above, the [3] application allows you to administer the Oracle 10g Express DBMS. Let’s start the DBMS [1], then the administration application [3] via the menu above:

in [1]: log in as the DBMS administrator, here (system/system)
in [2]: create a new user

in [4]: user name
in [5, 6]: their password, here jpa
in [7]: the user jpa has been created

In Oracle, a user is automatically associated with a database of the same name. The jpa database therefore exists at the same time as the jpa user.

5.7.4. Creating the [ARTICLES] table in the jpa database

OracleXE was installed with a SQL client running in command-line mode. You can work more comfortably with the SQL Develope , also provided by Oracle. It can be found at:

[http://www.oracle.com/technology/products/database/sql_developer/index.html]

in [1]: the download site
at [2]: choose a Windows version without JRE if it is already installed (as is the case here), since [SQL Developer] is a Java application.

[3]: Unzip the downloaded file
in [4]: run the executable [sqldeveloper.exe]

in [5]: When launching [SQL Developer] for the first time, specify the path to the JRE installed on the machine
in [5b]: create a new connection

in [6]: SQL Developer allows you to connect to various DBMSs. Select Oracle.
in [7]: Name given to the connection you are creating
in [8]: owner of the connection
in [9]: its password (jpa)
in [10]: keep the default values
in [11]: to test the connection (Oracle must be running)
in [12]: to complete the connection configuration
in [13]: the objects in the jpa database
in [14]: you can create tables. As in the previous cases, we will create the [ARTICLES] table using the script created in section 5.4.6.

in [15]: open an SQL script
in [16]: select the SQL script created in section 5.4.6
in [17]: the script to be executed

in [18]: the result of the execution: the [ARTICLES] table has been created. Double-click on it to access its properties.
in [19]: the table’s contents.

5.7.5. OracleXE JDBC driver

The OracleXE JDBC driver is available in the [jdbc/lib] folder within the OracleXE installation directory [1]:

We place the JDBC archive [ojdbc14.jar] in the <jdbc> folder [2], just as we did previously (section 5.4.7):

To test this JDBC driver, we will use Eclipse and the SQL Explorer plugin. The reader is invited to follow the procedure explained in section 5.4.7. We present a few relevant screenshots:

in [1]: we have specified the OracleXE JDBC driver archive
in [2]: the OracleXE JDBC driver is available

in [3]: connection definition (user, password)=(jpa, jpa)
in [4]: the connection is active
in [5]: the connected database
in [6]: the contents of the [ARTICLES] table

5.8. The DBMS s SQL Server Express 2005

5.8.1. Installation

SQL Server Express 2005 is available at [http://msdn.microsoft.com/vstudio/express/sql/download/]:

in [1]: first download and install the .NET 2.0 platform
in [2]: then install and download SQL Server Express 2005
Step [3]: Next, install and download SQL Server Management Studio Express, which allows you to administer SQL Server

Installing SQL Server Express creates a folder in [Start / Programs]:

in [1]: the SQL Server configuration application. Also allows you to start/stop the server
in [2]: the server administration application

5.8.2. Start / Stop SQL Server

As with previous DBMSs, SQL Server Express has been installed as a Windows service that starts automatically. We will change this configuration:

[Start / Control Panel / Performance and Maintenance / Administrative Tools / Services]:

in [1]: we double-click on [Services]
in [2]: we see that a service called [SQL Server] is present, that it is running [3], and that it starts automatically [4].
in [5]: another service related to SQL Server, called "SQL Server Browser," is also active and set to start automatically.

To change this behavior, double-click the [SQL Server] service:

in [1]: set the service to manual startup
in [2]: we stop it
in [3]: we confirm the new service configuration

We will do the same with the [SQL Server Browser] service (see [5] above). To manually start and stop the SQL Server service, we can use the [1] application in the [SQL Server] folder:

in [1]: ensure that the TCP/IP protocol is enabled, then go to the protocol properties.
in [2]: in the [IP Addresses] tab, [IPAll] option:
- the [TCP Dynamic ports] field is left blank
- the server's listening port is set to 1433 in [TCP Port]

In [3]: Right-clicking on the [SQL Server] service gives access to the server’s start/stop options. Here, we start it.
in [4]: SQL Server is launched

5.8.3. Creating a jpa user and a jpa database

Launch the DBMS as described above, then the administration application [1] via the menu below:

in [1]: log in to SQL Server as a Windows administrator
in [2]: configure the connection properties

in [3]: we enable mixed mode for connecting to the server: either with a Windows login (a Windows user) or with a SQL Server login (an account defined within SQL Server, independent of any Windows account).
in [3b]: create a SQL Server user

in [4]: [General] tab
in [5]: the login
in [6]: the password (jpa here)
in [7]: [Server Roles] option
in [8]: the user jpa will have the right to create databases

Confirm this configuration:

in [9]: the user jpa has been created
in [10]: log out
in [11]: log back in

in [12]: log in as user jpa/jpa
in [13]: once logged in, the user jpa creates a database

in [14]: the database will be named jpa
in [15]: and will belong to the user jpa
in [16]: The jpa database has been created

5.8.4. Creating the [ARTICLES] table in the jpa database

As in the previous examples, we will create the [ARTICLES] table using the script created in section 5.4.6.

in [1]: we open an SQL script
in [2]: Select the SQL script created in section 5.4.6, page 240.
in [3]: log in again (jpa/jpa)
in [4]: the script to be executed
in [5]: select the database in which the script will be executed
in [6]: execute it

in [7]: the result of the execution: the [ARTICLES] table has been created.
in [8]: we request to view its contents
in [9]: the table's contents.

5.8.5. SQL Server Express JDBC Driver

in [1]: A Google search for [Microsoft SQL Server 2005 JDBC Driver] takes us to the JDBC driver download page. We select the most recent version
in [2]: the downloaded file. We double-click on it. The file is extracted, creating a folder containing the JDBC driver [3]
in [4]: we place the JDBC archive [sqljdbc.jar] in the <jdbc> folder, just as we did previously (section 5.4.7)

To test this JDBC driver, we will use Eclipse and the SQL Explorer plugin. The reader is invited to follow the procedure explained in section 5.4.7. We present a few relevant screenshots:

in [1]: we have specified the SQL Server JDBC driver archive
in [2]: the SQL Server JDBC driver is available

in [3]: connection definition (user, password)=(jpa, jpa)
in [4]: the connection is active
in [5]: the connected database
in [6]: the contents of the [ARTICLES] table

5.9. The HSQLDB DBMS

5.9.1. Installation

The HSQLDB DBMS is available at the URL [http://sourceforge.net/projects/hsqldb]. It is a DBMS written in Java, very lightweight in memory, which manages databases in memory rather than on disk. The result is extremely fast query execution. This is its main advantage. The databases created in memory in this way can be recovered when the server is shut down and then restarted. This is because the SQL commands issued to create the databases are stored in a log file to be replayed the next time the server starts up. This ensures the persistence of the databases over time.

The method has its limitations, and HSQLDB is not a commercial-grade DBMS. Its main value lies in testing or demonstration applications. For example, the fact that HSQLDB is written in Java allows it to be included in Ant (Another Neat Tool) tasks, a Java task automation tool. Thus, daily code tests for software under development, automated by Ant, can incorporate database tests managed by the HSQLDB DBMS. The server will be started, stopped, and managed by Java tasks.

in [1]: the download site
in [2]: download the latest version

in [3]: unzip the downloaded file
in [4]: the [hsqldb] folder resulting from the extraction
in [5]: the [demo] folder containing the script to start the [hsql] server [6] and in [7], the one to launch a basic server administration tool.

5.9.2. Start / Stop HSQLDB

To start the HSQLDB server, double-click the [runManager.bat] application [6] above:

in [1]: you can see that to stop the server, simply press Ctrl-C in the window.

5.9.3. The [test] database

The default database is located in the [data] folder:

in [1]: upon startup, the HSQL DBMS executes the script named [test.script]

CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 10

Line 1: A [public] schema is created
Line 2: A user [sa] with an empty password is created
Line 3: The user [sa] is granted administrative privileges

In the end, a user with administrative privileges has been created. This is the user we will use going forward.

5.9.4. HSQL JDBC Driver

The JDBC driver for the HSQL DBMS is located in the [lib] folder:

in [1]: the [hsqldb.jar] archive contains the HSQL DBMS JDBC driver
in [2]: we place this archive, like the previous ones (section 5.4.7), in the <jdbc> folder

To verify this JDBC driver, we will use Eclipse and the SQL Explorer plugin. The reader is invited to follow the procedure explained in section 5.4.7. We present a few relevant screenshots:

in [1]: [Window / Preferences / SQL Explorer / JDBC Drivers]
in [2]: configure the [HSQLDB] server
in [3]: specify the archive [hsqldb.jar] containing the JDBC driver
in [4]: the name of the Jdbc driver’s Java class
in [5]: the JDBC driver is configured

Once that is done, we connect to the HSQL server. We start the server first.

in [6]: create a new connection
in [7]: give it a name
in [8]: we want to connect to the HSQLDB server
in [9]: the URL of the database you want to connect to. This will be the [test] database seen earlier.
in [10]: log in as user [sa]. We saw that he is the DBMS administrator.
in [11]: the user [sa] has no password.

We validate the connection configuration.

in [12]: we connect
in [13]: we log in
in [14]: you are logged in

in [15]: the [PUBLIC] schema does not yet have a table
in [16]: We will create the [ARTICLES] table using the [schema-articles.sql] script created in section 5.4.6.
in [17]: select the script

in [18]: the script to execute
in [19]: we run it after removing all comments, as HSQLB does not accept them.

Once the script has run, refresh the database view in [20]
in [21]: the [ARTICLES] table is there
in [22]: its contents

Let’s stop and then restart the HSQLDB server. Once that’s done, let’s examine the [test.script] file:

CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE ARTICLES(ID INTEGER NOT NULL,NAME VARCHAR(20) NOT NULL,PRICE DOUBLE NOT NULL,CURRENTSTOCK INTEGER NOT NULL,MINIMUMSTOCK INTEGER NOT NULL,CONSTRAINT PK_ARTICLES PRIMARY KEY(ID),CONSTRAINT CHK_ID CHECK(ARTICLES.ID>0), CONSTRAINT CHK_PRICE CHECK(ARTICLES.PRICE>0),CONSTRAINT CHK_CURRENT_STOCK CHECK(ARTICLES.CURRENT_STOCK>0),CONSTRAINT CHK_MINIMUM_STOCK CHECK(ARTICLES.MINIMUM_STOCK>0),CONSTRAINT CHK_NAME CHECK(ARTICLES.NAME!=''),CONSTRAINT UNQ_NAME UNIQUE(NAME))
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 10
SET SCHEMA PUBLIC
INSERT INTO ARTICLES VALUES(1,'article1',100.0E0,10,1)
INSERT INTO ARTICLES VALUES(2,'article2',200.0E0,20,2)
INSERT INTO ARTICLES VALUES(3,'article3',300.0E0,30,3)

We can see that the DBMS has stored the various SQL statements executed during the previous session and re-executes them when the new session starts. We can also see (line 2) that the [ARTICLES] table is created in memory (MEMORY). At the start of each session, the SQL statements issued are stored in [test.log] to be copied at the start of the next session into [test.script] and replayed at the start of the session.

5.10. The Apache Derby DBMS

5.10.1. Installation

The Apache Derby DBMS is available at the URL [http://db.apache.org/derby/]. It is a DBMS also written in Java and is similarly very lightweight in memory. It offers advantages similar to those of HSQLDB. It too can be embedded in Java applications, i.e., be an integral part of the application and run within the same JVM.

in [1]: the download site
in [2,3]: download the latest version

in [3]: unzip the downloaded file
in [4]: the [db-derby-*-bin] folder resulting from the extraction
in [5]: the [bin] folder containing the script to start the [db derby] server [6] and in [7], the one to stop it.

5.10.2. Start / Stop Apache Derby (DB Derby)

To start the Db Derby server, double-click the [startNetworkServer] application [6] above:

in [1]: the server is started. It will be stopped using the [stopNetworkServer] application [7] above.

5.10.3. Db Derby JDBC Driver

The JDBC driver for the Db Derby DBMS is located in the [lib] folder of the installation directory:

in [1]: the [derbyclient.jar] archive contains the JDBC driver for the Db Derby DBMS
in [2]: we place this archive, like the previous ones (section 5.4.7), in the <jdbc> folder

To test this JDBC driver, we will use Eclipse and the SQL Explorer plugin. The reader is invited to follow the procedure explained in section 5.4.7. We present a few relevant screenshots:

in [1]: [Window / Preferences / SQL Explorer / JDBC Drivers]
in [2]: the Apache Derby JDBC driver is not in the list. We add it.

in [3]: we give the new driver a name
in [4]: specify the format of the URLs supported by the JDBC driver
in [5]: we specify the .jar file for the JDBC driver
in [5b]: the name of the JDBC driver's Java class
in [5c]: the JDBC driver is configured

Once this is done, connect to the Apache Derby server. Start the server beforehand.

in [6]: create a new connection
in [7]: we give it a name
in [8]: we want to connect to the Apache Derby server
in [9]: the URL of the database we want to connect to. After the standard prefix [jdbc:derby://localhost:1527], we’ll specify the path to a directory on the disk containing a Derby database. The [create=true] option allows us to create this directory if it doesn’t exist yet.
in [10,11]: we connect as user [jpa/jpa]. I haven’t looked into this in depth, but it seems you can use any login and password you want. Here we specify the database owner if create=true.

We validate the connection configuration.

in [12]: log in
in [13]: log in (jpa/jpa)
in [14]: you are logged in

in [15]: the [jpa] schema does not appear yet.
in [16]: we will create the [ARTICLES] table from the [schema-articles.sql] script created in section 5.4.6.
in [17]: select the script

in [18]: the script to execute
in [19]: we execute it after removing all comments, since Apache Derby, like HSQLB, does not accept them.

Once the script has run, refresh the database view in [20]
in [21]: the [jpa] schema and the [ARTICLES] table are there
in [22]: the contents of the [ARTICLES] table

in [23]: the contents of the [derby\jpa] folder where the database was created.

5.11. The Spring 2 framework

The Spring 2 framework is available at the URL [http://www.springframework.org/download]:

at [1]: download the latest version
in [2]: download the version labeled "with dependencies" because it contains the .jar files for the third-party tools that Spring integrates and that you always need.

in [3]: unzip the downloaded archive
in [4]: the Spring 2.1 installation folder

in [5]: in the <dist> folder, you’ll find the Spring archives. The [spring.jar] archive contains all the classes of the Spring framework. These are also available by module in the <modules> folder in [6]. If you know which modules you need, you can find them here. This way, you avoid including archives in the application that it doesn’t need.

in [7]: the <lib> folder contains the archives of third-party tools used by Spring
in [8]: some archives from the [jakarta-commons] project

When the tutorial uses Spring archives, you must retrieve them either from the <dist> folder or the <lib> folder within the Spring installation directory.

5.12. The JBoss EJB3 container

The JBoss EJB3 container is available at the URL [http://labs.jboss.com/jbossejb3/downloads/embeddableEJB3]:

in [1]: Download JBoss EJB3. Note the product date (September 2006), even though the download is being performed in May 2007. One might wonder if this product is still being developed.
in [2]: the downloaded file

in [3]: the unzipped ZIP file
in [4]: the archives [hibernate-all.jar, jboss-ejb3-all.jar, thirdparty-all.jar] form the JBoss EJB3 container. They must be placed in the classpath of the application using this container.