Custom sql and Stored proceduresCurrently 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:
- "preparing" refactor (DONE)
- Refactored persisters to have common "generateSQLString" methods. (removing the need for having a CustomSQL/StoredProcedure perister)
- 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
- 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
- Basic support in collection persisters (DONE)
- "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 docsHow 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.
|