Back to: Java Servlets Tutorials
Fetch Data from Database using Servlet
In this example, we will see How to Fetch Data from the Database using Servlet. Please read our previous article where we develop the Registration Form Application in Servlet. Here, we are using the below employee table, and based on the empid we will fetch the data from this employee table.
We are going to use the following employee table in this demo.
Please use the below SQL Script to create an “emp” database. Create a table “employee” with three columns:
CREATE TABLE employee
(
empid VARCHAR(10),
empname VARCHAR(45),
sal int
)
Insert following data into the table:
insert into emp.employee values('e001','raj',10000);
insert into emp.employee values('e002','harry',20000);
insert into emp.employee values('e003','sunil',30000);
insert into emp.employee values('e004','pollock',40000);
insert into emp.employee values('e005','jonty',50000);
insert into emp.employee values('e006','kallis',60000);
insert into emp.employee values('e007','richard',70000);
We have inserted a few records into the employee table. And we are getting the data from the database in servlet and printing it. Here we have three files:
- index.html
- Search.java
- web.xml
index.html page gets empid from the user and forwards this data to a servlet which is responsible to show the records based on the given empid. Search.java is the servlet file that gets the input from the user and maps this data with the database and prints the record for the matched data. On this page, we are displaying the column name of the database along with data, so we are using the ResultSetMetaData interface. web.xml is the configuration file that provides information about the servlet to the container.
index.html
<html> <body> <form action="servlet/Search"> Enter your Employee ID: <input type="text" name="empid" /> <br /> <input type="submit" value="search" /> </form> </body> </html>
Search.java
import java.io.*;
import java.sql.*;
import javax.servlet.ServletException;
import javax.servlet.http.*;
public class Search extends HttpServlet
{
public void doGet (HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
{
response.setContentType ("text/html");
PrintWriter out = response.getWriter ();
String empid = request.getParameter ("empid");
try
{
Class.forName ("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection ("jdbc:mysql://localhost:3306/emp", "root", "876745");
PreparedStatement ps =
con.prepareStatement ("select * from employee where empid=?");
ps.setString (1, empid);
out.print ("<table width=50% border=1>");
out.print ("<caption>Employee Details:</caption>");
ResultSet rs = ps.executeQuery ();
/* Printing column names */
out.print ("</br></br>");
ResultSetMetaData rsmd = rs.getMetaData ();
int total = rsmd.getColumnCount ();
out.print ("<tr>");
for (int i = 1; i <= total; i++)
{
out.print ("<th>" + rsmd.getColumnName (i) + "</th>");
}
out.print ("</tr>");
/* Printing result */
while (rs.next ())
{
out.print ("<tr><td>" + rs.getString (1) + "</td><td>" + rs.getString (2) + " </td><td>" + rs.getInt (3) + "</td></tr>");
}
out.print ("</table>");
}
catch (Exception e2)
{
e2.printStackTrace ();
}
finally
{
out.close ();
}
}
}
web.xml
<?xml version="1.0" encoding="UTF-8"?> <web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"> <servlet> <servlet-name>Search</servlet-name> <servlet-class>Search</servlet-class> </servlet> <servlet-mapping> <servlet-name>Search</servlet-name> <url-pattern>/servlet/Search</url-pattern> </servlet-mapping> </web-app>
Output
Enter Employee ID (empid) and click on the search button.
After clicking on the search button, you will get the following output:
In the next article, I am going to discuss How to Improve Servlet performance to fetch records from the database. Here, in this article, we develop an application which Fetch Data from the Database using Servlet and I hope you enjoy this How to Fetch Data from the Database using Servlet article.
About the Author:
Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.
