4

I am switching ORM framework from Hibernate to OpenJPA.

In Hibernate we could annotate a field with @ColumnTransformer like below.

@Column(name = "EMP_NAME", length = 4000)
@ColumnTransformer(
        read = "pgp_pub_decrypt(emp_name::bytea,dearmor('"+key1+"'))",
        write = "pgp_pub_encrypt(?, dearmor('"+key2+"'))"
)
private String empName;

How to do the same in OpenJPA

auhuman
  • 962
  • 2
  • 13
  • 34
  • If specifying the entire query instead of transforming just a single column is acceptable, there are some possibilities listed [here](http://stackoverflow.com/a/21487061/2646526). – heenenee Aug 15 '16 at 20:19
  • Transforming single column is what i am looking for. Also need to call the function when persisting the data. This link shows example for reading only. – auhuman Aug 15 '16 at 23:14

1 Answers1

2

I am not sure of OpenJPA specific capabilities related to this, but the following two alternatives would work for all JPA providers:

  1. Create an updatable view that does the necessary transformations and map the entity to the view instead of the table.
  2. Move the transformations to middleware and apply them in entity lifecycle callbacks.

The other benefit of both solutions is that you keep the entities clean of custom native SQL.

Community
  • 1
  • 1
Dragan Bozanovic
  • 23,102
  • 5
  • 43
  • 110
  • Thanks. I have a doubt in this approach. In my requirement I have to pass the key for the encrypt method from the application. Is it possible to pass the public key & private key to the view dynamically from the entity on INSERT & SELECT? – auhuman Aug 16 '16 at 22:44
  • I also tried moving the transformation to entity lifecycle callbacks - PrePersist. It is persisting the postgres function as string instead of calling the function `@PrePersist @PreUpdate public void prePersist(){ this.empName = "pgp_pub_encrypt('"+this.empName+"', dearmor('"+key1+"'))"; }` – auhuman Aug 16 '16 at 22:53
  • I am trying out the option 1. Faced with challenges. Overcoming one by one. – auhuman Aug 17 '16 at 06:30
  • @auhuman Regarding passing keys to the view, I'm not sure how exactly you are storing and accessing these keys. If they are in the database, then the view could access them directly. Regarding the lifecycle methods, I meant to call the equivalent encryption library from there, not the database function, i.e. the middleware would do encryption and decryption. – Dragan Bozanovic Aug 17 '16 at 11:29
  • I want to pass the privateKey and passphrase from my java application when I am quering the view. I have created an entity to with regular properties + properties like publicKey, privateKey and passphrase. I also created an insert rule on the view to use the publicKey to encrypt, it works fine. Query the view with the privateKey and passpharse passed dynamically to the view from the java service. `CREATE VIEW visits AS select id,visit_time,host,url,referer,pgp_pub_decrypt(comment, dearmor(**privateKey**), **passphrase**) as comment from accesslog;` How pass them from OpenJPA on query? – auhuman Aug 17 '16 at 19:38
  • There is no way to pass parameters to a view on `select` statements. You can store the keys in the database as well and access them in the view implementation. – Dragan Bozanovic Aug 17 '16 at 20:33
  • Thanks Dragan Bozanovic. I am using JPA Repository Native Query to the table directly and dynamically pass the key and passphrase. – auhuman Aug 18 '16 at 06:54