Configuring a (Oracle) datasource on Glassfish

16

With Java EE 5 being final, application servers need to be upgraded to support features like EJB 3.0. In some cases, loading the Java EE 5 jars may be sufficient. But in most cases a fully Jave EE 5 compliant application server is needed. Enters Glassfish, the free, open
source
application server which implements
the newest features in the Java EE 5 platform
.

One of the issues I had with Glassfish was configuring a datasource so my web application could use it. This blog will tell you how I did it. I am dealing with configuring an Oracle datasource here, but the instructions should work similarly on other databases.

....

First, you’ll need Glassfish. Go to the Glassfish project page and download it. See the instructions at the bottom of the downloads page for how to install Glassfish. Next, start up the default domain on the Glassfish server. See the Glassfish Quick Start Guide for more instructions if this next command fails

asadmin start-domain
domain1

After having started you can check and verify that the installation and startup were done correctly by opening a browser and browsing to

http://localhost:4848

or to the correct host if you decided to install Glassfish elsewhere. This will open the login screen to the Glassfish admin console.

In order to get the Oracle (or your favourite database) loaded in Glassfish, Glassfish needs to be shut down again. To do this, issue

asadmin stop-domain
domain1

Next, copy the database driver jar to <glassfish install dir>/lib. In case of Oracle, the Glassfish Administration Guide asks for the ojdbc14.jar file. Once copied to the lib dir, start Glassfish again.

Reload the admin console and login again. Expand the Resources menu item and then the JDBC menu item in the menu to the left. The JDBC menu contains two submenus, being JDBC Resources and Connection Pools. The JDBC Resource is exposed to (web) applications through JNDI. In order to create a JDBC Resource, you’ll need a Connection Pool first.

To define an Oracle Connection Pool, click on connection Pools in th menu to the left. Next, click the New button to enter the Connection Pool wizard. The first thing to do is specify the Connection Pool Name, Resource Type and Database Vendor. The Resource Type should be javax.sql.ConnectionPoolDatasource. Select the database vendor from the select box. Please note that many database vendors are specified in the list, but for most of them the database drivers are missing! In case of Oracle, select Oracle (duh) and click the Next button.

In the General Settings section, make sure the Datasource Classname is set to either oracle.jdbc.pool.OracleDataSource (which is the default) or oracle.jdbc.xa.client.OracleXADataSource. Set all the other parameters as you like and scroll down to the Additional Properties section.
In this section, three or four properties are needed. All others can be discarded. The three needed ones are User, Password and URL.
The fourth, optional, property is xa-driver-does-not-support-non-tx-operations. This property is only needed if both non-XA and XA connections are retrieved from the same connection pool. Might degrade performance. See chater 2 of the Glassfish Administration Guide.
So, specify the username, password and url. The last one is of the form jdbc:oracle:thin:@<host>:<port>:<sid> where <port> usually is 1521.
After specifying these properties, click Finish. This will bring you back to the connection Pool overview. Click on the Connection Pool name you just created and then the Ping button to see if the connection can be established. If you see Ping Succeeded you’ve set up the connection pool correctly.

Now click on the JDBC Resources menu item in the menu to the left and then on the New button to create a new JDBC Resource. Enter the name for the JDBC Resource and bear in mind that JNDI JDBC datasource names usually start with "jdbc/" so enter a name like "jdbc/hr" or so. Also select the Connection Pool name you just created and successfully pinged from the select box. Hit the OK button and you’re done.

Share.

About Author

16 Comments

  1. Hi I found your article extremely helpful, and I am so grateful for it, however, as a newby I needed more information on the .jar files.   The following would be my revisions.

    For instance, http://www-01.ibm.com/support/docview.wss?uid=swg21330098 discusses the differences between the driver you mention in your article ending in 14.jar and the newer drivers for folks using the newer updates of JDK (Java Developers Kit) and also for Oracle, whether they are using 10g or 11 g.

    Also one of the Glassfish links is broken on the blog you created now.

    The best advice I can give anyone trying to adapt their solution to your great and wonderful and prosaic knowledge is to realize that your driver has to be the same JDK as what you have installed, meaning if you installed JDK 1.6, you have to stay with 1.6, if you installed 1.5 you have to stay with 1.5 and if you installed 1.4 you have to stay with 1.4, and also when they go to the Oracle JDBC drivers download page located here:

    http://www.oracle.com/technetwork/indexes/downloads/index.html

    They need to realize that when they download the .jar file associated with the right Oracle and the right JDK version they are running, that you DO NOT EXTRACT the file!  Just put the file in the target directory. AGAIN DO NOT EXTRACT!!!!

    One other issue in the article that I feel should be addressed is that there is a CLASSPATH variable that has to be set, you can call it CLASSPATH_WHATEVER or JAVA_CLASSPATH, but it has to point to the jar file.
     

  2. I did the same configuration as you mention above. But I dont know why when I ping I get the error “ORA-00604: error occurred at recursive SQL level 1 ORA-12705: Cannot access NLS data files or invalid environment specified”…

    Enviroment is
    Oracle Database 10g Express Edition Release 10.2.0.1.0 – Product
    PL/SQL Release 10.2.0.1.0 – Production
    CORE 10.2.0.1.0 Production
    TNS for 32-bit Windows: Version 10.2.0.1.0 – Production
    NLSRTL Version 10.2.0.1.0 – Production
    Win Xp Sp3

    And The Locale is tr_TR.

    thanks

  3. Craig Cruden on

    Followup, I copied the newest driver back, and did a ping. Came back with the same error related to ConnectionCachingEnabled. I compared the properties between work and home, and noticed it is not a property at work (old driver). I deleted the "ConnectionCachingEnabled" which was "false". It then came up with another error related to FastConnectionFailoverEnabled. I then deleted "FastConnectionFailoverEnabled" which also was false, and tried ping again. Ping succeeded.

  4. Craig Cruden on

    James, I was getting the same problem. I think it has something to do with the new Oracle JDBC driver and glassfish (or at least the configuration). I had setup glassfish at work and it pinged correctly. At home I copied a local (newer version) of ojdbc14.jar and was getting that error. I then copied a newer version down from Oracle — and that one failed (both over 1,500,000 in size – or close to it). I could not see any difference in configuration – so I copied the ojdbc14.jar from my work computer (size 1,200,046), and it worked (the ping). I suspect that the older driver is probably going to kick me later, but it was the only one that I got working. I am wondering if some of the EJB3 updates that they added — does not allow a certain attribute to be updated (for whatever reason).

  5. James Cummings on

    I got the error:

    Operation ‘pingConnectionPool’ failed in ‘resources’ Config Mbean. Target exception message: Access denied to execute this method : setConnectionCachingEnabled

    So there appears to be a permission issue of some sort. The user that installed the GlassFish software is in the administrators group (this is a WinXP Pro x64 machine).

    Any ideas?

  6. @cambazz:

    I got this procedure to work with Oracle XE by selecting javax.sql.DataSource as the resource type and deleting the following two additional properties: ConnectionCachingEnabled and FastConnectionFailoverEnabled. Delete both of those properties, hit “save”, and then click the ping.

    HTH

  7. Hello,

    When I click ping I get “Operation ‘pingConnectionPool’ failed in ‘resources’ Config Mbean. Target exception message: Access denied to execute this method : setConnectionCachingEnabled” error message.

    I am using oracle xe on winxp.

    best regards,
    -C.B.

  8. Wouter van Reeven on

    Hi Eduardo,

    Thank you for bringing my blog entry to the attention of your readers. I will spread the word within AMIS about your request to send any future Glassfish related blog entries to the theaquarium email address.

    Take care, Wouter