2

Today I faced the duplicate query problem (N+1 select). My question is what is the difference between the annotation and the JQL command JOIN FETCH.

For me the annotation doens't make any difference.

Down the results that I receive. Can anyone explaint o me?

This is my class:

@Entity
@Table(name = "tab_resp")
public class Responsavel implements Serializable, IBeanEnable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @GenField(fieldText = "Nome", columnName = "Nome", type = GenField.CHAR_STRING, isRepresentation = true)
    @Column(length = 70)
    private String nome;
    @GenField(fieldText = "Email", columnName = "Email", type = GenField.CHAR_STRING)
    @Column(length = 150)
    private String email;
    @GenField(fieldText = "Empresa", columnName = "Empresa", type = GenField.OBJECT)
    @OneToOne//(fetch = FetchType.EAGER)
    @Fetch(FetchMode.JOIN)
    private Empresa empresa;
    @GenField(fieldText = "Cargo", columnName = "Cargo", type = GenField.OBJECT)
    @OneToOne//(fetch = FetchType.EAGER)
    @Fetch(FetchMode.JOIN)    
    private Cargo cargo;
    @GenField(fieldText = "Data Cadastro", columnName = "Data Cadastro", type = GenField.DATE, dateTimeFormat = "dd/MM/yyyyy HH:mm:ss")
    @Column(name = "dh_cad")
    private LocalDateTime dataHoraRegistro;
    @GenField(fieldText = "Data Atualização", columnName = "Data Atualização", type = GenField.DATE, dateTimeFormat = "dd/MM/yyyyy HH:mm:ss")
    @Column(name = "dh_atu")
    private LocalDateTime dataHoraAtualizacao;
    @GenField(fieldText = "Ativo", columnName = "Ativo", type = GenField.BOOLEAN)
    @Convert(converter = BooleanToIntegerConverter.class)
    private Boolean ativo;
...

Result:

Hibernate: select responsave0_.id as id1_18_, responsave0_.ativo as ativo2_18_, responsave0_.cargo_id as cargo_id7_18_, responsave0_.dh_atu as dh_atu3_18_, responsave0_.dh_cad as dh_cad4_18_, responsave0_.email as email5_18_, responsave0_.empresa_id as empresa_8_18_, responsave0_.nome as nome6_18_ from tab_resp responsave0_
Hibernate: select cargo0_.id as id1_5_0_, cargo0_.ativo as ativo2_5_0_, cargo0_.dataHoraRegistro as dataHora3_5_0_, cargo0_.descricao as descrica4_5_0_ from tab_carg cargo0_ where cargo0_.id=?
Hibernate: select empresa0_.id as id1_10_0_, empresa0_.ativo as ativo2_10_0_, empresa0_.dh_atu as dh_atu3_10_0_, empresa0_.dh_cad as dh_cad4_10_0_, empresa0_.nome as nome5_10_0_ from tab_empr empresa0_ where empresa0_.id=?
Hibernate: select cargo0_.id as id1_5_0_, cargo0_.ativo as ativo2_5_0_, cargo0_.dataHoraRegistro as dataHora3_5_0_, cargo0_.descricao as descrica4_5_0_ from tab_carg cargo0_ where cargo0_.id=?
Hibernate: select cargo0_.id as id1_5_0_, cargo0_.ativo as ativo2_5_0_, cargo0_.dataHoraRegistro as dataHora3_5_0_, cargo0_.descricao as descrica4_5_0_ from tab_carg cargo0_ where cargo0_.id=?
Hibernate: select cargo0_.id as id1_5_0_, cargo0_.ativo as ativo2_5_0_, cargo0_.dataHoraRegistro as dataHora3_5_0_, cargo0_.descricao as descrica4_5_0_ from tab_carg cargo0_ where cargo0_.id=?
Hibernate: select cargo0_.id as id1_5_0_, cargo0_.ativo as ativo2_5_0_, cargo0_.dataHoraRegistro as dataHora3_5_0_, cargo0_.descricao as descrica4_5_0_ from tab_carg cargo0_ where cargo0_.id=?
Hibernate: select cargo0_.id as id1_5_0_, cargo0_.ativo as ativo2_5_0_, cargo0_.dataHoraRegistro as dataHora3_5_0_, cargo0_.descricao as descrica4_5_0_ from tab_carg cargo0_ where cargo0_.id=?
Hibernate: select cargo0_.id as id1_5_0_, cargo0_.ativo as ativo2_5_0_, cargo0_.dataHoraRegistro as dataHora3_5_0_, cargo0_.descricao as descrica4_5_0_ from tab_carg cargo0_ where cargo0_.id=?
Hibernate: select cargo0_.id as id1_5_0_, cargo0_.ativo as ativo2_5_0_, cargo0_.dataHoraRegistro as dataHora3_5_0_, cargo0_.descricao as descrica4_5_0_ from tab_carg cargo0_ where cargo0_.id=?
Hibernate: select empresa0_.id as id1_10_0_, empresa0_.ativo as ativo2_10_0_, empresa0_.dh_atu as dh_atu3_10_0_, empresa0_.dh_cad as dh_cad4_10_0_, empresa0_.nome as nome5_10_0_ from tab_empr empresa0_ where empresa0_.id=?
Hibernate: select cargo0_.id as id1_5_0_, cargo0_.ativo as ativo2_5_0_, cargo0_.dataHoraRegistro as dataHora3_5_0_, cargo0_.descricao as descrica4_5_0_ from tab_carg cargo0_ where cargo0_.id=?
Hibernate: select cargo0_.id as id1_5_0_, cargo0_.ativo as ativo2_5_0_, cargo0_.dataHoraRegistro as dataHora3_5_0_, cargo0_.descricao as descrica4_5_0_ from tab_carg cargo0_ where cargo0_.id=?
Hibernate: select cargo0_.id as id1_5_0_, cargo0_.ativo as ativo2_5_0_, cargo0_.dataHoraRegistro as dataHora3_5_0_, cargo0_.descricao as descrica4_5_0_ from tab_carg cargo0_ where cargo0_.id=?
Hibernate: select cargo0_.id as id1_5_0_, cargo0_.ativo as ativo2_5_0_, cargo0_.dataHoraRegistro as dataHora3_5_0_, cargo0_.descricao as descrica4_5_0_ from tab_carg cargo0_ where cargo0_.id=?
Hibernate: select cargo0_.id as id1_5_0_, cargo0_.ativo as ativo2_5_0_, cargo0_.dataHoraRegistro as dataHora3_5_0_, cargo0_.descricao as descrica4_5_0_ from tab_carg cargo0_ where cargo0_.id=?

JQL with Fecth option result in just one query command.

dao.consulta("select r from Responsavel r JOIN FETCH r.cargo c JOIN FETCH r.empresa e");

Result:

Hibernate: select responsave0_.id as id1_18_0_, cargo1_.id as id1_5_1_, empresa2_.id as id1_10_2_, responsave0_.ativo as ativo2_18_0_, responsave0_.cargo_id as cargo_id7_18_0_, responsave0_.dh_atu as dh_atu3_18_0_, responsave0_.dh_cad as dh_cad4_18_0_, responsave0_.email as email5_18_0_, responsave0_.empresa_id as empresa_8_18_0_, responsave0_.nome as nome6_18_0_, cargo1_.ativo as ativo2_5_1_, cargo1_.dataHoraRegistro as dataHora3_5_1_, cargo1_.descricao as descrica4_5_1_, empresa2_.ativo as ativo2_10_2_, empresa2_.dh_atu as dh_atu3_10_2_, empresa2_.dh_cad as dh_cad4_10_2_, empresa2_.nome as nome5_10_2_ from tab_resp responsave0_ inner join tab_carg cargo1_ on responsave0_.cargo_id=cargo1_.id inner join tab_empr empresa2_ on responsave0_.empresa_id=empresa2_.id
Angel Cuenca
  • 1,637
  • 6
  • 24
  • 46
Krismorte
  • 642
  • 7
  • 24
  • I just tested your code and it works as expected. Which version of Hibernate? Did you actually load the entity with `(fetch = FetchType.EAGER)` commented out? `@Fetch(FetchMode.JOIN)` and `(fetch = FetchType.EAGER)` are effectively mutually exclusive – crizzis May 22 '17 at 21:31
  • Hibernate 5. I ran some tests put the (fetch = FetchType.LAZY) and the numbers of querys decreases – Krismorte May 22 '17 at 21:47
  • When I use FetchType.LAZY or FetchType.EAGER theres somes kind of rule to help me choose the better? And When I define FetchType.LAZY which is the better way to load Related entities? – Krismorte May 22 '17 at 21:49
  • See https://stackoverflow.com/questions/2990799/difference-between-fetchtype-lazy-and-eager-in-java-persistence-api and https://docs.jboss.org/hibernate/orm/5.0/manual/en-US/html/ch20.html#performance-fetching. By default, 'to-one' relationships are fetched eagerly, while 'to-many' relationships are fetched lazily, which is sensible for **most** applications. You should only load 'to-many' relationships eagerly when it's effectively a 'to-few' association, and the parent entity is not very useful without its related children (e.g. `Order` and `LineItem`s) – crizzis May 22 '17 at 22:01
  • 1
    Also, with `FetchType.LAZY`, specifying `@Fetch` doesn't make sense: since you don't want a `JOIN FETCH`, the associated entities will not be loaded until you request them, at which point the loading will obviously be done using a single query – crizzis May 22 '17 at 22:08

0 Answers0