Servlet Database Connectivity Example

We are going to describe how to connect to the database in servlet.We have been provide some classes and API with which we will make use of the database connection. Database plays an important role in any application which requires to store the data and to fetch the data.

Servlet Database Connectivity Example

Servlet Database Connectivity Example

We are going to describe how to connect to the database in servlet.We have been provide some classes and API with which we will make use of the database connection. Database plays an important role in any application which requires to store the data and to fetch the data.

Requirements to connect with the MySQL database in Servlet:

MySQL should be installed in your computer.

MySQL connector JAR (should be kept into lib folder).

How to connect database in servlet.This are as follows:

First of all we have a created database and we create some table  fields which you can copy and use it:

 CREATE TABLE `userinformation` (         
                   `id` int(11) NOT NULL AUTO_INCREMENT,  
                   `name` varchar(20) DEFAULT NULL,       
                   `sex` varchar(10) DEFAULT NULL,        
                   `address` varchar(30) DEFAULT NULL,                                                       
                   PRIMARY KEY (`id`)                     
                 ) ENGINE=InnoDB DEFAULT CHARSET=latin1   

Description of program:

In this example we have used JDBC connection in servlet.We have to first create the a table in MySQL database and then connect it through JDBC to show all the records on web page. we have used for some servlet method "doGet" and "doPost". The doGet() is used to get information from the client/browser and doPost() is used to send information back to the browser.

PrintWriter out = response.getWriter():-PrintWriter is a representations of objects to a text-output stream. This class implements all of the print methods found in PrintStream. It does not contain methods for writing raw bytes, for which a program should use unencoded byte streams.

ConnectorJDBC.java

package connector;

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 ConnectorJDBC extends HttpServlet{
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
try{
doProcess(request,response);
}catch(SQLException e){
e.printStackTrace();
}
}
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
try{
doProcess(request,response);
}catch(SQLException e){
e.printStackTrace();
}
}
private void doProcess(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException , SQLException{
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
PrintWriter out = response.getWriter();
try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/naulej","root","root");
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from userinformation");

out.print("<html>");
out.print("<head>");
out.print("<title>Hello Connect Database</title>");
out.print("</head>");
out.print("<name>");
out.print("<h1>Name from database</h1>");
out.print("<br/>");

System.out.println("Get value from table userinformation.");
while(rs.next()){
out.println("<tr>");
out.print("Name : " + rs.getString("name"));
out.print("\t\t\t");

out.print("Sex : " + rs.getString("sex"));

out.print("Address : " + rs.getString("address"));

out.print("</tr><br/>");
}

out.print("</body>");
out.print("</html>");
// out.flush();
}catch(Exception e){
e.printStackTrace();
}finally{
if(stmt != null){
stmt.close();
}
if(conn != null){
conn.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>ConnectorJDBC</servlet-name>
<servlet-class>connector.ConnectorJDBC</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>ConnectorJDBC</servlet-name>
<url-pattern>/ConnectorJDBC</url-pattern>
</servlet-mapping>
</web-app>

output

Download Source Code