Yet another Oracle error. However, in this case it is accurately described.
Ran into this with HQL where clause something like:
where entity1.id = entity2.id
One of the id’s was numeric, the other was a string. Had made the silly assumption that PK’s would all be nice integers.
Our normal query execution path is:
1) change query to perform a select count on the query to get paging information established
2) load a page from the query
This works fine when you have something like “select distinct table.id” – just replace the from part of the query to be “select count(distinct table.id)”
Once in a while we have the case of wanting a multiple column distinct, e.g. “select distinct table1.id, table2.other,…” HQL doesn’t let you use multiple columns/objects in a select count distinct.
The first step is to reduce your query from objects as to atomic values. Instead of getting the Address object, just get the Address id. This will be some Integer or other native Java that does not require any materialization (and follow-on Hibernate querying to fulfill due to aggressive loading).
So, now your standard query looks like “select distinct c1, c2, c3, …” where everything being selected is a native Java type.
Here is the leap of faith: just load the entire result set into a List and get the size() of the list. Something like:
Integer size = query.list().size();
Assuming you are performing some limits on the query, say under 20K records I think we are looking at about 50K of space allocated for the List resultset. Further, the execution and transfer of data from the database to Java should be very quick (if not you probably have some work to do to optimize your query in the first place)
We ran across cases where a list (had correct size()) contained null objects on some of our queries. At first we just put in a work around to ignore nulls.
However, a new use case arrived that required an auxiliary table to be populated.
Looking into the issue, the new table had a composite key with several elements. It turns out when Hibernate is attempting to populate the object it verifies that each of the composite key elements is not null. If it finds one that is null then it returns a null object. The thinking is that a PK should never have a null.
Well, that’s nice rhetoric when you have a new system where you design from the ground up, including the database. My current environment is the opposite: massive database compiled with over 20 years of data and associated changes.
The solution is to remove any element from a composite key that may be null. In the particular case we were dealing with that was possible. I can imagine there are other cases where that may not be possible. It would then be pretty painful to massage the data to the form you need (not nulls) effect the change across the various applications using the data.
This is a wonderful error message that probably has nothing to do with right parenthesis. You have probably seen this as a result of some Hibernate coding and wondering how on earth you can have Hibernate generate code with missing parens!
The error really means you have an ORDER BY clause in a subquery. Oracle can not do this. The best you can achieve is the use of a WHERE ROWNUM=n clause in the subquery, but any ordering makes Oracle lose it’s mind.
There are theoretical reasons for this which won’t help you get your job done. Just have to live with the limitation in Oracle.
A problem came up the other day that a mapping file had a Where clause that did not appear to be taking effect. It should have only included records in a result set that had the specific settings in place.
Looking into the where clause there are caveats to it’s use: “specify an arbitrary SQL WHERE condition to be used when retrieving objects of this class”. Like most of Hibernate documentation, what does that mean? When does the Where clause come into effect?
There are several such questions floating around the internet. One good response from the Hibernate team was “The where clause is not used at all during association navigation.” Meaning if you are just doing normal object references the Where clause has no effect. You see there is no additional logic to the generated SQL. He did mention using a Filter later on.
A Filter is plunked in the middle of the mapping file. It looks just like a Where clause. It can be used per session. It is defined in the mapping and can be used in classes or collections as needed. It works the way you would expect the Where clause to work.
It seems like the Where clause is in there for some historical reason. It should be deprecated.
I have recently been working on a project looking for memory leaks. (BTW, I am extremely happy with the tools available in the JDK – jconsole, jmap, jhat). While poking around at some changes we could make in our code I kept finding quite a few Hibernate objects hanging around. One in particular was the number of logging objects (we log user actions in the db) that were in memory just for a login step. Examining the code there was no apparent flaw. Putting in some debugging statements showed a tremendous amount of logging objects being instantiated during the parent.getChildren() type of statement in the code that adds the logging event to the database table.
If you find the documentation somewhere (I have a personal nit that I think most of the Hibernate documentation is atrocious. I can NEVER find what I am looking for) you will see that the lazy attribute for a set has three choices:
false means load the collection when the primary object is loaded. I think everyone expected that.
true means wait until some code asks for a collection. Then load the entire set! I don’t think most people expected that.
extra means return a Hibernate interceptor for the collection to the caller and only load individual members of the set when explicitly called for. I think this is the one most people expect to have happen.
We have been running with Hibernate for over a year. Like everyone else we thought we were taking advantage of lazy loading of collections. We have code all over the application that looks like:
Child child = new Child()
child.setParent(parent)
parent.getChildren().add(child)
session.flush()
Of particular note is the parent.getChildren statement. This function call is intercepted by Hibernate and we thought would lazy load the collection. Our mapping for the set in the Parent mapping had no setting for lazy, as the documentation says that lazy is on by default.
This means in our code where we just wanted to add a logging event we were loading the entire logging event table every time we added a record to the table!
We have changed all of our one-to-many sets to use extra. So far we are guessing that the many-to-many sets will likely traverse every matching member so the default true setting is good.
Yes, I know, technically HQL doesn’t support derived tables or columns. However, you can count on the underlying SQL to do some of the work for you.
For example if you needed to order by any of 3 columns in left joined tables, depending on whether the join worked or not you could do something like:
select distinct t1,
case when t2.t2Id is null
then
(case when t3.t3Id is null
then t1.orderingColumn
else t3.orderingColumn
end)
else t2.orderingColumn
end
from table1 t1
left join t1.t2s t2
left join t1.t3s t3
order by 2, t1.description
This builds on allowing case in the select clause (thereby giving you your pseudo-derived column) and the result order number (2) which falls directly back to the underlying SQL.
This is a really ugly exception thrown by Oracle from the bowels of Hibernate. In my case I had just added a table to the mapping so I at least had some idea where to look.
Looking at the integer values in the table I went through and verified each as correct in the db, mapping, entity and sequence. No luck there.
Poking around the internet though told me the error probably had nothing to do with a LONG mapping and could be ANY size mapping on any field. It’s a generic sizing error.
Luckily the table I created only had a few fields so I was able to narrow done the problem pretty quickly. In my case a VARCHAR2(4000) was attempted to be stored with much more. So, the String in the Java entity had to be controlled to make sure it was small enough.
It’s not much, but thought I would pass the info along and maybe save you some time.
We have recently run across a couple of situations that are really batch operations. You know read N records of type Y or write M records of type Z, all within a nice Hibernate session/transaction of course.
If you do the brain-dead coding looping over calls to object.setX(), object.save(), you are making a round trip from your logic layer all the way to the database (including the cache) for every loop step.
There is also the slight problem that you may run out of memory with a large operation as all of this has to sit in the cache until flushed out.
Looking at some literature points to the Hibernate batch size, but others say this really doesn’t work.
Others are more extreme and point to using raw SQL to perform your batch.
Why aren’t there batch calls sitting in Hibernate? This is not a new persistence problem.
This is one of those errors that shouldn’t happen. However, due to lazy-loading Hibernate is smart enough to realize you have loaded an object once before during a session and does not attempt to do so again – even though you have deleted it during the session and it should not be a part of any collection anymore.
A workaround I have started to use is to set the version of objects I am deleting to -1 and the later in time when I am about to persist or merge an object I check to see if the version is still valid first. This includes objects I delete by hand and objects that may be about to be cascade deleted that I may later on attempt to persist/merge.
I am sure there is a smarter way to do this, but this works well.
One thing I was toying with was the error message that comes up for this exception usually mentions the object using the standard notation of <object>@<hashcode> where the hashcode that prints out is null. This is a little wierd as the hashcode that I have in my entities is a int not an Integer, so I can’t tell how to poke at a null int to get the same effect as my invalid version/deleted flag.
Click here to see if our profiles connect:
