problem with mysql time stamp field with Spring Data jpa Date time type while inserting the record into the database

I have added new field of type time stamp using liquibase for my Spring Data Jpa, Angulars and mysql based project as,

 
  <changeSet author="mnkb" id="20151008151559">
           <addColumn schemaName="sample"
                tableName="invoice">       
             <column name="bill_date" type="timestamp"/>
        </addColumn>
    </changeSet>

Open in new window


From my angular js front end request is going as :
   
{"accountNumber":"AC6434364","invoiceNumber":"IN67347643","invoiceAmount":326,"status":"open","ediNumber":"6565EDI","billDate":"2012-05-02T17:07:00.000Z","id":null}

Open in new window


But my REST call Debug log is as follows :


 
   [DEBUG] com.sample.aop.logging.LoggingAspect - Enter: com.sample.web.rest.InvoiceResource.create() with argument[s] = [Invoice{id=null, accountNumber='AC6434364', invoiceNumber='IN67347643', invoiceAmount='326.0', ediNumber='6565EDI', status='open', billDate='2012-05-02T22:37:00.000+05:30'}]
    [DEBUG] com.sample.web.rest.InvoiceResource - REST request to save Invoice : Invoice{id=null, accountNumber='AC6434364', invoiceNumber='IN67347643', invoiceAmount='326.0', ediNumber='6565EDI', status='open', billDate='2012-05-02T22:37:00.000+05:30'}
    Hibernate: insert into invoice (account_number, bill_date, edi_number, invoice_amount, invoice_number, status) values (?, ?, ?, ?, ?, ?)
    [WARN] org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 1292, SQLState: 22001
    [ERROR] org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Data truncation: Incorrect datetime value: '\xAC\xED\x00\x05sr\x00\x16org.joda.time.DateTime\xB8<xdj[\xDD\xF9\x02\x00\x00xr\x00\x1Forg.joda.time.base.BaseDateTime\xFF\xFF\x' for column 'bill_date' at row 1
    [ERROR] com.sample.aop.logging.LoggingAspect - Exception in com.sample.web.rest.InvoiceResource.create() with cause = org.hibernate.exception.DataException: could not execute statement
    org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not execute statement
    	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:259) ~[spring-orm-4.1.7.RELEASE.jar:4.1.7.RELEASE]
    	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:221) ~[spring-orm-4.1.7.RELEASE.jar:4.1.7.RELEASE]
    	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:417) ~[spring-orm-4.1.7.RELEASE.jar:4.1.7.RELEASE]
    	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59) ~[spring-tx-4.1.7.RELEASE.jar:4.1.7.RELEASE]
    	at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213) ~[spring-tx-4.1.7.RELEASE.jar:4.1.7.RELEASE]
    	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147) ~[spring-tx-4.1.7.RELEASE.jar:4.1.7.RELEASE]
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.1.7.RELEASE.jar:4.1.7.RELEASE]

Open in new window



Due to this error this particular record is not getting inserted, earlier when I don't have billDate, the CRUD operation is working fine.

And in Invoce.java, I have
    import org.hibernate.annotations.Cache;
    import org.hibernate.annotations.CacheConcurrencyStrategy;
    import org.joda.time.DateTime;
    
    import javax.persistence.*;
    import java.io.Serializable;
    import java.util.HashSet;
    import java.util.Set;
    import java.util.Objects;
    
     @Column(name = "bill_date")
        private DateTime billDate;
    
    
    		public DateTime getBillDate() {
    			return billDate;
    		}
    
    		public void setBillDate(DateTime billDate) {
    			this.billDate = billDate;
    		}
    
    @Override
        public String toString() {
            return "Invoice{" +
                    "id=" + id +
                    ", accountNumber='" + accountNumber + "'" +
                    ", invoiceNumber='" + invoiceNumber + "'" +
                    ", invoiceAmount='" + invoiceAmount + "'" +
                    ", ediNumber='" + ediNumber + "'" +
                    ", status='" + status + "'" +
                    ", billDate='" + billDate + "'" +
                    '}';
        }

Open in new window


The following error is shown in my browser console.
    POST http://localhost:8080/api/invoices?cacheBuster=1444330431628 500 (Internal Server Error)

    timestamp: 1444330431767, status: 500, error: "Internal Server Error",…}
    error: "Internal Server Error"
    exception: "org.springframework.dao.DataIntegrityViolationException"
    message: "could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not execute statement"
    path: "/api/invoices"
    status: 500
    timestamp: 1444330431767

Open in new window

Too SmartAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gurpsbassiCommented:
I don't think JPA supports joda datetime officially. You normally have to write a convertor to convert between java.sql.Date and joda DateTime.

Try using java.util.Date instead to confirm this is the problem.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Too SmartAuthor Commented:
Yes that is only the problem but if I change it to java.util.Date and my column to

 @Column(name = "bill_date")
    private Date billDate;

Open in new window


in Invoice.java, the accurate date is not getting stored.

For example from the front end console I have the following log:
{"accountNumber":"TRE765656","invoiceNumber":"IVN63565","invoiceAmount":3333333,"status":"Open","ediNumber":"DE524324345","billDate":"2014-10-10T03:25:00.000Z","id":null}

Open in new window


But in the server log   see,

[DEBUG] com.trace.aop.logging.LoggingAspect - Exit: com.sample.web.rest.InvoiceResource.create() with result = <201 Created,Invoice{id=16, accountNumber='TRE765656', invoiceNumber='IVN63565', invoiceAmount='3333333.0', ediNumber='DE524324345', status='Open', billDate='Fri Oct 10 08:55:00 IST 2014'},{Location=[/api/invoices/16], X-traceApp-alert=[traceApp.invoice.created], X-traceApp-params=[16]}>

Open in new window


How can i retain the date as it is as I have entered. or as you suggested is there any simple way/reference to have joda time converter.
0
Too SmartAuthor Commented:
or Do you have any other better solution for me to solve this?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

gurpsbassiCommented:
I don;t know what is generating your log statements.

What does the date get persisted as in the database??
0
gurpsbassiCommented:
Any update on this?
Do you still require help?
0
Too SmartAuthor Commented:
As I was in travel I did not check this yet, by today I will night I will update it, sorry for the late.
0
Too SmartAuthor Commented:
If I use java.util.Date, it is working fine, for now, I have removed joda time.
0
Too SmartAuthor Commented:
java.util.Date has solved the problem.
0
gurpsbassiCommented:
However If you really want to use Joda time, you can still do it with a custom convertor.

For example look at this article.

Although its using the Java 8 java.time API the same principle applies if you wish to use it with joda.
I would prefer to use the convertor approach described in the article
0
Too SmartAuthor Commented:
Hi gurpsbassi, In your comments article link is missing, which you were referring. Could you please add it?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java EE

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.