GTU WTAD Practical 30 : Java MySQL Connectivity Example

GTU WTAD Practical 30 : Write a Java application to invoke a stored procedure using a CallableStatement. For this a stored procedure called incrementSalary may be developed to increase all the employees salary by a percentage specified in the parameter.

GTU WTAD Practical 30

Jump to GTU WTAD Practical 31

Database : Mysql

Database username : root

Database Password : <blank>

Database Name: mydabase

Table : employee

Column : emp_id,emp_name_emp_salary

My SQL Procedure :

DELIMITER //
CREATE PROCEDURE update_salary
(IN par1 INT)
BEGIN
 update employee set emp_salary = emp_salary + (emp_salary*par1/100);
END //
DELIMITER ;

 

To call SQL Procedure (10 % Salary Increment passed as parameter):

call update_salary(10);

 

File Name : index.jsp

File Location : Tomcat_Apache\webapps\gtu30\

<%@ page import="java.sql.*;" %>
<html>
	<head>
		<title>GTU WTAD Practical 30</title>
	</head>
	<body>
	
	<h2 align="center"> GTU WTAD Practical 30 : Write a Java application to invoke a stored procedure using a CallableStatement. For this a stored procedure called incrementSalary may be developed to increase all the employees salary by a percentage specified in the parameter.  </h2>
	<br><br>
	
	<form method="post" action="CallStoredProcedure">
		<table>
		<tr><td colspan="2"> Salary Increment  </td></tr>
		<tr><td colspan="2"> &nbsp; </td></tr>
		<tr><td>Increment Percentage</td> <td> <input type="text" name="per"></td></tr>
		<tr><td colspan="2"> <input type="submit" value="Insert"> </td></tr>
		</table>
		
		<%
			String s = (String) session.getAttribute("msg");
			
			if(s!=null)
			{
				out.println("<div align=\"left\"><font color=\"blue\">"+s+"</font></div>");
				session.removeAttribute("msg");
			}
		%>
	</form>
	</body>
</html>

 

File Name : CallStoredProcedure.java

File Location : Tomcat_Apache\webapps\gtu30\WEB-INF\classes

/*
		GTU WTAD Practical 30 : Write a Java application to invoke a stored procedure using a CallableStatement. For this a stored procedure called incrementSalary may be developed to increase all the employees salary by a percentage specified in the parameter.
*/

import java.io.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;

public class CallStoredProcedure extends HttpServlet
{
   
	
	public void doPost(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException
	{
 
		HttpSession session = request.getSession();
		
		try
		{
            Class.forName("com.mysql.jdbc.Driver");
            Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase","root","");
            Statement st=con.createStatement();
			
			String per = request.getParameter("per");
			 
			String callprocedure = "call update_salary(?)";
			PreparedStatement ps = con.prepareStatement(callprocedure);
			ps.setInt(1,Integer.parseInt(per));
			ps.executeUpdate();
			
			session.setAttribute("msg","Salary Incremented Succesfully");
			response.sendRedirect("./");
       }
       catch (Exception e)
       {
          
       }
	}
}

 

File Name : web.xml

File Location : Tomcat_Apache\webapps\gtu30\WEB-INF\

<?xml version="1.0" encoding="ISO-8859-1"?>
<web-app 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"
  version="3.0"
  metadata-complete="true">  
  
  <display-name>GTU WTAD Practical 30  </display-name>
  <description>Write a Java application to invoke a stored procedure using a CallableStatement. For this a stored procedure called incrementSalary may be developed to increase all the employees salary by a percentage specified in the parameter. </description>
  
  <servlet>
    <servlet-name>CallStoredProcedure</servlet-name>
    <servlet-class>CallStoredProcedure</servlet-class>
  </servlet>

  <servlet-mapping>
    <servlet-name>CallStoredProcedure</servlet-name>
      <url-pattern>/CallStoredProcedure</url-pattern>
  </servlet-mapping> 
 
</web-app>

 

Output

GTU WTAD Practical 30

Leave a Reply