Can I use enum parameter into JpaRepository nativeQuery?

Harnessing Spring Expression Language (SpEL)

Add some spring to your steps! With Spring Expression Language (SpEL), you can introduce more flexibility in your queries:

@Query(value = "SELECT * FROM your_table WHERE status = CAST(:#{#status.name()} AS varchar)", nativeQuery = true)
List<YourEntity> findByStatus(@Param("status") ApplicationStatus status);


Just pass the enum as it is to the findByStatus, and let Spring do all the leg work.

Engage with your SQL database's enums

Find yourself dealing with enum types at database level? No worries! Cast them in your @Query itself:

CAST(:#{#status.name()} AS enum_type)


Mastering collection of enums

When you need to include a list of enums in your query, you may find that Spring's projection is a handy tool:

@Query(value = "SELECT * FROM your_table WHERE status IN :statuses", nativeQuery = true)
List<YourEntity> findByStatuses(@Param("statuses") Collection<String> statuses);

// Spring cleaning your Enums
List<YourEntity> result = repository.findByStatuses(YOUR_ENUMS.stream().map(Enum::name).collect(Collectors.toList()));


The stream does the hard work of converting Enums to their name representations.

Tackling nullable enum parameters

Here's how you ensure null safety in your queries when enum parameter may be null:

:#{#paramName != null ? #paramName.name() : 'DEFAULT_VALUE'}


It's like asking: Is this null? If it's not, great! If it's null, here's a default.

Good practices and overlooked situations

Matching Enum Names

Ensure enum names directly match their corresponding database values to avoid painting yourself into a corner of mismatches.

Embrace JPQL

When native queries make you feel like you are juggling with glass balls, consider using JPQL:

@Query("SELECT e FROM Entity e WHERE e.status = :status")
List<YourEntity> findByStatus(@Param("status") ApplicationStatus status);


JPQL takes enum as a parameter and spares you from converting or casting.

Use SpEL with care

Although SpEL is amazing, remember too much magic can turn your pumpkin-query into a mess instead of a chariot.

The Java Persistence Query Language - The Java EE 6 Tutorial. Java-land's official tour guide for JPQL.

5. Youtube Video on Advanced JPA Query Techniques Including Enums - Learn to juggle with enum parameters in style by watching these visual lessons.