SpringBoot Mybatis custom TypeHandler
When using MyBatis for DB operation, one thing we often do is to map db fields to Java beans, usually we use ResultMap to achieve the mapping, through this label can specify the binding relationship between the two. So what if the field type in the Java Bean is different from that in the DB?
For example, timestamp is defined in db, but long is defined in Java bean
- through
BaseTypeHandler
To implement custom type conversions
I. Environment preparation
1. Prepare the database
Using mysql as the instance database for this article, add a new table
CREATE TABLE `money` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL DEFAULT ' ' COMMENT 'Username',
`money` int(26) NOT NULL DEFAULT '0' COMMENT 'money',
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
`create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
`update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time'.PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Copy the code
2. Project environment
This article is developed with SpringBoot 2.2.1.RELEASE + Maven 3.5.3 + IDEA
Pom dependencies are as follows
<dependencies>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
</dependencies>
Copy the code
Db configuration information Application.yml
spring:
datasource:
url: JDBC: mysql: / / 127.0.0.1:3306 / story? useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password:
Copy the code
II. Example demonstration
1. The entity definition
Notice that the create_AT and update_AT types in the above case are timestmap, and we define the Entity as follows
@Data
public class MoneyPo {
private Integer id;
private String name;
private Long money;
private Integer isDeleted;
private Timestamp createAt;
private Long updateAt;
}
Copy the code
2. Mapper test interface
Define a simple query interface that uses annotations directly (there is little difference in how XML is written)
/** * primary key query **@param id id
* @return {@link MoneyPo}
*/
@Select("select * from money where id = #{id}")
@Results(id = "moneyResultMap", value = { @Result(property = "id", column = "id", id = true, jdbcType = JdbcType.INTEGER), @Result(property = "name", column = "name", jdbcType = JdbcType.VARCHAR), @Result(property = "money", column = "money", jdbcType = JdbcType.INTEGER), @Result(property = "isDeleted", column = "is_deleted", jdbcType = JdbcType.TINYINT), @Result(property = "createAt", column = "create_at", jdbcType = JdbcType.TIMESTAMP), // @Result(property = "updateAt", column = "update_at", jdbcType = JdbcType.TIMESTAMP)})
@Result(property = "updateAt", column = "update_at", jdbcType = JdbcType.TIMESTAMP, typeHandler = Timestamp2LongHandler.class)})
MoneyPo getById(@Param("id") int id);
// More on the use of SelectProvider later, mainly a demonstration of dynamic SQL
@SelectProvider(type = MoneyService.class, method = "getByIdSql")
@ResultMap(value = "moneyResultMap")
MoneyPo getByIdForProvider(@Param("id") int id);
Copy the code
Description:
@Results
: This annotation works as a ResultMap tag and is used to define the mapping between db fields and Java Beansid = "moneyResultMap"
This id definition, this id definition, allows you to reuse at sign Results@Result
Under:updateAt
TypeHandler, which specifies a custom typeHandler, to implementJdbcType.TEMSTAMP
Conversion to long in Java Beans
3. Type conversion
Custom type conversions mainly inherit from the BaseTypeHandler class. Generic types are types in Java beans
/** * Custom type conversion: convert the date type in the database to a timestamp of type long ** three registration methods: * 1. Specify typeHandler directly in the Result tag, as in@Result(property = "updateAt", column = "update_at", jdbcType = JdbcType.TIMESTAMP, typeHandler = Timestamp2LongHandler.class) * 2. SetTypeHandlers (new Timestamp2LongHandler()); * 3. The XML configuration, < typeHandler handler = "com. Git. Hui. Boot. Mybatis. Handler. Timestamp2LongHandler" / > * *@author yihui
* @date2021/7/7 * /
@MappedTypes(value = Long.class)
@MappedJdbcTypes(value = {JdbcType.DATE, JdbcType.TIME, JdbcType.TIMESTAMP})
public class Timestamp2LongHandler extends BaseTypeHandler<Long> {
/** * Convert Java type to JDBC type **@param preparedStatement
* @param i
* @paramALong millisecond timestamp *@paramJdbcType Indicates the db field type *@throws SQLException
*/
@Override
public void setNonNullParameter(PreparedStatement preparedStatement, int i, Long aLong, JdbcType jdbcType) throws SQLException {
if (jdbcType == JdbcType.DATE) {
preparedStatement.setDate(i, new Date(aLong));
} else if (jdbcType == JdbcType.TIME) {
preparedStatement.setTime(i, new Time(aLong));
} else if (jdbcType == JdbcType.TIMESTAMP) {
preparedStatement.setTimestamp(i, newTimestamp(aLong)); }}@Override
public Long getNullableResult(ResultSet resultSet, String s) throws SQLException {
return parse2time(resultSet.getObject(s));
}
@Override
public Long getNullableResult(ResultSet resultSet, int i) throws SQLException {
return parse2time(resultSet.getObject(i));
}
@Override
public Long getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
return parse2time(callableStatement.getObject(i));
}
private Long parse2time(Object value) {
if (value instanceof Date) {
return ((Date) value).getTime();
} else if (value instanceof Time) {
return ((Time) value).getTime();
} else if (value instanceof Timestamp) {
return ((Timestamp) value).getTime();
}
return null; }}Copy the code
- SetNonNullParameter: Converts Java type to JDBC type
- GetNullableResult: Converts the JDBC type to the Java type
4. TypeHandler registration
There’s nothing wrong with defining a TypeHandler ourselves. The next step is to make it work. In general, there are several ways
4.1 Specified in the result tag
Specified by typeHandler in the Result tag
The way to use XML is as follows
<result column="update_at" property="updateAt" jdbcType="TIMESTAMP" typeHandler="com.git.hui.boot.mybatis.handler.Timestamp2LongHandler"/>
Copy the code
Annotation @result in the following way
@Result(property = "updateAt", column = "update_at", jdbcType = JdbcType.TIMESTAMP, typeHandler = Timestamp2LongHandler.class)
Copy the code
4.2 SqlSessionFactory Global configuration
The usage posture above is precisely specified, and if we want to apply it to all scenarios, we can do it through SqlSessionFactory
@Bean(name = "sqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(
// Set the XML location of Mybatis
new PathMatchingResourcePatternResolver().getResources("classpath*:mapping/*.xml"));
// Register TypeHandler for global use
bean.setTypeHandlers(new Timestamp2LongHandler());
return bean.getObject();
}
Copy the code
4.3 Global XML Configuration
In addition to the above case, there is also a mybatis-config. XML configuration file to register, such as
<! DOCTYPEconfiguration
PUBLIC "- / / ibatis.apache.org//DTD Config / 3.1 / EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<! -- Hump underline format support -->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<typeHandlers>
<typeHandler handler="com.git.hui.boot.mybatis.handler.Timestamp2LongHandler"/>
</typeHandlers>
</configuration>
Copy the code
Note To use the above configuration file, you need to specify the following configuration in SpringBoot; otherwise, the configuration will not take effect
mybatis:
config-location: classpath:mybatis-config.xml
Copy the code
4.4 SpringBoot Configuration Mode
A Springboot configuration file that registers TypeHandler by specifying a type-handlers-package
mybatis:
type-handlers-package: com.git.hui.boot.mybatis.handler
Copy the code
5. Summary
This article mainly introduces the type in DB and the type in Java bean mapping adaptation strategy, mainly by inheriting BaseTypeHandler to achieve custom type conversion
There are two ways to use a custom TypeHandler: globally valid or precisely specified
@Result
/<result>
Tag, specified by typeHandler- SqlSessionFactory Sets typeHandler globally
mybatis-config.xml
Configuration file SettingstypeHandlers
In addition, the configuration of this article also supports the interconversion configuration of hump and underscore. This is also a common configuration, which can be configured in mybatis-config as follows
<setting name="mapUnderscoreToCamelCase" value="true"/>
Copy the code
Hump and underline can rotate each other, so is there any way to implement custom name mapping? If you know, please feel free to advise
III. Can’t miss the source code and related knowledge points
0. Project
- Project: github.com/liuyueyi/sp…
- Source: github.com/liuyueyi/sp…
- Source: github.com/liuyueyi/sp…
Mybatis series blog post
- 【DB series 】SpringBoot series Mybatis Mapper interface and Sql binding several posture
- 【DB series 】SpringBoot series Mybatis Mapper registration of several ways
- 【DB series 】Mybatis-Plus multi-data source configuration
- Mybatis based on DB series 】 【 AbstractRoutingDataSource multiple source switch with AOP implementation
- 【DB series 】Mybatis multi-data source configuration and use
- 【DB series 】 Multi-data source configuration and use of JdbcTemplate
- 【DB series 】Mybatis-Plus code automatic generation
- 【DB series 】MybatisPlus Integration
- 【DB series 】Mybatis+ annotations integration
- 【DB series 】Mybatis+ XML integration
1. Wechat official account: Yash Blog
As far as the letter is not as good, the above content is purely one’s opinion, due to the limited personal ability, it is inevitable that there are omissions and mistakes, if you find bugs or have better suggestions, welcome criticism and correction, don’t hesitate to appreciate
Below a gray personal blog, record all the study and work of the blog, welcome everyone to go to stroll
- A grey Blog Personal Blog blog.hhui.top
- A Grey Blog-Spring feature Blog Spring.hhui.top