Inside NatJet: January 2012

In our previous post we saw how to deploy a NatJet webapp on WebSphere 6.1. In this post, I will describe how to set an efficient database connection pool in WebSphere.

This post is oriented for NatJet but it should works for any JEE WebApp that needs to use the WebSphere database pool.

We will used a Oracle database (I won’t explain here how to install or configure the database). Notice that using a MySql database may be more difficult as IBM does not provide a out of the box connector for MySql.

We will start with a schema that summarizes the main resources and their interactions.

In your webapp you have two files that will be impacted :

WEB-INF/web.xml : in this file you need to declare the jndi resource that you want to used
hibernate.cfg.xml : this file defines the database connection, will just reference the declared jndi resource of the web.xml

In WebSphere, the process is more tricky :

WebSphere stores in a central place all authentications (couples of login/password) independently of their usage
The WebSphere JDBC provider will link together all information necessary to establish a database connection : the type of database (important to handle correctly the connection in the pool), the connection URL (where is the database) and the credential defined previously
And when you will deploy your webApp into WebSphere, it will request you to map any jndi resources defined in the web.xml to one of its jndi resource (in our case a JDBC provider) : this is the mapping object of our schema)

This means, that there will be 3 stages in our process:

Development : adapt two files, hibernate.cfg.xml and web.xml, to declare and use a jndi datasource
Configure Websphere : declare and configure a jndi datasource
Deploy your application and map the two jndi datasources : the one defined at development stage and the one configure in websphere

Development stage

This is the tasks you need to do at development time, before delivering your NatJet WebApp.

Compare to a standard development, you just need to modify two files :

hibernate.cfg.xml
web.xml

In some case, this even won’t be necessary (if you are already using a JNDI Datasource). In this case, you should still check the web.xml file as Tomcat is not so rigorous about the definition of JNDI datasources.

Configure hibernate with hibernate.cfg.xml

NatJet automatically creates a file hibernate.cfg.xml in the src folder. This file allows us to configure hibernate.

Usually, this file contains database information to establish the connection (login, password, url…). All this information should be deleted in the case of a datasource and replace by one property : connection.datasource.

This property defines the name of the JNDI datasource we want to use. Notice, that all resources defined in the web.xml belong to the namespace java:comp/env/.

Convention is to prefix by jdbc all JDBC connections. Thus the name of our JNDI datasource will be jdbc/OracleDS and the way to access it from our application will be “java:comp/env/jdbc/OracleDS”.

We still need to indicate hibernate the dialect.

Follow a sample hibernate.cfg.xml, the line you need to add is in bold.

<?xml version="1.0" encoding="UTF-8"?>

<hibernate-configuration>
    <session-factory>
        <property name="connection.datasource">java:comp/env/jdbc/OracleDS</property>
        <property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property>

Defining the JNDI datasource in the web.xml

We need to add the resource in the web.xml as a resource-ref. We have to reuse the JNDI name we’ve used in hibernate.cfg.xml.

The part has been added at the end of the file : it’s in bold.

<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_ID" version="2.4" 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">
    <display-name>NatJetWebSphere</display-name>
    <servlet>
        <servlet-name>WebContainerSpringServletNS</servlet-name>
        <servlet-class>
            fr.natsystem.natjet.echo2.webcontainer.WebContainerSpringServletNS</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>WebContainerSpringServletNS</servlet-name>
        <url-pattern>/app</url-pattern>
    </servlet-mapping>
    <listener>
        <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
    </listener>
    <listener>
        <listener-class>
            org.springframework.web.context.request.RequestContextListener</listener-class>
    </listener>
    <welcome-file-list>
        <welcome-file>index.html</welcome-file>
    </welcome-file-list>
    <session-config>
        <session-timeout>5</session-timeout>
    </session-config>
    <resource-ref>
      <description>DB Connection</description>
      <res-ref-name>jdbc/OracleDS</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
    </resource-ref>

</web-app>

Notice that we have to indicate its type : sql.Datasource

Check nsCommonContext.xml

In a NatJet WebApp, you define the way you connect the database in the nsCommonContext.xml file. This file should already be correct, nevertheless we are going to check that :

you are using the correct hibernate.cfg.xml file
you’ve optimized your webApp configuration by forcing database connection at starting time and not the first time a user connects.

Use XML editor to open nsCommonContext and find the natorb bean definition.

forceInitSession to true to force database connection at the beginning.
check that sessionResource indicate the right hibernate.cfg.xml (you can use a file with a different name than the default one, but in this case you should change the sessionResource property.

Defining a JDBC provider in WebSphere

The following process is an administrative task in WebSphere : you may not have to do it. Your WebSphere administrator may request you to use an existing and configured datasource.

This process first defines 2 resources (oracle driver and authentication) and then links both of them to create a JNDI Datasource.

Defining Oracle driver

This is the first administrative step : you need to provide WebSphere the jdbc Oracle driver.

When prototyping a webApp, you usually include the Oracle ojdbc14.jar in the WEB-INF/lib folder of your WebApp. Thus, the jar is delivered with your WebApp.

When you want to use a datasource (and this is true for any web server as Tomcat, JBoss or WebSphere), this is the web server that establishes the database connection independently of any WebApp. Thus the driver should be available to the server even if the webApp is not deployed : it is no more possible to put the ojdbc14.jar in your WEB-INF/lib folder : Or, more exactly, this will be of no help.

To solve this problem, you should define with your WebSphere administrator where should be stored jdbc drivers for you WebSphere server. Some store the jar in ${WAS_INSTALL_ROOT}/lib. In our case we will create a folder C:\IBM\driver as we are working under Windows and WebSphere is installed under C:\IBM\WebSphere.

Copy the Oracle JDBC driver ojdbc14.jar to the directory you’ve previously selected (in our case C:\IBM\driver).

The ojdbc14.jar driver can be retrieve from your Oracle install or from Oracle website http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html but if you’ve already tested your WebApp, you should already have somewhere (like WEB-INF/lib) this ojdbc14.jar file.

Notice : classe12.jar,the former Oracle driver should be considered as obsolete and replaced by a ojdbc14.jar. It is also better to upgrade your ojdbc14.jar to a version superior or equals to the one provided with Oracle 10G R2.

Define Database Authentication in WebSphere

Connect to your administrative WebSphere console : https://nomserveur:9043/ibm/console.

In the node "Activités guidées", select the link "Connexion à la base de données".
Open the node "Configuration des justificatifs pour l'accès sécurisé à une base de données"
On the bottom, click on the link "Cliquer pour l'exécution"

Then press the button “Nouveau/New” in the “JAAS – Données d’authentification J2C” panel.

In the new “JAAS – Données d’authentification J2C > Nouveau/New”

Enter the following value :

Alias : OracleDemo
ID utilisateur/login : the user login for the database. The famous default scott for Oracle Database
Mot de passe/Password : the user password for the database (the famous default tiger)
Description : Database Oracle for demo

Press button "Valider/validate" and then"Ok"

At the end of the node "Configuration des justificatifs pour l'accès sécurisé à une base de données",
there is a link "Etape suivante/Next Step". click on this link to move to the next step of the process.

Configure a JDBC Provider

When you’ve pressed on the link "Etape suivante/Next Step", a new node should have opened : "Configuration d'un fournisseur JDBC/Configure a JDBC provider”.

In this step you will define the right to use an Oracle driver from any WebApp. in WebSphere terminology, we are talking of a JDBC Provider. You need only one JDBC Provider by database type : It means one for all your Oracle 10G R2 databases. A JDBC Provider is neither link to a login/password, neither to a database URL.

Notice that if you need to define a new JDBC Provider, you will need at the end of the process to restart your WebSphere server.

Continuing, at the end of the text, there is a link "Cliquer pour l'exécution". Click on this link. A new panel “Fournisseur JDBC/JDBC provider” is displayed.

In this panel "Fournisseur JDBC/JDBC provider" :

1. select node: server1 for the field “portée/scope”
2. press button Nouveau/New

In the new panel "Créez un fournisseur JDBC/Create a new JDBC provider" input :

Type base de données/Database type : Oracle
Type de fournisseur/provider type : Oracle JDBC driver
Type d’implementation/Implementation type : Connection pool data source. That means we want WebSphere to manage this database pool of connections. We are doing all this fuss to use this mechanism, so pick this one.
Nom/Name : Oracle JDBC Driver Demo
Description : My Oracle JDBC provider for demo

Then press button “Suivant/Next”

At this step we need to provide the path to the JDBC driver we’ve copied before. The path will be available in the ORACLE_JDBC_DRIVER_PATH variable.

Thus in the field : “Chemin d’accès aux classes” input the value “C:/IBM/driver/ojdbc14.jar”.

Press button “Suivant/Next”, the summary of the definition is displayed.

Then press “Terminer/Terminate”. The panel “Fournisseurs JDBC/JDBC Providers” is displayed. You should see a new line “Oracle JDBC Driver Demo”. The name match the Nom/Name define in "Créez un fournisseur JDBC/Create a new JDBC provider".

At this point you’ve configured a JDBC provider : in WebSphere that means that you can use a Oracle driver. You haven’t yet configured a database connection. This is just the right to use Oracle driver.

Close the node "Configuration d'un fournisseur JDBC".

Configure a JNDI datasource

Open the next node : "Configuration d'une source de données".

At the end of the text, there is a link "Cliquer pour l'exécution". Click on this link. A new panel “Fournisseur JDBC/JDBC provider” is displayed.

In this panel "Fournisseur JDBC/JDBC provider" :

1. select node: server1 for the field “portée/scope”
2. click on the line “Oracle JDBC Driver Demo” : this matches the one you’ve just created.

The panel "Fournisseurs JDBC > Oracle JDBC Driver Demo" is displayed. You should have for the field “Chemin d'accès aux classes :” the following value : ${ORACLE_JDBC_DRIVER_PATH}/ojdbc14.jar

In the right part of the page, there is a link "Sources de données/Data source". Click on this link. A new panel : "Fournisseurs JDBC > Oracle JDBC Driver Demo > Source de données" is displayed.

Press button "Nouveau/New", and input in panel “Entrez les informations de base relatives à la source de données/Enter basic data source information” the following :

“Nom de la source de données/Data source name” : Oracle JDBC Driver Demo. This is just a display name
“Nom JNDI/JNDI name” :jdbc/OracleDS. This is the jndi name of the datasource.
For “Alias d'authentification géré par composant/Component-managed authentication alias and XA recovery authentication data”, select : OracleDemo. this is the value you’ve defined in chapter “database Authentication in WebSphere”.

Press button “Suivant/Next”, the step 2 is displayed. At this point you should define the URL to access your database : this should match the hibernate.connection.url property of your former hibernate.cfg.xml file.

It should be something like : jdbc:oracle:thin:@servername:1521:ORCL where servername is the name of your server or it IP address, 1521 is the port, ORCL is the Oracle database name.

Be careful not to use an URL that specifies the username and its password.

Select “Classe auxiliare au magasin de données”

And uncheck “Utiliser cette source de données dans la persistence CMP”

Press button “Suivant/Next”, the summary is displayed.

Press button “Terminer/Terminate”. The panel "Fournisseurs JDBC/JDBC Provider > Oracle JDBC Driver Demo > Source de données/Datasource" is displayed. You should see a line with JNDI name “jdbc/OracleDS” displayed.

You can click on link “Tester la connexion/test connection” to validate the connection is working.

Now you can save your new configuration : just click on link Save/Sauvegarder on top of the page.

Restart WebSphere server

At this point, you need to restart the server. Yes, the server ! not just the WebApp. This is compulsory if you’ve just defined the JDBC Provider (the ojdbc14.jar). If you reused an existing one (A JDBC Provider) you shouldn’t have to restart the server.

Stopping a server request the administrative password in Windows.

This cannot be done from the administrative web console.

Notice that at this point, all configuration you’ve made in WebSphere are independent from your WebApp. Part of the configuration is dependent from the database but none from your WebApp. This means that you can reuse this part from one WebApp to the other as far as they use the same database.

Deployment

This is the last step of the process and it has to be done only once for each WebApp. If you redeploy the same WebApp after some bug fixing or a new version this specific part is not necessary.

To deploy a WebApp in WebSphere you need to be connected to the web admin console : https://nomserveur:9043/ibm/console.

Then you go to the node “Applications” and select “Applications d’entreprise”. If your WebApp appears in the list you need to stop it and the Update it. When you go through the process of updating at step 3, you will be required to map resources. This is the one thing you should do that is new when using a JNDI Datasource.

So you are in Step 3 “Mapper les références de ressources vers les ressources", you have a group javax.sql.Datasource displayed.

Press the button “Parcourir/Browse”. The panel "Applications d'entreprise/Entreprise application > Application d'entreprise > ressources disponibles/available resources" is displayed. A line jdbc/OracleDS should appear in the list.

Select the line “jdbc/OracleDS” and press button “Valider/Validate”.

You should go back to “javax;sql.Datasource” and in the list at the bottom, the JNDI name for the web.xml module of your application should be jdbc/OracleDS.

Above, this list, :

select radio button “Utiliser la méthode par défaut/Use default method”
Select in drop down list "OracleDemo". This is the credential you’ve defined before.
Check the box of your line in the former list
Press button “Valider/Validate”

The page is refreshed, the column configuration of the connection is updated :

Conteneur/Container : Méthode d’authentification/Authentication method
DefaultPrincipalMapping : mypcnameNode01/OracleDemo

You can process as usual for the end of the deployment.

If you deploy a new application, you should get the same step where you will be requested to define the mapping of the Database JNDI resources. This step appears every time deployment encounters a new resource defined in the web.xml of a WebApp.

Conclusion

Using the database connections pool provided by your WebSphere Server should be your preferred solution to access database. One of the main effort of high ended webserver providers as IBM, is research on optimization for efficient database pooling. It will be quite inconsistent of using WebSphere and rely on the non production hibernate pooling for database access : this is what happens when you don’t use a JNDI datasource with hibernate, you let hibernate handle the pool of connections. JBoss that provides Hibernate, does not consider this embedded pool as reliable for production.

The process may appear quite cumbersome, but it is a standard part of WepSphere administration, and your admin should provide to you,the developer, a configure JNDI datasource to access your database. In this case, you shouldn’t need to go through all this long process and focus on the development part.

Inside NatJet

Wednesday, January 4, 2012

NatJet and Database Connections pool with WebSphere 6.1