Batch insert HQL query with literals

marco-p · **Posted:** Wed Aug 16, 2006 12:58 pm

Hi, I've some troubles trying to write an HQL query to duplicate a price list.
I've two entities, PriceList and PriceListRow, and I'm trying to implement a clone procedure to create a new PriceList from another, duplicating all the rows. Cloning the PriceList is trivial, the problem is with PriceListRow.

The SQL to do this is something like this:

INSERT INTO PriceListRow( priceListId, areaId, price )
SELECT 2, areaId, price FROM PriceListRow WHERE priceListId = 1

where '2' is the id generated for the new PriceList.
How can I specify a literal in HQL ?

I've tried:
-using a SQL query (not permitted)
-using named parameter (I get an error: number of select types did not match those for insert)
-using the literal (I get an error: column not mapped, with no name specified)

Anyone has an idea on how to proceed?

TIA

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

All of those options will work, with the correct syntax. However, they're a bit DB-oriented, and we prefer O-oriented. I suggest that you evict all the entities (iterate over the rows to evict them), set all the IDs to their unsaved-values, and save the same pojos. These makes them into new entities.

Note that you will have to deproxy/force-load the rows before evicting them, if they're lazily loaded and not yet loaded.

marco-p · **Posted:** Thu Aug 17, 2006 4:42 am

Thanks for your reply. Generally speaking, I can agree to prefer a O-oriented approach, but not in this case.
For the DB, I think it's much better a single INSERT .. SELECT than many INSERT .. VALUES, but we know Hibernate it's much more than a query generator.

The OO approach, correct me if I'm wrong, is something like this:
- iterate the collection of entities to be duplicated
- for each entity evict it to make it detached
- reset the id to unsaved-value and change the reference to parent entity
- save the new entity
- periodically flush the session and clear the cache (according to what is stated in chapter 13 for batch processing).

Given that, in my specific case, I still prefer a DB-oriented approach, if possible.

I'll add some detail:

*SQLQuery:

Code:

session.createSQLQuery("any SQL").executeUpdate();

gives the error:

Code:

java.lang.UnsupportedOperationException: Update queries only supported through HQL
        at org.hibernate.impl.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:753)
...

*Named parameter:

Code:

session.createQuery( "insert into PriceListRows (priceList, area, price) select :toPriceList, p.area, p.price from PriceListRows p where p.priceList.id = :fromPriceListId" ).setEntity("toPriceList", priceList).setLong("fromPriceListId", priceListId).executeUpdate();

gives the error:

Code:

org.hibernate.QueryException: number of select types did not match those for insert [insert into PriceListRows (priceList, area, price) select :toPriceList, p.area, p.price from PriceListRows p where p.priceList.id = :fromPriceListId]
        at org.hibernate.hql.ast.tree.IntoClause.validateTypes(IntoClause.java:92)
        at org.hibernate.hql.ast.tree.InsertStatement.validate(InsertStatement.java:34)
        at org.hibernate.hql.ast.HqlSqlWalker.postProcessInsert(HqlSqlWalker.java:600)
        at org.hibernate.hql.antlr.HqlSqlBaseWalker.insertStatement(HqlSqlBaseWalker.java:491)
        at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:253)
        at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:218)
        at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:158)
        at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:109)
...

*Literal:
if I try:

Code:

session.createQuery( "insert into PriceListRows (priceList.id, area, price) select " + toPriceListId + ", p.area, p.price from PriceListRows p where p.priceList.id = :fromPriceListId" ).setLong("fromPriceListId", priceListId).executeUpdate();

I get:

Code:

org.hibernate.QueryException: could not resolve property:  of: model.PriceListRows [insert into PriceListRows (priceList.id, area, price) select 17, p.area, p.price from PriceListRows p where p.priceList.id = :fromPriceListId]
        at org.hibernate.persister.entity.AbstractPropertyMapping.throwPropertyException(AbstractPropertyMapping.java:43)
        at org.hibernate.persister.entity.AbstractPropertyMapping.toType(AbstractPropertyMapping.java:37)
        at org.hibernate.persister.entity.AbstractEntityPersister.getSubclassPropertyTableNumber(AbstractEntityPersister.java:1282)
        at org.hibernate.hql.ast.tree.IntoClause.isSuperclassProperty(IntoClause.java:184)
        at org.hibernate.hql.ast.tree.IntoClause.visitPropertySpecNodes(IntoClause.java:143)
        at org.hibernate.hql.ast.tree.IntoClause.initializeColumns(IntoClause.java:125)
        at org.hibernate.hql.ast.tree.IntoClause.initialize(IntoClause.java:37)
...

Am I missing something obvious? I re-checked the documentation and seems the correct syntax to me, but I'm evidently wrong...

pepelnm · **Posted:** Thu Aug 17, 2006 5:14 am

Does this work?

Code:

      String hql = 
         "insert into PriceListRows" +
         "(" +
         "   priceList, " +
         "   area, " +
         "   price " +
         ")" +
         "select distinct" +
         "   pl, " +
         "   p.area, " +
         "   p.price " +
         "from" +
         "   PriceList pl," +
         "   PriceListRows p" +
         "where" +
         "   pl = :toPriceList" +
         "   and" +
         "   p.priceList = :fromPriceList";
      session.createQuery( hql )
         .setEntity("fromPriceList", fromPriceList)
         .setEntity("toPriceList", toPriceList)
         .executeUpdate();

Don't forget to rate.

marco-p · **Posted:** Thu Aug 17, 2006 6:17 am

Did you tried it? Just to be sure, I'm still getting the 'could not resolve property' error...

pepelnm · **Posted:** Thu Aug 17, 2006 6:18 am

Are you using the id or the entity?

marco-p · **Posted:** Thu Aug 17, 2006 7:04 am

My fault:
in the 'insert into' I wrote (priceList.id), but descendants cannot be used there (the error message ca be much more clear, IMHO).

I corrected the error and then the parser barks that distinct is not recognized. Ok, thrown away (I shouldn't need it) and then I get an Oracle error (ORA-00918: column ambiguously defined).

Lookin' at the generated SQL I found that the HQL condition 'pl = :toPriceList' is translated into 'PRICELIST_ID=?' without table name! Both PriceList and PriceListRows physical table have a column with such name, so here's the error.

I don't think it's correct behaviour...

pepelnm · **Posted:** Thu Aug 17, 2006 8:15 am

What if you simply change:?

From:

Code:

" pl = :toPriceList" +

To:

Code:

" pl.id = :toPriceListId" +

And from:

Code:

.setEntity("toPriceList", toPriceList)

To:

Code:

.setLong("toPriceListId", toPriceListId)

marco-p · **Posted:** Thu Aug 17, 2006 10:40 am

Smart guy! :)
it works, thank you!

I'd like to have a feedback from Hibernate developers on the ambiguos column issue, I think this should not happen.
Another thing, if I try to use a literal on the select list in a HQL query using an alias, a NPE is thrown.

singalen · **Joined:** Mon Aug 31, 2009 11:28 am **Posts:** 2

Got the same problem that can't be resolved with that trick, when the explicit value field doesn't exist in source table.

This gives the same error in 3.3.1.
Query would look like:

Code:

INSERT INTO PriceListRowDigest( priceListId, areaId, price, digestType )
SELECT priceListId, areaId, price, :digestTypeParam as digestType
FROM PriceListRow WHERE ...

Anyone can give an advice?

(fails with "number of select types did not match those for insert")

nvenky · **Posted:** Wed Oct 21, 2009 3:29 am

Hi,

Great solution. But we have a requirement to populate a Boolean column to "True" by default.

Code:

insert into X (user, createDate, ratingDate, booleanField)
   select user, systimestamp(), systimestamp(), true
   from user.....

We face the same issue of

Quote:

org.hibernate.QueryException: number of select types did not match those for insert

Is there a way to solve this issue?

singalen · **Joined:** Mon Aug 31, 2009 11:28 am **Posts:** 2

nvenky wrote:

But we have a requirement to populate a Boolean column to "True" by default.

That's exactly what I said. No, sorry.
I ended up not setting the field (inserting default values), and setting them with a separate statement, in a transaction, like "update object set flag = true where flag is null".
If you can afford this, try it. You'll have to drop not-null constraint, though.

nyc3 · **Joined:** Mon Jun 06, 2011 2:41 am **Posts:** 1

Quote:

org.hibernate.QueryException: number of select types did not match those for insert

I was able to workaround this exception by using the substring function:

Code:

INSERT INTO ... (p1, p2) SELECT o.p1, SUBSTRING(:p2, 1, LENGTH(:p2)) FROM ... o WHERE ...

In general, you could also use:

Code:

INSERT INTO ... (p1, p2) SELECT o.p1, CAST(:p2 AS <hibernateType>) FROM ... o WHERE ...

<hibernateType> would be string in this case or boolean for boolean literals, etc.

Hope that helps!