performance using spring JPA hibernate

Question

I have 2 tables that are bind in a "One to many" connection when I set the relation to EAGER , then the records are fetched from table A and then for each record the data is fetched from table B if let’s say I have 100 record in table B then 100 select query are done which is really bad for the performance. what do I need to do to the set it correct so all the data will be fetched in 1 query?

here is all the code: for table A (survey)

package hibernateDataFiles;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

import org.hibernate.annotations.NotFound;
import org.hibernate.annotations.NotFoundAction;

/**
 * Surveys generated by hbm2java
 */
@Entity
@Table(name = "surveys", schema = "edi_ms")
public class Survey implements java.io.Serializable {

    private static final long serialVersionUID = 1L;
    private long surveyId;
    private String umn;
    private String firstName;
    private String middleName;
    private String lastName;
    private String phoneNumber;
    private Date creationDate;
    private Long shortFeedbackGrade;
    private String shortFeedbackComment;
    private List<MemberAnswer> memberAnswers = new ArrayList<MemberAnswer>(0);
    private List<CategoryAnswer> categoriesAnswers = new ArrayList<CategoryAnswer>(0);

    public Survey() {
    }

    public Survey(long surveyId, String firstName, String lastName, String phoneNumber, Date creationDate) {
        this.surveyId = surveyId;
        this.firstName = firstName;
        this.lastName = lastName;
        this.phoneNumber = phoneNumber;
        this.creationDate = creationDate;
    }

    public Survey(long surveyId, String umn, String firstName, String middleName, String lastName, String phoneNumber,
            Date creationDate, Long shortFeedbackGrade, String shortFeedbackComment,
            List<MemberAnswer> memberAnswers, List<CategoryAnswer> categoriesAnswer) {
        this.surveyId = surveyId;
        this.umn = umn;
        this.firstName = firstName;
        this.middleName = middleName;
        this.lastName = lastName;
        this.phoneNumber = phoneNumber;
        this.creationDate = creationDate;
        this.shortFeedbackGrade = shortFeedbackGrade;
        this.shortFeedbackComment = shortFeedbackComment;
        this.memberAnswers = memberAnswers;
        this.categoriesAnswers = categoriesAnswer;
    }

    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "survey_id_seq")
    @SequenceGenerator(allocationSize = 1, name = "survey_id_seq", sequenceName = "EDI_MS.survey_id_seq")
    @Id

    @Column(name = "survey_id", unique = true, nullable = false)
    public long getSurveyId() {
        return this.surveyId;
    }

    public void setSurveyId(long surveyId) {
        this.surveyId = surveyId;
    }

    @Column(name = "umn", length = 15)
    public String getUmn() {
        return this.umn;
    }

    public void setUmn(String umn) {
        this.umn = umn;
    }

    @Column(name = "first_name", nullable = false, length = 20)
    public String getFirstName() {
        return this.firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    @Column(name = "middle_name", length = 20)
    public String getMiddleName() {
        return this.middleName;
    }

    public void setMiddleName(String middleName) {
        this.middleName = middleName;
    }

    @Column(name = "last_name", nullable = false, length = 20)
    public String getLastName() {
        return this.lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    @Column(name = "phone_number", nullable = false, length = 15)
    public String getPhoneNumber() {
        return this.phoneNumber;
    }

    public void setPhoneNumber(String phoneNumber) {
        this.phoneNumber = phoneNumber;
    }

    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "creation_date", nullable = false, length = 29)
    public Date getCreationDate() {
        return this.creationDate;
    }

    public void setCreationDate(Date creationDate) {
        this.creationDate = creationDate;
    }

    @Column(name = "short_feedback_grade")
    public Long getShortFeedbackGrade() {
        return this.shortFeedbackGrade;
    }

    public void setShortFeedbackGrade(Long shortFeedbackGrade) {
        this.shortFeedbackGrade = shortFeedbackGrade;
    }

    @Column(name = "short_feedback_comment", length = 500)
    public String getShortFeedbackComment() {
        return this.shortFeedbackComment;
    }

    public void setShortFeedbackComment(String shortFeedbackComment) {
        this.shortFeedbackComment = shortFeedbackComment;
    }

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "survey")
    @NotFound(action = NotFoundAction.IGNORE)
    public List<MemberAnswer> getMemberAnswers() {
        return this.memberAnswers;
    }

    public void setMemberAnswers(List<MemberAnswer> membersAnswers) {
        this.memberAnswers = membersAnswers;
    }

    @OneToMany(fetch = FetchType.EAGER, mappedBy = "survey")
    @NotFound(action = NotFoundAction.IGNORE)
    public List<CategoryAnswer> getCategoriesAnswers() {
        return this.categoriesAnswers;
    }

    public void setCategoriesAnswers(List<CategoryAnswer> categoriesAnswers) {
        this.categoriesAnswers = categoriesAnswers;
    }

}

here is the JPA

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import hibernateDataFiles.Survey;

public interface SurveyRepository extends JpaRepository<Survey, Long> {

    @Query("from Survey where  ?1 <= creation_date  and  creation_date  <  ?2 ")
    List<Survey> getSurveysByDates(Date fromDate , Date toDate );

}

here is table B (category_answers)

    package hibernateDataFiles;
    // Generated Jul 5, 2018 8:37:29 AM by Hibernate Tools 5.2.10.Final

    import javax.persistence.AttributeOverride;
    import javax.persistence.AttributeOverrides;
    import javax.persistence.Column;
    import javax.persistence.EmbeddedId;
    import javax.persistence.Entity;
    import javax.persistence.FetchType;
    import javax.persistence.JoinColumn;
    import javax.persistence.ManyToOne;
    import javax.persistence.Table;
    import com.fasterxml.jackson.annotation.JsonIgnore;

    /**
     * CategoriesAnswers generated by hbm2java
     */
    @Entity
    @Table(name = "categories_answers", schema = "edi_ms")
    public class CategoryAnswer implements java.io.Serializable {

        private static final long serialVersionUID = 1L;
        private CategoryAnswerId id;
        private Category category;
        private Survey survey;
        private long grade;
        private String comment;

        public CategoryAnswer() {
        }

        public CategoryAnswer(CategoryAnswerId id, Category category, Survey survey, long grade) {
            this.id = id;
            this.category = category;
            this.survey = survey;
            this.grade = grade;
        }

        public CategoryAnswer(CategoryAnswerId id, Category category, Survey survey, long grade, String comment) {
            this.id = id;
            this.category = category;
            this.survey = survey;
            this.grade = grade;
            this.comment = comment;
        }

        @EmbeddedId

        @AttributeOverrides({ @AttributeOverride(name = "surveyId", column = @Column(name = "survey_id", nullable = false)),
                @AttributeOverride(name = "categoryId", column = @Column(name = "category_id", nullable = false)) })

        public CategoryAnswerId getId() {
            return this.id;
        }

        public void setId(CategoryAnswerId id) {
            this.id = id;
        }

        @ManyToOne(fetch = FetchType.LAZY)
        @JoinColumn(name = "category_id", nullable = false, insertable = false, updatable = false)
        public Category getCategory() {
            return this.category;
        }

        public void setCategory(Category category) {
            this.category = category;
        }

        @ManyToOne(fetch = FetchType.LAZY)
        @JoinColumn(name = "survey_id", nullable = false, insertable = false, updatable = false)
        @JsonIgnore
        public Survey getSurvey() {
            return this.survey;
        }

        public void setSurvey(Survey survey) {
            this.survey = survey;
        }

        @Column(name = "grade", nullable = false)
        public long getGrade() {
            return this.grade;
        }

        public void setGrade(long grade) {
            this.grade = grade;
        }

        @Column(name = "comment", length = 500)
        public String getComment() {
            return this.comment;
        }

        public void setComment(String comment) {
            this.comment = comment;
        }

    }

and categoryAnswerId (pk of the table ) 
package hibernateDataFiles;
// Generated Jul 5, 2018 8:37:29 AM by Hibernate Tools 5.2.10.Final

import javax.persistence.Column;
import javax.persistence.Embeddable;

import org.hibernate.annotations.NotFound;
import org.hibernate.annotations.NotFoundAction;

/**
 * CategoriesAnswersId generated by hbm2java
 */
@Embeddable
public class CategoryAnswerId implements java.io.Serializable {

    private static final long serialVersionUID = 1L;
    private long surveyId;
    private long categoryId;

    public CategoryAnswerId() {
    }


    public CategoryAnswerId(long surveyId, long categoryId) {
        this.surveyId = surveyId;
        this.categoryId = categoryId;
    }

    @Column(name = "survey_id", nullable = false)

    public long getSurveyId() {
        return this.surveyId;
    }

    public void setSurveyId(long surveyId) {
        this.surveyId = surveyId;
    }

    @Column(name = "category_id", nullable = false)
    public long getCategoryId() {
        return this.categoryId;
    }

    public void setCategoryId(long categoryId) {
        this.categoryId = categoryId;
    }

    public boolean equals(Object other) {
        if ((this == other))
            return true;
        if ((other == null))
            return false;
        if (!(other instanceof CategoryAnswerId))
            return false;
        CategoryAnswerId castOther = (CategoryAnswerId) other;

        return (this.getSurveyId() == castOther.getSurveyId()) && (this.getCategoryId() == castOther.getCategoryId());
    }

    public int hashCode() {
        int result = 17;

        result = 37 * result + (int) this.getSurveyId();
        result = 37 * result + (int) this.getCategoryId();
        return result;
    }

}

and the JPA:

package repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import hibernateDataFiles.Category;

public interface CategoryRepository extends JpaRepository<Category, Long>{
    @Transactional 
    @Modifying
    @Query("update  Category set expiration_date = current_date() where category_id = ?1 ")
    void expireCategory(Long id );  


    @Query("from Category where function ('coalesce' ,effectiveDate ,current_date() ) <= current_date() "
            + "and function('coalesce' ,expirationDate , to_date('50001231','yyyymmdd')) > current_date() ")
    List<Category> getEffective( );



}

docs.jboss.org/hibernate/orm/current/userguide/html_single/… — JB Nizet
– JB Nizet, Commented Sep 20, 2018 at 6:07

jokster · Accepted Answer · 2018-09-20 06:19:07Z

1

You have the so called 1+n problem.

One solution is to tweak the fetch settings of the collection. Can't find a JPA way on the short hand. But since your using Hibernate, this should work:

@org.hibernate.annotations.Fetch(FetchMode.JOIN) or better FetchMode.SUBSELECT

If you're executing a query, you have to adjust the query by adding join fetch.

answered Sep 20, 2018 at 6:19

jokster

5575 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

RMagen Over a year ago

thank you !! adding @Fetch(FetchMode.SUBSELECT) solved my problem (Fetchmode.JOIN did not worked ) . one more Q - if I use a query what is the syntax i mean when i am doing this @Query("from Survey s join fetch CategoryAnswer ca where ?1 <= s.creation_date and s.creation_date < ?2 ") i am getting an error "could not resolve property: creation_date of: hibernateDataFiles.Survey"

jokster Over a year ago

The property is named creationDate. I guess this becomes creation_date in the database.

RMagen Over a year ago

thank you but i am still missing some thing... the select i am getting is without the "on" part meaning i am getting "select ... inner join edi_ms.categories_answers categoryan1_ on where ... " there should be some values after the on ( the "on" is generated ) maybe I need to add some annotation ?

jokster Over a year ago

Hm. That is strange. Sounds like a bug in hibernate to me. What version are you using?

RMagen Over a year ago

my hibernate version is 5.2.17.Final

Collectives™ on Stack Overflow

performance using spring JPA hibernate

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related