DataBase Connectivity and validation of data from Oracle Database in JAVA Servlet

Mohan Rathour
Nov 07, 2011
76.7k
0
0
Article

Introduction

This article is the next in the series of articles about Java Servlet. In this article we make an application and we will learn about how to Connect the Database and validate the username and password from the Database enter from the Login page. In this application we are using Oracle Database 10g. First of all we make a Database table for login validation. Steps to create a table in Oracle Database are given below:

Step-1: Install the Oracle Database. After Installing , Go to Start-> All Programs-> Oracle Database 10g Express Edition-> Run SQL Command Line click it. A run SQL command prompt appears.

Runsqlcommandline

opencommand

Step-2: First type connect or conn. Press Enter key then Enter user-name system again. Press Enter key then Enter Password. This password is same as installation time but by default we use tiger or scott. Press Enter key now you are connected to the Oracle Database.

Step-3: Now we are creating a Table. Creating tables is done with the create table command. The create table command does the following:

Defines the table name
Defines the columns in the table and the datatypes of those columns
Defines what tablespace the table resides in (optional)
Defines other characteristics of the table (optional)

Let's look at the create table command in action:

create table loginvalidation(username varchar2(20) primary key , password number(5) not null);

In this example, we create a table called loginvalidation which has 2 columns. The first column is username which is a VARCHAR2 datatype. This means we will be storing VARCHAR2 in this column. Did you notice on the line where we define the column username, that we included the word primary key? This is known as an in-line constraint because we are defining the constraint on the same line as the column associated with the constraint. What is a primary key constraint? A constraint is a rule that is applied to the table. In this case, the primary key constraint is a rule is says can't have a duplicate entry in the username column, and the username column can never be empty or "null". The Second column is password which is a NUMBER datatype. This means we will be storing NUMBER in this column that include the word not null. When we define a column to be NOT NULL, that means it cannot have a NULL value assigned. This eliminates the problem of three valued logic very nicely, but may not always be possible when designing tables.

Step-4: Insert the value in to the Table.

Let's look at the insert value into the table command in action:

insert into loginvalidation values('admin' ,12345);

By using this command, 1 row is created into the loginvalidation table and values are admin and 12345. Here we can stored 2 records into the database. Now close the command prompt.

Step-5: Open the eclipse, Select File-> New-> Dynamic web project. Fill up all the Entry and press Finish button. A new project is displayed into the Project Explorer. Here the Project Name is ServletLoginDetails.

Step-6: Now create Login.html, reg.html, home.html page. We make these pages as follows:

Right click on project -> new -> Html page .

Login.html

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Login page</title>
</head>
<body>
<center>
<form action="./firstserv" method="post">
username    
<input type="text" name="username" />
<br>
<br>
password    
<input type="password" name="password"></input><br><br>
   
<input type="submit" value="login"></input>
   
<a href="./reg.html">new user</a>
</form>
</center>
</body>
</html>

home.html

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Home page</title>
</head>
<body>
home page under construction...............
make a home page ...........
</body>
</html>

reg.html

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Registration Form</title>
</head>
<body>
registration page under construction...............
make a registration form.
</body>
</html>

Step-7: Now create Servlet as follows

Right click on project -> new -> Servlet. And fill up all the Entry here the name of Servlet is SerExam.

SerExam.java

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class SerExam extends HttpServlet
{
      Connection con;
      PreparedStatement ps;
      ResultSet rs;
      public void init(ServletConfig config)throws ServletException
      {
            try
               {
                        Class.forName("oracle.jdbc.driver.OracleDriver");
                        con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","tiger");
               }
                  catch (ClassNotFoundException e)
                     {
                        System.out.println(e);
                     }
                  catch (SQLException e)
                    {
                        System.out.println(e);
                     }
      }
      protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
      {
            doPost(request, response);
      }
      protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
      {
            response.setContentType("text/html");
            PrintWriter pw=response.getWriter();
            String username=request.getParameter("username");
            String password=request.getParameter("password");
            pw.println("<html><body>");
            try
            {
                  ps=con.prepareStatement("select * from loginvalidation where username=? and password=?");
                  ps.setString(1, username);
                  ps.setString(2, password);
                  rs=ps.executeQuery();
                  if(rs.next())
                  {
                        pw.println("<h3>welcome " +" " + username +"</h3>");
                        RequestDispatcher rd1=request.getRequestDispatcher("./home.html");
                        rd1.include(request,response);
                        //or
                        //response.sendRedirect("./home.html");
                        pw.println("<form method=\"post\" action=\"Login.html\">");
                        pw.println("<input type=\"submit\" name=\"logout\" " + "value=\"Logout\">");
                        pw.println("</form>");

                  }
                  else
                  {
                        pw.println("<center><h3>invalid username/password Enter Correct username/password</h3></center>");
                        RequestDispatcher rd2=request.getRequestDispatcher("./Login.html");
                        rd2.include(request,response);
                        //or
                        //response.sendRedirect("./Login.html");
                  }
            }
            catch (SQLException e)
                  {
                  e.printStackTrace();
                  }
      }
}

Step-8: Create a web.xml file if u use Eclipse IDE by default created by IDE then modify the url pattern as follows:

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd%22%20id=%22WebApp_ID%22%20version=%222.5 id="WebApp_ID" version="2.5">
<servlet>
    <servlet-name>SerExam</servlet-name>
    <servlet-class>SerExam</servlet-class>
</servlet>
<servlet-mapping>
    <servlet-name>SerExam</servlet-name>
    <url-pattern>/firstserv</url-pattern>
</servlet-mapping>
</web-app>

Step-8: Right Click on Project or application -> Run As -> Run on Server. Browser is open and at the end of URL enter the Login.html. For example: http://localhost:9999/ServletLoginDetails/Login.html. Enter the username and password in text boxes and click on login button.

If the username and password are valid then you see the url is changed that is this page is redirect to the given url pattern in web.xml file.

If the username and password are not valid then you see the url is not changed (http://localhost:9999/ServletLoginDetails/firstserv) but we are in Login.html page with a message.because we are using the RequestDispatcher rather than sendRedirect.

If we click the newuser hyperlink the the url also changed http://localhost:9999/ServletLoginDetails/reg.html.

I think it will be helpful for you to create this type of application.