This chapter describes how to configure a database connection and the Java EE data source resource using the WAS Administration Console. As you will see in a minute, the configuration of a data source with WAS is not performed in a single step but is broken down into several parts, each one concerned with a particular aspect (JDBC Provider, Database connectivity, Security). Although this might appear quite a lot of work to do, it allows for extreme flexibility if you need to create or modify several data sources which are maybe different just for one aspect.
Creating a data source with WAS can be split down into three steps:
1. Creation of a JDBC Provider which connects to the JDBC Driver implementation
2. Creation of a J2C alias to connect to the Database
3. Creation of the data source
The first step requires that you have a JDBC driver which connects to the database. In this recipe we will show here how to connect to an Oracle database. To create a JDBC provider, log into the WAS administrative console and choose Resources > JDBC > JDBC Providers.
There you can see a list of available JDBC Drivers which by default include the Derby JDBC driver.
The default DerbyJDBC driver can be used to connect to Derby open source relational database. The advantage of using this solution is mostly the small footprint and the usage of SQL standards. You can use it for learning or for non-core solutions.
In order to create a new JDBC Driver, choose at first the scope which you want to use for your Driver (1). Next click “New” (2) which will move you to the next screen:
In the above screen, select the Database type you are going to connect (Oracle) among the list of available DB types. For each Database type there can be one or more Provider types; in our case we have selected the Oracle JDBC Driver. Next you need to specify the Implementation type which should be “XA Datasource” if you are planning to use two-phase commit protocol transactions in your application. Otherwise stick to the basic connection pool. (Once you have selected this combo, the wizard will automatically choose some defaults for Name and Description). Click on Next.
In the next step, you will need to check in the JDBC Driver information as indicated by this screen:
The path specified (2) in this screen will be automatically included into the ORACLE_JDBC_DRIVER_PATH variable.
Click Next to proceed to the Summary screen where you can review your configuration. Once done click on Finish and you will be redirected to the main JDBC Driver page which now includes the Oracle JDBC Driver.
In order to connect to our database we need to set up a J2C authentication alias which will contain the username and password of our database user. In order to do that, complete the following steps:
1. Expand the Security Node and click on Global security.
2. In the Authentication area, expand Java Authentication and Authorization Service and click on the J2C authentication data:
In the next screen click New in order to create a new authentication alias.
You will be taken to the following screen where you will enter an alias name, user and password for connecting to the database, as shown in the next picture:
Click Apply and then Save. A new J2C authentication alias has been created that can now be referenced during the data source creation.
Now we have all the ingredients to define our data source. In order to do that, perform the following steps:
1. Open the Administrative console and navigate to the Resources > JDBC section and click on Data sources.
2. Select the Scope (1) for your data source (include the server where you are running your JDBC applications) and click New (2) as shown by the next screenshot:
The data source wizard will start. In the Step 1 enter the value for the Data source name field and the value for the JNDI name field, where the data source will be bound:
Click Next to move on to the next screen. In Step 2 you will be asked to select a JDBC provider for your data source. Select the JDBC provider that we created previously, as seen in the following screenshot:
Click Next. In the Step 3 enter into the URL field the JDBC connection string, which is required to connect to the JDBC Driver, as shown by the following picture:
Click Next. In Step 4 you will need to associate your data source with a security alias:
There are two options for applying the J2C alias, which are listed as follows:
This involves creating a mapping from an alias name to the username and password. As the alias can only be resolved in the application server, this restricts authenticated access to applications running in the application server.
This works in much the same way as the component-managed alias but the data source needs to be looked up via a resource-reference that specifies a res-auth of Container. As a consequence, the administrator must have explicitly bound the resource-reference to the resource on deployment of the application
In our example we are supposing that our application is controlling access to the database, so we are going to use a Component-managed authentication alias. Click Next to review your data source configuration and, once done, hit Finish and then Save. You will now see the data source listed in the data sources panel:
In order to verify the database connectivity you can test the data source by selecting it from the main data source view and clicking Test Connection. If the data source is configured correctly, you will see on the screen a message like:
“The test connection operation for data source [data source name] on server [servername] at node [nodename] was successful”