Hibernate performing multiple of the same query

Question

I am using Java 8 with Spring 4.3.1.RELEASE, JPA 2 and Hibernate 5.2.1.Final and MySQL.

hibernate.dialect = org.hibernate.dialect.MySQLDialect
hibernate.show_sql = true
hibernate.format_sql = true

I have the following code, which does a simple SELECT statement:

import java.util.List;    
import org.springframework.stereotype.Repository;    
import com.jobs.spring.domain.Category;

@Repository("categoryDao")
public class CategoryDaoImpl extends JpaDao<String, Category> implements CategoryDao {

    @Override
    public List<Category> findAll() {
        List<Category> categories = (List<Category>) entityManager.createQuery("Select t from " + entityClass.getSimpleName() + " t order by name").getResultList();
        return categories;
    }
}

If I debug the code, this method is only called once, but as you can see from the output below, Hibernate is performing the same SQL statement multiple times.

10:52:13,960 INFO  [stdout] (default task-4) Hibernate: 
10:52:13,962 INFO  [stdout] (default task-4)     select
10:52:13,962 INFO  [stdout] (default task-4)         category0_.id as id1_0_,
10:52:13,962 INFO  [stdout] (default task-4)         category0_.ICON as ICON2_0_,
10:52:13,963 INFO  [stdout] (default task-4)         category0_.CATERGORY as CATERGOR3_0_ 
10:52:13,963 INFO  [stdout] (default task-4)     from
10:52:13,964 INFO  [stdout] (default task-4)         category category0_ 
10:52:13,964 INFO  [stdout] (default task-4)     order by
10:52:13,965 INFO  [stdout] (default task-4)         category0_.CATERGORY
10:52:14,399 INFO  [stdout] (default task-4) Hibernate: 
10:52:14,400 INFO  [stdout] (default task-4)     select
10:52:14,400 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_0_,
10:52:14,401 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_0_,
10:52:14,402 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_1_,
10:52:14,402 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_1_,
10:52:14,403 INFO  [stdout] (default task-4)         subcategor0_.ICON as ICON2_21_1_,
10:52:14,403 INFO  [stdout] (default task-4)         subcategor0_.SUBCATEGORY as SUBCATEG3_21_1_ 
10:52:14,404 INFO  [stdout] (default task-4)     from
10:52:14,404 INFO  [stdout] (default task-4)         subcategory subcategor0_ 
10:52:14,405 INFO  [stdout] (default task-4)     where
10:52:14,406 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID=?
10:52:14,813 INFO  [stdout] (default task-4) Hibernate: 
10:52:14,814 INFO  [stdout] (default task-4)     select
10:52:14,815 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_0_,
10:52:14,815 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_0_,
10:52:14,816 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_1_,
10:52:14,816 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_1_,
10:52:14,817 INFO  [stdout] (default task-4)         subcategor0_.ICON as ICON2_21_1_,
10:52:14,817 INFO  [stdout] (default task-4)         subcategor0_.SUBCATEGORY as SUBCATEG3_21_1_ 
10:52:14,818 INFO  [stdout] (default task-4)     from
10:52:14,819 INFO  [stdout] (default task-4)         subcategory subcategor0_ 
10:52:14,819 INFO  [stdout] (default task-4)     where
10:52:14,820 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID=?
10:52:15,219 INFO  [stdout] (default task-4) Hibernate: 
10:52:15,220 INFO  [stdout] (default task-4)     select
10:52:15,221 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_0_,
10:52:15,221 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_0_,
10:52:15,222 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_1_,
10:52:15,222 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_1_,
10:52:15,223 INFO  [stdout] (default task-4)         subcategor0_.ICON as ICON2_21_1_,
10:52:15,223 INFO  [stdout] (default task-4)         subcategor0_.SUBCATEGORY as SUBCATEG3_21_1_ 
10:52:15,224 INFO  [stdout] (default task-4)     from
10:52:15,225 INFO  [stdout] (default task-4)         subcategory subcategor0_ 
10:52:15,225 INFO  [stdout] (default task-4)     where
10:52:15,226 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID=?
10:52:15,637 INFO  [stdout] (default task-4) Hibernate: 
10:52:15,637 INFO  [stdout] (default task-4)     select
10:52:15,638 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_0_,
10:52:15,638 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_0_,
10:52:15,639 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_1_,
10:52:15,640 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_1_,
10:52:15,640 INFO  [stdout] (default task-4)         subcategor0_.ICON as ICON2_21_1_,
10:52:15,641 INFO  [stdout] (default task-4)         subcategor0_.SUBCATEGORY as SUBCATEG3_21_1_ 
10:52:15,641 INFO  [stdout] (default task-4)     from
10:52:15,642 INFO  [stdout] (default task-4)         subcategory subcategor0_ 
10:52:15,642 INFO  [stdout] (default task-4)     where
10:52:15,643 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID=?
10:52:16,079 INFO  [stdout] (default task-4) Hibernate: 
10:52:16,080 INFO  [stdout] (default task-4)     select
10:52:16,080 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_0_,
10:52:16,081 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_0_,
10:52:16,081 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_1_,
10:52:16,082 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_1_,
10:52:16,082 INFO  [stdout] (default task-4)         subcategor0_.ICON as ICON2_21_1_,
10:52:16,083 INFO  [stdout] (default task-4)         subcategor0_.SUBCATEGORY as SUBCATEG3_21_1_ 
10:52:16,084 INFO  [stdout] (default task-4)     from
10:52:16,084 INFO  [stdout] (default task-4)         subcategory subcategor0_ 
10:52:16,085 INFO  [stdout] (default task-4)     where
10:52:16,085 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID=?
10:52:16,819 INFO  [stdout] (default task-4) Hibernate: 
10:52:16,819 INFO  [stdout] (default task-4)     select
10:52:16,820 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_0_,
10:52:16,820 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_0_,
10:52:16,821 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_1_,
10:52:16,822 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_1_,
10:52:16,822 INFO  [stdout] (default task-4)         subcategor0_.ICON as ICON2_21_1_,
10:52:16,823 INFO  [stdout] (default task-4)         subcategor0_.SUBCATEGORY as SUBCATEG3_21_1_ 
10:52:16,824 INFO  [stdout] (default task-4)     from
10:52:16,824 INFO  [stdout] (default task-4)         subcategory subcategor0_ 
10:52:16,825 INFO  [stdout] (default task-4)     where
10:52:16,825 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID=?
10:52:17,943 INFO  [stdout] (default task-4) Hibernate: 
10:52:17,944 INFO  [stdout] (default task-4)     select
10:52:17,945 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_0_,
10:52:17,945 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_0_,
10:52:17,946 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_1_,
10:52:17,946 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_1_,
10:52:17,947 INFO  [stdout] (default task-4)         subcategor0_.ICON as ICON2_21_1_,
10:52:17,947 INFO  [stdout] (default task-4)         subcategor0_.SUBCATEGORY as SUBCATEG3_21_1_ 
10:52:17,948 INFO  [stdout] (default task-4)     from
10:52:17,949 INFO  [stdout] (default task-4)         subcategory subcategor0_ 
10:52:17,949 INFO  [stdout] (default task-4)     where
10:52:17,950 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID=?
10:52:19,460 INFO  [stdout] (default task-4) Hibernate: 
10:52:19,461 INFO  [stdout] (default task-4)     select
10:52:19,462 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_0_,
10:52:19,462 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_0_,
10:52:19,463 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_1_,
10:52:19,463 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_1_,
10:52:19,464 INFO  [stdout] (default task-4)         subcategor0_.ICON as ICON2_21_1_,
10:52:19,464 INFO  [stdout] (default task-4)         subcategor0_.SUBCATEGORY as SUBCATEG3_21_1_ 
10:52:19,465 INFO  [stdout] (default task-4)     from
10:52:19,465 INFO  [stdout] (default task-4)         subcategory subcategor0_ 
10:52:19,466 INFO  [stdout] (default task-4)     where
10:52:19,467 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID=?
10:52:20,521 INFO  [stdout] (default task-4) Hibernate: 
10:52:20,521 INFO  [stdout] (default task-4)     select
10:52:20,522 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_0_,
10:52:20,523 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_0_,
10:52:20,523 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_1_,
10:52:20,524 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_1_,
10:52:20,524 INFO  [stdout] (default task-4)         subcategor0_.ICON as ICON2_21_1_,
10:52:20,525 INFO  [stdout] (default task-4)         subcategor0_.SUBCATEGORY as SUBCATEG3_21_1_ 
10:52:20,525 INFO  [stdout] (default task-4)     from
10:52:20,526 INFO  [stdout] (default task-4)         subcategory subcategor0_ 
10:52:20,526 INFO  [stdout] (default task-4)     where
10:52:20,527 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID=?
10:52:20,965 INFO  [stdout] (default task-4) Hibernate: 
10:52:20,966 INFO  [stdout] (default task-4)     select
10:52:20,966 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_0_,
10:52:20,967 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_0_,
10:52:20,968 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_1_,
10:52:20,968 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_1_,
10:52:20,969 INFO  [stdout] (default task-4)         subcategor0_.ICON as ICON2_21_1_,
10:52:20,969 INFO  [stdout] (default task-4)         subcategor0_.SUBCATEGORY as SUBCATEG3_21_1_ 
10:52:20,970 INFO  [stdout] (default task-4)     from
10:52:20,970 INFO  [stdout] (default task-4)         subcategory subcategor0_ 
10:52:20,971 INFO  [stdout] (default task-4)     where
10:52:20,971 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID=?
10:52:21,364 INFO  [stdout] (default task-4) Hibernate: 
10:52:21,365 INFO  [stdout] (default task-4)     select
10:52:21,365 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_0_,
10:52:21,366 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_0_,
10:52:21,366 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_1_,
10:52:21,367 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_1_,
10:52:21,367 INFO  [stdout] (default task-4)         subcategor0_.ICON as ICON2_21_1_,
10:52:21,368 INFO  [stdout] (default task-4)         subcategor0_.SUBCATEGORY as SUBCATEG3_21_1_ 
10:52:21,368 INFO  [stdout] (default task-4)     from
10:52:21,369 INFO  [stdout] (default task-4)         subcategory subcategor0_ 
10:52:21,370 INFO  [stdout] (default task-4)     where
10:52:21,370 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID=?
10:52:21,774 INFO  [stdout] (default task-4) Hibernate: 
10:52:21,774 INFO  [stdout] (default task-4)     select
10:52:21,775 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_0_,
10:52:21,775 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_0_,
10:52:21,776 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_1_,
10:52:21,777 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_1_,
10:52:21,777 INFO  [stdout] (default task-4)         subcategor0_.ICON as ICON2_21_1_,
10:52:21,778 INFO  [stdout] (default task-4)         subcategor0_.SUBCATEGORY as SUBCATEG3_21_1_ 
10:52:21,778 INFO  [stdout] (default task-4)     from
10:52:21,779 INFO  [stdout] (default task-4)         subcategory subcategor0_ 
10:52:21,779 INFO  [stdout] (default task-4)     where
10:52:21,780 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID=?
10:52:22,194 INFO  [stdout] (default task-4) Hibernate: 
10:52:22,195 INFO  [stdout] (default task-4)     select
10:52:22,195 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_0_,
10:52:22,196 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_0_,
10:52:22,196 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_1_,
10:52:22,197 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_1_,
10:52:22,197 INFO  [stdout] (default task-4)         subcategor0_.ICON as ICON2_21_1_,
10:52:22,198 INFO  [stdout] (default task-4)         subcategor0_.SUBCATEGORY as SUBCATEG3_21_1_ 
10:52:22,199 INFO  [stdout] (default task-4)     from
10:52:22,199 INFO  [stdout] (default task-4)         subcategory subcategor0_ 
10:52:22,200 INFO  [stdout] (default task-4)     where
10:52:22,200 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID=?
10:52:22,590 INFO  [stdout] (default task-4) Hibernate: 
10:52:22,590 INFO  [stdout] (default task-4)     select
10:52:22,591 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_0_,
10:52:22,591 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_0_,
10:52:22,592 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_1_,
10:52:22,592 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_1_,
10:52:22,593 INFO  [stdout] (default task-4)         subcategor0_.ICON as ICON2_21_1_,
10:52:22,594 INFO  [stdout] (default task-4)         subcategor0_.SUBCATEGORY as SUBCATEG3_21_1_ 
10:52:22,594 INFO  [stdout] (default task-4)     from
10:52:22,595 INFO  [stdout] (default task-4)         subcategory subcategor0_ 
10:52:22,595 INFO  [stdout] (default task-4)     where
10:52:22,596 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID=?
10:52:23,009 INFO  [stdout] (default task-4) Hibernate: 
10:52:23,010 INFO  [stdout] (default task-4)     select
10:52:23,010 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_0_,
10:52:23,011 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_0_,
10:52:23,012 INFO  [stdout] (default task-4)         subcategor0_.id as id1_21_1_,
10:52:23,012 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID as CATEGORY4_21_1_,
10:52:23,013 INFO  [stdout] (default task-4)         subcategor0_.ICON as ICON2_21_1_,
10:52:23,013 INFO  [stdout] (default task-4)         subcategor0_.SUBCATEGORY as SUBCATEG3_21_1_ 
10:52:23,014 INFO  [stdout] (default task-4)     from
10:52:23,015 INFO  [stdout] (default task-4)         subcategory subcategor0_ 
10:52:23,015 INFO  [stdout] (default task-4)     where
10:52:23,016 INFO  [stdout] (default task-4)         subcategor0_.CATEGORY_ID=?

If anyone can advise how I can get Hibernate to execute the query only once as expected, I would appreciate it.

UPDATE

Here is my Model object:

Category.java

@Entity
@Table(name="category")
@XmlRootElement(name="category")
public class Category extends AbstractDomain<String> {

    @Id
    private String id;

    @Size(min=3, max=55)
    @Column(name = "CATERGORY", nullable = false)   
    private String name;

    @JsonIgnore
    @OneToMany(cascade = CascadeType.ALL, fetch=FetchType.EAGER, mappedBy = "category")
    private Set<SubCategory> subCategories;

    @JsonIgnore
    @Basic(fetch=FetchType.LAZY)
    @Lob
    @Column(name = "ICON", nullable = true)
    private byte[] icon;

    @Transient
    private byte[] icon64;

    @XmlElement
    public byte[] getIcon64() {
        return icon64;
    }

    public void setIcon64(byte[] icon64) {
        this.icon64 = icon64;
    }

    @XmlElement
    public byte[] getIcon() {
        return icon;
    }

    public void setIcon(byte[] icon) {
        this.icon = icon;
    }

    @XmlElement
    public String getId() {
        return id;
    }

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

    @XmlElement
    public String getName() {
        return name;
    }

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

    public Set<SubCategory> getSubCategories() {
        return subCategories;
    }

    public void setSubCategories(Set<SubCategory> subCategories) {
        this.subCategories = subCategories;
    }
}

UPDATE

Thank you for the advise below. I update the code to the following:

public List<Category> findAll() {
    List<Category> categories = (List<Category>) entityManager.createQuery("Select t from " + entityClass.getSimpleName() + " t JOIN FETCH com.jobs.spring.domain.SubCategory order by t.name").getResultList();
    return categories;
}

But still get an error:

11:29:42,188 INFO  [stdout] (default task-2) Hibernate: 
11:29:42,188 INFO  [stdout] (default task-2)     select
11:29:42,188 INFO  [stdout] (default task-2)         category0_.id as id1_0_,
11:29:42,188 INFO  [stdout] (default task-2)         category0_.ICON as ICON2_0_,
11:29:42,188 INFO  [stdout] (default task-2)         category0_.CATERGORY as CATERGOR3_0_ 
11:29:42,188 INFO  [stdout] (default task-2)     from
11:29:42,188 INFO  [stdout] (default task-2)         category category0_ 
11:29:42,189 INFO  [stdout] (default task-2)     inner join
11:29:42,189 INFO  [stdout] (default task-2)         subcategory subcategor1_ 
11:29:42,189 INFO  [stdout] (default task-2)             on 
11:29:42,189 INFO  [stdout] (default task-2)     order by
11:29:42,189 INFO  [stdout] (default task-2)         category0_.CATERGORY
11:29:42,536 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-2) SQL Error: 1064, SQLState: 42000
11:29:42,537 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-2) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order by category0_.CATERGORY' at line 1
11:29:43,306 ERROR [io.undertow.request] (default task-2) UT005023: Exception handling request to /jbosswildfly-1.0/category/list: org.springframework.web.util.NestedServletException: Request processing failed; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

UPDATE

I updated the code to the following:

    List<Category> categories = (List<Category>) entityManager.createQuery("Select c from Category c JOIN FETCH c.subCategories s order by c.name").getResultList();

Which gets no errors, but it returns only duplicates of Categories, and no SubCategories. So I thin I need to change what's in the SELECT.

I have tried:

SELECT * (unexpected token: * near line 1)

SELECT c, s  (same as SELECT c)

Answer 1

That because your Category class has reference to other categories, called subcategory. For each category you need to fetch all subcategories, so you get many subqueries. We call it SQL N+1 problem. Let's try to use FETCH JOIN:

SELECT c FROM category c JOIN FETCH c.subCategories

Should execute one (bigger) query.