Friday, September 11, 2009

Oracle: Servlets with JDBC call

I took the code from http://www.deakin.edu.au/its/dba/oracle-doco/9.0.4.1/9.0.4_doc_library/web.904/b10322/servprim.htm
But as the used JNDI for database connectivity thus I changed the part below:
public void init() throws ServletException {                     // 3
    try {
      InitialContext ic = new InitialContext();                    // 4
      ds = (DataSource) ic.lookup("jdbc/OracleDS");                // 5
      conn = ds.getConnection();                                   // 6
    }
    catch (SQLException se) {                                      // 7
      throw new ServletException(se);
    }
    catch (NamingException ne) {                                   // 8
      throw new ServletException(ne);
    }
  }




Replaced with:




public void init() throws ServletException {                     // 3

    try {


      DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());



                conn = DriverManager.getConnection

                ("jdbc:oracle:thin:@151.284.29.148:1521:MySID", "scott", "tiger");              // 5


         }


    catch (SQLException se) {                                      // 7


      throw new ServletException(se);


    }



  }





Thus the code became:





import javax.servlet.*;

import javax.servlet.http.*;                                       // 1


import java.sql.*;


import java.io.*;



public class GetEmpInfo extends HttpServlet {


  Connection conn;


public void init() throws ServletException {                     // 3

    try {


      DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());



                conn = DriverManager.getConnection

                ("jdbc:oracle:thin:@151.284.29.148:1521:MySID", "scott", "tiger");              // 5


         }


    catch (SQLException se) {                                      // 7


      throw new ServletException(se);


    }



  }


  public void doGet (HttpServletRequest req, HttpServletResponse resp)

                     throws ServletException, IOException {



    String query = "select COF_NAME,SUP_ID from COFFEES ";


    resp.setContentType("text/html");


    PrintWriter out = resp.getWriter();

    out.println("<html>");


    out.println("<head><title>GetEmpInfo</title></head>");


    out.println("<body>");



    try {

      Statement stmt = conn.createStatement();                     //11


      ResultSet rs = stmt.executeQuery(query);                     //12



        out.println("<table border=1 width=50%>");

          out.println("<tr><th width=75%>COF_NAME</th><th width=25%>SUP_ID</th></tr>");



        int count=0;

        while (rs.next()) {                                        //13


         count++;


         out.println("<tr><td>" + rs.getString(1) + "</td><td>" +rs.getInt(2) +


                    "</td></tr>");



        }

         out.println("</table>");


         out.println("<h3>" + count + " rows retrieved</h3>");



      rs.close();                                                  //14

      stmt.close();


    }


    catch (SQLException se) {                                      //15


      se.printStackTrace(out);


    }



    out.println("</body></html>");

  }



  public void destroy() {                                          //16

    try {


      conn.close();


    }


    catch (SQLException se) {                                      //15


      se.printStackTrace();


    }


  }


}



Now if the class-path etc is proper the code will compile and then after putting the proper web.xml entries you might try the code..but  you might receive the error below:





Sep 11, 2009 2:01:25 AM org.apache.catalina.core.StandardWrapperValve invoke

SEVERE: Allocate exception for servlet GetEmpInfo


javax.naming.NameNotFoundException: Name jdbc is not bound in this Context



[Check the mentioned log:$TOMCAT_HOME/logs/localhost.2009-09-11.log]





It is because of the fact that the oracle’s jdbc library is not within the context periphery of TOMCAT.Thus you need to put the particular


library within the TOMCAT boundary and set path accordingly.





So what I did is


Copied “/home/oracle/oracle/OracleAS/jdbc/lib/ojdbc14.jar” to

“/home/oracle/oracle/OracleAS/SERVLET_LIB/apache-tomcat-5.5.28/webapps/servlets-examples/WEB-INF/lib/ojdbc14.jar”



and set the classpath as


export CLASSPATH=$CLASSPATH:.:/home/oracle/oracle/OracleAS/SERVLET_LIB/apache-tomcat-5.5.28/webapps/servlets-examples/WEB-INF/lib/ojdbc14.jar


And WoW!!!! It started working ;-)

No comments:

Post a Comment