almex / Raildelays

Fill-in Excel sheet with delays from belgian railway company
http://repo1.maven.org/maven2/com/github/almex/
MIT License
7 stars 3 forks source link

findByRouteIdAndDateAndStationName NonUniqueResultException #66

Closed almex closed 8 years ago

almex commented 8 years ago

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 commented 8 years ago

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 commented 8 years ago

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 commented 8 years ago

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

SELECT linestop0_.ID AS ID10, linestop0_.version AS version20, linestop0_.ARRIVAL_TIME_DELAY AS ARRIVAL_30, linestop0_.ARRIVAL_TIME_EXPECTED AS ARRIVAL_40, linestop0_.CANCELED_ARRIVAL AS CANCELED50, linestop0_.CANCELED_DEPARTURE AS CANCELED60, linestop0_.DATE AS DATE70, linestop0_.DEPARTURE_TIME_DELAY AS DEPARTUR80, linestop0_.DEPARTURE_TIME_EXPECTED AS DEPARTUR90, linestop0_.NEXT_ID AS NEXT_ID100, linestop0_.PREVIOUS_ID AS PREVIOU110, linestop0_.STATION_ID AS STATION120, linestop0_.TRAIN_ID AS TRAIN_I130 FROM LINESTOP linestop0 CROSS JOIN TRAINLINE trainline1 CROSS JOIN STATION station2 WHERE linestop0.TRAINID = trainline1.ID AND linestop0_.STATIONID = station2.ID AND linestop0_.DATE = TODATE('12/02/2016', 'DD/MM/YYYY') AND trainline1.ROUTEID = 516 AND (station2.FRENCHNAME = 'Liege-Guillemins' OR station2.ENGLISHNAME = '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 commented 8 years ago

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

almex commented 8 years ago

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 commented 8 years ago

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 commented 8 years ago

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

almex commented 8 years ago

JPA Criteria API seems to solve the problem!

almex commented 8 years ago

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 commented 8 years ago

Disabled Optimistic Locking by removing @Versionfrom the AbstractEntity