How to use database functions on insert/update

bsiggelkow · **Posted:** Sat Sep 06, 2003 12:38 am

I would like to apply a database function to a column value when the value is inserted or updated. In particular, I want to apply the MySQL SHA function to hash the users password prior to its storage - I would roughly like the following SQL to occur:

Code:

update users set values (?,SHA(?),...) where user.id=?

What I would use the new "formula" property attribute for this? If so, is the formula applied on both storage and retrieval? Can an example be provided? Currently, I am able to use the function when selecting values as in the following HQL:

Code:

select usr from eg.Users usr where usr.username=? and usr.password=SHA(?)

I pre-apologize if this has alreay been answered :)

gavin · **Posted:** Sat Sep 06, 2003 12:44 am

The formula is applied on storage.

If you want to apply a SQL function, use a trigger.

gavin · **Posted:** Sat Sep 06, 2003 12:44 am

uggh

The formula is NOT applied on storage.

bsiggelkow · **Posted:** Sat Sep 06, 2003 12:47 am

Hmm looking at the road map document maybe I thought I could do something like:

Code:

However, this not what I want -- by design, I cannot unhash the value -- still thinking ... maybe native SQL statement?

bsiggelkow · **Posted:** Sat Sep 06, 2003 12:49 am

Arghh -- AFAIK MySQL doesn't support triggers :( Anyway, I will look into other options -- thanks for the quick response.

jpoley · **Joined:** Fri Oct 24, 2003 9:23 pm **Posts:** 10

I am wondering if this problem has a solution. I too want to use a native MySQL function (md5) for password encryption.

thanks in advance,
-j

gavin · **Posted:** Fri Oct 24, 2003 11:05 pm

Perhaps its a case for a custom persister.

jpoley · **Joined:** Fri Oct 24, 2003 9:23 pm **Posts:** 10

I assume that would be by extending EntityPersister?

-jp

mtnsurfer · **Joined:** Mon Jun 02, 2008 7:14 pm **Posts:** 1

is there an answer to this?

dharmendra.pandey · **Posted:** Tue Jun 03, 2008 2:19 am

Yes i agree that’s the problem with hibernate. I faced while same issue with pass code encryption and decryption .For retrieval you could use function in HBM files. But if you want to encrypt while insert or update I would suggest, try to implement those logic in setter and getter not sure .I guess that would work. That is not very good solution for them who has good background of DBA or for legacy system.

Any way another way we could think to this same is write these logic in our own extend class of public class AuditLogInterceptor implements Interceptor is which use to intercept save update and on load .I don’t know how efficient it would be .Try it that won’t take too much time
http://www.hibernate.org/318.html