how to call a stored function from jdbc?

Question

I am creating a login page using custom tag, in the below code i want to execute a stored oracle function where that function will take 2 parameter (name,password) to authenticate and return a number. But when i compile the below code it gives an error saying ( found: int) incompatible type. please tell me where am i going wrong ? am i calling the function correctly ?

package pack.java;
import pack.java.MyModel;
import java.io.*;
import java.lang.*;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.jsp.*;
import javax.servlet.jsp.tagext.*;
import java.sql.*;

public class MyController extends TagSupport
{
    HttpServletRequest request;
    HttpServletResponse response;

    public int doStartTag()throws JspException
    {
        request = (HttpServletRequest)pageContext.getRequest();              
        response = (HttpServletResponse)pageContext.getResponse();
        return EVAL_PAGE;
    }   

    public void check()
    {
        HttpSession mysession = request.getSession();
        Connection con;
        CallableStatement stmt;
        JspWriter out = pageContext.getOut();
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        }
        catch(ClassNotFoundException ex) {}
        try {         
            String aa = (String)MyModel.name.trim();
            String bb = (String)MyModel.pass.trim();
            con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","gaurav","oracle");
            stmt = con.prepareCall("select usercheck1(?,?) from dual");
            stmt.setString(1, aa);
            stmt.setString(2, bb);       
            rs = stmt.executeQuery();
            try {
                while (rs.next()) {
                    String empid = rs.getString (1);
                    mysession.setAttribute("user", empid);
                    if (empid != null) {
                        response.sendRedirect("/Myjsp/selectaction.jsp");
                    }
                    else
                        out.println("InValid User");
                }
            }
            catch(Exception ex) {}
        }
        catch(SQLException ex) {}
    }

    public int doEndTag() throws JspException {
        check();
        return EVAL_PAGE;
    }
}

below is the stored function

create or replace function usercheck1
    (uname varchar2, upass varchar2)
    return number
as 
    numb number;
begin
    select (employe_id)
      into numb
      from record
     where name = uname
       and password = upass;
    return numb;
end usercheck1;
/

executing the function using the below statement

select usercheck1 ('ghg','aa') from dual;

Answer 1

To execute a function, the canonical way is to use a CallableStatement. This is a special case (subclass) of PreparedStatement.

In addition, you have to specify the right output parameters type using one of the registerOutParameter methods. Once the call is completed (execute), you extract the output value from the statement itself. Not through a ResultSet.

All of this lead to something like:

CallableStatement stmt = con.prepareCall("{? = call usercheck1(?, ?)}");
# ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
# Prepare a `call`

stmt.registerOutParameter(1, Types.INTEGER);
# Setup out parameter type    ^^^^^^^^^^^^^

stmt.setString(2,aa);       
stmt.setString(3,bb);
stmt.execute(); 
#    ^^^^^^^
# execute the statement

int output = stmt.getInt(1);
#            ^^^^^^^^^^^
# Extract the result from the statement itself

---

A totally different way of doing, is to actually use a SELECT query to call the function. This is what was suggested by the OP in the question. That way, you can use a ResultSet as usual to extract the values:

PreparedStatement stmt = con.prepareStatement("SELECT usercheck1(?, ?) FROM DUAL");
# ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
# Prepare a statement

stmt.setString(2,aa);       
stmt.setString(3,bb);
ResultSet rs = stmt.executeQuery(); 
#                   ^^^^^^^^^^^^
# execute the query

while(rs.next()) {
  int output = rs.getInt(1);
  #            ^^^^^^^^^
  # Extract the result from the `ResultSet`

  # ...
  # do whatever you want with the data

}

Answer 2

First off you say the proc returns a number but your code is expecting a resultset which returns a string? Anyway, assuming that your proc returns an int. Change these lines

    stmt=con.prepareCall("call usercheck1(?,?)");

    stmt.setString(1,aa);
stmt.setString(2,bb);       
    rs=stmt.executeUpdate();

to

    stmt=con.prepareCall ("{? = call usercheck1(?, ?)}");

    stmt.registerOutParameter (1, Types.INTEGER);
    stmt.setString(2,aa);       
     stmt.setString(3,bb);
    stmt.execute(); 
    int output =stmt.getInt (1);

If you are actually expecting a string as a result from the proc, then change to

    stmt=con.prepareCall ("{? = call usercheck1(?, ?)}");

    stmt.registerOutParameter (1, Types.VARCHAR2);
    stmt.setString(2,aa);       
     stmt.setString(3,bb);
    stmt.execute(); 
    String output =stmt.getString (1);

ResultSets are used for cursors, and would require you to specify your out parameters as such. Hope that helps.

http://docs.oracle.com/cd/A84870_01/doc/java.816/a81354/samapp2.htm

If you are using a newer version of oracle >= 9i, you may want to use the begin end syntax.

Answer 3

Others suggests running the query through a select statement, but this will throw an exception if the function does any DML. Using a CallableStatement is the best option.