Database Connectivity in Servlet

Database Connectivity in Servlet

In this section we will discuss how can we connect to the database in servlet.

In this example I used the MS Access to connect the database. To connect with MS Access there are some steps that should be followed :

1. Create a MS Access database file (as I created myServlet.mdb)

* Run MS Access -> 'Blank Access Database' -> Enter File name with '.mdb' extension.

* Create table by double clicking on "Create table in Design view".

*Define the columns and save the table (as I saved 'servlet')

2. To use the JDBC-ODBC Bridge driver, we have required to create a DSN (Data Source Name) which shows the connection of an ODBC to a specific server. To create a DSN follow these steps :

* Go to Control panel -> Administrative tools -> Data Sources (ODBC).

* In the ODBC Data Source Administrator select the tab System DSN -> Click on Add button -> select MS Access Driver (*.mdb) -> click on Finish button.

* In the ODBC Microsoft Access Setup fill the headers according to their field, and then select the 'Select' button.

* In the Select Database dialog box select the directory where your file is stored and give the name in the place of the Database Name ( I give myServlet.mdb) and then press OK -> OK -> OK.

3. Now we are able to connect the database using java servlet.

Example :

ConnectDb.java

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
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 ConnectDb extends HttpServlet {
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws IOException, ServletException {
res.setContentType("text/html");
PrintWriter pw = res.getWriter();
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:myServlet");
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT* FROM servlet");
pw.println("Id \t \t Name \t \t Place<br>");
while (rs.next()) {
pw.println("\t \t \t \t \t");
pw.println(rs.getObject(1).toString());
pw.println(rs.getObject(2).toString());
pw.println(rs.getObject(3).toString());
pw.println("<br>");
}
} catch (SQLException e) {
pw.println(e.getNextException());
} catch (ClassNotFoundException e) {
pw.println(e.getException());
} finally {
try {
if (rs != null) {
rs.close();
rs = null;
}
if (stmt != null) {
stmt.close();
stmt = null;
}
if (con != null) {
con.close();
con = null;
}
} catch (Exception e) {
pw.close();
}
}
}
}

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_ID" version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
<display-name>dbconnection</display-name>
<servlet>
<servlet-name>ConnectDb</servlet-name>
<servlet-class>ConnectDb</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>ConnectDb</servlet-name>
<url-pattern>/ConnectDb</url-pattern>
</servlet-mapping>
</web-app>

Output :

At first we will create a database

When you will execute the above example you will get the following example :

Download Source Code