Java Servlet Database Connectivity with Oracle Express Edition

Java Servlet Database Connectivity with Oracle Express Edition

Prerequisites to Execute Database Connectivity:

1)      Download Jar and save to lib folder of Apache  (ojdbc14-1.0.jar Required): Download Here

2)      Update Classpath to following (Path in Bold are new):

C:\Program Files\Apache Software Foundation\Tomcat 7.0\lib\servlet-api.jar;C:\Program Files\Apache Software Foundation\Tomcat 7.0\lib\jsp-api.jar;C:\Program Files\Apache Software Foundation\Tomcat 7.0\lib\ojdbc14-1.0.jar;.

3)      Download and Install Oracle Express Edition.

First Create Your Database and Tables

Table Name: student

FieldName

Type

sid number(10) Primary Key
sname varchar2(20)

 

Query for Table:

CREATE TABLE  "STUDENT"
(
   "SID" NUMBER(4,0),
   "SNAME" VARCHAR2(20),
   CONSTRAINT "STUDENT_PK" PRIMARY KEY ("SID") ENABLE
)

Hello JDBC using Servlet Example

FilePath : C:\Program Files\Apache Software Foundation\Tomcat 7.0\webapps\example02\WEB-INF\classes

FileName: ex02.java

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class ex02 extends HttpServlet 
{

    protected void doGet(HttpServletRequest req,HttpServletResponse res)throws ServletException,IOException
    {

                PrintWriter pw=res.getWriter();
                res.setContentType("text/html");       
                String tb="student";
                pw.println("Initializing Connection....");

        try
        {

             Class.forName("oracle.jdbc.driver.OracleDriver");
             Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","adarsh","patel");
             Statement st=con.createStatement();
             System.out.println("connection established successfully...!!");   
             ResultSet rs=st.executeQuery("Select * from "+tb);
             pw.println("<table border=1>");

              while(rs.next())
             {
                   pw.println("<tr><td>"+rs.getInt(1)+"</td><td>"+rs.getString(2)+"</td>"+"</tr>");
             }
             pw.println("</table>");
             pw.close();
        }
        catch (Exception e)
        {
               pw.println(e.getMessage());
        }
    }
}

 Hello JDBC using Servlet Example Explanation:

Line : Class.forName(“oracle.jdbc.driver.OracleDriver”);

Explanation : This Line if toload Driver for Oracle Database Connectivity with Java Servlet.

 

Line: Connection con = DriverManager.getConnection (“jdbc:oracle:thin:@localhost:1521:XE” ,  “adarsh”, “patel”);

Explanation : This Line is most important as it contains userid and password of Oracle Express Edition. Here I have used userid : adarsh and password : patel. Only 2 Fields you need to change is userid and password.

 

FilePath : C:\Program Files\Apache Software Foundation\Tomcat 7.0\webapps\example02\WEB-INF\

FileName: web.xml

<?xml version="1.0" encoding="ISO-8859-1"?>

<web-app> 
  <display-name>Example 02 </display-name>
  <description>  JDBC Using Servlet </description>
    <servlet>
    <servlet-name>ex02</servlet-name>
    <servlet-class>ex02</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>ex02</servlet-name>
      <url-pattern>/ex02</url-pattern>
  </servlet-mapping>
</web-app>

 

FilePath : C:\Program Files\Apache Software Foundation\Tomcat 7.0\webapps\example02\

FileName: index.jsp

<%
     response.sendRedirect(getServletContext().getContextPath()+"/ex02");
%>

Output

 Java Servlet Database Connectivity with Oracle Express Edition

Leave a Reply