I have created one spring-boot application with datasource routing. I have used javax.persistence.EntityManager to retrive data from database using store-procedure. After restarting sql server, EntityManager fail to retrive data from database using store-procedure. I have used both property TestOnBorrow and ValidationQuery.
RoutingDataSourceCode
public class MessagingChildDataSourceConfig{
private static final Logger LOG = LoggerFactory.getLogger(MessagingChildDataSourceConfig.class);
@Autowired
protected MessagingDataSourceUtil util;
@Autowired
protected JPAProperties jpaProperties;
@Bean(name = "messageDatasource")
@PostConstruct
public DataSource messageDatasource() throws SQLException {
Map<Object, Object> datasourceMap = new HashMap<>();
DataSource msgChildDataSources = DataSourceBuilder.create()
.url("URL")
.username("USER")
.password("PASS")
.build();
datasourceMap.put("testDataSOurce", msgChildDataSources);
MessagingRoutingDataSource datasource = new MessagingRoutingDataSource();
DataSource msgDefaultDataSource = util.defaultDataSource();
util.setTypeSpecificProperties(msgDefaultDataSource);
datasource.setDefaultTargetDataSource(msgDefaultDataSource);
datasource.setTargetDataSources(datasourceMap);
datasource.afterPropertiesSet();
return datasource;
}
@Bean(name = "messageEntityManagerFactory")
public LocalContainerEntityManagerFactoryBean messageEntityManagerFactory(EntityManagerFactoryBuilder builder,
@Qualifier("messageDatasource") DataSource childDatasource) {
HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
LocalContainerEntityManagerFactoryBean factory = builder.dataSource(childDatasource)
.packages(getPackages()) // for entity package
.persistenceUnit("messagesdbTest").properties(CommonUtil.getJPAProperties(jpaProperties)).build();
factory.setJpaVendorAdapter(vendorAdapter);
return factory;
}
@Bean(name = "messageTransactionManager")
public PlatformTransactionManager messageTransactionManager(
@Qualifier("messageEntityManagerFactory") EntityManagerFactory childEntityManagerFactory) {
return new JpaTransactionManager(childEntityManagerFactory);
}
@Bean(name = "channelEntityManager")
public EntityManager entityManager(@Qualifier("messageEntityManagerFactory") LocalContainerEntityManagerFactoryBean factory){
return factory.getNativeEntityManagerFactory().createEntityManager();
}
public String[] getPackages(){
return new String[]{"com.asite.abc.data.entity"};
}
}
Get data from database using store procedure.
public class ChannelService {
@Qualifier("channelEntityManager")
@Autowired
private EntityManager entityManager;
@Transactional(propagation = Propagation.REQUIRES_NEW, readOnly = true, isolation = Isolation.READ_UNCOMMITTED, transactionManager = MessagingStringPool.CHANNEL_TRANSACTION_MANAGER)
public void getUserAccessibleDCs(Long channelId, Long projectId) {
StoredProcedureQuery query = this.entityManager.createNamedStoredProcedureQuery("channelUserAccessibleDCs");
this.entityManager.setFlushMode(FlushModeType.AUTO);
query.setParameter("channel_id", channelId);
query.execute(); //**Error from this line**
}
}
Properties:
spring.datasource.password=Pass
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.testOnBorrow=true
spring.datasource.username=Username
spring.datasource.validationQuery=SELECT 1
spring.jpa.hibernate.ddl-auto=none
spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.ImprovedNamingStrategy
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServer2008Dialect
spring.jpa.database=default
spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true
ERROR while execute store-procedure query using EntityManager, After restart sql server. Error log
11-09-2018 14:57:50.579 [pool-14-thread-32] ERROR c.a.m.c.c.ChannelMessageCommand.execute - Error while converting JSON to object
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not prepare statement
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692)
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602)
at org.hibernate.jpa.internal.StoredProcedureQueryImpl.execute(StoredProcedureQueryImpl.java:224)
at com.asite.message.channel.entity.service.ChannelService.getUserAccessibleDCs(ChannelService.java:619)
at com.asite.message.channel.entity.service.ChannelService$$FastClassBySpringCGLIB$$ab52f291.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:738)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:673)
at com.asite.message.channel.entity.service.ChannelService$$EnhancerBySpringCGLIB$$303bc2d0.getUserAccessibleDCs(<generated>)
at com.asite.message.session.service.channel.ChannelSessionService.setChannelUserAccessibleDCs(ChannelSessionService.java:1140)
at com.asite.message.command.channel.ChannelMessageCommand.execute(ChannelMessageCommand.java:112)
at com.asite.message.command.channel.ChannelMessageCommand.execute(ChannelMessageCommand.java:44)
at com.asite.message.TextMessageProcessor.lambda$executeMessageCommand$77(TextMessageProcessor.java:95)
at com.asite.message.TextMessageProcessor$$Lambda$80/1154803940.run(Unknown Source)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.hibernate.exception.GenericJDBCException: could not prepare statement
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareStatement(StatementPreparerImpl.java:78)
at org.hibernate.procedure.internal.ProcedureCallImpl.buildOutputs(ProcedureCallImpl.java:395)
at org.hibernate.procedure.internal.ProcedureCallImpl.getOutputs(ProcedureCallImpl.java:363)
at org.hibernate.jpa.internal.StoredProcedureQueryImpl.outputs(StoredProcedureQueryImpl.java:234)
at org.hibernate.jpa.internal.StoredProcedureQueryImpl.execute(StoredProcedureQueryImpl.java:217)
... 21 common frames omitted
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:206)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:724)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareCall(SQLServerConnection.java:2990)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareCall(SQLServerConnection.java:2593)
at sun.reflect.GeneratedMethodAccessor228.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
at org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:75)
at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81)
at com.sun.proxy.$Proxy93.prepareCall(Unknown Source)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$1.doPrepare(StatementPreparerImpl.java:86)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:172)
... 26 common frames omitted
