[ Team LiB ] Previous Section Next Section

Recipe 21.6 Using a DataSource from WebLogic in a JSP


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


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


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.*" %>

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

    Context env = null;

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


    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>

             //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>

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


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.

See Also

The JDBC specification: http://java.sun.com/products/jdbc/download.html; Chapter 2 on deploying servlets and JSPs on WebLogic; Recipe 21.1 on accessing a database from a servlet without a connection pool; Recipe 21.2 and Recipe 21.3 on using a DataSource on Tomcat; Recipe 21.4 and Recipe 21.5 on using DataSources with servlets on WebLogic; Recipe 21.7 and Recipe 21.8 on calling stored procedures from servlets and JSPs; Recipe 21.9 on converting a java.sql.ResultSet object to a javax.servlet.jsp.jstl.sql Result; Recipe 21.10 and Recipe 21.11 on using transactions in servlets and JSPs; Recipe 21.12 on finding out information about a ResultSet.

    [ Team LiB ] Previous Section Next Section