findByRouteIdAndDateAndStationName NonUniqueResultException

[almex]

org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:119) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207) at com.sun.proxy.$Proxy61.findByRouteIdAndDateAndStationName(Unknown Source) at be.raildelays.batch.processor.AggregateLineStopProcessor.merge(AggregateLineStopProcessor.java:100) at be.raildelays.batch.processor.AggregateLineStopProcessor.passThrough(AggregateLineStopProcessor.java:51) at be.raildelays.batch.processor.AggregateLineStopProcessor.process(AggregateLineStopProcessor.java:39) at be.raildelays.batch.processor.AggregateLineStopProcessor.process(AggregateLineStopProcessor.java:22)

[almex]

I've tried the query below and it return no row :

SELECT ROUTE_ID, STATION_ID, DATE
FROM LINE_STOP, TRAIN_LINE
WHERE STATION_ID = 13354
AND TRAIN_ID = TRAIN_LINE.ID
AND DATE = TO_DATE('06/02/2016', 'DD/MM/YYYY')
GROUP BY ROUTE_ID, STATION_ID, DATE
HAVING COUNT(*) > 1;

But without HAVING COUNT(*) > 1 it return a result

[almex]

I've try this query below and it still return no row :

SELECT ROUTE_ID, STATION_ID, DATE
FROM LINE_STOP, TRAIN_LINE, STATION
WHERE date = TO_DATE('06/02/2016', 'DD/MM/YYYY')
AND LINE_STOP.TRAIN_ID = TRAIN_LINE.ID
AND LINE_STOP.STATION_ID = STATION.ID
AND (STATION.FRENCH_NAME = 'Liège-Guillemins'
OR STATION.ENGLISH_NAME = 'Liège-Guillemins'
OR STATION.DUTCH_NAME = 'Liège-Guillemins')
GROUP BY ROUTE_ID, STATION_ID, DATE
HAVING COUNT(ROUTE_ID) > 1;

[almex]

I've extracted the query from Hibernate and I get this :

SELECT
linestop0_.ID AS ID1_0_,
linestop0_.version AS version2_0_,
linestop0_.ARRIVAL_TIME_DELAY AS ARRIVAL_3_0_,
linestop0_.ARRIVAL_TIME_EXPECTED AS ARRIVAL_4_0_,
linestop0_.CANCELED_ARRIVAL AS CANCELED5_0_,
linestop0_.CANCELED_DEPARTURE AS CANCELED6_0_,
linestop0_.DATE AS DATE7_0_,
linestop0_.DEPARTURE_TIME_DELAY AS DEPARTUR8_0_,
linestop0_.DEPARTURE_TIME_EXPECTED AS DEPARTUR9_0_,
linestop0_.NEXT_ID AS NEXT_ID10_0_,
linestop0_.PREVIOUS_ID AS PREVIOU11_0_,
linestop0_.STATION_ID AS STATION12_0_,
linestop0_.TRAIN_ID AS TRAIN_I13_0_
FROM LINE_STOP linestop0_
CROSS JOIN TRAIN_LINE trainline1_
CROSS JOIN STATION station2_
WHERE linestop0_.TRAIN_ID = trainline1_.ID
AND linestop0_.STATION_ID = station2_.ID
AND linestop0_.DATE = TO_DATE('12/02/2016', 'DD/MM/YYYY')
AND trainline1_.ROUTE_ID = 516
AND (station2_.FRENCH_NAME = 'Liege-Guillemins'
OR station2_.ENGLISH_NAME = 'Liege-Guillemins'
OR station2_.DUTCH_NAME = 'Liege-Guillemins');

Which return only one row :

1146 1 0 19:01:00 false false 2016-02-12 60000 19:04:00 1145 1147 8 90

[almex]

I've upgrade to Hibernate 5.1.0 + made usage RepositoryItemWriter instead of JpaItemWriter.

[almex]

I've modified the code to get a List<LineStop> instead of a LineStop and I retrieve 15 LineStop
It seems to be the content of the linked list from the root LineStop.

[almex]

Seems to be N+1 SELECT problem with a OneToOne mapping. I'm trying to add @PrimaryKeyJoinColumn and left join fetch to JQL queries :

public class LineStop {
    @OneToOne(fetch = FetchType.EAGER)
    @PrimaryKeyJoinColumn(name = "PREVIOUS_ID")
    protected LineStop previous;

    @OneToOne(fetch = FetchType.EAGER)
    @PrimaryKeyJoinColumn(name = "NEXT_ID")
    protected LineStop next;
} 

[almex]

Removed the @PrimaryKeyJoinColumn and trying JPA Criteria API instead...

[almex]

JPA Criteria API seems to solve the problem!

[almex]

I had to implement Persistable in the AbstractEntityto override isNew()method but now I get :

org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction

[almex]

Disabled Optimistic Locking by removing @Versionfrom the AbstractEntity