How to connect to Oracle from jsp page in eclipse
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.
[509 byte] By [
Sudipthaa] at [2007-11-27 9:49:34]
# 4
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
> 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();
