CODEWITHSUNDEEP
sqlspringannotationsspring-data-jpa
AliReza
AliReza

Reputation: 776

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

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?

Upvotes: 0

Views: 1439

Answers (5)

AliReza
AliReza

Reputation: 776

"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

Upvotes: 0

Omid P
Omid P

Reputation: 211

There are two ways to handle your situation.

The hard way is using RepositoryFactoryBean as follow

  1. 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;
    }

}

}

  1. create Dao interface

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

  2. 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
}

    }

  3. 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.

Upvotes: 0

flexguse
flexguse

Reputation: 479

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.

Upvotes: 0

isah
isah

Reputation: 5351

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.

Upvotes: 0

Barath
Barath

Reputation: 5283

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/

Upvotes: 2

Related Questions