"java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist" when using @Primary on one of the two SQLSessionFactory bean

1
votes

I have two datasources in place both are working fine separately. But, when I use @Primary on one of the two SqlSessionFactory the other starts throwing exception mentioned below :

Exception in thread "main" org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'pServiceImpl': Invocation of init method failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: Error querying database. Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

The error may exist in com/abc/def/mapper/pMapper.java (best guess)

The error may involve defaultParameterMap

The error occurred while setting parameters

SQL: select C.CONTACT_ID, C.REF_ID, S.P_ID, S.COURIER_P_NUM, S.SENDER_ADDR_ID, S.CREATION_TS, CA.CONTACT_ADDR_ID from P_EVENT_TRACKING SE, P S, CONTACT_ADDR CA, CONTACT C where SE.EVENT_CD = 'PICKUP' and SE.P_ID = s.p_id and S.SENDER_ADDR_ID = CA.CONTACT_ADDR_ID and CA.CONTACT_ID = c.contact_id and C.GROUP_CD = 'OT' and SE.EVENT_OCCURRED_IND = 'N' and S.CREATION_TS >= (select CURRENT_TIMESTAMP - interval '30' day from dual)and S.SCHEDULE_PICKUP_IND = 'Y'

Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor.postProcessBeforeInitialization(InitDestroyAnnotationBeanPostProcessor.java:137) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyBeanPostProcessorsBeforeInitialization(AbstractAutowireCapableBeanFactory.java:409) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1620) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:555) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483) at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306) at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197) at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:761) at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:867) at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:543) at org.springframework.context.annotation.AnnotationConfigApplicationContext.(AnnotationConfigApplicationContext.java:84) at com.abc.def.app.starter.Pickup.main(Pickup.java:11) Caused by: org.springframework.jdbc.BadSqlGrammarException:

Error querying database. Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

The error may exist in com/abc/def/mapper/PMapper.java (best guess)

The error may involve defaultParameterMap

The error occurred while setting parameters

SQL: select C.CONTACT_ID, C.REF_ID, S.P_ID, S.COURIER_P_NUM, S.SENDER_ADDR_ID, S.CREATION_TS, CA.CONTACT_ADDR_ID from P_EVENT_TRACKING SE, P S, CONTACT_ADDR CA, CONTACT C where SE.EVENT_CD = 'PICKUP' and SE.P_ID = s.p_id and S.SENDER_ADDR_ID = CA.CONTACT_ADDR_ID and CA.CONTACT_ID = c.contact_id and C.GROUP_CD = 'OT' and SE.EVENT_OCCURRED_IND = 'N' and S.CREATION_TS >= (select CURRENT_TIMESTAMP - interval '30' day from dual)and S.SCHEDULE_PICKUP_IND = 'Y'

Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446) at com.sun.proxy.$Proxy26.selectList(Unknown Source) at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230) at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:137) at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:75) at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59) at com.sun.proxy.$Proxy32.findPByOutlet(Unknown Source) at com.abc.def.dao.PDAOImpl.getPByOutlet(PDAOImpl.java:23) at com.abc.def.service.PickupServiceImpl.startExecution(PickupServiceImpl.java:62) at com.abc.def.service.PickupServiceImpl.execute(PickupServiceImpl.java:46) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleElement.invoke(InitDestroyAnnotationBeanPostProcessor.java:366) at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleMetadata.invokeInitMethods(InitDestroyAnnotationBeanPostProcessor.java:311) at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor.postProcessBeforeInitialization(InitDestroyAnnotationBeanPostProcessor.java:134) ... 13 more

2
javaspringoraclespring-mvcmybatis
You may consider 'P' as valid table name (let say POWER), I just renamed it as I can not publish the real name. Also, SQL query is correct as it is working fine when I remove annotate from first and place on the second SQLSessionFactory with @Primary – Shamim Ahmad

2 Answers

3
votes

If changing of the placement of the @Primary annotation fixes this it means that in your spring configuration you are using autowiring by type of the SqlSessionFactory into SqlSessionTemplate that is used by PDAOImpl or autowiring by type of the SqlSessionTemplate in the PDAOImpl.

Make sure that you specify correct dependency explicitly.

You get this error because PDAOImpl uses incorrect SqlSessionFactory which makes calls to the database that does not have that table.

0
votes

It worked for me like this. Removed auto-registration of mapper (i.e @Mapper) and manually registered Mapper in SQLSessionTemplate as shown below:

 @Bean
    public TestMapper testMapper() throws Exception {
        SqlSessionTemplate sessionTemplate = new SqlSessionTemplate(sqlSessionFactory());        
        sessionTemplate.getConfiguration().addMapper(TestMapper.class); // new code
        return sessionTemplate.getMapper(TestMapper.class);
    }