Connection Polling in Oracle 10g Using NetBeans ID

[Satyajeet Kadam]

I am facing the problem while connecting to oracle10g using DataSource approach.I am using classes12.jar in my classpath. I am able to connect to database if i harcode the values in JSP.

I am getting error as follows

    

message 
description The server encountered an internal error () that prevented it from fulfilling this request.

exception 
org.apache.jasper.JasperException: An exception occurred processing JSP page /index.jsp at line 35

32:            System.out.println("envContext="+envContext);
33:            DataSource ds = (DataSource)envContext.lookup("jdbc/myoracle");
34:            System.out.println("datasource="+ds);
35:            Connection con=ds.getConnection();
36:            Statement stmt=con.createStatement();
37:            ResultSet rs=stmt.executeQuery("select * from emp");
38: 


Stacktrace:
	org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:505)
	org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:398)
	org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342)
	org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:390)


root cause 

javax.servlet.ServletException: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot load JDBC driver class 'oracle.jdbc.OracleDriver'
	org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:852)
	org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:781)
	org.apache.jsp.index_jsp._jspService(index_jsp.java:121)
	org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
	org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342)
	org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:390)


root cause 

org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot load JDBC driver class 'oracle.jdbc.OracleDriver'
	org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1136)
	org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
	org.apache.jsp.index_jsp._jspService(index_jsp.java:84)
	org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
	org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342)
	org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:390)


root cause 

java.lang.ClassNotFoundException: oracle.jdbc.OracleDriver
	java.net.URLClassLoader$1.run(URLClassLoader.java:202)
	java.security.AccessController.doPrivileged(Native Method)
	java.net.URLClassLoader.findClass(URLClassLoader.java:190)
	java.lang.ClassLoader.loadClass(ClassLoader.java:307)
	java.lang.ClassLoader.loadClass(ClassLoader.java:248)
	java.lang.Class.forName0(Native Method)
	java.lang.Class.forName(Class.java:169)
	org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1130)
	org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
	org.apache.jsp.index_jsp._jspService(index_jsp.java:84)
	org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
	org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342)
	org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:390)


note The full stack trace of the root cause is available in the Apache Tomcat/6.0.18 logs.

Source code

Index.jsp

I am able to get connection and retrive the results properly if i hadcode the values.I have commented this code.

  
<%-- 
    Document   : index
    Created on : Jul 23, 2010, 6:07:54 AM
    Author     : satyajeet
--%>

<%@page
contentType="text/html"
pageEncoding="UTF-8"
import="javax.sql.*,java.sql.*,javax.naming.*"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">

<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <h1>Hello World!</h1>   
        <%



      //    String url="jdbc:oracle:thin:@115.242.13.246:1521:orcl";
        //  Class.forName("oracle.jdbc.driver.OracleDriver");// driver
          //Connection con=DriverManager.getConnection(url,"system","sakshi");//database url
          //Statement stmt=con.createStatement();
           Context initContext = new InitialContext();
           System.out.println("initContext="+initContext);
           Context envContext  = (Context)initContext.lookup("java:/comp/env");
           System.out.println("envContext="+envContext);
           DataSource ds = (DataSource)envContext.lookup("jdbc/myoracle");
           System.out.println("datasource="+ds);
           Connection con=ds.getConnection();
           Statement stmt=con.createStatement();
           ResultSet rs=stmt.executeQuery("select * from emp");

            if(rs.next())
             {%>
                   <table border="1"  >
                      <thead>
                       <tr>
                            <th>Employee name</th>
                                 <tr><%=rs.getString(1)%></tr>
                            <th>Salary</th>
                                 <tr><%=rs.getString(2)%></tr>
                            <th>Manager</th>
                                  <tr><%=rs.getString(3)%></tr>
                       </tr>
                     </thead>
                   </table>

             <%}
            %>
    </body>
</html>

Web.xml

 

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
    <session-config>
        <session-timeout>
            30
        </session-timeout>
    </session-config>
    <welcome-file-list>
        <welcome-file>index.jsp</welcome-file>
        </welcome-file-list>


            <resource-ref>
               <description>Oracle Datasource example</description>
               <res-ref-name>jdbc/myoracle</res-ref-name>
               <res-type>javax.sql.DataSource</res-type>
               <res-auth>Container</res-auth>
            </resource-ref>

    </web-app>

context.xml

<?xml version="1.0" encoding="UTF-8"?>
<Context antiJARLocking="true" path="/DataSourcing">
  <Resource auth="Container"
  driverClassName="oracle.jdbc.OracleDriver"
  factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
  maxActive="20" maxIdle="10" maxWait="-1"
  name="jdbc/myoracle" password="sakshi"
  type="javax.sql.DataSource"
  url="jdbc:oracle:thin:@115.242.13.246:1521:orcl"
  username="System"/>
</Context>

 

Please help me

[Satyajeet Kadam]

PLease let me know if a anybody knows the solution

[Ireneusz Kordal]

Hi,

your code uses this driver:
// Class.forName("oracle.jdbc.driver.OracleDriver");// driver

But Tomcat tries to load this driver:
java.lang.ClassNotFoundException: oracle.jdbc.OracleDriver

There is a subtle difference

Either install proper driver file somewhere in the classpath (the best place is $CATALINA/lib) , or change the driver name in context.xml.

As described here:
http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html

For Oracle 9i onwards you should use oracle.jdbc.OracleDriver rather than oracle.jdbc.driver.OracleDriver as Oracle have stated that oracle.jdbc.driver.OracleDriver is deprecated and support for this driver class will be discontinued in the next major release.