postgresql 使用条件时hibernate查询太慢-忽略延迟加载?

siv3szwd  于 2023-06-22  发布在  PostgreSQL
关注(0)|答案(1)|浏览(186)

我已经实现了一个jpa + hibernate应用程序,但如果我使用hibernate标准应用程序是非常缓慢的。
时间差是10/20倍。
如果我用这个

entityManager.createQuery("SELECT u FROM PlantEntity u order by idPlant").setFirstResult(0).setMaxResults(10).getResultList();

执行时间约为5秒
如果我使用

Session hs = (Session) entityManager.getDelegate();
Criteria c = hs.createCriteria(PlantEntity.class,"plant");  
c.setMaxResults(Integer.parseInt(length));
c.setFirstResult(Integer.parseInt(start));
c.list();

时间约为40/50秒
表包含大约2000条记录
PlantEntity的manyToOne和OneToMany关系都设置为惰性。当我执行criteria查询时,延迟加载被忽略了,我注意到在log4j中,所有关系都被初始化了
我的persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" version="2.0">

    <persistence-unit name="NewPersistenceUnit">
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>

        <class>it.ciro.model.PlantEntity</class>
        <class>it.ciro.model.SegmentEntity</class>
        <class>it.ciro.model.PlanningEntity</class>
        <properties>
            <property name="hibernate.connection.url" value="jdbc:postgresql://94.23.66.103:5432/db"/>
            <property name="hibernate.connection.driver_class" value="org.postgresql.Driver"/>
            <property name="hibernate.connection.username" value="xxxx"/>
            <property name="hibernate.connection.password" value="xxxxx"/>
            <property name="hibernate.archive.autodetection" value="class"/>
            <property name="hibernate.format_sql" value="true"/>
            <property name="hibernate.connection.useUnicode" value="true" />
            <property name="hibernate.connection.characterEncoding" value="UTF-8" />
<property name="hibernate.generate_statistics" value="true"/>
            <property name="hbm2ddl.auto" value="validate"/>
            <property name="hibernate.c3p0.min_size" value="5" />
            <property name="hibernate.c3p0.max_size" value="20" />
            <property name="hibernate.c3p0.timeout" value="60" />
            <property name="hibernate.c3p0.max_statements" value="50" />
            <property name="hibernate.c3p0.idle_test_period" value="90" />
            <property name="hibernate.c3p0.preferredTestQuery" value="SELECT 1" />
            <property name="hibernate.c3p0.testConnectionOnCheckout" value="true" />
        </properties>
    </persistence-unit>
</persistence>

如何加快应用程序速度?
实体
植物实体

package it.ciro.model;
// Generated 13-giu-2016 12.32.01 by Hibernate Tools 5.1.0.Alpha1

import java.util.HashSet;
import java.util.Set;
import javax.persistence.*;

/**
 * PlantEntity generated by hbm2java
 */
@Entity
@Table(name = "plant", schema = "public")
public class PlantEntity implements java.io.Serializable {

    private int idPlant;
    private SegmentEntity segment;
    private Integer plantTypeId;
    private String name;
    private String coordinate;
    private String nfcCode;
    private String plantCode;
    private String picture_link;
    private String attached_a;
    private String attached_b;

    private Set plannings = new HashSet(0);

    public PlantEntity() {
    }

    public PlantEntity(int idPlant, SegmentEntity segment) {
        this.idPlant = idPlant;
        this.segment = segment;
    }

    public PlantEntity(int idPlant, SegmentEntity segment, Integer plantTypeId, String name, String coordinate, String nfcCode,
                        String plantCode, String picture_link,String attached_a,String attached_b) {
        this.idPlant = idPlant;
        this.segment = segment;
        this.plantTypeId = plantTypeId;
        this.name = name;
        this.coordinate = coordinate;
        this.nfcCode = nfcCode;
        this.plantCode = plantCode;
        this.picture_link = picture_link;
        this.attached_a = attached_a;
        this.attached_b = attached_b;
    }

    public PlantEntity(int idPlant, SegmentEntity segment, Integer plantTypeId, String name, String coordinate, String nfcCode,
                       Set plannings, String plantCode, String picture_link,String attached_a,String attached_b) {
        this.idPlant = idPlant;
        this.segment = segment;
        this.plantTypeId = plantTypeId;
        this.name = name;
        this.coordinate = coordinate;
        this.nfcCode = nfcCode;
        this.plannings = plannings;
        this.plantCode = plantCode;
        this.picture_link = picture_link;
        this.attached_a = attached_a;
        this.attached_b = attached_b;
    }

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id_plant", unique = true, nullable = false)
    public int getIdPlant() {
        return this.idPlant;
    }

    public void setIdPlant(int idPlant) {
        this.idPlant = idPlant;
    }

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "segment_id", nullable = false)
    public SegmentEntity getSegment() {
        return this.segment;
    }

    public void setSegment(SegmentEntity segment) {
        this.segment = segment;
    }

    @Column(name = "plant_type_id")
    public Integer getPlantTypeId() {
        return this.plantTypeId;
    }

    public void setPlantTypeId(Integer plantTypeId) {
        this.plantTypeId = plantTypeId;
    }

    @Column(name = "name")
    public String getName() {
        return this.name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Column(name = "coordinate", length = 100)
    public String getCoordinate() {
        return this.coordinate;
    }

    public void setCoordinate(String coordinate) {
        this.coordinate = coordinate;
    }

    @Column(name = "nfc_code")
    public String getNfcCode() {
        return this.nfcCode;
    }

    public void setNfcCode(String nfcCode) {
        this.nfcCode = nfcCode;
    }

    @Column(name = "plant_code")
    public String getPlantCode() {
        return this.plantCode;
    }

    public void setPlantCode(String plantCode) {
        this.plantCode = plantCode;
    }

    @Column(name="picture")
    public String getPicture_link() {
        return picture_link;
    }

    public void setPicture_link(String picture_link) {
        this.picture_link = picture_link;
    }

    @Column(name = "attached_a")
    public String getAttached_a(){
        return attached_a;
    }

    public void setAttached_a(String attached_a){
        this.attached_a = attached_a;
    }

    @Column(name = "attached_b")
    public String getAttached_b(){
        return attached_b;
    }

    public void setAttached_b(String attached_b){
        this.attached_b = attached_b;
    }

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "plant")
    public Set<PlanningEntity> getPlannings() {
        return this.plannings;
    }

    public void setPlannings(Set<PlanningEntity> plannings) {
        this.plannings = plannings;
    }

}

分段实体

package it.ciro.model;
// Generated 13-giu-2016 12.32.01 by Hibernate Tools 5.1.0.Alpha1

import java.util.HashSet;
import java.util.Set;
import javax.persistence.*;

/**
 * SegmentEntity generated by hbm2java
 */
@Entity
@Table(name = "segment", schema = "public")
public class SegmentEntity implements java.io.Serializable {

    private int idSegment;
    private PipelineEntity pipeline;
    private String name;
    private String area;
    private String segCode;
    private String picture;

    private Set plants = new HashSet(0);

    public SegmentEntity() {
    }

    public SegmentEntity(int idSegment) {
        this.idSegment = idSegment;
    }

    public SegmentEntity(int idSegment, PipelineEntity pipeline, String name, String area, Set plants,String segCode, String picture) {
        this.idSegment = idSegment;
        this.pipeline = pipeline;
        this.name = name;
        this.area = area;
        this.plants = plants;
        this.segCode = segCode;
        this.picture = picture;
    }

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id_segment", unique = true, nullable = false)
    public int getIdSegment() {
        return this.idSegment;
    }

    public void setIdSegment(int idSegment) {
        this.idSegment = idSegment;
    }

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "pipeline_id")
    public PipelineEntity getPipeline() {
        return this.pipeline;
    }

    public void setPipeline(PipelineEntity pipeline) {
        this.pipeline = pipeline;
    }

    @Column(name = "name")
    public String getName() {
        return this.name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Column(name = "area")
    public String getArea() {
        return this.area;
    }

    public void setArea(String area) {
        this.area = area;
    }

    @Column(name = "seg_code")
    public String getSegCode() {
        return this.segCode;
    }

    public void setSegCode(String segCode) {
        this.segCode = segCode;
    }

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "segment")
    public Set<PlantEntity> getPlants() {
        return this.plants;
    }

    public void setPlants(Set<PlantEntity> plants) {
        this.plants = plants;
    }

    @Column(name="picture")
    public String getPicture() {
        return picture;
    }

    public void setPicture(String picture) {
        this.picture = picture;
    }
}

规划实体

package it.ciro.model;
// Generated 17-giu-2016 9.26.25 by Hibernate Tools 5.1.0.Alpha1

import java.util.Date;
import java.util.HashSet;
import java.util.Set;
import javax.persistence.*;

/**
 * Planning generated by hbm2java
 */
@Entity
@Table(name = "planning", schema = "public")
public class PlanningEntity implements java.io.Serializable {

    private int idPlanning;
    private PlanningEntity planning;
    private PlantEntity plant;
    private ServiceEntity service;
    private UserEntity user;
    private Date schedulingData;
    private Date completationData;
    private Integer result;
    private Integer status;


    private String planningType;
    private Set plannings = new HashSet(0);
    private Set planningDetails = new HashSet(0);

    public PlanningEntity() {
    }

    public PlanningEntity(int idPlanning, PlantEntity plant, ServiceEntity service, UserEntity user, Date schedulingData) {
        this.idPlanning = idPlanning;
        this.plant = plant;
        this.service = service;
        this.user = user;
        this.schedulingData = schedulingData;
    }

    public PlanningEntity(int idPlanning, PlanningEntity planning, PlantEntity plant, ServiceEntity service, UserEntity user, Date schedulingData,
            Date completationData, Integer result, Integer status, Set plannings, Set planningDetails) {
        this.idPlanning = idPlanning;
        this.planning = planning;
        this.plant = plant;
        this.service = service;
        this.user = user;
        this.schedulingData = schedulingData;
        this.completationData = completationData;
        this.result = result;
        this.status = status;
        this.plannings = plannings;
        this.planningDetails = planningDetails;
    }

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id_planning", unique = true, nullable = false)
    public int getIdPlanning() {
        return this.idPlanning;
    }

    public void setIdPlanning(int idPlanning) {
        this.idPlanning = idPlanning;
    }

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "parent_planning")
    public PlanningEntity getPlanning() {
        return this.planning;
    }

    public void setPlanning(PlanningEntity planning) {
        this.planning = planning;
    }

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "plant_id", nullable = false)
    public PlantEntity getPlant() {
        return this.plant;
    }

    public void setPlant(PlantEntity plant) {
        this.plant = plant;
    }

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "service_id", nullable = false)
    public ServiceEntity getService() {
        return this.service;
    }

    public void setService(ServiceEntity service) {
        this.service = service;
    }

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id", nullable = false)
    public UserEntity getUser() {
        return this.user;
    }

    public void setUser(UserEntity user) {
        this.user = user;
    }

    @Temporal(TemporalType.DATE)
    @Column(name = "scheduling_data", nullable = false, length = 13)
    public Date getSchedulingData() {
        return this.schedulingData;
    }

    public void setSchedulingData(Date schedulingData) {
        this.schedulingData = schedulingData;
    }

    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "completation_data", length = 29)
    public Date getCompletationData() {
        return this.completationData;
    }

    public void setCompletationData(Date completationData) {
        this.completationData = completationData;
    }

    @Column(name = "result")
    public Integer getResult() {
        return this.result;
    }

    public void setResult(Integer result) {
        this.result = result;
    }

    @Column(name = "status")
    public Integer getStatus() {
        return this.status;
    }

    public void setStatus(Integer status) {
        this.status = status;
    }

    @Column(name = "planning_type")
    public String getPlanningType() {
        return planningType;
    }

    public void setPlanningType(String planningType) {
        this.planningType = planningType;
    }

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "planning")
    public Set<PlanningEntity> getPlannings() {
        return this.plannings;
    }

    public void setPlannings(Set<PlanningEntity> plannings) {
        this.plannings = plannings;
    }

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "planning")
    @OrderBy("idPlanningDetail")
    public Set<PlanningDetailEntity> getPlanningDetails() {
        return this.planningDetails;
    }

    public void setPlanningDetails(Set<PlanningDetailEntity> planningDetails) {
        this.planningDetails = planningDetails;
    }

}
mqkwyuun

mqkwyuun1#

我也有同样的问题。hibernate的响应时间约为120秒,SQL Developper为0.2秒
我在hibernate.cfg.xml中删除了以下行:

<property name="hibernate.connection.defaultNChar">true</property>

性能问题也解决了。

相关问题