Native SQL select query using Spring JPA Data Annotation @Query, to cover non-empty, empty and null values at the same time

Question

I have something like this in my repository class in a Spring project:

@Query(value = "SELECT * FROM accounts WHERE (first_name LIKE %:firstName% AND last_name LIKE %:lastName%)", nativeQuery = true)
public List<Account> searchByFirstnameAndLastname(@Param("firstName")String firstName,@Param("lastName")String lastName);

I want it to return everything if the parameters are not provided. Even the ones with null firstname/lastname. And it ignores the null values because of the wildcard used. Since null is different from ''.

I was thinking of an if-statement structure and building the query in runtime based on the provided parameters and then setting the value for the @Query annotation.

I tried generating the where clause and passing it as a parameter but it didn't work. I guess the way Spring Data JPA processes the value of this annotation caused it.

Any idea what is the best solution to this?

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.single-repository-behaviour, https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#core.extensions.querydsl are two documented ways. — JB Nizet, Aug 17 '17 at 17:11

Barath · Answer 1 · 2017-08-17T17:10:25.487

2

Have you tried containing keyword like below :

   List<Account> findByFirstnameContainingAndLastNameContaining(String firstName,String lastName);

Docs: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/

edited Aug 17 '17 at 17:10

answered Aug 17 '17 at 17:05

Barath

5,093
1
17
42

I want it to load everything if the parameters are not provided. Even the ones with null firstname/lastname. – AliReza Aug 17 '17 at 17:16
@Akbari you can simply handle no parameters in a logic ? if (param) call this or else just call findAll() to find all the entries – Barath Aug 17 '17 at 17:30
I have no idea why I forgot to answer you. And thank you for the comment. it helped. – AliReza Aug 24 '17 at 01:35
@Akbari If it has helped you, Please accept it as answer – Barath Aug 24 '17 at 04:11

score 0 · Answer 2 · answered Aug 17 '17 at 17:14

0

You cannot go far with @Query

For dynamic queries(with many optional filters), the way to go is using Criteria API or JPQL. I suggest the Criteria API as it is object oriented and suitable for dynamic queries.

answered Aug 17 '17 at 17:14

isah

5,221
3
26
36

score 0 · Answer 3 · answered Aug 17 '17 at 21:31

I would suggest to use QueryDSL. It is mentioned in the docs JB Nizet already posted. There is is nice but quite old tutorial here.

With QueryDSL it is very convenient to create your queries dynamically and it is easier to understand than the JPA Criteria API. The only difficulty in using QueryDSL is the need to automatically create the query objects from your entities but this can be automated by using maven.

score 0 · Answer 4 · answered Aug 19 '17 at 05:06

There are two ways to handle your situation.

The hard way is using RepositoryFactoryBean as follow

create a custom RepositoryFactoryBean

public class DaoRepositoryFactoryBean, T, I extends Serializable> extends JpaRepositoryFactoryBean {

@Override
protected RepositoryFactorySupport createRepositoryFactory(EntityManager entityManager)
{
    return new DaoRepositoryFactory(entityManager);
}

private static class DaoRepositoryFactory<E extends AbstractEntity, I extends Serializable> extends JpaRepositoryFactory
{

    private EntityManager entityManager;


    public DaoRepositoryFactory(EntityManager entityManager)
    {
        super(entityManager);
        this.entityManager = entityManager;

    }

    @Override
    protected Object getTargetRepository(RepositoryMetadata metadata)
    {
        return new DaoImpl<E>((Class<E>) metadata.getDomainType(), entityManager);
    }

    @Override
    protected Class<?> getRepositoryBaseClass(RepositoryMetadata metadata)
    {
        return Dao.class;
    }

}

}

create Dao interface

@NoRepositoryBean public interface Dao extends JpaRepository { List findByParamsOrAllWhenEmpty(); }

create your implementation

@Transactional(readOnly = true) public class DaoImpl extends SimpleJpaRepository implements Dao { private EntityManager entityManager;

public DaoImpl(Class<E> domainClass, EntityManager em)
{
    super(domainClass, em);
    this.entityManager = em;
    this.domainClass = domainClass;
}

List<E> findByParamsOrAllWhenEmpty()
{
    //implement your custom query logic
    //scan your domainClass methods for Query anotations and do the rest
}

}

introduce it to Spring Jpa Data

jpa:repositories base-package="" query-lookup-strategy="" factory-class="com.core.dao.DaoRepositoryFactoryBean"

The easy way using Custom Impl which in this case you can't use @Query annotation.

score 0 · Accepted Answer · answered Oct 13 '17 at 19:57

"coalesce" on MySQL or "IsNull" on SQL Server is my preferred solution. They return back the first non-null value of a list and you may use it as a trick to deal with an empty string just like a null:

@Query(value = "SELECT * FROM accounts WHERE (COALESCE(first_name,'') LIKE %:firstName% AND COALESCE(last_name,'') LIKE %:lastName%)", nativeQuery = true)
public List<Account> searchByFirstnameAndLastname(@Param("firstName")String firstName,@Param("lastName")String lastName);

Thanks to the questioner and the answerer :D at this page:

like '%' does not accept NULL value

Native SQL select query using Spring JPA Data Annotation @Query, to cover non-empty, empty and null values at the same time

5 Answers5