Hibernate/jpa Persisting Java Date to H2 DB Sql TimeStamp

Question

I am trying to persist entity creation date into database (h2) as time stamp. Here is code:

@Temporal(TemporalType.TIMESTAMP)
@Required
public Date creationDate = new.Date();

The error I get:

[PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute native bulk manipulation query] 
Caused by: org.h2.jdbc.JdbcSQLException: Blad skladniowy w wyrazeniu SQL "INSERT INTO MedicalIncident (customer_id, actionTakenAfterIncident, actionTypeTakenAfterIncident, year_id, age_id, incidentOccurencePatientInformation, month_id, sex_id, patientHarm, patientHarmType_id, patientHarmType_Other, unit_id, place_id, incidentType_id, incidentType_Diagnosis_id, incidentType_Infection_id, incidentType_MedicineApplication_id, incidentType_PatientRightsViolation_id, incidentType_Surgery_id, incidentType_Therapy_id, incidentType_WorkOrganization_id, incidenttype_other, creationDate ) VALUES (1,true,'Różniejsze',2014,37,false,02,1,true,4,'',12,5,3,null,null,null,null,2,null,null,','Wed Jul 16 13? CEST 2014[*]')"
    Syntax error in SQL statement "INSERT INTO MedicalIncident (customer_id, actionTakenAfterIncident, actionTypeTakenAfterIncident, year_id, age_id, incidentOccurencePatientInformation, month_id, sex_id, patientHarm, patientHarmType_id, patientHarmType_Other, unit_id, place_id, incidentType_id, incidentType_Diagnosis_id, incidentType_Infection_id, incidentType_MedicineApplication_id, incidentType_PatientRightsViolation_id, incidentType_Surgery_id, incidentType_Therapy_id, incidentType_WorkOrganization_id, incidenttype_other, creationDate ) VALUES (1,true,'Różniejsze',2014,37,false,02,1,true,4,'',12,5,3,null,null,null,null,2,null,null,','Wed Jul 16 13? CEST 2014[*]')"; SQL statement:
    INSERT INTO MedicalIncident (customer_id, actionTakenAfterIncident, actionTypeTakenAfterIncident, year_id, age_id, incidentOccurencePatientInformation, month_id, sex_id, patientHarm, patientHarmType_id, patientHarmType_Other, unit_id, place_id, incidentType_id, incidentType_Diagnosis_id, incidentType_Infection_id, incidentType_MedicineApplication_id, incidentType_PatientRightsViolation_id, incidentType_Surgery_id, incidentType_Therapy_id, incidentType_WorkOrganization_id, incidenttype_other, creationDate ) VALUES (1,true,'Różniejsze',2014,37,false,02,1,true,4,'',12,5,3,null,null,null,null,2,null,null,','Wed Jul 16 13? CEST 2014') [42000-172]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:329) ~[h2.jar:1.3.172]
        at org.h2.message.DbException.get(DbException.java:169) ~[h2.jar:1.3.172]
        at org.h2.message.DbException.get(DbException.java:146) ~[h2.jar:1.3.172]
        at org.h2.message.DbException.getSyntaxError(DbException.java:181) ~[h2.jar:1.3.172]
        at org.h2.command.Parser.getSyntaxError(Parser.java:484) ~[h2.jar:1.3.172]
        at org.h2.command.Parser.checkRunOver(Parser.java:3318) ~[h2.jar:1.3.172]
        at org.h2.command.Parser.initialize(Parser.java:3234) ~[h2.jar:1.3.172]
        at org.h2.command.Parser.parse(Parser.java:266) ~[h2.jar:1.3.172]
        at org.h2.command.Parser.parse(Parser.java:255) ~[h2.jar:1.3.172]
        at org.h2.command.Parser.prepareCommand(Parser.java:218) ~[h2.jar:1.3.172]
        at org.h2.engine.Session.prepareLocal(Session.java:425) ~[h2.jar:1.3.172]
        at org.h2.engine.Session.prepareCommand(Session.java:374) ~[h2.jar:1.3.172]
        at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1138) ~[h2.jar:1.3.172]
        at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:70) ~[h2.jar:1.3.172]
        at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:267) ~[h2.jar:1.3.172]
        at com.jolbox.bonecp.ConnectionHandle.prepareStatement(ConnectionHandle.java:1024) ~[bonecp.jar:na]
        at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:534) ~[hibernate-core-3.6.9.Final.jar:3.6.9.Final]
        at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:116) ~[hibernate-core-3.6.9.Final.jar:3.6.9.Final]
        at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:109) ~[hibernate-core-3.6.9.Final.jar:3.6.9.Final]
        at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:202) ~[hibernate-core-3.6.9.Final.jar:3.6.9.Final]
        ... 30 common frames omitted

I think it is caused by incorrect date format. Date it is something like that: 'Wed Jul 16 13? CEST 2014' And timestamp is a digits f.e.: 124144324

I've also create clear class to check temporal persist. Here is code

    package models;

import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

import play.db.jpa.JPA;
import play.db.jpa.Transactional;

@Entity
public class Uss {

    @Id
    public Long id; 
    @Column(name="name")  
    public  String name;
    @Temporal(TemporalType.TIMESTAMP)
    public Date createDate = new Date();


    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Date getCreateDate() {
        return createDate;
    }

    public void setCreateDate(Date createDate) {
        this.createDate = createDate;
    }

 public String toString() {
     return "uss class: name=" + this.name + ", id=" + this.id + ", createDate=" + this.createDate; 
 }
 public int toDatabase() {
     JPA.em().persist(this);
    return 1;

 }

@Transactional
public static Uss getById(Long id) {
    return JPA.em().find(Uss.class, id);
}

}

This one works great. In database I've got timestamp column with entries like: 2014-07-17 10:24:14.357 And when I get this entity I can work with the timestamp like with java.util.date object (cause it is java.util.date object)

In my first example (the one dosnt work) I do not use persist method. I am using "native query"

String sqlQueryString = "Insert into table () values ();
Query tempQuery = JPA.em().createNativeQuery(sqlQueryString);
    tempQuery.executeUpdate();

The 'Wed Jul 16 13? CEST 2014' is the toString() representation of a java.util.Date while 124144324 is the epoch. The H2 TIMESTAMP type is represented as yyyy-MM-dd hh:mm:ss. Are you using java.sql.Date? — Vlad Mihalcea
– Vlad Mihalcea, Commented Jul 16, 2014 at 12:04

masterdany88 · Accepted Answer · 2014-07-18 07:11:07Z

1

I finally made it.

Variable declaration:

@Temporal(TemporalType.TIMESTAMP)
public Date createDate;

Insert method:

String sqlQuery = "INSERT INTO Incident "
            + "(customer_id, unit_id, place_id, "
            + "incidentType_id, createDate ) "
            + "VALUES ("+ this.customer.id +","+ this.unit.id +","+ this.place.id +","
            + this.incidentType.id +",'"+ Index.getDate("yyyy-MM-dd HH:mm:ss") + "')" ;
Query tempQuery = JPA.em().createNativeQuery(sqlQuery);
tempQuery.executeUpdate();

Or just persist on model class:

     JPA.em().persist(this);

In the database I have finally entry with the date in sql timestamp type, f.e.:

CREATEDATE  
2014-07-17 15:56:28.0

answered Jul 18, 2014 at 7:11

masterdany88

5,36915 gold badges70 silver badges140 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Hibernate/jpa Persisting Java Date to H2 DB Sql TimeStamp

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related