Add the ablity to use sql functions on generated insert/update -------------------------------------------------------------- Key: HHH-272 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-272 Project: Hibernate3 Issue Type: New Feature Components: core Affects Versions: 3.1 rc3 Environment: Hibernate3, Oracle9 Reporter: Joe Dunne Priority: Minor Attachments: AbstractCollectionPersister.java, BasicEntityPersister.java, Column.java, HbmBinder.java, hibernate-mapping-3.0.dtd, Insert.java, SelectFragment.java, Update.java I would like to have Hibernate generate insert and update statements that contain a stored proc call. For example: insert into cat (id, name, tag) values ( ?, ? , encrypt(?) ) Currently I have implemented the following: maping file: <property name="ssn" type="java.lang.String" access="field"> <column name="SSN" scale="128" precision="0" not-null="false" sql-type="VARCHAR2" /> </property> <property name="ssnDecrypted" formula="FNC_JAVA_DECRYPT(SSN)" type="java.lang.String" access="field"/> <sql-insert>insert into PARTICIPANT (VERSION, DATE_MODIFIED, DATE_CREATED, MODIFIED_BY, CREATED_BY, FIRST_NAME, LAST_NAME, PASSWORD, USERNAME, SSN, EMAIL, ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, FNC_JAVA_ENCRYPT(?), ?, ?)</sql-insert> <sql-update>update PARTICIPANT set VERSION=?, DATE_MODIFIED=?, DATE_CREATED=?, MODIFIED_BY=?, CREATED_BY=?, FIRST_NAME=?, LAST_NAME=?, PASSWORD=?, USERNAME=?, SSN = FNC_JAVA_ENCRYPT(?), EMAIL=? where ID=? and VERSION=?</sql-update> Domain Object: public String getSsn() { return ssnDecrypted; } public void setSsn(String ssn) { this.ssn = ssn; this.ssnDecrypted = ssn; } This works, but I would like to avoid the <sql-insert> and <sql-update> tags since the project I am on has multiple customer deployments. Each customer can specify what fields they want encrypted. It would be much easier to not have the deployment team write the sql for insert and update for each customer. Also, Java encryption is out. We have to do it in the database. The EnhancedUserType could support this - perhaps. public String getUpdateFragment() { return "encrypt(?)"; } public String getInsertFragment() { return "encrypt(?)"; } //Is this one possible too? This would avoid the double mapping //above and the magic in the domain object public String getSelectFragment(String columnName) { return "decrypt("+columnName+")"; }