Can stored procedures return a result set?

Question

I have a table called employee, which has its identifier, name, phone number. I use MySQL as my database. For this I use Java Swing. I am looking for a staff table with a name now using Java (I used the like clause on my Java page).

Now I need to implement this function in stored procedures. Is it possible? And how can we take this result set in Java code, if possible?

Now I wrote my stored procedure as follows

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

Sample code will be appreciated. Thanks

Answer 1

First, you have to write a msql procedure that sends a parameter to LIKE ,

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

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

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

Answer 2

Yes, you can. A stored procedure can even return multiple result sets.

 DELIMITER $$ -- recognized by mysql client but not phpmyadmin CREATE PROCEDURE prc_test() BEGIN SELECT * FROM employee WHERE empName LIKE '%su%'; END; $$ DELIMITER ; CALL prc_test(); -- to call 

Answer 3

When executing a stored procedure, it can actually return multiple ResultSet objects and / or update counts if it executes multiple statements.

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

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