Using a DataSource from WebLogic in a JSP

By: Bruce W. Perry in JSP Tutorials on 2008-11-24

Example below 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.

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.

Previous
<< Deploying an Individual JSP on Tomcat

Next
Getting HTTP Request Headers in a JSP >>