Index Previous Next

Applications Software

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

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://smirnoff.uk.ingenotech.com:5432/homepages
       	- 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"
                                 MaxCapacity="10" 
                                 Name="MyPoolName"
                                 Password="{3DES}yIsebsUSRdE="
                                 Properties="user=ed;password=secret" 
                                 Targets="myserver"
                                 URL="jdbc:postgresql://hostname:port/database"/>
      
        - Add the following to create a Data Source referencing that pool:
          <JDBCDataSource JNDIName="MyDataSource" 
                             Name="MyDataSource" 
                             PoolName="MyPoolName" 
                             Targets="myserver"/>
      

   
   
4. Define a reference to your Data Source in the EJB deployment descriptor files:
   In ejb-jar.xml within the section add:

         <resource-ref>
   	<res-ref-name>jdbc/MyPoolName</res-ref-name> <!-- This is the name chosen for the Connection Pool with "jdbc/" prepended -->
   	<res-type>javax.sql.DataSource</res-type>
           <res-auth>Container</res-auth>
         </resource-ref>
   

   
   In weblogic-ejb-jar.xml within the <weblogic-enterprise-bean> section add:

       <reference-descriptor>
         <resource-description>
           <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 -->
         </resource-description>
       </reference-descriptor>
   

   
   
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;
   try
   {
       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 (rs.next())
       {	// 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);
   }
   finally
   {
       try
       {
   	if (st != null)
   	    st.close();
       }
       catch (SQLException ex)
       {
       }
   
       try
       {
   	if (conn != null)
   	    conn.close();
       }
       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

   ---

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

   ---