Criteria API性能问题的解决思路

问题描述

我正在使用Criteria API进行动态查询，我遇到的问题是，当执行该查询时，将对查询返回的每个记录重复每个查询（这在Hibernate控制台中显示）。然后我得到非常高的响应时间。

我的规范类如下：

public class PacienteSpecification implements Specification {

    private Paciente filter;

    public PacienteSpecification(Paciente filter) {
        super();
        this.filter = filter;
    }

    public Predicate toPredicate(Root root, CriteriaQuery> cq,
                                 CriteriaBuilder cb) {
        Predicate p = cb.conjunction();
        p.getExpressions().add(cb.equal(root.get("eliminado"), "N"));

        if (filter.getNombres() != "") {
            p.getExpressions().add(cb.like(cb.upper(root.get("nombres")), "%" + filter.getNombres().toUpperCase() + "%"));
        }
        if(filter.getApePaterno() != "") {
            p.getExpressions().add(cb.or(cb.like(cb.upper(root.get("apePaterno")), "%" + filter.getApePaterno().toUpperCase() + "%")));
        }
        if(filter.getApeMaterno() != "") {
            p.getExpressions().add(cb.or(cb.like(cb.upper(root.get("apeMaterno")), "%" + filter.getApeMaterno().toUpperCase() + "%")));
        }
        if (filter.getFicha() != "") {
            p.getExpressions().add(cb.equal(cb.upper(root.get("ficha")), filter.getFicha()));
        }
        if(filter.getFechaNacimiento() != null){
            p.getExpressions().add(cb.equal(root.get("fechaNacimiento"), filter.getFechaNacimiento()));
        }
        if(filter.getGenero() != null){
            p.getExpressions().add(cb.equal(root.get("genero"), filter.getGenero()));
        }
        return p;
    }
}

我调用存储库的服务如下：

List listaPacientes = new ArrayList();
        Specification pacienteDocEspecificacion = new PacienteDocEspecification(pacBusqueda.getPacienteDocs().get(0));
        final List pacDoc = pacienteDocRep.findAll(pacienteDocEspecificacion);

并且在调用服务时，休眠控制台向我显示了对每个记录的查询：

Hibernate: select pacientedo0_.id_paciente_doc as id_paciente_doc1_44_, pacientedo0_.eliminado as eliminado2_44_, pacientedo0_.fecha_creacion as fecha_creacion3_44_, pacientedo0_.fecha_ult_modificacion as fecha_ult_modifica4_44_, pacientedo0_.id_usuario_creacion as id_usuario_creacio5_44_, pacientedo0_.id_usuario_ult_mod as id_usuario_ult_mod6_44_, pacientedo0_.num_documento as num_documento7_44_, pacientedo0_.id_paciente as id_paciente8_44_, pacientedo0_.id_tipo_documento as id_tipo_documento9_44_ from dgs_core.paciente_doc pacientedo0_ where 1=1
Hibernate: select paciente0_.id_paciente as id_paciente1_43_0_, paciente0_.ape_materno as ape_materno2_43_0_, paciente0_.ape_paterno as ape_paterno3_43_0_, paciente0_.celular as celular4_43_0_, paciente0_.id_comuna as id_comuna19_43_0_, paciente0_.direccion as direccion5_43_0_, paciente0_.eliminado as eliminado6_43_0_, paciente0_.email as email7_43_0_, paciente0_.id_estado_civil as id_estado_civil20_43_0_, paciente0_.id_etnia as id_etnia21_43_0_, paciente0_.fecha_creacion as fecha_creacion8_43_0_, paciente0_.fecha_defuncion as fecha_defuncion9_43_0_, paciente0_.fecha_nacimiento as fecha_nacimiento10_43_0_, paciente0_.fecha_ult_modificacion as fecha_ult_modific11_43_0_, paciente0_.ficha as ficha12_43_0_, paciente0_.fono as fono13_43_0_, paciente0_.fono_emergencia as fono_emergencia14_43_0_, paciente0_.id_genero as id_genero22_43_0_, paciente0_.id_unidad_creacion as id_unidad_creacio15_43_0_, paciente0_.id_usuario_creacion as id_usuario_creaci16_43_0_, paciente0_.id_usuario_ult_mod as id_usuario_ult_mo17_43_0_, paciente0_.id_nacionalidad as id_nacionalidad23_43_0_, paciente0_.nombres as nombres18_43_0_, paciente0_.id_prevision as id_prevision24_43_0_, paciente0_.id_profesion as id_profesion25_43_0_, catalogo1_.id_catalogo as id_catalogo1_35_1_, catalogo1_.activo as activo2_35_1_, catalogo1_.comentario as comentario3_35_1_, catalogo1_.id_def_catalogo as id_def_catalogo14_35_1_, catalogo1_.orden as orden4_35_1_, catalogo1_.valido_desde as valido_desde5_35_1_, catalogo1_.valido_hasta as valido_hasta6_35_1_, catalogo1_.valor1 as valor7_35_1_, catalogo1_.valor2 as valor8_35_1_, catalogo1_.valor3 as valor9_35_1_, catalogo1_.valor4 as valor10_35_1_, catalogo1_.valor5 as valor11_35_1_, catalogo1_.valor6 as valor12_35_1_, catalogo1_.version as version13_35_1_, definicion2_.id_def_catalogo as id_def_catalogo1_36_2_, definicion2_.activo as activo2_36_2_, definicion2_.columna1 as columna3_36_2_, definicion2_.columna2 as columna4_36_2_, definicion2_.columna3 as columna5_36_2_, definicion2_.columna4 as columna6_36_2_, definicion2_.columna5 as columna7_36_2_, definicion2_.columna6 as columna8_36_2_, definicion2_.comentario as comentario9_36_2_, definicion2_.nombre_catalogo as nombre_catalogo10_36_2_, definicion2_.valido_desde as valido_desde11_36_2_, definicion2_.valido_hasta as valido_hasta12_36_2_, definicion2_.version as version13_36_2_, catalogo3_.id_catalogo as id_catalogo1_35_3_, catalogo3_.activo as activo2_35_3_, catalogo3_.comentario as comentario3_35_3_, catalogo3_.id_def_catalogo as id_def_catalogo14_35_3_, catalogo3_.orden as orden4_35_3_, catalogo3_.valido_desde as valido_desde5_35_3_, catalogo3_.valido_hasta as valido_hasta6_35_3_, catalogo3_.valor1 as valor7_35_3_, catalogo3_.valor2 as valor8_35_3_, catalogo3_.valor3 as valor9_35_3_, catalogo3_.valor4 as valor10_35_3_, catalogo3_.valor5 as valor11_35_3_, catalogo3_.valor6 as valor12_35_3_, catalogo3_.version as version13_35_3_, catalogo4_.id_catalogo as id_catalogo1_35_4_, catalogo4_.activo as activo2_35_4_, catalogo4_.comentario as comentario3_35_4_, catalogo4_.id_def_catalogo as id_def_catalogo14_35_4_, catalogo4_.orden as orden4_35_4_, catalogo4_.valido_desde as valido_desde5_35_4_, catalogo4_.valido_hasta as valido_hasta6_35_4_, catalogo4_.valor1 as valor7_35_4_, catalogo4_.valor2 as valor8_35_4_, catalogo4_.valor3 as valor9_35_4_, catalogo4_.valor4 as valor10_35_4_, catalogo4_.valor5 as valor11_35_4_, catalogo4_.valor6 as valor12_35_4_, catalogo4_.version as version13_35_4_, catalogo5_.id_catalogo as id_catalogo1_35_5_, catalogo5_.activo as activo2_35_5_, catalogo5_.comentario as comentario3_35_5_, catalogo5_.id_def_catalogo as id_def_catalogo14_35_5_, catalogo5_.orden as orden4_35_5_, catalogo5_.valido_desde as valido_desde5_35_5_, catalogo5_.valido_hasta as valido_hasta6_35_5_, catalogo5_.valor1 as valor7_35_5_, catalogo5_.valor2 as valor8_35_5_, catalogo5_.valor3 as valor9_35_5_, catalogo5_.valor4 as valor10_35_5_, catalogo5_.valor5 as valor11_35_5_, catalogo5_.valor6 as valor12_35_5_, catalogo5_.version as version13_35_5_, catalogo6_.id_catalogo as id_catalogo1_35_6_, catalogo6_.activo as activo2_35_6_, catalogo6_.comentario as comentario3_35_6_, catalogo6_.id_def_catalogo as id_def_catalogo14_35_6_, catalogo6_.orden as orden4_35_6_, catalogo6_.valido_desde as valido_desde5_35_6_, catalogo6_.valido_hasta as valido_hasta6_35_6_, catalogo6_.valor1 as valor7_35_6_, catalogo6_.valor2 as valor8_35_6_, catalogo6_.valor3 as valor9_35_6_, catalogo6_.valor4 as valor10_35_6_, catalogo6_.valor5 as valor11_35_6_, catalogo6_.valor6 as valor12_35_6_, catalogo6_.version as version13_35_6_, catalogo7_.id_catalogo as id_catalogo1_35_7_, catalogo7_.activo as activo2_35_7_, catalogo7_.comentario as comentario3_35_7_, catalogo7_.id_def_catalogo as id_def_catalogo14_35_7_, catalogo7_.orden as orden4_35_7_, catalogo7_.valido_desde as valido_desde5_35_7_, catalogo7_.valido_hasta as valido_hasta6_35_7_, catalogo7_.valor1 as valor7_35_7_, catalogo7_.valor2 as valor8_35_7_, catalogo7_.valor3 as valor9_35_7_, catalogo7_.valor4 as valor10_35_7_, catalogo7_.valor5 as valor11_35_7_, catalogo7_.valor6 as valor12_35_7_, catalogo7_.version as version13_35_7_, catalogo8_.id_catalogo as id_catalogo1_35_8_, catalogo8_.activo as activo2_35_8_, catalogo8_.comentario as comentario3_35_8_, catalogo8_.id_def_catalogo as id_def_catalogo14_35_8_, catalogo8_.orden as orden4_35_8_, catalogo8_.valido_desde as valido_desde5_35_8_, catalogo8_.valido_hasta as valido_hasta6_35_8_, catalogo8_.valor1 as valor7_35_8_, catalogo8_.valor2 as valor8_35_8_, catalogo8_.valor3 as valor9_35_8_, catalogo8_.valor4 as valor10_35_8_, catalogo8_.valor5 as valor11_35_8_, catalogo8_.valor6 as valor12_35_8_, catalogo8_.version as version13_35_8_ from dgs_core.paciente paciente0_ left outer join dgs_core.catalogo catalogo1_ on paciente0_.id_comuna=catalogo1_.id_catalogo left outer join dgs_core.definicion_catalogo definicion2_ on catalogo1_.id_def_catalogo=definicion2_.id_def_catalogo left outer join dgs_core.catalogo catalogo3_ on paciente0_.id_estado_civil=catalogo3_.id_catalogo left outer join dgs_core.catalogo catalogo4_ on paciente0_.id_etnia=catalogo4_.id_catalogo left outer join dgs_core.catalogo catalogo5_ on paciente0_.id_genero=catalogo5_.id_catalogo left outer join dgs_core.catalogo catalogo6_ on paciente0_.id_nacionalidad=catalogo6_.id_catalogo left outer join dgs_core.catalogo catalogo7_ on paciente0_.id_prevision=catalogo7_.id_catalogo left outer join dgs_core.catalogo catalogo8_ on paciente0_.id_profesion=catalogo8_.id_catalogo where paciente0_.id_paciente=?

思路：

似乎您的实体映射或多或少是这样的：

public class Paciente {
    ...
    @OneToMany(fetch=FetchType.EAGER)
    private Set docs = new HashSet();
    ...
}

然后您遇到What is the "N+1 selects problem" in ORM (Object-Relational Mapping)?

要解决此问题，只需将@OneToMany(fetch=FetchType.EAGER)更改为等于@OneToMany的@OneToMany(fetch=FetchType.LAZY)
 

关于Criteria API性能问题的解决思路小编说到这里就结束了， 你还需要深入了解这方面的内容，可以来我们网站翻阅相关文章，有需要可以关注我们爱站技术频道网站。