JPA Query fails then connection is lost

So I have a problem with the EntityManager closing the transaction when a SQL exception is throw by the Oracle database.

Caused by: java.sql.SQLException: ORA-01722: invalid number

I have a collection of SQL data validation rules in the database which I append to my SQL query in the below code. The query than runs these rules on the database against a EAV (Entity, Attribute, Value) database table.

An example of a data rule.

t1.attribute_name = 'ATTRIBUTE_NAME' and (cast(t1.value as number) < 1 or cast(t1.value as number) > 2)

Now because the table is a EAV base table all the data we are working with is in String format, however if you compare numbers the comparison is done base on String not numbers. Which is not what we want so we cast the numbers to do the comparison correctly. This however causes issues if there is data in the EAV table that is not a number, for example 12A (which is perfectly valid, cause there are other checks executed to inform the user of that invalid data type situation.) as the query fails with the exception we mentioned above.

Now the query failing is good cause, obviously, we can not run it cause it is trying to do something with the data it can't do. However, as there is a collection of rules that need to be run, I would like to continue on running the rules after I detect that one has failed. That is where the problem occurs.

When a rule query fails and is caught by the exception handler, I am unable to continue using the Entity Manager that was injected into the bean because the transaction has been closed.

I have tried to obtain a new Entity Manager in the catch block however I am not having much luck. I tried obtaining an EntityManagerFactory and creating a new EntityManager from there, however when I obtain the session factory it states the following,

11:24:13,800 INFO  [TransactionFactoryFactory] Transaction strategy: org.hibernate.ejb.transaction.JoinableCMTTransactionFactory
11:24:13,801 INFO  [TransactionManagerLookupFactory] instantiating TransactionManagerLookup: org.hibernate.transaction.JBossTransactionManagerLookup
11:24:13,801 INFO  [TransactionManagerLookupFactory] instantiated TransactionManagerLookup
11:24:13,801 INFO  [SettingsFactory] Automatic flush during beforeCompletion(): disabled
11:24:13,801 INFO  [SettingsFactory] Automatic session close at end of transaction: disabled
11:24:13,801 INFO  [SettingsFactory] JDBC batch size: 15
11:24:13,801 INFO  [SettingsFactory] JDBC batch updates for versioned data: disabled
11:24:13,802 INFO  [SettingsFactory] Scrollable result sets: enabled
11:24:13,802 INFO  [SettingsFactory] JDBC3 getGeneratedKeys(): disabled
11:24:13,802 INFO  [SettingsFactory] Connection release mode: auto
11:24:13,802 INFO  [SettingsFactory] Default schema: SCHEMA_NAME
11:24:13,802 INFO  [SettingsFactory] Default batch fetch size: 1
11:24:13,802 INFO  [SettingsFactory] Generate SQL with comments: disabled
11开发者_开发技巧:24:13,802 INFO  [SettingsFactory] Order SQL updates by primary key: disabled
11:24:13,802 INFO  [SettingsFactory] Order SQL inserts for batching: disabled
11:24:13,802 INFO  [SettingsFactory] Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
11:24:13,802 INFO  [ASTQueryTranslatorFactory] Using ASTQueryTranslatorFactory
11:24:13,802 INFO  [SettingsFactory] Query language substitutions: {}
11:24:13,802 INFO  [SettingsFactory] JPA-QL strict compliance: enabled
11:24:13,802 INFO  [SettingsFactory] Second-level cache: enabled
11:24:13,802 INFO  [SettingsFactory] Query cache: disabled
11:24:13,802 INFO  [SettingsFactory] Cache region factory : org.hibernate.cache.impl.NoCachingRegionFactory
11:24:13,803 INFO  [SettingsFactory] Optimize cache for minimal puts: disabled
11:24:13,803 INFO  [SettingsFactory] Structured second-level cache entries: disabled
11:24:13,803 INFO  [SettingsFactory] Statistics: disabled
11:24:13,803 INFO  [SettingsFactory] Deleted entity synthetic identifier rollback: disabled
11:24:13,803 INFO  [SettingsFactory] Default entity-mode: pojo
11:24:13,803 INFO  [SettingsFactory] Named query checking : enabled
11:24:13,813 INFO  [SessionFactoryImpl] building session factory
11:24:13,813 INFO  [SessionFactoryObjectFactory] Not binding factory to JNDI, no JNDI name configured
11:24:13,813 INFO  [SchemaValidator] Running schema validator
11:24:13,813 INFO  [SchemaValidator] fetching database metadata
11:24:14,156 INFO  [NamingHelper] JNDI InitialContext properties:{}

But when I try to run the query, once I have created the new EntityManager from the factory, I get the following exception.

11:25:11,128 ERROR [STDERR] javax.persistence.PersistenceException: org.hibernate.MappingException: Unknown entity: org.myprogram.entity.MisCollVariableValue
11:25:11,129 ERROR [STDERR]     at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:614)
11:25:11,129 ERROR [STDERR]     at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:76)
11:25:11,129 ERROR [STDERR]     at org.myprogram.dataloader.DataValidator.validateInputRules(DataValidator.java:444)
11:25:11,129 ERROR [STDERR]     at org.myprogram.dataloader.DataValidator.methodThatCallsThePrivateOne(DataValidator.java:124)
11:25:11,129 ERROR [STDERR]     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
11:25:11,129 ERROR [STDERR]     at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
11:25:11,129 ERROR [STDERR]     at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
11:25:11,129 ERROR [STDERR]     at java.lang.reflect.Method.invoke(Unknown Source)
11:25:11,129 ERROR [STDERR]     at org.jboss.seam.util.Reflections.invoke(Reflections.java:22)
11:25:11,129 ERROR [STDERR]     at org.jboss.seam.intercept.RootInvocationContext.proceed(RootInvocationContext.java:32)
11:25:11,129 ERROR [STDERR]     at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:56)
11:25:11,129 ERROR [STDERR]     at org.jboss.seam.transaction.RollbackInterceptor.aroundInvoke(RollbackInterceptor.java:28)
11:25:11,129 ERROR [STDERR]     at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
11:25:11,129 ERROR [STDERR]     at org.jboss.seam.core.BijectionInterceptor.aroundInvoke(BijectionInterceptor.java:77)
11:25:11,129 ERROR [STDERR]     at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
11:25:11,129 ERROR [STDERR]     at org.jboss.seam.core.MethodContextInterceptor.aroundInvoke(MethodContextInterceptor.java:44)
11:25:11,129 ERROR [STDERR]     at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
11:25:11,129 ERROR [STDERR]     at org.jboss.seam.intercept.RootInterceptor.invoke(RootInterceptor.java:107)
11:25:11,129 ERROR [STDERR]     at org.jboss.seam.intercept.JavaBeanInterceptor.interceptInvocation(JavaBeanInterceptor.java:185)
11:25:11,129 ERROR [STDERR]     at org.jboss.seam.intercept.JavaBeanInterceptor.invoke(JavaBeanInterceptor.java:103)
11:25:11,129 ERROR [STDERR]     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
11:25:11,130 ERROR [STDERR]     at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
11:25:11,130 ERROR [STDERR]     at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
11:25:11,130 ERROR [STDERR]     at java.lang.reflect.Method.invoke(Unknown Source)
11:25:11,130 ERROR [STDERR]     at org.jboss.seam.util.Reflections.invoke(Reflections.java:22)
11:25:11,130 ERROR [STDERR]     at org.jboss.seam.intercept.RootInvocationContext.proceed(RootInvocationContext.java:32)
11:25:11,130 ERROR [STDERR]     at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:56)
11:25:11,130 ERROR [STDERR]     at org.jboss.seam.transaction.RollbackInterceptor.aroundInvoke(RollbackInterceptor.java:28)
11:25:11,130 ERROR [STDERR]     at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
11:25:11,130 ERROR [STDERR]     at org.jboss.seam.core.BijectionInterceptor.aroundInvoke(BijectionInterceptor.java:77)
11:25:11,130 ERROR [STDERR]     at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
11:25:11,130 ERROR [STDERR]     at org.jboss.seam.core.MethodContextInterceptor.aroundInvoke(MethodContextInterceptor.java:44)
11:25:11,130 ERROR [STDERR]     at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
11:25:11,130 ERROR [STDERR]     at org.jboss.seam.intercept.RootInterceptor.invoke(RootInterceptor.java:107)
11:25:11,130 ERROR [STDERR]     at org.jboss.seam.intercept.JavaBeanInterceptor.interceptInvocation(JavaBeanInterceptor.java:185)
11:25:11,130 ERROR [STDERR]     at org.jboss.seam.intercept.JavaBeanInterceptor.invoke(JavaBeanInterceptor.java:103)
11:25:11,130 ERROR [STDERR]     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
11:25:11,130 ERROR [STDERR]     at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
11:25:11,130 ERROR [STDERR]     at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
11:25:11,130 ERROR [STDERR]     at java.lang.reflect.Method.invoke(Unknown Source)
11:25:11,130 ERROR [STDERR]     at org.jboss.el.util.ReflectionUtil.invokeMethod(ReflectionUtil.java:335)
11:25:11,130 ERROR [STDERR]     at org.jboss.el.util.ReflectionUtil.invokeMethod(ReflectionUtil.java:348)
11:25:11,130 ERROR [STDERR]     at org.jboss.el.parser.AstPropertySuffix.invoke(AstPropertySuffix.java:58)
11:25:11,130 ERROR [STDERR]     at org.jboss.el.parser.AstValue.invoke(AstValue.java:96)
11:25:11,130 ERROR [STDERR]     at org.jboss.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:276)
11:25:11,130 ERROR [STDERR]     at com.sun.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:68)
11:25:11,130 ERROR [STDERR]     at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:88)
11:25:11,130 ERROR [STDERR]     at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102)
11:25:11,131 ERROR [STDERR]     at javax.faces.component.UICommand.broadcast(UICommand.java:387)
11:25:11,131 ERROR [STDERR]     at org.ajax4jsf.component.UIDataAdaptor.broadcast(UIDataAdaptor.java:1364)
11:25:11,131 ERROR [STDERR]     at org.ajax4jsf.component.AjaxViewRoot.processEvents(AjaxViewRoot.java:321)
11:25:11,131 ERROR [STDERR]     at org.ajax4jsf.component.AjaxViewRoot.broadcastEvents(AjaxViewRoot.java:296)
11:25:11,131 ERROR [STDERR]     at org.ajax4jsf.component.AjaxViewRoot.processPhase(AjaxViewRoot.java:253)
11:25:11,131 ERROR [STDERR]     at org.ajax4jsf.component.AjaxViewRoot.processApplication(AjaxViewRoot.java:466)
11:25:11,131 ERROR [STDERR]     at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:82)
11:25:11,131 ERROR [STDERR]     at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:100)
11:25:11,131 ERROR [STDERR]     at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:118)
11:25:11,131 ERROR [STDERR]     at javax.faces.webapp.FacesServlet.service(FacesServlet.java:265)
11:25:11,131 ERROR [STDERR]     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
11:25:11,131 ERROR [STDERR]     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
11:25:11,131 ERROR [STDERR]     at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:83)
11:25:11,131 ERROR [STDERR]     at org.jboss.seam.web.IdentityFilter.doFilter(IdentityFilter.java:40)
11:25:11,131 ERROR [STDERR]     at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
11:25:11,131 ERROR [STDERR]     at org.jboss.seam.web.MultipartFilter.doFilter(MultipartFilter.java:90)
11:25:11,131 ERROR [STDERR]     at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
11:25:11,131 ERROR [STDERR]     at org.jboss.seam.web.ExceptionFilter.doFilter(ExceptionFilter.java:64)
11:25:11,131 ERROR [STDERR]     at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
11:25:11,131 ERROR [STDERR]     at org.jboss.seam.web.RedirectFilter.doFilter(RedirectFilter.java:45)
11:25:11,131 ERROR [STDERR]     at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
11:25:11,131 ERROR [STDERR]     at org.ajax4jsf.webapp.BaseXMLFilter.doXmlFilter(BaseXMLFilter.java:178)
11:25:11,131 ERROR [STDERR]     at org.ajax4jsf.webapp.BaseFilter.handleRequest(BaseFilter.java:290)
11:25:11,131 ERROR [STDERR]     at org.ajax4jsf.webapp.BaseFilter.processUploadsAndHandleRequest(BaseFilter.java:368)
11:25:11,131 ERROR [STDERR]     at org.ajax4jsf.webapp.BaseFilter.doFilter(BaseFilter.java:495)
11:25:11,131 ERROR [STDERR]     at org.jboss.seam.web.Ajax4jsfFilter.doFilter(Ajax4jsfFilter.java:56)
11:25:11,131 ERROR [STDERR]     at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
11:25:11,132 ERROR [STDERR]     at org.jboss.seam.web.LoggingFilter.doFilter(LoggingFilter.java:60)
11:25:11,132 ERROR [STDERR]     at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
11:25:11,133 ERROR [STDERR]     at org.jboss.seam.web.HotDeployFilter.doFilter(HotDeployFilter.java:53)
11:25:11,133 ERROR [STDERR]     at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
11:25:11,133 ERROR [STDERR]     at org.jboss.seam.servlet.SeamFilter.doFilter(SeamFilter.java:158)
11:25:11,133 ERROR [STDERR]     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
11:25:11,133 ERROR [STDERR]     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
11:25:11,133 ERROR [STDERR]     at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
11:25:11,133 ERROR [STDERR]     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
11:25:11,134 ERROR [STDERR]     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
11:25:11,134 ERROR [STDERR]     at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:235)
11:25:11,134 ERROR [STDERR]     at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
11:25:11,134 ERROR [STDERR]     at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:190)
11:25:11,134 ERROR [STDERR]     at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:433)
11:25:11,134 ERROR [STDERR]     at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:92)
11:25:11,134 ERROR [STDERR]     at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.process(SecurityContextEstablishmentValve.java:126)
11:25:11,134 ERROR [STDERR]     at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.invoke(SecurityContextEstablishmentValve.java:70)
11:25:11,134 ERROR [STDERR]     at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
11:25:11,134 ERROR [STDERR]     at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
11:25:11,134 ERROR [STDERR]     at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:158)
11:25:11,134 ERROR [STDERR]     at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
11:25:11,134 ERROR [STDERR]     at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:330)
11:25:11,134 ERROR [STDERR]     at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:829)
11:25:11,134 ERROR [STDERR]     at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:598)
11:25:11,134 ERROR [STDERR]     at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
11:25:11,134 ERROR [STDERR]     at java.lang.Thread.run(Unknown Source)
11:25:11,135 ERROR [STDERR] Caused by: org.hibernate.MappingException: Unknown entity: org.myprogram.entity.MisCollVariableValue
11:25:11,135 ERROR [STDERR]     at org.hibernate.impl.SessionFactoryImpl.getEntityPersister(SessionFactoryImpl.java:628)
11:25:11,135 ERROR [STDERR]     at org.hibernate.loader.custom.sql.SQLQueryReturnProcessor.getSQLLoadable(SQLQueryReturnProcessor.java:335)
11:25:11,135 ERROR [STDERR]     at org.hibernate.loader.custom.sql.SQLQueryReturnProcessor.processRootReturn(SQLQueryReturnProcessor.java:376)
11:25:11,135 ERROR [STDERR]     at org.hibernate.loader.custom.sql.SQLQueryReturnProcessor.processReturn(SQLQueryReturnProcessor.java:355)
11:25:11,135 ERROR [STDERR]     at org.hibernate.loader.custom.sql.SQLQueryReturnProcessor.process(SQLQueryReturnProcessor.java:171)
11:25:11,135 ERROR [STDERR]     at org.hibernate.loader.custom.sql.SQLCustomQuery.<init>(SQLCustomQuery.java:87)
11:25:11,135 ERROR [STDERR]     at org.hibernate.engine.query.NativeSQLQueryPlan.<init>(NativeSQLQueryPlan.java:67)
11:25:11,135 ERROR [STDERR]     at org.hibernate.engine.query.QueryPlanCache.getNativeSQLQueryPlan(QueryPlanCache.java:136)
11:25:11,135 ERROR [STDERR]     at org.hibernate.impl.AbstractSessionImpl.getNativeSQLQueryPlan(AbstractSessionImpl.java:160)
11:25:11,135 ERROR [STDERR]     at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
11:25:11,135 ERROR [STDERR]     at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
11:25:11,135 ERROR [STDERR]     at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:67)
11:25:11,135 ERROR [STDERR]     ... 102 more

So I guess my question is,

• Is it possible to obtain the EntityManagerFactory and get a new EntityManger so that I can continue running my query rules.
• If so, how (cause I am at a loss as to why it does not know about the entities)?
• And is this a good approach to solve my problem or am I going about this the wrong way?

I hope I have been clear. Cheers.

@Name("dataValidator")
@Scope(ScopeType.CONVERSATION)
@AutoCreate
public class DataValidator implements DataValidatorIntf {

  @Logger private Log log;

  @In EntityManager entityManager;

  public void methodThatCallsThePrivateOne() {
    validateInputRules();
  }

  private void validateInputRules() {
    if (fInputRules == null) {
      MdInputRuleList mdInputRuleList = (MdInputRuleList) Component.getInstance(MdInputRuleList.class, true);
      fInputRules = mdInputRuleList.getResultList();
    }

    // This is my query, which can fail.
    String sql = 
      " select " +
      "   t2.coll_var_value_id, " + 
      "   t2.attribute_name, " +
      "   t2.load_id, " +
      "   t2.coll_variable_id, " +
      "   t2.collection_id, " +
      "   t2.source_file_line, " +
      "   t2.value " + 
      " from " + 
      "   mis_coll_variable_value t1 " +
      "   inner join mis_coll_variable_value t2 on t2.source_file_line = t1.source_file_line " +
      "     and t2.load_id = :pLoadId " +
      " where " + 
      "   t1.load_id = :pLoadId " + 
      "   and %s " +
      " order by  " + 
      "   t1.source_file_line," +
      "   t1.coll_variable_id "
    ;

    fInputRulesViolations = new HashMap<Long, List<MisCollVariableValue>>();

    for (MdInputRule mdInputRule : fInputRules) {
      Query query = entityManager.createNativeQuery(
        String.format(sql, mdInputRule.getRuleSql()),
        MisCollVariableValue.class
      );
      query.setParameter("pLoadId", fLoadToValidate.getLoadId());

      try {
        if (!fInputRulesViolations.containsKey(mdInputRule.getInputRuleId())) {
          fInputRulesViolations.put(mdInputRule.getInputRuleId(), null);
        }

        fInputRulesViolations.put(
          mdInputRule.getInputRuleId(), 
          query.getResultList()  
        );
      } catch (Exception ex) {
        log.info("Input Rule #0 [Id #1] failed", mdInputRule.getRuleid(), mdInputRule.getInputRuleId());
        ex.printStackTrace();

        // TODO: Try and get a new Entity Manager when a exception is thrown in this method

        // My attempt at getting a EntityManagerFactory

        // EntityManagerFactory myEmf = Persistence.createEntityManagerFactory("PersistenceUnitName");
        // entityManager = myEmf.createEntityManager();
      }
    }
  }

---

You can always get an entityManager by using

Component.getInstance("entityManager");

But your entity isn't going to be connected to the Seam persistence context. I think there's something wrong with the way you're dong the validation, or maybe I don't understand how it works, but why not use String comparison rather than a failing query?