How to add OR Specification

[chetankpatil]

As of now findAll(DataTablesInput paramDataTablesInput, Specification paramSpecification); method, always ANDs paramSpecification in the criteria query.

How can I OR paramSpecification? Is there a workaround?

[darrachequesne]

Hi! I don't really see a workaround in the current state, apart from extending DataTablesRepositoryImpl itself..

May I ask what is your use case please?

[chetankpatil]

Hello Damien, Thanks for the quick reply. My use case is as follows. I've JPA mapped entities as follows, which I'm displaying on UI using jQuery DataTables and spring-data-jpa-datatables jar.

Menu entity

@Entity
@Table(name = "MENUS")
public class Menu implements {
    @Id
    @Column(name = "ID")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "NAME", length = 255)
    private String name;

    @ManyToMany(fetch = FetchType.LAZY, cascade = { CascadeType.PERSIST, CascadeType.MERGE, CascadeType.DETACH })
    @JoinTable(name = "MENU_TAG", joinColumns = { @JoinColumn(name = "MENU_ID", referencedColumnName = "ID") }, inverseJoinColumns = {
            @JoinColumn(name = "TAG_ID", referencedColumnName = "ID") })
    private Set<Tag> tags = new HashSet<>();

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "RESTAURANT_ID")
    private Restaurant restaurant;
}

Tag entity

@Entity
@Table(name = "TAGS")
public class Tag {
    @Id
    @Column(name = "ID")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "TAG_NAME", length = 50, unique = true)
    private String name;
}

Restaurant entity

@Entity
@Table(name = "RESTAURANTS")
public class Restaurant {
    @Id
    @Column(name = "ID")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "NAME", length = 255)
    private String name;
}

I'm displaying id, name, restaurant.id, restaurant.name of Menus, except for tags. I haven't mapped tags field to DataTables on UI. However, I want the global search to be applied on tags.name as well.

When I add the Specification, by default it ORs id, name, restaurant.id and restaurant.name in the query but ANDs the tags.name. Following is the server-side code.

@RequestMapping(value = "/menuitems", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON_VALUE)
@ResponseBody
public DataTablesOutput<Menu> getAllMenuItems(@Valid DataTablesInput input) {
        String searchValue = input.getSearch().getValue();

        DataTablesOutput<Menu> result = null;
        if (StringUtils.isNotEmpty(searchValue)) {
            result = menuRepo.findAll(input, (root, query, cb) -> {
                               return cb.like(root.join(Menu_.tags).get(Tag_.name), "%" + value.toLowerCase() + "%");  });
        } else {
            result = menuRepo.findAll(input);
        }
           return result;
}

Now, this ORs all the column inputs but ANDs the like clause of Tag.name which gives me incorrect result set. If somehow, I could manage to OR the like clause of Tag.name, I'll get the correct result set.

As per your suggestion, I also tried extending the DataTablesRepository and DataTablesRepositoryImpl and overrode the findAll methods to replace "and"ing with "or"ing but then I started getting the following error.

2016-04-26 16:10:56.981 ERROR o.s.w.c.ContextLoader Context initialization failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataTableRepositoryCustom': Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1578)
.....
.....
Caused by: javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.persistenceException(EntityManagerFactoryBuilderImpl.java:954)
....
....
Caused by: org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.spi.CacheImplementor]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:244)
....
....
Caused by: org.hibernate.cache.CacheException: net.sf.ehcache.CacheException: Another unnamed CacheManager already exists in the same VM. Please provide unique names for each CacheManager in the config or do one of following:
1. Use one of the CacheManager.create() static factory methods to reuse same CacheManager with same name or create one if necessary
2. Shutdown the earlier cacheManager before creating new one with same name.
The source of the existing CacheManager is: DefaultConfigurationSource [ ehcache.xml or ehcache-failsafe.xml ]
    at org.hibernate.cache.ehcache.EhCacheRegionFactory.start(EhCacheRegionFactory.java:90)

For your reference, I've attached the extended interface and class are attached. Also, if I remove these files, and change the code to use original DataTableRepository, the error goes away.

Not sure, where am I going wrong. Please help. Thanks again.

DataTableRepositoryCustom.zip

[darrachequesne]

Couldn't you add a tags.name column in the datatables definition? Something like:

columns : [ {
    data : 'id'
}, {
    data : 'name'
}, {
    data : 'tags.name',
    render: function(data, type, row) {
        return row['tags'] ? row['tags'].map(function (tag) {
          return tag.name;
        }).join(',') : '';
    }
}, {
    data : 'restaurant.name'
}]

Else, the issue you're having net.sf.ehcache.CacheException: Another unnamed CacheManager already exists in the same VM does not seem related to Datatables, maybe some configuration issue?

[chetankpatil]

Problem with adding tags.name in DataTables definition is, tags property from Menu entity is a collection of Tag entity i.e., Set<Tag> tags. There can be multiple Tag entities per Menu entity. Also, I don't want it to be seen on the UI.

You're right, the exception I'm getting isn't related to DataTables. I had missed adding @NoRepositoryBean to DataTableRepositoryCustom interface that I extended from DataTablesRepository and so the error.

That exception is gone now but though I've extended DataTablesRepositoryImpl, it's still calling the original findAll() and is ANDing the tags.name.

I'll try the solution suggested by you though. Thanks a lot.

[chetankpatil]

Hey Damien, Your suggestion worked. Thanks a lot. :+1:

[SaurabhGupta69]

@chetankpatil how did you make it work? Because on creating a custom Repository by extending the default, it says

The constructor DataTablesRepositoryImpl<T,Long>(JpaEntityInformation<T,?>, EntityManager) is not visible

on super public DataTableRepositoryImpl(JpaEntityInformation<T, ?> entityInformation, EntityManager entityManager) { super(entityInformation, entityManager); }

[chetankpatil]

@SaurabhGupta69,

I've attached the implementation in my reply. Here is the link to it.

Also, this is how I've used it in my Repository implementation. That's all.

`public interface RestaurantRepository extends RestaurantRepositoryCustom, DataTablesRepository<Restaurant, Long> {

}`

[SaurabhGupta69]

Yes, I tried the same but it throws following error

[chetankpatil]

@SaurabhGupta69 You are implementing it incorrectly. Please refer to the attached sample classes to understand how I implemented the repository. impl.zip