Is this possible - insert/update with sql function call

jjdunne · **Joined:** Thu Feb 17, 2005 4:20 pm **Posts:** 29

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(?) )

It looks like a LiteralType is supposed to be able to implement the objectToSQLString method to return "encrypt(?)" instead of "?".

snippet from org.hibernate.sql.Insert

Code:

   public Insert addColumn(String columnName, Object value, LiteralType type) throws Exception {
      return addColumn( columnName, type.objectToSQLString(value) );
   }

However, it does not look like this method is called in the Hibernate code base.

Am I looking at things correctly, or is there something I am missing?

max · **Posted:** Wed Mar 23, 2005 6:00 pm

check out <sql-insert> and <sql-update> tags.

allow you to specify custom sql.

jjdunne · **Joined:** Thu Feb 17, 2005 4:20 pm **Posts:** 29

max wrote:

check out <sql-insert> and <sql-update> tags.

allow you to specify custom sql.

Yep, did that. Those tags do provide the functionality.

I thought I could do it in a slicker way, however. Looking at the Insert and Update classes, it looks like a LiteralType can override the "?".

The reason I would like to avoid the <sql-insert> and <sql-updaet> tags is that 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. Enter the LiterType...

Also, Java encryption is out. We have to do it in the database.

rlynch · **Joined:** Mon Jan 31, 2005 1:33 pm **Posts:** 11

I was looking into similar functionality but it appears only discriminators utilize the LiteralType override of the "?". I would agree that functionality would be nice to have. One design concern would be that the POJO property could not be initialized to the resulting function output without a second round trip to the database.

max · **Posted:** Thu Mar 24, 2005 6:41 am

i dont know if we can do it safely - but put a request for improvement in the jira with this usecae....

jjdunne · **Joined:** Thu Feb 17, 2005 4:20 pm **Posts:** 29

rlynch wrote:

One design concern would be that the POJO property could not be initialized to the resulting function output without a second round trip to the database.

I was going to solve this is problem by mapping two fields for the column.

Code:

    <property name="ssn" type="java.lang.String" access="field" column="SSN"\>

    <property name="ssnDecrypted" formula="FNC_JAVA_DECRYPT(SSN)" type="java.lang.String" access="field"/>

Then in my Domain Object:

Code:

    public String getSsn() {
        return ssnDecrypted;
    }

    public void setSsn(String ssn) {
        this.ssn = ssn;
        this.ssnDecrypted = ssn;
    }

Is there a better way? Should this be part of the feature request?

max · **Posted:** Thu Mar 24, 2005 2:42 pm

looks like a pretty good workaround.

but yes, put it in a jira as the current workaround.

jjdunne · **Joined:** Thu Feb 17, 2005 4:20 pm **Posts:** 29

I've created issue number HHH-272 for this.