CHAPTER 8 Legacy databases and custom SQL column. Hence, the stored procedure mapping includes three columns bound to the homeAddress property. The call of the stored procedure prepares one OUT (the question mark) and a named input parameter. If you aren t using the Oracle JDBC drivers (other drivers or a different DBMS), you don t need to reserve the first OUT parameter; the result can be returned directly from the stored procedure. Look at the regular class mapping of the User class. Notice that the column names returned by the procedure in this example are the same as the column names you already mapped. You can omit the binding of each property and let Hibernate take care of the mapping automatically: { call SELECT_USERS_BY_RANK(?, :rank) } The responsibility for returning the correct columns, for all properties and foreign key associations of the class with the same names as in the regular mappings, is now moved into the stored procedure code. Because you have aliases in the stored procedure already (select … us.FIRSTNAME as FIRSTNAME…), this is straightforward. Or, if only some of the columns returned in the result of the procedure have different names than the ones you mapped already as your properties, you only need to declare these: { call SELECT_USERS_BY_RANK(?, :rank) } Finally, let s look at the call of the stored procedure. The syntax you re using here, { call PROCEDURE() }, is defined in the SQL standard and portable. A non- portable syntax that works for Oracle is begin PROCEDURE(); end;. It s recommended that you always use the portable syntax. The procedure has two parameters. As explained, the first is reserved as an output parameter, so you use a positional parameter symbol (?). Hibernate takes care of this parameter if you configured a dialect for an Oracle JDBC driver. The second is an input parameter you have to supply when executing the call. You can either use only positional parameters or mix named and positional parameters. We prefer named parameters for readability.
Visit our web design programs services for an affordable and reliable webhost to suit all your needs.
This entry was posted
on Saturday, March 8th, 2008 at 11:05 pm and is filed under PHP5.
You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.