Applications Software

How do I configure Weblogic Application server to connect to Postgres?

How do I connect to a Postgresql database from within an EJB using Weblogic server?

For some reason I spent many hours failing to get this to work before I finally cracked it. Here are the notes I made when I succeeded.

  1. The driver classes (postgresql.jar file) must be on the server CLASSPATH.
    For example, copy it to the WLHOME/ext directory, and edit the script WLHOME/config/mydomain/startWebLogic.cmd
    Append "./ext/postgresql.jar" to the CLASSPATH setting there.

  2. Start the WebLogic server for your domain.

  3. Create a JDBC Connection Pool which connects to the Postgres database, either by:

    1. ) Using the administrative console:
       - Configure a new JDBC Connection Pool
           - General tab:
       	- Name: Any name you like for your pool (eg MyPoolName)
       	- URL: jdbc:postgresql://hostname:port/database
       	  eg jdbc:postgresql://
       	- Driver Classname: org.postgresql.Driver
       	- Properties: user=ed password=anything
       	- ACL Name: (blank)
       	- Password: The real password to connect to the database, this
       	            will be kept encrypted and substituted for the value 
       	            you put in "Properties".
              - Press "Create" 
          - Go to the "Targets" tab
              - Select "myserver" from the "Abailable" list and move it
                to "Chosen".  Apply.
        - Create a JDBC Data Source which references the Connection Pool above
          - Configure a new JDBC Data Source - Configuration Tab
            - Name: Any name you like (eg MyDataSource)
            - JNDI Name:  Make this the same as "Name" above (MyDataSource).
            - Pool Name: The name of the ConnectionPool created above (MyPoolName)
            - Press "Create" 
          - Go to the "Targets" tab
            - Select "myserver" from the "Available" list and move it
              to "Chosen".  Apply.

    2. ) or by using the command line as follows:
        java -cp {path to weblogic.jar} weblogic.Admin -url localhost:7001 \
             -username system -password password \
             CREATE_POOL .... TBD ....

    3. ) or, with the server stopped, edit the WLHOME/config/mydomain/config.xml file:
        - Add the following to create a connection pool:
          <JDBCConnectionPool DriverName="org.postgresql.Driver"
        - Add the following to create a Data Source referencing that pool:
          <JDBCDataSource JNDIName="MyDataSource" 

  4. Define a reference to your Data Source in the EJB deployment descriptor files:
    In ejb-jar.xml within the section add:
    	<res-ref-name>jdbc/MyPoolName</res-ref-name> <!-- This is the name chosen for the Connection Pool with "jdbc/" prepended -->

    In weblogic-ejb-jar.xml within the <weblogic-enterprise-bean> section add:
            <res-ref-name>jdbc/MyPoolName</res-ref-name> <!-- This is the name chosen for the Connection Pool with "jdbc/" prepended -->
    	<jndi-name>MyDataSource</jndi-name> <!-- this is the name you chose for the DataSource -->

  5. In your bean or a suitable utility class, write a "getConnection()" method which returns a Connection object which you can then use in the usual way. This will do a JNDI lookup to find a javax.sql.DataSource object configured in your server and obtain a Connection from that. The name used to obtain your DataSource is, confusingly, not the JNDI name but the set above, beneath the java:comp/env hierarchy as follows:
    private Connection getConnection() throws NamingException
        InitialContext ic = new InitialContext();
        DataSource ds = (DataSource)ic.lookup("java:comp/env/jdbc/MyPoolName");
        return ds.getConnection();

  6. Here is an example of how to use your Connection "in the usual way"...
    Connection conn = null;
    PreparedStatement st = null;
        conn = getConnection();
        // Prepare your SQL statement, substitute "somevalue" for the first "?" parameter.
        st = conn.prepareStatement("SELECT COL1, COL2, COL3 FROM TABLENAME WHERE COL1 = ?");
        st.setString(1, somevalue);
        // Execute the SQL and read the rows returned
        ResultSet rs = st.executeQuery();
        while (
        {	// Read and process each row of the ResultSet
    	String col1 = rs.getString(1);
    	String col2 = rs.getString(2);
    	String col3 = rs.getString(3);
    	// etc...
    catch (SQLException ex)
        System.out.println("SQL exception occurred" +ex);
    	if (st != null)
        catch (SQLException ex)
    	if (conn != null)
        catch (SQLException ex)
    Ed, Thu May 16 15:46:03 2002

    Ed, Thu May 16 15:46:03 2002, Thu Jan 22 17:02:28 2004

    Previous : How do I change the delay before voicemail kicks in?
    Next : Some ideas for connecting the STB to 2 TV sets