Data access applications
We have just deployed an application that did not require database connectivity. Often, applications in the business world require access to a RDBMS to fulfill their business objective. If an application requires the ability to retrieve from, or store information in, a database, then you will need to create a data source which will allow the application to connect and use the database (DB).
Looking at the figure below, we can see the logical flow of the sample data access application that we are going to install. The basic idea of the application is to display a list of tables that exist in a database schema. Since the application requires a database connection, we need to configure WebSphere before we can deploy the application. We will now cover the preparation work before we install our application.
Each data source is associated with a JDBC provider that is configured for access to a specific database type. The data source provides connectivity which allows an application to communicate with the database.
Preparing our sample database
Before you create a data source, you need to ensure that the appropriate client database driver software is installed. For our demonstration, we are going to use Oracle Express Edition (Oracle XE) for Linux which is the free version of Oracle. We are using version Oracle XE 10g for Linux and the download size is about 210MB, so it will take time to download. We installed Oracle XE using the default install option for installing an RPM. The administration process is fully documented on Oracle's web site and in the documentation which is installed with the product.
We could have chosen to use many open source/free databases, however their explanations and configurations would detract from the point. We have chosen to use Oracle's free RDBMS called Oracle XE, and JDBC with Oracle XE is quite easy to configure. By following these steps, you will be able to apply the same logic to any of the major vendors' full RDMS products, that is, DB/2, Oracle, SQL Server, and so on. Another reason why we chose Oracle XE is that it is an enterprise-ready DB and is administered by a simple web interface and comes with sample databases.
We need to test that we can connect to our database without WebSphere so that we can evaluate the DB design. To do this, we will need to install Oracle XE. We will now cover the following steps one by one.
- Download Oracle XE from Oracle's web site using the following URL:http://www.oracle.com/technology/products/database/xe/index.html.
- Transfer the oracle-xe-10.2.0.1-1.0.i386.rpm file to an appropriate directory on your Linux server using WinSCP (Secure Copy) or your chosen Secure FTP client.
- Since the XE installer uses X Windows, ensure that you have Xming running. Then install Oracle XE by using the rpm command, as shown here:
rpm -ivh oracle-xe-10.2.0.1-1.0.i386.rpm
- Follow the installer steps as prompted:
- HTTP port = 8080
- Listener port = 1521
- SYS & SYSTEM / password = oracle
- Autostart = y
Oracle XE requires 1024 minimum swap space and requires 1.5 GB of disk space to install.
Ensure that Oracle XE is running. You can now access the web interface via a browser from the local machine; by default, XE will only accept a connection locally. As shown in the following figure, we have a screenshot of using Firefox to connect to OracleXE using the URL http://localhost:8080/apex. The reason we use Firefox on Linux is that this is the most commonly installed default browser on the newer Linux distributions.
When the administration application loads, you will be presented with a login screen as seen in the following screenshot. You can log in using the username SYSTEM and password oracle as set by your installation process.
Oracle XE comes with a pre-created user called HR which is granted ownership to the HR Schema. However, the account is locked by default for security reasons and so we need to unlock the HR user account. To unlock an account, we need to navigate to the Database Users | Manage Users screen, as demonstrated in the following screenshot:
You will notice that the icon for the HR user is locked. You will see a small padlock on the HR icon, as seen in this figure:
Click on the HR user icon and unlock the account as shown in the following figure. You need to reset the password and change Account Status to Unlocked, and then click Alter User to set the new password.
The following figure shows that the HR account is unlocked:
The HR account is now unlocked as seen above. Log out and log back into the administration interface using the HR user to ensure that the account is now unlocked. Another good test to perform to ensure connectivity to Oracle is to use an Oracle admin tool called sqlplus. Sqlplus is a command line tool which database administrators can use to administer Oracle. We are going to use sqlplus to do a simple query to list the tables in the HR schema. To run sqlplus, we need to set up an environment variable called $ORACLE_HOME which is required to run sqlplus. To set $ORACLE_HOME, type the following command in a Linux shell:
If you have installed Oracle XE in a non-default location, then you may have to use a different path.
To run sqlplus, type the following command:
The result will be a login screen as shown below:
You will be prompted for a username. Type the following command:
For the password, type the following command:
When you have successfully logged in, you can type the following commands in the SQL prompt:
- SELECT TABLE_NAME FROM user_tables<enter>
The / command means execute the command buffer. The result will be a list of tables in the HR schema, as shown in the following screenshot:
We have now successfully verified that Oracle works from a command line, and thus it is very likely that WebSphere will also be able to communicate with Oracle. Next, we will cover how to configure WebSphere to communicate with Oracle.
Deployed applications use JDBC providers to communicate with RDBMS.
- The JDBC provider object provides the actual JDBC driver implementation class for access to a specific database type, that is, Oracle, SQL Server, DB/2, and so on.
- You associate a data source with a JDBC provider. A data source provides the connection to the RDBMS.
- The JDBC provider and the data source provide connectivity to a database.
Creating a JDBC provider
Before creating a JDBC provider, you will need to understand the application's resource requirements, that is, the data sources that the application references. You should know the answer to the following questions:
- Does your application require a data source? Not all applications use a database.
- The security credentials required to connect to the database. Often databases are secured and you will need a username and password to access a secure database.
- Are there any web components (Servlets, JSP, and so on) or EJBs which need to access a database.
Answering these questions will determine the amount of configuration required for your database connectivity configurations.
To create a JDBC provider, log into the administration console and click on the JDBC Provider link in the JDBC category of the Resources section located in the left-hand panel of the administration console as shown below.
We need to choose an appropriate scope from the Scope drop-down pick list. Scope determines how the provider will be seen by applications. We will talk more about scope in the JNDI section. For now, please choose the Cell scope as seen below.
Click New and the new JDBC provider wizard is displayed.
Select the Database type as Oracle, Provider type as Oracle JDBC Driver, Implementation type as Connection pool data source, and Name for the new JDBC provider. We are going to enter MyJDBCDriver as the provider name as seen in the previous screenshot. We also have to choose an Implementation type. There are two implementation types for Oracle JDBC Drivers. The table below explains the two different types.
Connection pool data source
Use Connection Pool datasource if your application does not require connection that supports two-phase commit transactions...
Use XA Datasource if your application requires two-phase commit transactions.
Click Next to go to the database classpath screen.
As shown in the following screenshot, enter the database class path information for the JDBC provider.
As long as you have installed Oracle XE using the default paths, you will be able to use the following path in the Directory location field: /usr/lib/oracle/xe/oracle/product/10.2.0/server/jdbc/lib.
Click Next to proceed to the next step, where you will be presented with a summary as shown in the following screenshot. Review the JDBC provider information that you have entered and click Finish.
You will now be prompted to save the JDBC provider configuration. Click Save, as shown in the following screenshot. Saving this will persist the configuration to disk the resources to resources.xml.
Before we finish, we need to update the JDBC Provider with the correct JAR file as the default one is not the one that we wish to use as it was assuming a later Oracle driver which we are not using. To change the driver, we must first select the driver that we created earlier called MyJDBCDriver as shown in the following screenshot:
In the screen presented, we are going to change the Classpath field from:
Since WAS 7.0 is the latest version of WebSphere, the wizard already knows about the new version of the oracle 11g JDBC Driver. We are connecting to Oracle XE 10g and the driver for this is ojdbc14.jar.
The classpath file can contain a list of paths or JAR file names which together form the location for the resource provider classes. Class path entries are separated by using the ENTER key and must not contain path separator characters (such as ; or :). Class paths can contain variable (symbolic) names that can be substituted using a variable map. Check your driver installation notes for specific JAR file names that are required.
Click Apply and save the configuration.
Creating a J2C alias
If a database has security enabled, which is the case for most RDBMS, we will need to somehow provide the username and password for the connection. By creating a J2C alias, we can create an authentication resource independent from the provider and data source. Using this approach, we can change the alias if the database username and password are changed without reconfiguring the provider or data source. This is a key concept and WebSphere provides levels of abstraction to allow the configuration of resources independent form each other. So, in a way, you could say that the JDBC Provider, data source, and J2C alias are loosely-coupled.
Navigate to the Security section in the left-hand navigation panel and click on the Global security link as seen in the following screenshot:
Expand the Java Authentication and Authorization Service category in the Authentication section found in the bottom right-hand side of the Global security screen.
Click on J2C authentication data as shown in the following figure:
You will be presented with a screen where you can click New to create a new J2C authentication alias. Please enter HR for the Alias and hr for the User ID and hr for Password as shown in the following screenshot:
Oracle usernames and passwords are not case sensitive.
Click Apply and then Save.
A new alias will be created, as shown below.
We will reference this J2C authentication alias when we create our data source in the following steps.
Creating a data source
In the next steps, we will use the administrative console to create a new data source.
Open the administrative console and navigate to the JDBC section in the left-hand side panel and click on Data sources as shown in the following screenshot:
Select the Cell scope and click New as demonstrated next.
You will now be asked to fill out the data source information.
Enter the value MyDataSource in the Data source name field and the value jdbc/mydatasource in the JNDI name field as shown in the following image.
The JNDI name provides a naming context for the data source as used in resource lookups by application code. Click Next to move on to the next screen where you will be asked to select a JDBC provider for your data source. The following image demonstrates this.
Select the JDBC provider that we created previously.
Click Next, and in the next screen, enter the following value in the URL field: jdbc:oracle:thin:@192.168.0.94:1521/xe
In our example, we are using an oracle thin client URL. An Oracle thin client URL allows Websphere to connect to Oracle via a URL which is made up of the following syntax: <driver_type>:@<host>:<port>/<service_name>. It can be broken up as follows:
<driver_type> = jdbc:oracle:thin
<host> = 192.168.0.94 (the IP address of your Linux machine; it could even be localhost as Oracle is installed on the same machine as WebSphere)
<port>=1521 (Default oracle listener port)
<service_name> = xe (the Oracle instance and service name)
Click Next to set up a security alias. In this screen, there are several fields.
There are two options to apply the J2C alias. They are listed below.
Use when the resource configured in the EJB's deployment descriptor res-auth property is set to Application.
Used when the resource configured in the EJB's deployment descriptor res-auth property is set to Container.
Since our application is controlling access to the database, we are going to use a component-managed alias. We will now select our J2C authentication alias mapping our user id and password to the data source as shown in the image below, so it can connect to the database.
Click Next to view the summary screen and click Finish and then save. You will now see the data source listed in the data sources panel.
Test the data source by selecting the data source you wish to test and click Test Connection. If the data source is configured correctly, you will see a message similar to the one below.
Deploying a data access application
Now that we have created a JDBC provider and a data source which uses the provider, we are now ready to deploy a data access application. Our application comes in the form of an EAR file called HRLister.ear, which can be downloaded from the PACKT Publishing web site at the following location: www.packtpub.com.
The HRLister EAR file contains a single web application which, in turn, contains a servlet called listtable. The application is used to show how to deploy an application which uses a resource reference. The resource reference uses a JNDI lookup to find the data source and allow the application to connect to the HR database.
Our application contains several deployment descriptors; one is called application.xml and the other is called web.xml. The two descriptors detail certain configuration information which will be used by WebSphere during deployment.
During our deployment, we will select the detailed approach so we can see what kinds of steps are required when deploying an application which contains resource references.
Similarly, we will navigate to the Applications panel on the left-hand side navigation panel of the admin console, selecting WebSphere enterprise applications from the application types tree list.
To begin the deployment, click the Install button in the Enterprise Applications section as seen in the main panel.
If you have not already done so, download the HRLister.ear file into c:/temp on your desktop, click on the Browse button to browse for the EAR file in c:/temp, and click Next to continue.
On the next page, select the Detailed-Show all installation options and parameters radio option button which will force the wizard to ask for information during the deployment steps. The wizard is dynamic and will contain more steps depending on the contents of the EAR file. The workings of the sample HRListerEAR EAR file are quite simple; however some EAR files can contain many J2EE artefacts which result in a greater number of steps to complete the deployment process.
Below is a list of the steps we will be presented with by the installation wizard during the installation process.
Selecting installation options
Click Next to enter the Select installation options screen, and change the Application name from HRListerEAR to HRLister. This is a logical name and does not affect the running of the application.
In environments that contain multiple streams of development, it may be prudent to name each application with a standard naming scheme to determine which version of the application is at what stage of the development process.
Mapping modules to servers
Leave all other options as default and click Next to enter the Map modules to servers screen.
Because we have a web application, we need to ensure that the application can map to the appropriate virtual host. A virtual host already exists by default as explained earlier and is allowing HTTP connections to port 9080, the default HTTP port as set by WebSphere.
If we have more than one virtual host, we can choose at this stage to map the web application(s) contained in the EAR file to separate ports. Since our application only contains one web application, we do not wish to change it and will use the default host. If we did wish to change it, we would select the web module and map it to an available virtual host and click Apply which would store the mapping.
Leave the screen set as it is and click Next to move on to the next screen where we will set some loading options.
Providing JSP reloading options for web modules
In the Provide JSP reloading options for Web modules screen, we can once again leave the defaults. The JSP reloading option allows the web server to reload JSPs, which allow for hot deployment of artefacts. This means that we can replace JSPs on the file system and WebSphere will automatically pick up those changed. JSPs are compiled into servlets to work inside the web container and these options decide whether they are cached after the first compile or not.
Mapping shared libraries
The Map Share Libraries section allows the configuration of shared libraries. A shared library is a JAR file that is used by more than one module in an EAR file. An EARfile can contain many web modules and EJB modules and they can be designed to share common code routines. We do not have any other modules in the applications and no shared JARS, so we do not need to worry about shared libraries in this deployment.
We can click Next.
Once again, we have no shared libraries so we need not worry about this page. Click Next to go to the Map resource references to resources page.
Mapping resource references to resources
On the Map resource references to resources page we can see that there is a field called Target Resource JNDI Name as shown in the figure below, and it already has the JNDI name jdbc/mydatasource. If you recall from our datasource creation, this was the JNDI name that we gave to our data source which we want to use to connect to the Oracle database. The reason that this wizard page is presented is to give us an option to override the JNDI name of the data source that we wish to use. The idea here is about decoupling the application from the data source. This level of abstraction means that an administrator can choose which data source is to be used as opposed to the application. There might be a requirement to change the database and move the data to a different data source. This can be done via the wizard at deployment time and also post-deployment by changing the application settings in the administrative console.
The web.xml file contained in the web module defines an internal name that the application uses for the data source. In this application example, it uses the name jdbc/hrdatasource, however, we configured our WebSphere data source to be jdbc/mydatasource. This screen is used to map the two together. This is a very powerful concept of J2EE application design and allows for deployment time changes based on administration decisions which do not affect the application code, providing flexibility of deployment.
Click Next to open the Map virtual hosts for Web modules page.
Mapping virtual hosts for web modules
Here, we leave the default settings and use the default_host which will assign the web module to port 9080. Click Next.
Mapping context roots for web modules
The next page of the wizard is where we set the application Context Root. The context root is used to calculate the base path of the application. All URIs should be relative to the context root; this allows for the application's runtime URL to be changed as required. Each application URI must be unique. If multiple web applications exist in an EAR, or multiple web applications across multiple EAR are installed on the server, then the context root can be overridden by the administrator. The application.xml file contains the default context root and we can override it here if we wish. At this time, we will leave it set to hrlister.
Reviewing the deployment steps
Click Next to review the summary of the wizard's deployment steps and click Finish when you are happy. A report similar to the following will be presented detailing the installation process.
If there are no errors shown in the summary, you can click Save to persist the applications deployment. If there are errors found at this point, you will need to review the logs to see what the problem could be. If you were to find a problem, you can choose not to save the configuration.
After saving to the master configuration, you will be redirected back to the main Enterprise Applications screen, where you will see the HR Lister application currently in the stopped state.
Using the application
Select the HRLister application, as shown in the following screenshot, and click Start to start the application.
Once the HR Lister application has started, open a browser and navigate to the following URL:
The URL can be broken up as follows:
<host_name> = ipaddress or hostname
<port> = 9080, the default host port for WebSphere
<URI> = the resource name, that is, the ListTable servlet
As it loads, the ListTable servlet will connect to the HR database and list the tables in the HR schema, as shown in the following screenshot:
Congratulations! You have now successfully installed and configured a data access application.
In this article we focused on manual deployments in the installation of WebSphere applications. We learned about Java Virtual Machines (JVMs), the web container, virtual hosts, and about deploying applications. We have covered the three different file types used in J2EE applications. A JAR file contains Java utility classes, EJBs, and sometimes shared libraries alongside other manifest information. WAR files are the containers for web application artefacts and EAR files can contain a mixture of WARs and JARs. We deployed two types of applications, one being a simple web application and the other being a data access application which was connected to a database. In this article, we focused on Oracle, however, we know that we could use any other database vendor and WebSphere can be easily configured to talk to other RDBMS types.