Hibernate - Custom insert into database

Question

I write this post to know if someone knows how to do this:

I want to do this insert:

INSERT INTO TABLA (CAMPO1, CAMPO2) VALUES (?, crypt(?,'cdp'))

Crypt is a function stored in my database and the insert I would want to do it in my code. Actually when I want to insert something in the database I use:

getHibernateTemplate().persist(obj);

But I want to do a "custom" insert, because I need to use that function.

I am using hibernate + annotations:

@org.hibernate.annotations.SQLInsert (sql = "INSERT INTO TABLA (CAMPO1, CAMPO2) VALUES (?, crypt(?,'cdp'))")

But the key 'cdp' must be readed from a file, so this solution doesn't work for me.

I want to use a method on my code to execute a SQL query (INSERT query)

Is this doable using raw SQL (in other words, let's put Hibernate aside)? If yes, I don't understand why the `@SQLInsert` doesn't work for you. If not, I'm afraid Hibernate won't help. Can you clarify? — Pascal Thivent, Sep 14 '10 at 08:12
Doesn't work for me because I need to get a String with the cryption key, so I don't know how to introduce it on the @SQLInsert — Michel, Sep 14 '10 at 08:24

score 24 · Accepted Answer · edited Jun 13 '14 at 15:49

24

Here's a solution:

Query query = getSession().createSQLQuery("INSERT INTO TABLA (CAMPO1, CAMPO2) VALUES (:valor1, encripta(:valor2, :key))");
query.setParameter("valor1", valor1);
query.setParameter("valor2", valor2);
query.setParameter("key", key);
query.executeUpdate();

edited Jun 13 '14 at 15:49

Matt Passell

4,549
3
24
39

answered Sep 15 '10 at 06:47

Michel

681
2
7
17

10

you should not be in the habbit of doing string appends to your sql statements. Better to use named parameters. – Nathan Feger Apr 12 '12 at 15:15
2

@NathanFeger your point is important enough that I edited this answer to incorporate it – Matt Passell Jun 13 '14 at 15:49
Is there a way to get the generate keys [as in JDBC](http://stackoverflow.com/a/4224729/1652461) ? – remi Sep 25 '15 at 10:09

score 19 · Answer 2 · answered Mar 17 '14 at 18:12

Like Nathan Feger mentioned, named parameters are much cleaner and safer. In this case, the statement can be executed with the following code:

Query query = getSession().createSQLQuery("INSERT INTO TABLA (CAMPO1, CAMPO2) VALUES (:valor1, encripta(:valor2, :key))");
query.setParameter("valor1", valor1);
query.setParameter("valor2", valor2);
query.setParameter("key", key);
query.executeUpdate();

Hibernate - Custom insert into database

2 Answers2

Linked