JDBC Callable Statement example (Calling stored procedure of oracle)

stored procedure

CREATE OR REPLACE
PROCEDURE GETEMPLOYEEINFO
( ENO IN VARCHAR2
, OUT_NAME OUT VARCHAR2
, OUT_JOB OUT VARCHAR2
, OUT_SAL OUT VARCHAR2
) AS
BEGIN
  
  SELECT ENAME,JOB,SAL INTO OUT_NAME,OUT_JOB,OUT_SAL 
  FROM EMP WHERE EMPNO=ENO;
EXCEPTION
      WHEN NO_DATA_FOUND THEN
        OUT_NAME := NULL;
END;
package mypackage;

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

public class OracleCallableDemo extends HttpServlet
{
	public void doGet(HttpServletRequest req,HttpServletResponse res) throws ServletException,IOException
	{
		PrintWriter out = res.getWriter();
		res.setContentType("text/html");
		try
		{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url = "jdbc:oracle:thin:@//localhost:1521/orcl";
			String unm = "scott";
			String pwd = "tiger";
			Connection con = DriverManager.getConnection(url,unm,pwd);
			CallableStatement cstmt = 
			con.prepareCall("{call GETEMPLOYEEINFO(?,?,?,?)}");
			cstmt.setInt(1,1111);
			cstmt.registerOutParameter(2,java.sql.Types.VARCHAR);
			cstmt.registerOutParameter(3,java.sql.Types.VARCHAR);
			cstmt.registerOutParameter(4,java.sql.Types.INTEGER);
			cstmt.execute();
			
			if (cstmt.getString(2)!=null)
			{
				out.print (cstmt.getString(2) + " " + cstmt.getString(3) + " " + 
				cstmt.getInt(4));
			}
			else
			{
				out.print ("No record found");
			}
			
		}
		catch(Exception e)
		{
			out.print (e);
		}
	}
}

Login Form (validate user from database entry)

<body>
	<form action="ValidateUser" >
		User Name : 
		<input type="text" name="unm"><br>
		Password:
		<input type="password" name="pwd"><br>
		<input type="submit">
	</form>
</body>
package mypackage;

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

public class ValidateUser extends HttpServlet
{
	public void doGet(HttpServletRequest req,HttpServletResponse res) throws ServletException,IOException
	{
		PrintWriter out = res.getWriter();
		res.setContentType("text/html");
		String unm = req.getParameter("unm");
		String pwd = req.getParameter("pwd");
		
		try
		{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url = "jdbc:oracle:thin:@//localhost:1521/orcl";
			String unm1 = "scott";
			String pwd1 = "tiger";
			Connection con = DriverManager.getConnection(url,unm1,pwd1);
			String query = "select unm,pwd from REG_INFO where unm='"+unm+"' and pwd='"+pwd+"'";
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			if(rs.next())
				out.print ("Login successful");
			else
				res.sendRedirect ("SignupForm.html");
			
		}
		catch(Exception e)
		{
			out.print (e);
		}
	}
}