asked on

How to use functions in the select clause of a NamedQuery in JPA

Hi

We are in the process of migrating an application to JPA(Hibernate implementation).
Previously the application was using direct sqls.

One of the table has it's data in encrypted format using Oracle DES encryption.
The encryption logic has been implemented in a user defined oracle encrypt function and it is invoked in the trigger.
The decryption is done while fetching the data by invoking the user defined oracle decrypt function in the
select clause, some thing like this:

select id, decrypt(name) as name, decrypt(location) as location from Company

Now as we are migrating the application to JPA, I'm having difficulty in fetching the data from this table.
The encryption works fine as usual with by the triggers. But I do not know how to call the decrypt function when we
fetch the Company entities.

Is it possible to use the oracle functions in the NamedQuery's select clause. If so, how? Or is there any other way
to do this?

Your help is much appreciated.

Thanks,
Madhan

malfunction84

Have you tried the .createNativeQuery() method? You have to be careful to select all of the columns.

Query q = em.createNativeQuery("select id, decrypt(name) as name, decrypt(location) as location from company", Company.class);
List<Company> list = q.getResultList();

Open in new window

hai_madhan

ASKER

Thanks malfunction84,

I did try the NativeQuery option. Looks like NativeQueries are the answer to my question.
But I faced a different issue though. I'm not able to fetch the related entities.

For example one of the Company's attribute is country which. I don't know how to query that along with the company.

Could you please give me a sample to fetch an entity and the related entities using the NativeQuery.

Many Thanks.
Madhan

malfunction84

Darn, I was afraid of that. When you select the columns of the Company table, do you return the Company's "country_id" foreign key as well?

hai_madhan

ASKER

Yes.

ASKER CERTIFIED SOLUTION

malfunction84

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hai_madhan

ASKER

Unfortunately I couldn't find any solution for this. So we decided to implement the encryption in java level and remove the trigger based encryption.

Anyway Thanks for your replies malfunction84.