前言
欢迎回到我们的MyBatis系列教程。在前几期中,我们已经全面探讨了MyBatis的基础使用、CRUD操作、高级查询、缓存机制、插件使用以及与Spring的整合。通过这些内容,相信大家已经掌握了MyBatis的基本使用方法。本期内容中,我们将进入MyBatis的进阶使用,探讨多数据源配置、动态SQL生成器、MyBatis Generator的使用以及实现复杂查询的方法。这些进阶功能将帮助我们更高效地进行开发,满足复杂的业务需求。
10.1 多数据源配置
在实际开发中,我们可能需要连接多个数据源(例如主库和从库)。MyBatis与Spring结合可以方便地实现多数据源配置。
配置多数据源
- 添加依赖
在pom.xml
中添加MyBatis和Spring的相关依赖:
<dependencies>
<!-- Spring Boot 依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- MyBatis 依赖 -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>
<!-- MySQL 依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
</dependencies>
- 配置数据源
在application.properties
中配置多数据源:
# 主数据源配置
spring.datasource.primary.url=jdbc:mysql://localhost:3306/primary_db
spring.datasource.primary.username=root
spring.datasource.primary.password=password
spring.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driver
# 从数据源配置
spring.datasource.secondary.url=jdbc:mysql://localhost:3306/secondary_db
spring.datasource.secondary.username=root
spring.datasource.secondary.password=password
spring.datasource.secondary.driver-class-name=com.mysql.cj.jdbc.Driver
- 定义数据源配置类
创建数据源配置类:
package com.example.config;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
import org.springframework.boot.jdbc.DataSourceBuilder;
@Configuration
public class DataSourceConfig {
@Primary
@Bean(name = "primaryDataSource")
@ConfigurationProperties(prefix = "spring.datasource.primary")
public DataSource primaryDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "secondaryDataSource")
@ConfigurationProperties(prefix = "spring.datasource.secondary")
public DataSource secondaryDataSource() {
return DataSourceBuilder.create().build();
}
@Primary
@Bean(name = "primarySqlSessionFactory")
public SqlSessionFactory primarySqlSessionFactory(@Qualifier("primaryDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/primary/*.xml"));
return sessionFactory.getObject();
}
@Bean(name = "secondarySqlSessionFactory")
public SqlSessionFactory secondarySqlSessionFactory(@Qualifier("secondaryDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/secondary/*.xml"));
return sessionFactory.getObject();
}
@Primary
@Bean(name = "primaryTransactionManager")
public DataSourceTransactionManager primaryTransactionManager(@Qualifier("primaryDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "secondaryTransactionManager")
public DataSourceTransactionManager secondaryTransactionManager(@Qualifier("secondaryDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
}
- 配置Mapper扫描
在Spring Boot应用程序类中配置Mapper扫描:
package com.example;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.mybatis.spring.annotation.MapperScan;
@SpringBootApplication
@MapperScan(basePackages = "com.example.mapper.primary", sqlSessionFactoryRef = "primarySqlSessionFactory")
@MapperScan(basePackages = "com.example.mapper.secondary", sqlSessionFactoryRef = "secondarySqlSessionFactory")
public class MyBatisMultiDataSourceApplication {
public static void main(String[] args) {
SpringApplication.run(MyBatisMultiDataSourceApplication.class, args);
}
}
10.2 动态SQL生成器
MyBatis动态SQL生成器提供了一种便捷的方式来生成动态SQL语句。通过动态SQL生成器,我们可以根据不同的条件生成不同的SQL语句,从而减少手工拼接SQL的工作量。
示例:使用动态SQL生成器
package com.example.mapper;
import com.example.model.User;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.jdbc.SQL;
import java.util.List;
public interface UserMapper {
@SelectProvider(type = UserSqlBuilder.class, method = "buildGetUsersByConditions")
List<User> getUsersByConditions(String name, Integer age);
class UserSqlBuilder {
public String buildGetUsersByConditions(final String name, final Integer age) {
return new SQL() {{
SELECT("*");
FROM("users");
if (name != null && !name.isEmpty()) {
WHERE("name = #{name}");
}
if (age != null) {
WHERE("age = #{age}");
}
}}.toString();
}
}
}
10.3 MyBatis Generator使用
MyBatis Generator(MBG)是一个代码生成工具,可以根据数据库表生成相应的MyBatis代码,包括实体类、Mapper接口和Mapper映射文件。
配置MyBatis Generator
- 添加依赖
在pom.xml
中添加MyBatis Generator插件依赖:
<build>
<plugins>
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.7</version>
<configuration>
<configurationFile>src/main/resources/generatorConfig.xml</configurationFile>
<verbose>true</verbose>
<overwrite>true</overwrite>
</configuration>
<executions>
<execution>
<id>Generate MyBatis Artifacts</id>
<goals>
<goal>generate</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
- 配置generatorConfig.xml
在src/main/resources
目录下创建generatorConfig.xml
文件:
<?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>
<context id="DB2Tables" targetRuntime="MyBatis3">
<jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
connectionURL="jdbc:mysql://localhost:3306/mybatis_demo"
userId="root"
password="password"/>
<javaModelGenerator targetPackage="com.example.model" targetProject="src/main/java"/>
<sqlMapGenerator targetPackage="mapper" targetProject="src/main/resources"/>
<javaClientGenerator type="XMLMAPPER" targetPackage="com.example.mapper" targetProject="src/main/java"/>
<table tableName="users"/>
</context>
</generatorConfiguration>
- 运行MyBatis Generator
在命令行中运行以下命令,生成代码:
mvn mybatis-generator:generate
10.4 使用MyBatis实现复杂查询
在实际项目中,复杂查询是常见的需求。MyBatis提供了丰富的功能来支持复杂查询,如关联查询、子查询、联合查询等。
示例:关联查询
假设我们有两张表users
和orders
,分别表示用户和订单。我们需要查询用户及其对应的订单信息。
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
age INT
);
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
order_date DATE,
amount DECIMAL(10, 2),
FOREIGN KEY (user_id) REFERENCES users(id)
);
定义数据模型
package com.example.model;
import java.util.List;
public class User {
private int id;
private String name;
private int age;
private List<Order> orders;
// Getters and Setters
}
public class Order {
private int id;
private int userId;
private Date orderDate;
private BigDecimal amount;
// Getters and Setters
}
定义Mapper接口
package com.example.mapper;
import com.example.model.User;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface UserMapper {
@Select("SELECT * FROM users")
List<User> getAllUsers();
@Select("SELECT * FROM users WHERE id = #{id}")
User getUserById(int id);
@Select("SELECT * FROM orders WHERE user_id = #{userId}")
List<Order> getOrdersByUserId(int userId);
}
使用ResultMap进行关联映射
在UserMapper.xml
中配置ResultMap:
<?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.example.mapper.UserMapper">
<resultMap id="userResultMap" type="com.example.model.User">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<collection property="orders" ofType="com.example.model.Order" column="id" select="getOrdersByUserId"/>
</resultMap>
<select id="getAllUsers" resultMap="userResultMap">
SELECT * FROM users
</select>
<select id="getUserById" resultMap="userResultMap">
SELECT * FROM users WHERE id = #{id}
</select>
<select id="getOrdersByUserId" resultType="com.example.model.Order">
SELECT * FROM orders WHERE user_id = #{userId}
</select>
</mapper>
总结
通过本期内容,我们学习了MyBatis的进阶使用,包括多数据源配置、动态SQL生成器、MyBatis Generator的使用以及实现复杂查询的方法。通过这些进阶功能,我们可以更高效地进行开发,满足复杂的业务需求。
下期内容预告
在下一期中,我们将结合前面的所有知识点,进行一个完整的项目实战。我们将从项目背景和需求分析开始,设计项目架构,进行数据库设计和实现基础功能,最终完成一个完整的MyBatis项目。通过这个实战项目,大家将能够更好地巩固所学知识,并将其应用到实际开发中。
感谢大家的阅读,我们下期再见!