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?

Solution 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();

Solution 2

You can use a JPQL query for this, with the @org.springframework.data.jpa.repository.Query annotation:

@Query("select distinct state from CalloutRequest")
List<String> findDistinctStates();

Comments

[Rasmus Sjørslev]

I have updated the question with why i asked for the custom query initially to show that i need to use it to gather a count of the Distinct values returned.

[Luís Soares]

I don't see the issue creating an enum. Just create one with all the possible values: docs.oracle.com/javase/tutorial/java/javaOO/enum.html Then create a field under CalloutRequest of that enum type. Finally, annotate it with: @Enumerated(EnumType.String) tomee.apache.org/examples-trunk/jpa-enumerated/README.html

[Rasmus Sjørslev]

The fact that this is my first Java application is the issue, i simply didn't know how to do it, but thank you for clarifying.

[Rasmus Sjørslev]

This answer is correct as well as it does answer the headline of my question. My reason for marking @Luis Soares answer as the correct one was only due to the fact that he expanded upon the solution and provided guidance on the enum approach. Thank you -

[Luís Soares]

That's fine. If you need help I can send you an example

[Jefferson Lima]

This didn't work for me, I get the error Couldn't find PersistentEntity for type class java.lang.String. Apparently, you can't return a list of strings from the repository.