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://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)
       {
       }
   }
   

   
   

Suba and Sandhya Batch (Batch2 for PS)

Core Java SE APIs , JSP, Servlets, JSF, Struts, Spring, Hibernate, Web Services, JMS, EJB3.0, Tomcat, GlassFish, Eclipse, NetBeans, Mysql.

Siva, Venky Web Services Batch

• JAX-RPC with Eclipse, Axis2 and Tomcat 

• JAX-WS with Netbeans, GlassFish 

• JAX-RS (RESTful webservices) with Eclipse+ Jercy+Tomcat and Netbeans + GlassFish

Why Every Java Developer Should Also Know and Use Oracle PLSQL

http://library.dzone.com/assets/request/express/48595/07728590706139405c0af