javaspring-boothibernateormbackend

Hibernate N + 1 queries after explictly setting FetchType.LAZY


Being very concise:

I have the following use case for my app, where I update an entity named Turno (which represents a shift in a factory)

@Service
public class PausarTurnoUseCase {
    private final ITurnoRepository turnoRepository;
    private final ITurnoMapper turnoMapper;

    public PausarTurnoUseCase(ITurnoRepository turnoRepository, ITurnoMapper turnoMapper) {
        this.turnoRepository = turnoRepository;
        this.turnoMapper = turnoMapper;
    }

    @Transactional
    public Result<ShowTurnoDto> execute(UUID turnoId) {
        try {
            Optional<Turno> turnoOpt = turnoRepository.findById(turnoId);
            if (turnoOpt.isEmpty()) {
                return Result.failure("El turno no existe");
            }
            
            Turno turno = turnoOpt.get();
            
            if (turno.getEstado() == Estado.PAUSADO && turno.getFechaFin() == null) {
                return Result.failure("El turno ya está pausado");
            }
            
            turno.pause(); 
            
            Turno savedTurno = turnoRepository.save(turno);
            
            ShowTurnoDto turnoDto = turnoMapper.toShowTurnoDto(savedTurno);
            return Result.success(turnoDto, "Turno pausado con éxito");
        } catch (Exception e) {
            return Result.failure("Error al pausar el turno: " + e.getMessage());
        }
    }
} 

These are my entities:

Turno (shift):

@Data
@Entity
@Table(name = "turnos")
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(callSuper = false, onlyExplicitlyIncluded = true)
public class Turno extends BaseEntity<UUID> {
    @Id
    @EqualsAndHashCode.Include
    @GeneratedValue(strategy = GenerationType.UUID)
    @Column(name = "turno_id", updatable = false, nullable = false)
    private UUID turnoId;
    
    @ManyToOne
    @JoinColumn(name = "mesa_id", nullable = true)
    private Mesa mesa;
    
    @Column(name = "fecha_inicio", nullable = false)
    private LocalDateTime fechaInicio;
    
    @Column(name = "fecha_fin")
    private LocalDateTime fechaFin;
    
    @Enumerated(EnumType.STRING)
    @Column(name = "estado", nullable = false)
    private Estado estado = Estado.ACTIVO;

    @Column(name = "etapa", nullable = false)
    private Etapa etapa;
    
    @ManyToMany(fetch = FetchType.LAZY )
    @JoinTable(
        name = "turnos_usuarios",
        joinColumns = @JoinColumn(name = "turno_id"),
        inverseJoinColumns = @JoinColumn(name = "usuario_id")
    )
    private Set<Usuario> usuarios = new HashSet<>();
} 

Usuario (user):

@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "USUARIOS")
public class Usuario extends BaseEntity<UUID> {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    @Column(name = "USUARIO_ID", updatable = false, nullable = false)
    private UUID usuarioId;

    @Column(name = "NOMBRE_USUARIO", nullable = false, unique = true, length = 50)
    private String nombreUsuario;

    @Column(name = "NOMBRES", nullable = false, length = 30)
    private String nombres;

    @Column(name = "APELLIDOS", nullable = false, length = 30)
    private String apellidos;

    @Column(name = "CEDULA", length = 11)
    private String cedula;

    @Column(name = "PASSWORD_HASH", nullable = false)
    private String passwordHash;

    @Column(name = "FECHA_CREACION", nullable = false)
    private LocalDateTime fechaCreacion;

    @Column(name = "FECHA_VIGENCIA", nullable = false)
    private LocalDateTime fechaVigencia;

    @Column(name = "CORREO", unique = true, length = 100)
    private String correo;

    @Column(name = "ACTIVO", nullable = false)
    private boolean activo = false;

    @Column(name = "FOTO_PERFIL", length = 300)
    private String fotoPerfil;

    @Column(name = "CELULAR", length = 10)
    private String celular;

    @Column(name = "cambiar_contrasena", nullable = false)
    private boolean cambiarContrasena;

    @Column(name = "FECHA_ULTIMO_ACCESO", nullable = true)
    private LocalDateTime fechaUltimoAcceso;
}

I have worked with other ORMs and I don't think this should be a tricky case, I just need to retrieve the entity, update one of it's atributtes and then save and that's it, it should only take 1 select and 1 update, no?

Well, here's what Hibernate does:

Hibernate: select t1_0.turno_id,t1_0.estado,t1_0.etapa,t1_0.fecha_creacion,t1_0.fecha_fin,t1_0.fecha_inicio,m1_0.mesa_id,m1_0.capacidad_maxima,m1_0.numero_mesa,m1_0.tipo_extendido,t1_0.usuario_creador_id,uc1_0.usuario_id,uc1_0.activo,uc1_0.apellidos,uc1_0.cambiar_contrasena,uc1_0.cedula,uc1_0.celular,uc1_0.correo,uc1_0.fecha_creacion,uc1_0.fecha_ultimo_acceso,uc1_0.fecha_vigencia,uc1_0.foto_perfil,uc1_0.nombre_usuario,uc1_0.nombres,uc1_0.password_hash from turnos t1_0 left join mesas m1_0 on m1_0.mesa_id=t1_0.mesa_id join usuarios uc1_0 on uc1_0.usuario_id=t1_0.usuario_creador_id where t1_0.turno_id=?
Hibernate: select u1_0.turno_id,u1_1.usuario_id,u1_1.activo,u1_1.apellidos,u1_1.cambiar_contrasena,u1_1.cedula,u1_1.celular,u1_1.correo,u1_1.fecha_creacion,u1_1.fecha_ultimo_acceso,u1_1.fecha_vigencia,u1_1.foto_perfil,u1_1.nombre_usuario,u1_1.nombres,u1_1.password_hash from turnos_usuarios u1_0 join usuarios u1_1 on u1_1.usuario_id=u1_0.usuario_id where u1_0.turno_id=?
Hibernate: select ome1_0.usuario_id,ome1_0.orden_maestra_etapa_id,ome1_0.activo,ome1_0.estado,ome1_0.etapa,ome1_0.fecha_fin,ome1_0.fecha_inicio,om1_0.orden_maestra_id,om1_0.ajustar_tela,om1_0.ano_temporada,om1_0.aprobacion_consumo,om1_0.clase_orden,om1_0.codigo_marca,om1_0.coleccion,ed1_0.orden_maestra_id,ed1_0.direccion,ed1_0.muestra_fisica,ed1_0.plantillas,ed1_0.talla_base,om1_0.fecha_actualizacion,om1_0.fecha_creacion,om1_0.fecha_inicio_trazo,om1_0.justificacion_consumo,l1_0.linea_id,l1_0.nombre_linea,l1_0.numero_linea,l1_0.precio,l1_0.valor_auxiliar,om1_0.material_anterior,om1_0.material_generico,om1_0.metros_tela_principal,om1_0.nt_telas,om1_0.nombre_marca,odt1_0.orden_maestra_id,odt1_0.bloques,odt1_0.fecha_actualizacion,odt1_0.fecha_creacion,odt1_0.fecha_elaboracion,odt1_0.observaciones,odt1_0.sesgos_json,odt1_0.tipo_prenda,oe1_0.orden_maestra_id,oe1_0.fecha_ingreso_papeleria,oe1_0.fecha_ingreso_trazo,oe1_0.incluye_muestra_fisica,oe1_0.metros,oe1_0.numero_piezas,oe1_0.precio_pieza,oe1_0.responsable_ingreso_papeleria,t1_0.id_tercero,t1_0.activo,t1_0.codigo,t1_0.direccion,t1_0.email,t1_0.fecha_creacion,t1_0.identificacion,t1_0.nombre,t1_0.telefono,t1_0.tipo_identificacion,t1_0.tipo_tercero,oe1_0.ubicacion_tela,om1_0.promedio_diseno,om1_0.reproceso,s1_0.sam_id,l2_0.linea_id,l2_0.nombre_linea,l2_0.numero_linea,l2_0.precio,l2_0.valor_auxiliar,s1_0.metros,s1_0.tipo_tejido,om1_0.segmento_stock,om1_0.temporada,om1_0.tipo_orden,om1_0.tipo_prenda,om1_0.unidad_medida,u1_0.usuario_id,u1_0.activo,u1_0.apellidos,u1_0.cambiar_contrasena,u1_0.cedula,u1_0.celular,u1_0.correo,u1_0.fecha_creacion,u1_0.fecha_ultimo_acceso,u1_0.fecha_vigencia,u1_0.foto_perfil,u1_0.nombre_usuario,u1_0.nombres,u1_0.password_hash,om1_0.utilizacion,ome1_0.posicion,ome1_0.prioridad from ordenes_maestras_etapas ome1_0 left join ordenes_maestras om1_0 on om1_0.orden_maestra_id=ome1_0.orden_maestra_id left join espigas_detalles ed1_0 on om1_0.orden_maestra_id=ed1_0.orden_maestra_id left join lineas l1_0 on l1_0.linea_id=om1_0.linea_id left join ordenes_de_trazo odt1_0 on om1_0.orden_maestra_id=odt1_0.orden_maestra_id left join ordenes_maestras_extendidas oe1_0 on om1_0.orden_maestra_id=oe1_0.orden_maestra_id left join terceros t1_0 on t1_0.id_tercero=oe1_0.id_tercero left join sams s1_0 on s1_0.sam_id=om1_0.sam_id left join lineas l2_0 on l2_0.linea_id=s1_0.linea_id left join usuarios u1_0 on u1_0.usuario_id=om1_0.usuario_id where ome1_0.usuario_id=?
[Skipped another 13 super long, useless queries]

I kind of understand that it is loading all the related entities, since Turno is related to Usuario, and Usuario is related to many other entities, but I have explicitly set all Usuario relations to be FetchType.LAZY, same with the Turno-Usuario (Shift-User) relation.

Is there a simple way to fix this? I think this is getting out of hand...

I've worked with .NET and never had this kind of issue with EFC (Microsoft's ORM) since to access related entities I had to explicitly use .Include(e => e.Relation), but in SpringBoot this seems to be happening every time, with every entity even after I declare the fetch type to be lazy.

I tried configuring FetchType.LAZY, using DTO and interface projections (but not a real fan of how projections work on SpringBoot to be honest)

I have read many others solutions for this problem, but none have solved it for me, am I missing something?


Solution

  • I believe the culprit here is not Hibernate but Lombok, lombok @Data annotation, which generates an unnecessary toString() and @EqualsAndHashCode and these methods use relational data. To resolve this, I suggest removing the @Data annotation and instead using the @Getter, @Setter, @NoArgsConstructor, @AllArgsConstructor and @EqualsAndHashCode(onlyExplicitlyIncluded = true) annotations. Alternatively, you could completely ditch using Lombok and rely on ide-generated getters and setters instead.

    If you only want to update one field, use the update query directly. The save method in Spring Data JPA functions both as an update and a persist, which can sometimes lead to unexpected behavior.