CODEWITHSUNDEEP
javahibernatejpahqlinsert-into
Berne
Berne

Reputation: 627

Hibernate: insert in entity using select with named parameters

I have a very specific problem in which I must do something like (in HQL):

insert into EntityA(field1, field2, field3, field4, field5)
select
    :paramForField1, 
    :paramForField2,
    :paramForField3,
    :paramForField4,
    :paramForField5
from 
    EntityB
where 
    ...

The parameters are being passed using Query.setParameter(String, Object) (JavaDoc) and they are String, String, String, Date and an Enum.

Although the parameter number is right (five parameters for five fields), Hibernate keeps raising the following exception:

...
Caused by: org.hibernate.QueryException: number of select types did not match those for insert [insert into ...]
    at org.hibernate.hql.ast.tree.IntoClause.validateTypes(IntoClause.java:116)
    at org.hibernate.hql.ast.tree.InsertStatement.validate(InsertStatement.java:57)
    at org.hibernate.hql.ast.HqlSqlWalker.postProcessInsert(HqlSqlWalker.java:701)
    at org.hibernate.hql.antlr.HqlSqlBaseWalker.insertStatement(HqlSqlBaseWalker.java:513)
    at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:255)
    at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:254)
    at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:185)
    at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136)
    at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
    at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
    at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:94)
    at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156)
    at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135)
    at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1651)
    ...

I'm using Hibernate 3.3.2 GA.

Thanks in advance.

Upvotes: 3

Views: 3958

Answers (3)

Christian Beikov
Christian Beikov

Reputation: 16400

Starting with hibernate 4.3 this is possible. Versions before hibernate 4.3 did not support parameters in the select clause.

Upvotes: 4

Vlad Mihalcea
Vlad Mihalcea

Reputation: 153700

Try this instead:

Class<?> entityAClass = EntityA.class;

Field field1 = entityAClass.getDeclaredField("paramForField1");
field1.setAccessible(true); 
String paramForField1 = field1.getName();

Field field2 = entityAClass.getDeclaredField("paramForField2");
field2.setAccessible(true); 
String paramForField2 = field2.getName();

Field field3 = entityAClass.getDeclaredField("paramForField3");
field3.setAccessible(true); 
String paramForField3 = field3.getName();

String hqlInsert = String.format(
    "insert into EntityA(%1$s, %2$s, %3$s)" + 
    "select c.%1$s, c.%2$s, c.%3$s from EntityB b" + 
    "where ...", 
    paramForField1, paramForField2, paramForField3);
int createdEntities = s.createQuery( hqlInsert )
    .executeUpdate();

Hibernate doesn't support parametrized INSERTs or SELECTs. You can use parameters in the WHERE clause only.

Any SQL string formatting is susceptible to SQL injection, that's why you need to use the Java Reflection idiom I suggested. If you don't supply a valid EntityA field name, then the field won't be resolved and an exception will be thrown.

This way you can build a dynamic query and also make sure you don't expose your code to SQL injection.

Upvotes: 1

micgn
micgn

Reputation: 265

I would guess that Hibernate is not prepared for this. Why you need to set the column names of table EntityB as parameters? Did you think of just concatenating the query string?

Upvotes: 0

Related Questions