Using distinct in Spring data over multiple columns

Question

My domain model is like this:

CollectedData {
  String name;
  String description;
  int count;
  int xAxis,
  int yAxis
}

Using Spring data repository query, I would like to retrieve all the unique rows (unique with name, xAxis, yAxis)

I am trying something like this

@Query("select distinct a.name, a.xAxis, a.yAxis from CollectedData a")
List<CollectedData> findAllDistinctData();

So, when I do

List<CollectedData> records= findAllDistinctData();
for (CollectedData record : records) { //Exception on this line
 }

Exception [Ljava.lang.Object; cannot be cast to CollectedData.

Is there any other way to write query for this ?

score 15 · Accepted Answer · answered Jun 04 '13 at 11:06

15

@Query return ArrayList of Object(s) instead of specific type of object. so you have to define some thing like

@Query("select distinct a.name, a.xAxis, a.yAxis from CollectedData a")
List<Object> findAllDistinctData();

then cast according to your requirement,

List<Object> cdataList=findAllDistinctData();
for (Object cdata:cdataList) {
   Object[] obj= (Object[]) cdata;
     String name = (String)obj[0];
    String description = (String)obj[1];;
 ...
  }

answered Jun 04 '13 at 11:06

bNd

7,512
7
39
72

Additional and important remark. Simply use a very straightforward REST controller and avoid using Spring Data when using distinct for multiple columns. – Wim Van den Brande Jun 19 '20 at 09:42
In my case, I needed to convert `List – cd491415 Oct 17 '21 at 22:43

score 1 · Answer 2 · answered Jun 29 '17 at 06:39

Instead of returning an object you can use JPA's constructor expression feature to return a more specific object holding only the columns you're interested in. See also following answer:

JPQL Constructor Expression - org.hibernate.hql.ast.QuerySyntaxException:Table is not mapped

According to your example you could create a new Object with only the columns you are interested in:

SELECT DISTINCT new com.mypackage.MyInterestingCollectedData(a.name, a.xAxis, a.yAxis) from CollectedData a

score 0 · Answer 3 · answered Mar 07 '22 at 10:03

If you want to select complete object based on distinct values of multiple columns,

In that case the native query would be the option.

e.g.

@Query(
        value = "select distinct on (column1, column2, column3) * From my_table where someId=: order by column1 asc,column2 desc,column3 desc,column4 desc",
        nativeQuery = true
    )
    fun finalAllDistinctBy(containerId: String): List<MyTable>

Using distinct in Spring data over multiple columns

3 Answers3