efficiently loading collection of collections from database in hibernate

Question

I have an web application with hibernate which manages data in multiple languages. Currently basically every request generates a shower of select statements on the languagetranslations. The models are roughly as following:

Data <1-1> Placeholder <1-many> languageTranslation <many-1> language

If I query for all/many Dataobjects, I see lots of single selects which select one languageTranslation for the placeholder. The SQL I optimally would want to generate:

SELECT * FROM data join placeholder join languagetranslation 
WHERE data.placeholder_id = placeholder.id 
AND languagetranslation.placeholder_id = placeholder.id 
AND languagetranslation.language_id = ?

so that I get every data with placeholder with translation in one single call. The languagetranslations have an composite primary key of language_id and placeholder_id.

I have no HBM file, everything is managed with annotations. Modelcode (only relevant sections are shown):

@Entity
public class Data {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @OneToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL, optional = false)
    @Fetch(FetchMode.JOIN)
    private Placeholder content;
}

public class Placeholder { 

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @OneToMany(mappedBy = "primaryKey.placeholder", cascade = CascadeType.ALL, fetch = FetchType.EAGER, orphanRemoval = true)
    @Fetch(FetchMode.JOIN)
    private Set<LanguageTranslation> languageTranslations = new HashSet<>();
}

public class LanguageTranslation {

    @EmbeddedId
    private LanguageTranslationPK primaryKey = new LanguageTranslationPK();

    @Type(type = "org.hibernate.type.StringClobType")
    private String text;

}

@Embeddable
public class LanguageTranslationPK {

    @ManyToOne(fetch = FetchType.EAGER)
    @Fetch(FetchMode.JOIN)
    private TextPlaceholder textPlaceholder;

    @ManyToOne(fetch = FetchType.EAGER)
    @Fetch(FetchMode.JOIN)
    private Language language;
}

public class Language {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
}

I experimented with FetchType and FetchMode but couldn't generate the behavior I want, it always single selects for single languageTranslations.

I also tried multiple ways to query, criteria based, HQL, and raw SQL. My current raw SQL query is the following:

String sql_query = "select data.*, lt.* from Data as data join languagetranslation as lt on data.content_id = lt.textplaceholder_id";
Query q = getSession().createSQLQuery(sql_query).addEntity("data", Data.class).addJoin("data.content_id", "data.title").addJoin("lt", "data.content.languageTranslations").setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
return q.list();

Am I doing something generally wrong here? How can I convince hibernate to get all entities in one single database call? Or is there some other methods to improve performance in my case (e.g. batch selecting)?

What do you mean by "getting all entities in one single database call"? Can you manually simulate what kind of query you expect to receive and how do you imagine it should be translated to object model? — Andremoniy, Mar 28 '17 at 09:30
@Andremoniy I want all Data objects with initiliazed `data.content` and initialized `data.content.languageTranslations`. So basically I want the SQL that I showed but have hibernate recognise that I already loaded all Placeholders and LanguageTranslations for it and that it doesn't need to query for it anymore. — syntonym, Mar 28 '17 at 09:44

score 1 · Accepted Answer · answered Mar 28 '17 at 09:36

1

You may create proxy pojo which have your all entity variables with getter setter and constructor. then initialize this constructor in hibernate query so that you just get all needed data from database.

import com.proxy;
 class userProxy{
private string name;
private string password;
private string address;
private int pincode;
private byte[] profilePic;
private int age;

public userProxy(string name,string password){
  this.name = name;
  this.password = password;
}

//Getter and setter of all variable...
}

Then use this constructor to Hibernate query like

select new com.proxy.userProxy(user.name,user.password) from usertable

answered Mar 28 '17 at 09:36

Anurag Dadheech

629
1
5
14

But that would mean I have to write proxies for every class that I need to query from the database, right? I hoped that I could avoid that. Is there some way to let hibernate generate the proxies for me or to reuse the original entitites as proxies? – syntonym Mar 28 '17 at 09:52
you still have one more option to create constructor in entity class but i haven't tried yet. Please try once and please let me know if it works. – Anurag Dadheech Mar 28 '17 at 09:55
1

Yes I think this works with a new constructor but with the same objects. But it looks like one has to be careful to not trigger any eager loads etc. in which case writing a proxy like you did is probably less work and more straighforward. – syntonym Mar 28 '17 at 12:47
1

I'm now doing it the following way: bia [this question](https://stackoverflow.com/questions/2216547/converting-hibernate-proxy-to-real-object) I deactivate hibernate from intervening and then I can simply `obj.setProperty(property)`, the object will behave as a POJO. That way I don't even need to write constructors for all cases I want to support. That creates problem with `orphanRemovel=True`, but oh well, what gives. – syntonym Apr 03 '17 at 10:27

score 0 · Answer 2 · answered Mar 28 '17 at 09:35

0

Am I doing something generally wrong here?

No, you are not. That is how Hibernate works.

How can I convince hibernate to get all entities in one single database call

You have to use HQL or SQL query to do that. You do not need to have HBM file. It can be done through @NamedQueries / @NamedQuery annotation with list method.

There are many samples on Internet as example simple one:

http://www.mkyong.com/hibernate/hibernate-named-query-examples/

answered Mar 28 '17 at 09:35

Vadim

4,027
2
10
26

I tried various ways to query for that, but I couldn't get it to work. I edited my current raw SQL query into the question. I guess that version does something wrong with the addJoin and the names, but also tried HQL and always failed to initialize the collection of collections in one call. The link only seems to initiliaze a single collection, can you point me in the right direction how the HQL/SQL query should look like? – syntonym Mar 28 '17 at 09:50
For that you may need to reorganize your Entities. Probably by using Hibernate Components there, because in case of such query, You have to treat your query results as "one denormalized table" – Vadim Mar 28 '17 at 11:33
But to do that I do not only need to change some code but the complete architecture of the entities and with that also the database, right? The application is already in use and while your solution seems to be the cleanest way to do that in hibernate, currently I cannot expense too much time and effort to rewrite so much. – syntonym Mar 28 '17 at 13:38
Not necessary and not Database at all. Maybe You can differentiate structures for existing and additional for your queries. I mean something like separate Search/Select structures from CRUD structures. But even with CRUD there is an overhead how Hibernate handles one-to-many collections. Sometime I had to replace pure Hibernate to bulk statement blocks and that why now I prefer to do not use Hibernate at all. – Vadim Mar 28 '17 at 15:59

efficiently loading collection of collections from database in hibernate

2 Answers2