Spring boot整合mybatis

Spring boot整合mybatis

制作人：全心全意

引入依赖包

<dependency>
	<groupId>org.mybatis.spring.boot</groupId>
	<artifactId>mybatis-spring-boot-starter</artifactId>
	<version>1.1.1</version>
</dependency>
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.1.21</version>
</dependency>

　　

新建application.yml文件（resources中）

spring:
  datasource:
	## spring2 多数据源读取名称存在bug（使用jdbc-url）
	url: jdbc:mysql://172.16.1.12:3306/test?useSSL=false
	username: root
	password: 123456
	driver-class-name: com.mysql.jdbc.Driver

　　

创建UserEntity对象

　　包含数据库字段名，和对应的get和set方法（类似bean对象）

 

创建UserMapper接口

package com.zq.mapper;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

public interface UserMapper {
	@Select("select * from users where name = #{name}")
	UserEntity findByName(@Param("name") String name);

	@Insert("insert into users(name,age) values(#{name},#{age})")
	int insert(@Param("name") String name, @Param("age") Integer age);
}

　　

启动类

package com.zq;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.zq.mapper") // 默认不会扫描mapper，需要此注解指定
public class App {
	public static void main(String[] args) {
		SpringApplication.run(App.class, args);
	}

}

　　

 

springboot多数据源（一个jar中存在多个不同的JDBC数据库连接）

多数据源方案：

　　分包名
　　　　com.zq.data1：对应第一个数据源
　　　　com.zq.data2：对应第二个数据源
　　　　...

　　使用注解形式（一般不使用）

　　　　自定义@DataSource注解

 

新建application.yml文件（resources中）

spring:
  datasource:
	##url:  jdbc:mysql://172.16.1.12:3306/test?useSSL=false
	## data1数据源
	data1:
	  ## spring2 多数据源读取名称存在bug（使用jdbc-url）
	  jdbc-url: jdbc:mysql://172.16.1.12:3306/test?useSSL=false
	  username: root
	  password: 123456
	  driver-class-name: com.mysql.jdbc.Driver

	## data2数据源
	data2:
	  ## spring2 多数据源读取名称存在bug（使用jdbc-url）
	  jdbc-url: jdbc:mysql://172.16.1.12:3306/ti?useSSL=false
	  username: root
	  password: 123456
	  driver-class-name: com.mysql.jdbc.Driver

　　

创建两个数据源的mapper

package com.zq.mybatis.dao.data1;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;

public interface data1Mapper {
	@Insert("insert into users(userName,age) values(#{userName},#{age})")
	int insert(@Param("userName") String userName, @Param("age") Integer age);
}

======================================================
package com.zq.mybatis.dao.data2;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;

public interface data2Mapper {
	@Insert("insert into users(userName,age1) values(#{userName},#{age})")
	int insert(@Param("userName") String userName, @Param("age") Integer age);
}

　　

创建两个数据源的Config

package com.zq.mybatis.dao.config.data1;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

@Configuration
@MapperScan(basePackages = {
		"com.zq.mybatis.dao.data1" }, sqlSessionTemplateRef = "data1SqlSessionTemplate")
public class data1DataSourceConfig {

	// 创建dataSource
	@Bean("data1DataSource")
	//ConfigurationProperties中prefix配置yml中的限定名
	@ConfigurationProperties(prefix = "spring.datasource.data1")
	public DataSource data1DataSource() throws Exception {
		return DataSourceBuilder.create().build();
	}

	// 创建SqlSessionFactory
	@Bean(name = "data1SqlSessionFactory")
	public SqlSessionFactory data1SqlSessionFactory(
			@Qualifier("data1DataSource") DataSource dataSource) throws Exception {
		SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
		sqlSessionFactoryBean.setDataSource(data1DataSource());
		return sqlSessionFactoryBean.getObject();
	}

	// 创建data1管理器
	@Bean(name = "data1TransactionManager")
	public DataSourceTransactionManager data1TransactionManager(
			@Qualifier("data1DataSource") DataSource dataSource) {
		return new DataSourceTransactionManager(dataSource);
	}

	// 创建sqlSession模板
	@Bean(name = "data1SqlSessionTemplate")
	public SqlSessionTemplate data1SqlSessionTemplate(
			@Qualifier("data1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
		return new SqlSessionTemplate(sqlSessionFactory);
	}

}

========================================================================================
package com.zq.mybatis.dao.config.data2;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

@Configuration
@MapperScan(basePackages = "com.zq.mybatis.dao.data2", sqlSessionTemplateRef = "data2SqlSessionTemplate")
public class data2DataSourceConfig {

	// 创建dataSource
	@Bean("data2DataSource")
	@ConfigurationProperties(prefix = "spring.datasource.data2")
	public DataSource data2DataSource() {
		return DataSourceBuilder.create().build();
	}

	// 创建SqlSessionFactory
	@Bean(name = "data2SqlSessionFactory")
	public SqlSessionFactory data2SqlSessionFactory(
			@Qualifier("data2DataSource") DataSource dataSource) throws Exception {
		SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
		sqlSessionFactoryBean.setDataSource(data2DataSource());
		return sqlSessionFactoryBean.getObject();
	}

	// 创建data2管理器
	@Bean(name = "data2TransactionManager")
	public DataSourceTransactionManager data2TransactionManager(
			@Qualifier("data2DataSource") DataSource dataSource) {
		return new DataSourceTransactionManager(dataSource);
	}

	// 创建sqlSession模板
	@Bean(name = "data2SqlSessionTemplate")
	public SqlSessionTemplate data2SqlSessionTemplate(
			@Qualifier("data2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
		return new SqlSessionTemplate(sqlSessionFactory);
	}

}

　　

创建MyBatisController

package com.zq.mybatis.dao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.zq.mybatis.dao.data1.data1Mapper;
import com.zq.mybatis.dao.data2.data2Mapper;

@RestController
public class MyBatisController {

	@Autowired
	private data1Mapper data1Mapper;

	@Autowired
	private data2Mapper data2Mapper;

	@RequestMapping("/addUser1")
	public String addUser1(String userName, Integer age) {
		return data1Mapper.insert(userName, age) > 0 ? "success" : "fail";
	}

	@RequestMapping("/addUser2")
	public String addUser2(String userName, Integer age) {
		return data2Mapper.insert(userName, age) > 0 ? "success" : "fail";
	}

}

　　

启动类

package com.zq.main;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;

@ComponentScan("com.zq.mybatis")
@SpringBootApplication
public class App {
	public static void main(String[] args) {
		SpringApplication.run(App.class, args);
	}
}

　　

 

SpringBoot中事务的使用

@Transactional：事务注解，一般作用的Controller或server的方法上，在方法出现报错的情况下，可以不将数据提交到数据库，默认即使报错，数据也会被提交到数据库

　　@Transactional(transactionManager = "data1TransactionManager")

　　transactionManager：多数据源时指定某个数据源的事务管理器

　　当方法指定data1数据源的事务管理器后，在方法内调用data2的方法，出现错误，data1回滚，data2不会回滚

　　通过jta+atomikos解决分布式事务管理

 

引入依赖包

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-jta-atomikos</artifactId>
</dependency>

<!-- 该插件使用@Data可以代替get/set方法,eclipse使用需安裝插件https://www.cnblogs.com/boonya/p/10691466.html，https://projectlombok.org/download -->
<dependency>
	<groupId>org.projectlombok</groupId>
	<artifactId>lombok</artifactId>
	<version>1.16.10</version>
</dependency> 

　　

修改application.yml文件（resources中）

spring:
  datasource:
    ##url:  jdbc:mysql://192.168.5.17:3306/test?useSSL=false
    ## data1数据源
    data1:
      ## spring2 多数据源读取名称存在bug（使用jdbc-url）
      url: jdbc:mysql://192.168.5.17:3306/ceshi1?useSSL=false
      username: root
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver
      borrowConnectionTimeout: 30
      loginTimeout: 30
      maintenanceInterval: 60
      maxIdleTime: 60
      maxLifetime: 20000
      maxPoolSize: 25
      minPoolSize: 3
      uniqueResourceNmae: data1Datasource
 
    ## data2数据源
    data2:
      ## spring2 多数据源读取名称存在bug（使用jdbc-url）
      url: jdbc:mysql://192.168.5.17:3306/ceshi2?useSSL=false
      username: root
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver
      borrowConnectionTimeout: 30
      loginTimeout: 30
      maintenanceInterval: 60
      maxIdleTime: 60
      maxLifetime: 20000
      maxPoolSize: 25
      minPoolSize: 3
      uniqueResourceNmae: data2Datasource

　　

mapper同上

 

创建Config配置

package com.zq.mybatis.dao.config.data1;

import org.springframework.boot.context.properties.ConfigurationProperties;

import lombok.Data;

@ConfigurationProperties(prefix = "spring.datasource.data1")
@Data //等同于@Getter+Setter
public class Data1Config {
	//@Getter	//
	//@Setter
	private String url;
	private String userName;
	private String passWord;
	private int minPoolSize;
	private int maxPoolSize;
	private int borrowConnectionTimeout;
	private int loginTimeout;
	private int maintenanceInterval;
	private int maxIdleTime;
	private String testQuery;
	private String uniqeResourceName;

}
=============================================================
package com.zq.mybatis.dao.config.data2;

import org.springframework.boot.context.properties.ConfigurationProperties;

import lombok.Data;

@ConfigurationProperties(prefix = "spring.datasource.data2")
@Data
public class Data2Config {
	private String url;
	private String userName;
	private String passWord;
	private int minPoolSize;
	private int maxPoolSize;
	private int borrowConnectionTimeout;
	private int loginTimeout;
	private int maintenanceInterval;
	private int maxIdleTime;
	private String testQuery;
	private String uniqeResourceName;

}

　　

修改DataSourceConfig

package com.zq.mybatis.dao.config.data1;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.jta.atomikos.AtomikosDataSourceBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import com.mysql.jdbc.jdbc2.optional.MysqlXADataSource;

@Configuration
@MapperScan(basePackages = {
		"com.zq.mybatis.dao.data1" }, sqlSessionTemplateRef = "data1SqlSessionTemplate")
public class data1DataSourceConfig {

	// 创建dataSource
	// 将数据源统一的交给全局xa事务去管理
	@Bean("data1DataSource")
	public DataSource data1DataSource(Data1Config data1Config) throws Exception {
		// 1.創建xa的DataSource
		MysqlXADataSource mysqlXaDataSource = new MysqlXADataSource();
		mysqlXaDataSource.setUrl(data1Config.getUrl());
		mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);
		mysqlXaDataSource.setPassword(data1Config.getPassWord());
		mysqlXaDataSource.setUser(data1Config.getUserName());
		mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);

		// 2.注冊到全局事务
		AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
		xaDataSource.setXaDataSource(mysqlXaDataSource);
		xaDataSource.setUniqueResourceName(data1Config.getUniqeResourceName());
		xaDataSource.setMinPoolSize(data1Config.getMinPoolSize());
		xaDataSource.setMaxPoolSize(data1Config.getMaxPoolSize());
		xaDataSource.setMaxLifetime(data1Config.getMaxIdleTime());
		xaDataSource.setBorrowConnectionTimeout(data1Config.getBorrowConnectionTimeout());
		xaDataSource.setLoginTimeout(data1Config.getLoginTimeout());
		xaDataSource.setMaintenanceInterval(data1Config.getMaintenanceInterval());
		xaDataSource.setMaxIdleTime(data1Config.getMaxIdleTime());
		xaDataSource.setTestQuery(data1Config.getTestQuery());

		return xaDataSource;
	}

	// 创建SqlSessionFactory
	@Bean(name = "data1SqlSessionFactory")
	public SqlSessionFactory data1SqlSessionFactory(
			@Qualifier("data1DataSource") DataSource dataSource) throws Exception {
		SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
		sqlSessionFactoryBean.setDataSource(dataSource);
		return sqlSessionFactoryBean.getObject();
	}

	// 删除创建data1管理器

	// 创建sqlSession模板
	@Bean(name = "data1SqlSessionTemplate")
	public SqlSessionTemplate data1SqlSessionTemplate(
			@Qualifier("data1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
		return new SqlSessionTemplate(sqlSessionFactory);
	}

}
==================================================================
package com.zq.mybatis.dao.config.data2;


import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.jta.atomikos.AtomikosDataSourceBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import com.mysql.jdbc.jdbc2.optional.MysqlXADataSource;

@Configuration
@MapperScan(basePackages = "com.zq.mybatis.dao.data2", sqlSessionTemplateRef = "data2SqlSessionTemplate")
public class data2DataSourceConfig {

	// 创建dataSource
	@Bean("data2DataSource")
	public DataSource data2DataSource(Data2Config data2Config) throws Exception {
		// 1.創建xa的DataSource
		MysqlXADataSource mysqlXaDataSource = new MysqlXADataSource();
		mysqlXaDataSource.setUrl(data2Config.getUrl());
		mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);
		mysqlXaDataSource.setPassword(data2Config.getPassWord());
		mysqlXaDataSource.setUser(data2Config.getUserName());
		mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);

		// 2.注冊到全局事务
		AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
		xaDataSource.setXaDataSource(mysqlXaDataSource);
		xaDataSource.setUniqueResourceName(data2Config.getUniqeResourceName());
		xaDataSource.setMinPoolSize(data2Config.getMinPoolSize());
		xaDataSource.setMaxPoolSize(data2Config.getMaxPoolSize());
		xaDataSource.setMaxLifetime(data2Config.getMaxIdleTime());
		xaDataSource.setBorrowConnectionTimeout(data2Config.getBorrowConnectionTimeout());
		xaDataSource.setLoginTimeout(data2Config.getLoginTimeout());
		xaDataSource.setMaintenanceInterval(data2Config.getMaintenanceInterval());
		xaDataSource.setMaxIdleTime(data2Config.getMaxIdleTime());
		xaDataSource.setTestQuery(data2Config.getTestQuery());

		return xaDataSource;
	}

	// 创建SqlSessionFactory
	@Bean(name = "data2SqlSessionFactory")
	public SqlSessionFactory data2SqlSessionFactory(
			@Qualifier("data2DataSource") DataSource dataSource) throws Exception {
		SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
		sqlSessionFactoryBean.setDataSource(dataSource);
		return sqlSessionFactoryBean.getObject();
	}

	// 删除data2的管理器

	// 创建sqlSession模板
	@Bean(name = "data2SqlSessionTemplate")
	public SqlSessionTemplate data2SqlSessionTemplate(
			@Qualifier("data2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
		return new SqlSessionTemplate(sqlSessionFactory);
	}

}

　　

创建Controller

package com.zq.mybatis.dao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.zq.mybatis.dao.data1.data1Mapper;
import com.zq.mybatis.dao.data2.data2Mapper;

@RestController
public class MyBatisController {

	@Autowired
	private data1Mapper data1Mapper;

	@Autowired
	private data2Mapper data2Mapper;

	@RequestMapping("/addUser1")
	@Transactional() //删除transactionManager，使用全局事务管理器
	public String addUser1(String userName, Integer age) {

		data2Mapper.insert(userName, age);
		int j = data1Mapper.insert(userName, age);
		//int i = 1 / 0;
		return j > 0 ? "success" : "fail";
	}

	@RequestMapping("/addUser2")
	public String addUser2(String userName, Integer age) {
		return data2Mapper.insert(userName, age) > 0 ? "success" : "fail";
	}

}

　　

启动类

package com.zq.main;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.scheduling.annotation.EnableScheduling;

import com.zq.mybatis.dao.config.data1.Data1Config;
import com.zq.mybatis.dao.config.data2.Data2Config;


@SpringBootApplication
@EnableConfigurationProperties( { Data1Config.class, Data2Config.class })
public class App {
	public static void main(String[] args) {
		SpringApplication.run(App.class, args);
	}
}