MyBatis extended application
Mybatis PageHelper (paging plug-in)
When using MySQL, we need to use the limit statement to create paging. The logic is very complex and troublesome. Mybatis PageHelper wraps these statements for us. We only need to write a query statement
Import dependent
Build maven project and directly introduce the following dependencies
<!-- mybatis Paging plug-in for -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.10</version>
</dependency>
Configure interceptor plug-in
Under normal MyBatis, you can directly configure the interceptor plug-in in the global configuration file
<!--to configure mybatis plug-in unit--> <plugins> <plugin interceptor="com.github.pagehelper.PageInterceptor"/> </plugins>
Under the integrated SSM framework, Spring's attribute configuration method is used
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- Note other configurations -->
<property name="plugins">
<array>
<bean class="com.github.pagehelper.PageInterceptor">
<property name="properties">
<!--Configure parameters in the following way, one per line -->
<value>
params=value1
</value>
</property>
</bean>
</array>
</property>
</bean>
How to use in code
Calling PageHelper.startPage() before querying the method, by observing the official document, it is found that only the first query followed by this method will be paged.
You can use PageInfo to wrap the queried data, which is more powerful than page. You can view the specific usage in the document
https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/HowToUse.md
@Test
public void test02() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
//The first parameter represents the current page number, and the second parameter represents the number of records per page
Page<Object> page = PageHelper.startPage(5, 2);
List<Employee> emps = mapper.getEmps();
//The second parameter is: how many pages are displayed continuously
PageInfo<Employee> info = new PageInfo<>(emps,5);
for(Employee emp : emps){
System.out.println(emp);
}
System.out.println("Current page:" + page.getPageNum());
System.out.println("Records per page:" + page.getPageSize());
System.out.println("Total page:" + page.getPages());
System.out.println("Total records:" + page.getTotal());
System.out.println("First page:" + info.isIsFirstPage());
int[] nums = info.getNavigatepageNums();
System.out.println("show page numbers");
for (int i = 0;i<nums.length;i++){
System.out.println(nums[i]);
}
}finally {
sqlSession.close();
}
}
Batch operation
Previously, when integrating the SSM framework, batch operations were performed through circular splicing of sql statements. Here is another method with stronger performance
When you create an executor by default, you create a simpleexecution, and a batch executor can perform batch operations
<!--void insertEmp(Employee emp);-->
<insert id="insertEmp">
insert into employee (last_name,gender,email) values (#{lastName},#{gender},#{email})
</insert>
@Test
public void test03() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
//Set the method of creating the executor to batch. This is the executor that can execute batch statements. The default is simple
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
long start = System.currentTimeMillis();
try {
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
for(int i = 0;i < 10000;i++){
mapper.insertEmp(new Employee(UUID.randomUUID().toString().substring(0 ,5),i + "@123.com","0"));
}
sqlSession.commit();
long end = System.currentTimeMillis();
System.out.println("Execution duration:" + (end-start));//Execution time: 1592
}finally {
sqlSession.close();
}
}
Enumeration type handling in MyBatis
MyBatis type processing is implemented by implementing the TypeHandler interface, while enumeration type processing can be used by two processors. Enumeration types are divided into index and name. EnumOrdinalTypeHandler is stored through index and EnumTypeHandle (default) is stored through name
We create an empStatus enumeration attribute based on the employee table
public enum EmpStatus {
LOGIN,LOGOUT,REMOVE;
}
By default, EnumTypeHandle is used to store to the database, and the stored database value is LOGOUT
@Test
public void test04() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
//Set the method of creating the executor to batch. This is the executor that can execute batch statements. The default is simple
SqlSession sqlSession = sqlSessionFactory.openSession();
long start = System.currentTimeMillis();
try {
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
mapper.insertEmp(new Employee("enum","enum@123.com","1", EmpStatus.LOGOUT));
sqlSession.commit();
}finally {
sqlSession.close();
}
}
By modifying the global configuration file, you can modify the default enumeration type processor to EnumOrdinalTypeHandler with a storage value of 1
<typeHandlers> <typeHandler handler="org.apache.ibatis.type.EnumOrdinalTypeHandler" javaType="com.yellowstar.bean.EmpStatus"></typeHandler> </typeHandlers>
In the actual development process, we are often not satisfied with using the default enumeration type processor, such as modifying the enumeration class to the following form. If we want to insert custom code or msg data into the database, we need a custom type processor
public enum EmpStatus {
LOGIN(100,"Logged in"),LOGOUT(200,"Logged out"),REMOVE(300,"non-existent");
Integer code;
String msg;
EmpStatus(Integer code, String msg) {
this.code = code;
this.msg = msg;
}
public Integer getCode() {return code;}
public void setCode(Integer code) {this.code = code;}
public String getMsg() {return msg;}
public void setMsg(String msg) {this.msg = msg;}
//Returns an enumeration class object according to code
public static EmpStatus getEmpStatusByCode(Integer code){
switch (code){
case 100:
return LOGIN;
case 200:
return LOGOUT;
case 300:
return REMOVE;
default:
return LOGOUT;
}
}
}
There are two ways to create a custom processor: implement the TypeHandler interface or inherit the BaseTypeHandler class. We use the way to implement the interface and override the method
public class EmpStatusTypeHandler implements TypeHandler<EmpStatus> {
// Defines how the current data is saved to the database
@Override
public void setParameter(PreparedStatement preparedStatement, int i, EmpStatus empStatus, JdbcType jdbcType) throws SQLException {
System.out.println("Status code to save:" + empStatus.getCode());
preparedStatement.setInt(i,empStatus.getCode());
}
// Get database data
@Override
public EmpStatus getResult(ResultSet resultSet, String s) throws SQLException {
//You need to return an enumeration object according to the enumeration status code obtained from the database
int code = resultSet.getInt(s);
System.out.println("Status code obtained from database:"+code);
EmpStatus empStatus = EmpStatus.getEmpStatusByCode(code);
return empStatus;
}
@Override
public EmpStatus getResult(ResultSet resultSet, int i) throws SQLException {
int code = resultSet.getInt(i);
System.out.println("Status code obtained from database:"+code);
EmpStatus empStatus = EmpStatus.getEmpStatusByCode(code);
return empStatus;
}
@Override
public EmpStatus getResult(CallableStatement callableStatement, int i) throws SQLException {
int code = callableStatement.getInt(i);
System.out.println("Status code obtained from database:"+code);
EmpStatus empStatus = EmpStatus.getEmpStatusByCode(code);
return empStatus;
}
}