CODEWITHSUNDEEP
javaoracle-databasejsptomcatjdbc
Chanda
Chanda

Reputation: 13

Error connecting to oracle database in tomcat server using jsp

Tried everything but couldnt solve the problem. I use Windows 8 enterprise edition(if this has something to do with the problem)

I have oracle installed in d: and tomcat server on c:. I copied the jar file ojdbc6.jar from oracle's lib directory to tomcat's lib directory. Then i created a classpath in environments variables (system variable) with value as"C:\Program Files\Apache Software Foundation\Tomcat 8.0\lib\ojdbc6.jar".

my program is as follows in notepad(.jsp file):

<%@ page import="java.sql.*" %>
<html>
   <body>
       <%
           Connection conn;
           Statement st; ResultSet rs;
           new oracle.jdbc.OracleDriver();
           String dbURL="jdbc:odbc:oracle:thin:@localhost:1521:XE";
           String userId="system";
           String pwd="moon";
           conn=DriverManager.getConnection(dbURL,userId,pwd);
           st=conn.createStatement();
           rs= st.executeQuery("SELECT * FROM login"); 
           while(rs.next())
           {
               System.out.println(rs.getString(1)+""+rs.getString(2));
           }
       %>

   </body>
</html>

I checked all my services are running for oracle as well as tomcat server.``

EDIT

According to the Chanda's comments the error is:

HTTP Status 500 - An exception occurred processing JSP page /page2.jsp at line 14. Line 14 is conn=DriverManager.getConnection(dbURL,userId,pwd)

java.lang.ClassNotFoundException: org.apache.jsp.new_jsp The server encountered an internal error that prevented it from fulfilling this request.

Upvotes: 1

Views: 3932

Answers (3)

duffymo
duffymo

Reputation: 308763

I would not recommend that you do this. Scriptlet code and database calls do not belong in JSPs. It's a much longer discussion.

But the immediate problem is that your connection URL is incorrect:

String dbURL="jdbc:odbc:oracle:thin:@localhost:1521:XE";

should be

String dbURL="jdbc:oracle:thin:@localhost:1521:XE"

Tomcat will ignore all system environment variables. I'd recommend that you never have a CLASSPATH set that way.

The correct thing to do is to put the ojdbc6.jar in the Tomcat server /lib folder.

Add other 3rd party JARs in the WEB-INF/lib of your web app.

       Connection conn;
       Statement st; 
       ResultSet rs;
       String dbURL="jdbc:odbc:oracle:thin:@localhost:1521:XE";
       String userId="system";
       String pwd="moon";
       try {
           Class.forName("oracle.jdbc.OracleDriver");
           conn=DriverManager.getConnection(dbURL,userId,pwd);
           st=conn.createStatement();
           rs= st.executeQuery("SELECT * FROM login"); 
           while(rs.next()) {
               System.out.println(rs.getString(1)+" "+rs.getString(2));
           }
       } catch (Exception e) {
           e.printStackTrace();
       } finally {
           DatabaseUtils.close(rs);  // implement static methods to do this.
           DatabaseUtils.close(st);
           DatabaseUtils.close(conn);
       }

I would recommend something like this: 

public class DatabaseUtils {
    public static void close(Connection c) {
        try {
            if (c != null) {
                c.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    // I'll leave the others for you.
}

Upvotes: 1

Chanda
Chanda

Reputation: 13

Finally my problem got solved. Actually i couldnot get it previously. I found another version of oracle was installed on my system oracle 10.2.0. I think this was that which created problem.I removed both versions from my system and re-installed oracle 11g,and made some changes to my program and it worked. Here is my program with the changes:

<%@ page import="java.sql.*" %>
<html>
<body>
<%
Connection conn;
Statement st;
ResultSet rs;
new oracle.jdbc.OracleDriver();
String dbURL="jdbc:oracle:thin:@localhost:1521:XE";
String userId="system";
String pwd="moon";

conn=DriverManager.getConnection(dbURL,userId,pwd);
st=conn.createStatement();
rs= st.executeQuery("SELECT * FROM login"); 

while(rs.next())
{%>
<%= rs.getString(1) %>
<%= rs.getString(2) %>
<% }

%>

</body>
</html>

Upvotes: 0

Ernesto Campohermoso
Ernesto Campohermoso

Reputation: 7371

Improving duffymo answer:

  • The method forName doesn't accept a Class object.
  • Since you are using Java 7 you can use try with resources.

So the right way to solve this is doing the following:

   try {
       Class.forName("oracle.jdbc.OracleDriver");
   } catch (ClassNotFoundException ex) {
       // YOU MUST IMPROVE THE ERROR MANAGEMENT
       out.println("Error trying to load the OracleDriver, review the ojdbc6.jar!!");
       ex.printStackTrace();
   }

   String dbURL="jdbc:oracle:thin:@localhost:1521:XE";
   String userId="system";
   String pwd="moon";
   try (
     Connection conn = DriverManager.getConnection(dbURL,userId,pwd);
     Statement st=conn.createStatement();
   ){
       try {
           rs= st.executeQuery("SELECT * FROM login"); 
           while(rs.next()) {
               out.println(rs.getString(1)+" "+rs.getString(2)); //Maybe is prefereable print to HTTP response
           }
       } catch(SQLException sqlex) {
           out.println("Error executing query!!"+sqlex.getMessage());
           sqlex.printStackTrace();
       }
   } catch (SQLException e) {
       out.println("Error trying to connect to the database!!"+e.getMessage());
       e.printStackTrace();
   }

Recommendations:

  • Is not recommended call to the database from JSPs, you can search information about the MVC dessign pattern.
  • Also you can read about of some frameworks that help you to implement MVC like:
    • Java Server Faces
    • Spring MVC
    • Wicket

Upvotes: 0

Related Questions