springboot中使用 mybatis 和 Druid 连接池对数据库进行操作

最新推荐文章于 2024-09-12 19:28:16 发布

shenzhenNBA

最新推荐文章于 2024-09-12 19:28:16 发布

阅读量2.3k

点赞数

分类专栏：管理者/开发者 Java 数据库文章标签： druid mybatis 数据库 springboot

本文链接：https://blog.csdn.net/shenzhenNBA/article/details/80643492

版权

管理者/开发者同时被 3 个专栏收录

209 篇文章 1 订阅

订阅专栏

Java

131 篇文章 2 订阅

订阅专栏

数据库

15 篇文章 0 订阅

订阅专栏

WEB工程中使用 mybatis 和 Druid 连接池非常多，这里简单介绍一下在springboot 2.0中的使用例子，使用的MS SQL Server 2008数据库，工程项目的准备工作；工程样本建立；工程导入Eclipse开发环境，下载相关依赖JAR包步骤参考前一篇博文，这里省略，仅仅展示关键之处，整个工程结构如下图，

一，工程 pom.xml 修改，引入druid数据库链接池和相应数据库对应的依赖jar包，内容如下，

<?xml version="1.0" encoding="UTF-8"?>
<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>

	<groupId>com.qyh</groupId>
	<artifactId>springBoot01Database</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>springBoot01Database</name>
	<description>Demo project for Spring Boot</description>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.2.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-rest</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-thymeleaf</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.3.2</version>
		</dependency>
		
		<!-- ms sql server JDBC 依赖jar包 -->
		<dependency>
			<groupId>com.microsoft.sqlserver</groupId>
			<artifactId>mssql-jdbc</artifactId>
			<scope>runtime</scope>
		</dependency>
		 
		 
		<!-- mysql db 依赖jar包 -->
		<!-- 
		<dependency>  
		    <groupId>mysql</groupId>  
		    <artifactId>mysql-connector-java</artifactId>  
		    <scope>runtime</scope>  
		</dependency> 
		 -->
		
		<!-- oracle db 依赖jar包,好像难从maven仓库下载,需手动下载放到本地仓库 --> 
		<!--  
		<dependency>
		    <groupId>com.oracle</groupId>
		    <artifactId>ojdbc6</artifactId>
		    <version>11.2.0.2.0</version>
		</dependency>
		 -->
		  
		<!-- start:使用druid数据源依赖 -->
        <dependency>  
            <groupId>com.alibaba</groupId>  
            <artifactId>druid</artifactId>  
            <version>1.1.2</version>  
        </dependency>
		<!-- end:使用druid数据源依赖 -->
		 
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		
		<!-- 支持热部署依赖 -->
		<dependency>
		    <groupId>org.springframework.boot</groupId>
		    <artifactId>spring-boot-devtools</artifactId>
		    <optional>true</optional>
		    <scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<defaultGoal>compile</defaultGoal>
		<finalName>springBoot01Database</finalName>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
				<configuration>
	                <!-- 没有该配置，devtools 不生效 -->
	                <fork>true</fork>
	            </configuration>
			</plugin>
		</plugins>
	</build>

</project>

二，配置文件 application.properties 文件修改，
为方便在开发，测试，生成三种环境之间切换，配置文件分为三个，即： application-dev.properties，application-test.properties，application-prod.properties，然后 application.properties 仅仅配置一行使用其中一种环境即可， springboot启动读取的是 application.properties 配置文件，需要注意的是， mybatis 指定的实体包，本地是配置XML文件和数据库表到实体的映射XML文件需要在配置文件中指定，如下为各个配置文件，

#===================================================
# application-dev.properties
#===================================================
# server evn setting
#===================================================
server.address=127.0.0.1
server.port=8080
server.servlet.contextPath=/
server.session-timeout=30

#=====For springMVC setting=====  
spring.thymeleaf.prefix=classpath:/templates/
#spring.thymeleaf.suffix=.html

spring.mvc.view.prefix=/WEB-INF/view/
spring.mvc.view.suffix=.jsp


#===================================================
# database setting
#===================================================
#=====For connect ms sql server use jdbc pool=====
#use dependency: jdbc[default] and mybatis-spring-boot-starter-1.1.1.jar
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.url=jdbc:sqlserver://localhost:1433;databaseName=myDBName
spring.datasource.username=dbUserName
spring.datasource.password=dbPassword


#=====For connect mysql use jdbc pool=====
#spring.datasource.driverClassName=com.mysql.jdbc.Driver  
#spring.datasource.url=jdbc:mysql://127.0.0.1:3306/myDBName?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8  
#spring.datasource.username=DBUserName  
#spring.datasource.password=DBPassword  


#=====For connect oracle db use jdbc pool=====
#spring.datasource.driverClassName=oracle.jdbc.OracleDriver
#spring.datasource.url=jdbc:oracle:thin:@localhost:1521:oracleSID
#spring.datasource.username=DBUserName 
#spring.datasource.password=DBPassword


#=====For common dataSource setting info=====
spring.datasource.initialSize=10
spring.datasource.minIdle=10
spring.datasource.maxActive=120
spring.datasource.maxWait=60000
spring.datasource.timeBetweenEvictionRunsMillis=60000
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=SELECT 1 
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.filters=stat,wall
#spring.datasource.filters=stat,wall,log4j
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000


#=====For mybatis setting======
mybatis.typeAliasesPackage=com.qyh.pro01.model,com.qyh.pro01.param
mybatis.configLocations=classpath:config/mybatisConfig.xml
#main thing is setting alias, can not use configLocations
mybatis.mapperLocations=classpath:mapper/*.xml


#===================================================
# other setting
#===================================================

#===================================================
#application.properties
#===================================================
spring.profiles.active=dev
#active dev evn

三，mybatis的本地配置文件，
mybatis的本地配置文件主要是定义实体的别名，同时还有数据库连接的一些基本信息（可以不设置），整个文件可以不用，为完整这里使用，代码如下，

<?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>
	<!-- settings 部分可以不用 -->
	<!--
	<settings>
	    <setting name="cacheEnabled" value="true" />  
	    <setting name="lazyLoadingEnabled" value="true" />  
        <setting name="aggressiveLazyLoading" value="true"/>
        <setting name="multipleResultSetsEnabled" value="true"/> 
	    <setting name="useColumnLabel" value="true" />  
	    <setting name="defaultExecutorType" value="SIMPLE" />     
        <setting name="defaultStatementTimeout" value="60000"/>        
    </settings>
	-->
	<!-- can more setting add here -->
	
    
    <!-- POJO的别名设置 -->
	<typeAliases>
		<typeAlias alias="member" type="com.qyh.pro01.model.Member" />
	</typeAliases>
	
	<!-- 当单独使用mybatis时设置,此处为链接 MS SQL SERVER 2008 -->
	<!-- 
	<environments default="development">
        <environment id="development">
        	<transactionManager type="JDBC"/>
            <dataSource type="POOLED">
            	<property name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
            	<property name="url" value="jdbc:sqlserver://localhost:1433;databaseName=myDBName" />
            	<property name="username" value="dbUserName"/>
            	<property name="password" value="dbPassword"/>
            </dataSource>
        </environment>
    </environments>
    -->
    
   <!-- 当单独使用mybatis时设置映射XML文件 --> 
   <!-- 
    <mappers>
        <mapper resource="mapper/Member.xml"/>
   </mappers>
   -->

</configuration>

此外还有库表到JAVA实体对应的各个映射mapper的XML文件，此处省略。

四，编写自定义数据源类，目的是产生DataSource和SqlSessionFactory并注入spring容器中，类正确使用相应注解，并确保其所在的包在启动类中扫描到，代码如下，

package com.qyh.pro01.common;

import java.sql.SQLException;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import com.alibaba.druid.pool.DruidDataSource;

/**
 * @author shenzhenNBA
 * @since 2018.06.10
 * 自定义数据源类
 */

@Configuration    //该注解类似于spring配置文件  
public class MyDataSourceConfig {
	private Logger logger = LoggerFactory.getLogger(MyDataSourceConfig.class);  
	
	@Autowired  
    private Environment env; 
	//使用该实例读取项目资源路径下application.properties文件中的内容
	
	@Value("${spring.datasource.type}") //读取配置文件中指定KEY的值到变量中
    private String dbType;    
	
	@Value("${spring.datasource.url}")    
    private String dbUrl;    
        
    @Value("${spring.datasource.username}")    
    private String username;    
        
    @Value("${spring.datasource.password}")    
    private String password;    
        
    @Value("${spring.datasource.driverClassName}")    
    private String driverClassName;    
        
    @Value("${spring.datasource.initialSize}")    
    private int initialSize;    
        
    @Value("${spring.datasource.minIdle}")    
    private int minIdle;    
        
    @Value("${spring.datasource.maxActive}")    
    private int maxActive;    
        
    @Value("${spring.datasource.maxWait}")    
    private int maxWait;    
        
    @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")    
    private int timeBetweenEvictionRunsMillis;    
        
    @Value("${spring.datasource.minEvictableIdleTimeMillis}")    
    private int minEvictableIdleTimeMillis;    
        
    @Value("${spring.datasource.validationQuery}")    
    private String validationQuery;    
        
    @Value("${spring.datasource.testWhileIdle}")    
    private boolean testWhileIdle;    
        
    @Value("${spring.datasource.testOnBorrow}")    
    private boolean testOnBorrow;    
        
    @Value("${spring.datasource.testOnReturn}")    
    private boolean testOnReturn;    
        
    @Value("${spring.datasource.poolPreparedStatements}")    
    private boolean poolPreparedStatements;    
        
    @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")    
    private int maxPoolPreparedStatementPerConnectionSize;    
        
    @Value("${spring.datasource.filters}")    
    private String filters;    
        
    @Value("{spring.datasource.connectionProperties}")    
    private String connectionProperties;  

    @Bean  		//产生bean实例加载进srping容器中,与spring配置文件中配置bean一样
    @Primary  	//当有多个实现时以此为优先为准
    public DataSource getDataSource() throws Exception{  
    	DruidDataSource datasource = new DruidDataSource(); 
    	
    	//datasource.setDbType(dbType);   //有些版本不支持该属性
    	datasource.setUrl(dbUrl);
    	//datasource.setUrl(env.getProperty("spring.datasource.url"));
    	//使用Environment对象实例读取property文件中指定key的值,配置文件读取方式之一
    	
    	datasource.setDriverClassName(driverClassName);
        datasource.setUsername(username);
        datasource.setPassword(password);
        
        //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);    
        } catch (SQLException e) {    
            logger.error("druid configuration initialization filter", e);    
        }
        datasource.setConnectionProperties(connectionProperties);    
    	return datasource;
    } 
    
    
	@Bean 	//产生bean实例加载进srping容器中,与spring配置文件中配置bean一样
    public SqlSessionFactory sqlSessionFactory(DataSource ds) throws Exception{  
        SqlSessionFactoryBean sqlSFB = new SqlSessionFactoryBean();  
        sqlSFB.setDataSource(ds);	
        //指定自定义的数据源,这个必须用,否则报错  
        
        sqlSFB.setTypeAliasesPackage(env.getProperty("mybatis.typeAliasesPackage")); 
        //指定对应的实体包,多个包之间逗号隔开 
        
        Resource configLocationResource = new PathMatchingResourcePatternResolver().getResource(
        		env.getProperty("mybatis.configLocations"));
        sqlSFB.setConfigLocation(configLocationResource);
        //指定mybatis的本地配置文件资源,目的是定义实体等别名,可以不用,如果不用对应配置文件应注释掉
        
        Resource[] mapperLocations = new PathMatchingResourcePatternResolver().getResources(
        		env.getProperty("mybatis.mapperLocations"));
        sqlSFB.setMapperLocations(mapperLocations);
        //指定mybatis的库表到实体的映射xml文件的mapper资源  
        
        return sqlSFB.getObject();  
    }
    
}

五，启动类中相关功能注解使用
因为要使用 Druid 和 mybatis 对数据库进行操作，所以必须在启动类中使用相应的注解并扫描各个相应的包，然后目标包中的各个含有各种注解的类对应的实例才能加载进spring容器中，才能使用；比如使用 @EntityScan扫描实体对应所在的包， @MapperScan扫描DAO层定义的接口所在的包， @ComponentScan扫描controller，service等所在的包，如下为启动类代码，

package com.qyh.springBootApp;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.context.annotation.ComponentScan;

/**
 * springboot+mybatis+druid+mssqlserver例子
 * @author shenzhenNBA
 * @since 2018.06.10
 */

@SpringBootApplication  //标注为启动类
@EntityScan("com.qyh.pro01.model,com.qyh.pro01.param")  //扫描实体所在的包
@MapperScan("com.qyh.pro01.dao")          //扫描DAO接口所在的包
@ComponentScan("com.qyh.pro01.common"     //扫描自定义数据源所在的包
		+ ",com.qyh.pro01.controller"     //扫描控制器所在的包
		+ ",com.qyh.pro01.service.impl")  //扫描业务接口实现类所在的包
public class SpringBoot01DBApplication {

	public static void main(String[] args) {
		SpringApplication.run(SpringBoot01DBApplication.class, args);
	}
	
}

当项目启动后，对数据库进行操作时即可看到 Druid 已经进行了初始化，如下图所示，

以上设置完即可在项目中使用 Druid 数据源连接池了，总的来说要使用 Druid ，首先需要引入 Druid 依赖JAR包和对应各种数据库的依赖JAR包，其次在properties配置文件中设置对应的数据源信息，然后编写mybatis对应本地配置XML文件（主要定义实体别名，这个不是必须），再然后编写自定义的数据源类，最后在启动类中扫描到自定义数据源的类，这样即可使用 druid 数据源了，此为俺做的例子，欢迎拍砖讨论....