title: Hibernate combine multiple queries
body: <p>lets say I have following database structure:</p>
  
  <p><a href="https://i.stack.imgur.com/vWmpJ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vWmpJ.png" alt="enter image description here"></a></p>
  
  <p>In red, you can see TABLES, and in black FIELDS</p>
  
  <p><strong>Structure</strong>(s) are linked to <strong>LocationType</strong> using <strong>StructureLocationType</strong></p>
  
  <p>Now I need to get a list of Structure(s) that belong to LocationType:</p>
  
  <pre><code>    // get LocationType
      LocationTypeEntity locationTypeEntity = databaseManager.selectLocationType(session, locationTypeID);
  
      // get list of StructureLocationType(s)
      List&lt;StructureLocationTypeEntity&gt; structureLocationTypeEntities = databaseManager.selectStructureLocationTypes(session, locationTypeID);
  
      // get list of Structures(s)
      List&lt;StructureEntity&gt; structures = new ArrayList&lt;&gt;();
      for (StructureLocationTypeEntity structure: structureLocationTypeEntities)
      {
          structures.add(databaseManager.selectStructure(session, structure.getStructureId()));
      }
      return structures;
  </code></pre>
  
  <p>My helper methods to retrieve data using hibernate:</p>
  
  <pre><code>public LocationTypeEntity selectLocationType(Session session, int id)
  {
      session.beginTransaction();
      LocationTypeEntity locationTypeEntity = session.get(LocationTypeEntity.class, id);
      session.getTransaction().commit();
      return locationTypeEntity;
  }
  
  public List&lt;StructureLocationTypeEntity&gt; selectStructureLocationTypes(Session session, int locationTypeId)
  {
      session.beginTransaction();
      CriteriaBuilder builder = session.getCriteriaBuilder();
      CriteriaQuery&lt;StructureLocationTypeEntity&gt; query = builder.createQuery(StructureLocationTypeEntity.class);
      Root&lt;StructureLocationTypeEntity&gt; root = query.from(StructureLocationTypeEntity.class);
      query.select(root).where(builder.equal(root.get("locationTypeId"), locationTypeId));
      Query&lt;StructureLocationTypeEntity&gt; q = session.createQuery(query);
      List&lt;StructureLocationTypeEntity&gt; 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;
  }
  </code></pre>
  
  <p>So it already seems ineffective, but assuming there were 3 <strong>Structures</strong> linked with <strong>LocationType</strong>, 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)</p>
  
  <p>Thank you.</p>