Last updated on:
When talking about data persistence in Spring, we can’t overlook Mybatis, which is widely used in enterprise apps. It eases troubles of writing and testing data access and process codes. What will we need to do in order to integrate Mybatis into a Spring Boot app? Simply put,
- Tell Spring Boot where the mappers are (@MapperScan);
- Declare interfaces in the Java mapper file (@Mapper);
- Define SQL statements in the XML file corresponding to the interfaces, or you can put down those SQLs in the Java mapper file if you like.
We’ll walk through a few examples to see how Mybatis works in some scenarios. In the first example, we’ll use Mybatis to retrieve the list of all departments from the database.
The source codes are available in the sample projects on GitHub.
Prerequisite
- Spring Tool Suite is installed in the IDE;
- the tables, Departments and Employees, are created in the database;
- and the corresponding data classes are defined.
For details, refer to [Tips] Spring Boot – Access Data with JDBC.
When you new a project for practice, choose Spring Starter Project.
Dependency
To enable Mybatis, we’ll need to include Mybatis dependency in the Spring Boot app’s pom.xml file.
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>3.0.3</version>
</dependency> In case of using a default data source
If you prefer Spring Boot to configure a data source as the default one for the app, all you need to do is to write down the connection strings in the application property file, as shown in the example below.
Note that the keys’ names prefix with “spring.datasource”.
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@localhost:1521/xepdb1
spring.datasource.username=dbuser
spring.datasource.password=dbpassword
In the start class of the app, declare the directory of the mappers by using @MapperScan.
@SpringBootApplication
@MapperScan("com.example._50_dao.mapper")
public class SpringMvcApplication {
static final Logger log = LoggerFactory.getLogger(SpringMvcApplication.class);
public static void main(String[] args) {
SpringApplication.run(SpringMvcApplication.class, args);
}
}
Make sure to place the Java mapper file under src/main/java, and to place the XML mapper file under src/main/resources, respectively. They should share the same sub-directory structure as well.
SpringMVC project/DepartmentMapper.java
@Mapper
public interface DepartmentMapper {
List<DptDto> findAll();
…
}
<?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._50_dao.mapper.DepartmentMapper">
<select id="findAll" resultType="com.example._60_dto.DptDto">
SELECT department_id, department_name, manager_id
FROM departments
ORDER BY department_id
</select>
…
</mapper>
At the DptService bean, autowire a mapper to fetch all the departments from the database.
@Service
public class DptService{
@Autowired
DepartmentMapper dptMapper;
@Transactional
public Lis<DptDto> findAll() {
return dptMapper.findAll();
}
}
Run the app
Following the recipe step by step, we got Mybatis ready for use now. System standard I/O is handy, you can use it to check the result. Here we show the departments on a web view.
How to handle joins?
For example, in case we’d like to get a dataset containing all the columns of Employees and Departments, how can we get this done?
Of course, we join the tables in the SQL script, in other words, we let the database to complete the join operation, rather than write Java codes to combine the datasets of Employees and Departments; therefore, we just need to handle the joined dataset provided by the database, much easier.
We start with defining a data class named EmpDptDto which has all the fields of the join tables.
public class EmpDptDto {
private Integer employee_id;
private String first_name;
private String last_name;
private Double salary;
private String hire_date;
private Integer manager_id;
private Long department_id;
private String department_name;
/*
* Constructors, Getters, Setters etc.
*/
}
Declare a method in the Java mapper interface, which returns a list of EmpDptDto objects.
@Mapper
public interface EmployeeMapper {
List<EmpDptDto> empLeftJoinDpt();
}
In the XML mapper file, define a <select> element, with id set as “empLeftJoinDpt”, resultType set as the preceding data class.
<select id="empLeftJoinDpt" resultType="com.example._60_dto.EmpDptDto">
SELECT e.employee_id, e.first_name, e.last_name, e.salary, e.hire_date, e.manager_id,
d.department_id, d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
</select">
That’s it. The result comes out like this if we print out that list:
[Employee-Department[employee_id='100', first_name='David', last_name='OConnell',
salary='4800.000000', hire_date='2010/01/01', manager_id='null', department_id=10,
department_name=Administration],
…
Employee-Department[employee_id='202', first_name='Pat', last_name='Fay',
salary='6000.000000', hire_date='2010/12/31', manager_id='200', department_id=20,
department_name=Marketing]]
What if accessing the secondary data source?
This is also a common scenario. For example, we’d like to get the full list of employees from the secondary data source. How to do this? The point is that Mybatis’s mappers are bound to a data source.
Update the secondary data source’s configuration
In the configuration file of the secondary data source,
- Specify the mapper location (@MapperScan);
- Define a SqlSessionBean.
@Configuration(proxyBeanMethods = false)
@MapperScan(
basePackages = "com.example._50_dao.sndmapper"
)
public class SecondaryDataSourceConfig {
/*
* Secondary data source
*/
@Qualifier("secondary")
@Bean(defaultCandidate = false)
public DataSource sndDataSource(
@Value("${secondary.datasource.driver-class-name}") String dsDriverClassName,
@Value("${secondary.datasource.url}") String dsURL,
@Value("${secondary.datasource.username}") String dsUsername,
@Value("${secondary.datasource.password}") String dsPassword
) {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName(dsDriverClassName);
dataSource.setUrl(dsURL);
dataSource.setUsername(dsUsername);
dataSource.setPassword(dsPassword);
return dataSource;
}
…
/*
* SqlSessionFactoryBean for Mybatis
*/
@Qualifier("secondary")
@Bean(defaultCandidate = false)
public SqlSessionFactory sndSqlSessionFactory(
@Qualifier("secondary") DataSource dataSource) throws Exception {
SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
return sessionFactory.getObject();
}
@Qualifier("secondary")
@Bean(defaultCandidate = false)
public SqlSessionTemplate sndSqlSessionTemplate(
@Qualifier("secondary") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
Add Java and XML mapper files
The files are located in the directories specified in the data source’s configuration file, as shown in the below screenshot. In this example, we copied the mapper files for the primary data source, with different names. The namespace in the XML mapper file was updated as well.
<mapper namespace="com.example._50_dao.sndmapper.SndEmployeeMapper">
<mapper namespace="com.example._50_dao.mapper.EmployeeMapper"">
Call the mapper’s method
At the EmpService bean, autowire a SndEmployeeMapper bean and call the findAll() method.
@Service
public class EmpService{
@Autowired
SndEmployeeMapper empMapper;
public List<EmpDto> findAll() {
return empMapper.findAll();
}
}
Check out the result
Launch the SpingMVC sample app from Eclipse.
Click the Employees button.
The result is shown in the screenshot below.
No comments:
Post a Comment