Java Reference
In-Depth Information
Invoking Stored Procedures
Data outlives application logic. This is a general rule of thumb, and as we can attest, it holds
true in practice. The natural lifespan of a database will tend to see multiple applications. The
lifespan of some of these applications will, in turn, tend to overlap, so that at any one time we
expect substantially different code bases to be accessing the same data.
To resolve such issues, databases usually provide their own programming language to
allow complex business rules to be expressed and enforced within the boundary of the data-
base itself. These languages are expressed in stored procedures—essentially an API to the
database. Often, free-form SQL access to such a database is denied, and only access through
stored procedures is permitted. Barring errors in the code of the stored procedures them-
selves, this removes any risk of corruption.
One final advantage of using stored procedures is that when a substantial calculation is
required, the use of a stored procedure can reduce the network traffic involved. For example,
if you invoke a stored procedure to calculate the grand total of a table of accounts, only the
request and the result figure would need to traverse the network. The equivalent client-side
implementation would need to acquire the value to be totaled from every row!
Taking the client example from the “Putting SQL into a Mapping” section, we could
replace the SQL logic in the <sql-insert> tag with a call to a suitable stored procedure. The
callable attribute is set to true to indicate that Hibernate needs to issue a call to a stored pro-
cedure instead of a standard query (see Listing A-19).
Listing A-19. Mapping a Call to the Stored Procedure
<sql-insert callable="true">
{call insertClient(?,?,?,?,?,?)}
</sql-insert>
In the stored procedure definition (see Listing A-20), you will note that the order of the
parameters to be passed in has been tailored to match the order in which they will be pro-
vided by Hibernate.
Listing A-20. The Logic of the Stored Procedure
CREATE PROCEDURE
insertClient( p_name varchar(32),
p_number varchar(10),
p_streetname varchar(128),
p_town varchar(32),
p_city varchar(32),
p_id int)
AS
BEGIN
INSERT INTO client
(id,name,number,streetname,town,city,country)
VALUES
(:p_id,:p_name,:p_number,:p_streetname,:p_town,:p_city,'UK');
END
Search WWH ::




Custom Search