JAVA DATABASE PROGRAMMING - Introduction to Java Programming: Comprehensive Version - page 1201

from Student
where Student.firstName = first and
Student.lastName = last;

return result;
end ;
//

delimiter ;
/* Please note that there is a space between delimiter and ; */

If you use an Oracle database, the function can be defined as follows:

create or replace function studentFound
(first varchar2 , last varchar2 )
/* Do not name firstName and lastName. */
return number is
numberOfSelectedRows number := 0 ;
begin
select count(*) into numberOfSelectedRows
from Student
where Student.firstName = first and
Student.lastName = last;

return numberOfSelectedRows;
end studentFound;
/

Suppose the function studentFound is already created in the database. Listing 32.4 gives an
example that tests this function using callable statements.

L ISTING 32.4

TestCallableStatement.java
1 import java.sql.*;
2
3 public class TestCallableStatement {
4 /** Creates new form TestTableEditor */
5 public static void main(String[] args) throws Exception {
6 Class.forName( "com.mysql.jdbc.Driver" );
7 Connection connection = DriverManager.getConnection(
8 "jdbc:mysql://localhost/javabook" ,
9 "scott" , "tiger" );
10 // Connection connection = DriverManager.getConnection(
11 // ("jdbc:oracle:thin:@liang.armstrong.edu:1521:orcl",
12 // "scott", "tiger");
13
14

load driver
connect database

// Create a callable statement

15 CallableStatement callableStatement = connection.prepareCall( create callable statement

16 "{? = call studentFound(?, ?)}" );

17
18 java.util.Scanner input = new java.util.Scanner(System.in);
19 System.out.print( "Enter student's first name: " );
20 String firstName = input.nextLine();
21 System.out.print( "Enter student's last name: " );
22 String lastName = input.nextLine();
23
24

enter fist Name

enter last Name

callableStatement.setString( 2 , firstName); set IN parameter

set IN parameter
register OUT parameter
execute statement

25 callableStatement.setString( 3 , lastName);

26 callableStatement.registerOutParameter( 1 , Types.INTEGER);

27 callableStatement.execute();