HIBERNATE JBoss.org
 |  Register  | 
     
News 
About 
   Feature List 
   Road Map 
Documentation 
   Related Projects 
   External Documentation 
Download 
Forum & Mailinglists 
Support & Training 
JIRA Issue Tracking
Wiki Community Area


Hibernate Public Training Courses


Get Hibernate in Action eBook!


JavaWorld 2003 Finalist


Jolt Award 2004 Winner
      
Documentation > Community Area > Custom sql and Stored procedures

Custom sql and Stored procedures

Currently a scripple-page for me while working on adding custom sql and stored procedure support into hibernate 2.2/3 - and the basic support is now available in CVS.

Plan:

  1. "preparing" refactor (DONE)
    • Refactored persisters to have common "generateSQLString" methods. (removing the need for having a CustomSQL/StoredProcedure perister)
  1. Basic custom-sql (DONE)
    • Add sql-insert, sql-update, sql-delete tags to the dtd and Binder.
    • Add custom sql metadata and have the persisters manage it
    • With this we will have "crude" support for custom sql (crude as in you need to put the ?'s in the sequence internals expect it
  1. Basic stored procedure support (DONE)
    • Add support for callable="true" on sql-xxx
    • With this we will have similar crude support for stored procedures/functions
  1. Basic support in collection persisters (DONE)
  1. "user-friendly" custom sql/sp support
    • Add support for using :propname instead of ?'s

Syntax is as follows (for callable statements -> Stored Procedures):

    <class name="Patient" persister="net.sf.hibernate.persister.StoredProcedurePersister">

        <id name="id" type="long">
            <generator class="native"/>
        </id>

        <property name="firstName" type="string"/>
        ...

        <sql-insert callable="true">{call createPatient (?, ?, ?, ?)}</sql-insert>
        <sql-delete callable="true">{? = call deletePatient (?)}</sql-delete>
        <sql-update callable="true">{? = call updatePatient (?, ?, ?, ?)}</sql-update> 
    </class>
  • user need to specify sql/stored procedure and place ?'s in the order of the mapped properties.
  • maybe we should add support for :'syntax so it would be something like:

<sql-insert callable="true">{call createPatient (:firstName, :id, :version, :lastName)}</sql-insert> ?

Possible issues:

  • when hibernate looses generation of CUD-sql control it gets hard/impossible to support stuff like dynamic update/insert, custom where clause etc. Binder should complain if user expects to much by specifying both custom sql and dynamic features of hibernate
  • how about properties that span multiple columns ? (e.g. MonetaryAmount) - should we support :money.1, :money.2 for a money property with a two column span ? (note: customtype requires such columns to appear in the defined sequence)
  • Can we assume the SP's has the granularity of a table or of an entity ?

Is it addA(id,a1,a2) and addB(aid,b1,b2) or is it addA(id,a1,a2) and addB(id,a1,a2,b1,b2) where adding a B will only require one SP call? if yes - then how do we express that in hbm.xml ? (and how do we control it smoothly in the hibernate core ?)

  • Oracle sucks regarding stored procedures and returning resultsets. Every other db's can do a executeQuery() and return a resultset. We would have to go through dirty loops to support oracle's faulty jdbc implementation to get a resultset from them in a cross-db uniform way. Thus how about we added support for session.createResultsetQuery(ResultSet rs, mapping) ? (where mapping is some magic i haven't decided on yet ;)

Initial docs

How it works

The class and collection persisters already contains a set of configuration time generated strings (insertsql, deletesql, updatesql etc.). The new tags sql-insert, ql-delete, sql-update simply overrules these strings.

The callable attribute simply toggles wether the sql should be done via CallableStatements instead of PreparedStatements.

If callable=false everything works like any other sql - the only difference is the user can provide custom sql (probably not that useful, but that's the basis for it ;)

if callable=true then it goes through callablestatements.

Syntax and prerequistes:

The syntax is simply just ordinary sql - anything the underlying jdbc driver can handle. You can find some crude examples in CustomSQLTest.java and CustomSQL.hbml.xml

The stored procedures are in most cases (read: better do it than not) required to return the number of rows inserted/updated/deleted since hibernate has some runtime checks on wether the statement had the expected success. Hibernate always register the 1 jdbc parameter as a numeric output parameter for the CUD operations. (this might need to be configurable - but let see if it is a problem).

Example:

CREATE OR REPLACE FUNCTION updatePatient (

uid IN NUMBER,

unationalID IN VARCHAR2,

ufirstName IN VARCHAR2,

ulastName IN VARCHAR2

) RETURN NUMBER IS

BEGIN

update patient

set

nationalID = unationalID,

firstName = ufirstName,

lastName = ulastName

where id = uid;

return SQL%ROWCOUNT;

END updatePatient;

Collection persisters have the extra sql-delete-all which does a complete delete of an collection. The class persisters does not have a similar construct.

To see the sequence hibernate expects the parameters for the sql you should put the following in log4j:

### enable logging of CUD sql (et.al)###

log4j.logger.net.sf.hibernate.persister.BasicEntityPersister=debug

log4j.logger.net.sf.hibernate.collection=debug

This will activate "dump of static sql" to the console from which you can see what sql hibernate uses.

      

coWiki