Hibernate: Getting a paged list of distinct results in join… a series of unfortunate events

Tables hierarchy:
Parent, ChildFirstLevel (Parent's child), and ChildSecondLevelSet (ChildFirstLevel's child)

In the system I’m working on, there are two very similar queries:
-Query 1: filter Parents (filter criteria: some fields of Parent) and display results in a paging list
-Query 2: same filtering of Parents, but it’s required to retrieve parents and their children in all levels. No paging.

Problem #0: Hibernate and returning distinct results from joins

See: https://www.hibernate.org/117.html#A3: Hibernate does not return distinct results for a query with outer join fetching enabled for a collection (even if I use the distinct keyword)?
Query 2 joins parents with children, so to overcome this I used

setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)

to remove join duplicates, which was no issue since Query 2 does not require paging.

Problem #1: Paging and Criteria APIs

We then got a change request to add some ChildFirstLevel fields to the filter criteria. This required editing Query 1 to join Parent and ChildFirstLevel tables, which caused duplicates to return in the result set. The solutions in https://www.hibernate.org/117.html#A3 to remove duplicates didn’t work because they all remove duplicates in-memory (after executing the SQL on the database and retrieving the results), which messed up the paging.

Solution:
To keep using Criteria APIs, the solution was to use Projections.distinct(), which translates to select distinct in SQL.
Projections.distinct() will cause the query to only return the properties in the projection, since it constructs the select clause (with the distinct keyword). So we will need to use AliasToBeanResultTransformer to construct Parent objects from the result set, which will have the individual fields of Parent.

See: https://forum.hibernate.org/viewtopic.php?t=941669 Post: one of the solutions.

Problem #2: Projections.distinct() and listing all properties in the query

Parent table is huge. We don’t want to list all the fields. You want to generate their properties names.

Solution:
This can be done using getClassMetadata(Parent.class).getPropertyNames().

Problem #3: Projections.distinct() and component beans

Parent class has many component beans, so we will need recursion in generating the property names (to generate the properties inside the components). Otherwise, the query will contain component names that SQL does not recognize. Here’s the messy code (based on http://www.agitar.com/openquality/hibernate2-2.1.8/net/sf/hibernate/expression/Example.java.html):

private ProjectionList addPropNamesToPrjList(ProjectionList prjList, String componentName, String[] propNames, Type[] propTypes) {
for (int i = 0; i <>
if (propTypes[i].isComponentType()) {
String[] subNames = ((AbstractComponentType)propTypes[i]).getPropertyNames();
Type[] subTypes = ((AbstractComponentType)propTypes[i]).getSubtypes();
String newComponentName = "".equals(componentName) ? propNames[i] : (componentName + "." + propNames[i]);
addPropNamesToPrjList(prjList, newComponentName, subNames, subTypes);
} else {
String propName = "".equals(componentName) ? propNames[i] : (componentName + "." + propNames[i]);
prjList.add(Projections.property(propName), propName);
}
}

return prjList;
}

/** This medhod constructs a ProjectionaList containing the given class' properties
* Supports comoponent properties using recursion
*/
protected ProjectionList constructParentPropsProjList(Session session, Class classObj) {
String[] propNames = session.getSessionFactory().getClassMetadata(classObj).getPropertyNames();
Type[] propTypes = session.getSessionFactory().getClassMetadata(classObj).getPropertyTypes();
ProjectionList prjList = Projections.projectionList();

prjList = addPropNamesToPrjList(prjList, "", propNames, propTypes);
return prjList;
}

The above code does construct correct running SQL, but then you get a Hibernate error when Hibernate attempts to construct Parent objects from the result:
Could not set property component.x on Parent.
No solution was found to this. Apparently, Hibernate is unable to construct the component beans from the properties we had such a hard injecting into the query.

A tweak to the above code: Not using an alias
prjList.add(Projections.property(propName));
Returns empty objects.

Solution:
Use HQL to be able to insert the distinct keyword directly in a query that would be both humane and working.

Problem #4: HQL and lazy initialization

The HQL query is something like:
select distinct prnt -- or select count(distinct prnt.id) if you want
from Parent prnt
left join prnt.childFirstLevelSet firstLevel
left join firstLevel.childSecondLevelSet
where … (conditions on Parent and ChildFirstLevel)

This does generate correct running SQL. But unfortunately, it does not work when the conditions are on ChildFirstLevel. It gives:
Org.hibernate.LazyInitializationException: failed to lazily initialize a collection of role: Parent.childFirstLevelSet, no session or session was closed

This seems to be related to the following
https://forum.hibernate.org/viewtopic.php?f=1&t=960269&start=0
that quotes Hibernate in Action (page 261):
Hibernate currently limits you to fetching just one collection eagerly. This is a reasonable restriction, since fetching more than one collection in a single
query would be a Cartesian product result.
Attempts to use with and fetch all keywords came to nothing.

Solution:
Luckily, throughout the system ChildFirstLevel is always retrieved with its children. So changing its children’s mapping by adding lazy="false" solved the problem. No need for the join statements (except the first one) that were causing wrong results. So the query would be:
select distinct prnt
from Parent prnt
left join prnt.childFirstLevelSet firstLevel
where … (conditions on Parent and ChildFirstLevel)

Problem #5: fetch and distinct results

For Query 2, we need to fetch the children so the query should be
select distinct prnt
from Parent prnt
left join fetch prnt.childFirstLevelSet firstLevel
where … (conditions on Parent and ChildFirstLevel)

Adding fetch caused the results to duplicate (again).

Solution:
That was easy, since Query 2 does not require paging. We can just throw the results into a HashSet.

Problem #6: Retrieving filtered children only (and not other children in same parent)

Query 2: We now have a working query that returns distinct results filtered by conditions on both the parent and child tables. The problem is that since we filter by ChildFirstLevel fields, only objects that meet the filter criteria will be retrieved. If the parent has two children and only one meets the filtering criteria, only one child will be retrieved.

Solution:
This required a tweak to the query:

select distinct prnt
from Parent prnt
left join fetch prnt.childFirstLevelSet firstLevel
where … (conditions on Parent and ChildFirstLevel)
-- repeat query to get all parent’s children
or prnt.id = firstLevel.parent.id
and prnt.id in
(select prnt.id from Parent prnt
left join prnt.childFirstLevelSet firstLevel
where … (conditions on Parent and ChildFirstLevel))

World Financial crisis... root cause, anybody?

This is a post I've been wanting to write a long time ago. A few months before the current world financial crisis.

Money creation

The process by which banks create money is so simple that the mind is repelled -John Kenneth Galbraith, Economist

Now let's see this video: Money As Debt (or this link with Arabic subtitles); a must see before you do any bank transaction (at least watch the first 20 minutes).

The video explains how Money today -instead of representing value like in the past- represents Debt. It shows how money is 'created'. In the past, for more gold/siver money to be created, more gold/silver had to be dug out. But in the present, -simplifing a complex operation- banks create money (out of no where) whenever anyone signs a loan from the bank. A loan is a promise to repay from the loaner's side, allowing the bank to conjure into existance the amount of the loan. The bank then collects interest on this amount. That's just it. The bank does not have the money it lends out. The system works because not everyone go remove their money at the same time (called a run on the bank), and because bank systems are like closed loops; money loaned from one bank is deposited in another.

95% of the money in our world is created by banks. This means that almost all of the money we deal with is fictional. It does not really exist.

I'm afraid that the ordinary citizen will not like to be told that banks can and do create money -Reginald McKenna, past Chairman of the Board, Midlands bank of England

Every single dollar in circulation is loaned into existence by a bank, with interest -Chris Martenson

Paying debts... Settling accounts?

One would be enticed to think that if all debts were paid, there'd be more money. Like in the case of personal loans. But the truth is: If all debts (of governments, businesses, and individualts) were paid off, there'd be NO MONEY. So our economies are totally dependant on this money creation process.

If the value of loans is P, then we need P + I money to repay the debts with interest (where I is the Interest value). But knowing that the amount of money in existance is P (because nearly all money is created by loans), where can we get the money to repay the interest I?

The answer is we need to create more money/debt. The system needs debt to continously expand just to function (have an acceptable percentage of foreclosures).

One thing to realize about our fractional reserve banking system is that like a child's game of musical chairs, as long as the music is playing, there are no losers -Andrew Gause, Monetary historian

But will the music stop?

We have seen how the money supply keeps expanding, but since the volume of production and trade does not grow at the same rate, we get this terrible monster called Inflation.

Our monetary system has to infinitely keep growing the amount of money/debt. But this exponential increase can't go on for ever.

Let's leave it to the scientists to explain.

"The greatest shortcoming of the human race is our inability to understand the exponential function." This is the opening line of a talk I have given over 1500 times since 1969. In this context, the exponential function is used to give a quantitative description of steady growth of, for example, a population. As we all know, quantities that grow steadily, at even modest rates, quickly become impossibly large. Yet non-scientists in the business and government communities continue to fight for "sustainable growth" of the U.S. economy and population. What are scientists doing to increase public comprehension of the impossibility of "sustainable growth?" The main role of scientists seems to be to avoid calling attention to the impossibility of continued growth of populations and of rates of consumption of resources and, instead, to focus on minor aspects of the related problems. In so doing, we are complicit in making the problems worse. For scientists, this opening line should be revised to read: "The greatest shortcoming of scientists is our unwillingness to apply our knowledge of the exponential function to the great problems that are facing the human race."

Anyone who believes exponential growth can go on forever in a finite world is either a madman or an economist -Kenneth E. Boulding, Economist

So instead of asking Will the music stop; we should have asked When will the music stop?

Current crisis

The current crisis that started in the U.S. ows its roots to the fact that banks became too lenient in giving mortgage loans, which led to a high percentage of foreclosures. But does not the code read that this should be no problem because banks will just take over the mortgaged property? Well, given the large number of foreclosures (and real estate properties for sale), real estate prices plunged downs. Banks did not get back the expected values. Also, too many properties for sale meant slower sales. And banks became short of liquidity -unable to fulfill their commitment. Multiple runs on banks happened. The domino effect started.

You must have seen that coming, right? After knowing how the system works. The contemporary monetary system is built and compelled on Greed. Therefore, the needed hard-to-reach balance to cover the fictional money was doomed to failure. It was blind greed of bankers and borrowers that caused the current mortage crises. The greedy 'continous expansion' can never work (see section “But will the music stop?”).

I related to how tricks of loans and interest have been used to control nations in this interview with John Perkins, author of Confessions of an Economic Hit Man.

Now read this

“Allah obliterateth usury, and increaseth the alms. And Allah loveth not any ingrate sinner” The Quran: 'The Cow' chapter: verse 276

“Those who devour Usury Shall not be able to stand except standeth one whom the Satan hath confounded with his touch. That shall be because they say: bargaining is but as usury whereas Allah hath allowed bargaining and hath forbidden usury. Wherefore Unto whomsoever an exhortation cometh from his Lord, and he desisteth, his is that which is past, and his affair is with Allah. And whosoever returnoth --such shall be the fellows of the Fire, therein they shall be abiders” The Quran: 'The Cow' chapter: verse 275

You can't just leave like that

Chris Martenson puts it this way: But the end of something is always the beginning of something else. Where's our modern day Adam Smith? We need a new economic model.

This is a time when we could really use the concepts of Islamic Economy that thrived the economy of Muslims for hundreds of years in an area that stretched from Spain to the borders of China. It is worth mentioning that Musim traders (with their morals and fair dealings) carried Islam to countries that are now predominantely muslims like Indonesia.

While muslim scholares have yet to formulate (and then implement) these concepts in our modern world, here are some resources:

Eid al-Fitr 08 Khutbah (sermon) by Shaykh Hamza Yusuf [YouTube video that touches on the current crisis]

Understanding Islamic Finance (The Wiley Finance Series) by Muhammad Ayub [Book -link given on Amazon]

Islamic Finance in the Global Economy by Ibrahim Warde [Book -link given on Amazon]

نداء من الأقصى

هل تعلم أن

- المسجد الأقصى ليس مسجد قبة الصخرة أو الجامع القبلي ذو القبة الرصاصية؟

- حائط المبكى هو اسم يطلقه اليهود زورا على حائط البراق الذي ربط رسول الله صلى الله عليه و سلم البراق به يوم الإسراء؟

- الحفريات أسفل المسجد الأقصى تسببت في انهيار في ساحاته الداخلية؟

- تحرير كل فلسطين فرض عين علينا جميعا؟

- بإمكانك العمل الآن لنصرة المسجد؟

شاهد هذا العرض لتتعرف على المسجد الأقصى و حقه و جانب من الانتهاكات التي تعرض لها من 1967 إلى اليوم و ما تستطيع أن تفعله لنصرة الأقصى

خسوف القمر أية عظيمة... سبحان الله

أهو ربنا بيفكرك... لعلك تتعظ. حصل كسوف جزئي للقمر شوفناه في مصر يوم السبت 17-8-2008. آية عظيمة، ميغيرش من الحقيقة دي إننا عارفين الخسوف بيحصل إزاي و بنتوقعه

اللَّهُ الَّذِي رَفَعَ السَّمَاوَاتِ بِغَيْرِ عَمَدٍ تَرَوْنَهَا ثُمَّ اسْتَوَى عَلَى الْعَرْشِ وَسَخَّرَ الشَّمْسَ وَالْقَمَرَ كُلٌّ يَجْرِي لأَجَلٍ مُّسَمًّى يُدَبِّرُ الأَمْرَ يُفَصِّلُ الآيَاتِ لَعَلَّكُم بِلِقَاء رَبِّكُمْ تُوقِنُونَ
سورة الرعد-آية 2

Rachel Corie... we won't forget

On this day, 5 years ago, a flower was cruelly crushed by Israeli hands. That flower, Rachel Corie, makes us all seem small and selfish.
Rachel was a volunteer with the International Solidarity Movement in Palestine. She was trying to protect the house of a Palestinian family in Rafah from being demolished by the Israeli army, when an Israeli bulldozer mercilessly ran over her. Twice.⁽¹⁾

This is part of an email Rachel wrote to her parents from Palestine: "This has to stop. I think it is a good idea for us all to drop everything and devote our lives to making this stop. I don’t think it’s an extremist thing to do anymore. I really want to dance around to Pat Benatar and have boyfriends and make comics for my co-workers. But I also want this to stop. Disbelief and horror is what I feel. Disappointment. I am disappointed that this is the base reality of our world and that we, in fact, participate in it. This is not at all what I asked for when I came into this world. This is not at all what the people here asked for when they came into this world. This is not what they are asking for now. This is not the world you and Dad wanted me to come into when you decided to have me."⁽²⁾

Rachel Corie... we won't forget

--------------------
(1) For a full account of Rachel's murder, see The Moments Before Rachel Corrie's Murder http://www.ccmep.org/2003_articles/Palestine/032003_the_moments_before.htm
(2)http://www.partnersforpeace.org/pressreleases/db200303190/

راشيل كوري... لن ننسي

مر اليوم 5 أعوام على جريمة قتل راشيل كوري... وصمة العار الباقية على جبين العالم. كانت راشيل عضوة في في حركة التضامن الدولية. و في يوم مقتلها, كانت تحاول و مجموعة من النشطاء منع الجيش الإسرائيلي من هدم منزل عائلة فلسطينية في رفح، حين دهستها الجرافة الإسرائيلية و مرت فوق جسدها النحيل مرتين.⁽¹⁾

هذا جزء من رسالة إلكترونية كتبتها راشيل إلى والديها من فلسطين:
"ما يحدث يجب أن يتوقف. أظن أنها فكرة جيدة لنا جميعاً أن نترك كل شيء و نكرس حياتنا لإيقاف ما يحدث... إن شعوري هو شعور بعدم التصديق وبالفزع، وبالإحباط. إنه من المحبط أن يكون هذا هو الواقع العاري لعالمنا و أننا في الواقع نشترك فيه. ليس هذا على الإطلاق ما تمنيته حين جئت إلي هذا العالم. ليس هذا على الإطلاق ما تمناه الناس هنا حين جاؤوا إلي هذا العالم. ليس هذا ما يتمنوه الآن. ليس هذا هو العالم الذي أردتي أنت و أبي أن آتي إليه حين قررتما إنجابي."⁽²⁾

^{راشيل كوري... لن ننسي}

^{--------------------
1: http://www.ccmep.org/2003_articles/Palestine/032003_the_moments_before.htm
2: http://www.partnersforpeace.org/pressreleases/db200303190}

المسرى يستغيث

يقول الشيخ كمال خطيب نائب رئيس الحركة الإسلامية في الداخل الفلسطيني: "المرحلة الحالية والقادمة تمثل المرحلة المفصلية من واقع المسجد الأقصى وعلى كل الأمة اليوم مسؤولية التحرك السريع والجاد لإنقاذ المسجد الأقصى"

في ناس حتقول هو حصل إيه؟! حصل إن المسجد حيتهد و احنا نايمين على ودانّا. اتفرج على الفيديو ده

http://www.alaqsa-online.com/vedio/Before%20it%20is%20Destoryed_arb.wmv

اللّي مدته ربع ساعة اللّي عملته مؤسسة الأقصى لإعمار المقدسات الإسلامية. بيبيّن الدرجة المريعة اللّي وصلتلها الحفريّات الإسرائيلية تحت المسجد الأقصى المبارك... شبكة أنفاق واسعة و عميقة تحت المسجد الأقصى و في محيطه تشكل ما يشبه بناء مدينة تحتية تهويدية.

يقول الشيخ رائد صلاح رئيس الحركة الإسلامية في الداخل الفلسطيني في مؤتمر صحفي يوم 10/3/2008: "الحفريات أدت الى عدة انهيارات، الأول في ساحات الأقصى الداخلية قرب سبيل قايتباي، والثاني وقع قبل أسبوع عند حمام العين، والانهيار الثالث وقع لأحد أجزاء المسجد الأقصى وهو باب المغاربة، و الانهيار الرابع هو التصدعات التي أصابت عشرات البيوت المقدسية التي تقع على امتداد الحائط الغربي للمسجد الأقصى، والخامس الانهيارات في بيوت أهلنا في سلوان ومسجد سلوان.. وهناك انهيارات كثيرة أخرى".

يا ريت تركز في الدقيقة 13 اللّى فيها الانهيار في ساحات الأقصى الداخلية.

و هذا تسجيل للشيخ رائد صلاح يحذّر من المخاطر المحدقة بالمسجد الأقصى ويدعو إلى تكثيف الرباط فيه. اضغط الصورة للذهاب إلى رابط الموضوع للاستماع و التحميل