1.If you fire the query " SELECT sysdate param FROM dual;" (or) " SELECT systimestamp param FROM dual;"Hibernate will complain " org.hibernate.MappingException: No Dialect mapping for JDBC type: -101". Not sure of the JPA error
2.To address this, you need to ensure that Hibernate is educated to identify the sysdate (or) systimestamp as a registered field
3.The only way to do this is to addScalar to the SQLQuery
4.But here again if you say addScalar("param",Hibernate.TIMESTAMP), Type fields in org.hibernate.Hibernate are deprecated (and actually removed) as result of HHH-5196
5.So the other way is to say as follows
6.sqlQuery.addScalar("param", org.hibernate.type.TimestampType.INSTANCE);
7.Now that this is done for Hibernate, I was wondering the alternative for JPA.
8.So the thIrd hack was to hack the JPA Entity Manager to use Hibernate scalar as follows
9.query = entityMgrObj.createNativeQuery(queryString);
10.query.unwrap(SQLQuery.class).addScalar("param", org.hibernate.type.TimestampType.INSTANCE);
So here we go
For hibernate
/**
* public method to get the database Date and Time
*/
public Timestamp getCurrentDBTime() throws ORMException {
SQLQuery sqlQuery = null;
String queryString=null;
Timestamp dbTimeStamp = null;
try {
queryString = "SELECT systimestamp param FROM DUAL";
sessionObj = getHibSession();
sqlQuery = sessionObj.createSQLQuery(queryString);
sqlQuery.addScalar("param", org.hibernate.type.TimestampType.INSTANCE);
dbTimeStamp = (Timestamp)sqlQuery.uniqueResult();
return dbTimeStamp;
} catch (HibernateException ex) {
throw new ORMException(ex, IORMErrorCode.LQM_ORM_E_GETCURRENT_DATE_TIME);
} catch (RuntimeException ex) {
throw new ORMException(ex, IORMErrorCode.LQM_ORM_E_GEN_EXCEPTION);
} finally {
if (sessionObj != null) {
sessionObj.close();
}
}
}
/**
* public method to get the database Date
*/
public Date getCurrentDBDate() throws ORMException {
SQLQuery sqlQuery = null;
String queryString=null;
Date dbDate = null;
try {
queryString = "SELECT sysdate param FROM DUAL";
sessionObj = getHibSession();
sqlQuery = sessionObj.createSQLQuery(queryString);
sqlQuery.addScalar("param", org.hibernate.type.DateType.INSTANCE);
dbDate = (Date)sqlQuery.uniqueResult();
return dbDate;
} catch (HibernateException ex) {
throw new ORMException(ex, IORMErrorCode.LQM_ORM_E_GETCURRENT_DATE_TIME);
} catch (RuntimeException ex) {
throw new ORMException(ex, IORMErrorCode.LQM_ORM_E_GEN_EXCEPTION);
} finally {
if (sessionObj != null) {
sessionObj.close();
}
}
}
For JPA
/**
* public method to get the database Date and Time
*/
public Timestamp getCurrentDBTime() throws ORMException {
Timestamp dbTimeStamp = null;
String queryString=null;
Query query = null;
try {
queryString = "SELECT systimestamp param FROM DUAL";
entityMgrObj = getHibJpaEntityManager();
query = entityMgrObj.createNativeQuery(queryString);
query.unwrap(SQLQuery.class).addScalar("param", org.hibernate.type.TimestampType.INSTANCE);
dbTimeStamp = (Timestamp)query.getSingleResult();
return dbTimeStamp;
} catch (HibernateException ex) {
throw new ORMException(ex, IORMErrorCode.LQM_ORM_E_GETCURRENT_DATE_TIME);
} catch (RuntimeException ex) {
throw new ORMException(ex, IORMErrorCode.LQM_ORM_E_GEN_EXCEPTION);
}
}
/**
* public method to get the database Date
*/
public Date getCurrentDBDate() throws ORMException {
Date dbDate = null;
String queryString=null;
Query query = null;
try {
queryString = "SELECT sysdate param FROM DUAL";
entityMgrObj = getHibJpaEntityManager();
query = entityMgrObj.createNativeQuery(queryString);
query.unwrap(SQLQuery.class).addScalar("param", org.hibernate.type.DateType.INSTANCE);
dbDate = (Date)query.getSingleResult();
return dbDate;
} catch (HibernateException ex) {
throw new ORMException(ex, IORMErrorCode.LQM_ORM_E_GETCURRENT_DATE_TIME);
} catch (RuntimeException ex) {
throw new ORMException(ex, IORMErrorCode.LQM_ORM_E_GEN_EXCEPTION);
}
}
