Lesson 3: Set up the database connection

In this lesson, you will connect your project to the database that you imported in the previous lesson.

About this task

Because the goal of this tutorial is not to teach you how to connect to a database, this lesson will not explain the process in detail. In short, you will set up a database connection that allows your EGL application to connect to the database both when you design the application (the design-time connection) and when you run the application on the server (the run-time connection).

As explained in a previous lesson, WebSphere® Application Server uses database connection information in the EAR project. Tomcat uses database connection information in the web project. Thus, the database connection steps differ for each server.

Creating the connection

About this task

Regardless of which server you are using, you must create a design-time connection to the database. If you are using WebSphere® Application Server, EGL automatically creates a matching run-time connection as well. If you are using Tomcat, additional steps later in this lesson will guide you through associating this connection with Tomcat. In the next lesson, you will also use this design-time connection to create starter EGL code.

Show Me

Procedure

In the Enterprise Explorer view, right-click the EGLWeb project and then click Properties.
In the Properties window, click EGL Runtime Data Source.
On the EGL Runtime Data Source page, select Load values from a data tools connection and then click New.
The New Connection window opens.
In the Connection Profile window, under Connection profile types, click Derby.
For Name, type the following name:
```
EGLDerbyDB
```
You can leave the description blank. Click Next.
From the Drivers list, leave the default BIRT SampleDb Derby Embedded Driver.
Under Properties, in the Database location field, click Browse and navigate to the following folder:

workspace-location/EGLWeb/WebContent/EGLDerbyDB

workspace-location is the full path to your current workspace. Click OK.
Clear the User name field and leave the Password field blank. You do not need a user name or password for this database.
Accept the default value for URL. You can clear the check box for Create database (if required) as the database already exists. Make sure Connect when the wizard completes is selected.
Click Test Connection.
If all information is correct, the New Connection window should look like the following example, with your own workspace and location information in the Database location field:
Click OK to close the test connection window.
Click Finish.
The new connection is created and the necessary information for the connection is filled into the fields below:

Note that EGL has given this connection a JNDI name, which is an identifier for the connection. By default, the JNDI name is jdbc/EGLDerbyDB, based on the name of the database. The application will use this name to access the database connection at run time.
Click OK.
You may see a window asking if you want to update the information in the default build options for this project. If you see this window, click Yes.
In the Project Explorer view, expand the EGLWeb project and the EGLSource folder. Open the build file for the project by double-clicking the EGLWeb.eglbld file in the Project Explorer view.
The build file opens in the build parts editor.
Check to see that the EGL Runtime Data Source window has set the build descriptor options based on the connection information.
The build descriptor options should look like this for WebSphere® Application Server:
For the database connection to work, the following options need to be set:
dbms

This build descriptor option indicates the type of database, in this case DERBY.

sqlDB
This build descriptor option indicates the connection URL, or a string that the server uses to find the database. The format of the connection URL differs for each type of database, but for Derby, the format is the connection protocol (in this case JDBC), a colon separator, the type of database (Derby), another colon separator, the path to the database on disk, and any parameters for the connection. In this case, the connection URL is something like the following example, with the path to your database in place of D:\MyData\workspace_jsf_tutorial:
jdbc:derby:D:\MyData\workspace_jsf_tutorial\EGLWeb\WebContent\EGLDerbyDB
sqlValidationConnectionURL

This build descriptor option sets a connection URL to be used to validate the connection to the database. In this case, as in most cases, this option is the same as sqlDB.

sqlJDBCDriverClass

This build descriptor option sets the name of the database driver, the program used to access the database. The New Connection window retrieved this name from the derby.jar file: org.apache.derby.jdbc.EmbeddedDriver.

sqlJNDIName

The JNDI name that represents the connection at run time.
If the build descriptor options have been set based on the information you filled into the New Connection window, close the build descriptor without making any changes. If the build descriptor options have not been set, follow these steps to set them:
1. In the Load DB options using Connection list, select your EGLDerbyDB connection.
  Several of the options are set, except for the sqlJNDIName option.
2. Set the sqlJNDIName option to the following JNDI name, exactly as shown:
```
jdbc/EGLDerbyDB
```
  Note:
  To open the sqlJNDIName option for editing, click twice slowly in the Value column next to that option. Also, you can click three times quickly in the Value column.
  The values of the build descriptor options now match those described above.
3. Save and close the build descriptor.
4. Optionally, set the EGL Runtime Database Connection window to make these changes in the future by enabling the associated preference. Click Window > Preferences and then click EGL > Default Build Descriptor. Under Update default build descriptor options for project when runtime data source is modified, select Always to update the build descriptor options automatically, or select Prompt to give you the option.
  This preference takes effect the next time you use the EGL Runtime Database Connection window.

Lesson checkpoint

In this lesson, you set up a database connection for the project.

Procedure

When you used the EGL Runtime Data Source page of the project's Properties window, you first created a design-time connection to the database using the workbench's data tools. Then, if you were using WebSphere® Application Server, EGL used the information in this design-time connection to create a matching connection to be used at run time. In this case, the changes EGL made to your projects include:

EGL set the values of certain database-related build descriptor options, as explained earlier in the lesson.
EGL created a JNDI name to use as a name for the connection. By default, the JNDI name created for your project is jdbc/EGLDerbyDB, based on the name of the database.
EGL added a resource reference to that JNDI name in the EGLWeb project's web deployment descriptor, WebContent\WEB-INF\web.xml. Now the EGLWeb project can use the data source defined in the EAR project, using the JNDI name. The editor looks very different depending on the version of the application server you use. The following example shows the editor for WebSphere® Application Server Version 8.

If you are using Tomcat, your project doesn't have an EAR project, so EGL added a contexts file to the web project that gives the information for the connection. The contexts file performs essentially the same task as the information in the deployment descriptors: it associates the JNDI name with the location of the database and other information that the server needs to connect to it. The connection is valid only for this project.

Note:

From this point forward, most of the steps are the same regardless of which server you are using.