---Contact Tel Detail - Id: 1, Contact id: 1, Type: Mobile, Number: 1234567890
Contact - Id: 2, First name: Scott, Last name: Tiger, Birthday: 1990-11-02
---Contact Tel Detail - Id: 3, Contact id: 2, Type: Home, Number: 1234567890
Contact - Id: 3, First name: John, Last name: Smith, Birthday: 1964-02-28
Contact - Id: 4, First name: Rod, Last name: Johnson, Birthday: 2001-11-01
Contact - Id: 5, First name: Michael, Last name: Jackson, Birthday: 1964-11-01
---Contact Tel Detail - Id: 4, Contact id: 5, Type: Home, Number: 11111111
---Contact Tel Detail - Id: 5, Contact id: 5, Type: Mobile, Number: 22222222
You can see that the new contacts with the telephone details were all inserted into the database.
Calling Stored Functions Using SqlFunction
Spring also provides a number of classes to simplify the execution of stored procedures/functions using
JDBC. In this section, we will show you a simple function using the SqlFunction class to call a SQL
function in the database. We will use MySQL as an example, create a stored function, and call it using the
SqlFunction<T> class.
We're assuming you have a MySQL database with a schema called prospring3_ch8, with a user name
and password both equaling prospring3 (the same as the example in the section "Exploring the JDBC
Infrastructure"). Let's create a stored function called getFirstNameById(), which accepts the contact's ID
and returns the first name of the contact. Listing 8-50 shows the script to create the stored function in
MySQL (store-function.sql). Run the script against the MySQL database.
Listing 8-50. Store Function for MySQL
DELIMITER //
CREATE FUNCTION getFirstNameById(in_id INT)
RETURNS VARCHAR(60)
BEGIN
RETURN (SELECT first_name FROM contact WHERE id = in_id);
END //
DELIMITER ;
The stored function should be self-explanatory. It simply accepts the ID and returns the first name
of the contact record with the ID.
Let's create a new interface called ContactSfDao for this example. Listing 8-51 shows the interface.
Listing 8-51. The ContactSfDao Interface
package com.apress.prospring3.ch8.dao;
public interface ContactSfDao {
public String getFirstNameById(Long id);
}
The second step is to create the SfFirstNameById class to represent the stored function operation,
which extends the SqlFunction<T> class. Listing 8-52 shows the class.
Search WWH :
