Hibernate combine multiple queries

Question

lets say I have following database structure:

In red, you can see TABLES, and in black FIELDS

Structure(s) are linked to LocationType using StructureLocationType

Now I need to get a list of Structure(s) that belong to LocationType:

    // get LocationType
    LocationTypeEntity locationTypeEntity = databaseManager.selectLocationType(session, locationTypeID);

    // get list of StructureLocationType(s)
    List<StructureLocationTypeEntity> structureLocationTypeEntities = databaseManager.selectStructureLocationTypes(session, locationTypeID);

    // get list of Structures(s)
    List<StructureEntity> structures = new ArrayList<>();
    for (StructureLocationTypeEntity structure: structureLocationTypeEntities)
    {
        structures.add(databaseManager.selectStructure(session, structure.getStructureId()));
    }
    return structures;

My helper methods to retrieve data using hibernate:

public LocationTypeEntity selectLocationType(Session session, int id)
{
    session.beginTransaction();
    LocationTypeEntity locationTypeEntity = session.get(LocationTypeEntity.class, id);
    session.getTransaction().commit();
    return locationTypeEntity;
}

public List<StructureLocationTypeEntity> selectStructureLocationTypes(Session session, int locationTypeId)
{
    session.beginTransaction();
    CriteriaBuilder builder = session.getCriteriaBuilder();
    CriteriaQuery<StructureLocationTypeEntity> query = builder.createQuery(StructureLocationTypeEntity.class);
    Root<StructureLocationTypeEntity> root = query.from(StructureLocationTypeEntity.class);
    query.select(root).where(builder.equal(root.get("locationTypeId"), locationTypeId));
    Query<StructureLocationTypeEntity> q = session.createQuery(query);
    List<StructureLocationTypeEntity> locationTypeEntities = q.getResultList();
    session.getTransaction().commit();
    return locationTypeEntities;
}

public StructureEntity selectStructure(Session session, int structureID)
{
    session.beginTransaction();
    StructureEntity structure = session.get(StructureEntity.class, structureID);
    session.getTransaction().commit();
    return structure;
}

So it already seems ineffective, but assuming there were 3 Structures linked with LocationType, it takes ~1200ms to get list of Structures. I am using it for automation testing, so in theory it does need to be speed of light, but I believe I need to improve it, would be grateful if someone can help me to improve my code to maybe execute it with single query ? (now obviously it sends multiple queries to the database)

Thank you.

"In red, you can see TABLES, and in black FIELDS" - so LocationType is a field? ;) — Thomas
– Thomas, Commented Nov 21, 2018 at 13:23
Sorry my mistake, LocationType is table as well, and locationTypeId is field, will fix the image. — Matthewek
– Matthewek, Commented Nov 21, 2018 at 13:24
I'd suggest you pass multiple ids to load a bunch of entities at once and add some fetch criteria to fetch the associated entities in the same query. However, if you load the entities as batches (i.e. 3 structures and then all their location types etc.) then you should already get a tremendous speedup when going from many single queries to just 3 (or maybe some more if the results get too big and you're running into memory problems). You might further consider using only one transaction for all reads or ditch JPA transactions completely if the data is meant to be readonly. — Thomas
– Thomas, Commented Nov 21, 2018 at 13:25

Matthewek · Accepted Answer · 2018-11-22 08:21:37Z

0

Found a solution, quite simple in fact (was not aware of it)

using JOIN keyword and executing single native query:

Query q = session.createNativeQuery("Select *, StatusId \n" +
        "FROM dbo.Structure AS S\n" +
        "JOIN dbo.StructureLocationType AS SLT ON SLT.StructureId = S.Id\n" +
        "WHERE SLT.LocationTypeId = 1080").addEntity(StructureEntity.class);

List<StructureEntity> zones2 = q.list();

Where in this case '1080' was my LocationType id.

answered Nov 22, 2018 at 8:21

Matthewek

1,5692 gold badges24 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Hibernate combine multiple queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related