6 Replies Latest reply on Jul 6, 2007 7:38 PM by 843838

    How to connect to Oracle from jsp page in eclipse

    843838
      Hello All,
      I am having a webapplication that uses jsp as front end and oracle as the backend. I am using Eclipse as my development platform with Tomcat as the App Server. My Jsp works fine. I need to connect to the Oracle database installed locally in my system. What are the steps that need to be done? Should I install any .jar files like the ojdbc14.jar or classes12.jar. They are in my oracle library. Should they also be present in my eclipse directory.

      Any help would be appreciated.
        • 1. Re: How to connect to Oracle from jsp page in eclipse
          843838
          Please read Tomcat documentation:
          http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html
          • 2. Re: How to connect to Oracle from jsp page in eclipse
            843838
            hello peter,
            Thank you for getting back. But the link doesnt mention anything about the Eclipse IDE and how we can connect to the database in eclipse. I am using Eclipse IDE and need info on where to keep jar files and stuff.
            Please help me out.
            • 3. Re: How to connect to Oracle from jsp page in eclipse
              843838
              Just put the Oracle JAR's in the classpath (build path) of the project and write JDBC/DAO code accordingly.
              • 4. Re: How to connect to Oracle from jsp page in eclipse
                843838
                If you want to connect from jsp, the only thing you need is a connection object.

                I will provide you the steps

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

                --->the above statement is common no changes rquired.



                2) Connection conn = DriverManager.getConnection
                ("jdbc:oracle:thin:@hostname:1526:orcl", "scott", "tiger");
                // @machineName:port:SID, userid, password
                --->type "select * from global_name" in oracle you will get a string as an output
                --->copy the first word before the first dot
                ---> in the above statement change the "orcl" witht he word you have copied.
                -->change the username scott with your oracle username and tiger with your oracle password

                Statement stmt = conn.createStatement();
                --> the above is to create the statement object which will be usefull to
                -->send sql query to the oracle database
                ResultSet rset = stmt.executeQuery("select * from emp");
                -->the above statement executes the query and the whole table is
                -->stored inthe ResultSet object

                while (rset.next())
                System.out.println (rset.getString(1)); // Print col 1
                --->the above statement is to loop through the resultset object and get the first column data and print it in the console.

                if you want to get other columns you can use rset.getString(2);rset.getString(3) and soo on.

                stmt.close();

                -->the above is used to close the statement.

                Also make sure to pu the "classes111.zip" it will be located in the classpath "D:\oracle\ora81\jdbc\lib"
                you can do it in the system environment vaiable

                or you can put the zip file in "....\Tomcat 5.0\common\lib folder. It will be automatically picked up by the eclipse IDE

                hope it will be clear enough

                Message was edited by:
                Anil_kumar
                • 5. Re: How to connect to Oracle from jsp page in eclipse
                  843838
                  hello peter,
                  Thank you for getting back. But the link
                  doesnt mention anything about the Eclipse IDE and
                  how we can connect to the database in eclipse. I am
                  using Eclipse IDE and need info on where to keep jar
                  files and stuff.
                  lease help me out.
                  Everything is there. This documentation is IDE independent. You should: (quote form tutorial):

                  1) Driver:
                  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. Copy the Oracle JDBC jar to $CATALINA_HOME/lib, where CATALINA_HOME is a path in you're tomcat installation.

                  2) server.xml configuration, add:
                  <Resource name="jdbc/myoracle" auth="Container"
                  type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"
                  url="jdbc:oracle:thin:@127.0.0.1:1521:mysid"
                  username="scott" password="tiger" maxActive="20" maxIdle="10"
                  maxWait="-1"/>
                  where you need to change:
                  - url - is url in "oracle style", change it
                  - username
                  - password

                  All possible options are listed here: http://jakarta.apache.org/commons/dbcp/configuration.html

                  3) Code example:
                  Context initContext = new InitialContext();
                  Context envContext = (Context)initContext.lookup("java:/comp/env");
                  DataSource ds = (DataSource)envContext.lookup("jdbc/myoracle");
                  Connection con = ds.getConnection();

                  String query = "SELECT * FROM students WHERE UserLogin=?";
                  PreparedStatement prepStmt = con.prepareStatement(query);
                  prepStmt.setString(1, userName);
                  ResultSet rs = prepStmt.executeQuery();

                  rs.close();
                  prepStmt.close();
                  con.close();
                  • 6. Re: How to connect to Oracle from jsp page in eclipse
                    843838
                    Hello Peter,
                    Thank you for your response. I got it working now, but dint have to change anything in the server.xml or web.xml
                    I just had to include ojdbc14.jar file in my project's java build path in eclipse. Thats it. It worked.

                    Thank you