Hibernate and MS SQL 2005 Sepcific sql statements.

slavag · **Joined:** Thu Jun 29, 2006 11:54 am **Posts:** 3

Hi.
I have some question how to do:
1) I deloping application that shoud run under JBoss or under pure java using hibernate anotations and persistence.

2) This application in addition to the regular enityt operations need also to contact MS SQL 2005 Server to retrive some encypted data (encrypted by secutiry mechanism in MS SQL 2005, like encryptbykey and open key).
I mean i need to execute some sql statements, for example:

"OPEN SYMMETRIC KEY 'KEY_NAME' DECRYPTION BY CERTIFICATE 'CERT_NAME';"

And

"create symmetric key 'KEY_NAME' with algorithm = TRIPLE_DES ENCRYPTION BY certificate 'CERT_NAME';"

But i don't know how to execute these statements, native query is not suitable here ( i tried - not working).

May be it could be done is other way.

Thank You and Best Regards.

alesj · **Posted:** Fri Jun 30, 2006 8:14 am

Use plain JDBC.

You can get the connection out of Session.

Code:

   /**
    * Get the JDBC connection of this Session.<br>
    * <br> 
    * If the session is using aggressive collection release (as in a
    * CMT environment), it is the application's responsibility to
    * close the connection returned by this call. Otherwise, the
    * application should not close the connection.
    *
    * @return the JDBC connection in use by the <tt>Session</tt>
    * @throws HibernateException if the <tt>Session</tt> is disconnected
    */
   public Connection connection() throws HibernateException;

slavag · **Joined:** Thu Jun 29, 2006 11:54 am **Posts:** 3

Thank You for the reply.
I'm the new to the EJB 3.0 and Hibernate.

I'm using JPA (Java Persistence API) that wraps the Hibernate. So i even don't have hibernate configuration file (i'm using persistence.xml). So, i don't know thow to get Session either JDBC Connection.

Could you please explain this in more details.

Thank You.

alesj · **Posted:** Sat Jul 01, 2006 5:51 pm

Tjhere is a tone of manuals, read at least one of them - hibernate.org/5.html.

slavag · **Joined:** Thu Jun 29, 2006 11:54 am **Posts:** 3

Hi.
I read those manuals. But, i'm not using Hibernate directly, for this reason i don't see a way to retrieve the Session. This is a problem :-(

alesj · **Posted:** Sun Jul 02, 2006 6:42 am

Like you inject

Code:

@PersistenceContext EntityManager em;

in JBoss EJB3 you can d othe same with Hibernate Session:

Code:

@PersistenceContext Session session;

Rgds, Ales

emmanuel · **Posted:** Fri Jul 07, 2006 9:35 am

or
( (Session) em.getDelegate() ).connection()

or
inject the datasource through @Resource Datasource ds in your session bean