每天 Spring 和 Hibernate 项目都会抛出 JDBC 异常。

Question

每天 Spring 和 Hibernate 项目都会抛出 JDBC 异常。

5

我正在使用Spring和Hibernate来开发我的项目，数据库是MySql。在代码中，我使用注解而不是XML。每天第一次请求登录时，会出现以下异常，但之后刷新就可以正常工作。

18-Feb-2016 10:59:20.990 SEVERE [http-nio-443-exec-9] org.apache.catalina.core.StandardWrapperValve.invoke Servlet.service() for servlet [dispatcher] in context with path [/ATS] threw exception
 org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is javax.persistence.PersistenceException: org.hibernate.TransactionException: JDBC begin transaction failed: 
    at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:431)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:427)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:276)
    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:653)
    at com.services.MyAuthoritiesPopulator$$EnhancerBySpringCGLIB$$9580eab6.getGrantedAuthorities(<generated>)
    at org.springframework.security.ldap.authentication.LdapAuthenticationProvider.loadUserAuthorities(LdapAuthenticationProvider.java:215)
    at org.springframework.security.ldap.authentication.AbstractLdapAuthenticationProvider.authenticate(AbstractLdapAuthenticationProvider.java:84)
    at org.springframework.security.authentication.ProviderManager.authenticate(ProviderManager.java:167)
    at org.springframework.security.authentication.ProviderManager.authenticate(ProviderManager.java:192)
    at org.springframework.security.web.authentication.UsernamePasswordAuthenticationFilter.attemptAuthentication(UsernamePasswordAuthenticationFilter.java:93)
    at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:217)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
    at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:120)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
    at org.springframework.security.web.csrf.CsrfFilter.doFilterInternal(CsrfFilter.java:120)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
    at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:64)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
    at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:91)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
    at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:53)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
    at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:213)
    at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:176)
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:518)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1091)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:673)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1526)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1482)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:745)
Caused by: javax.persistence.PersistenceException: org.hibernate.TransactionException: JDBC begin transaction failed: 
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1763)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:1771)
    at org.hibernate.jpa.internal.TransactionImpl.begin(TransactionImpl.java:64)
    at org.springframework.orm.jpa.DefaultJpaDialect.beginTransaction(DefaultJpaDialect.java:67)
    at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:380)
    ... 49 more
Caused by: org.hibernate.TransactionException: JDBC begin transaction failed: 
    at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:76)
    at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:162)
    at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1471)
    at org.hibernate.jpa.internal.TransactionImpl.begin(TransactionImpl.java:61)
    ... 51 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 65,266,256 milliseconds ago.  The last packet sent successfully to the server was 65,266,256 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1038)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3621)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2429)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2541)
    at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:4882)
    at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:371)
    at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setAutoCommit(PoolingDataSource.java:328)
    at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:72)
    ... 54 more
Caused by: java.net.SocketException: Broken pipe
    at java.net.SocketOutputStream.socketWrite0(Native Method)
    at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109)
    at java.net.SocketOutputStream.write(SocketOutputStream.java:153)
    at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
    at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3603)
    ... 61 more

看起来服务器与MySQL关闭了连接，我的应用服务器是Tomcat，可能是代码或Tomcat的问题？这是我的Spring配置类：

@EnableWebMvc
@Configuration
@PropertySource(value = { "classpath:application.properties" })
@ComponentScan({ "com.*" })
@EnableTransactionManagement
@Import({ SecurityConfig.class, SpringMvcInitializer.class})
@EnableJpaRepositories("com.repository")
public class AppConfig extends WebMvcConfigurerAdapter{
    @Autowired
    private Environment env;

    private static final String PROPERTY_NAME_DATABASE_DRIVER = "db.driver";
    private static final String PROPERTY_NAME_DATABASE_PASSWORD = "db.password";
    private static final String PROPERTY_NAME_DATABASE_URL = "db.url";
    private static final String PROPERTY_NAME_DATABASE_USERNAME = "db.username";

    private static final String PROPERTY_NAME_HIBERNATE_DIALECT = "hibernate.dialect";
    //  private static final String PROPERTY_NAME_HIBERNATE_SHOW_SQL = "hibernate.show_sql";
    private static final String PROPERTY_NAME_ENTITYMANAGER_PACKAGES_TO_SCAN = "entitymanager.packages.to.scan";
    private static final String PROPERTY_NAME_HIBERNATE_FORMAT_SQL = "hibernate.format_sql";


    /**
     * This and the next methods are used to avoid exception while jackson mapping the entity, so fields are setted with null value
     * unless use Hibernate.initialize
     * @return
     */
    public MappingJackson2HttpMessageConverter jacksonMessageConverter(){
        MappingJackson2HttpMessageConverter messageConverter = new MappingJackson2HttpMessageConverter();

        ObjectMapper mapper = new ObjectMapper();
        //Registering Hibernate4Module to support lazy objects
        mapper.registerModule(new Hibernate4Module());

        messageConverter.setObjectMapper(mapper);
        return messageConverter;

    }
    /**
     * Used for spring security
     * @return
     */
    @Bean
    public SpringSecurityDialect springSecurityDialect() {
        SpringSecurityDialect dialect = new SpringSecurityDialect();
        return dialect;
    }

    @Override
    public void configureMessageConverters(List<HttpMessageConverter<?>> converters) {
        //Here we add our custom-configured HttpMessageConverter
        converters.add(jacksonMessageConverter());
        super.configureMessageConverters(converters);
    }

    private Properties getHibernateProperties() {
        Properties properties = new Properties();
        properties.put(PROPERTY_NAME_HIBERNATE_DIALECT, env.getRequiredProperty(PROPERTY_NAME_HIBERNATE_DIALECT));
        //      properties.put(PROPERTY_NAME_HIBERNATE_SHOW_SQL, env.getRequiredProperty(PROPERTY_NAME_HIBERNATE_SHOW_SQL));
        properties.put(PROPERTY_NAME_HIBERNATE_FORMAT_SQL, env.getRequiredProperty(PROPERTY_NAME_HIBERNATE_FORMAT_SQL));
        properties.put("hibernate.enable_lazy_load_no_trans",true);
        return properties;
    }

    @Bean(name = "dataSource")
    public BasicDataSource dataSource() {
        BasicDataSource ds = new BasicDataSource();
        ds.setDriverClassName(env.getRequiredProperty(PROPERTY_NAME_DATABASE_DRIVER));
        ds.setUrl(env.getRequiredProperty(PROPERTY_NAME_DATABASE_URL));
        ds.setUsername(env.getRequiredProperty(PROPERTY_NAME_DATABASE_USERNAME));
        ds.setPassword(env.getRequiredProperty(PROPERTY_NAME_DATABASE_PASSWORD));
        return ds;
    }

    @Bean
    public ServletContextTemplateResolver TemplateResolver(){
        ServletContextTemplateResolver resolver = new ServletContextTemplateResolver();
        resolver.setPrefix("/WEB-INF/templates/pages/");
        resolver.setSuffix(".html");
        resolver.setTemplateMode("LEGACYHTML5");
        resolver.setCacheable(false);
        return resolver;
        /*ServletContextTemplateResolver resolver = new ServletContextTemplateResolver();
        resolver.setPrefix("/WEB-INF/pages/");
        resolver.setSuffix(".html");
        resolver.setTemplateMode("HTML5");
        return resolver;*/
    }

    @Bean
    public SpringTemplateEngine templateEngine(){
        SpringTemplateEngine templateEngine = new SpringTemplateEngine();
        templateEngine.setTemplateResolver(TemplateResolver());
        templateEngine.addDialect(springSecurityDialect());
        return templateEngine;
    }


    @Bean
    public ThymeleafViewResolver viewResolver() {
        ThymeleafViewResolver resolver = new ThymeleafViewResolver();
        resolver.setTemplateEngine(templateEngine());
        resolver.setOrder(1);
        resolver.setViewNames(new String[]{"*", "js/*", "template/*"});
        return resolver;
    }

    /**
     * Register multipartResolver for file upload
     * @return
     */
    @Bean
    public CommonsMultipartResolver multipartResolver() {
        CommonsMultipartResolver resolver=new CommonsMultipartResolver();
        resolver.setDefaultEncoding("utf-8");
        return resolver;    
    }

    /**
     * Allow use of bootstrap
     */
    @Override
    public void addResourceHandlers(ResourceHandlerRegistry registry) {
        registry.addResourceHandler("/static/**")
        .addResourceLocations("/static/");
    }

    /**
     * Allow use of JPA
     */
    @Bean
    public JpaTransactionManager transactionManager() {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(entityManagerFactory().getObject());
        return transactionManager;
    }
    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
        LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();
        entityManagerFactoryBean.setDataSource(dataSource());
        entityManagerFactoryBean.setPersistenceProviderClass(HibernatePersistenceProvider.class);
        entityManagerFactoryBean.setPackagesToScan(env.
                getRequiredProperty(PROPERTY_NAME_ENTITYMANAGER_PACKAGES_TO_SCAN));
        entityManagerFactoryBean.setJpaProperties(getHibernateProperties());
        return entityManagerFactoryBean;

    }

}

更新： 我创建了这个类。

@Service
@EnableScheduling
public class wakeUpDatabase {

    @Autowired
    private DatabaseFleetsAndCarsServices databaseFleetsAndCarsServices;
    //This task is executing every 7h
    @Scheduled(fixedDelay = 25200000)
    public void smartQeury(){
        databaseFleetsAndCarsServices.getEcu();
    }

}

我会测试它，并告诉你是否有效。

- luca

1

可能是重复问题：为什么Hibernate/JDBC/MySQL在一天左右会断开连接？，你的连接由于防火墙超时而被断开。 - Ferrybig

请检查此链接是否有帮助：http://stackoverflow.com/questions/14849856/com-mysql-jdbc-exceptions-jdbc4-communicationsexception-communications-link-fai - Rupesh

它说这可能是JDBC驱动程序超时的问题，但我该如何解决？我可以每6小时调用一次查询吗？ - luca

3个回答

0

看起来防火墙在一定的空闲时间后终止了连接。为了避免这种情况，您可以设置“BasicDataSource”类的“validationQuery”属性。

使用“select sysdate from dual”查询即可。

在AppConfig类的数据源配置方法中添加以下内容： ds.setValidationQuery("select sysdate from dual");

这样做将在返回连接对象给调用者之前验证池中的连接。如果连接已关闭，则会创建一个新的连接对象并返回给调用者。

- Shriram

0

根据堆栈跟踪，可以将autoReconnect=true设置为使用Connector/J连接属性autoReconnect=true来避免此问题。或者安排一个保持连接打开的tickle线程：

 /*
   HeartBeat is scheduled on a BackgroundJobScheduler
 */
private static class HeartBeat implements Runnable {
    private Connection conn;

    public HeartBeat(Connection conn) {
        this.conn = conn;
    }

    @Override
    public void run() {
        if (null != conn) {
            try {
                if (!conn.isClosed()) {
                    String queryStr = "select 1 from dual";
                    Statement st = conn.createStatement();
                    st.execute(queryStr);
                    st.close();
                }
            } catch (RuntimeException e){
                LOG.fatal("Uncaught Runtime Exception "+e.getMessage());
                return; // Keep working
            } catch (SQLException e){
                LOG.fatal("Uncaught SQL Exception "+e.getMessage());
                return; // Keep working
            } catch (Throwable e){
                LOG.fatal("Unrecoverable error "+e.getMessage());
                throw e; // abort
            }
        }
    }

}

将其安排为后台作业，并在应用程序终止时终止：

@WebListener
public class BackgroundJobScheduler implements ServletContextListener {
    private static ScheduledExecutorService scheduler;

    @Override
    public void contextInitialized(ServletContextEvent event) {
        scheduler = Executors.newSingleThreadScheduledExecutor();
    }

    public void schedule (Runnable task) {
        if (scheduler == null ) 
           scheduler = Executors.newSingleThreadScheduledExecutor();

        scheduler.scheduleAtFixedRate(task, 0, 14, TimeUnit.MINUTES);
    }

    @Override
    public void contextDestroyed(ServletContextEvent event) {
        scheduler.shutdownNow();
    }
}

- Johannes Jander

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- luca · Accepted Answer

我每7个小时使用一个查询解决了这个问题：

@Service
@EnableScheduling
public class wakeUpDatabase {

    @Autowired
    private DatabaseFleetsAndCarsServices databaseFleetsAndCarsServices;
    //This task is executing every 7h
    @Scheduled(fixedDelay = 25200000)
    public void smartQeury(){
        databaseFleetsAndCarsServices.getEcu();
    }

}