Spring Data JPA Distinct - Return results from a single column

Question

I have some data that contains a STATE field (String/Text) that defines what state a given request is currently in (e.g. pending, approved denied etc.) and to get all the unique values from that column I can run the following TSQL query

SELECT DISTINCT STATE FROM CALLOUT_REQUEST

where CALLOUT_REQUEST is my table name and STATE being the field which returns something like:

STATE

• approved
• denied
• pending
• ...

However I don't understand how I would turn that into a query in my repository as it seems I need a "by" statement or some other filter mechanism which i can get the STATE based on?

What I am looking to return - as shown in the raw TSQL query above - is some kind of List or Array object which contains all the unique/distinct values in all of the STATE fields.

So in pseudo code i think i am looking for something like this:

String[] states = repository.findDisinctState();

where findDistinctState() would then return an array of sorts.

Hope that makes sense - I am very new to Java and Spring in general so I think I am missing some conceptual knowledge to utilise the above.

UPDATE:

The 'state' concept is closed so i could implement that as an enum - only problem is i dont know how to do that :) Ill look into how i can do that as i think it fits perfectly with what i am trying to achieve.

The List i get from the query provided is intended to be used to get a count of all the occurrences. I had this code before to get a total count for each of the 'states':

Map stats = new HashMap();
String[] states = {"approved", "denied", "pending", "deactivated"};
for (int i = 0; i < states.length; i++) {
    stats.put(states[i], repository.countByState(states[i]));
}

Am i correct in understanding that the states Array that i have in the above code snippet could be turned into an enum and then i dont even need the custom @Query anymore?

Answer 1

If that state concept is closed - you know its possible set of values - it should be an enum.

After that you can create queries that you invoke like:

repository.findByState(State.APPROVED)

If you can't create an enum, you need a separate method to get the distinct values, which can't be provided by JPA, because you need a list of strings and not a list of CalloutRequests. Then you need to specify a query manually like:

@Query("SELECT DISTINCT State FROM CALLOUT_REQUEST")
List<String> findDistinctStates();