SpringBoot实战教程-数据访问

【仅需一次订阅,作者所有专栏都能看】

文章目录

导读4.1、SpringBoot整合使用JdbcTemplate4.1.1 引入pom依赖4.1.2 配置application.properties4.1.3 UserService类4.1.4 单元测试 4.2、SpringBoot整合使用mybatis4.2.1、pom文件配置4.2.2、配置application.properties4.2.3、编写Mapper接口4.2.4、Service代码4.2.5、引导类4.2.6、单元测试4.2.7、SpringBoot整合Mybatis分页插件pageHelper简介引入Maven依赖配置application.propertiesMapper层Service层Controller层运行测试 4.2.8、SpringBoot整合Mybatis Generator1.新建Maven项目2.编写配置文件(resource目录下)3.运行generator插件4.查看生成的文件5.使用Example6. MyBatis Generator生成注解版Mapper7.一些小技巧 4.3、SpringBoot整合spring data jpa4.3.1 引入pom依赖4.3.2 application.properties新增配置4.3.3 创建实体类4.3.4 创建Dao4.3.5 创建Controller4.3.6 运行测试 4.4、SpringBoot整合多数据源4.4.1配置文件中新增两个数据源4.4.2读取成Spring配置4.4.3根据不同的配置创建数据源4.4.4在不同包下创建Mapper4.4.5 Service层4.4.6 Controller层4.4.7运行测试

导读

  本篇主要介绍springboot整合各种数据访问组件，包括JdbcTemplate、mybatis、spring data jpa、整合多数据源、MyBatis Generator生成注解版Mapper等知识点。

4.1、SpringBoot整合使用JdbcTemplate

4.1.1 引入pom依赖

<!-- jdbcTemplate 依赖 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <!-- mysql 依赖 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <!--单元测试--> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <scope>test</scope> </dependency>

4.1.2 配置application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/springboot_demo?serverTimezone=GMT spring.datasource.username=root spring.datasource.password=123456 spring.datasource.driver-class-name=com.mysql.jdbc.Driver

4.1.3 UserService类

接口 public interface UserService { void createUser(User user); void deleteUser(User user); User getUser(User user); } 实现类 @Service public class UserServiceImpl implements UserService { @Autowired private JdbcTemplate jdbcTemplate; @Override public void createUser(User user) { jdbcTemplate.update("insert into t_user(name,user_name,pass,age,sex,birthday) values(?,?,?,?,?,?);", user.getName(), user.getUsername(), user.getPass(), user.getAge(), user.getSex(), user.getBirthday()); } @Override public void deleteUser(User user) { jdbcTemplate.update("delete from t_user where id=?", user.getId()); } @Override public User getUser(User user) { return jdbcTemplate.queryForObject("select * from t_user where id=?", new RowMapper<User>() { @Override public User mapRow(ResultSet res, int i) throws SQLException { User userRes = new User(); userRes.setId(res.getInt("id")); userRes.setName(res.getString("name")); userRes.setUsername(res.getString("user_name")); userRes.setAge(res.getInt("age")); userRes.setSex(res.getString("sex")); userRes.setPass(res.getString("pass")); userRes.setBirthday(res.getDate("birthday").toString()); return userRes; } }, user.getId()); } }

4.1.4 单元测试

src/test/java/com/bigbird/springbootdemo/service/UserServiceTest.java

package com.bigbird.springbootdemo.service; import com.bigbird.springbootdemo.model.User; import org.junit.jupiter.api.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.test.context.junit4.SpringRunner; import java.time.LocalDate; @RunWith(SpringRunner.class) @SpringBootTest class UserServiceTest { @Autowired UserServiceImpl userService; @Test void createUser() { User user = new User(); user.setName("陈太太"); user.setUsername("哈先森"); user.setAge(22); user.setSex("女"); user.setPass("12334"); user.setBirthday(LocalDate.now().toString()); userService.createUser(user); } @Test void getUser() { User user = new User(); user.setId(1); System.out.println(userService.getUser(user)); } @Test void deleteUser() { User user = new User(); user.setId(1); userService.deleteUser(user); } }

4.2、SpringBoot整合使用mybatis

4.2.1、pom文件配置

<dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.1</version> </dependency>

4.2.2、配置application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/springboot_demo?serverTimezone=GMT spring.datasource.username=root spring.datasource.password=123456 spring.datasource.driver-class-name=com.mysql.jdbc.Driver #开启mybatis日志 logging.level.com.bigbird.springbootdemo.service=debug

4.2.3、编写Mapper接口

com.bigbird.springbootdemo.service.MyUserMapper

public interface MyUserMapper { // 查询 @Select("SELECT * FROM T_USER WHERE NAME = #{name}") @Results({ @Result(column = "user_name", property = "userName", jdbcType = JdbcType.VARCHAR) }) User findByName(@Param("name") String name); // 删除 @Delete("DELETE FROM T_USER WHERE id = #{id}") int deleteUserById(@Param("id") int id); // 添加 @Insert("INSERT INTO T_USER(NAME,USER_NAME,PASS,AGE,SEX,BIRTHDAY) VALUES (#{name},#{userName}, #{pass}, #{age}, #{sex}, #{birthday})") int insertUser(User user); }

4.2.4、Service代码

@Service public class UserService4MybatisImpl implements UserService { @Autowired private MyUserMapper userMapper; @Override public void createUser(User user) { userMapper.insertUser(user); } @Override public User getUser(User user) { User userRes = userMapper.findByName(user.getName()); return userRes; } @Override public void deleteUser(User user) { userMapper.deleteUserById(user.getId()); } }

4.2.5、引导类

使用注解扫描Mapper

@MapperScan(basePackages = {"com.bigbird.springbootdemo.service"}) @SpringBootApplication public class SpringbootLearnApplication { public static void main(String[] args) { SpringApplication.run(SpringbootLearnApplication.class, args); } }

4.2.6、单元测试

com.bigbird.springbootdemo.service.UserService4MybatisImplTest

@RunWith(SpringRunner.class) @SpringBootTest class UserService4MybatisImplTest { @Autowired MyUserMapper userMapper; @Test void createUser() { User user = new User(); user.setName("周老板"); user.setUserName("周先森"); user.setAge(22); user.setSex("男"); user.setPass("212334"); user.setBirthday(LocalDate.now().toString()); userMapper.insertUser(user); } @Test void getUser() { User user = userMapper.findByName("周老板"); System.out.println(user); } @Test void deleteUser() { userMapper.deleteUserById(2); } }

4.2.7、SpringBoot整合Mybatis分页插件

pageHelper简介

PageHelper 是一款好用的开源免费的 Mybatis 第三方物理分页插件

物理分页 物理分页依赖的是某一物理实体，这个物理实体就是数据库，比如MySQL数据库提供了limit关键字，程序员只需要编写带有limit关键字的SQL语句，数据库返回的就是分页结果。逻辑分页 逻辑分页依赖的是程序员编写的代码。数据库返回的不是分页结果，而是全部数据，然后再由程序员通过代码获取分页数据，常用的操作是一次性从数据库中查询出全部数据并存储到List集合中，因为List集合有序，再根据索引获取指定范围的数据。PageHelper支持常见的 12 种数据库。Oracle,MySql,MariaDB,SQLite,DB2,PostgreSQL,SqlServer 等。支持多种分页方式，支持常见的 RowBounds(PageRowBounds)，PageHelper.startPage方法调用、Mapper 接口参数调用。

引入Maven依赖

<!-- springboot 整合 pagehelper --> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper-spring-boot-starter</artifactId> <version>1.2.5</version> </dependency>

配置application.properties

#pagehelper配置 pagehelper.helperDialect=mysql pagehelper.reasonable=true pagehelper.supportMethodsArguments=true pagehelper.params=count=countSql pagehelper.page-size-zero=true

Mapper层

com.bigbird.springbootdemo.service.MyUserMapper 增加查询所有用户的方法

@Select("SELECT * FROM T_USER") @Results({@Result(column = "user_name", property = "userName", jdbcType = JdbcType.VARCHAR)}) List<User> findAllUsers();

Service层

com.bigbird.springbootdemo.service.UserService4MybatisImpl

/** * page 当前页数 * size 每页大小 * * @param pageNum * @param pageSize * @return */ public PageInfo<User> findUserList(int pageNum, int pageSize) { // 开启分页插件,放在查询语句上面,作用于后面第一条sql语句 PageHelper.startPage(pageNum, pageSize); List<User> listUser = userMapper.findAllUsers(); // 封装分页之后的数据 PageInfo<User> pageInfoUser = new PageInfo<>(listUser); return pageInfoUser; }

Controller层

com.bigbird.springbootdemo.controller.PageHelpController

@RestController public class PageHelpController { @Autowired private UserService4MybatisImpl userService; @RequestMapping("/findUserList") public PageInfo<User> findUserList(@RequestParam(value = "page", required = false, defaultValue = "1") int page, @RequestParam(value = "size", required = false, defaultValue = "3") int size) { System.out.println("====================page:" + page + " size:" + size + "===================="); return userService.findUserList(page, size); } }

运行测试

启动主程序，浏览器输入： http://localhost:8080/findUserList http://localhost:8080/findUserList?page=3&size=2

pageHelp插件会自动过滤不合理的页码,比如负数页码直接转换为1 配置项pagehelper.reasonable=true表示查询页码不合理时（比如超出总页数）总返回最后一页的数据。

4.2.8、SpringBoot整合Mybatis Generator

MyBatis Generator（MBG）是MyBatis 和iBATIS的代码生成器。只要配置好数据库的相关信息，就可以根据数据库表生成简单CRUD操作的XML配置文件、Mapper文件(DAO接口)、Java实体类。实际开发中能够有效减少程序员的工作量，甚至不用程序员手动写sql。官网：http://mybatis.org/generator/index.htmlmybatis-generator有多种用法：命令行、maven插件等。命令行方式通常要把相关jar包下载到本地，再使用java -jar 运行。方便起见，本文演示使用maven插件的方式。

1.新建Maven项目

新建一个新的springBoot工程即可，pom文件引入mybatis-generator-maven-plugin

<!-- mybatis-generator自动生成代码插件 --> <plugin> <groupId>org.mybatis.generator</groupId> <artifactId>mybatis-generator-maven-plugin</artifactId> <version>1.4.0</version> </plugin>

2.编写配置文件(resource目录下)

generator.properties：配置数据库信息,在generatorConfig.xml使用：

#generatorConfig Info generator.location=D:\\software\\maven\\apache-maven-3.3.9\\repository\\mysql\\mysql-connector-java\\5.1.32\\mysql-connector-java-5.1.32.jar generator.targetPackage=com.zpc.videoshow.generated #gererator.schema=oracle-schema gererator.tableName=video_info gererator.domainObjectName=VideoInfo jdbc.driver=com.mysql.jdbc.Driver jdbc.host=jdbc:mysql://localhost:3306/videoshow jdbc.userName=root jdbc.passWord=123456 jdbc.initialSize=0 jdbc.maxActive=20 jdbc.maxIdle=20 jdbc.minIdle=1 jdbc.maxWait=1000

generatorConfig.xml：配置generator插件运行需要的参数信息

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE generatorConfiguration PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN" "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd"> <generatorConfiguration> <!-- 引入配置文件 --> <properties resource="generator.properties"/> <!-- 数据库驱动包位置,路径请不要有中文--> <!-- <classPathEntry location="D:\software\lib\mysql-connector-java-5.1.21.jar" /> --> <classPathEntry location="${generator.location}"/> <!-- 一个数据库一个context--> <context id="DB2Tables" targetRuntime="MyBatis3"> <!-- 生成的pojo，将implements Serializable --> <plugin type="org.mybatis.generator.plugins.SerializablePlugin"></plugin> <!-- 注释 --> <commentGenerator> <property name="suppressAllComments" value="true"/><!-- 是否取消注释 --> <!-- <property name="suppressDate" value="true" /> 是否生成注释代时间戳 --> </commentGenerator> <!-- 数据库链接URL、用户名、密码 --> <!-- <jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://localhost:3306/sy" userId="sypro" password="sypro"> --> <jdbcConnection driverClass="${jdbc.driver}" connectionURL="${jdbc.host}" userId="${jdbc.userName}" password="${jdbc.passWord}"> </jdbcConnection> <!-- 类型转换 --> <javaTypeResolver> <!-- 默认false，把JDBC DECIMAL 和 NUMERIC 类型解析为 Integer true，把JDBC DECIMAL 和 NUMERIC 类型解析为java.math.BigDecimal --> <property name="forceBigDecimals" value="false"/> </javaTypeResolver> <!-- 生成model模型，设置对应的包名(targetPackage)和存放路径(targetProject)。targetProject可以指定具体的路径,如./src/main/java,也可以使用MAVEN来自动生成,这样生成的代码会在target/generatord-source目录下 --> <javaModelGenerator targetPackage="${generator.targetPackage}" targetProject="./src/main/java"> <!-- 是否在当前路径下新加一层schema,eg：false路径com.oop.eksp.user.model 而true:com.oop.eksp.user.model.[schemaName] --> <property name="enableSubPackages" value="false"/> <!-- 从数据库返回的值被清理前后的空格 --> <property name="trimStrings" value="true"/> </javaModelGenerator> <!--对应的mapper.xml文件 --> <sqlMapGenerator targetPackage="${generator.targetPackage}" targetProject="./src/main/java"> <property name="enableSubPackages" value="true"/> </sqlMapGenerator> <!-- 对应的Mapper接口类文件 --> <javaClientGenerator type="XMLMAPPER" targetPackage="${generator.targetPackage}" targetProject="./src/main/java"> <property name="enableSubPackages" value="true"/> </javaClientGenerator> <!-- 列出要生成代码的所有表，这里配置的是不生成Example文件 --> <!-- schema即为数据库名tableName为对应的数据库表 domainObjectName是要生成的实体类 enable*ByExample是否生成 example类 --> <table tableName="${gererator.tableName}" domainObjectName="${gererator.domainObjectName}" schema="${gererator.schema}" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"> <!-- 忽略列，不生成bean 字段 <ignoreColumn column="FRED" />--> <!-- 指定列的java数据类型 <columnOverride column="LONG_VARCHAR_FIELD" jdbcType="VARCHAR" /> --> <!-- 用于指定生成实体类时是否使用实际的列名作为实体类的属性名。false是 Camel Case风格--> <property name="useActualColumnNames" value="false"/> </table> </context> </generatorConfiguration>

3.运行generator插件

运行之前确保数据库表已经存在，数据库出于可连接状态 方法1：直接找到mybatis-generator的插件，右击运行 方法2：在运行配置里面添加maven命令

4.查看生成的文件

5.使用Example

用过Hibernate的同学一定感叹于其完全不用手动写sql的强大功能，其实Mybatis也可以配置生成Example，省去一些简单的sql编写，实际开发中也会带来方便。

a.修改generatorConfig.xml的配置： enableCountByExample=“true” enableUpdateByExample=“true” enableDeleteByExample=“true” enableSelectByExample=“true” selectByExampleQueryId=“true”

b.pom中引入mybatis的依赖：

<dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.5</version> </dependency> c.运行generator 这种情况下多生成了一个Example的文件，Mapper文件的内容也会多很多example相关的：

6. MyBatis Generator生成注解版Mapper

参见官方：http://mybatis.org/generator/whatsNew.html 修改<javaClientGenerator> 元素的 type属性为 ANNOTATEDMAPPER

<!-- 对应的Mapper接口类文件 --> <javaClientGenerator type="ANNOTATEDMAPPER" targetPackage="${generator.targetPackage}" targetProject="./src/main/java"> <property name="enableSubPackages" value="true"/> </javaClientGenerator>

7.一些小技巧

建表时，字段名称建议用"_"分隔多个单词，比如:AWB_NO、REC_ID…，这样生成的entity，属性名称就会变成漂亮的驼峰命名，即：awbNo、recId

oracle中，数值形的字段，如果指定精度，比如Number(16,2)，默认生成entity属性是BigDecimal型 ，如果不指定精度，比如:Number(8)，指默认生成的是Long型

oracle中的nvarchar/nvarchar2，mybatis-generator会识别成Object型，建议不要用nvarchar2，改用varchar2

4.3、SpringBoot整合spring data jpa

Spring Data JPA 是 Spring 基于 ORM 框架、JPA 规范的基础上封装的一套JPA应用框架，可使开发者用极简的代码即可实现对数据的访问和操作。它提供了包括增删改查等在内的常用功能，且易于扩展。使用 Spring Data JPA 可以极大提高开发效率！！！

4.3.1 引入pom依赖

<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency>

4.3.2 application.properties新增配置

#update表示如果原来不存表则创建表结构,前提是数据存在 spring.jpa.hibernate.ddl-auto=update #是否打印出自动生产的SQL，方便调试的时候查看 spring.jpa.show-sql= true

4.3.3 创建实体类

com.bigbird.springbootdemo.model.UserEntity

import javax.persistence.*; @Entity(name = "t_jpa_user") public class UserEntity { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer id; @Column(name = "name") private String name; @Column(name = "age") private Integer age; }

4.3.4 创建Dao

com.bigbird.springbootdemo.dao.UserDao

import com.bigbird.springbootdemo.model.UserEntity; import org.springframework.data.jpa.repository.JpaRepository; public interface UserDao extends JpaRepository<UserEntity, Integer> { }

4.3.5 创建Controller

com.bigbird.springbootdemo.controller.JpaController (省略了Service层)

import java.util.Optional; @RestController public class JpaController { @Autowired private UserDao userDao; @RequestMapping("/jpaFindUser") public Object findUser(UserEntity user) { Optional<UserEntity> userOptional = userDao.findById(user.getId()); UserEntity reusltUser = userOptional.get(); return reusltUser == null ? "没有查询到数据" : reusltUser; } @RequestMapping("/jpaInsertUser") public Object insertUser(UserEntity user) { UserEntity entity = userDao.save(user); return entity; } @RequestMapping("/jpaDeleteUser") public Object deleteUser(UserEntity user) { userDao.delete(user); return "success"; } }

4.3.6 运行测试

运行引导类启动应用，将自动连接数据库创建表t_jpa_user。

浏览器输入下列请求，观察结果 http://localhost:8080/jpaInsertUser?id=1&age=11&name=大鸟 http://localhost:8080/jpaInsertUser?age=99&name=白眉道长 http://localhost:8080/jpaFindUser?id=1 http://localhost:8080/jpaFindUser?id=100

欢迎关注「程猿薇茑」

4.4、SpringBoot整合多数据源

思考：在项目中何时使用到多数据源呢？说起多数据源，一般用于主从模式或者业务比较复杂的场景，需要多个数据库分库来支持的业务。原理：可以根据包名，加载不同的数据源不同的数据源可以理解为不同的数据库，即程序中需要访问多个数据库。

4.4.1配置文件中新增两个数据源

application.properties

# Mysql 1 mysql.datasource.test1.url = jdbc:mysql://localhost:3306/test01?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT mysql.datasource.test1.username = root mysql.datasource.test1.password = 123456 mysql.datasource.test1.minPoolSize = 3 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 # Mysql 2 mysql.datasource.test2.url =jdbc:mysql://localhost:3306/test02?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT mysql.datasource.test2.username =root mysql.datasource.test2.password =123456 mysql.datasource.test2.minPoolSize = 3 mysql.datasource.test2.maxPoolSize = 25 mysql.datasource.test2.maxLifetime = 20000 mysql.datasource.test2.borrowConnectionTimeout = 30 mysql.datasource.test2.loginTimeout = 30 mysql.datasource.test2.maintenanceInterval = 60 mysql.datasource.test2.maxIdleTime = 60

4.4.2读取成Spring配置

com.bigbird.springbootdemo.config.DBConfig1

@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; //省略getter、setter }

com.bigbird.springbootdemo.config.DBConfig2

@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; //省略getter、setter }

4.4.3根据不同的配置创建数据源

Datasource1:com.bigbird.springbootdemo.config.MyBatisConfig1

@Configuration // basePackages 最好分开配置 如果放在同一个文件夹可能会报错,不同包下的mapper操作不同的数据库数据 @MapperScan(basePackages = "com.bigbird.springbootdemo.mapper1", sqlSessionTemplateRef = "test1SqlSessionTemplate") public class MyBatisConfig1 { // 配置数据源 @Bean(name = "test1DataSource") public DataSource testDataSource(DBConfig1 dbConfig1) throws SQLException { MysqlXADataSource mysqlXaDataSource = new MysqlXADataSource(); mysqlXaDataSource.setUrl(dbConfig1.getUrl()); mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true); mysqlXaDataSource.setPassword(dbConfig1.getPassword()); mysqlXaDataSource.setUser(dbConfig1.getUsername()); mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true); return mysqlXaDataSource; } @Bean(name = "test1SqlSessionFactory") public SqlSessionFactory testSqlSessionFactory(@Qualifier("test1DataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); bean.setDataSource(dataSource); return bean.getObject(); } @Bean(name = "test1SqlSessionTemplate") public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) { return new SqlSessionTemplate(sqlSessionFactory); } }

Datasource2:com.bigbird.springbootdemo.config.MyBatisConfig2

@Configuration @MapperScan(basePackages = "com.bigbird.springbootdemo.mapper2", sqlSessionTemplateRef = "test2SqlSessionTemplate") public class MyBatisConfig2 { // 配置数据源 @Bean(name = "test2DataSource") public DataSource testDataSource(DBConfig2 dbConfig2) throws SQLException { MysqlXADataSource mysqlXaDataSource = new MysqlXADataSource(); mysqlXaDataSource.setUrl(dbConfig2.getUrl()); mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true); mysqlXaDataSource.setPassword(dbConfig2.getPassword()); mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true); mysqlXaDataSource.setUser(dbConfig2.getUsername()); return mysqlXaDataSource; } @Bean(name = "test2SqlSessionFactory") public SqlSessionFactory testSqlSessionFactory(@Qualifier("test2DataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); bean.setDataSource(dataSource); return bean.getObject(); } @Bean(name = "test2SqlSessionTemplate") public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) { return new SqlSessionTemplate(sqlSessionFactory); } }

4.4.4在不同包下创建Mapper

com.bigbird.springbootdemo.mapper1.UserMapper01

public interface UserMapper01 { // 查询 @Select("SELECT * FROM T_USER WHERE USER_NAME = #{userName}") @Results({@Result(column = "user_name", property = "userName", jdbcType = JdbcType.VARCHAR)}) User findUserByUserName(@Param("userName") String userName); @Select("SELECT * FROM T_USER ") @Results({@Result(column = "user_name", property = "userName", jdbcType = JdbcType.VARCHAR)}) List<User> findAllUsers(); // 新增 @Insert("INSERT INTO T_USER(NAME,USER_NAME,PASS,AGE,SEX,BIRTHDAY) VALUES (#{name},#{userName}, #{pass}, #{age}, #{sex}, #{birthday})") int insertUser(User user); }

com.bigbird.springbootdemo.mapper2.UserMapper02

public interface UserMapper02 { // 查询 @Select("SELECT * FROM T_USER WHERE USER_NAME = #{userName}") @Results({@Result(column = "user_name", property = "userName", jdbcType = JdbcType.VARCHAR)}) User findUserByUserName(@Param("userName") String userName); @Select("SELECT * FROM T_USER ") @Results({@Result(column = "user_name", property = "userName", jdbcType = JdbcType.VARCHAR)}) List<User> findAllUsers(); // 新增 @Insert("INSERT INTO T_USER(NAME,USER_NAME,PASS,AGE,SEX,BIRTHDAY) VALUES (#{name},#{userName}, #{pass}, #{age}, #{sex}, #{birthday})") int insertUser(User user); }

4.4.5 Service层

对不同的数据源创建不同的service com.bigbird.springbootdemo.service.UserService01

@Service public class UserService01 { @Autowired private UserMapper01 userMapper01; public int insertUser(String name, String userName, int age) { User user = new User(); user.setName(name); user.setUserName(userName); user.setAge(age); int insertUserResult = userMapper01.insertUser(user); return insertUserResult; } public User queryUserByName(String userName) { User user = userMapper01.findUserByUserName(userName); return user; } public List<User> queryAllUsers() { List<User> allUsers = userMapper01.findAllUsers(); return allUsers; } }

com.bigbird.springbootdemo.service.UserService02

@Service public class UserService02 { @Autowired private UserMapper01 userMapper01; @Autowired private UserMapper02 userMapper02; public int insertUser(String name, String userName, Integer age) { User user = new User(); user.setName(name); user.setUserName(userName); user.setAge(age); int insertUserResult = userMapper02.insertUser(user); return insertUserResult; } public int insertUserTest01AndTest02(String name, String userName, Integer age) { User user = new User(); user.setName(name); user.setUserName(userName); user.setAge(age); // 第一个数据源 int insertUserResult01 = userMapper01.insertUser(user); // 第二个数据源 int insertUserResult02 = userMapper02.insertUser(user); int result = insertUserResult01 + insertUserResult02; return result; } public List<User> queryAllUsers() { List<User> allUsers1 = userMapper01.findAllUsers(); List<User> allUsers2 = userMapper02.findAllUsers(); allUsers2.addAll(allUsers1); return allUsers2; } }

4.4.6 Controller层

com.bigbird.springbootdemo.controller.MybatisMultilDataSourceController

@RestController public class MybatisMultilDataSourceController { @Autowired private UserService01 userService01; @Autowired private UserService02 userService02; @RequestMapping("/insertUser01") public int insertUserTest1(String name, String userName,int age) { return userService01.insertUser(name,userName, age); } @RequestMapping("/insertUser02") public int insertUserTest2(String name, String userName,int age) { return userService02.insertUser(name, userName,age); } @RequestMapping("/insertUser01And02") public int insertUserTest01AndTest02(String name,String userName, int age) { return userService02.insertUserTest01AndTest02(name, userName,age);//同时操作两个数据库 } @RequestMapping("/queryUser01") public User queryUser01(String name) { return userService01.queryUserByName(name); } @RequestMapping("/queryAllUser01") public List<User> queryAllUser01() { return userService01.queryAllUsers(); } @RequestMapping("/queryAllUser02") public List<User> queryAllUser02() { return userService02.queryAllUsers(); } }

4.4.7运行测试

// 开启读取配置文件 @EnableConfigurationProperties(value = {DBConfig1.class, DBConfig2.class}) //@MapperScan(basePackages = {"com.bigbird.springbootdemo.service"}) @SpringBootApplication public class SpringbootLearnApplication { public static void main(String[] args) { SpringApplication.run(SpringbootLearnApplication.class, args); } }

运行可能报错： file [F:\springboot-learn\target\classes\com\bigbird\springbootdemo\service\MyUserMapper.class] required a single bean, but 2 were found:

test1SqlSessionFactory: defined by method ‘testSqlSessionFactory’ in class path resource [com/bigbird/springbootdemo/config/MyBatisConfig1.class]test2SqlSessionFactory: defined by method ‘testSqlSessionFactory’ in class path resource [com/bigbird/springbootdemo/config/MyBatisConfig2.class]

注释掉先前数据库相关的代码 主函数：com.bigbird.springbootdemo.SpringbootLearnApplication

//@MapperScan(basePackages = {"com.bigbird.springbootdemo.service"})

几个服务：

com.bigbird.springbootdemo.service.UserService4MybatisImpl= com.bigbird.springbootdemo.service.UserServiceImpl com.bigbird.springbootdemo.dao.UserDao

再次运行引导类，可能报如下错误： A component required a bean named ‘entityManagerFactory’ that could not be found.

需要注释掉先前引入的jpa依赖：

<!-- <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> -->

以及注释掉：

JpaController.java UserEntity.java

再次运行引导类，启动成功！

浏览器输入下列地址测试：

操作数据库1 http://localhost:8080/insertUser01?name=test01&userName=xx&age=99操作数据库2 http://localhost:8080/insertUser02?name=test02&userName=yy&age=98同时操作两个数据库 http://localhost:8080/insertUser01And02?name=testall&userName=xxy y&age=100查询 http://localhost:8080/queryUser01?name=xxyy http://localhost:8080/queryAllUser01 http://localhost:8080/queryAllUser02

市面上有一些不错的插件比如[Mybatis-plus](https://mp.baomidou.com/),也可以方便地实现多数据源管理。

欢迎关注「程猿薇茑」