Monday, December 14, 2009

Create Postgresql Connection Pool in Glassfish 2.x.x

We will create a PostgreSQL (8.4) Connection Pool on a GlassFish 2.1.1 [and GlassFish v3].

First of all, we need to download the latest jdbc driver:

http://jdbc.postgresql.org/download.html

Copy the downloaded archive postgresql-x.x.-xxx.jdbc4.jar in {glassfish}/lib directory and restart glassfish:

asadmin stop-domain domain1
asadmin start-domain domain1

Now we 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.DataSource'.
Select 'Database Vendor' : 'PostgreSQL'.
Select 'Next'.

At the next page go down at the additional Properties.
Find and edit the following properties :
Select 'Name' : 'User' and 'Value' : {db_user}
Select 'Name' : 'Password' and 'Value' : {db_user_pass}
Select 'Name' : 'DatabaseName' and 'Value' : {db_name}
Select 'Name' : 'ServerName' and 'Value' : {server_hostname}
If your dqatabase is not in the default port (5432) you might also need to edit the following attribute :
Select 'Name' : 'PortNumber' and 'Value' : {port_number}
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 just 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.

11 comments:

  1. thanks, very helpful

    ReplyDelete
  2. Simple way to do it in glassfish3.1

    http://avalontechnology.blogspot.com/2010/11/postgresql-connection-pool-in-glassfish.html

    ReplyDelete
  3. Problem With datasource creation in netbeans.Do you know how to solve it?

    ReplyDelete
  4. Can you elaborate on your problem? What is the error in the logs? Which step is failing?

    ReplyDelete
  5. help me please, problem is Can't find SerialContextProvider

    ReplyDelete
  6. Can you elaborate on your problem? Which step is failing? What is the code you are running?

    ReplyDelete
  7. Help me, please.. I have done all steps you tell, but when I ping, I obtain this messagge:

    An error has occurred Ping Connection Pool failed for postgres. Class name is wrong or classpath is not set for : org.postgresql.ds.PGSimpleDataSource Please check the server.log for more details.

    Thanks!

    ReplyDelete
  8. Please repeat all the steps from the begining and if it fails again try with another version of the postgres driver.

    ReplyDelete
  9. Please
    How to set autoCommit false into glassfish.
    I have add autoCommit false into pool property but is not work.
    My diver is postgresql-9.2-1003.jdbc4.jar


    ReplyDelete
  10. My friend BOBO,
    I am always glad to lend a hand. You are definitely in the right direction. Only a slight change is needed, as far as the configuration goes.
    I had a glance at the postgresql 9.2 documentation : http://jdbc.postgresql.org/documentation/92/ds-cpds.html
    and I found out that the pool property name is actually "defaultAutoCommit".
    I hope this solves your problem.
    I am worried though because the description goes like this : "Whether connections should have autocommit enabled or disabled when they are supplied to the caller. The default is `false`, to disable autocommit."
    IMHO I would presume that the autocommit is disabled by default and you need not disable it at all.
    Hence, I am supposing that the problem may lie elsewhere, like inside the application setting autocommit to true with java code like this :
    conn.setAutoCommit(true);
    Drop a word, if you need any more help.
    Cheers

    ReplyDelete