I am using Hibernate 4.2 with JPA and oracle 11g as a database. I am using the simple java program to store the Date with timezone information in the database.Below is the defination of my table
CREATE TABLE "AW"."AW_REQUEST_DETAILS_TEST"
( "ID" NUMBER,
"REQUEST_TYPE" VARCHAR2(20 BYTE),
"REQUESTOR" VARCHAR2(20 BYTE),
"REQUESTOR_REGION" VARCHAR2(20 BYTE),
"REQUESTOR_TYPE" VARCHAR2(20 BYTE),
"EVENT_DATETIME" TIMESTAMP (6),
"EVENT_TYPE_ID" NUMBER,
"PRINTED_MTRL" VARCHAR2(1 BYTE),
"IS_LOCATION_USA" VARCHAR2(1 BYTE),
"IS_ANALYST_ATTESTATION" VARCHAR2(1 BYTE),
"ANALYST_ATTESTATION" VARCHAR2(500 BYTE),
"ANALYST_DISCLOSURE" VARCHAR2(500 BYTE),
"ANALYST_DERV_POS" VARCHAR2(1 BYTE),
"ANALYST_POS_DETAILS" VARCHAR2(500 BYTE),
"LAST_MODIFIED_BY" VARCHAR2(20 BYTE),
"LAST_MODIFIED_DATE" TIMESTAMP (6),
"REQ_CREATED_BY" VARCHAR2(20 BYTE),
"REQ_CREATE_DATE" TIMESTAMP (6) WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
"REQUEST_STATUS" VARCHAR2(50 BYTE),
"PROCESS_INSTANCE_ID" NUMBER,
"RSCH_APPRVL_FILE" BLOB,
"IS_APPRVL_BY_RSCH" VARCHAR2(1 BYTE),
"IS_APPRVL_BY_CORP" VARCHAR2(1 BYTE),
"CORP_APPRVL_FILE" BLOB,
"TASKREADY_ID" NUMBER,
"TASKREADY_NAME" VARCHAR2(50 BYTE),
"REQCOMPDISCUSSDETIALSCS" VARCHAR2(255 BYTE),
"REQCOMPMAYDISCUSSDETAILSCS" VARCHAR2(255 BYTE),
"REQCONTDISCUSSDETAILSCS" VARCHAR2(255 BYTE),
"REQCONTMAYDISCUSSDETAILSCS" VARCHAR2(255 BYTE)
)
This is the java bean class with appropriate hibernate annotation that contains the mapping with the above table.
@Entity
@Table(name = "AW_REQUEST_DETAILS_TEST")
public class MediaRequestDetails implements WFPayload , Serializable{
/**
*
*/
private static final long serialVersionUID = 1L;
@Id
@SequenceGenerator(name="REQUEST_ID_SEQ", sequenceName="REQUEST_ID_DBSEQ", allocationSize=1, initialValue = 1)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "REQUEST_ID_SEQ")
@Column(name = "ID", nullable = false)
Long requestId;//id of the request
@Column(name="REQUEST_TYPE", nullable = false)
String requestType;//Type of the request Media/Non Media
@Column(name="REQUESTOR", nullable = false)
String requestor;//SOE ID of the requestor
@Column(name="REQUESTOR_REGION")
String requestorRegion;//This will be auto populated once the requestor is been added.
@Column(name="REQUESTOR_TYPE")
String requestorType;//requestortype OBO,analyst or Coprorate Affairs
@Column(name="EVENT_DATETIME")
//@Temporal(TemporalType.TIMESTAMP)
@org.hibernate.annotations.Type(type = "com.cira.raws.mediawf.bean.impl.UtcTimestampType")
Date eventDate;//Date of the event // Please capture sate timezone as well
@Column(name="EVENT_TYPE_ID")
long eventType;//Type of the event
@Column(name="PRINTED_MTRL")
@Type(type="yes_no")
boolean printedMTRL;//printed material will be produced or not
@Column(name="IS_LOCATION_USA")
@Type(type="yes_no")
boolean locationIsUsa;//flag will this take place in USA or Not
@Column(name="IS_ANALYST_ATTESTATION")
@Type(type="yes_no")
boolean hasAnalystAtts;
@Column(name="ANALYST_ATTESTATION")
String analystAtts;//AnalystAttastation
@Column(name="ANALYST_DISCLOSURE")
String analystDisclosure;//AnalystDisclosure
@Column(name="ANALYST_DERV_POS")
@Type(type="yes_no")
boolean analystDervPos;//Information Regarding the Derivative Position(Y/N)
@Column(name="ANALYST_POS_DETAILS")
String analystPosdetails;//Information regarding Disclosure
@Column(name="LAST_MODIFIED_BY")
String lastModifiedBy;//media request modified by name
@Column(name="LAST_MODIFIED_DATE")
@Temporal(TemporalType.TIMESTAMP)
Date lastModifiedDate;//date of the media request modified
@Column(name="REQ_CREATED_BY")
String createdBy;//media requestor creator user SOE Id.
@Column(name="REQ_CREATE_DATE")
//Temporal(TemporalType.TIMESTAMP)
@org.hibernate.annotations.Type(type = "com.cira.raws.mediawf.bean.impl.UtcTimestampType")
Date createDate;//media requestor creator user SOE Id.
@Column(name="REQUEST_STATUS")
String requestStatus;//Setting the status of the Request
@Column(name="PROCESS_INSTANCE_ID")
Long processInstaceId;
@Column(name="TASKREADY_ID")
Long taskReadyId;
@Column(name="TASKREADY_NAME")
String taskReadyName;
@Column(name="RSCH_APPRVL_FILE")
@Lob
byte[] rschApprvlFile;//Attached the approval of Research Management group
@Column(name="CORP_APPRVL_FILE")
@Lob
byte[] coprApprvlFile;//Attached approval of coprorate group.
@Column(name="IS_APPRVL_BY_CORP")
@Type(type="yes_no")
boolean hasApprovedByCorporate;//is request been approved by corporate
@Column(name="IS_APPRVL_BY_RSCH")
@Type(type="yes_no")
boolean hasApprovedByRschMgmt;//is request been approved by rsch management
//rest of the getters and setters
}
I have created the custom type UtcTimestampType to convert the current date/time information to UTC timezone and store it in the database. Below is the source code for it.In the below class I have passed the Calendar.getInstance(UTC) object to st.setTimestamp to convert the current date/time which are in IST timezone to UTC.
public class UtcTimestampTypeDescriptor extends TimestampTypeDescriptor {
/**
*
*/
private static final long serialVersionUID = 1L;
public static final UtcTimestampTypeDescriptor INSTANCE = new UtcTimestampTypeDescriptor();
private static final TimeZone UTC = TimeZone.getTimeZone("UTC");
public <X> ValueBinder<X> getBinder(final JavaTypeDescriptor<X> javaTypeDescriptor) {
return new BasicBinder<X>( javaTypeDescriptor, this ) {
@Override
protected void doBind(PreparedStatement st, X value, int index, WrapperOptions options) throws SQLException {
System.out.println("do Bind method gets called " + value);
st.setTimestamp( index, javaTypeDescriptor.unwrap( value, Timestamp.class, options ), Calendar.getInstance(UTC) );
}
};
}
public <X> ValueExtractor<X> getExtractor(final JavaTypeDescriptor<X> javaTypeDescriptor) {
return new BasicExtractor<X>( javaTypeDescriptor, this ) {
@Override
protected X doExtract(ResultSet rs, String name, WrapperOptions options) throws SQLException {
System.out.println("do Extract method gets called ");
return javaTypeDescriptor.wrap( rs.getTimestamp( name, Calendar.getInstance(UTC) ), options );
}
};
}
}
Below is the main program which I execute to store the data in the table AW_REQUEST_DETAILS_TEST. My below program is executed by the JVM having the timezone as IST.
public class TestRawsConnection {
public static void main(String args[]) throws Exception{
EntityManager entityManager = Persistence.createEntityManagerFactory("rawsjpa").createEntityManager();
if(entityManager!=null){
System.out.println("************* EntityManager is obtained 1234*****************");
Date dt=new Date();
//Date finalDate=convertDateUTC(dt);
entityManager.getTransaction().begin();
MediaRequestDetails obj=new MediaRequestDetails();
obj.setRequestor("ns52289");
obj.setRequestorRegion("USA");
obj.setRequestorType("OBO");
obj.setRequestType("MediaAppr");
obj.setEventDate(dt);
obj.setEventType(12);
obj.setLocationIsUsa(true);
obj.setPrintedMTRL(false);
obj.setLocationIsUsa(false);
obj.setHasAnalystAtts(true);
obj.setAnalystAtts("analystattestation");
obj.setAnalystDisclosure("analystdisclosure");
obj.setAnalystDervPos(true);
obj.setAnalystPosdetails("analystposdetails");
obj.setLastModifiedBy("jinesh");
obj.setLastModifiedDate(dt);
obj.setCreatedBy("jinesh123");
obj.setCreateDate(dt);
obj.setRequestStatus("pending");
obj.setProcessInstaceId(1234L);
obj.setTaskReadyId(1245L);
obj.setTaskReadyName("JInesh");
obj.setHasApprovedByCorporate(true);
obj.setHasApprovedByRschMgmt(true);
obj.setCoprApprvlFile(new String("Jinesh parikh").getBytes());
obj.setRschApprvlFile(new String("Sejal Mehta").getBytes());
entityManager.persist(obj);
entityManager.getTransaction().commit();
System.out.println("Committing the data done");
}
//entityManager.getTransaction().commit();
System.out.println("****************** Existed fetching the record *************************");
}
}
Above program works absolutly fine. It stores the date/time in the column (REQ_CREATE_DATE) as per the UTC timezone but name of timezone stored in the column is ASIA/CALCUTTA which is of my JVM and which is incorrect which I am expecting that it should be UTC.My database server is in EST time zone. Below are the values I get when I execute the select query.
select REQ_CREATE_DATE from AW_REQUEST_DETAILS_TEST;
10-JUN-15 02.13.48.000000000 PM ASIA/CALCUTTA
10-JUN-15 02.16.41.513000000 PM ASIA/CALCUTTA
In the above case the Date/Time info is correct but the timezone info is incorrect? Does anybody know how to solve this ?