How do I see how Hibernate is trying to map a pojo to the sql result?

Question

Here is my code for getting a list of FrameFactory from database using Hibernate

@SuppressWarnings("unchecked")
@Override
public List<FrameFactory> getFrameFactoryByActiveYN(Boolean activeYN) {
    String sql = sqlProperties.getProperty("getFrameFactoryByActiveYN");
    SQLQuery q = getSession().createSQLQuery(sql);
    q.addEntity(FrameFactory.class);
    q.setParameter("activeYN",activeYN,BooleanType.INSTANCE);
    List<?> uncast = q.list(); // <---- exception here
    System.out.println(Arrays.toString(uncast.toArray()));
    List<FrameFactory>frameFactorys = (List<FrameFactory>)q.list(); // <---- exception also here
    return frameFactorys;
}

My sqlPropertiesFile

getFrameFactoryByActiveYN=SELECT * FROM TBLFRAMEFACTORY WHERE FRAMEFACTORY_ACTIVEYN \= \:activeYN

My FrameFactory class

@Entity
@Table(name="TBLFRAMEFACTORY")
public class FrameFactory extends Auditable implements Serializable {

private static final long serialVersionUID = 1L;

@Type(type="yes_no")
@NotNull
@Column(name="FRAMEFACTORY_ACTIVEYN")
protected Boolean activeYN = false;
@Id
@Column(name="FRAMEFACTORY_CLASSPATH", updatable=false, nullable=false)
protected String classPath = null;

public FrameFactory() { super(); }

public FrameFactory(FrameFactory frameFactory) {
    setActiveYN(frameFactory.getActiveYN());
    setClassPath(frameFactory.getClassPath());
}

public Boolean getActiveYN() {
    return activeYN;
}

public void setActiveYN(Boolean activeYN) {
    this.activeYN = activeYN;
}

public String getClassPath() {
    return classPath;
}

public void setClassPath(String classPath) {
    this.classPath = classPath;
}

@Transient
@Override
public String getPrimaryKeyDisplay() {
    StringBuilder sb = new StringBuilder();
    if (classPath == null) {
        sb.append(" classPath: null");
    } else {
        sb.append(" classPath: " + classPath.toString());
    }
    return sb.toString();
}
@Transient
@Override
public String toString() {
    return new ToStringBuilder(this).append("activeYN", activeYN).append("classPath", classPath).toString();
}

@Transient
@Override
public int hashCode() {
    return new HashCodeBuilder().append(activeYN).append(classPath).toHashCode();
}

@Transient
@Override
public boolean equals(Object obj) {
    if (obj == null) { return false; }
    if (obj == this) { return true; }
    if (obj.getClass() != getClass()) { return false; }
    FrameFactory that = (FrameFactory) obj;
    return new EqualsBuilder().append(activeYN, that.activeYN).append(classPath, that.classPath).isEquals();
}

}

And my Auditable class

public abstract class Auditable {
@Transient
protected Integer auditContactId = null;

public void setAuditContactId(Integer auditContactId) {
    this.auditContactId = auditContactId;
}

public Integer getAuditContactId() {
    return auditContactId;
}

public abstract String getPrimaryKeyDisplay();
}

I am getting the following exception on my server

WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (EJB default - 6) SQL Error: 1722, SQLState: 42000
ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (EJB default - 6) ORA-01722: invalid number

I understand that the exception is trying to tell me that it can't force a conversion to a number. But I'm not expecting a number, I'm expecting a Boolean mapped in the db as a char(1) 'Y' or 'N' and a String mapped in the db as a VarChar2(256). I would like to see what Hibernate is doing behind the scenes when trying to map data from the ResultSet to the FrameFactory object. Any suggestions?

The following is the complete stack trace as requested.

 16:48:20,250 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (EJB default - 3) SQL Error: 1722, SQLState: 42000
 16:48:20,251 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (EJB default - 3) ORA-01722: invalid number

 16:48:20,253 ERROR [org.jboss.as.ejb3.invocation] (EJB default - 3) JBAS014134: EJB Invocation failed on component KdsSessionBean for method public abstract java.util.List com.kable.newsstand.kdsejb.session.KdsSession.getFrameFactoryByActiveYN(java.lang.Boolean): javax.ejb.EJBException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
       at org.jboss.as.ejb3.tx.CMTTxInterceptor.handleExceptionInOurTx(CMTTxInterceptor.java:190) [jboss-as-ejb3.jar:7.5.8.Final-redhat-2]
       at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInOurTx(CMTTxInterceptor.java:280) [jboss-as-ejb3.jar:7.5.8.Final-redhat-2]
       at org.jboss.as.ejb3.tx.CMTTxInterceptor.required(CMTTxInterceptor.java:330) [jboss-as-ejb3.jar:7.5.8.Final-redhat-2]
       at org.jboss.as.ejb3.tx.CMTTxInterceptor.processInvocation(CMTTxInterceptor.java:242) [jboss-as-ejb3.jar:7.5.8.Final-redhat-2]
       at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation.jar:1.1.2.Final-redhat-1]
       at org.jboss.as.ejb3.remote.EJBRemoteTransactionPropagatingInterceptor.processInvocation(EJBRemoteTransactionPropagatingInterceptor.java:79) [jboss-as-ejb3.jar:7.5.8.Final-redhat-2]
       at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation.jar:1.1.2.Final-redhat-1]
       at org.jboss.as.ejb3.component.interceptors.CurrentInvocationContextInterceptor.processInvocation(CurrentInvocationContextInterceptor.java:41) [jboss-as-ejb3.jar:7.5.8.Final-redhat-2]
       at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation.jar:1.1.2.Final-redhat-1]
       at org.jboss.as.ejb3.component.invocationmetrics.WaitTimeInterceptor.processInvocation(WaitTimeInterceptor.java:43) [jboss-as-ejb3.jar:7.5.8.Final-redhat-2]
       at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation.jar:1.1.2.Final-redhat-1]
       at org.jboss.as.ejb3.security.SecurityContextInterceptor.processInvocation(SecurityContextInterceptor.java:89) [jboss-as-ejb3.jar:7.5.8.Final-redhat-2]
       at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation.jar:1.1.2.Final-redhat-1]
       at org.jboss.as.ejb3.component.interceptors.ShutDownInterceptorFactory$1.processInvocation(ShutDownInterceptorFactory.java:64) [jboss-as-ejb3.jar:7.5.8.Final-redhat-2]
       at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation.jar:1.1.2.Final-redhat-1]
       at org.jboss.as.ejb3.component.interceptors.LoggingInterceptor.processInvocation(LoggingInterceptor.java:59) [jboss-as-ejb3.jar:7.5.8.Final-redhat-2]
       at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation.jar:1.1.2.Final-redhat-1]
       at org.jboss.as.ee.component.NamespaceContextInterceptor.processInvocation(NamespaceContextInterceptor.java:50) [jboss-as-ee.jar:7.5.8.Final-redhat-2]
       at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation.jar:1.1.2.Final-redhat-1]
       at org.jboss.as.ejb3.component.interceptors.AdditionalSetupInterceptor.processInvocation(AdditionalSetupInterceptor.java:55) [jboss-as-ejb3.jar:7.5.8.Final-redhat-2]
       at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation.jar:1.1.2.Final-redhat-1]
       at org.jboss.as.ee.component.TCCLInterceptor.processInvocation(TCCLInterceptor.java:45) [jboss-as-ee.jar:7.5.8.Final-redhat-2]
       at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation.jar:1.1.2.Final-redhat-1]
       at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61) [jboss-invocation.jar:1.1.2.Final-redhat-1]
       at org.jboss.as.ee.component.ViewService$View.invoke(ViewService.java:185) [jboss-as-ee.jar:7.5.8.Final-redhat-2]
       at org.jboss.as.ejb3.remote.protocol.versionone.MethodInvocationMessageHandler.invokeMethod(MethodInvocationMessageHandler.java:319) [jboss-as-ejb3.jar:7.5.8.Final-redhat-2]
       at org.jboss.as.ejb3.remote.protocol.versionone.MethodInvocationMessageHandler.access$100(MethodInvocationMessageHandler.java:68) [jboss-as-ejb3.jar:7.5.8.Final-redhat-2]
       at org.jboss.as.ejb3.remote.protocol.versionone.MethodInvocationMessageHandler$1.run(MethodInvocationMessageHandler.java:201) [jboss-as-ejb3.jar:7.5.8.Final-redhat-2]
       at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:473) [rt.jar:1.7.0_141]
       at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_141]
       at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_141]
       at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_141]
       at java.lang.Thread.run(Thread.java:748) [rt.jar:1.7.0_141]
       at org.jboss.threads.JBossThread.run(JBossThread.java:122)
 Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
       at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:82) [hibernate-core.jar:4.2.23.Final-redhat-1]
       at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) [hibernate-core.jar:4.2.23.Final-redhat-1]
       at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:124) [hibernate-core.jar:4.2.23.Final-redhat-1]
       at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109) [hibernate-core.jar:4.2.23.Final-redhat-1]
       at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:88) [hibernate-core.jar:4.2.23.Final-redhat-1]
       at org.hibernate.loader.Loader.getResultSet(Loader.java:2064) [hibernate-core.jar:4.2.23.Final-redhat-1]
       at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1861) [hibernate-core.jar:4.2.23.Final-redhat-1]
       at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1840) [hibernate-core.jar:4.2.23.Final-redhat-1]
       at org.hibernate.loader.Loader.doQuery(Loader.java:905) [hibernate-core.jar:4.2.23.Final-redhat-1]
       at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:347) [hibernate-core.jar:4.2.23.Final-redhat-1]
       at org.hibernate.loader.Loader.doList(Loader.java:2552) [hibernate-core.jar:4.2.23.Final-redhat-1]
       at org.hibernate.loader.Loader.doList(Loader.java:2538) [hibernate-core.jar:4.2.23.Final-redhat-1]
       at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2368) [hibernate-core.jar:4.2.23.Final-redhat-1]
       at org.hibernate.loader.Loader.list(Loader.java:2363) [hibernate-core.jar:4.2.23.Final-redhat-1]
       at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:340) [hibernate-core.jar:4.2.23.Final-redhat-1]
       at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1788) [hibernate-core.jar:4.2.23.Final-redhat-1]
       at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:232) [hibernate-core.jar:4.2.23.Final-redhat-1]
       at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:157) [hibernate-core.jar:4.2.23.Final-redhat-1]
       at com.kable.newsstand.kdsejb.session.KdsSessionBean.getFrameFactoryByActiveYN(KdsSessionBean.java:3523) [kdsSession.jar:]
       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_141]
       at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_141]
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_141]
       at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_141]
       at org.jboss.as.ee.component.ManagedReferenceMethodInterceptor.processInvocation(ManagedReferenceMethodInterceptor.java:52) [jboss-as-ee.jar:7.5.8.Final-redhat-2]
       at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation.jar:1.1.2.Final-redhat-1]
       at org.jboss.invocation.WeavedInterceptor.processInvocation(WeavedInterceptor.java:53) [jboss-invocation.jar:1.1.2.Final-redhat-1]
       at org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:63) [jboss-as-ee.jar:7.5.8.Final-redhat-2]
       at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation.jar:1.1.2.Final-redhat-1]
       at org.jboss.invocation.WeavedInterceptor.processInvocation(WeavedInterceptor.java:53) [jboss-invocation.jar:1.1.2.Final-redhat-1]
       at org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:63) [jboss-as-ee.jar:7.5.8.Final-redhat-2]
       at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation.jar:1.1.2.Final-redhat-1]
       at org.jboss.as.ejb3.component.invocationmetrics.ExecutionTimeInterceptor.processInvocation(ExecutionTimeInterceptor.java:43) [jboss-as-ejb3.jar:7.5.8.Final-redhat-2]
       at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation.jar:1.1.2.Final-redhat-1]
       at org.jboss.as.jpa.interceptor.SBInvocationInterceptor.processInvocation(SBInvocationInterceptor.java:47) [jboss-as-jpa.jar:7.5.8.Final-redhat-2]
       at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation.jar:1.1.2.Final-redhat-1]
       at org.jboss.invocation.InitialInterceptor.processInvocation(InitialInterceptor.java:21) [jboss-invocation.jar:1.1.2.Final-redhat-1]
       at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation.jar:1.1.2.Final-redhat-1]
       at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61) [jboss-invocation.jar:1.1.2.Final-redhat-1]
       at org.jboss.as.ee.component.interceptors.ComponentDispatcherInterceptor.processInvocation(ComponentDispatcherInterceptor.java:53) [jboss-as-ee.jar:7.5.8.Final-redhat-2]
       at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation.jar:1.1.2.Final-redhat-1]
       at org.jboss.as.ejb3.component.pool.PooledInstanceInterceptor.processInvocation(PooledInstanceInterceptor.java:51) [jboss-as-ejb3.jar:7.5.8.Final-redhat-2]
       at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation.jar:1.1.2.Final-redhat-1]
       at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInOurTx(CMTTxInterceptor.java:278) [jboss-as-ejb3.jar:7.5.8.Final-redhat-2]
       ... 32 more
 Caused by: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number

       at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
       at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
       at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
       at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
       at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
       at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
       at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
       at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
       at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774)
       at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925)
       at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111)
       at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
       at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845)
       at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501)
       at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
       at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79) [hibernate-core.jar:4.2.23.Final-redhat-1]
       ... 70 more

I have tried changing my method code to the following, it still returns the same exception.

@SuppressWarnings("unchecked")
@Override
public List<FrameFactory> getFrameFactoryByActiveYN(Boolean activeYN) {
    String sql = sqlProperties.getProperty("getFrameFactoryByActiveYN");
    SQLQuery q = getSession().createSQLQuery(sql);
//      q.addEntity(FrameFactory.class);
    q.setParameter("activeYN",activeYN,BooleanType.INSTANCE);
    q.addScalar("FRAMEFACTORY_ACTIVEYN", BooleanType.INSTANCE).addScalar("FRAMEFACTORY_CLASSPATH", StringType.INSTANCE).setResultTransformer(new AliasToBeanResultTransformer(FrameFactory.class));
    List<FrameFactory>frameFactorys = (List<FrameFactory>)q.list();
    return frameFactorys;
}

Take a look at [this post](https://stackoverflow.com/questions/1710476/how-to-print-a-query-string-with-parameter-values-when-using-hibernate) — stdunbar, Feb 23 '18 at 21:23
@stdunbar I don't need to know how Hibernate is binding values to query parameters, I need to know how Hibernate is pulling query results into the FrameFactory class. — bcr666, Feb 23 '18 at 22:18
@JBNizet Your suggestion isn't helpful. I have about 400 native sql queries and a couple of days to get this working. Most of the people in my shop know SQL really well. — bcr666, Feb 23 '18 at 22:18
Knowing how to use a screwdriver very well isn't helpful when you chose to build something with nails. Learning how to use a hammer is the right thing to do. You're using the wrong tool. — JB Nizet, Feb 23 '18 at 22:20

score 0 · Answer 1 · answered Feb 24 '18 at 14:15

According to http://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html_single/#objectstate-querying-nativesql you should rewrite your native SQL query like that :

getFrameFactoryByActiveYN=SELECT {f.*} FROM TBLFRAMEFACTORY f WHERE FRAMEFACTORY_ACTIVEYN \= \:activeYN

score 0 · Accepted Answer · answered Feb 27 '18 at 16:03

It turns out the problem isn't mapping to the result. It is accepting a boolean as a query parameter. I tried putting

<property name="hibernate.query.substitutions" value="true 'Y', false 'N'" />

in the persistence.xml, but that didn't work. So my work around is detecting a Boolean SQL parameter and changing it to one of the 'Y'/'N' values. Like the following.

@SuppressWarnings("unchecked")
@Override
public List<FrameFactory> getFrameFactoryByActiveYN(Boolean activeYN) {
    String sql = sqlProperties.getProperty("getFrameFactoryByActiveYN");
    SQLQuery q = getSession().createSQLQuery(sql);
    q.addEntity(FrameFactory.class);
    q.setParameter("activeYN", activeYN == null ? "N" : activeYN ? "Y" : "N", StringType.INSTANCE);
    List<FrameFactory>frameFactorys = q.list();
    return frameFactorys;
}

How do I see how Hibernate is trying to map a pojo to the sql result?

2 Answers2