Stored Procedure - Java - MySQL -Login Example - Eclipse

Log in Example Using Stored Procedure 

Java - MySQL - Eclipse

 Project Explorer:

Database Table: sec_login

mysql> desc sec_login;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | YES  |     | NULL    |       |
| password | varchar(30) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.05 sec)

mysql>

Stored Procedure: checklogin()

delimiter //
create procedure checklogin(in user varchar(20), in pass varchar(30))
begin

select * 
from sec_login 
where username=user AND password=pass;

end
//
delimiter ;

LoginDao.java

package com.login.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.login.model.Login;
import com.login.utilities.DBUtilities;

public class LoginDao 
{

 public boolean checkLogin(Login login) throws SQLException
 {
  Connection con=null;
  CallableStatement callableStatement = null;
  try{
 
  con=DBUtilities.getConnection();
  String dbCheckLogin = "{call checklogin(?,?)}";
  callableStatement = con.prepareCall(dbCheckLogin);
  callableStatement.setString(1, login.getUsername());
  callableStatement.setString(2, login.getPassword());
  ResultSet rs= callableStatement.executeQuery();
  
  if(rs.next())
  {
   return true;
  }
  else 
  {
   return false;
  }
   
  }
  finally{
   DBUtilities.closeConnection(con);
  }
 }
 
}

LoginManagement.java

package com.login.model;

import java.sql.SQLException;
import com.login.dao.LoginDao;

public class LoginManagement 
{

 LoginDao logindao= new LoginDao();
 public boolean checkLogin(Login login) throws SQLException
 {
  return logindao.checkLogin(login);
 }

 
}

LoginController.java

package com.login.controller;

import java.io.IOException;
import java.sql.SQLException;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.login.model.Login;
import com.login.model.LoginManagement;

/**
 * Servlet implementation class LoginController
 */
@WebServlet("/LoginController")
public class LoginController extends HttpServlet {
 private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public LoginController() {
        super();
        // TODO Auto-generated constructor stub
    }

 /**
  * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
  */
 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  // TODO Auto-generated method stub
  doPost(request, response);
 }

 /**
  * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
  */
 protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  // TODO Auto-generated method stub
  
  LoginManagement loginManagement = new LoginManagement();
  RequestDispatcher rd= null;
  String action=request.getParameter("actiontype");
  if(action.equals("Login"))
  {
   
   String username=request.getParameter("username");
   String password= request.getParameter("password");
   Login login= new Login(username, password);
   boolean result=false;
   try {
     result = loginManagement.checkLogin(login);
     if(result)
     {
      request.setAttribute("user", login.getUsername());
      rd=request.getRequestDispatcher("Home.jsp");
      rd.forward(request, response);
      return;
     }
  else
  {
   request.setAttribute("err", "err");
   rd=request.getRequestDispatcher("Login.jsp");
   rd.forward(request, response);
   return;
  }
   } catch (SQLException e) {
    request.setAttribute("err", "err");
    rd=request.getRequestDispatcher("Login.jsp");  
    rd.forward(request, response);
    return;
   } 
  }
  

 }

}

Login.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!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>Log in</title>
<script type="text/javascript">
function validateForm()
{
 
 var x=document.getElementById("username");
 if (x.value=="")
   {
  
  document.getElementById('username_innermsg').innerHTML="Please enter the Username.";
    x.focus();
  return false;
   }
 
 document.getElementById('username_innermsg').innerHTML='';
 var x=document.getElementById("password");
 if (x.value=="")
   {
  
  document.getElementById('password_innermsg').innerHTML="Please enter the Password.";
    x.focus();
  return false;
   }
 
 document.getElementById('password_innermsg').innerHTML='';
}

</script>

</head>
<body>
<center>
<h1>Log in</h1>
<form action="LoginController" method="post" onsubmit="return validateForm();">
<input type="hidden" name="actiontype" value="Login">
<table >
<tr>
<td>Username :</td><td><input type="text" name="username" id="username"></td><td width="200px"> <i style="color: red;" id="username_innermsg"></i></td>

</tr>
<tr>
<td>Password :</td><td><input type="password" name="password" id="password"></td><td width="200px"> <i style="color: red;" id="password_innermsg"></i></td>
</tr>
<tr><td></td><td  ><input type="submit" value="Login"><input type="reset" value="Cancel"></td><td ></td> </tr>
</table>


</form>
<i  style="color: red;">
<%
String er=null;
try{
 er= (String)request.getAttribute("err");
 //out.print(er);
 if(er.equals("err"))
 {
  out.print("Username/Password not correct"); 
 }
}
catch (Exception e){
 
}

%>
</i>
</center>
</body>
</html>

Download project: SecureLoginUsingStoredProcedure

Stored Procedure MySQL Example

MySQl Stored Procedure Example

Table:

mysql> desc employees;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| empid     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| salary | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql>

mysql> select * from employees order by id;
+------+-----------+--------+
| id   | name      | salary |
+------+-----------+--------+
|  100 | Ramu      |  10000 |
|  101 | Santhosh  |  15000 |
|  102 | Pratheesh |  16000 |
|  103 | Vasudevan |  20000 |
|  104 | Sajeevan  |  25000 |
+------+-----------+--------+
5 rows in set (0.02 sec)

mysql>

Stored Procedure getsalary() - gets name and salary for the given id.

delimiter //
create procedure getsalary(IN id INT)
begin
select name, salary
from employees
where empid=id;
end
//
delimiter ;

Defining Stored Procedure in MySQL:

mysql> delimiter //
mysql> create procedure getsalary(IN id INT)
    -> begin
    -> select name, salary
    -> from employees
    -> where empid=id;
    -> end
    -> //
Query OK, 0 rows affected (0.04 sec)

mysql> delimiter ;

Calling Stored Procedure

mysql> call getsalary(102);
+-----------+--------+
| name      | salary |
+-----------+--------+
| Pratheesh |  16000 |
+-----------+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

Notes:

DELIMITER statement is used to change the standard delimiter(;) to another, in this case the delimiter is changed to //.


Declaring Variables

DECLARE variablename datatype DEFAULT initialvalue;

delimiter //
create procedure gettotalsalary()
begin
DECLARE totalsalary int DEFAULT 0; 
select sum(salary) into totalsalary
from employees;
select totalsalary;
end
//
delimiter ;

mysql> call gettotalsalary;
+-------------+
| totalsalary |
+-------------+
|       86000 |
+-------------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> 

Parameters
The parameter has one of three modes IN, OUT and INOUT.

IN -parameter to input.
OUT-parameter to output.
INOUT -shared parameter.

Conditional Control

IF expression THEN commands
ELSEIF expression THEN commands
ELSE commands
END IF; 

CASE
WHEN expression THEN commands
…
WHEN expression THEN commands
ELSE commands
END CASE;

WHILE loop

WHILE expression DO
Statements
END WHILE

REPEAT loop - equivalent to do while loop in c.

REPEAT
statements;
UNTIL expression
END REPEAT

LEAVE - equivalent to BREAK in c.
   
ITERATE - equivalent to CONTINUE in c.

Reverse of a String and Number of Vowels - Java Program - Scanner

Reverse of a String and Number of Vowels  

 Scanner -Java Program

Program: Reverse.java

import java.util.Scanner;
class Reverse 
{
public static void main(String args[])
{
 String string,reverseString = "";
 int vowelCount=0;
 
   Scanner sc=new Scanner(System.in);
   System.out.print("Enter the word: ");
   string= sc.nextLine();
   
   for(int i=string.length()-1;i>=0;i--)
   {
    char ch=string.charAt(i);
    reverseString+=ch;
    if(ch=='A'||ch=='a'||ch=='E'||ch=='e'||ch=='I'||ch=='i'||ch=='O'||ch=='o'||ch=='U'||ch=='u')
    vowelCount++;
   }
   
   
   System.out.println("Reverse: "+reverseString+"\nNumber of vowels: "+vowelCount);
   
}
}

Output:

Enter the word: 2k8cse
Reverse: esc8k2
Number of vowels: 1