Recipe 21.6 Using a DataSource from WebLogic in a JSP

Problem

You want to use the javax.sql.DataSource that you set up on WebLogic in a JSP.

Solution

Use JSP scriptlets to access the DataSource with a JNDI lookup, then use the JDBC API in the scriptlets to access the database.

Discussion

The JSP in Example 21-6 transplants code from a servlet inside of HTML template text. The JSP uses scriptlets, which contain Java code within "<% %>" characters.

JSTL SQL tags are preferable to scriptlets in a JSP; however, the JSTL implementation I use for this book's examples cannot access a DataSource from WebLogic's JNDI implementation. See Recipe 23.6 for an example that uses the JSTL SQL tags with a Tomcat DataSource.

Example 21-6 imports the necessary classes at the top of the code using the page directive and its import attribute. Otherwise, this JSP accomplishes everything that the servlet of the prior recipe does, including the display of nearly identical output in the web browser (see Figure 21-1 in Recipe 21.1).

Example 21-6. Using a JSP scriptlet to access a WebLogic DataSource

<%@ page import="java.util.Hashtable,java.sql.*,javax.naming.*,javax.sql.*" %>

<html>
<head><title>Database Query in WebLogic</title></head>
<body>
<h2>Querying a database with a JSP in WebLogic</h2>

<%
    Context env = null;

    DataSource pool = null;
         
    Hashtable ht = new Hashtable( );

    ht.put(Context.INITIAL_CONTEXT_FACTORY,
      "weblogic.jndi.WLInitialContextFactory");

    ht.put(Context.PROVIDER_URL,"t3://localhost:7001");
    
    env = new InitialContext(ht);

    //Lookup this DataSouce at the top level of the WebLogic JNDI tree
    pool = (DataSource) env.lookup ("oracle-8i-athletes"); 

    String sql = "select * from athlete";

    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    ResultSetMetaData rsm = null; %>
        
 <table border='1'><tr>

  <% 
     try{   
             //get a java.sql.Connection from the pool
             conn = pool.getConnection( );

             stmt = conn.createStatement( );//create a java.sql.Statement
      
             //execute a SQL statement,generating  a ResultSet
             rs = stmt.executeQuery(sql); 

             rsm = rs.getMetaData( );

             int colCount =  rsm.getColumnCount( );
            
             //print column names
             for (int i = 1; i <=colCount; ++i) { %>
                
               <th><%=rsm.getColumnName(i)%> </th>

          <%  } %>
            
           </tr>
          
          <% while( rs.next( )){ %>
                
                <tr>
                
            <%     for (int i = 1;  i <=colCount; ++i) { %>
                <td>    <%= rs.getString(i) %> </td>
            <%}//for %>  
                </tr>
          <%} //while
           
  } catch (Exception e) {
            
           throw new JspException(e.getMessage( ));
            
  } finally {
            
      try{
                
          stmt.close( );
          conn.close( );
                
      } catch (SQLException sqle){ }
            
  }                 %>

</body>
</html>

After making sure that you have properly configured the connection pool and DataSource in the WebLogic console, view this JSP's output by copying it to WebLogic's default web application, then request a URL in your browser that looks like this one: http://localhost:7001/sqlWeblogic.jsp.

Recipe 21.6 Using a DataSource from WebLogic in a JSP

Problem

Solution

Discussion

Example 21-6. Using a JSP scriptlet to access a WebLogic DataSource

See Also