spring boot 数据访问2 整合mybatis，pagehelper分页，多数据源事务处理

「爱情、让人受尽委屈。」 2022-02-16 13:15 241阅读 0赞

** 1、导入包**

<dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</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>2.0.1</version>
            </dependency>
    
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.1.8</version>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>runtime</scope>
            </dependency>

**2、yaml配置文件**

spring:
       datasource:
          username: root
          password: 123456
          url: jdbc:mysql://192.168.1.16:3306/mybatis
          driver-class-name: com.mysql.jdbc.Driver
          type : com.alibaba.druid.pool.DruidDataSource
    
          platform: mysql
          # 下面为连接池的补充设置，应用到上面所有数据源中
          # 初始化大小，最小，最大
          initialSize: 1
          minIdle: 3
          maxActive: 20
          # 配置获取连接等待超时的时间
          maxWait: 60000
          # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
          timeBetweenEvictionRunsMillis: 60000
          # 配置一个连接在池中最小生存的时间，单位是毫秒
          minEvictableIdleTimeMillis: 30000
          validationQuery: select 'x'
          testWhileIdle: true
          testOnBorrow: false
          testOnReturn: false
          # 打开PSCache，并且指定每个连接上PSCache的大小
          poolPreparedStatements: true
          maxPoolPreparedStatementPerConnectionSize: 20
          # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
          filters: stat,wall,slf4j
          # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
          connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    
    # 设置驼峰命名转换（user_name 和userName相互转换）
    mybatis:
       configuration:
          map-underscore-to-camel-case: true

**3、druid配置**

@Configuration
    public class DruidConfig {
    
        @ConfigurationProperties(prefix = "spring.datasource")
        @Bean
        public DataSource druid(){
            return new DruidDataSource();
        }
    
        //配置Druid的监控
        //1、配置一个管理后台的Servlet
        @Bean
        public ServletRegistrationBean statViewServlet(){
            ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(),
                    "/druid/*");
            Map<String,String> initParams = new HashMap<>();
            initParams.put("loginUsername","admin");
            initParams.put("loginPassword","123456");
            initParams.put("allow","");//默认就是允许所有访问
            initParams.put("deny","192.168.15.21");
            bean.setInitParameters(initParams);
            return bean;
        }
        //2、配置一个web监控的filter
        @Bean
        public FilterRegistrationBean webStatFilter(){
            FilterRegistrationBean bean = new FilterRegistrationBean();
            bean.setFilter(new WebStatFilter());
            Map<String,String> initParams = new HashMap<>();
            initParams.put("exclusions","*.js,*.css,/druid/*");
            bean.setInitParameters(initParams);
            bean.setUrlPatterns(Arrays.asList("/*"));
            return bean;
        }
    
    }

**4、指定mapper(这里可以不加@Mapper注解，但得在启动类上加@MapperScan(value = "com.stude.mybatis.mapper")指定mapper位置)**

//指定这是一个操作数据库的mapper
    @Mapper
    public interface DepartmentMapper {
    
        @Select("select * from department where id=#{id}")
        public Department getDeptById(Integer id);
    
        @Delete("delete from department where id=#{id}")
        public int deleteDeptById(Integer id);
    
        @Options(useGeneratedKeys = true,keyProperty = "id")//主键递增
        @Insert("insert into department(department_name) values(#{departmentName})")
        public int insertDept(Department department);
    
        @Update("update department set department_name=#{departmentName} where id=#{id}")
        public int updateDept(Department department);
    }

** 5、web调用**

@RestController
    public class DeptController {
    
        @Autowired
        DepartmentMapper departmentMapper;
    
        @GetMapping("/dept/{id}")
        public Department getDepartment(@PathVariable("id") int id){
            return departmentMapper.getDeptById(id);
        }
    
        @GetMapping("/dept")
        public Department insertDept(Department department){
            departmentMapper.insertDept(department);
            return department;
        }
    }

**集成pagehelper分页插件**

PageHelper 是一款好用的开源免费的 Mybatis 第三方物理分页插件  
物理分页  
支持常见的 12 种数据库。Oracle,MySql,MariaDB,SQLite,DB2,PostgreSQL,SqlServer 等  
支持多种分页方式  
支持常见的 RowBounds(PageRowBounds)，PageHelper.startPage 方法调用，Mapper 接口参数调用

<dependency>
                <groupId>com.github.pagehelper</groupId>
                <artifactId>pagehelper-spring-boot-starter</artifactId>
                <version>1.2.10</version>
            </dependency>

yaml配置

reasonable 为true时 `pageNum<=0` 时会查询第一页，`pageNum>pages`（超过总数时），会查询最后一页。

pagehelper:
       helperDialect: mysql
       reasonable: true
       supportMethodsArguments: true
       params: count=countSql
       page-size-zero: true

mapper添加查询

@Select("SELECT * FROM department ")
        List<Department> findUserList();

添加分页方法

@Service
    public class DepartmentService {
    
        @Autowired
        private DepartmentMapper departmentMapper;
    
        public PageInfo<Department> findUserList(int page, int size) {
            // 开启分页插件,放在查询语句上面
            PageHelper.startPage(page, size);
            List<Department> listUser = departmentMapper.findUserList();
            // 封装分页之后的数据
            PageInfo<Department> pageInfoUser = new PageInfo<Department>(listUser);
            return pageInfoUser;
        }
    
    }

controller层调用

@GetMapping("/find")
        public PageInfo<Department> findDept(int page, int size){
            return departmentService.findUserList(page,size);
        }

参数说明：

public class PageInfo<T> implements Serializable {  
        private static final long serialVersionUID = 1L;  
        //当前页  
        private int pageNum;  
        //每页的数量  
        private int pageSize;  
        //当前页的数量  
        private int size;  
      
        //由于startRow和endRow不常用，这里说个具体的用法  
        //可以在页面中"显示startRow到endRow 共size条数据"  
      
        //当前页面第一个元素在数据库中的行号  
        private int startRow;  
        //当前页面最后一个元素在数据库中的行号  
        private int endRow;  
        //总记录数  
        private long total;  
        //总页数  
        private int pages;  
        //结果集  
        private List<T> list;  
      
        //前一页  
        private int prePage;  
        //下一页  
        private int nextPage;  
      
        //是否为第一页  
        private boolean isFirstPage = false;  
        //是否为最后一页  
        private boolean isLastPage = false;  
        //是否有前一页  
        private boolean hasPreviousPage = false;  
        //是否有下一页  
        private boolean hasNextPage = false;  
        //导航页码数  
        private int navigatePages;  
        //所有导航页号  
        private int[] navigatepageNums;  
        //导航条上的第一页  
        private int navigateFirstPage;  
        //导航条上的最后一页  
        private int navigateLastPage;

## **多数据源事务处理** ##

**配置文件**

###datasource1
    spring.datasource.test1.driver-class-name = com.mysql.jdbc.Driver
    spring.datasource.test1.jdbc-url = jdbc:mysql://192.168.1.16:3306/mybatis?useUnicode=true&characterEncoding=utf-8
    spring.datasource.test1.username = root
    spring.datasource.test1.password = 123456
    ###datasource2
    spring.datasource.test2.driver-class-name = com.mysql.jdbc.Driver
    spring.datasource.test2.jdbc-url = jdbc:mysql://192.168.1.16:3306/jpa?useUnicode=true&characterEncoding=utf-8
    spring.datasource.test2.username = root
    spring.datasource.test2.password = 123456

mapper01

//指定这是一个操作数据库的mapper
    @Mapper
    public interface DepartmentMapper01 {
    
        @Select("select * from department where id=#{id}")
        public Department getDeptById(Integer id);
    
        @Select("SELECT * FROM department ")
        List<Department> findUserList();
    
        @Delete("delete from department where id=#{id}")
        public int deleteDeptById(Integer id);
    
        @Options(useGeneratedKeys = true,keyProperty = "id")//主键递增
        @Insert("insert into department(department_name) values(#{departmentName})")
        public int insertDept(Department department);
    
        @Update("update department set department_name=#{departmentName} where id=#{id}")
        public int updateDept(Department department);
    }

mapper02

@Mapper
    public interface DepartmentMapper02 {
    
        @Select("select * from department where id=#{id}")
        public Department getDeptById(Integer id);
    
        @Select("SELECT * FROM department ")
        List<Department> findUserList();
    
        @Delete("delete from department where id=#{id}")
        public int deleteDeptById(Integer id);
    
        @Options(useGeneratedKeys = true,keyProperty = "id")//主键递增
        @Insert("insert into department(department_name) values(#{departmentName})")
        public int insertDept(Department department);
    
        @Update("update department set department_name=#{departmentName} where id=#{id}")
        public int updateDept(Department department);
    }

**多数据源配置**

指定配置文件

*  @PropertySource(value = \{"classpath:multiDataSource.properties"\})
 *  @ConfigurationProperties(prefix = "spring.datasource.test1")

使用当前数据源的mapper地方com.stude.mybatis.mapper.test01

@Configuration // 注册到springboot容器中
    @PropertySource(value = {"classpath:multiDataSource.properties"})
    @MapperScan(basePackages = "com.stude.mybatis.mapper.test01", sqlSessionFactoryRef = "test1SqlSessionFactory")
    public class DataSource1Config {
    
        @Bean(name = "test1DataSource")
        @ConfigurationProperties(prefix = "spring.datasource.test1")
        public DataSource testDataSource() {
            return DataSourceBuilder.create().build();
        }
    
        @Bean(name = "test1SqlSessionFactory")
        public SqlSessionFactory testSqlSessionFactory(@Qualifier("test1DataSource") DataSource dataSource)
                throws Exception {
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            // bean.setMapperLocations(
            // new
            // PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/test1/*.xml"));
            return bean.getObject();
        }
    
        @Bean(name = "test1TransactionManager")
        public DataSourceTransactionManager testTransactionManager(@Qualifier("test1DataSource") DataSource dataSource) {
            return new DataSourceTransactionManager(dataSource);
        }
    
        @Bean(name = "test1SqlSessionTemplate")
        public SqlSessionTemplate testSqlSessionTemplate(
                @Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    }

@Configuration // 注册到springboot容器中
    @PropertySource(value = {"classpath:multiDataSource.properties"})
    @MapperScan(basePackages = "com.stude.mybatis.mapper.test02", sqlSessionFactoryRef = "test2SqlSessionFactory")
    public class DataSource2Config {
    
        @Bean(name = "test2DataSource")
        @ConfigurationProperties(prefix = "spring.datasource.test2")
        public DataSource testDataSource() {
            return DataSourceBuilder.create().build();
        }
    
        @Bean(name = "test2SqlSessionFactory")
        public SqlSessionFactory testSqlSessionFactory(@Qualifier("test2DataSource") DataSource dataSource)
                throws Exception {
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            // bean.setMapperLocations(
            // new
            // PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/test2/*.xml"));
            return bean.getObject();
        }
    
        @Bean(name = "test2TransactionManager")
        public DataSourceTransactionManager testTransactionManager(@Qualifier("test2DataSource") DataSource dataSource) {
            return new DataSourceTransactionManager(dataSource);
        }
    
        @Bean(name = "test2SqlSessionTemplate")
        public SqlSessionTemplate testSqlSessionTemplate(
                @Qualifier("test2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    }

** 每种数据源对应自己的方法，对应自己的事务（此时多数据源必须使用自己的事务）**

@Autowired
        DepartmentMapper02 departmentMapper02;
    
        @Autowired
        DepartmentMapper01 departmentMapper01;
    
        @GetMapping("/dept1")
        @Transactional(transactionManager = "test1TransactionManager")
        public Department insertDept1(Department department){
            departmentMapper01.insertDept(department);
            int m = 1/0;
            return department;
        }
    
        @GetMapping("/dept2")
        @Transactional(transactionManager = "test2TransactionManager")
        public Department insertDept2(Department department){
            departmentMapper02.insertDept(department);
            int m = 1/0;
            return department;
        }

## **springboot+jta+atomikos 分布式事物管理（传统项目）** ##

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

上面配置文件2种数据源都添加下面配置

mysql.datasource.test1.maxPoolSize = 25
    mysql.datasource.test1.maxLifetime = 20000
    mysql.datasource.test1.borrowConnectionTimeout = 30
    mysql.datasource.test1.loginTimeout = 30
    mysql.datasource.test1.maintenanceInterval = 60
    mysql.datasource.test1.maxIdleTime = 60

@Data
    @Configuration
    @ConfigurationProperties(prefix = "mysql.datasource.test1")
    public class DBConfig1 {
    
    	private String url;
    	private String username;
    	private String password;
    	private int minPoolSize;
    	private int maxPoolSize;
    	private int maxLifetime;
    	private int borrowConnectionTimeout;
    	private int loginTimeout;
    	private int maintenanceInterval;
    	private int maxIdleTime;
    	private String testQuery;
    }
    
    @Data
    @Configuration
    @ConfigurationProperties(prefix = "mysql.datasource.test2")
    public class DBConfig2 {
    
    	private String url;
    	private String username;
    	private String password;
    	private int minPoolSize;
    	private int maxPoolSize;
    	private int maxLifetime;
    	private int borrowConnectionTimeout;
    	private int loginTimeout;
    	private int maintenanceInterval;
    	private int maxIdleTime;
    	private String testQuery;
    }

这里是添加数据源，并把数据源注册到全局事务中（第二个数据源一样配置，名字改一下即可。）

这样配置后会有三个事务管理类，1全局的（数据源1+数据源2），数据源1的，数据源2的

@Configuration
    @MapperScan(basePackages = "com.itmayiedu.test01", sqlSessionTemplateRef = "testSqlSessionTemplate")
    public class MyBatisConfig1 {
    
    	// 配置数据源
    
    	@Bean(name = "testDataSource")
    	public DataSource testDataSource(DBConfig1 testConfig) throws SQLException {
    		MysqlXADataSource mysqlXaDataSource = new MysqlXADataSource();
    		mysqlXaDataSource.setUrl(testConfig.getUrl());
    		mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);
    		mysqlXaDataSource.setPassword(testConfig.getPassword());
    		mysqlXaDataSource.setUser(testConfig.getUsername());
    		mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);
    
    		// 将本地事务注册到创 Atomikos全局事务
    		AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
    		xaDataSource.setXaDataSource(mysqlXaDataSource);
    		xaDataSource.setUniqueResourceName("testDataSource");
    
    		xaDataSource.setMinPoolSize(testConfig.getMinPoolSize());
    		xaDataSource.setMaxPoolSize(testConfig.getMaxPoolSize());
    		xaDataSource.setMaxLifetime(testConfig.getMaxLifetime());
    		xaDataSource.setBorrowConnectionTimeout(testConfig.getBorrowConnectionTimeout());
    		xaDataSource.setLoginTimeout(testConfig.getLoginTimeout());
    		xaDataSource.setMaintenanceInterval(testConfig.getMaintenanceInterval());
    		xaDataSource.setMaxIdleTime(testConfig.getMaxIdleTime());
    		xaDataSource.setTestQuery(testConfig.getTestQuery());
    		return xaDataSource;
    	}
    
    	@Bean(name = "testSqlSessionFactory")
    	public SqlSessionFactory testSqlSessionFactory(@Qualifier("testDataSource") DataSource dataSource)
    			throws Exception {
    		SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
    		bean.setDataSource(dataSource);
    		return bean.getObject();
    	}
    
    	@Bean(name = "testSqlSessionTemplate")
    	public SqlSessionTemplate testSqlSessionTemplate(
    			@Qualifier("testSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
    		return new SqlSessionTemplate(sqlSessionFactory);
    	}
    }

这里默认就是全局的事务管理

@Transactional()
    	public int insertUserTest01AndTest02(String name, Integer age) {
    		// 传统分布式事务解决方案 jta+atomikos 注册同一个全局事务中
    		// 第一个数据源
    		int insertUserResult01 = userMapperTest01.insert(name, age);
    		// 第二个数据源
    		int insertUserResult02 = userMapperTest02.insert(name, age);
    		int i = 1 / 0;
    		int result = insertUserResult01 + insertUserResult02;
    		// test01入库 test02回滚
    		return result;
    	}