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:50:05]
# 1
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.
hope it will be clear enough
Message was edited by:
Anil_kumar
# 2
[nobr]you need to add ojdbc14.jar to /WEB-INF/lib/ AND TOMCAT_INSTALL_DIR/common/lib/ directories:
here a jsp oracle sample:
<!- Sample basic Jsp/Oracle ,http://www.exzilla.net/, Jan3,2000 -->
<%@ page import="java.sql.*" %>
<HTML>
<HEAD><TITLE>Simple JSP/Oracle Query Example</TITLE></HEAD>
<BODY BGCOLOR="#FFFFFF">
<CENTER>
<B>Employees</B>
<BR><BR>
<%
Connection conn = null;
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:fuju",
"scott",
"tiger");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM scott.emp");
//Print start of table and column headers
out.println("<TABLE CELLSPACING=\"0\" CELLPADDING=\"3\" BORDER=\"1\">");
out.println("<TR><TH>ID</TH><TH>NAME</TH><TH>SURNAME</TH>");
out.println(" <TH>SALARY</TH><TH>STARTDATE</TH></TR>");
//Loop through results of query.
while(rs.next())
{
out.println("<TR>");
out.println(" <TD>" + rs.getString("EMPNO") + "</TD>");
out.println(" <TD>" + rs.getString("ENAME") + "</TD>");
out.println(" <TD>" + rs.getString("JOB") + "</TD>");
out.println(" <TD>" + rs.getInt("SAL") + "</TD>");
out.println(" <TD>" + rs.getString("HIREDATE") + "</TD>");
out.println("</TR>");
}
out.println("</TABLE>");
}
catch(SQLException e)
{
out.println("SQLException: " + e.getMessage() + "<BR>");
while((e = e.getNextException()) != null)
out.println(e.getMessage() + "<BR>");
}
catch(ClassNotFoundException e)
{
out.println("ClassNotFoundException: " + e.getMessage() + "<BR>");
}
finally
{
//Clean up resources, close the connection.
if(conn != null)
{
try
{
conn.close();
}
catch (Exception ignored) {}
}
}
%>
</CENTER>
</BODY>
</HTML>
[/nobr]
