ORACLE SQL Difference between two dates hibernate

Question

I need to calculate the difference between two dates into SQL dialect into my hibernate repository, i tried many solutions but for now it's not working, i keep getting this error:

org.hibernate.MappingException: No Dialect mapping for JDBC type: -104

NOTE: The query is working directly into DBeaver in SQL, but not into Spring.

I'm working with Springboot, hibernate, oracle.

I have to expose the calculated new time (as "Duration") trough my springboot REST controller into json like this format: HH:mm:ss The idea is that i have a datatable on the front-end side, showing some info into a table, and i would like to show the calculated time.

Here is my repository:

public interface BatchInfosJoinRepository extends JpaRepository<BatchInfosJoin, Long> {
    @Query(value = "SELECT ji.JOB_NAME, bse.STEP_NAME, bse.STATUS,bse.START_TIME, bse.END_TIME, bse.END_TIME - bse.START_TIME AS DATE_DIFF " +
            " FROM BATCH_JOB_EXECUTION bje INNER JOIN BATCH_STEP_EXECUTION bse ON bje.JOB_EXECUTION_ID = bse.JOB_EXECUTION_ID " +
            " INNER JOIN BATCH_JOB_INSTANCE ji ON ji.JOB_INSTANCE_ID = bje.JOB_INSTANCE_ID", nativeQuery = true)
    Set<Object[]> fetchStepJoin();
}

My model entity "BatchInfoJoin":

@Entity
public class BatchInfosJoin {

    @Id
    @Column(name = "step_execution_id")
    private Long stepExecutionId;

    @Column(name = "job_name")
    private String jobName;
    @Column(name = "step_name")
    private String stepName;
    @Column(name = "status")
    private String status;
    @Column(name = "start_time")
    private Date startTime;
    @Column(name = "end_time")
    private Date endTime;
    @Column(name = "date_diff")
    private Date dateDiff;
}

Or maybe should i calculate it using an special "Util" class in Java?

https://vladmihalcea.com/hibernate-no-dialect-mapping-for-jdbc-type/ https://stackoverflow.com/questions/12985533/getting-org-hibernate-mappingexception-no-dialect-mapping-for-jdbc-type-4-exc — XtremeBaumer, Nov 18 '19 at 15:08
@XtremeBaumer : Thanks for the links, i have already seen them boths (i can' understand the first one), but could someone could provide a more specific answer to my problem? For the second, how can i use the whole record (as mentionned into the stackoverflow link) if want to make a precise select and return specific columns to my controller? — JibZ, Nov 18 '19 at 15:17
I think this is the answer you are looking for : https://stackoverflow.com/a/27166944/10496246 — , Nov 19 '19 at 18:23

score 1 · Accepted Answer · answered Nov 18 '19 at 15:12

1

Replace

bse.END_TIME - bse.START_TIME AS DATE_DIFF

with

(bse.END_TIME - bse.START_TIME)*60*60*24 AS DATE_DIFF

answered Nov 18 '19 at 15:12

Arvind Kumar Avinash

71,965
6
74
110

ORACLE SQL Difference between two dates hibernate

1 Answers1