|
When creating a query using EntityGraph feature, and specifying 2 subgraph, or 4 subgraph for this case, generates an invalid SQL.
Domain model referenced in testecase attached.
Test case:
package co.noproblem.sanroque.business.integration;
import static org.junit.Assert.*;
import java.util.List;
import javax.inject.Inject;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.PersistenceUnitUtil;
import org.jboss.arquillian.container.test.api.Deployment;
import org.jboss.arquillian.junit.Arquillian;
import org.jboss.arquillian.junit.InSequence;
import org.jboss.arquillian.persistence.Cleanup;
import org.jboss.arquillian.persistence.TestExecutionPhase;
import org.jboss.shrinkwrap.api.ShrinkWrap;
import org.jboss.shrinkwrap.api.spec.WebArchive;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import com.noproblem.sanroque.business.TransactionLocal;
import com.noproblem.sanroque.model.Purchase;
import com.noproblem.sanroque.model.Supplier;
@RunWith(Arquillian.class)
@Cleanup(phase=TestExecutionPhase.NONE)
/**
-
TransactionLocal business interface integration tests
-
@author Win 7
*
*/
public class TransactionsFindOperationsIT {
@Deployment
public static WebArchive createDeployment() { return ShrinkWrap.create(WebArchive.class, "srtxtest.war") .addPackage("com.noproblem.jess.utils") .addPackage("com.noproblem.reportengine.model") .addPackage("com.noproblem.sanrocko.exceptions") .addPackage("com.noproblem.sanrocko.utils") .addPackage("com.noproblem.sanroque.business") .addPackage("com.noproblem.sanroque.model") .addAsResource("META-INF/test-persistence.xml", "META-INF/persistence.xml") .addAsWebInfResource("test-beans.xml", "beans.xml") ; }
@Inject
private TransactionLocal tx;
@PersistenceContext
private EntityManager em;
private PersistenceUnitUtil puUtil;
@Before
public void setup() { puUtil = em.getEntityManagerFactory().getPersistenceUnitUtil(); }
@Test
@InSequence(3)
public void testFindPurchaseWithTXBYId() { Purchase p = tx.findPurchaseWithTXBYId(18); assertTrue(puUtil.isLoaded(p, "transactionId")); assertTrue(puUtil.isLoaded(p, "supplierId")); assertTrue(puUtil.isLoaded(p.getSupplierId(), "party")); assertTrue(puUtil.isLoaded(p.getTransactionId(), "companyId")); assertTrue(puUtil.isLoaded(p.getTransactionId().getCompanyId(), "party")); }
}
TransactionLocal implementation:
@Override
public Purchase findPurchaseWithTXBYId(Integer id) { EntityGraph<Purchase> egp = em.createEntityGraph(Purchase.class); Subgraph txSg = egp.addSubgraph("transactionId");//.addSubgraph("companyId").addSubgraph("party"); Subgraph spSg = egp.addSubgraph("supplierId"); txSg.addAttributeNodes("companyId"); spSg.addAttributeNodes("party"); //txSg.addAttributeNodes("companyId"); Purchase p = em .createQuery("select P from Purchase P where P.id = :id", Purchase.class).setParameter("id", id) .setHint("javax.persistence.loadgraph", egp).getSingleResult(); // em.refresh(p.getTransactionId()); // TODO: change or optimize // p.updateTotalAfterAccountMovement(); // TODO: change or optimize return p; }
package com.noproblem.sanroque.business;
import com.noproblem.jess.utils.DateUtils;
import com.noproblem.reportengine.model.SQLParameter;
import com.noproblem.sanroque.model.Article;
import com.noproblem.sanroque.model.ArticleCompany;
import com.noproblem.sanroque.model.ArticleCompanyHistory;
import com.noproblem.sanroque.model.ArticleCustomer;
import com.noproblem.sanroque.model.ArticleCustomerHistory;
import com.noproblem.sanroque.model.Category;
import com.noproblem.sanroque.model.Company;
import com.noproblem.sanroque.model.Customer;
import com.noproblem.sanroque.model.MeasureUnit;
import com.noproblem.sanroque.model.Purchase;
import com.noproblem.sanroque.model.PurchasedItem;
import com.noproblem.reportengine.model.SQLQuery;
import com.noproblem.sanroque.model.Account;
import com.noproblem.sanroque.model.AccountMovement;
import com.noproblem.sanroque.model.AccountType;
import com.noproblem.sanroque.model.Party;
import com.noproblem.sanroque.model.Sale;
import com.noproblem.sanroque.model.SoldItem;
import com.noproblem.sanroque.model.Supplier;
import com.noproblem.sanroque.model.Transaction;
import com.noproblem.sanroque.model.User;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.TimeZone;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.annotation.Resource;
import javax.ejb.SessionContext;
import javax.ejb.Stateless;
import javax.inject.Inject;
import javax.persistence.EntityGraph;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.Subgraph;
import javax.persistence.TemporalType;
import javax.persistence.TypedQuery;
@Stateless
public class TransactionBean implements TransactionLocal {
@Override
public Purchase findPurchaseWithTXBYId(Integer id) { EntityGraph<Purchase> egp = em.createEntityGraph(Purchase.class); Subgraph txSg = egp.addSubgraph("transactionId");//.addSubgraph("companyId").addSubgraph("party"); Subgraph spSg = egp.addSubgraph("supplierId"); txSg.addAttributeNodes("companyId"); spSg.addAttributeNodes("party"); //txSg.addAttributeNodes("companyId"); Purchase p = em .createQuery("select P from Purchase P where P.id = :id", Purchase.class).setParameter("id", id) .setHint("javax.persistence.loadgraph", egp).getSingleResult(); // em.refresh(p.getTransactionId()); // TODO: change or optimize // p.updateTotalAfterAccountMovement(); // TODO: change or optimize return p; }
}
This is the logged SQL Query that hibernate tries to execute:
16:39:07,911 INFO [stdout] (default task-2) Hibernate:
16:39:07,912 INFO [stdout] (default task-2) select
16:39:07,912 INFO [stdout] (default task-2) purchase0_.id as id1_20_0_,
16:39:07,912 INFO [stdout] (default task-2) transactio1_.id as id1_29_1_,
16:39:07,912 INFO [stdout] (default task-2) company2_.id as id1_9_2_,
16:39:07,912 INFO [stdout] (default task-2) supplier3_.id as id1_28_3_,
16:39:07,913 INFO [stdout] (default task-2) party4_.id as id1_18_4_,
16:39:07,913 INFO [stdout] (default task-2) purchase0_.comments as comments2_20_0_,
16:39:07,913 INFO [stdout] (default task-2) purchase0_.supplier_id as supplier4_20_0_,
16:39:07,913 INFO [stdout] (default task-2) purchase0_.totalAmount as totalAmo3_20_0_,
16:39:07,913 INFO [stdout] (default task-2) purchase0_.transaction_id as transact5_20_0_,
16:39:07,914 INFO [stdout] (default task-2) transactio1_.company_id as company_3_29_1_,
16:39:07,914 INFO [stdout] (default task-2) transactio1_.transactionDate as transact2_29_1_,
16:39:07,914 INFO [stdout] (default task-2) transactio1_.username as username4_29_1_,
16:39:07,915 INFO [stdout] (default task-2) company2_.active as active2_9_2_,
16:39:07,915 INFO [stdout] (default task-2) company2_.parentCompany_id as parentCo3_9_2_,
16:39:07,915 INFO [stdout] (default task-2) company2_.party_id as party_id4_9_2_,
16:39:07,915 INFO [stdout] (default task-2) supplier3_.active as active2_28_3_,
16:39:07,915 INFO [stdout] (default task-2) supplier3_.company_id as company_3_28_3_,
16:39:07,916 INFO [stdout] (default task-2) supplier3_.party_id as party_id4_28_3_,
16:39:07,916 INFO [stdout] (default task-2) party4_.active as active2_18_4_,
16:39:07,916 INFO [stdout] (default task-2) party4_.code as code3_18_4_,
16:39:07,916 INFO [stdout] (default task-2) party4_.displayName as displayN4_18_4_,
16:39:07,916 INFO [stdout] (default task-2) party4_.documentType_id as document5_18_4_
16:39:07,916 INFO [stdout] (default task-2) from
16:39:07,917 INFO [stdout] (default task-2) Purchase purchase0_
16:39:07,917 INFO [stdout] (default task-2) left outer join
16:39:07,917 INFO [stdout] (default task-2) TX transactio1_
16:39:07,917 INFO [stdout] (default task-2) on purchase0_.transaction_id=transactio1_.id
16:39:07,917 INFO [stdout] (default task-2) left outer join
16:39:07,918 INFO [stdout] (default task-2) Company company2_
16:39:07,918 INFO [stdout] (default task-2) on transactio1_.company_id=company2_.id cross
16:39:07,918 INFO [stdout] (default task-2) join
16:39:07,918 INFO [stdout] (default task-2)
16:39:07,918 INFO [stdout] (default task-2) left outer join
16:39:07,918 INFO [stdout] (default task-2) Supplier supplier3_
16:39:07,919 INFO [stdout] (default task-2) on purchase0_.supplier_id=supplier3_.id
16:39:07,919 INFO [stdout] (default task-2) left outer join
16:39:07,919 INFO [stdout] (default task-2) Party party4_
16:39:07,919 INFO [stdout] (default task-2) on supplier3_.party_id=party4_.id
16:39:07,919 INFO [stdout] (default task-2) where
16:39:07,919 INFO [stdout] (default task-2) purchase0_.id=?
And this is the thrown exception:
16:39:07,964 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-2) SQL Error: 1064, SQLState: 42000
16:39:07,965 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 'left outer join Supplier supplier3_ on purchase0_.supplier_id=supplier3_.id left' at line 1
16:39:07,968 ERROR [org.jboss.as.ejb3] (default task-2) javax.ejb.EJBTransactionRolledbackException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
|
