Solved

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

Posted on 2008-06-11
8
1,429 Views
Last Modified: 2013-11-24
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
0
Comment
Question by:hai_madhan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
8 Comments
 
LVL 8

Expert Comment

by:malfunction84
ID: 21766059
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

0
 

Author Comment

by:hai_madhan
ID: 21771856
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
0
 
LVL 8

Expert Comment

by:malfunction84
ID: 21772151
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?
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

by:hai_madhan
ID: 21774724
Yes.
0
 
LVL 8

Accepted Solution

by:
malfunction84 earned 500 total points
ID: 21776200
Hmm, it would appear that the JPA by itself doesn't support this.

I was about to suggest calling em.refresh() on each instance in that List, but I don't think that would work.  The reason: the Entity mapping itself would still be fetching encrypted values from the database, since it doesn't know to call decrypt() on the raw data from those columns.

Then I was going to suggest replacing the @Column annotations for the encrypted columns with @org.hibernate.annotations.Formula.  The problem with that is that those columns would then be read-only.  Even if it weren't, there's no way to specify another formula to use when inserting (to encrypt).

Toplink, itself an Oracle product, offers a DescriptorCustomizer which you can use to customize how queries are constructed.  An example is in the following thread:
http://forum.java.sun.com/thread.jspa?messageID=10205608&tstart=0

Sorry, that's all I've got.  I don't think it's possible.
0
 

Author Comment

by:hai_madhan
ID: 21794140
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.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I had a project requirement for a displaying a user workbench .This workbench would consist multiple data grids .In each grid the user will be able to see a large number of data. These data grids should allow the user to 1. Sort 2. Export the …
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Suggested Courses

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question