CODEWITHSUNDEEP
javaspringoracle-databasejpaspring-data-jpa
Abhishek Patil
Abhishek Patil

Reputation: 1445

Spring JPA Java [ ORA-01861 literal does not match format string ]

I receive a date in 'yyyy-MM-dd' format when I am trying to save the date in database via spring JPA save() method I am getting error 

java.sql.SQLException: ORA-01861: literal does not match format string

oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:966)
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1170)
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3423)
org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:208)
org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:62)
org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3124)
org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3581)
org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:104)
org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:465)
org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:351)
org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:350)
org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:56)
org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1258)
org.hibernate.jpa.spi.AbstractEntityManagerImpl.flush(AbstractEntityManagerImpl.java:1335)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:606)
org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:291)
com.sun.proxy.$Proxy48.flush(Unknown Source)
org.springframework.data.jpa.repository.support.SimpleJpaRepository.flush(SimpleJpaRepository.java:478)
org.springframework.data.jpa.repository.support.SimpleJpaRepository.saveAndFlush(SimpleJpaRepository.java:446)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:606)
org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.executeMethodOn(RepositoryFactorySupport.java:434)
org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:419)
org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:391)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)

Below is my entity model sample 

import java.util.Date;
import javax.persistence.*;
@Entity
@Table(name = "INVOICE")
public class PublishInvoice {
    @Id
    private int INVOICE_ID;

    @javax.persistence.Temporal(TemporalType.TIMESTAMP)
    @Column(name = "MATURITY_DATE")
    private Date MATURITY_DATE;

    @Transient
    private String mDATE;
}

I am getting the date from UI in the string which I then convert to util.Date() before saving, by below code

try {
    Date d = new SimpleDateFormat("yyyy-MM-dd").parse(publishInvoice.getmDATE());
    System.out.println(publishInvoice.getmDATE() + "\t" + d.toString());

    publishInvoice.setMATURITY_DATE(d);

} catch (ParseException e) {
    e.printStackTrace();
}
publishInvoiceService.save(publishInvoice);

Below is my DB table

CREATE TABLE "WIZ1"."INVOICE" 
(   
    "MATURITY_DATE" DATE, 
    "INVOICE_ID" NUMBER NOT NULL ENABLE, 
     CONSTRAINT "WIZ_PK" PRIMARY KEY ("INVOICE_ID")
}

Let me know if anything else is required. Any help is appreciated

Upvotes: 1

Views: 3174

Answers (1)

Milan Desai
Milan Desai

Reputation: 1306

I must ask you why @Transient is being used, try removing it if it's not necessity.

See this example to understand ORA-01861 I reckon oracle is trying to do something like this.

Run this in SQL Developer

SELECT TO_DATE (‘20190522’, ‘yyyy-mm-dd’) FROM dual;

and you will get error you're seeing. ORA-01861

Run this to verify how it should work,

SELECT TO_DATE (‘2019-05-22’, ‘yyyy-mm-dd’) FROM dual;

With that being said I have a feeling your code, Date d = new SimpleDateFormat("yyyy-MM-dd").parse(publishInvoice.getmDATE()); is not returning correct date.

I believe java Date could be potential problem here. update database column with field type TIMESTAMP and java mDATE to java.sql.Timestamp;

Upvotes: 2

Related Questions