[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-272) Add the ablity to use sql functions on generated insert/update

[Jason A.H. Mroz (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-272?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_28378 ] 

Jason A.H. Mroz commented on HHH-272:
-------------------------------------

Is there any movement on or workaround for this ticket?  I need to use Oracle's SYSTIME and can't maintain sql-insert config for the number of tables involved.

> 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+")";
> }

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira