这是原本的DataSource配置
@Bean
public DataSource poolData(){
DataSource dt = new DataSource();
//设置基本属性
dt.setDriverClassName(env.getProperty("sheshan.driver"));
dt.setUrl(env.getProperty("sheshan.url"));
dt.setUsername(env.getProperty("sheshan.user"));
dt.setPassword(env.getProperty("sheshan.pwd"));
//dt.setDataSource(datatemp());
// 配置初始化大小、最小、最大连接数
dt.setInitialSize(1);
dt.setMinIdle(1);
dt.setMaxIdle(100);
// 配置获取连接等待超时的时间
dt.setMaxWait(10000);
//配置检测时间,检测关闭空闲链接
dt.setTimeBetweenEvictionRunsMillis(60000);
//配置连接池最小生存时间
dt.setMinEvictableIdleTimeMillis(600000);
return dt;
}
启动项目、执行查询时报错
java.sql.SQLException: com.mysql.jdbc.Driver
at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:254)
at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:182)
at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:701)
at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:635)
at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:486)
at org.apache.tomcat.jdbc.pool.ConnectionPool.<init>(ConnectionPool.java:144)
at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:116)
at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:103)
at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:127)
at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)
at org.mybatis.spring.transaction.SpringManagedTransaction.openConnection(SpringManagedTransaction.java:84)
at org.mybatis.spring.transaction.SpringManagedTransaction.getConnection(SpringManagedTransaction.java:70)
at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:336)
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:84)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:77)
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.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:434)
at com.sun.proxy.$Proxy24.selectOne(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:167)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:82)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53)
at com.sun.proxy.$Proxy25.selectByID(Unknown Source)
at sheshan.com.service.UserService.selectByID(UserService.java:17)
at sheshan.com.action.TestAction.testPage(TestAction.java:24)
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.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:114)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:624)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:957)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:423)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1079)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:620)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Unknown Source)
at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:246)
... 71 more
在网上查找后发现, 原来直接使用tomcat jdbc pool 的连接池无法加载驱动类。
解决方案1:将jdbc的jar包放到tomcat的lib目录下才能使用。但是由于项目需要在不同环境中运行, 这样做会导致很大的风险,所以我采用了方案2;
方案2:为了更好的看配置,我把整个DataSource配置贴出来。对于该配置, 我个人的理解是:将加载驱动的动作交给Spring来做,然后由tomcat 去连接。
@Configuration
@EnableTransactionManagement
@MapperScan(basePackages={"sheshan.com.**.mapper"},sqlSessionFactoryRef= "sessionFactory")
public class DataConfig {
@Autowired
public Environment env;
@Bean
public DataSource poolData(){
DataSource dt = new DataSource();
//设置基本属性
// dt.setDriverClassName(env.getProperty("sheshan.driver"));
// dt.setUrl(env.getProperty("sheshan.url"));
// dt.setUsername(env.getProperty("sheshan.user"));
// dt.setPassword(env.getProperty("sheshan.pwd"));
dt.setDataSource(datatemp());
// 配置初始化大小、最小、最大连接数
dt.setInitialSize(1);
dt.setMinIdle(1);
dt.setMaxIdle(100);
// 配置获取连接等待超时的时间
dt.setMaxWait(10000);
//配置检测时间,检测关闭空闲链接
dt.setTimeBetweenEvictionRunsMillis(60000);
//配置连接池最小生存时间
dt.setMinEvictableIdleTimeMillis(600000);
return dt;
}
@Bean
public SqlSessionFactory sessionFactory() throws Exception{
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setDataSource(poolData());
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
factoryBean.setMapperLocations(resolver.getResources("classpath:sheshan/com/**/*.xml"));
return factoryBean.getObject();
}
@Bean
public PlatformTransactionManager txManager(){
return new DataSourceTransactionManager(poolData());
}
public DriverManagerDataSource datatemp(){
DriverManagerDataSource dtm = new DriverManagerDataSource();
dtm.setDriverClassName(env.getProperty("sheshan.driver"));
dtm.setUrl(env.getProperty("sheshan.url"));
dtm.setUsername(env.getProperty("sheshan.user"));
dtm.setPassword(env.getProperty("sheshan.pwd"));
return dtm;
}
方案3:根据网络多数据池对比, 还可以采用Druid 数据池。Druid数据池和tomcat jdbc pool不太一样, 在我的测试中, Druid是不需要通过Spring去加载驱动的。暂时不清楚什么原因。有知道的大神, 请留言告诉我。 代码如下
@Bean
public DruidDataSource druidData(){
DruidDataSource dds = new DruidDataSource();
dds.setDriverClassName(env.getProperty("sheshan.driver"));
dds.setUrl(env.getProperty("sheshan.url"));
dds.setUsername(env.getProperty("sheshan.user"));
dds.setPassword(env.getProperty("sheshan.pwd"));
dds.setInitialSize(1);
dds.setMinIdle(1);
//dds.setMaxIdle(100); 已失效
dds.setMaxActive(100); //配置最大连接数
dds.setMaxWait(60000);//设置最大等待时间
dds.setMinEvictableIdleTimeMillis(300000);//是指最短生存时间
return dds;
欢迎大神指正