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

[Rob Hasselbaum (JIRA)]

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

Rob Hasselbaum updated HHH-272:
-------------------------------

    Attachment: hhh-272-hbcore-patch.txt

I am attaching an updated patch and unit tests for this based on the code in 3.3.2.GA. This patch covers more cases than Stuart's original proposal and fixes some issues. It handles entity CRUD operations (of course), projections, predicates, order by, group by having, components, composite elements, user types, and any other case I could identify. To use it, change your property mapping like so:

<property name="creditCardNumber">
  <column name="credit_card_num" sql-set="encrypt(?)" sql-get="decrypt(credit_card_num)"/>
</property>

You can use any valid SQL expression--not just functions. Essentially, it works just like a formula property with two differences:

(1) The data is backed by a real column, which is exported with the schema.
(2) The data is read-write, not read-only.

There are just a couple of caveats:

(1) It works for property columns only. I made no attempt to handle id, key, index, or other kinds of non-property columns, which would involve more pervasive changes.
(2) The sql-set expression must contain exactly one placeholder ('?') character. This is enforced in the mapping validation. (Sorry Martin. I think you could use a generated property instead, though.)

I am interested in feedback, particularly if there's a case I missed or bugs. ;-) I also have a patch nearly ready to expose this functionality in hibernate-annotations in case the core patch is accepted.

> 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: Hibernate Core
>          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, hhh-272-hbcore-patch.txt, 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