通用Mapper
1 简介
1、作用
- 生成常用增删改查操作的SQL语句。
2、网站
Gitee:abel533/Mapper
2 基本使用
1、测试sql
CREATE SCHEMA `common_mapper` ; create table table_emp ( emp_id int not null auto_increment, emp_name varchar(500) null, emp_salary double(15,5) null, emp_age int null, primary key (emp_id) ) INSERT INTO `common_mapper`.`table_emp` (`emp_id`, `emp_name`, `emp_salary`, `emp_age`) VALUES ('1', 'tom', '1254.37', '27'); INSERT INTO `common_mapper`.`table_emp` (`emp_id`, `emp_name`, `emp_salary`, `emp_age`) VALUES ('2', 'jerry', '6635.42', '38'); INSERT INTO `common_mapper`.`table_emp` (`emp_id`, `emp_name`, `emp_salary`, `emp_age`) VALUES ('3', 'bob', '5560.11', '40'); INSERT INTO `common_mapper`.`table_emp` (`emp_id`, `emp_name`, `emp_salary`, `emp_age`) VALUES ('4', 'kate', '2209.11', '22'); INSERT INTO `common_mapper`.`table_emp` (`emp_id`, `emp_name`, `emp_salary`, `emp_age`) VALUES ('5', 'justin', '4203.15', '30');
2、创建项目
通用Mapper依赖:
<dependency> <groupId>tk.mybatis</groupId> <artifactId>mapper-spring-boot-starter</artifactId> <version>2.1.5</version> </dependency>
完整依赖:
<dependencies> <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.1.2</version> </dependency> <dependency> <groupId>tk.mybatis</groupId> <artifactId>mapper-spring-boot-starter</artifactId> <version>2.1.5</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.21</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> <exclusions> <exclusion> <groupId>org.junit.vintage</groupId> <artifactId>junit-vintage-engine</artifactId> </exclusion> </exclusions> </dependency> </dependencies>
3、配置
spring: datasource: type: com.alibaba.druid.pool.DruidDataSource url: jdbc:mysql://localhost:3306/common_mapper?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC username: root password: root
4、开启注解扫描
在启动类上使用
@MapperScan("com.xianhuii.mapper")
,扫描该包下的所有接口。该注解位于
tk.mybatis.spring.annotation.MapperScan
包下。package com.xianhuii; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import tk.mybatis.spring.annotation.MapperScan; @MapperScan("com.xianhuii.mapper") @SpringBootApplication public class CommonMapperApplication { public static void main(String[] args) { SpringApplication.run(CommonMapperApplication.class, args); } }
5、实体类
考虑到基本数据类型在Java类中都有默认值,会导致MyBatis在执行相关操作时很难判断当前字段是否为null。所以,在MyBatis环境下使用Java实体类时,尽量不要使用基本数据类型,都应该使用对应的包装类型。
@Table(name = "table_emp")
:指定映射的表名为table_emp
。如不添加该注解,默认映射的表名为类名小写,此时为employee
。package com.xianhuii.entities; import javax.persistence.*; @Table(name = "table_emp") public class Employee { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer empId; // emp_id private String empName; // emp_name @Column(name = "emp_salary") private Double empSalary; // emp_salary private Integer empAge; // emp_age public Employee() { } public Employee(Integer empId, String empName, Double empSalary, Integer empAge) { this.empId = empId; this.empName = empName; this.empSalary = empSalary; this.empAge = empAge; } public Integer getEmpId() { return empId; } public void setEmpId(Integer empId) { this.empId = empId; } public String getEmpName() { return empName; } public void setEmpName(String empName) { this.empName = empName; } public Double getEmpSalary() { return empSalary; } public void setEmpSalary(Double empSalary) { this.empSalary = empSalary; } public Integer getEmpAge() { return empAge; } public void setEmpAge(Integer empAge) { this.empAge = empAge; } @Override public String toString() { return "Employee{" + "empId=" + empId + ", empName='" + empName + '\'' + ", empSalary=" + empSalary + ", empAge=" + empAge + '}'; } }
6、Mapper
具体操作数据库的Mapper接口。
需要继承通用Mapper提供的核心接口:
tk.mybatis.mapper.common.Mapper<T>
,泛型是对应的实体类。package com.xianhuii.mapper; import com.xianhuii.entities.Employee; import tk.mybatis.mapper.common.Mapper; public interface EmployeeMapper extends Mapper<Employee> { }
3 深入源码
1、Mapper
package tk.mybatis.mapper.common; import tk.mybatis.mapper.annotation.RegisterMapper; @RegisterMapper public interface Mapper<T> extends BaseMapper<T>, ExampleMapper<T>, RowBoundsMapper<T>, Marker { }
2、BaseMapper
基本的增、删、改、查。
package tk.mybatis.mapper.common; import tk.mybatis.mapper.annotation.RegisterMapper; import tk.mybatis.mapper.common.base.BaseDeleteMapper; import tk.mybatis.mapper.common.base.BaseInsertMapper; import tk.mybatis.mapper.common.base.BaseSelectMapper; import tk.mybatis.mapper.common.base.BaseUpdateMapper; @RegisterMapper public interface BaseMapper<T> extends BaseSelectMapper<T>, BaseInsertMapper<T>, BaseUpdateMapper<T>, BaseDeleteMapper<T> { }
3、ExampleMapper
模糊查询。
package tk.mybatis.mapper.common; import tk.mybatis.mapper.annotation.RegisterMapper; import tk.mybatis.mapper.common.example.DeleteByExampleMapper; import tk.mybatis.mapper.common.example.SelectByExampleMapper; import tk.mybatis.mapper.common.example.SelectCountByExampleMapper; import tk.mybatis.mapper.common.example.SelectOneByExampleMapper; import tk.mybatis.mapper.common.example.UpdateByExampleMapper; import tk.mybatis.mapper.common.example.UpdateByExampleSelectiveMapper; @RegisterMapper public interface ExampleMapper<T> extends SelectByExampleMapper<T>, SelectOneByExampleMapper<T>, SelectCountByExampleMapper<T>, DeleteByExampleMapper<T>, UpdateByExampleMapper<T>, UpdateByExampleSelectiveMapper<T> { }
4、RowBoundsMapper
分页。
package tk.mybatis.mapper.common; import tk.mybatis.mapper.annotation.RegisterMapper; import tk.mybatis.mapper.common.rowbounds.SelectByExampleRowBoundsMapper; import tk.mybatis.mapper.common.rowbounds.SelectRowBoundsMapper; @RegisterMapper public interface RowBoundsMapper<T> extends SelectByExampleRowBoundsMapper<T>, SelectRowBoundsMapper<T> { }
4 常用注解
1、@Table
- 建立
实体类↔数据库表
之间的对应关系。 - 默认规则:指定
实体类类名首字母小写
作为表名。 - 使用
name
属性显式指定对应的表名。
2、@Column
- 建立
实体类属性↔表字段
之间的对应关系。 - 默认规则:
- 实体类字段:驼峰式命名。
- 数据库表字段:使用“_”分隔各个单词。
- 使用
name
属性显式指定对应的字段名。
3、@Id
- 通用Mapper在执行
xxxByPrimaryKey(key)
方法时,有两种情况。 - 情况一:没有使用
@Id
注解指明主键字段,会将实体类中的所有属性组合起来作为联合主键。 - 情况二:使用
@Id
注解明确标记与数据库表中的主键对应的属性。
4、@GeneratedValue
- 使通用Mapper在执行
insert
方法后,将数据库自动生成的主键值回写到实体类对象中。 - 自增主键用法:
@GeneratedValue(strategy = GenerationType.IDENTITY)
。 - 序列主键用法:略。
- 应用:购物车结账(生成订单数据→封装到Order对象中→保存Order对象→生成一系列订单详情数据→List<orderitem>→在每一个OrderItem中设置Order对象的主键值作为外键→批量保存List<orderitem>)。</orderitem></orderitem>
5、@Transient
- 一般情况下,实体类中的属性和数据库表中的字段是一一对应的。但是也有很多情况我们也会在实体类中添加一些额外的属性。在这种情况下,就需要使用
@Transient
注解告诉通用Mapper这不是表中的字段。
5 常用方法
1、selectOne
使用
非null
的值生成WHERE子句。在WHERE子句中使用
=
进行比较。要求必须返回一个实体类结果,如果有多个,则会抛出异常。
// 1、创建封装查询条件的实体类对象 Employee bob = new Employee(null, "bob", null, null); // 2、执行查询 Employee result = employeeService.selectOne(bob); // 3、打印 System.out.println(result); // Employee{empId=3, empName='bob', empSalary=5560.11, empAge=40}
其他类似的方法:
selectAll
、selectByPrimaryKey
、selectCount
、select
。
2、xxxByPrimaryKey
- 需要使用
@Id
注解明确标记和数据库表主键字段对应的实体类属性,否则会将所有实体类属性作为联合主键。
3、selectByPrimaryKey
根据主键字段进行查询,方法参数必须包含完整的主键属性,查询条件使用等号。
需要使用
@Id
注解明确标记和数据库表主键字段对应的实体类属性,否则会将所有实体类属性作为联合主键。// 1、提供id Integer empId = 3; // 2、根据主键进行查询 Employee employee = employeeService.selectByPrimaryKey(empId); // 3、打印结果:Employee{empId=3, empName='bob', empSalary=5560.11, empAge=40} System.out.println(employee);
4、existsWithPrimaryKey
- 需要使用
@Id
注解明确标记和数据库表主键字段对应的实体类属性,否则会将所有实体类属性作为联合主键。// 1、提供id Integer empId = 3; // 2、根据主键进行查询 Boolean exists = employeeService.existsWithPrimaryKey(empId); // 3、打印结果:true System.out.println(exists);
5、insert
插入数据。
若在实体类的主键属性上使用
@GeneratedValue(strategy = GenerationType.IDENTITY)
,则会回写对应的主键。// 1、创建实体类对象,封装要保存的信息 Employee employee = new Employee(null, "emp01", 1000.00, 23); // 2、执行插入操作 employeeService.insert(employee); // 3、获取employee对象的主键字段值:7 Integer empId = employee.getEmpId(); System.out.println(empId);
6、xxxSelective
- 非主键字段如果为null,则不加入到SQL语句中,效率更高。
7、insertSelective
选择性插入数据。
非主键字段如果为null,则不加入到SQL语句中,效率更高。
// 1、创建实体类对象,封装要保存的信息 Employee employee = new Employee(null, "emp02", null, 23); // 2、执行插入操作 employeeService.insertSelective(employee);
8、updateByPrimaryKeySelective
根据主键选择性更新。
需要使用
@Id
注解明确标记和数据库表主键字段对应的实体类属性,否则会将所有实体类属性作为联合主键。非主键字段如果为null,则不加入到SQL语句中,效率更高。
// 1、创建实体类 Employee employee = new Employee(7, "empNewName", null, null); // 2、执行更新:只有emp_name修改了,其他不变 employeeService.updateByPrimaryKeySelective(employee);
9、delete
根据实体属性作为条件进行删除,查询条件使用等号。
注意:如果传入的参数为null,或参数的属性全为null,则会删除所有数据。
// 1、声明实体类对象作为查询条件 Employee employee = null; // 2、执行删除:所有数据都会被删除 employeeService.delete(employee);
10、deleteByPrimaryKey
// 1、提供主键值 Integer empId = 5; // 2、执行删除:删除emp_id=5的数据 employeeService.deleteByPrimaryKey(empId);
6 QBC查询(Query By Criterial)
- 通过Java对象,将查询条件进行模块化封装。
1、selectByExample
// 目标:WHERE (emp_salary > ? AND emp_age < ?) OR (emp_salary < ? AND emp_age > ?) // 1、创建Example对象 Example example = new Example(Employee.class); // +++++++++++++++++++++++++++++++++++++++++++++++++++++++ // ①设置排序 example.orderBy("empSalary").asc().orderBy("empAge").desc(); // ②设置去重 example.setDistinct(true); // ③设置select字段 example.selectProperties("empName", "empSalary"); // +++++++++++++++++++++++++++++++++++++++++++++++++++++++ // 2、通过example创建Criteria对象 Example.Criteria criteria01 = example.createCriteria(); Example.Criteria criteria02 = example.createCriteria(); // 3、在两个criteria中分别设置查询条件 // property参数:实体类的属性名 // value参数:实体类的属性值 criteria01.andGreaterThan("empSalary", 3000) .andLessThan("empAge", 25); criteria02.andLessThan("empSalary", 5000) .andGreaterThan("empAge", 30); // 4、使用OR关键字组装两个criteria对象 example.or(criteria02); // 5、执行查询 List<Employee> exampleList = employeeService.selectByExample(example); for (Employee employee: exampleList) { System.out.println(employee); }
2、selectByRowBounds
- 没有使用LIMIT,而是将所有数据查询出来,在内存中进行分页。
- 一般不使用此方法进行分页,而是使用PageHelper。
7 逆向工程
1、新建Maven工程
pom.xml
<?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.xianhuii</groupId> <artifactId>MapperMBG</artifactId> <version>1.0-SNAPSHOT</version> <properties> <!--targetJavaProject:声明存放源码的目录位置--> <!--${basedir}:引用工程根目录--> <targetJavaProject>${basedir}/src/main/java</targetJavaProject> <!--targetMapperPackage:声明MBG生成的XxxMapper接口存放的package位置--> <targetMapperPackage>com.xianhuii.project.mapper</targetMapperPackage> <!--targetModelPackage:声明MBG生成的实体类的存放位置--> <targetModelPackage>com.xianhuii.project.entity</targetModelPackage> <!--targetResourcesProject:声明存放资源文件、XML配置文件的位置--> <targetResourcesProject>${basedir}/src/main/resources</targetResourcesProject> <!--targetXMLPackage:声明存放具体XxxMapper.xml文件的目录位置--> <targetXMLPackage>mapper</targetXMLPackage> <!--通用Mapper的版本号--> <mapper.version>4.0.0-beta3</mapper.version> <!--MySQL驱动的版本号--> <mysql.version>8.0.19</mysql.version> </properties> <build> <plugins> <plugin> <groupId>org.mybatis.generator</groupId> <artifactId>mybatis-generator-maven-plugin</artifactId> <version>1.3.2</version> <!--配置generatorConfig.xml配置文件的路径--> <configuration> <configurationFile>${basedir}/src/main/resources/generator/generatorConfig.xml</configurationFile> <overwrite>true</overwrite> <verbose>true</verbose> </configuration> <!--MBG插件的依赖信息--> <dependencies> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>${mysql.version}</version> </dependency> <dependency> <groupId>tk.mybatis</groupId> <artifactId>mapper</artifactId> <version>${mapper.version}</version> </dependency> </dependencies> </plugin> </plugins> </build> </project>
resources/config.properties
数据库配置
jdbc.driverClass = com.mysql.cj.jdbc.Driver jdbc.url = jdbc:mysql://localhost:3306/common_mapper?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC jdbc.user = root jdbc.password = root #c3p0 jdbc.maxPoolSize=50 jdbc.minPoolSize=10 jdbc.maxStatements=100 jdbc.testConnection=true
通用Mapper配置
mapper.plugin = tk.mybatis.mapper.generator.MapperPlugin mapper.Mapper = tk.mybatis.mapper.common.Mapper
resources/generator/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">
<context id="Mysql" targetRuntime="MyBatis3Simple" defaultModelType="flat"> <property name="beginningDelimiter" value="`"/> <property name="endingDelimiter" value="`"/> <!--配置通用Mapper的MBG插件相关信息--> <plugin type="${mapper.plugin}"> <property name="mappers" value="${mapper.Mapper}"/> </plugin> <!--配置连接数据库的基本信息--> <jdbcConnection driverClass="${jdbc.driverClass}" connectionURL="${jdbc.url}" userId="${jdbc.user}" password="${jdbc.password}"> </jdbcConnection> <!--配置Java实体类存放位置--> <javaModelGenerator targetPackage="${targetModelPackage}" targetProject="${targetJavaProject}"/> <!--配置XxxMapper.xml存放位置--> <sqlMapGenerator targetPackage="${targetXMLPackage}" targetProject="${targetResourcesProject}"/> <!--配置XxxMapper.java接口存放位置--> <javaClientGenerator targetPackage="${targetMapperPackage}" targetProject="${targetJavaProject}" type="XMLMAPPER" /> <!--根据数据库表生成Java文件的相关规则--> <!--tableName="%"表示数据库中所有表都参与逆向工程,此时使用默认规则--> <!--默认规则:table_dept -> TableDept--> <!--不符合默认规则时,使用tableName、domainObjectName显式指定--> <table tableName="table_emp" domainObjectName="Employee" > <!--配置主键生成策略--> <generatedKey column="emp_id" sqlStatement="Mysql" identity="true"/> </table> </context></generatorconfiguration> ```
- 目录结构:
2、运行
- 打开Terminal终端。
- 在pom.xml这一级目录的命令行窗口执行
mvn mybatis-generator:generate
即可(前提是配置了mvn)。
3、生成代码
pom.xml(需要添加JPA、MyBatis相关依赖,测试需要MySQL驱动依赖)
<dependencies> <!--由于生成的代码需要依赖JPA,因此需要导入相关依赖--> <dependency> <groupId>tk.mybatis</groupId> <artifactId>mapper</artifactId> <version>4.1.5</version> </dependency> <!--RowBounds需要依赖MyBatis--> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.3</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>8.0.19</scope> </dependency> </dependencies>
src\main\java\com\xianhuii\project\entity\Employee.java(可适当修改)
package com.xianhuii.project.entity; import javax.persistence.*; @Table(name = "table_emp") public class Employee { @Id @Column(name = "emp_id") @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer empId; @Column(name = "emp_name") private String empName; @Column(name = "emp_salary") private Double empSalary; @Column(name = "emp_age") private Integer empAge; /** * @return emp_id */ public Integer getEmpId() { return empId; } /** * @param empId */ public void setEmpId(Integer empId) { this.empId = empId; } /** * @return emp_name */ public String getEmpName() { return empName; } /** * @param empName */ public void setEmpName(String empName) { this.empName = empName; } /** * @return emp_salary */ public Double getEmpSalary() { return empSalary; } /** * @param empSalary */ public void setEmpSalary(Double empSalary) { this.empSalary = empSalary; } /** * @return emp_age */ public Integer getEmpAge() { return empAge; } /** * @param empAge */ public void setEmpAge(Integer empAge) { this.empAge = empAge; } }
src\main\java\com\xianhuii\project\mapper\EmployeeMapper.java
package com.xianhuii.project.mapper; import com.xianhuii.project.entity.Employee; import tk.mybatis.mapper.common.Mapper; public interface EmployeeMapper extends Mapper<Employee> { }
src\main\resources\mapper\EmployeeMapper.xml
<?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.xianhuii.project.mapper.EmployeeMapper" > <resultMap id="BaseResultMap" type="com.xianhuii.project.entity.Employee" > <!-- WARNING - @mbg.generated --> <id column="emp_id" property="empId" jdbcType="INTEGER" /> <result column="emp_name" property="empName" jdbcType="VARCHAR" /> <result column="emp_salary" property="empSalary" jdbcType="DOUBLE" /> <result column="emp_age" property="empAge" jdbcType="INTEGER" /> </resultMap> </mapper>
8 自定义Mapper接口
- 可以根据开发的实际需要对Mapper接口进行定制。
1、自定义Mapper接口
MyMapper.java(注意:不能与EmployeeMapper接口在在同一个包下):
package com.xianhuii.mymapper; import tk.mybatis.mapper.common.base.select.SelectAllMapper; import tk.mybatis.mapper.common.example.SelectByExampleMapper; public interface MyMapper<T> extends SelectAllMapper<T>, SelectByExampleMapper<T> { }
2、配置
- 将我们自定义的接口作为Mapper。
mapper: mappers: com.xianhuii.mymapper.MyMapper # 默认为tk的Mapper<T>
3、使用
EmployeeMapper.java:
package com.xianhuii.mapper; import com.xianhuii.entities.Employee; import com.xianhuii.mymapper.MyMapper; public interface EmployeeMapper extends MyMapper<Employee> { }
4、测试
/** * 结果: * Employee{empId=1, empName='tom', empSalary=1254.37, empAge=27} * Employee{empId=2, empName='jerry', empSalary=6635.42, empAge=38} * Employee{empId=3, empName='bob', empSalary=5560.11, empAge=40} * Employee{empId=4, empName='kate', empSalary=2209.11, empAge=22} */ @Test public void test() { List<Employee> employees = employeeService.selectAll(); for (Employee employee: employees) { System.out.println(employee); } }
9 接口扩展
- 增加通用Mapper没有提供的功能。
1、MyBatchUpdateProvider(核心)
package com.xianhuii.mymapper; import org.apache.ibatis.mapping.MappedStatement; import tk.mybatis.mapper.entity.EntityColumn; import tk.mybatis.mapper.mapperhelper.EntityHelper; import tk.mybatis.mapper.mapperhelper.MapperHelper; import tk.mybatis.mapper.mapperhelper.MapperTemplate; import tk.mybatis.mapper.mapperhelper.SqlHelper; import java.util.Set; public class MyBatchUpdateProvider extends MapperTemplate { public MyBatchUpdateProvider(Class<?> mapperClass, MapperHelper mapperHelper) { super(mapperClass, mapperHelper); } /** * <foreach collection="list" item="record" seperator=";"> * UPDATE table_emp * <set> * emp_name=#{record.empName}, * emp_age=#{record.empAge}, * emp_salary=#{record.empSalary}, * </set> * WHERE * emp_id=#{record.empId} * </foreach> */ public String batchUpdate(MappedStatement statement) { StringBuilder builder = new StringBuilder(); builder.append("<foreach collection=\"list\" item=\"record\" separator=\";\">"); Class<?> entityClass = super.getEntityClass(statement); String tableName = super.tableName(entityClass); String updateClause = SqlHelper.updateTable(entityClass, tableName); builder.append(updateClause); builder.append("<set>"); Set<EntityColumn> columns = EntityHelper.getColumns(entityClass); String idColumn = null; String idHolder = null; for (EntityColumn entityColumn : columns) { boolean isPrimaryKey = entityColumn.isId(); if (isPrimaryKey) { idColumn = entityColumn.getColumn(); idHolder = entityColumn.getColumnHolder("record"); } else { String column = entityColumn.getColumn(); String columnHolder = entityColumn.getColumnHolder("record"); builder.append(column).append("=").append(columnHolder).append(","); } } builder.append("</set>"); builder.append("where ").append(idColumn).append("=").append(idHolder); builder.append("</foreach>"); System.out.println(builder.toString()); return builder.toString(); } }
2、MyBatchUpdateMapper
package com.xianhuii.mymapper; import org.apache.ibatis.annotations.UpdateProvider; import java.util.List; public interface MyBatchUpdateMapper<T> { @UpdateProvider(type = MyBatchUpdateProvider.class, method = "dynamicSQL") void batchUpdate(List<T> list); }
3、MyMapper
package com.xianhuii.mymapper; import tk.mybatis.mapper.common.base.select.SelectAllMapper; import tk.mybatis.mapper.common.example.SelectByExampleMapper; public interface MyMapper<T> extends MyBatchUpdateMapper<T> { }
4、配置文件
- url中添加
allowMultiQueries=true
,允许批量执行SQL语句。spring: datasource: type: com.alibaba.druid.pool.DruidDataSource url: jdbc:mysql://localhost:3306/common_mapper?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&allowMultiQueries=true username: root password: root driver-class-name: com.mysql.cj.jdbc.Driver mapper: mappers: com.xianhuii.mymapper.MyMapper
5、测试
/** 结果: * Employee{empId=1, empName='emp01', empSalary=111.11, empAge=10} * Employee{empId=2, empName='emp02', empSalary=111.11, empAge=10} * Employee{empId=3, empName='emp02', empSalary=111.11, empAge=10} * Employee{empId=4, empName='emp04', empSalary=111.11, empAge=10} */ @Test public void test() { List<Employee> empList = new ArrayList<>(); empList.add(new Employee(1, "emp01", 111.11, 10)); empList.add(new Employee(2, "emp02", 111.11, 10)); empList.add(new Employee(3, "emp02", 111.11, 10)); empList.add(new Employee(4, "emp04", 111.11, 10)); employeeService.batchUpdateEmp(empList); }
10 类型处理器:TypeHandler
- 通用Mapper默认情况下只处理简单类型,而对复杂类型不进行映射,相当于对复杂类型添加了
@Transient
注解。 - 复杂类型↔TypeHandler↔数据库表字段。
1、实体类
User:
package com.xianhuii.entities; import com.xianhuii.mapper.AddressTypeHandler; import tk.mybatis.mapper.annotation.ColumnType; import javax.persistence.Id; import javax.persistence.Table; @Table(name = "table_user") public class User { @Id private Integer userId; private String userName; @ColumnType(typeHandler = AddressTypeHandler.class) private Address address; private SeasonEnum season; public User() { } public User(Integer userId, String userName, Address address, SeasonEnum season) { this.userId = userId; this.userName = userName; this.address = address; this.season = season; } public Integer getUserId() { return userId; } public void setUserId(Integer userId) { this.userId = userId; } public String getUserName() { return userName; } public void setUserName(String userName) { this.userName = userName; } public Address getAddress() { return address; } public void setAddress(Address address) { this.address = address; } public SeasonEnum getSeason() { return season; } public void setSeason(SeasonEnum season) { this.season = season; } @Override public String toString() { return "User{" + "userId=" + userId + ", userName='" + userName + '\'' + ", address=" + address + ", season=" + season + '}'; } }
Address:
package com.xianhuii.entities; public class Address { private String province; private String city; private String street; public Address() { } public Address(String prov***ring city, String street) { this.province = province; this.city = city; this.street = street; } public String getProvince() { return province; } public void setProv***ring province) { this.province = province; } public String getCity() { return city; } public void setCity(String city) { this.city = city; } public String getStreet() { return street; } public void setStreet(String street) { this.street = street; } }
2、自定义类型处理器
AddressTypeHandler:
package com.xianhuii.mapper; import com.xianhuii.entities.Address; import org.apache.ibatis.type.BaseTypeHandler; import org.apache.ibatis.type.JdbcType; import java.sql.CallableStatement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class AddressTypeHandler extends BaseTypeHandler<Address> { /** * 将复杂类型转化为字符串 * @param preparedStatement * @param i * @param address * @param jdbcType * @throws SQLException */ @Override public void setNonNullParameter(PreparedStatement preparedStatement, int i, Address address, JdbcType jdbcType) throws SQLException { // 1、对address对象进行验证 if (address == null) { return; } // 2、从address对象中取出具体数据 String province = address.getProv***ring city = address.getCity(); String street = address.getStreet(); // 3、拼装成字符串,各个值之间使用“,”分开 StringBuilder builder = new StringBuilder(); builder.append(province) .append(",") .append(city) .append(",") .append(street); String parameterValue = builder.toString(); // 4、设置参数 preparedStatement.setString(i, parameterValue); } @Override public Address getNullableResult(ResultSet resultSet, String columnName) throws SQLException { // 1、根据字段名,从resultSet对象中获取字段值 String columnValue = resultSet.getString(columnName); // 2、验证columnValue是否有效 if (columnValue == null || columnValue.length() == 0 || !columnValue.contains(",")) { return null; } // 3、根据“,”对columnValue进行拆分 String[] split = columnValue.split(","); // 4、从拆分结果数组中获取Address需要的具体数据 String province = split[0]; String city= split[1]; String street = split[2]; // 5、根据具体对象组装一个Address对象 Address address = new Address(province, city, street); return address; } @Override public Address getNullableResult(ResultSet resultSet, int columnIndex) throws SQLException { // 1、根据字段名,从resultSet对象中获取字段值 String columnValue = resultSet.getString(columnIndex); // 2、验证columnValue是否有效 if (columnValue == null || columnValue.length() == 0 || !columnValue.contains(",")) { return null; } // 3、根据“,”对columnValue进行拆分 String[] split = columnValue.split(","); // 4、从拆分结果数组中获取Address需要的具体数据 String province = split[0]; String city= split[1]; String street = split[2]; // 5、根据具体对象组装一个Address对象 Address address = new Address(province, city, street); return address; } @Override public Address getNullableResult(CallableStatement callableStatement, int columnIndex) throws SQLException { // 1、根据字段名,从resultSet对象中获取字段值 String columnValue = callableStatement.getString(columnIndex); // 2、验证columnValue是否有效 if (columnValue == null || columnValue.length() == 0 || !columnValue.contains(",")) { return null; } // 3、根据“,”对columnValue进行拆分 String[] split = columnValue.split(","); // 4、从拆分结果数组中获取Address需要的具体数据 String province = split[0]; String city= split[1]; String street = split[2]; // 5、根据具体对象组装一个Address对象 Address address = new Address(province, city, street); return address; } }
3、配置
- 实体类属性上配置
@ColumnType
。@ColumnType(typeHandler = AddressTypeHandler.class) private Address address;