I'm attempting to do a batch insert from a query using HQL. However, I'm not getting the results I expect. Here's the HQL query I'm attempting to use:
Code:
return session.createQuery("insert into ListingUrl (campaignUrl, listingTarget) " +
"select c, t " +
"from CampaignUrl as c, ListingTarget as t " +
"where c.id in (:campaignIds) " +
"and t.listingUid in (:listingUids) " +
"and not exists (" +
" select 1 " +
" from ListingUrl u " +
" where u.listingTarget = t " +
" and u.campaignUrl = c)")
.setParameterList("campaignIds", campaignIds)
.setParameterList("listingUids", listingUids)
.executeUpdate();
This generates the following SQL (HSQLDB dialect)
Code:
insert into listing_url ( id, campaign_url_id, listing_uid )
select next value for lurl$listing_url_id$seq, campaignur0_.id as col_0_0_, listingtar1_.listing_uid as col_1_0_
from campaign_url campaignur0_, listing_target listingtar1_
where (campaignur0_.id in (? , ?)) and (listingtar1_.listing_uid in (? , ? , ?))
and not (exists (
select 1
from listing_url listingurl2_
where listingurl2_.listing_uid=listing_uid
and listingurl2_.campaign_url_id=id))
Notice that the subquery is missing the aliases for campaignur0_ and listingtar1_. The WHERE clause in the subquery should read:
Code:
where listingurl2_.listing_uid=listingtar1_.listing_uid
and listingurl2_.campaign_url_id=campaignur0_.id
If I execute the SELECT statement by itself (e.g. without the insert), it returns the results I would expect. And if I manually run the SQL with the aliases added, the correct results are returned.
I couldn't find anything in the documentation that would explain this behavior. Any reason the SQL generated for this insert is different than when running the SELECT by itself? Perhaps I'm missing something in the HQL?
Any help would be appreciated!
David
Login
FAQ
Search