Can stored procedures return a result set?

Question

I have a table named employee, which has his id, name, phone number. I am using MySQL as my database. I am using Java Swing for that. I am searching the employee table with name now using Java (I have used like clause in my Java page).

Now I need to implement that function in stored procedures. Is it possible? And how can we take that resultset in Java code, if it is possible??

Now I have written my stored procedure as follows

BEGIN
     SELECT * FROM employee where empName like '%su%'
END

Sample code will be appreciated.. Thanks

Answer 1

First thing is you should write msql procedure that sends parameter for LIKE,

CREATE PROCEDURE simpleproc (param1 CHAR(20))
BEGIN
SELECT * FROM employee where empName like param1;
END

Then from java program you can use this code to use procedure,

CallableStatement cstmt = con.prepareCall("{call simpleproc(?)}");
cstmt.setString(1, "%su%");
ResultSet rs = cstmt.executeQuery();

Answer 2

When executing a stored procedure it may actually return multiple ResultSet objects and/or update counts if it does several statements.

You use CallableStatement to execute the proc and then getResultSet() or getUpdateCount() to get the right result. For multiple results sets/statements you call getMoreResults() to move through the results of the stored proc.

For a simple case like this you should just need to call getResultSet() once and process it.