Friday, February 26, 2010

Creating a Connection Pool to PostgreSQL on Glassfish V3

I recently created a new connection pool to PostgreSQL on Glassfish and I would like to share the steps I followed with you. Obviously, you need PostgreSQL and Glassfish installed on your machine and a database already created in PostgreSQL. If you didn't configure your new PostgreSQL installation yet, follow the steps I described at this previous post and come back here to continue with the connection pool. The necessary steps are:
  1. We need the PostgreSQL JDBC Driver, since Glassfish and its deployed applications are writen in Java. Drivers are available for download at http://jdbc.postgresql.org. For this experiment choose the JDBC4 driver.
  2. Download the driver file postgresql-<version>.jdbc4.jar and copy it to the diretory [glassfish_home]/glassfish/domains/domain1/lib/.
  3. Restart Glassfish in order to make it load the new database driver. I thought that adopting an OSGI architecture Glassfish would never need restarts again, but I was wrong. At least, the restarting process is faster than V2.
  4. Enter in the administration console and go to Resources/JDBC/Connection Pools.
  5. Create a new connection pool with the name [database_name]Pool, select the resource type javax.sql.ConnectionPoolDataSource, select the database vendor PostgreSQL and click next.
  6. Select the datasource classname org.postgresql.ds.PGConnectionPoolDataSource and inform the following additional properties:
    DatabaseName=[database-name]
    Password=******* ;)
    PortNumber=5432 (this is the default port but make sure that you are using the correct one)
    ServerName=[server-name|ip]
    User=<database-username>
  7. Click Finish to save the new connection pool.
  8. Go to the list of connection pools again and select the new one that you just created.
  9. Click on Ping to check if the connection was correctly configured. The message "Ping Succeeded" means that the connection is working fine.
  10. In order to be able to use this connection pool in JEE applications, we have to create a JNDI name for it. Go to Resources/JDBC/JDBC Resources.
  11. Click on New and set the JNDI Name jdbc/[database_name], select the connection pool created above and click Ok to finish. This JNDI name will be used by applications to access the PostgreSQL database.
These instructions may work with Glassfish V2 as well, since its database configuration is quite similar.

I'm being very specific in terms of chosen technologies, but if you have a slightly different configuration and these steps are not working yet, please comment your issues below, describing also your current configuration/context. Maybe, we can figure it out.
Tags: , ,

23 comments:

  1. Hi, I have installed GlassFish v3 on Vista and created Pooling DataSource to my PG/SQL database which is located on Unix Ubuntu in Virtual Machine. It does ping OK. I also created JNDI under Resources/JDBC/JDBC Resources named “jdbc/MyTestDB” mapping it with the Pooling DataSource I have previously created.
    Now, I have found and adopted in Eclipse (on Vista) a java application code to access that postgres database with the DataSource connection made available through the glassfish server but unfortunately I cannot and get the following error:

    javax.naming.NoInitialContextException: Need to specify class name in environment or system property, or as an applet parameter, or in an application resource file: java.naming.factory.initial

    the code that attempts to connect through the DataSource is as follows:

    /** Uses JNDI and Datasource (preferred style). */
    static Connection getJNDIConnection(){

    String DATASOURCE_CONTEXT = "java:comp/env/jdbc/MyTestDB";
    Connection conn = null;
    try {
    Context initialContext = new InitialContext();
    //initialContext.
    if ( initialContext == null){
    log("JNDI problem. Cannot get InitialContext.");
    }
    DataSource ds = (DataSource)initialContext.lookup(DATASOURCE_CONTEXT);

    if (ds != null) {
    conn = ds.getConnection();
    System.out.println("Connected to db using DataSource");
    }
    else {
    log("Failed to lookup datasource.");
    }
    }
    catch ( NamingException ex ) {
    log("Cannot get connection: " + ex);
    }
    catch(SQLException ex){
    log("Cannot get connection: " + ex);
    }
    return conn;
    }

    As an aside note I can say that I am able to connect to my postgres db using DriverManager:
    private Connection dbConnection;
    try {
    Class.forName("org.postgresql.Driver").newInstance();
    dbConnection = DriverManager.getConnection("jdbc:postgresql://192.168.56.101:5432/testDB", "user","password");
    }
    catch(Exception x){
    System.out.println( "Unable to load the driver class!" + x);
    }

    I am absolutely new to JEE and Glassfish. Maybe I should configure Glassfish server somehow further and do something extra to make that JNDI name datasource accessible to applications that I run on Vista in Eclipse.
    Please, help.
    ReplyDelete
  2. Hello Andrew,

    If the connection is working in other ways we have good news. It means that there is minor issues to solve. One of these minor issues might be solved with the code below:

    Context ctx = new InitialContext();
    DataSource ds = (DataSource) ctx.lookup("jdbc/MyTestDB");
    Connection conn = ds.getConnection();

    Basically, instead of using "java:comp/env/jdbc/MyTestDB", use just "jdbc/MyTestDB". Let me know if it is enough to solve the problem.
    ReplyDelete
  3. Hildeberto, many thanks for your quick response. Unfortunately changing the initialContext lookup value did not do. Any other ideas what can be wrong? … :|
    ReplyDelete
  4. Is the code running in the same application server where the JNDI was configured or is it deployed in another server? If not, you will need additional configurations that you can find here: http://www.developer.com/java/ejb/article.php/10931_2215571_4/Locating-Resources-Using-JNDI-Java-Naming-and-Directory-Interface.htm.

    When you performed the task 11 of the post, have you defined the JNDI name with the prefix "jdbc/"? If not, do it.
    ReplyDelete
  5. Hi Hildeberto, thank again for the quick response.
    As to the JNDI name, yes, it is prefixed with jdbc/: “jdbc/MyTestDB”.
    As regards the locations of the server and the client code:
    The client resides on my D drive D:\Java\workspace\jdbc\bin\DBConnection.class.
    (it originally had nothing to do with application server as it connected to PG/SQL directly through DriverManager, only after I found out that using DataSource is the preferred style did I installed glassfish and rewrote the code to connect do the postgres db through the application server's datasource).
    The server is installed on my C drive in the default install dir: C:\glassfishv3
    Both the program and the application server are running on the same system (Windows Vista). I am not sure whether that means that the program IS running on the SAME APPLICATION SERVER.
    I must admit that I did not make any special connections between the program that I wrote and the glassfish server so I’m not sure whether the program is able to find the JNDI resource that I created in the glassfish.
    Please, do say whether my understanding of the above is correct or not.
    ReplyDelete
  6. Hello Andrew, it seems that you developed a standalone application (it runs using the java command: java DBConnection). Is it right? Well, in this case you cannot access this kind of server resource through a standalone application. The application server only provides resources for those applications that are deployed on it. In fact, the application server should execute the application.

    It is possible to deploy your code on Glassfish, but you have to do a lot of additional things to be able to see the output of your code, like develop a simple web application.

    Therefore, in your specific case, you can access the PostgreSQL server through a conventional database connection only, using the DriveManager. The same way you did in your last example (first comment).
    ReplyDelete
  7. Thanks Hildeberto, that makes the picture much clearer,
    Best Regards
    ReplyDelete
  8. Hi, thank you for nice manual. :)
    ReplyDelete
  9. Hi, thanks for this manual!
    I just tried it on Glassfish V2 and
    it just worked.
    ReplyDelete
  10. Thank you very much for you help, it's was easy
    ReplyDelete
  11. Nice tutorial, thanks a lot. Worked flawlessly! I've worked with Glassfish a long time ago in school and your steps were exactly what I needed. Keep up the good work =)
    ReplyDelete
  12. Hey Hildeberto, What IDE are you using for connection pooling?
    ReplyDelete
  13. Hey Hildeberto, What IDE are you using for connection pooling?
    ReplyDelete
  14. Hi,

    well, I'm using Netbeans because of its usability and integration with Glassfish, but the connection pooling configuration is independent of the IDE. I'm explaining how I did it in Glassfish. So, it will work for Eclipse or any other IDE as well.
    ReplyDelete
  15. I succeeded to get my datasource deployed and ready in no time. Thanks for sharing your findings.
    ReplyDelete
  16. Using Glassfish V3 with Eclipse Helios if you have problems (ClassNotFoundException) while adding the JDBC Driver use [glassfish_home]/glassfish/lib/ as directory to copy your .jar file.

    By the way: Thanks for sharing! :)
    ReplyDelete
  17. Awesome post dude!!!
    Saved my work day!!!
    ReplyDelete
  18. Hi,
    great post, thank you.
    But there's a spelling mistake in the package name which nearly drove me nuts.
    It's of course "postgresql" not "portgresql".
    ReplyDelete
  19. Wow! I'm sorry for that. Fixed! Thank you for the feedback.
    ReplyDelete
  20. Saved me a lot of time.
    Especially point 3, I thought too that I got rid of restarting , I lost a time in it. Thanks for the post
    ReplyDelete
  21. Very interesting thanks. Just on remark : no JEE --> Java EE, best regards
    ReplyDelete
  22. Do not forget adding an additional property named "URL" to the pool.
    IDE's address mapping may crash.


    jdbc:postgresql://localhost:5432/[database-name]
    ReplyDelete
  23. This is not actually necessary to make the connection pool work :) Indeed, the URL is useful to create connections from the IDE. Thanks for adding this tip.
    ReplyDelete

Subscribe to: Post Comments (Atom)