java - Hibernate 组合多个查询

Question

假设我有以下数据库结构:

红色的是表格，黑色的是字段

Structure(s) 使用 StructureLocationType

链接到 LocationType

现在我需要获取属于 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;

我使用 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;
}

所以它看起来已经无效了，但是假设有 3 个Structures 与 LocationType 相关联，则需要大约 1200 毫秒才能获取结构列表。我正在使用它进行自动化测试，所以理论上它确实需要光速，但我相信我需要改进它，如果有人可以帮助我改进我的代码以便可以通过单个查询执行它，我将不胜感激？ (现在显然它向数据库发送了多个查询)

谢谢。

Answer 1

找到了一个解决方案，其实很简单(没想到)

使用 JOIN 关键字并执行单个 native 查询:

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();

在这种情况下，“1080”是我的 LocationType id。