springboot启动加载org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement;

使用Springboot + JPA + Druid连接池

1，项目启动时报错

2，检查配置文件中连接池的连接串

#######################oracle数据库连接池配置【start】############################
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.url=jdbc:oracle:thin:@10.7.101.38:1521:orcl
spring.datasource.druid.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.datasource.druid.username=wmxt_img
spring.datasource.druid.password=wmxt_img
# 初始化大小，最小，最大
spring.datasource.druid.initial-size=10
spring.datasource.druid.min-idle=10
spring.datasource.druid.max-active=20
# 配置获取连接等待超时的时间
spring.datasource.druid.max-wait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.druid.time-between-eviction-runs-millis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.druid.min-evictable-idle-time-millis=300000
#检测连接是否有效的sql
spring.datasource.druid.validation-query=SELECT 'x' FROM DUAL
spring.datasource.druid.test-while-idle=true
spring.datasource.druid.test-on-borrow=false
spring.datasource.druid.test-on-return=false
#打开PSCache，并且指定每个连接上PSCache的大小
spring.datasource.druid.pool-prepared-statements=true
spring.datasource.druid.max-pool-prepared-statement-per-connection-size=20
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.druid.filters=stat,wall,log4j
# 合并多个DruidDataSource的监控数据
#spring.datasource.druid.use-global-data-source-stat=true
spring.datasource.druid.connection-properties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
spring.datasource.druid.login-user-name=admin
spring.datasource.druid.login-password=123456
#Druid监控ip白名单设置(没有配置或者为空，则允许所有访问)
spring.datasource.druid.ip-allow=
#spring.datasource.druid.ip-allow="127.0.0.1,10.7.101.77"
#######################oracle数据库连接池配置【end】############################

#######################jpa配置【start】##########################
spring.jpa.database=ORACLE
spring.jpa.show-sql=false
#######################jpa配置【end】############################

3，检查pom文件中依赖的jar包

JPA包

ojdbc包和druid包， 因为oracle是12c版本，所以通过jar包方式引入

 

 

 

检查都没问题，竟发现是：需要在代码中将Druid实例注入到容器中

DruidDataSourceConfig：

package cn.com.fotic.eimp.ftp.druid;

import com.alibaba.druid.pool.DruidDataSource;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Primary;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.SQLException;


/**
 * @Title: DruidDataSourceConfig.java
 * @Description:  druid 定制信息配置
 * @author: liqiang
 * @date: 2018年12月14日 上午10:52:36
 */
@Data
@Component
@ConditionalOnClass(DruidDataSource.class)
@ConditionalOnProperty(name = "spring.datasource.type", havingValue = "com.alibaba.druid.pool.DruidDataSource", matchIfMissing = true)
@ConfigurationProperties(prefix = "spring.datasource.druid")
@Slf4j
public class DruidDataSourceConfig {

        private String url;
        private String username;
        private String password;
        private String driverClassName;
        private int initialSize;
        private int minIdle;
        private int maxActive;
        private int maxWait;
        private int timeBetweenEvictionRunsMillis;
        private int minEvictableIdleTimeMillis;
        private String validationQuery;
        private boolean testWhileIdle;
        private boolean testOnBorrow;
        private boolean testOnReturn;
        private boolean poolPreparedStatements;
        private int maxPoolPreparedStatementPerConnectionSize;
        private String filters;
        private String connectionProperties;
        private String loginUserName;
        private String loginPassword;
        private String ipAllow;

        // 解决 spring.datasource.filters=stat,wall,log4j 无法正常注册
        @Bean
        @Primary
        // 在同样的DataSource中，首先使用被标注的DataSource
        public DataSource dataSource() {
            DruidDataSource datasource = new DruidDataSource();
            datasource.setUrl(url);
            datasource.setUsername(username);
            datasource.setPassword(password);
            datasource.setDriverClassName(driverClassName);

            // configuration
            datasource.setInitialSize(initialSize);
            datasource.setMinIdle(minIdle);
            datasource.setMaxActive(maxActive);
            datasource.setMaxWait(maxWait);
            datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
            datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
            datasource.setValidationQuery(validationQuery);
            datasource.setTestWhileIdle(testWhileIdle);
            datasource.setTestOnBorrow(testOnBorrow);
            datasource.setTestOnReturn(testOnReturn);
            datasource.setPoolPreparedStatements(poolPreparedStatements);
            datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
            try {
                datasource.setFilters(filters);
                log.info("druid configuration initialization filter...");
            } catch (SQLException e) {
                log.error("druid configuration initialization filter: " + e);
            }
            datasource.setConnectionProperties(connectionProperties);
            return datasource;
        }

}

DruidMonitorConfig --Druid客户端配置：

package cn.com.fotic.eimp.ftp.druid;

import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringBootConfiguration;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;

/**
 * @Title: DruidMonitorConfig.java
 * @Description:
 * @author: liqiang
 * @date: 2018年12月14日 上午11:13:02
 */
@Slf4j
@SpringBootConfiguration
public class DruidMonitorConfig {

    @Autowired
    private DruidDataSourceConfig druidDataSourceConfig;

    @Bean
    public ServletRegistrationBean servletRegistrationBean() {
        log.info("init Druid Monitor Servlet ...");
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(),"/druid/*");
        // IP白名单 (没有配置或者为空，则允许所有访问)
        servletRegistrationBean.addInitParameter("allow", druidDataSourceConfig.getIpAllow());
        // IP黑名单(共同存在时，deny优先于allow)
//        servletRegistrationBean.addInitParameter("deny", "172.16.103.173");
        // 控制台管理用户
//        servletRegistrationBean.addInitParameter("loginUserName", "admin");
//        servletRegistrationBean.addInitParameter("loginPassword", "123456");
        servletRegistrationBean.addInitParameter("loginUserName", druidDataSourceConfig.getLoginUserName());
        servletRegistrationBean.addInitParameter("loginPassword", druidDataSourceConfig.getPassword());
        // 是否能够重置数据 禁用HTML页面上的“Reset All”功能
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean;
    }

    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }

}

 

启动ok