Announcement Announcement Module
Collapse
No announcement yet.
Data Jpa how to make Eager fetch works Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Data Jpa how to make Eager fetch works

    Hi all,
    I'm trying to do a very simple task. I've 2 entities, Account and Customer with a
    OneToOne relation, and an AccountRepository, I want to load, in a web page, a paged list of Accounts
    using Spring Data for JPA/Hibernate.
    I'm able to do this but if I look to the generated SQL queries I see that one query for single account is made to fetch the associate customer:

    Generated SQL queries

    Code:
    Hibernate: select count(*) as col_0_0_ from Account account0_
    
    Hibernate: select account0_.id as id1_0_, account0_.expiryDate as expiryDa2_0_ from Account account0_ cross join Customer customer1_ where account0_.id=customer1_.id order by customer1_.lastname asc, customer1_.firstname asc limit ?
    
    Hibernate: select customer0_.id as id1_1_1_, customer0_.firstname as firstnam2_1_1_, customer0_.lastname as lastname3_1_1_, account1_.id as id1_0_0_, account1_.expiryDate as expiryDa2_0_0_ from Customer customer0_ left outer join Account account1_ on customer0_.id=account1_.id where customer0_.id=?
    Hibernate: select customer0_.id as id1_1_1_, customer0_.firstname as firstnam2_1_1_, customer0_.lastname as lastname3_1_1_, account1_.id as id1_0_0_, account1_.expiryDate as expiryDa2_0_0_ from Customer customer0_ left outer join Account account1_ on customer0_.id=account1_.id where customer0_.id=?
    Hibernate: select customer0_.id as id1_1_1_, customer0_.firstname as firstnam2_1_1_, customer0_.lastname as lastname3_1_1_, account1_.id as id1_0_0_, account1_.expiryDate as expiryDa2_0_0_ from Customer customer0_ left outer join Account account1_ on customer0_.id=account1_.id where customer0_.id=?
    Hibernate: select customer0_.id as id1_1_1_, customer0_.firstname as firstnam2_1_1_, customer0_.lastname as lastname3_1_1_, account1_.id as id1_0_0_, account1_.expiryDate as expiryDa2_0_0_ from Customer customer0_ left outer join Account account1_ on customer0_.id=account1_.id where customer0_.id=?
    ......
    This is a waste of resources and has very poor performace.
    Is there a way to get the paged list of accounts with one query?

    Here my code:


    MVC Controller

    Code:
    public class HomeController {
    
    	@Autowired
    	private AccountRepository accountRepository;
    
    	private final int pageSize = 20;
    
    	@SuppressWarnings("unused")
    	@RequestMapping(value = "/", method = RequestMethod.GET)
    	public String home(Locale locale, Model model) {
    		return "home";
    	}
    
    	@RequestMapping(value = "/pages/{pageNumber}", method = RequestMethod.GET)
    	public String getItemsPage(@PathVariable Integer pageNumber, Model model) {
    
    		PageRequest page = new PageRequest(pageNumber, pageSize, Direction.ASC,
    		    "customer.lastname", "customer.firstname");
    
    		Page<Account> accounts = accountRepository.findAll(page);
    		
    		int current = accounts.getNumber() + 1;
    		int begin = Math.max(1, current - 5);
    		int end = Math.min(begin + pageSize, accounts.getTotalPages());
    
    		model.addAttribute("beginIndex", begin);
    		model.addAttribute("endIndex", end);
    		model.addAttribute("currentIndex", current);
    
    		model.addAttribute("accounts", accounts);
    
    		return "page";
    	}
    }
    MVC View
    Code:
    <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
    <%@ page session="false"%>
    
    <c:url var="firstUrl" value="/pages/1" />
    <c:url var="lastUrl" value="/pages/${accounts.totalPages}" />
    <c:url var="prevUrl" value="/pages/${currentIndex - 1}" />
    <c:url var="nextUrl" value="/pages/${currentIndex + 1}" />
    
    <html>
    <head>
    <title>Page</title>
    </head>
    <body>
    	<h1>Paged List</h1>
    	<a href='<c:url value="/"/>'>Home</a>
    
    	<table width="100%" border="1">
    		<thead>
    			<tr>
    				<td>Counter: ${accounts.getTotalElements()}</td>
    				<td>Name</td>
    				<td>Date</td>
    				<td>Width</td>
    				<td>Height</td>
    			</TR>
    		</thead>
    		<tbody>
    			<c:forEach items="${accounts.getContent()}" var="item"
    				varStatus="status">
    				<tr class="${status.count % 2 == 0 ? 'even' : 'odd'}">
    					<td>${status.count})</td>
    					<td>${item.id}</td>
    					<td>${item.expiryDate})</td>
    					<td>${item.customer.firstname}</td>
    					<td>${item.customer.lastname}</td>
    					<td></td>
    				</tr>
    			</c:forEach>
    		</tbody>
    	</table>
    
    	<div class="pagination">
    		<ul>
    			<c:choose>
    				<c:when test="${currentIndex == 1}">
    					<li class="disabled"><a href="#">&lt;&lt;</a></li>
    					<li class="disabled"><a href="#">&lt;</a></li>
    				</c:when>
    				<c:otherwise>
    					<li><a href="${firstUrl}">&lt;&lt;</a></li>
    					<li><a href="${prevUrl}">&lt;</a></li>
    				</c:otherwise>
    			</c:choose>
    			<c:forEach var="i" begin="${beginIndex}" end="${endIndex}">
    				<c:url var="pageUrl" value="/pages/${i}" />
    				<c:choose>
    					<c:when test="${i == currentIndex}">
    						<li class="active"><a href="${pageUrl}"><c:out
    									value="${i}" /></a></li>
    					</c:when>
    					<c:otherwise>
    						<li><a href="${pageUrl}"><c:out value="${i}" /></a></li>
    					</c:otherwise>
    				</c:choose>
    			</c:forEach>
    			<c:choose>
    				<c:when test="${currentIndex == accounts.totalPages}">
    					<li class="disabled"><a href="#">&gt;</a></li>
    					<li class="disabled"><a href="#">&gt;&gt;</a></li>
    				</c:when>
    				<c:otherwise>
    					<li><a href="${nextUrl}">&gt;</a></li>
    					<li><a href="${lastUrl}">&gt;&gt;</a></li>
    				</c:otherwise>
    			</c:choose>
    		</ul>
    	</div>
    
    </body>
    </html>


    Jpa Repository

    Code:
    public interface AccountRepository extends JpaRepository<Account, Long> {
    
    }

    Jpa Entities

    Code:
    @Entity
    @Table(name = "Account")
    public class Account {
    	@Id
    	private Long id;
    
    	@OneToOne(mappedBy = "account", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    	private Customer customer;
    
    	@Temporal(TemporalType.DATE)
    	private Date expiryDate;
    
    	public Long getId() {
    		return id;
    	}
    
    	public Customer getCustomer() {
    		return customer;
    	}
    
    	public Date getExpiryDate() {
    		return expiryDate;
    	}
    
    	public void setId(Long id) {
    		this.id = id;
    	}
    
    	public void setCustomer(Customer customer) {
    		this.customer = customer;
    	}
    
    	public void setExpiryDate(Date expiryDate) {
    		this.expiryDate = expiryDate;
    	}
    
    }
    
    
    @Entity
    @Table(name = "Customer")
    public class Customer {
    	@Id
    	private Long id;
    
    	private String firstname;
    	private String lastname;
    	
    	@PrimaryKeyJoinColumn
    	Account account;
    
    	public Long getId() {
    		return id;
    	}
    
    	public String getFirstname() {
    		return firstname;
    	}
    
    	public String getLastname() {
    		return lastname;
    	}
    
    	public void setId(Long id) {
    		this.id = id;
    	}
    
    	public void setFirstname(String firstname) {
    		this.firstname = firstname;
    	}
    
    	public void setLastname(String lastname) {
    		this.lastname = lastname;
    	}
    }

    Thank you in adevance

    Andrea

  • #2
    Any idea here?

    Comment


    • #3
      Eager -&gt; lazy

      Originally posted by atasca View Post
      Any idea here?
      Is it really necessary to load Customer's eagerly every time you load an account. You could make the relationship LAZY. I don't see any code in the page using the Customer entity.

      I only see a repository interface, but no implementation class. I guess Spring data is generating the implementation for you, correct? If NOT there is another option if writing the JPQL query yourself like this:

      SELECT a FROM Account a join fetch a.customer where a.x = ?

      Comment

      Working...
      X