How to Fetch Records From Database Using Servlet in Java

Introduction

This article explains how to fetch data from a database using a Servlet in Java. The NetBeans IDE is used for this application.

Fetch Data from Database

This article explains how to fetch data from a database using a servlet in Java. For this application we need the following tools:

  • Oracle10g Database
  • Tomcat Server
  • NetBeans IDE

We need to create the following files:

  1. userlogin table
  2. index.html file
  3. FetchData.java file
  4. web.xml file

1. userlogin table

For fetching data you need to have a table with multiple records. In this application I use a "userlogin" table syntax for creating this table as in the following:

create table userlogin(name varchar2(4000), password varchar2(4000), emailid varchar2(4000), country varchar2(4000));

To insert data the syntax is:

insert into userlogin values ('', '', '', '');

For example I inserted two rows using:

insert into userlogin values ('Sandeep', 'sandeep', 'sandy05.1991@gmail.com', 'India');

insert into userlogin values ('Rahul', 'rahul', 'rahul@gmail.com', 'India');

For creating manually

To manually create a table use the following link in which I show a demo.

http://www.c-sharpcorner.com/UploadFile/fd0172/registration-form-using-servlet-in-java/

Creating other files

We need to use the following procedure to create the other files.

Step 1

Open the NetBeans IDE.

Fig-1.jpg

Step 2

Choose "Java web" -> "Web application" as in the following:

Fig-2.jpg

Step 3

Type your project name as "Welcome" and click on "Finish" as in the following:

Fig-3.jpg

Step 4

Now delete your default "index.jsp" file and create a new "index.html" file and write the following code there.

index.html

<!DOCTYPE html>

<html>

    <head>

        <title>TODO supply a title</title>

        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

        <meta name="viewport" content="width=device-width">

    </head>

    <body>

       <form action="Search">

Enter your Name: <input type="text" name="uname"/><br/>

<input type="submit" value="search"/>

</form>

    </body>

</html>

 

 Step 5

Now create a servlet file with the name "Search" and write the following code there.

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 name=request.getParameter("uname");                          

              try{

                     Class.forName("oracle.jdbc.driver.OracleDriver");

                     Connection con=DriverManager.getConnection("jdbc:oracle:thin:@mcndesktop07:1521:xe","sandeep","welcome");               

                     PreparedStatement ps=con.prepareStatement("select * from userlogin where name=?");

                     ps.setString(1,name);                   

                     out.print("<table width=25% border=1>");

                     out.print("<center><h1>Result:</h1></center>");

                     ResultSet rs=ps.executeQuery();                

                     /* Printing column names */

                     ResultSetMetaData rsmd=rs.getMetaData();

                     while(rs.next())

                        {

                     out.print("<tr>");

                     out.print("<td>"+rsmd.getColumnName(1)+"</td>");

                        out.print("<td>"+rs.getString(1)+"</td></tr>");

                        out.print("<tr><td>"+rsmd.getColumnName(2)+"</td>");

                        out.print("<td>"+rs.getString(2)+"</td></tr>");

                        out.print("<tr><td>"+rsmd.getColumnName(3)+"</td>");

                        out.print("<td>"+rs.getString(3)+"</td></tr>");

                        out.print("<tr><td>"+rsmd.getColumnName(4)+"</td>");

                        out.print("<td>"+rs.getString(4)+"</td></tr>");                  

                     }

                     out.print("</table>");

 

              }catch (Exception e2)

                {

                    e2.printStackTrace();

                }

 

              finally{out.close();

                }

       }

 

} 

Step 6

Check your default web.xml file that it has the same code as in the following:

web.xml

<?xml version="1.0" encoding="UTF-8"?>

<web-app version="3.0" 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_3_0.xsd">

    <servlet>

        <servlet-name>Search</servlet-name>

        <servlet-class>Search</servlet-class>

    </servlet>

 

    <servlet-mapping>

        <servlet-name>Search</servlet-name>

        <url-pattern>/Search</url-pattern>

    </servlet-mapping>

</web-app> 

Step 7

Now our project is ready to run. Right-click on the "Project" menu then select "Run" as in the following:

Fig-4.jpg

Step 8

The following output is generated.

Fig-5.jpg

Step 9

Now type the name as you pass in your database file. For example I passed the name "Sandeep" since it exists in my database.

Fig-6.jpg

Step 10

Now click on the "Search" button. The data from the database is fetched with the username Sandeep as in the following:

Fig-7.jpg

Step 11

Now click on the back button of the browser and provide another name. Since I have two records in the database I passed the other one as the name "Rahul".

Fig-8.jpg

Step 12

Now click on "Search".

Fig-9.jpg