Spring4.0+Mybatis整合时占位符无法读取jdbc.properties的问题
1、在使用Spring+Mybatis整合时遇到了一个问题，在bean.xml配置文件引用外部jdbc.properties的时候报错，如下所示：
1 java.lang.ClassNotFoundException: ${jdbc.driver} 2 at org.apache.catalina.loader.WebappClassLoaderBase.loadClass(WebappClassLoaderBase.java:1358) 3 at org.apache.catalina.loader.WebappClassLoaderBase.loadClass(WebappClassLoaderBase.java:1180) 4 at java.lang.Class.forName0(Native Method) 5 at java.lang.Class.forName(Class.java:264) 6 at com.mchange.v2.c3p0.DriverManagerDataSource.ensureDriverLoaded(DriverManagerDataSource.java:100) 7 at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:132) 8 at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182) 9 at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171) 10 at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137) 11 at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014) 12 at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32) 13 at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810) 14 at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547) 15 [laboratory] 2021-02-24 09:40:42.339 < WARN> [com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0] BasicResourcePool.run(1841) | com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@398ac721 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: 16 java.sql.SQLException: No suitable driver 17 at java.sql.DriverManager.getDriver(DriverManager.java:315) 18 at com.mchange.v2.c3p0.DriverManagerDataSource.driver(DriverManagerDataSource.java:223) 19 at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134) 20 at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182) 21 at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171) 22 at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137) 23 at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014) 24 at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32) 25 at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810) 26 at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547) 27 org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 28 ### Error querying database. Cause: java.lang.RuntimeException: java.sql.SQLException: Connections could not be acquired from the underlying database! 29 ### Cause: java.lang.RuntimeException: java.sql.SQLException: Connections could not be acquired from the underlying database! 30 at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:77) 31 at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446) 32 at com.sun.proxy.$Proxy11.selectOne(Unknown Source) 33 at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:166) 34 at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:82) 35 at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59) 36 at com.sun.proxy.$Proxy16.userInfoLogin(Unknown Source) 37 at com.flea.service.impl.UserInfoServiceImpl.userInfoLogin(UserInfoServiceImpl.java:23) 38 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 39 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 40 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 41 at java.lang.reflect.Method.invoke(Method.java:498) 42 at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) 43 at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) 44 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) 45 at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98) 46 at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262) 47 at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95) 48 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) 49 at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) 50 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) 51 at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207) 52 at com.sun.proxy.$Proxy25.userInfoLogin(Unknown Source) 53 at com.flea.action.UserController.login(UserController.java:39) 54 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 55 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 56 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 57 at java.lang.reflect.Method.invoke(Method.java:498) 58 at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215) 59 at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132) 60 at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104) 61 at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:749) 62 at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:690) 63 at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83) 64 at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:945) 65 at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:876) 66 at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961) 67 at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:863) 68 at javax.servlet.http.HttpServlet.service(HttpServlet.java:652) 69 at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837) 70 at javax.servlet.http.HttpServlet.service(HttpServlet.java:733) 71 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) 72 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) 73 at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) 74 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) 75 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) 76 at com.flea.utils.UtfFilter.doFilter(UtfFilter.java:28) 77 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) 78 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) 79 at com.flea.utils.LoginFilter.doFilter(LoginFilter.java:43) 80 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) 81 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) 82 at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88) 83 at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:108) 84 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) 85 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) 86 at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199) 87 at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) 88 at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:544) 89 at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143) 90 at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81) 91 at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:690) 92 at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) 93 at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) 94 at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:616) 95 at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) 96 at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:831) 97 at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1634) 98 at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) 99 at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) 100 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) 101 at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) 102 at java.lang.Thread.run(Thread.java:748) 103 Caused by: org.apache.ibatis.exceptions.PersistenceException: 104 ### Error querying database. Cause: java.lang.RuntimeException: java.sql.SQLException: Connections could not be acquired from the underlying database! 105 ### Cause: java.lang.RuntimeException: java.sql.SQLException: Connections could not be acquired from the underlying database! 106 at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30) 107 at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150) 108 at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141) 109 at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:77) 110 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 111 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 112 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 113 at java.lang.reflect.Method.invoke(Method.java:498) 114 at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433) 115 ... 71 more 116 Caused by: java.lang.RuntimeException: java.sql.SQLException: Connections could not be acquired from the underlying database! 117 at com.github.pagehelper.PageHelper.getUrl(PageHelper.java:287) 118 at com.github.pagehelper.PageHelper.getSqlUtil(PageHelper.java:310) 119 at com.github.pagehelper.PageHelper.initSqlUtil(PageHelper.java:266) 120 at com.github.pagehelper.PageHelper.intercept(PageHelper.java:253) 121 at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) 122 at com.sun.proxy.$Proxy34.query(Unknown Source) 123 at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148) 124 ... 78 more 125 Caused by: java.sql.SQLException: Connections could not be acquired from the underlying database! 126 at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106) 127 at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:529) 128 at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128) 129 at com.github.pagehelper.PageHelper.getUrl(PageHelper.java:284) 130 ... 84 more 131 Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source. 132 at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1319) 133 at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557) 134 at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477) 135 at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525) 136 ... 86 more
我的bean.xml配置文件，如果直接引用数据库的驱动，账号密码，url就不会出错的，如下所示：
1 <!-- 引入外部配置文件,加载jdbc.properties里面的值 --> 2 <context:property-placeholder location="classpath:jdbc.properties" /> 3 4 <!-- 配置数据源dataSource,记得去掉mybatils.cfg.xml的数据源相关配置 --> 5 <bean id="dataSource" 6 class="com.mchange.v2.c3p0.ComboPooledDataSource"> 7 <property name="jdbcUrl" value="${jdbc.url}" /> 8 <property name="driverClass" value="${jdbc.driver}" /> 9 <property name="user" value="${jdbc.username}" /> 10 <property name="password" value="${jdbc.password}" /> 11 <!-- <property name="jdbcUrl" value="jdbc:mysql://127.0.0.1:3306/laboratory_sys?useUnicode=true&characterEncoding=UTF-8" /> 12 <property name="driverClass" value="com.mysql.jdbc.Driver" /> 13 <property name="user" value="root" /> 14 <property name="password" value="123456" /> 15 --> 16 </bean>
这个问题是无法识别占位符，就是在加载过程中直接把${jdbc.driver}当做字符串处理了，具体报错问题就是，MapperScannerConfigurer 先于properties文件处理，意思是没进行占位符的变量赋值就被使用了，去掉sqlSessionFactory这个属性，我最开始的配置，如下所示：
1 <!-- 配置 转换器，对于在basePackage设置的包（包括子包）下的接口类， 2 如果接口类的全类名在Mapper.xml文件中和定义过命名空间一致， 3 将被转换成spring的bean，在调用 的地方通过@Autowired方式将可以注入接口实例 4 --> 5 <!-- 注意: 1、配置mybatis的代理接口开发。 6 2、接口类名和映射文件必须同名。 7 3、接口类和映射文件必须在同一个目录下。 8 4、接口的映射文件的namespace名称必须是接口的全限定名。 9 5、接口的方法名必须和映射的statement的id一致。 --> 10 <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> 11 <property name="sqlSessionFactory" ref="sqlSessionFactory" /> 12 <!-- 扫描所有dao接口的实现，加入到ioc容器中 --> 13 <property name="basePackage" value="com.flea.dao" /> 14 </bean>
去掉sqlSessionFactory这个属性，之后，变成下面的样子了，如下所示：
1 <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> 2 <!-- <property name="sqlSessionFactory" ref="sqlSessionFactory" /> --> 3 <!-- 扫描所有dao接口的实现，加入到ioc容器中 --> 4 <property name="basePackage" value="com.flea.dao" /> 5 </bean>
我的session工厂配置，同时记得加上这样的bean，id的值是sqlSessionFactory，因为要使用的时候会去找这个bean去连接数据库。
1 <!-- 配置session工厂 --> 2 <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> 3 <property name="dataSource" ref="dataSource" /> 4 <property name="configLocation" value="classpath:mybatils.cfg.xml" /> 5 <!--配置扫描式加载SQL映射文件,记得去掉mybatis-config配置 --> 6 <property name="mapperLocations" value="classpath:com/flea/dao/*.xml" /> 7 </bean>
重启项目，测试，发现已经正常了。
2、此时，如果操作数据库发现乱码，可以修改url的字符集，我的jdbc.properties配置，如下所示：
1 jdbc.driver=com.mysql.jdbc.Driver 2 jdbc.url=jdbc:mysql://127.0.0.1:3306/laboratory_sys?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true 3 jdbc.username=root 4 jdbc.password=123456
