• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Devaka Cooray
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Knute Snortum
  • Bear Bibeault
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Piet Souris
  • Ganesh Patekar
Bartenders:
  • Frits Walraven
  • Carey Brown
  • Tim Holloway

How to add List<String> stream results to StringBuffer for use in a Hibernate query?

 
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have the following method:



Occasionally the list of rosterBadges will get passed into this method that exeeds 1000, hence the Stream/loop workaround trick I've come up with (Oracle doesn't allow you to have more than 1000 elements for an IN clause).   When I System.out.println what the loop generates, it looks good -- batches of 1000 badges with OR IN in-between each. Good. But when I actually run the code, I get the following error:



I know you're supposed to use setParameter to dynamically set parameters within a Hibernate query, but the way I have this set-up, I don't see how I can have that method available during the loop, though, since I'm dynamically piecing the query together, and then doing the setParameter()s at the end, and then running the query. Is it a matter of using something different than StringBuffer? Is there possibly another method like setParameter I could use to get it to "read in" that value correctly?  Maybe I'm misunderstanding what the Stream is returning?

Any help greatly appreciated! Happy Friday folks...

 
Ranch Foreman
Posts: 3268
20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The code you're looking at is only activated when you have > 1000 badges.  I'd suggest temporarily replacing the 1000 with something much smaller, like 3, to make it possible to test this with something you can easily see and debug.  Perhaps each time you see 1000, it should be a constant like LIST_CHUNK_SIZE, so you can easily change it to something else, in multiple locations at once.

Looking at the error messge, I see the SQL it's complaining about seems to be

Would that be valid SQL in whatever system you're using?  Normally I'd expect it should look more like


It looks like you're relying on the the toString() implemenation of a List, which is not matching SQL syntax.  Try something along these lines:


 
Bartender
Posts: 20842
125
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
While your attempt to do it all in a single SQL request is laudable, I think that the parameter set for the query (rosterBadges) is a bit large for that approach (and apparently so does Oracle).

I think that instead, you might just set up a Java loop to enumerate rosterBadges, query for one rosterBadge per loop pass and build up your return list item by item from the results of the loop passes (via list appending). You can tune this a bit I think by pre-allocating the result List size to the size of rosterBadges so that the system doesn't have to repeatedly re-allocate space for result list elements.

That should make your code much simpler and probably faster (or at least you'll start getting results sooner!). If that's not performant enough, then you could consider doing iy in an Oracle Stored Procedure, but stored procedures are not something I recommend when you can avoid them.
 
Rancher
Posts: 4117
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There's a couple of things I'm not sure generate correct HQL syntax:



As mentioned, are the square brackets correct (I don't think they are)?
Also, the <column> IN (...) OR IN (...)
Shouldn't that be <column> IN (...) OR <column> IN (...) ?
 
Tim Holloway
Bartender
Posts: 20842
125
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually, while I've never really used the original Hibernate, you rarely do a join in Hibernate JPA's PSQL query language because JPA does that part automatically when you relate 2 Entitys. I'm pretty sure that pre-JPA Hibernate worked that way, too.
 
Dave Tolls
Rancher
Posts: 4117
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Doesn't a 'join fetch' ensure that the thing you're joining to is brought back as well, rather than when the relevant field is accessed?
I'm sure that's what I've seen used in cases where the entity will end up detached.
 
Fred Maurice
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Mike Simmons wrote:The code you're looking at is only activated when you have > 1000 badges.  I'd suggest temporarily replacing the 1000 with something much smaller, like 3, to make it possible to test this with something you can easily see and debug.  Perhaps each time you see 1000, it should be a constant like LIST_CHUNK_SIZE, so you can easily change it to something else, in multiple locations at once.

Looking at the error messge, I see the SQL it's complaining about seems to be

Would that be valid SQL in whatever system you're using?  Normally I'd expect it should look more like


It looks like you're relying on the the toString() implemenation of a List, which is not matching SQL syntax.  Try something along these lines:




Thanks a ton Mike (and everyone else). Collectors.joining was definitely what I was looking for.  However, now with this updated code, I'm still getting this error:




Revised code:




I thought that would be the correct format for the IN clause and it seems to be when I look at JBoss documentation.  What am I missing now?
 
Tim Holloway
Bartender
Posts: 20842
125
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:Doesn't a 'join fetch' ensure that the thing you're joining to is brought back as well, rather than when the relevant field is accessed?
I'm sure that's what I've seen used in cases where the entity will end up detached.



Yeah, I guess so. I usually force the fetch myself. But I'm not sure join fetch for Entities was available in JPA v1 so old habits die hard and when I see "join" it generally means someone is trying to code JP/HQL like it's raw SQL. So, sorry about that.
 
Dave Tolls
Rancher
Posts: 4117
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Fred Maurice wrote:
I thought that would be the correct format for the IN clause and it seems to be when I look at JBoss documentation.  What am I missing now?



Since the error is apparently at column 10 thousand and something, now is the time to take Mike's suggestion earlier and use a much smaller grouping (he suggested 3) and some small test data for your List.
That way you can actually look at the created query, and post it here.

I still think your OR IN is incorrect.
 
Fred Maurice
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:

Fred Maurice wrote:
I thought that would be the correct format for the IN clause and it seems to be when I look at JBoss documentation.  What am I missing now?



Since the error is apparently at column 10 thousand and something, now is the time to take Mike's suggestion earlier and use a much smaller grouping (he suggested 3) and some small test data for your List.
That way you can actually look at the created query, and post it here.

I still think your OR IN is incorrect.




OK, when I limit to 3, here's the kind of code it generates:




If "OR IN" is incorrect, I don't know what else I'd use?
 
Dave Tolls
Rancher
Posts: 4117
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, it is the first OR IN it's complaining about.
That's because it is expecting a column name or similar between them, as I said earlier.

tar.person_id IN (...) OR tar.person_id IN (...)
 
Fred Maurice
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:Well, it is the first OR IN it's complaining about.
That's because it is expecting a column name or similar between them, as I said earlier.

tar.person_id IN (...) OR tar.person_id IN (...)




Dave,

Apologies for not reading your previous comment thoroughly enough.  That was it!  Now I have some other bugs to solve, but this thread was extremely helpful for getting this piece right.  Thanks everyone!
 
Consider Paul's rocket mass heater.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!