一、在webapp2项目的父pom文件中，引入dbcp2连接池、mybatis、mysql驱动等依赖包
<!-- dbcp2连接池 --> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-dbcp2</artifactId> <version>2.6.0</version> </dependency> <!-- mybatis ORM框架 --> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.1</version> </dependency> <!-- mybatis-spring适配器 --> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis-spring</artifactId> <version>1.3.2</version> </dependency> <!-- mysql数据库驱动 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.16</version> </dependency>二、在webapp2_web模块src/main/resources/env下，建立jdbc-dev.properties
###dbcp2#### jdbc.driverClassName=com.mysql.cj.jdbc.Driver #jdbc.url=jdbc:mysql://localhost:3306/webapp2 jdbc.url=jdbc:mysql://localhost:3306/webapp2?serverTimezone=UTC jdbc.username=user2 jdbc.password=654321 jdbc.initialSize=20 jdbc.maxTotal=100 jdbc.maxIdle=30 jdbc.minIdle=10 jdbc.logAbandoned=true jdbc.removeAbandonedOnMaintenance=true jdbc.removeAbandonedTimeout=60 jdbc.maxWaitMillis=-1 jdbc.defaultAutoCommit=false jdbc.testOnBorrow=true jdbc.validationQuery=select 1 from dual说明： 1、driverClassName：数据库驱动，com.mysql.jdbc.Driver已经废弃，换成最新的 2、logAbandoned：当Statement或连接被泄露时是否打印程序的stack traces日志 3、removeAbandonedOnMaintenance：配置检测连接池泄露的方式:OnMaintenance按照间隔时间,OnBorrow每次获取连接时 4、timeBetweenEvictionRunsMillis：配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 5、removeAbandonedTimeout：泄露的连接被删除的超时时间 6、maxWaitMillis：线程池等待超时时间 7、defaultAutoCommit：标识连接是否自动提交 8、testOnBorrow：标识是否检测连接可用 9、validationQuery：验证数据库连接是否可用的查询语句，mysql也有dual表
三、在webapp2_web模块src/main/resources下，建立spring-datasource.xml
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd"> <!-- 测试环境环境配置文件 --> <beans profile="dev"> <!-- 1.引入属性文件 --> <context:property-placeholder ignore-unresolvable="true" location="classpath:env/jdbc-dev.properties" /> <!-- 2.配置dbcp2数据源 --> <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource"> <!-- 数据库驱动 --> <property name="driverClassName" value="${jdbc.driverClassName}" /> <!-- 连接数据库的url --> <property name="url" value="${jdbc.url}" /> <!-- 连接数据库的用户名 --> <property name="username" value="${jdbc.username}" /> <!-- 连接数据库的密码 --> <property name="password" value="${jdbc.password}" /> <!-- 初始连接数 --> <property name="initialSize" value="${jdbc.initialSize}" /> <!-- 最大连接数 --> <property name="maxTotal" value="${jdbc.maxTotal}" /> <!-- 最大空闲连接数 --> <property name="maxIdle" value="${jdbc.maxIdle}" /> <!-- 最小空闲连接数 --> <property name="minIdle" value="${jdbc.minIdle}" /> <!-- 当Statement或连接被泄露时是否打印程序的stack traces日志 --> <property name="logAbandoned" value="${jdbc.logAbandoned}" /> <!-- 配置检测连接池泄露的方式:OnMaintenance按照间隔时间,OnBorrow每次获取连接时 --> <property name="removeAbandonedOnMaintenance" value="${jdbc.removeAbandonedOnMaintenance}" /> <!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 --> <property name="timeBetweenEvictionRunsMillis" value="60000" /> <!-- 泄露的连接被删除的超时时间 --> <property name="removeAbandonedTimeout" value="${jdbc.removeAbandonedTimeout}" /> <!-- 线程池等待超时时间 --> <property name="maxWaitMillis" value="${jdbc.maxWaitMillis}" /> <!-- 标识连接是否自动提交 --> <property name="defaultAutoCommit" value="${jdbc.defaultAutoCommit}" /> <!-- 标识是否检测连接可用 --> <property name="testOnBorrow" value="${jdbc.testOnBorrow}" /> <!-- 验证数据库连接是否可用的查询语句 --> <property name="validationQuery" value="${jdbc.validationQuery}" /> </bean> </beans> </beans>四、在webapp2_web模块src/main/resources下，建立spring-transaction.xml
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd"> <!-- 3.配置mybatis会话工厂 --> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="dataSource" /> <property name="configLocation" value="classpath:/mybatis/mybatis-config.xml" /> <property name="mapperLocations" value="classpath*:/mybatis/mapper/**/*.xml" /> </bean> <!-- 4.自动扫描对象关系映射 --> <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <!-- Mapper接口所在包名，Spring会自动查找其下的Mapper --> <property name="basePackage" value="com.study" /> <!--annotation 标注Mapper --> <property name="annotationClass" value="com.study.base.mybatis.annotation.MyBatis" /> </bean> <!-- 5.定义事物管理器, 由spring管理事务 --> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource" /> </bean> <!-- 6.注解事务 --> <tx:annotation-driven /> </beans>五、在webapp2_web模块src/main/resources下建立mybatis目录，并建立mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <properties> <property name="dialect" value="mysql" /> </properties> <!-- 配置mybatis的缓存，延迟加载等等一系列属性 --> <settings> <!-- 全局映射器启用缓存 --> <setting name="cacheEnabled" value="true" /> <!-- 查询时，关闭关联对象即时加载以提高性能 --> <setting name="lazyLoadingEnabled" value="true" /> <!-- 设置关联对象加载的形态，此处为按需加载字段(加载字段由SQL指 定)，不会加载关联表的所有字段，以提高性能 --> <setting name="aggressiveLazyLoading" value="false" /> <!-- 对于未知的SQL查询，允许返回不同的结果集以达到通用的效果 --> <setting name="multipleResultSetsEnabled" value="true" /> <!-- 允许使用列标签代替列名 --> <setting name="useColumnLabel" value="true" /> <!-- 允许使用自定义的主键值(比如由程序生成的UUID 32位编码作为键值)，数据表的PK生成策略将被覆盖 --> <!-- <setting name="useGeneratedKeys" value="true" /> --> <!-- 给予被嵌套的resultMap以字段-属性的映射支持 --> <!-- 对于批量更新操作缓存SQL以提高性能 --> <setting name="defaultExecutorType" value="SIMPLE" /> <setting name="autoMappingBehavior" value="FULL" /> <!-- 数据库超过25000秒仍未响应则超时 --> <setting name="defaultStatementTimeout" value="25000" /> <!-- 允许插入 NULL --> <setting name="jdbcTypeForNull" value="NULL" /> </settings> <!-- <plugins> <plugin interceptor="com.study.base.mybatis.page.PaginationInterceptor" /> </plugins> --> </configuration>六、在webapp2_base模块建立Mybatis.java
package com.study.base.mybatis.annotation; import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; /** * MyBatis Mapper annotation * */ @Retention(RetentionPolicy.RUNTIME) @Target(ElementType.TYPE) public @interface MyBatis { }七、在webapp2_domain建立一个POJO类，Teacher.java
package com.study.testBean; public class Teacher { public int id; public String name; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } }八、在webapp2_base模块建立dao层 1、webapp2_base模块添加webapp2_domain模块依赖关系
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>com.study</groupId> <artifactId>webapp2</artifactId> <version>0.0.1-SNAPSHOT</version> </parent> <artifactId>webapp2_base</artifactId> <name>webapp2_base</name> <dependencies> <dependency> <groupId>com.study</groupId> <artifactId>webapp2_domain</artifactId> <version>0.0.1-SNAPSHOT</version> </dependency> </dependencies> </project>2、添加dao类，TeacherMapper.java
package com.study.base.dao; import org.apache.ibatis.annotations.Param; import com.study.base.mybatis.annotation.MyBatis; import com.study.testBean.Teacher; @MyBatis public interface TeacherMapper { /** * 根据id */ public Teacher getTeacherById(@Param("id") int id); }九、在webapp2_base模块src/main/resources下建立mybatis/mapper目录，并建立TeacherMapper.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace="com.study.base.dao.TeacherMapper"> <resultMap id="TeacherInf" type="com.study.testBean.Teacher"> <result column="ID" property="id" /> <result column="NAME" property="name" /> </resultMap> <sql id="Teacher_Column_List"> ID, NAME </sql> <select id="getTeacherById" resultMap="TeacherInf" parameterType="int"> select <include refid="Teacher_Column_List" /> from tbl_teacher_inf <where> ID = #{id} </where> </select> </mapper>十、建立测试表和数据
drop table tbl_teacher_inf; create table tbl_teacher_inf ( id int not null, name varchar(100), primary key (id) ); insert into tbl_teacher_inf values (1, '刘备'); insert into tbl_teacher_inf values (2, '张飞'); insert into tbl_teacher_inf values (3, '关羽');十一、测试一下，在MyTest.java添加测试mybatis案例 执行testMybatis方法
package webapp; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.beans.factory.annotation.Value; import org.springframework.context.ApplicationContext; import org.springframework.test.context.ActiveProfiles; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; import org.springframework.test.context.web.WebAppConfiguration; import com.study.base.dao.TeacherMapper; import com.study.testBean.Student; import com.study.testBean.Teacher; import com.study.util.ApplicationContextHolder; import com.study.util.PropertiesUtil; import com.study.util.ReflectInvokeUtil; @RunWith(SpringJUnit4ClassRunner.class) @WebAppConfiguration @ContextConfiguration(locations = {"classpath:applicationContext.xml","classpath:spring-mvc.xml"}) @ActiveProfiles("dev") public class MyTest { @Value("${flag}") String flag; @Autowired Student stu; @Autowired TeacherMapper teacherMapper; @Test public void testInvoke() throws Exception { System.out.println("第一个测试方法*******"); ApplicationContext ac = ApplicationContextHolder.getApplicationContext(); ReflectInvokeUtil.invokeMethod("com.study.testBean.Student", "sayHello", null); } @Test public void testById() { System.out.println("第二个测试方法*******"); ApplicationContext ac = ApplicationContextHolder.getApplicationContext(); Student stu = (Student)ApplicationContextHolder.getBean("id.student1"); stu.sayHello(); System.out.println("**********"); } @Test public void testProperties() { System.out.println("第三个测试方法*******"); System.out.println("flag is:" + flag); System.out.println("appName is:" + PropertiesUtil.getProperties("appName")); } @Test public void testAutowired() { System.out.println("第四个测试方法*******"); stu.sayHello(); System.out.println("**********"); } @Test public void testMybatis() { System.out.println("第五个测试方法*******"); Teacher teacher = teacherMapper.getTeacherById(1); System.out.println("id is: " + teacher.getId()); System.out.println("name is: " + teacher.getName()); } }1、发生报错
### Error querying database. Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Cannot create PoolableConnectionFactory (The server time zone value '�й���ʱ��' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.) ### The error may exist in file [D:\workspace\study\webapp2\webapp2_base\target\classes\mybatis\mapper\TeacherMapper.xml] ### The error may involve com.study.base.dao.TeacherMapper.getTeacherById ### The error occurred while executing a query ### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Cannot create PoolableConnectionFactory (The server time zone value '�й���ʱ��' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.), mergedContextConfiguration = [WebMergedContextConfiguration@548e7350 testClass = MyTest, locations = '{classpath:applicationContext.xml, classpath:spring-mvc.xml}', classes = '{}', contextInitializerClasses = '[]', activeProfiles = '{dev}', propertySourceLocations = '{}', propertySourceProperties = '{}', contextCustomizers = set[[empty]], resourceBasePath = 'src/main/webapp', contextLoader = 'org.springframework.test.context.web.WebDelegatingSmartContextLoader', parent = [null]]], class annotated with @DirtiesContext [false] with mode [null], method annotated with @DirtiesContext [false] with mode [null]. 15:33:00.259 [main] DEBUG org.springframework.test.context.web.ServletTestExecutionListener - Resetting RequestContextHolder for test context [DefaultTestContext@5442a311 testClass = MyTest, testInstance = webapp.MyTest@7b94089b, testMethod = testMybatis@MyTest, testException = org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: ### Error querying database. Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Cannot create PoolableConnectionFactory (The server time zone value '�й���ʱ��' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.) ### The error may exist in file [D:\workspace\study\webapp2\webapp2_base\target\classes\mybatis\mapper\TeacherMapper.xml] ### The error may involve com.study.base.dao.TeacherMapper.getTeacherById ### The error occurred while executing a query ### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Cannot create PoolableConnectionFactory (The server time zone value '�й���ʱ��' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.), mergedContextConfiguration = [WebMergedContextConfiguration@548e7350 testClass = MyTest, locations = '{classpath:applicationContext.xml, classpath:spring-mvc.xml}', classes = '{}', contextInitializerClasses = '[]', activeProfiles = '{dev}', propertySourceLocations = '{}', propertySourceProperties = '{}', contextCustomizers = set[[empty]], resourceBasePath = 'src/main/webapp', contextLoader = 'org.springframework.test.context.web.WebDelegatingSmartContextLoader', parent = [null]]].经查询原因为使用的mysql驱动包版本比较高，需要添加一些参数 将
jdbc.url=jdbc:mysql://localhost:3306/webapp2改为
jdbc.url=jdbc:mysql://localhost:3306/webapp2?serverTimezone=UTC执行成功
15:59:41.290 [main] DEBUG org.springframework.test.context.support.AbstractDirtiesContextTestExecutionListener - Before test method: context [DefaultTestContext@5442a311 testClass = MyTest, testInstance = webapp.MyTest@7b94089b, testMethod = testMybatis@MyTest, testException = [null], mergedContextConfiguration = [WebMergedContextConfiguration@548e7350 testClass = MyTest, locations = '{classpath:applicationContext.xml, classpath:spring-mvc.xml}', classes = '{}', contextInitializerClasses = '[]', activeProfiles = '{dev}', propertySourceLocations = '{}', propertySourceProperties = '{}', contextCustomizers = set[[empty]], resourceBasePath = 'src/main/webapp', contextLoader = 'org.springframework.test.context.web.WebDelegatingSmartContextLoader', parent = [null]]], class annotated with @DirtiesContext [false] with mode [null], method annotated with @DirtiesContext [false] with mode [null]. 第五个测试方法******* 15:59:41.299 [main] DEBUG org.mybatis.spring.SqlSessionUtils - Creating a new SqlSession 15:59:41.336 [main] DEBUG org.mybatis.spring.SqlSessionUtils - SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2df6226d] was not registered for synchronization because synchronization is not active 15:59:41.368 [main] DEBUG org.springframework.jdbc.datasource.DataSourceUtils - Fetching JDBC Connection from DataSource 15:59:41.651 [main] DEBUG org.mybatis.spring.transaction.SpringManagedTransaction - JDBC Connection [1850597787, URL=jdbc:mysql://localhost:3306/webapp2?serverTimezone=UTC, UserName=user2@localhost, MySQL Connector/J] will not be managed by Spring 15:59:41.657 [main] DEBUG com.study.base.dao.TeacherMapper.getTeacherById - ==> Preparing: select ID, NAME from tbl_teacher_inf WHERE ID = ? 15:59:41.666 [main] DEBUG com.study.base.dao.TeacherMapper.getTeacherById - ==> Parameters: 1(Integer) 15:59:41.678 [main] DEBUG com.study.base.dao.TeacherMapper.getTeacherById - <== Total: 1 15:59:41.681 [main] DEBUG org.mybatis.spring.transaction.SpringManagedTransaction - Committing JDBC Connection [1850597787, URL=jdbc:mysql://localhost:3306/webapp2?serverTimezone=UTC, UserName=user2@localhost, MySQL Connector/J] 15:59:41.681 [main] DEBUG org.mybatis.spring.SqlSessionUtils - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2df6226d] id is: 1 name is: 刘备 15:59:41.682 [main] DEBUG org.springframework.test.context.support.AbstractDirtiesContextTestExecutionListener - After test method: context [DefaultTestContext@5442a311 testClass = MyTest, testInstance = webapp.MyTest@7b94089b, testMethod = testMybatis@MyTest, testException = [null], mergedContextConfiguration = [WebMergedContextConfiguration@548e7350 testClass = MyTest, locations = '{classpath:applicationContext.xml, classpath:spring-mvc.xml}', classes = '{}', contextInitializerClasses = '[]', activeProfiles = '{dev}', propertySourceLocations = '{}', propertySourceProperties = '{}', contextCustomizers = set[[empty]], resourceBasePath = 'src/main/webapp', contextLoader = 'org.springframework.test.context.web.WebDelegatingSmartContextLoader', parent = [null]]], class annotated with @DirtiesContext [false] with mode [null], method annotated with @DirtiesContext [false] with mode [null]. 15:59:41.683 [main] DEBUG org.springframework.test.context.web.ServletTestExecutionListener - Resetting RequestContextHolder for test context [DefaultTestContext@5442a311 testClass = MyTest, testInstance = webapp.MyTest@7b94089b, testMethod = testMybatis@MyTest, testException = [null], mergedContextConfiguration = [WebMergedContextConfiguration@548e7350 testClass = MyTest, locations = '{classpath:applicationContext.xml, classpath:spring-mvc.xml}', classes = '{}', contextInitializerClasses = '[]', activeProfiles = '{dev}', propertySourceLocations = '{}', propertySourceProperties = '{}', contextCustomizers = set[[empty]], resourceBasePath = 'src/main/webapp', contextLoader = 'org.springframework.test.context.web.WebDelegatingSmartContextLoader', parent = [null]]]. 15:59:41.684 [main] DEBUG org.springframework.test.context.support.AbstractDirtiesContextTestExecutionListener - After test class: context [DefaultTestContext@5442a311 testClass = MyTest, testInstance = [null], testMethod = [null], testException = [null], mergedContextConfiguration = [WebMergedContextConfiguration@548e7350 testClass = MyTest, locations = '{classpath:applicationContext.xml, classpath:spring-mvc.xml}', classes = '{}', contextInitializerClasses = '[]', activeProfiles = '{dev}', propertySourceLocations = '{}', propertySourceProperties = '{}', contextCustomizers = set[[empty]], resourceBasePath = 'src/main/webapp', contextLoader = 'org.springframework.test.context.web.WebDelegatingSmartContextLoader', parent = [null]]], class annotated with @DirtiesContext [false] with mode [null].十二、例子程序webapp2_2.rar 链接: https://pan.baidu.com/s/11ykIUqT9Q1_7vItIKIz6cg 提取码: 4dv9
参考资料： 1、dbcp连接池jdbc参数配置http://commons.apache.org/proper/commons-dbcp/configuration.html 2、Spring集成MyBatishttps://www.cnblogs.com/best/p/5648740.htmlhttps://blog.csdn.net/atai2002/article/details/76058184https://faceghost.com/article/794961 3、报错时区错误https://blog.csdn.net/weixin_40916641/article/details/80546040
