1

I would like to get all distinct values in a particular column. Normally this would be done using the @Query annotation by writing a select with the particular column name.

@Query(value = "SELECT DISTINCT t.columnName FROM #{#entityName} t")
Set<Object> findDistinctValues();

However the column name is dynamic. I need to search for distinct values using the column name argument. Also the repository is generic.

@Query(value = "SELECT DISTINCT t.:columnName FROM #{#entityName} t")
Set<Object> findDistinctValues(@Param("columnName") String columnName);

This doesn't work as the syntax is correct.

I've tried using CriteriaBuilder instead but it always returns the enter row instead of one column. This would mean I would need to use reflection to get the data I need out of the row as I only have the column name.

Is it possible to use Spring JPA to do this? If not, what is the best alternative method?

Michael
  • 3,411
  • 4
  • 25
  • 56
  • See https://stackoverflow.com/questions/36159151/specfiy-columns-which-should-be-considered-for-distinct-calculation for doing this with CriteriaBuilder – Turo Apr 21 '23 at 17:38
  • @Turo doesn't criteria builder always return back the entire entity? I need just the column contents in a set. – Michael Apr 22 '23 at 19:30
  • No, it doesn't. You can specify what you wan't as result, as shown in the example – Turo Apr 22 '23 at 22:44

1 Answers1

0

I think you need just remove the short name of table 't' and leave it like:

@Query(value = "SELECT DISTINCT :columnName FROM #{#entityName}")
Set<Object> findDistinctValues(@Param("columnName") String columnName);

If the first option didn't help you can remove this entityName by passing with @Param as you did to columnName and add a native query to dynamically find value :

@Query(value = "SELECT DISTINCT :columnName FROM :tableName", nativeQuery = true)
Set<Object> findDistinctValues(@Param("columnName") String columnName, @Param("tableName") String tableName);
Feel free
  • 758
  • 5
  • 15
  • In the first example if you pass `example-column` in as the param the result would always be a set containing `example-column` instead of the content. I believe that would also happen in your second example as it would run `SELECT DISTINCT `columnName` ...` too. – Michael Apr 21 '23 at 09:44