Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Java Новый топик    Ответить
 spring data jpa, универсальный фильтр на specification, пустой sql запрос  [new]
MarMot
Member

Откуда:
Сообщений: 35
Доброго дня!

Я пытаюсь сделать универсальный фильтр для получаемых объектов. Для этого я использую Specification вида
import app.project.persistence.entity.Model;
import app.project.util.Pair;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Component;
import javax.persistence.criteria.*;
import java.util.List;

@Component
public class TestSpec3 {
    public static Specification<Model> equal(final List<Pair<String, Object>> filter) {
        return new Specification<Model>() {
            @Override
            public Predicate toPredicate(Root<Model> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                Predicate predicate = cb.conjunction();
                for (Pair<String, Object> pair : filter) {
                    String field = pair.getKey();
                    Object value = pair.getValue();
                    predicate.getExpressions().add(cb.equal(root.get(field), value));
                }
                return predicate;
            }
        };
    }
}


Модель описана так:
import javax.persistence.*;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

@Entity(name = "models")
@Table(name = "models")
public class Model implements EntityInterface<Integer> {
    // --------------------------------------------------------------------------------------------------------
    @Id
    @SequenceGenerator(name = "modelsSeq", sequenceName = "models_seq", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "modelsSeq")
    @Column(name = "id_model", unique = true, nullable = false)
    private Integer idModel;
    @Column(name = "id_category")
    private Integer idCategory;
    @Column(name = "id_owner", nullable = false)
    private Integer idOwner;
    @Column(name = "id_format")
    private Integer idFormat;
    // --------------------------------------------------------------------------------------------------------
    @Column(nullable = false)
    private String title;
    @Column
    private String description;
    @Column(nullable = false)
    private BigDecimal price = new BigDecimal(0.00);
    // --------------------------------------------------------------------------------------------------------
    // --------------------------------------------------------------------------------------------------------
    @ElementCollection(fetch = FetchType.EAGER)
    @CollectionTable(name = "model_functionalities",
            joinColumns = @JoinColumn(name = "id_model", referencedColumnName = "id_model", unique = true))
    @Column(name = "id_functionality")
    private List<Integer> idFunctionalities;
    public List<Integer> getIdFunctionalities() { return idFunctionalities; }
    // --------------------------------------------------------------------------------------------------------
    @ElementCollection(fetch = FetchType.EAGER)
    @CollectionTable(name = "collection_models",
            joinColumns = @JoinColumn(name = "id_model", referencedColumnName = "id_model", unique = true))
    @Column(name = "id_collection")
    private List<Integer> idCollections;
    public List<Integer> getIdCollections() { return idCollections; }
... 
геттеры и сеттеры


Репозиторий для Models:
public interface ModelRepository extends PagingAndSortingRepository<Model, Integer>, JpaSpecificationExecutor<Model> {
    Page<Model> findAll(Specification specification, Pageable pageable);
}


Когда в Secification передаем фильтры на простые поля Model, то все работает отлично:
            List<Pair<String, Object>> filter = new ArrayList<>();

            filter.add(new Pair<String, Object>("title", "Test model"));
            filter.add(new Pair<String, Object>("idCategory", 1));

            Specification spec = TestSpec3.equal(filter);

            Page<Model> list = modelRepository.findAll(spec, Constants.DEFAULT_PAGE_REQUEST);
            for (Model model : list.getContent())
                System.out.println("Model:" + model.getTitle());


Но когда пытаемся фильтровать по спискам, которые связанны с другими таблицами:
            List<Pair<String, Object>> filter = new ArrayList<>();

            filter.add(new Pair<String, Object>("title", "Test model"));
            filter.add(new Pair<String, Object>("idCategory", 1));

            List<Integer> idList = new ArrayList<>();
            idList.add(1);
            filter.add(new Pair<String, Object>("idCollections", idList));

            Specification spec = TestSpec3.equal(filter);

            Page<Model> list = modelRepository.findAll(spec, Constants.DEFAULT_PAGE_REQUEST);
            for (Model model : list.getContent())
                System.out.println("Model:" + model.getTitle());


то получаем ошибку:
+
org.springframework.dao.DataIntegrityViolationException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not extract ResultSet
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:192)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:154)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:417)
at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.data.jpa.repository.support.LockModeRepositoryPostProcessor$LockModePopulatingMethodIntercceptor.invoke(LockModeRepositoryPostProcessor.java:92)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
at com.sun.proxy.$Proxy52.findAll(Unknown Source)
at app.madebyy.test.TestQuery.test(TestQuery.java:112)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:232)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:89)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:175)
at org.gradle.api.internal.tasks.testing.junit.JUnitTestClassExecuter.runTestClass(JUnitTestClassExecuter.java:86)
at org.gradle.api.internal.tasks.testing.junit.JUnitTestClassExecuter.execute(JUnitTestClassExecuter.java:49)
at org.gradle.api.internal.tasks.testing.junit.JUnitTestClassProcessor.processTestClass(JUnitTestClassProcessor.java:69)
at org.gradle.api.internal.tasks.testing.SuiteTestClassProcessor.processTestClass(SuiteTestClassProcessor.java:48)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.gradle.messaging.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:35)
at org.gradle.messaging.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:24)
at org.gradle.messaging.dispatch.ContextClassLoaderDispatch.dispatch(ContextClassLoaderDispatch.java:32)
at org.gradle.messaging.dispatch.ProxyDispatchAdapter$DispatchingInvocationHandler.invoke(ProxyDispatchAdapter.java:93)
at com.sun.proxy.$Proxy2.processTestClass(Unknown Source)
at org.gradle.api.internal.tasks.testing.worker.TestWorker.processTestClass(TestWorker.java:105)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.gradle.messaging.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:35)
at org.gradle.messaging.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:24)
at org.gradle.messaging.remote.internal.hub.MessageHub$Handler.run(MessageHub.java:360)
at org.gradle.internal.concurrent.DefaultExecutorFactory$StoppableExecutorImpl$1.run(DefaultExecutorFactory.java:64)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.hibernate.exception.DataException: could not extract ResultSet
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:135)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:89)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2065)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1862)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1838)
at org.hibernate.loader.Loader.doQuery(Loader.java:909)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354)
at org.hibernate.loader.Loader.doList(Loader.java:2551)
at org.hibernate.loader.Loader.doList(Loader.java:2537)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2367)
at org.hibernate.loader.Loader.list(Loader.java:2362)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:496)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:387)
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:229)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1260)
at org.hibernate.internal.QueryImpl.list(QueryImpl.java:103)
at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573)
at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:449)
at org.hibernate.jpa.criteria.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:67)
at org.springframework.data.jpa.repository.query.QueryUtils.executeCountQuery(QueryUtils.java:406)
at org.springframework.data.jpa.repository.support.SimpleJpaRepository.readPage(SimpleJpaRepository.java:433)
at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:332)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.executeMethodOn(RepositoryFactorySupport.java:358)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:343)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
... 55 more
Caused by: org.postgresql.util.PSQLException: No value specified for parameter 2.
at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:228)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:245)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:570)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:305)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:80)
... 87 more


И при этом формируется крайне странный SQL:
 select count(model0_.id_model) as col_0_0_ from models model0_ cross join collection_models idcollecti1_ where model0_.id_model=idcollecti1_.id_model and model0_.title=? and model0_.id_category=1 and .=?


Этот запрос считает полное кол-во объектов в ответе, для того что бы сформировать объект Page<Model>
и вместо ".=?" должно быть "idcollecti1_.id_collection=?" и должен передаваться значение для второго параметра.

Может кто сталкивался с такой проблемой и может подсказать где копать решение?
Спасибо.
30 мар 15, 18:42    [17451468]     Ответить | Цитировать Сообщить модератору
 Re: spring data jpa, универсальный фильтр на specification, пустой sql запрос  [new]
MarMot
Member

Откуда:
Сообщений: 35
Или может есть какой другой способ для универсальной фильтрации запросов в hibernate?
Спасибо
31 мар 15, 12:34    [17453753]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: spring data jpa, универсальный фильтр на specification, пустой sql запрос  [new]
Rebeled
Member

Откуда:
Сообщений: 160
MarMot,

Добавь root.join на collection_models. В фильтрах можно парсить вхождение влаженных сущностей и добавлять их в join
11 май 19, 14:42    [21882482]     Ответить | Цитировать Сообщить модератору
Все форумы / Java Ответить