MyBatis SQL mapping file
1. XML file of SQL mapping
The core of MyBatis is SQL Management
The feature is the SQL mapping statement, which is powerful and easy to use
Several top-level elements of SQL mapping file
mapper - the root element of the SQL mapping file with a namespace attribute
Cache – configure the cache for a given namespace
Cache ref – reference cache configuration from other namespaces
resultMap – used to describe the correspondence between database result sets and objects
SQL – SQL blocks that can be reused or referenced by other statements
Insert – map insert statements
Update – map update statements
Delete – map delete statement
select – map query statement
2.select condition query
mapper layer
package com.mapper;
import com.pojo.User;
import org.apache.ibatis.annotations.Param;
import java.util.List;
/**
* User class Mapper layer
*/
public interface UserMapper {
/**
* Single condition query
* @param name
* @return
*/
public User getUserByName(@Param("name") String name);
//@Param() means alias
//After aliasing, you can write a mapping in xml to specify a variable by alias
//If there are multiple parameters without aliasing, a variable cannot be specified by its name. You need to use param1 param2
//param3...... this parameter is used to obtain the number of parameters. If there is only one parameter, it is OK not to specify it. It is recommended to specify it
}
xml Mapping File
<!--Single condition query-->
<select id="getUserByName" resultType="com.pojo.User" parameterType="string">
select * from `user` where name=#{name}
</select>
<!--
id: Specify method name
resultType: Method return type
parameterType: Incoming parameter type
#{name}The variable name that refers to the input parameter can also be used#{param1} to get the first parameter of the method
-->
Test class
/**
* MyBatis Single condition query
*/
@Test
public void getUserByName() {
SqlSession sqlSession=MyBatisUtil.createSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//Incoming parameters
User user = userMapper.getUserByName("merchant");
System.out.println("_________________________");
System.out.println(user.getId());
System.out.println(user.getName());
System.out.println(user.getPwd());
MyBatisUtil.closeSqlSession(sqlSession);
}
Query results
3. multi criteria query
1. encapsulate conditions into Java objects as input parameters
Try to pass in the User object and use the User name and password as the id information of the query User
Write code:
mapper layer
/**
* Pass in User object query object id
* @param user
* @return
*/
public int getUserIdByUser(@Param("user") User user);
xml Mapping File
<!--afferent User Object query object id-->
<select id="getUserIdByUser" resultType="int" parameterType="com.pojo.User">
select id from `user` where name=#{user.name} and pwd=#{user.pwd}
</select>
<!--It can be found that the name of the variable is used.Property to use-->
Test class
/**
* Complex parameter query data
*/
@Test
public void getUserIdByUser() {
SqlSession sqlSession=MyBatisUtil.createSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user=new User();
user.setName("merchant");
user.setPwd("123");
//Pass in objects as parameters
int id = userMapper.getUserIdByUser(user);
//output
System.out.println(id);
MyBatisUtil.closeSqlSession(sqlSession);
}
Operation results:
2. encapsulate query criteria into a Map set as input parameters
Use map to store the user name and password as the id information of the query user
Write code:
mapper layer
/**
* map Implement multi condition query as input parameter condition
* @param map
* @return
*/
public int getUserIdByMap(@Param("map")Map<String,String> map);
xml Mapping
<!--afferent map As query criteria-->
<select id="getUserIdByMap" resultType="int" parameterType="map">
select * from `user` where name=#{map.name} and pwd=#{map.pwd}
</select>
<!--here#The meaning of {map.name} is the value-->
Test class
/**
* map Implement multi condition query as input parameter condition
*/
@Test
public void getUserIdByMap() {
SqlSession sqlSession = MyBatisUtil.createSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
Map<String, String> map = new HashMap<>();
map.put("name", "merchant");
map.put("pwd", "123");
//Incoming Map object
int id = userMapper.getUserIdByMap(map);
System.out.println(id);
MyBatisUtil.closeSqlSession(sqlSession);
}
Operation results:
3. use @Param annotation to realize multi parameter input
@Param annotation can alias parameters. Therefore, when multiple parameters are passed in, sql can be injected through the corresponding alias instead of using param1, param2... To obtain a variable
Pass in the user name and password as the id information of the query user
mapper layer
/**
* Using @Param annotation to implement multi parameter input
* @param name
* @param pwd
* @return
*/
public int getUserIdByUserNameAndPwd(@Param("userName") String name,
@Param("userPwd") String pwd);
xml Mapping File
<!--apply@Param Annotation implements multi parameter input-->
<select id="getUserIdByUserNameAndPwd" resultType="int">
select * from `user` where name=#{userName} and pwd=#{userPwd}
</select>
Test class
/**
* Using @Param annotation to implement multi parameter input
*/
@Test
public void getUserIdByUserNameAndPwd() {
SqlSession sqlSession = MyBatisUtil.createSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
String name="merchant";
String pwd="123";
//Pass in multiple parameters
int id = userMapper.getUserIdByUserNameAndPwd(name, pwd);
System.out.println(id);
MyBatisUtil.closeSqlSession(sqlSession);
}
Operation results:
If fuzzy query is required, just change the sql statement. For example:
concat('%',#{content}, '%') concatenation string
4.MyBatis result mapping (resultMap element user defined result mapping
1. Brief introduction to mybatis automatic mapping, resultMap mapping sql mapping object information
Previously, we wrote sql statements without manually mapping the result set of sql queries to objects.
It is an automatic mapping implementation of mybatis. The mybatis framework will obtain the column names in the sql result set and find the set method with the same named attributes for assignment
However, the actual development process will be more complex. For example, the names of databases and applications are not unified, and the column names and attribute names are not the same
Or multi table query needs to query other table information and bind it in the set (one order information contains multiple commodity information)
In this case, MyBatis cannot be implemented through automatic mapping
So Mybatis provides a resultMap mechanism to customize the result set (manual mapping)
Create a new entity class UserResultMap instead of User, and modify the variable name to show the effect
public class UserResultMap {
private long id_ResultMap; //User id
private String name_ResultMap; //user name
private String img_ResultMap; //User Avatar
private String pwd_ResultMap; //password
//Omit set/get method
}
mapper layer
/**
* resultMap Manually map result sets
* @return
*/
public List<UserResultMap> getUserAll_Result();
Let mybatis map automatically in the original way
<!--test resultMap Manually map result sets--> <select id="getUserAll_Result" resultType="com.pojo.UserResultMap"> select * from `user` </select>
Then write the test class
/**
* Test resultMap
*/
@Test
public void getUserAll_Result() {
SqlSession sqlSession = MyBatisUtil.createSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<UserResultMap> userResultMapList = userMapper.getUserAll_Result();
System.out.println("_________________________");
userResultMapList.forEach(v->{
System.out.println(v.getName_ResultMap());
});
System.out.println("_________________________");
MyBatisUtil.closeSqlSession(sqlSession);
}
Operation results:
The result shows that the null pointer is abnormal. Because mybatis cannot find the corresponding named set method, it does not pass the value to the user object. The object in the natural collection is null
Solution 1: try using the set method name according to the database attribute field name
This is the database field name
Modify the set method name:
Then re result:
It can be concluded that mybatis is searched by the name of the set method
Okay, here are some tests
Then let's introduce resultMap
Modify xml Mapping File
<!--test resultMap Manually map result sets--> <select id="getUserAll_ResultMap" resultMap="userResultMap"> select id,`name`,pwd from `user` </select> <!--write resultMap Mapping rules--> <resultMap id="userResultMap" type="com.pojo.UserResultMap"> <id property="id_ResultMap" column="id"/> <result property="name_ResultMap" column="name"/> <result property="pwd_ResultMap" column="pwd"/> </resultMap> <!-- Can be found select Return value of label resultType Change to resultMap，The return value specifies a resultMap Rules for Written for resultMap A manual mapping was performed resultMap Two properties of id Properties and type attribute id: The unique identifier of the mapping rule set, which can be select Elemental resultMap Attribute reference type: The type returned from the mapped result set. Here, the UserResultMap Object of (Because resultMap It also specifies the return value, so you need to specify the return value in the resultMap Set return type in) resultMap There are two labels inside id Labels and result label id Label: Specifies the identification attribute corresponding to the primary key field of the data table. Setting this item can promote MyBatis Performance of the framework, especially when applying caching and nested result set mapping result Label: Specifies the mapping relationship between result set fields and entity class attributes property: Represents type One of the entity classes specified by the set method column: Represents sql If the field name of the statement query is not specified (it will be reported in red, but it will not be reported in error if the name is written correctly, so it is recommended that sql Statement to write each field name. If the query field name is duplicate, you can set an alias. If not, the value of the leftmost field name is bound by default -->
Modify the test class to output more values
/**
* Test resultMap
*/
@Test
public void getUserAll_Result() {
SqlSession sqlSession = MyBatisUtil.createSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<UserResultMap> userResultMapList = userMapper.getUserAll_ResultMap();
System.out.println("_________________________");
userResultMapList.forEach(v->{
System.out.println(v.getId_ResultMap());//id
System.out.println(v.getName_ResultMap());//name
System.out.println(v.getPwd_ResultMap());//password
System.out.println(v.getImg_ResultMap());//Avatar
System.out.println("_________________________");
});
MyBatisUtil.closeSqlSession(sqlSession);
}
Run again:
It can be found that all mapped results exist, and the results without mapping are null
In addition, after using resultMap, even if the field name corresponds to the set method name, it will not be automatically mapped, because resultMap means that manual mapping is used, and the content that is not manually mapped will not be bound even if it is found
And the resultMap can be reused. It is just a mapping rule. The query label only needs to be specified with the resultMap attribute
2.resultMap nested result mapping association tag
General entity classes contain information related to multiple entity classes
If you want to query, you can design multi table associated query to query other objects
mybatis provides the association element to handle the case that the assignment attribute of the query entity class contains other entity classes
Take this as a demonstration
New entity class UserType
package com.pojo;
public class UserType {
private long id;
private String typename;
}
Add a UserType attribute to the UserResultMap entity class
//Add a new attribute private UserType userType_ResultMap; //userType object for testing //Omit set\get
mapper layer
/**
* association Mapping entity class properties of UserResultMap
* @return
*/
public UserResultMap getUserByUserId(@Param("userId") long userId);
xml Mapping File
<!--association mapping UserResultMap Entity class properties for-->
<select id="getUserByUserId" resultMap="association">
select user.id as userId,
user.name,
user.pwd,
ut.id as userTypeId,
ut.typename
from user
join user_type ut on user.usertype = ut.id
where user.id=#{userId}
</select>
<resultMap id="association" type="com.pojo.UserResultMap">
<id property="id_ResultMap" column="userId"/>
<result property="name_ResultMap" column="name"/>
<result property="pwd_ResultMap" column="pwd"/>
<association property="userType_ResultMap" javaType="com.pojo.UserType">
<id property="id" column="userTypeId"/>
<result property="typename" column="typename"/>
</association>
</resultMap>
<!--
Can be found association Is a label used to specify an entity class attribute, and then assign an extra value to this entity class
Inside property and javaType Two properties of
property: Of an entity class set method
javaType: Specify return type
Of which id and result The meaning of the label is the same as the previous explanation
-->
Test class
/**
* association Mapping entity class properties of UserResultMap
*/
@Test
public void getUserByUserId() {
SqlSession sqlSession = MyBatisUtil.createSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
UserResultMap userResultMap = userMapper.getUserByUserId(10004);
System.out.println("_________________________");
//Output user information
System.out.println(userResultMap.getId_ResultMap());
System.out.println(userResultMap.getName_ResultMap());
System.out.println(userResultMap.getPwd_ResultMap());
//Get UserType entity class in user
UserType userType = userResultMap.getUserType_ResultMap();
System.out.println("_________________________");
//Output user type entity class information
System.out.println(userType.getId());
System.out.println(userType.getTypename());
System.out.println("_________________________");
MyBatisUtil.closeSqlSession(sqlSession);
}
Operation results:
3. The collection tag element implements one to many (Collection)
The tags collection and association are similar, but collection represents one to many
A user can have multiple receiving addresses. Take the following as an example
Add entity class of address information
package com.pojo;
public class UserAddress {
private long id;
private long userId;
private String name;
private String phone;
private String province;
private String city;
private String region;
private String particular;
private long state;
//Omit set\get
}
Add address collection property for UserResultMap entity class
private List<UserAddress> userAddressList; //User address collection //Omit set\get
mapper layer
/**
* collection One to many mapping
* @return
*/
public List<UserResultMap> getUserAllAndUserAddress();
collection layer
<!--collection One to many query-->
<select id="getUserAllAndUserAddress" resultMap="collection">
select user.id as userId,
user.name as userName,
user.img,
user.pwd,
ua.id as addressId,
ua.user_id,
ua.name as addressUserName,
ua.phone,
ua.province,
ua.city,
ua.region,
ua.particular,
ua.state
from user
join user_address ua on user.id = ua.user_id
where user.id=#{userId}
</select>
<resultMap id="collection" type="com.pojo.UserResultMap">
<id property="id_ResultMap" column="userId"/>
<result property="name_ResultMap" column="userName"/>
<result property="pwd_ResultMap" column="pwd"/>
<collection property="userAddressList" ofType="com.pojo.UserAddress">
<id property="id" column="addressId"/>
<result property="name" column="addressUserName"/>
<result property="phone" column="phone"/>
<result property="province" column="province"/>
<result property="city" column="city"/>
<result property="region" column="region"/>
<result property="particular" column="particular"/>
<result property="state" column="state"/>
</collection>
</resultMap>
key point
Note that the data queried by sql is:
You can find that the first three items of sql query data are the same
The following is the list of user sets that we need to store
How to merge three pieces of data into one piece of data?
At this time, the id tag plays a key role. The id of the query user information specifies the userId column, and all values of this class are 10001. Therefore, only one piece of user information is injected
However, the following multiple pieces of data specify the attributes of the collection through the collection tag, while the primary key columns specified by the id tag in the collection are different, so the three pieces of data will not be merged into one, so the three pieces of data will be stored in the collection
This is why you need the id tag and the result tag to assign values manually
Writing test classes
/**
* collection Implement one to many query
*/
@Test
public void getUserAllAndUserAddress() {
SqlSession sqlSession = MyBatisUtil.createSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<UserResultMap> userList = userMapper.getUserAllAndUserAddress(10001);
userList.forEach(v->{
System.out.println(v.getId_ResultMap());
System.out.println(v.getName_ResultMap());
System.out.println(v.getPwd_ResultMap());
System.out.println("____________Multiple address information is as follows_____________");
v.getUserAddressList().forEach(v2->{
System.out.println(v2.getId());
System.out.println(v2.getName());
System.out.println(v2.getPhone());
System.out.println(v2.getProvince());
System.out.println(v2.getCity());
System.out.println(v2.getRegion());
System.out.println(v2.getParticular());
System.out.println("_________________________");
});
});
MyBatisUtil.closeSqlSession(sqlSession);
}
Operation results:
4. summary resultType and resultMap
Use the select query sql statement to use resultType and resultMap to return the data source results
From the perspective of usage scenarios
**resultType:** specifies the result type directly. It relies on automatic mapping to encapsulate complex data types such as entity classes. It is suitable for simple and direct data encapsulation scenarios
**resultMap:** is a reference to the external resultMap definition. It can freely control the result mapping rules and encapsulation scope, and can handle the more complex problems such as the inconsistency between the result set field name and the entity class attribute name, and the need to use nested mapping for the connection query results
From the perspective of underlying implementation classes
Mybatis first stores the queried result set in the Map structure with the field name key
When the select element uses the resultType to specify the result type, it is implemented by calling the setter accessor;
When using the resultMap attribute, the key value pairs in the Map are assigned to the specified entity class attribute according to the mapping rules defined in the referenced resultMap element
Finally, the resultType and resultMap attributes encapsulate the query field results, which are essentially the same as the Map data results.
However, in the select query element, the two cannot be used at the same time. If they are used at the same time, the resultType result will be ignored or an error will be reported directly
5.resutlMap automatic mapping behavior
As mentioned above, if the resultMap mapping is used, it must be manually mapped. However, the operation is too cumbersome, so the automatic mapping rules of Mybatis can be modified
Set the behavior of automatic mapping in the MyBatis framework core configuration file
<!-- 2.set up MyBatis Runtime behavior of the framework--> <settings> <!-- Set auto eject behavior --> <setting name="autoMappingBehavior" value="FULL"/> </settings>
After modification, the values not mapped manually in resultMap will also be mapped automatically, provided that the name corresponds to the
The value of autoMappingBehavior has three values
**NONE:** disable automatic mapping (resultType will fail)
PARTIAL: (default) for those without resultMap, use automatic mapping. For those with resultMap, change to manual mapping
**FULL:** all use automatic mapping, and resultMap also uses automatic mapping
6. Adding, deleting and modifying mybatis
Three tags are provided for addition, deletion and modification
insert, delete and update Tags
At the same time, the three operations used to add, delete and modify all return the number of successful rows, so the three tags return int type data by default, that is
No return value properties for resultType and resultMap
1. introduction to adding
Add operation under test
Entity class
public class User {
private long id; //User id
private String name; //user name
private String pwd; //password
//Since the database to be tested is extracted from a project, the following properties are not shown, just a little demonstration
private String img; //User Avatar
private String phone;
private long usertype;
private long sex;
private long state;
private long credit;
//Omit set/get
}
Mapper interface
/**
* Add user information
* @return
*/
public int addUserByUser(@Param("user") User user);
xml Mapping
<!--Add user information-->
<insert id="addUserByUser"><!--Default return affected rows( int)So no resultType and resultMap-->
insert into user(`name`, img, pwd, phone, usertype, sex, state, credit)
VALUE (#{user.name},#{user.img},#{user.pwd},#{user.phone},#{user.usertype},#{user.sex},#{user.state},#{user.credit})
</insert>
Test class
@Test
public void addUserByUser() {
//When the tool class builds a SqlSession, it sets the start transaction (false). Adding, deleting and modifying such operations need to be submitted manually
SqlSession sqlSession=MyBatisUtil.createSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user=new User();
user.setName("New user");
user.setImg("Picture information");
user.setPwd("123");
user.setPhone("12345678911");
user.setUsertype(0);
user.setSex(1);
user.setState(1);
user.setCredit(100);
int i = userMapper.addUserByUser(user);
System.out.println("Number of successful rows:"+i);
if (i>0){
//Submit data
sqlSession.commit();
}else{
//Data rollback
sqlSession.rollback();
}
MyBatisUtil.closeSqlSession(sqlSession);
}
Operation results:
Database:
The deletion and modification procedures are the same, so I won't describe them in detail
2. method of obtaining primary key after adding
mapper interface
/**
* Add user information
*
* @return
*/
public int addUserByUser(@Param("user") User user);
xml Mapping File
<!--Add user information-->
<insert id="addUserByUser" useGeneratedKeys="true" keyProperty="user.id">
insert into user(`name`, img, pwd, phone, usertype, sex, state, credit)
VALUE (#{user.name},#{user.img},#{user.pwd},#{user.phone},#{user.usertype},#{user.sex},#{user.state},#{user.credit})
</insert>
<!--
useGeneratedKeys: Return auto increment PK( true|false)
keyProperty: Returns the primary key value to be injected into the specified variable
-->
Test class
@Test
public void addUserByUser() {
//When the tool class builds a SqlSession, it sets the start transaction (false). Adding, deleting and modifying such operations need to be submitted manually
SqlSession sqlSession=MyBatisUtil.createSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user=new User();
user.setName("New user");
user.setImg("Picture information");
user.setPwd("123");
user.setPhone("12345678911");
user.setUsertype(0);
user.setSex(1);
user.setState(1);
user.setCredit(100);
int i = userMapper.addUserByUser(user);
System.out.println("Number of successful rows:"+i);
System.out.println("Returned primary key:"+user.getId());
if (i>0){
//Submit data
sqlSession.commit();
}else{
//Data rollback
sqlSession.rollback();
}
MyBatisUtil.closeSqlSession(sqlSession);
}
Breakpoint debugging:
Before adding data
It can be found that before adding data, the id of the incoming User is not assigned, which is 0
After adding data
It can be found that the generated primary key after adding data is returned to the ID of the passed in user parameter (user.id specified in xml)
7.Mybatis framework cache (just understand)
There are two levels of caching for Mybatis
L1 cache
Mybatis L1 cache is a HashMap local cache based on the PerpetualCache. By default, it is a SqlSession level cache, that is, it is valid within one life cycle of a SqlSession. After a SqlSession is closed, the L1 cache of the SqlSession will be cleared, and mybatis L1 cache is on by default
L2 cache
The L2 cache is at the SqlSessionFactory level, that is, the application level. The data in the cache can be shared by all sqlsessions.
The L2 cache of Mybatis is turned off by default. It needs to be set in the core configuration of Mybatis
L2 cache usage:
1. configure the core file
<!-- 2.set up MyBatis Runtime behavior of the framework--> <settings> <!-- Enable global L2 cache --> <setting name="cacheEnabled" value="true"/> </settings>
2.sql mapping xml file configuration cache
After the global L2 cache is enabled, the L2 cache is not used by default. You need to configure the cache in the Sql mapping file to enable the L2 cache for the current namespace
<mapper namespace="com.mapper.UserMapper"> <!--Cache configuration--> <cache eviction="FIFO" flushInterval="60000" size="512" readOnly="true"/> </mapper>
The functions of various elements in the cache tag are as follows:
**Occurrence:* * select the cache recycling option, which mainly includes the following strategies
1 (default) least recently recycled, removing cache objects that have not been used for the longest time
2 First in first out, removing objects in the order they enter the cache
3 Soft references, removing objects based on garbage collector status and soft reference rules
4 Weak references, more actively remove objects based on garbage collector and weak reference rules
**flushInterval:** sets the cache refresh interval. The unit (ms) sets how often the cache is automatically refreshed. By default, it is not automatically refreshed
**size:* * set the maximum number of objects stored in the cache, 1024 by default
**readOnly:** set whether the cache data is read-only. The default is false. Identify that the cache data is only used for read operations. Mybatis will return the same instance of the cache object for all operations that get data from the cache to obtain better performance
3. after configuring sql to support L2 cache, if you need to adjust individual queries, you can set them separately in the select element
For example:
<!--afferent User Object query object id-->
<select id="getUserIdByUser" resultType="int" parameterType="com.pojo.User" useCache="false"> <!-- useCache="false"Represents disabling the current sql L2 cache -->
select id from `user` where name=#{user.name} and pwd=#{user.pwd}
</select>
Simply understand the cache of Mybatis. Because the data volume reaches a certain scale, the built-in cache mechanism will not meet the requirements. The core of Mybatis is Sql Management. The number of caches is not what Mybatis is good at. Therefore, it is more reasonable to use Redis, MongoDB, OSCache, Memcached and other professional cache servers for caching data