Database Access in Servlets

Introduction

  • To access the database in Servlet, load the JDBC driver and return the Connection object.
  • The Connection object is used to create Statement object. Before starting with database access through servlet, make sure that the proper JDBC connection with database has been setup.

Accessing a Database

Following files are required for accessing the database:
i. welcome.html
ii. ServletDatabaseDemo.java
iii. web.xml
iv. ojdbc14.jar

Note: We are using Oracle database and type 4 (thin driver) to connect with Servlet application.

Example : Accessing the database and displaying the record

//welcome.html

<form action="show" method="post">
      Enter Table Name : <input type="text" name="table">
      <input type="submit" value="Show Records">
</form>

//ServletDatabaseDemo.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 ServletDatabaseDemo extends HttpServlet  
{
      protected void doPost(HttpServletRequest req, HttpServletResponse res)throws ServletException,IOException
      {
           PrintWriter pw = res.getWriter();
           res.setContentType("text/html");        
           String tableName = req.getParameter("table");
           try
           {
               Class.forName("oracle.jdbc.driver.OracleDriver");
               Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott","tiger");
               Statement st=con.createStatement();
               System.out.println ("connection established successfully...!!");     
               ResultSet rs = st.executeQuery("Select * from "+tableName);
               pw.println("<table border=1>");
               while(rs.next())
               {
                     pw.println("<tr><td>"+rs.getInt(1)+"</td>
                                         <td>"+rs.getString(2)+"</td>
                                         <td>"+rs.getString(3)+"</td>
                                         <td>"+rs.getString(4)+"</td></tr>");
               }
               pw.println("</table>");
               pw.close();
               rs.close();
               con.close();
           }
           catch (Exception e)
           {
               e.printStackTrace();
           }
      }
}

//web.xml

<web-app>
   <servlet>
        <servlet-name>ServletDBConnection</servlet-name>
        <servlet-class>ServletDatabaseDemo</servlet-class>    
   </servlet>
   <servlet-mapping>
        <servlet-name>ServletDBConnection</servlet-name>
        <url-pattern>/showrecord</url-pattern>
   </servlet-mapping>
</web-app>

▲