Directory
1. What is the persistence layer framework MyBatis
2. Why test MyBatis code
3. The idea of testing MyBatis code
4. The process of unit testing MyBatis code testing
5. Explanation of the mutual conversion between java objects and JDBC data
Six. Analyze the assembly and verification of SQL
7. Description of converting JDBC data to java objects
Eight. Test mapping
Nine. Verification of the mapper method for special cases
1. What is the persistence layer framework MyBatis
MyBatis is a persistence layer framework that supports custom SQL queries, stored procedures, and advanced mapping. It can be configured and mapped with XML or annotations, and the final executed SQL statement is formed by mapping the parameters to the configured SQL, and finally the executed result is mapped into a java object and returned.
Simply put, since java code cannot directly use relational database data, a mapping bridge is needed, and the MyBatis framework plays this role.
2. Why test MyBatis code
Similar to the test object point of view in my last article about testing SQL, development generally needs to call a relational database when implementing the specific functions of the service layer code. If the MyBatis framework is used at this time, this process requires writing a series of MyBatis code, so the code written by developers is the test object.
3. Ideas for testing MyBatis code
First of all, the general process of developing and using the MyBatis framework is as follows
1 Define a global configuration file, only one is required for a system. The main content is the information of the database to be linked, user name, password, and the address of the mapper class XML file to be defined
2 Declare an entity class for each database table, and the attributes in the class correspond to the fields of the database table one by one.
3 Declare a mapper interface for each database table, and the methods in the interface correspond to the SQL commands for operating the table in the XML file
4 Configure a mapper XML file for each database table, the main content is the select/update/delete label statement using the method in the mapper interface, and the statement corresponding to these statements applied to the mapping.
The above process is for a complete new project. If you are creating a new table during iteration, omit step 1. If a field or other modification is made to a table, it depends on the scope of the modification, and analyzes whether steps 2, 3, and 4 need to be modified as needed. Knowing these processes will be a very good guide for testing MyBatis code.
Let’s take a look at the general process of MyBatis framework operation
1 The java code calls a method in a mapper interface
2MyBatis framework finds the XML file with the same name as the mapper interface, and obtains the select/update/delete label statements with the same name as the method 1 in this file (these label statements will have an id)
3 Connect to the database and run SQL according to the global configuration file of MyBatis
4 Map the SQL return according to the resultType/resultMap of the statement in 2
The process of developing and using the MyBatis framework and running the MyBatis framework can guide us to get the idea of testing the MyBatis code.
1) Test entry
The method call in the mapper interface is generally a part of the service layer code, which is different from the web application that can test the service layer code from the control layer. If you want to test the MyBatis code alone, you need to write the test code, which is commonly known as the unit test.
2) Test MyBatis code alone
Some people should have doubts here, why can’t I use the interface tool to test the MyBatis code directly like the test interface? It’s not impossible to test it? The answer is that if you test from the interface tool, the test will inevitably mix the control layer, service layer, and MyBatis code in the service layer for testing. If you only want to test the MyBatis code but start with the interface, it will inevitably lead to bigger problems. s expenses. So here, according to the concept of layered testing, if you only want MyBatis code, then use the unit testing framework to do it.
3) What to test for MyBatis code
The so-called test is what to verify. Now we have learned the running process of the MyBatis framework based on the above, which codes need to be written when developing and using MyBatis code, and we want to separate the MyBatis code from the service layer code when we want to implement the test. , these information and goals can guide us to arrive at the answer.
4) Answer to 3)
* Verify the assembly of SQL
* Verify the mapping between SQL and java
Let me say a little more, citing the point of view in my last SQL article, we only pay attention to whether the development has the right SQL statement, and do not pay attention to whether the database will execute errors.
This sentence actually expresses the idea of using MyBatis code from the page or interface test, and here, we need to start paying attention to whether the development is useful for SQL. (Using the right SQL can be interpreted as meeting the requirements, or it can be understood as the SQL command can be run. The exact explanation for the previous article is the former, and this article is the latter)
4. The process of unit testing MyBatis code testing
1 Create a corresponding test class for each interface class, and create a corresponding test method for each method under the interface class
2 Create data for the database table to be called.
3 Call the method under the interface class in the test class and get the return of the database
4 Use assertions to verify return and expected values, mapped java objects
The standard for whether the assembly of the SQL statement is correct is very simple, as long as the database does not report an error during runtime. To verify the mapping, you need to assert each field returned by the SQL to ensure that there is no missing or wrong mapping. After a little analysis, it can be found that the prerequisite for successful mapping is that the SQL assembly is correct, so from the perspective of writing test scripts, there is no need to assert the SQL statement separately. (Asserting that the assembly of SQL statements actually requires the use of a rule set of SQL commands, which is obviously beyond the scope of the test)
Demonstrate the testing process with a simple example
Interface method: UserMapper
Corresponding entity class: SysRoles
Corresponding XML
final test script
public class BaseMapperTest {
private static SqlSessionFactory sqlSessionFactory;
@BeforeClass
public static void init(){
try {
Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
reader. close();
} catch (IOException ignore) {
ignore. printStackTrace();
}
}
public SqlSession getSqlSession(){
return sqlSessionFactory. openSession();
}
}
public class UserMapperTest extends BaseMapperTest {
@Test
public void testSelectById(){
SqlSession sqlSession = getSqlSession();
try {
// Get the corresponding interface class
UserMapper userMapper = sqlSession. getMapper(UserMapper. class);
//Call the method of the interface to query data
SysUser user = userMapper. selectById(1L);
//Assertion return
Assert.assertEquals("admin",user.getUserName());
Assert.assertTrue("create_time is a timestamp", user.getCreateTime() instanceof Date);
} finally {
sqlSession. close();
}
}
5. Description of the conversion between java objects and JDBC data
Java provides a set of APIs named JDBC for connecting and operating various relational databases. The MyBatis framework also uses JDBC internally, so the JDBC data type is actually mapped to the java object. There will be type conversion between the two, and their application scenarios are as follows:
*java object converted to JDBC data
The input parameter of the method in the Mapper interface corresponds to
1) The value of the where clause filter item in the select or update or delete statement
2) The value of the set clause in the update statement
3) The value of the values clause in the insert statement
The function of this part is dynamic SQL and the SQL that uses the mapper method to enter parameters, which will be explained in detail in the sixth part.
*JDBC data conversion to java object
1) The return value of the select statement uses the two attributes of resultType or resultMap for mapping. This is one of the most complicated parts of MyBatis code writing and is also the focus of testing.
2) Different from the select statement, after the update/delete/insert statement is executed, the database returns the number of affected data. If you do not need to use the quantity variable, you can set the return value of the corresponding method to void.
The so-called verification of the mapping between SQL and java is the logic of this part, which will be explained in detail in the seventh and eighth parts.
6. Analyze the assembly and verification of SQL
In the SQL example in the fourth part, the input parameters in the mapper method are used for the value of the filter item. In fact, the framework allows SQL statements to use the input parameters in the method in the select clause, the set clause of update, and the value part of insert. For dynamic processing, not limited to where clauses. What flexibility brings is the bug that may be caused when the input parameter is empty.
If the value of the filter item in the where clause is null, all filter items will be invalid, and the returned query result will be 0. Because SQL will be assembled into filter item=null, and the correct syntax to use null is is null or is not null. The same is true for update and delete statements, which will result in 0 pieces of data being affected after execution.
The value of the set clause in the update statement can be written equal to null, so if the set statement of the update statement uses input parameters but is not dynamic SQL, you must verify whether the update is allowed to be null according to the requirements.
In the above cases, I classify them as using the mapper method to enter parameters but not using dynamic SQL. It is meaningless to verify the null value of this type of SQL when doing unit tests. It must be verified at the integration test/system test level. A bug occurs because the method input parameter is empty.
The signature function of the MyBatis framework, dynamic SQL, solves the above problems to a large extent. Each label can realize through the judgment expression that if the parameter is empty, the code in the label will not be executed, so that there will be no filter item=null or set=null This happens.
The implementation of dynamic SQL is to obtain one or more values from the input parameters of the mapper method as the value of the test attribute or the value of the filter item in the where clause.
1) where tag and if tag
The figure is a simple example of mixing the where and if tags in dynamic SQL. The operation of the if tag is no different from the if in the programming language. An OGNL expression is specified through the test attribute, and it returns true to execute the code block. Code, return false to skip.
The where tag is used to make the part of the where clause in the SQL statement dynamic, and it can be used in conjunction with the if tag to realize the need for different processing of the filter conditions in the where clause according to requirements.
The method of testing the if label is the same as the white-box test of the if statement in the code. The SQL can be executed normally when the test is executed or not executed.
The idea of testing the where tag is
*Verify whether the SQL can be executed normally when all the if tags in the where tag return true
*Verify whether the SQL can be executed normally when the if tags in the where tag return false
Article 1 should be easy to understand, and Article 2 is because if all the if tags in the where tag return false, if there are no other SQL statements in the where tag, this entire paragraph will be ignored. This processing logic is actually It also provides the filter logic that must be executed when the developer can write the where clause. Take a closer look at the filter condition user_password is not null below the where tag in the above figure, which is not included in the two if tags, which means that even if the two if tags are false, this filter logic will also be used when the SQL is executed.
At this point, the SQL will be assembled like this:
select id, user_name userName, user_email userEmail
from sys_user
WHERE user_password is not null
Therefore, the conclusion is that because the where tag provides such processing logic, it is necessary to test this scenario.
2) choose label
The choose tag provides if-else logic processing capabilities for dynamic SQL, and it is implemented with when and otherwise tags. The test method is the same as the case expression in SQL. After testing each path, it is also necessary to test that there is no otherwise But when none of the conditions are met.
3) foreach tag
This tag implements the set of predicates in(?,?,?) in SQL, and there are 2 test points
*When the corresponding collection is set to not allow non-empty, the SQL runs normally when there are 2 elements in the verification
* The corresponding collection is set to allow non-empty, and the SQL runs fine when validating 0 elements and 2 elements. (Sometimes the foreach tag is placed in the if tag, and it is judged that the collection is not empty before execution)
4) include tag
This tag provides the function of separating the common parts of multiple SQL statements in the same XML file into blocks, and then using the include tag and refid attribute to reference. (Conceptually, it is no different from encapsulating a part of code as a method for other code to call)
The encapsulated SQL statement block exists in the SQL tag and has an id attribute. Since this part of the SQL statement block does not have a corresponding mapper interface, it cannot be directly tested from the interface call, which is different from the method encapsulated in the code. (Generally this part of SQL is not a complete SQL statement)
Testing of SQL statements using the include tag depends on changes in both the referrer and the referenced party.
* Both the referrer and the referenced party are newly written codes, just test the referrer directly
*Multiple references refer to the statement block in the same SQL tag, only one reference has been changed, and only this reference needs to be tested at this time
* Same as above, if a referrer and referenced party are changed, then all referrers must be tested again.
The include tag can actually call the SQL statement in another XML file. The idea of the test is the same, but it is undoubtedly a way of writing that greatly increases the complexity of the code.
To sum up, verifying dynamic SQL is very similar to path verification in white box testing, and it is necessary to verify that the SQL on each path is normal by controlling input parameters.
7. Instructions for converting JDBC data to java objects
The Select statement is composed of the interface method name and the mapping mark, and the mapping mark is divided into two types: resultType and resultMap. The mapper method with return parameters must be mapped to a java object, and there are two attributes, resultType and resultMap, for mapping. As the name suggests, its function is to map the value of the field in each row queried by the SQL command to a java object, and the tag is used to specify what kind of java object it is.
|
The return parameter type of the mapper method |
Mapping Types of SQL Statements in XML |
|
Java basic types (numbers, strings, collections) |
resultType |
|
custom entity class |
resultType /resultMap |
When the return parameter type of the mapper method is a basic type, the general select statement only queries one or a few values, and this writing method is generally used for logical judgment in the interface code.
The custom entity class can be the entity class corresponding to the database table, or it can be a DTO or response class. At this time, the select statement will query multiple values of one or more tables and map them to the entity class.
Example of Mapping Returns for Primitive Types
Example returned as DTO
resultMap is also used to map entity classes, but the implementation logic is more complicated. In terms of design, if a module only provides a query function externally, no matter how many database tables the module has data, writing a unified DTO can meet the requirements, and the resultType will be used at this time. If a module consists of multiple classes and provides multiple query functions, these functions also need to be related to each other, use nested queries, and one-to-many mapping, then you need to use resultMap. If the statement of the select tag uses resultMap, then the specific mapping for which java object needs to be obtained from the type attribute of the resultMap tag corresponding to the file.
In the example above, the type of resultMap whose id is ExtMap is com.glp.abs.dto.AssetInfoDTO, which is the name of the java object to be mapped. The column and property attributes in the result tag are used to map the fields of the database table and the attributes of the entity class. The association tag is used to specify another resultMap that is associated with this resultMap one-to-many.
When association or collection tags are used for association, the properties corresponding to the values of the property attributes of these two tags must be written in the corresponding entity class.
After explaining the resultType and resultMap, we can see how to test the mapping. If the resultType is mapped to the basic data type, it is very convenient. The return of the acquisition method can be directly asserted with the expected value. If it is mapped to an entity class, the situation is more complicated, and it is another focus of the test, please see below.
Eight. Test mapping
When writing code related to resultMap, the following problems may occur:
1 The data type of the entity class attribute, the JDBC data type of the corresponding field in the resultMap, and the data type of the database table field are inconsistent. The inconsistency here not only refers to accidentally mapping a string to a number, but also includes precision problems if the data type is incorrectly specified when the JDBC data type is converted to a java object. The corresponding relationship between the two can be found in the type.TypeHandlerRegistry of the source code of MyBatis.
2 Errors caused by the attributes column and property in the resultMap tag of the XML file
*Column or property is misspelled, and the correct value does not match
*Compared with the attributes of the corresponding entity class, the attributes column and property in the resultMap are missing, or the corresponding relationship is wrong
Although development colleagues generally use the MBG tool (a tool that comes with the MyBatis framework) to automatically generate the resultMap in XML, if the requirements and table design are frequently changed during the development process, making the MyBatis related codes have to be modified, then this The probability of class errors is greatly increased. At the same time, if there are some fields with similar names or meanings in the table (if a table has many fields, this is very common), it will also increase the probability of such problems.
The general steps to test mapping are as follows:
1. Insert data into the table corresponding to the mapper class to be tested. Each field must have a value and be able to distinguish it from each other.
2 Call the select method of the mapper class to be tested, use each value inserted to make an assertion, and verify that the mapping is correct.
If each field has a value and can be distinguished from each other when inserting data, the above errors can be prevented.
9. Verification of the mapper method in special cases
1) The mapper method without input parameters
Repeat, the input parameters of the mapper method are used in two places in the SQL statement of the XML file, one is used as the value of the OGNL expression in the test attribute, and the other is used as the value of the filter item. The absence of parameters in the method means that the SQL statement is not dynamic SQL, and the filtering logic of the where clause is also fixed. This type of code can be called directly during verification.
2) mapper method without return parameter
The mapper method does not return parameters. This kind of situation generally only appears in update/delete/insert statements. After these three types of statements are executed, the database will return the number of affected data. The MyBatis framework can define some attributes in the XML file to Obtaining the primary key of the updated/inserted data, obtaining the primary key of the data will make it very convenient to write test code to make assertions, but if the code does not leave us with such a hole, and we don’t want to modify the XML file, we have to find another way.
*If the same Mapper interface of the update/delete/insert to be tested has a select method available, then update first, and then call the select method to verify
*If not, you can use the example class of the general mapper package to directly assemble a select statement to assert