Monday, December 7, 2009

Oracle Connection Pool in GlassFish 2.x.x

Glassfish does not ship with oracle jdbc drivers. The oracle driver needs to be added manually.  The driver can be found either in a JDeveloper or oracle client or database installation in {install_path}/jdbc/lib, or from oracle's site:

http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_10201.html

Glassfish needs at least ojdbc14.jar to work properly. Older drivers do not work. Put the ojdbc14.jar in {glassfish}/lib directory and restart glassfish:

asadmin stop-domain domain1
asadmin start-domain domain1

Now you need to navigate to the administrator application:
http://localhost:4848/
 
Open the 'Resources' -> 'JDBC' -> 'Connection Pools' and select 'New.
Fill in the Connection Pool 'Name'.
Select 'Resource Type' : 'javax.sql.ConnectionPoolDataSource'.
Select 'Database Vendor' : 'Oracle'.
Select 'Next'.

At the next page go down at the additional Properties.
Select all the properties and select 'Delete Properties'.
Select 'Add Property', fill 'Name' : 'user' and 'Value' : {db_user}
Select 'Add Property', fill 'Name' : 'password' and 'Value' : {db_user_pass}
Select 'Add Property', fill 'Name' : 'url' and 'Value' :
jdbc:oracle:thin:@[host][:port]:SID
or
jdbc:oracle:thin:@//[host][:port]/SID
Select 'Finish'.

Navigate to Connection Pools and select the pool you just created.
Click Ping to test that it is working. If it is not, it is probably because you have mistyped the connection credentials. Go to 'Additional Properties' tab of the connection pool detail page and correct any errors. Try ping again.

Open the 'Resources' -> 'JDBC' -> 'JDBC Resources' and select 'New.
Fill the JNDI Name like 'jdbc/myconnnection'.
Select from 'Pool Name' combo box the pool we already created.
And select 'OK'.

The Connection Pool is ready!


p.s.
You can find me on fiverr for more personalized requests on any java app server configuration problem or java error that you encounter, with deliverance of less than a day (true!) and money back guarantee if not satisfied.

13 comments:

  1. Ok, and what next?

    How can I use it in, for example, EJB3?

    ReplyDelete
  2. My friend Dennis, you can make a web application the way you like it:
    ejb 3, java beans or even simple jsps
    and then you deploy it to GlassFish.
    You can find many good tutorials in google for web technologies.
    I hope this was helpful.

    ReplyDelete
  3. Muy bueno, why javax.sql.ConnectionPoolDataSource
    and not oracle.jdbc.pool.OracleDataSource

    ReplyDelete
  4. ThanQ Mr.komarios

    ReplyDelete
  5. :(

    An error has occurred
    Ping Connection Pool failed for OJDBPOOL. Class name is wrong or classpath is not set for : oracle.jdbc.pool.OracleConnectionPoolDataSource Please check the server.log for more details.

    ReplyDelete
  6. My friend Mohammed, The error you are getting arises because the oracle jdbc library was not installed properly.
    Please follow carefully all the steps of the tutorial and you will not have any problems.

    ReplyDelete
  7. oracle jdbc library installed but there is an Error
    java.lang.NoClassDefFoundError: Could not initialize class oracle.jdbc.pool.OracleConnectionPoolDataSource Could not initialize class oracle.jdbc.pool.OracleConnectionPoolDataSource


    I'm sure the class is founded :(

    thank you :)

    ReplyDelete
  8. Mohammed,
    The NoClassDefFoundError is a well known Java error reporting that it can not find the class definition for the mentioned class anywhere in the ClassPath directories. So it is obvious that the library is not in the correct directory.
    Maybe you could help me understand your problem by telling me the exact steps you have taken. And please elaborate as much as you can (about paths, GF version, oracle library version, Operating system etc).
    I will be glad to solve your problem as soon as I can.
    Cheers.

    ReplyDelete
  9. Thank you sir for making such a useful blog! Because of this, I was able to complete my graduation project, which required the connection of a version 2.1 glassfish to an oracle database.

    The trickiest part was figuring out where the Glassfish folder was located. Installing Java feels like such a mess.

    ReplyDelete
  10. Thank you, very helpful.

    ReplyDelete
  11. Nice blog. I'm getting java.lang.NoclassDefFoundError: oracle/sql/converter/CharacterConverterJDBC when trying to Ping the connection to Oracle DB 11.2.0.4.
    Have you encountered such error? I'm using Glassfish 4.1

    Thanks

    ReplyDelete
  12. Hi,
    This is a common error and it actually means that the ojdbc6.jar (the version which ships with oracle 11.x) is not installed correctly in glassfish.
    Please, follow the instructions above to install it.
    Cheers.

    ReplyDelete