How to use MyBatis with Spring Boot using a Mapper XML file
In this article, we'll explore how to use MyBatis with Spring Boot using a Mapper XML file.
MyBatis is a SQL Mapping framework with support for custom SQL, stored procedures and advanced mappings. SpringBoot doesn't provide official support for MyBatis integration, but the MyBatis community built a SpringBoot starter for MyBatis.
You can read about the SpringBoot MyBatis https://mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/ and you can explore the source code on GitHub https://github.com/mybatis/mybatis-spring-boot.
Dependencies
we will use the mybatis-spring-boot-starter library developed by a MyBatis community. starter dependency transitively includes mybatis-spring-boot-autoconfigure, spring-boot-starter, spring-boot-starter-jdbc and mybatis-spring dependencies that otherwise must be included separately.
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.1</version>
</dependency>
Data Model
we have a simple database schema with a table named users containing columns id, username, and email. we will interact with this database using MyBatis and a Mapper XML file.
create table users(
id varchar(255) not null PRIMARY key,
email varchar(255),
username varchar(255) not null,
created_at int8
);
Defining users Entity
@Data
@Entity(name = "users")
@Table(name = "users")
public class Users {
@Id
@GenericGenerator(name = "uuid2", strategy = "uuid2")
@GeneratedValue(strategy = GenerationType.IDENTITY, generator = "uuid2")
@Column(name = "id")
String id;
@Column(name = "email")
String email;
@Column(name = "username")
String userName;
@Column(name = "created_at")
Long createdAt;
}
MyBatisConfig
This configuration is used to specify the locations of our MyBatis mapper XML files. It tells MyBatis where to find the XML files that define the SQL statements and mappings. You provide a list of resource locations (file paths or classpath locations) containing your mapper XML files.
@Configuration
@Slf4j
public class MyBatisConfig
{
@Value("classpath:mapper/*.xml")
private Resource[] mapperResources;
@Bean
public DataSource getDataSourceForDev(DataSourceProperties dataSourceProperties)
{
HikariConfig config = new HikariConfig();
config.setJdbcUrl(dataSourceProperties.getUrl());
config.setUsername(dataSourceProperties.getUsername());
config.setPassword(dataSourceProperties.getPassword());
config.setPoolName(DB_CONNECTION_POOL);
config.setMaximumPoolSize(MAX_CONNECTION_POOL_SIZE);
return new HikariDataSource(config);
}
@Bean
@SneakyThrows
public SqlSessionFactoryBean sqlSessionFactory(DataSource dataSource)
{
SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
configuration.setCallSettersOnNulls(true);
sessionFactory.setDataSource(dataSource);
sessionFactory.setConfiguration(configuration);
sessionFactory.setMapperLocations(mapperResources);
return sessionFactory;
}
@Bean
@SneakyThrows
public SqlSessionTemplate sqlSession(SqlSessionFactoryBean sqlSessionFactory)
{
return new SqlSessionTemplate(sqlSessionFactory.getObject());
}
}
Here, the mapper/*.xml pattern indicates that MyBatis should scan for mapper XML files in the mapper directory on the classpath.
In configuration we have setCallSettersOnNulls to true. It Specifies if setters or map's put method will be called when a retrieved value is null. It is useful when you rely on Map.keySet() or null value initialization. The MyBatis setting table describes the settings, their meanings and their default values.
MyBatis Mapper XML File
Create a Mapper XML file with the Dynamic SQL queries for the operations. This file should be placed in the mapper directory on the classpath same as we used in configuration.
<?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="userReportQueries">
<select id="DAILY_USER_REPORT" resultType="java.util.LinkedHashMap">
SELECT
cod.id as user_id,cast(to_timestamp(cod.created_at / 1000) as date) as userDate,
cod.username,cod.email as userEmail
FROM users cod
WHERE cast(to_timestamp(cod.created_at / 1000) as date) >= #{startDate}
AND cast(to_timestamp(cod.created_at / 1000) as date) < #{endDate}
<if test="userIds != null">
AND cod.id IN
<foreach open="(" separator="," close=")" item="item" index="index" collection="userIds">
#{item}
</foreach>
</if>
order by cod.created_at desc
</select>
<select id="USER_EMAILS" resultType="java.util.LinkedHashMap">
SELECT
cod.id as user_id,cast(to_timestamp(cod.created_at / 1000) as date) as userDate,
cod.username,cod.email as userEmail
FROM users cod
WHERE cast(to_timestamp(cod.created_at / 1000) as date) >= #{startDate}
<if test="emailList != null">
AND cod.email IN
<foreach open="(" separator="," close=")" item="item" index="index" collection="emailList">
#{item}
</foreach>
</if>
<if test="userId != null">
AND cod.id = #{userId}
</if>
order by cod.created_at desc
</select>
</mapper>
In MyBatis XML mapper files, the resultType attribute is used to specify the Java type that MyBatis should map the result of a query to. If you want to fetch a list of objects using sqlSessionTemplate.selectList, you need to specify the appropriate resultType in your mapper XML file.
In this case, we are specifying the resultType as java.util.LinkedHashMap. This means that the result of the query will be mapped into a list of LinkedHashMap objects, where each LinkedHashMap represents a row of data with column names as keys and column values as values. LinkedHashMap maintains the insertion order of keys.
Each column values within these LinkedHashMap could be of various types(Long, Boolean, Date etc.), depending on the actual data returned from the database. column names as keys will be String type.
NOTE: It will be throwing exception due to use of < (Less then) so you may escape it with < or CDATA
Service or Controller
Let's create Service to perform operations. we will use SqlSessionTemplate, The primary Java interface for working with MyBatis.
@Service
@Slf4j
@AllArgsConstructor
public class MyBatisQueryService
{
private final SqlSessionTemplate sqlSessionTemplate;
public List<LinkedHashMap<String, Object>> getReportData(String reportType, Map<String, Object> parameterMap)
{
return sqlSessionTemplate.selectList(reportType, parameterMap);
}
public void doOperations(LocalDate startDate, LocalDate endDate)
{
HashMap<String, Object> parameterMapDailyUserReport = new HashMap<>();
HashMap<String, Object> parameterMapUserEmails = new HashMap<>();
parameterMapDailyUserReport.put("userIds", List.of("user11","user84","user49","use30"));
parameterMapDailyUserReport.put("startDate", startDate);
parameterMapDailyUserReport.put("endDate", endDate);
parameterMapUserEmails.put("startDate", startDate);
parameterMapUserEmails.put("emailList", List.of("user11@gmail.com","user84@collegeek.com"));
List<LinkedHashMap<String, Object>> DailyUserReportData = getReportData("userReportQueries.DAILY_USER_REPORT" , parameterMapDailyUserReport);
List<LinkedHashMap<String, Object>> UserEmailsData = getReportData("userReportQueries.USER_EMAILS" , parameterMapUserEmails);
}
Here in Select Query USER_EMAILS we didn't pass the userId. so Query will run with only email filter!. One of the most powerful features of MyBatis has always been its Dynamic SQL.
If you prefer to use a custom Java class to represent the data, you can create a POJO (Plain Old Java Object) class with fields matching the columns returned by the query. For example: we will use Users Entity as POJO.
Then, in your mapper XML file, you can specify the resultType as your custom class:
<select id="USER_EMAILS" resultType="com.example.Users">
<!-- Your SQL query here -->
</select>
By using your custom class as the resultType, MyBatis will automatically map the columns from the query result to the fields in your class. and in the sqlSessionTemplate.selectList specify the custom POJO to fetch a list of your custom objects:
List<Users> UserEmailsData = getReportData("userReportQueries.USER_EMAILS" , parameterMapUserEmails);
Conclusion
In this article, we explored how to use MyBatis with Spring Boot using a Mapper XML file.we used the sqlSessionTemplate to fetch a list of your custom objects.