Slow MySQL query performance is all too common. Of course it is. A good way to go is, naturally, a dedicated profiler that actually understands the ins and outs of MySQL.

The Jet Profiler was built for MySQL only , so it can do things like real-time query performance, focus on most used tables or most frequent queries, quickly identify performance issues and basically help you optimize your queries.

Critically, it has very minimal impact on your server's performance, with most of the profiling work done separately - so it needs no server changes, agents or separate services.

Basically, you install the desktop application, connect to your MySQL server , hit the record button, and you'll have results within minutes:

out the Profiler

1. Overview

In this tutorial, we’ll show how to handle null parameters in Spring Data JPA .

In some cases, when we search for records by parameters, we want to find rows with null as the field value. Other times, we want to ignore a null and skip that field in our query.

Below we’ll show how to implement each of these.

2. Quick Example

Let’s say we have a Customer entity:

@Entity
public class Customer {
    @GeneratedValue
    private long id;
    private String name;
    private String email;
    public Customer(String name, String email) {
        this.name = name;
        this.email = email;
    // getters/setters
Also, we have a JPA repository:

public interface CustomerRepository extends JpaRepository<Customer, Long> { 
   // method1
   // method2
We want to search for customers by name and email.
For this purpose, we’ll write two methods that handle null parameters differently.
3. Ways to Handle Null Parameters
First, we’ll create a method that interprets null values of the parameters as IS NULL. Then we’ll create a method that ignores null parameters and excludes them from the WHERE clause.
3.1. IS NULL Query
The first method is very simple to create because null parameters in the query methods are interpreted as IS NULL by default.
Let’s create the method:

List<Customer> findByNameAndEmail(String name, String email);
Now if we pass a null email, the generated JPQL will include the IS NULL condition:
customer0_.email is null
To demonstrate this, let’s create a test.
First, we’ll add some customers to the repository:
@Before
public void before() {
    entityManager.persist(new Customer("A", "[email protected]"));
    entityManager.persist(new Customer("D", null));
    entityManager.persist(new Customer("D", "[email protected]"));
Now let’s pass “D” as the value of the name parameter and null as the value of the email parameter to our query method.
We can see that exactly one customer will be found:

List<Customer> customers = repository.findByNameAndEmail("D", null);
assertEquals(1, customers.size());
Customer actual = customers.get(0);
assertEquals(null, actual.getEmail());
assertEquals("D", actual.getName());
3.2. Avoid null Parameter With Alternative Methods
Sometimes we want to ignore some parameters and not include their corresponding fields in the WHERE clause.
We can add more query methods to our repository.
For example, to ignore email, we can add a method that only accepts name:
 List<Customer> findByName(String name);
But this way of ignoring one of our columns scales poorly as the number increases since we would have to add many methods to achieve all the combinations.
3.3. Ignoring null Parameters Using the @Query Annotation
We can avoid creating additional methods by using the @Query annotation and adding a small complication to the JPQL statement:
@Query("SELECT c FROM Customer c WHERE (:name is null or c.name = :name) and (:email is null"
  + " or c.email = :email)")
List<Customer> findCustomerByNameAndEmail(@Param("name") String name, @Param("email") String email);
Notice that if the email parameter is null, the clause is always true and so doesn’t influence the whole WHERE clause:
:email is null or s.email = :email
Let’s make sure that this works:
List<Customer> customers = repository.findCustomerByNameAndEmail("D", null);
assertEquals(2, customers.size());
We found two customers whose name is “D”, ignoring their emails.

The generated JPQL WHERE clause looks like this:
where (? is null or customer0_.name=?) and (? is null or customer0_.email=?)
With this method, we are putting trust in the database server to recognize the clause regarding our query parameter being null and optimize the execution plan of the query so that it doesn’t have a significant performance overhead. There could be a performance overhead for some queries or database servers, especially involving a huge table scan.
Please note that the above methods for querying null parameters also work if the parameter type is of type – UUID. We can find examples in our Github repo.
4. Conclusion
In this article, we demonstrated how Spring Data JPA interprets null parameters in query methods and how to change the default behaviour.
Perhaps in the future, we’ll be able to specify how to interpret null parameters using the @NullMeans annotation. Notice that it’s a proposed feature at this time and is still under consideration.
To sum up, there are two main ways to interpret null parameters, and they would both be provided by the proposed @NullMeans annotation:
IS (is null) – the default option demonstrated in Section 3.1.
IGNORED (exclude a null parameter from the WHERE clause) – achieved either by extra query methods (Section 3.2.) or by using a workaround (Section 3.3.)
As usual, the complete source code is available on GitHub.
Partner – Aegik AB – NPI EA (tag = SQL)


Slow MySQL query performance is all too common. Of course
it is.
The Jet Profiler was built entirely for MySQL, so it's
fine-tuned for it and does advanced everything with relaly minimal
impact and no server changes.
out the Profiler

Partner – Codium – NPI EA (cat = Testing)


Explore the secure, reliable, and high-performance Test
Execution Cloud built for scale. Right in your IDE:
CodiumAI. Meaningful Code Tests for Busy Devs

Basically, write code that works the way you meant it to.
Partner – Machinet – NPI EA (cat = Testing)


AI is all the rage these days, but for very good reason. The
highly practical coding companion, you'll get the power of
AI-assisted coding and automated unit test generation.

Machinet's Unit Test AI Agent utilizes your own project
context to create meaningful unit tests that intelligently aligns
with the behavior of the code.

Simplify Your Coding Journey with Machinet AI:
>> Install Machinet
AI in your IntelliJ
Course – LSD (cat=Persistence)

Get started with Spring Data JPA through the reference Learn
Spring Data JPA
CHECK OUT THE COURSE

res – Persistence (eBook) (cat=Persistence)

An intro to Spring Data, JPA

and Transaction Semantics Details with JPA
Get Persistence Right with Spring
Download the E-book