blogger templates blogger widgets
This is part of a list of blog posts.
To browse the contents go to

JDBC web application - connections using DriverManager

Lets try to create a web application that uses JDBC.

This sample assumes that you have basic knowledge about servlets and JSPs.


- Create a "dynamic web application in eclipse".
- Create java package - "com.prodapps"
- Copy the ojdbc6.jar into WEB-INF/lib folder.

As shown above create and within com.prodapps package.
Also create index.jsp file under WebContent.

Note: If you find that web.xml file is missing then, right click the project -> Java EE tools -> Generate Deployment Descriptor Stub.

Now oracle database is a object-relational database management system (ORDBMS). ORDBMS is a RDBMS but with an object-oriented database model: objects, classes and inheritance are directly supported in database schemas and in the query language.


An entity(table schema) is synonymous with Java class.
Entity has attributes (columns) and Java classes has instance variables/attributes.
Record (each row) represents a real world entity which is synonymous with Java objects.
This opens the door to a whole new idea of ORM - Object relational Mapping.

There are tens of ORM frameworks available but we won't be using any. Nevertheless we would map records to java objects the simple, easy way.

//since our products schema involves only 2 attributes id and title, we design this java bean class which acts as a data transfer object (DTO) 
//DTO's are java class that represent the data present in our database tables

package com.prodapps;

public class Product {
 private int id;
 private String title;
 public void setId(int id) { = id;
 public int getId() {
  return id;
 public void setTitle(String title) {
  this.title = title;
 public String getTitle() {
  return title;
 public String toString() {
  return "Product [id=" + id + ", title=" + title + "]";

//Let's write our servlet that handles the JDBC code. I'm writing these //following J2EE 1.5 specification 

package com.prodapps;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

 * Servlet implementation class ProductServlet

@WebServlet(description = "This is primary product servlet", urlPatterns = { "/ProductServlet" })
public class ProductServlet extends HttpServlet {
 private Connection conn=null;
 private static final long serialVersionUID = 1L;

    public ProductServlet() {

 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  conn = createConnection();
  try {
   Statement stmt = conn.createStatement();
   ResultSet rs = stmt.executeQuery("select * from products where id=1");
   Product product = new Product();
   request.setAttribute("product", product);
   RequestDispatcher dispatch = request.getRequestDispatcher("/index.jsp");
   dispatch.forward(request, response);
  } catch (SQLException e) {

 public Connection createConnection(){
    try {
          conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "SYSTEM", "password");
    }catch(SQLException e){
    } catch (ClassNotFoundException e) {
  return conn;
 public void closeConnection(){
   try {
   } catch (SQLException e) {


Finally we write a jsp file, index.jsp

<%@ page import="com.prodapps.*" %>  
      language="java" contentType="text/html; charset=ISO-8859-1"  
 <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">  
 Product p = (Product)request.getAttribute("product");  
 Product id: <%= p.getId() %><br />  
 Product title: <%= p.getTitle() %>  

Build the project and publish on Tomcat server.

Hit this url on your browser.

Product id: 1
Product title: ipad

Download the project here.

Continue reading: JDBC web application - (shared) connections using DriverManager, connection rollbacks and other scenarios

No comments:

Post a Comment